根据部门标颜色,查看数据不出错

小伙伴们好啊,今天和大家分享一个条件格式有关的技巧。

如下图所示,A列已经按部门进行了排序,要根据不同部门来标记颜色。

根据部门标颜色,查看数据不出错插图

本题的主要切入点是:

自A2单元格开始向下依次判断有多少个不重复值,再判断不重复值的数量是不是2的倍数。

将公式运用到条件格式当中,就可以实现题目要求了。

具体操作的方法如下:

选择A2:C14单元格区域,依次单击【开始】→【条件格式】→【新建规则】→【使用公式确定要设置格式的单元格】。

输入以下公式:

=MOD(ROUND(SUM(1/COUNTIF($A$2:$A2,$A$2:$A2)),),2)

根据部门标颜色,查看数据不出错插图(1)

当选择A2:C14单元格区域的时候,第一个选中的单元格“A2”叫做活动单元格,在条件格式中可以直接针对这个单元格使用公式,Excel会自动将公式应用到我们提前选中的区域(A2:C14)。


接下来,我们简单说一下条件格式中这个公式的意思。

=MOD(ROUND(SUM(1/COUNTIF($A$2:$A2,$A$2:$A2)),),2)

先来看这一部分:

SUM(1/COUNTIF($A$2:$A2,$A$2:$A2))

细心的小伙伴可能发现了,这个公式中第一个A2有两个$($A$2),而第二个A2只有一个$($A2),这一美元到底有什么作用呢?

说到这里,有必要和大家先说说绝对引用与相对引用:

A1——相对引用,向右向下复制公式时引用的范围都会变;

A$1——列相对行绝对引用,向右复制公式时列标变化而向下复制时行号不会变。

$A1——列绝对行相对引用,向右复制公式时列标不会变而向下复制公式时行号会变。

$A$1——向右向下复制公式时引用的范围都不会变。

也可以理解成这个$就像一个钉子,钉到行号前面,行号就不变了;钉到列标前面,列标就不会变了。这么一说有点像绕口令,大家有时间可以试一下,实际动动手,会更容易理解一些。


本例中的$A$2:$A2这一部分,$A$2使用的是绝对引用, $A2使用的是列绝对引用。

当公式作用到B、C列中时,引用的列都不会发生变化,始终会计算A列的内容。

当公式作用到第三行时,$A2的行号发生变化,公式的引用区域就变成

$A$2:$A3。当公式作用到第四行时,公式的引用区域就变成$A$2:$A4……

也就是对A列自A2开始,到公式所在行的数据区域进行不重复的计数。


关于不重复计数的计算过程,可以参考一下下面的简要说明。

公式中包含了一个简单的数学逻辑:

任意一个数据重复出现N次,N个1/N的和值为1。

公式中的“COUNTIF($A$2:$A2,$A$2:$A2)”部分,作用是分别统计$A$2:$A2这个动态扩展的单元格区域中,每个元素出现的次数

以A14单元格为例,返回内存数组结果为:

{4;4;4;4;4;4;4;4;2;2;2;2;1}

再使用1除以返回的内存数组,即相当于计算COUNTIF函数所返回内存数组的倒数。

如果单元格的值在区域中是唯一值,这一步的结果是1。

如果重复出现两次,这一步的结果就有两个1/2。

如果单元格的值在区域中重复出现3次,结果就有3个1/3…

即每个元素对应的倒数合计起来结果仍是1。

最后用SUM函数求和,得出在动态扩展的数据范围中的不重复人数。

虽然这是一个数组公式,但是在条件格式中使用数组公式时,不需要按Shift+ctrl+回车结束。


MOD函数返回不重复人数与2相除的余数。得到结果为1或是0。在条件格式中,如果指定的条件返回逻辑值TRUE或是不等于0,就会返回我们指定的格式。

由于在实际运算中,可能会因为浮点误差造成MOD函数计算结果错误,所以在公式中使用了ROUND函数,对总人数的计算结果进行修约保留到整数。

好了,今天的内容就是这些吧,祝各位小伙伴一天好心情~~


练习文件在此:

链接: https://pan.baidu.com/s/15CS1jBJIZ-YC_NB7K1738g

提取码: ufy5



图文制作:祝洪忠

图片部分来自网络,如有侵权请联系QQ897219494,或者发布邮箱897219494@qq.com
技能大全,高效工具 » 根据部门标颜色,查看数据不出错

提供最优质的资源集合

立即查看 了解详情