![](/assets/home/images/public/lianxian.jpg)
- 视频
- 图文教程
- 教学动态
- 设计资讯
- 学员天地
- 图文教程 >
- 电脑与办公教程 >
- EXCEL >
- 用公式设置条件格式的规则,避免错误效果
用公式设置条件格式的规则,避免错误效果
作者:小窝来源:部落窝教育发布时间:2023-08-02 09:59:07点击:825
条件格式效果错误,不是自己想要的,原因是什么呢?用公式设置条件格式需要遵循什么样的规则才能避免错误呢?文章分别讲了四种需求的条件格式设置规则。
1.条件格式效果错误原因
为何有时用公式设置的条件格式效果达不到想要的结果,是错的?譬如下方。
需要:如果E列中数据大于100,其所在行整行数据突出显示。
实际:
用公式设置条件格式得到错误结果,主要有3个原因。
(1)条件单元格的引用方式(绝对引用或相对引用)错误
(2)条件单元格的引用地址错误
(3)公式错误
3个原因中,前方两个错误最迷惑人,下面具体说说。
2.用公式设置条件格式的单元格引用规则
1)需求1:当单元格条件为真,数据所在整行突显
整行突显,意味着公式右拉被引用单元格也要不变,所以需要锁定列。
规则:条件单元格的列绝对引用,行数等于应用范围中的最小行,列数在应用范围中按需指定。
譬如, D列中<100的数据所在行突出显示,应用范围C2:G11。
格式公式=$D2<100。
条件单元格D2的列绝对引用,行号是应用范围中的小值2。
再譬如: D列中重复的数值所在行突出显示,应用范围C2:G11。
格式公式=COUNTIF($D$2:$D$12,$D2)>1
条件单元格D2是列锁定,行数为范围最小值2。
公式中的其他部分,如$D$2:$D$12,应按在单元格中输入的同判断的可下拉右拉填充公式书写。
譬如,此处判断公式写作“COUNTIF($D$2:$D$12,$D2)>1”,因此格式公式中这部分就是$D$2:$D$12。
注:条件格式中通常不适合用“D:D”等整列整行引用方式。
又譬如,突显D列中最小数据所在行。
格式公式=$D2=MIN($D$2:$D$11)
若直接在H2输入可下拉右拉公式判断,=$D2=MIN($D$2:$D$11)。
2)需求2:单元格条件为真,数据所在整列突显
整列突显,意味着公式下拉被引用单元格也要不变,所以需要锁定行。
规则:条件单元格的行绝对引用,地址等于应用范围中第一个单元格。
譬如,第2行中>100的数字所在列突出显示,应用范围C2:G11。
格式公式=C$2>100
条件单元格C2的行绝对引用,地址是应用范围中第一个单元格C2。
再譬如, C2:G11内各列的列首数据有重复的,则数据所在整列突出显示。
格式公式=COUNTIF(C$2:C$11,C$2)>1
条件单元格C2的行绝对引用,地址等于应用范围中第一个单元格C2。
若直接在单元格中输入下拉右拉公式判断各列的首值是否重复,公式=COUNTIF(C$2:C$11,C$2)>1。
3)需求3:如果条件为真,数据所在单元格突显
规则:单元格为相对引用,行数为应用范围最小行,列数为应用范围第一列。
譬如,F列中大于50小于110的数据突出显示。
=AND(F2>50,F2<110)
条件单元格F2,行列都是相对引用,行号是应用范围F2:F11中最小行数2。
再譬如:在C2:G11内,如果数字在列内存在重复,则重复数字突显。
格式公式=COUNTIF(C$2:C$11,C2)>1
条件单元格C2,行列都是相对引用,行号是应用范围最小行2,列是应用范围首列C。
4)需求4:如果条件为真,应用范围中所有数据突显
规则:单元格为绝对引用,具体行列数按需指定。
譬如,若H1等于TRUE,则E列数据突出显示。
格式公式=$H$1=TRUE
条件单元格H1的行列都绝对引用。
再譬如,若G5等于G列最小值,则C2:G11全部突显。
格式公式=$G$5=MIN($G$2:$G$11)
到此,你知道文章最初部分格式效果错误的原因吗?
本文配套的练习课件请加入QQ群:902294808下载。
做Excel高手,快速提升工作效率,部落窝教育 Excel精品好课任你选择!
扫下方二维码关注公众号,可随时随地学习Excel:
相关推荐:
Excel里的条件格式图标集,你会用吗?
用条件格式查找数据
用条件格式建立报价查询系统、自律打卡表
半圆状的柱形图
版权申明:
本文作者小窝;部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。
最热教程
- 像绿皮火车一样长像珠穆拉玛峰一样高的Excel表怎么操作才方便?
- Power Query实战:按指定次数递增数据
- 2019年全网最全—excel提取身份证信息合集!(建议收藏)-下篇
- 明明没有重复,Excel却判定数据重复,这是怎么回事?
- 文本格式的求和,及求和中最容易出现的问题解疑
- 致命缺陷:不懂一维表!
- 函数组合思维,你有吗?
- 学会这2个公式,整理考勤数据只要一分钟
- 就算被说是拍马屁也成,今天你应该这样发Excel报表……
- 如何计算Excel单元格中的算式,四种求和方法请收好!
最新教程
- 提取、查找、替换数据的王者——REGEXP正则函数
- Excel表10万行数据,筛选卡顿怎么办?
- 两个不用嵌套的万能提取公式,含用REGEXP函数提取
- 用Excel制作刷题器,备考无忧 !
- 如何用LET+LAMBDA直接写递归公式?
- 用函数合并多个工作表数据
- 用LAMBDA自定义颜色求和函数
- 用SUMIF错位求和多表求和与查找
- BYROW和BYCOL函数用法详解
- makearray函数用法6例