|
如下图所示，A列已经按部门进行了排序，要根据不同部门来标记颜色。 本题的主要切入点是： 自A2单元格开始向下依次判断有多少个不重复值，再判断不重复值的数量是不是2的倍数。 将公式运用到条件格式当中，就可以实现题目要求了。 具体操作的方法如下： 选择A2:C14单元格区域，依次单击【开始】→【条件格式】→【新建规则】→【使用公式确定要设置格式的单元格】。 输入以下公式： =MOD(ROUND(SUM(1/COUNTIF($A$2:$A2,$A$2:$A2)),),2) 当选择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/13oupHquSfTuN0b2T9d_5Iw |
|
|
来自： hercules028 > 《excel》
