xssfworkbook设置单元格格式_「高效办公」Excel条件格式的高级应用方法

在讲解条件格式之前，需要先了解Excel中单元格相对引用的用法，如下图，A列是一组数据，在C3单元格输入公式=A1，再将C3的公式向下填充到C4、C5、C6，公式内引用的单元格对应变成A2、A3、A4。随着公式向其他单元格填充，公式内引用的单元格会对应变化，这就是单元格的相对引用。

接下来讲解条件格式，在Excel内，单元格由两部分组成，一是我们输入的内容，二是格式，包括背景颜色，边框颜色、字体颜色等等。

在格式上，Excel有条件格式的功能区，可用突出显示规则（包括大于指定值、小于指定值、包含指定文本等，格式可以自定义），最前/最后规则（基于位置突出单元格，格式可以自定义）。也可以用数据条、色阶、图标集功能针对一组数据进行填充，填充的规则是按照每个单元格内数据值的大小，如下图所示：

三种格式填充的规则都可以进行编辑

如果以上五种规则都不能实现我们要的效果，可以选中数据区域，点击新建规则，使用公式来确定哪些单元格进行格式填充，新建规则方法如下图，在中间区域输入公式，在下方设置格式，如果公式返回的结果是True，就填充指定格式，如果公式返回的结果是False，就不填充格式。

用一个小案例进行说明，如下图所示，第1行是指定学生姓名，第3-13行是所有学生姓名，我们现在要求在第3-13行突出显示（蓝底填充）第1行出现的名字。

因为是在第3-13行设置格式，所以要先选中B3:J13区域，点击条件格式-新建规则-使用公式确定格式，现在公式填充=1，在格式上设定蓝色背景填充，点击确定，所有的单元格都会被填充蓝色背景。

运行逻辑是，选中B3:J13区域设置条件格式，会遍历B3:J13每个单元格，当遍历到某个单元格时，看下条件格式里面的公式返回的是True还是False，如果是True就给到格式，如果是False，就不给格式，遍历下一个单元格。因为我们的公式是=1，也就是遍历所有单元格，公式返回的都是1（也就是True），就给每个单元格蓝色填充。

选中B3:J13区域，点击条件格式-管理规则，就可以更改公式了，现在把=1 改成 =match(B3,$B$1:$H$1,0)，点击确定，就会返回下图结果，也是我们想要的结果。

运行逻辑是遍历B3:J13每个单元格，会从B3开始遍历，条件格式的公式是=match(B3,$B$1:$H$1,0)，遍历到B4，条件格式的公式变成=match(B4,$B$1:$H$1,0)，遍历到B5,条件格式的公式变成=match(B5,$B$1:$H$1,0)，条件格式的公式引用的单元格会根据B3:J13区域的遍历单元格变化而变化，这也就是最开始讲的Excel单元格相对引用。

match函数的用法，在我前面的文章有写。整体逻辑是遍历B3:J13每个单元格，条件格式的match函数也会遍历B3:J13区域每个单元格，如果单元格在第1行出现过，match函数就返回一个值（非0和非错误的值都为true)，给到格式，如果在第1行没有出现过，就返回错误值（false），就不给格式。

内容扩展：

如果我们把条件格式改成=match(B4,$B$1:$H$1,0)，会变成什么样？如下图所示，也会得到蓝色填充，但是结果是错误的。如下图：

逻辑还是遍历B3:J13每个单元格，会从B3开始遍历，遍历B3的时候，条件格式的公式是=match(B4,$B$1:$H$1,0)，当遍历到B6的时候，条件格式的公式会变成=match(B7,$B$1:$H$1,0)。而B7在第一行，match函数的是一个值，也就是true，会给格式，而这时我们遍历的是B6单元格，所以就给B6单元格进行格式填充。

难点是单元格的相对引用，随着我们遍历区域单元格时，条件格式里面公式引用的单元格会相应变化。

案例扩展：

如下图，我们在B3和B4做了数据验证-序列，B3可以选择任意型号，B4可以选择任意规格，要求当确定型号和规格后，在E2:G10对应的值会黄底填充。比如现在B3是A0111，B4是20，对应的就是F3单元格，给F3进行黄底填充。

选中E2:G10区域，点击条件格式-新建规则-使用公式，在公式栏输入公式：=AND($D2=$B$3,E$1=$B$4)，点击确定就会得到想要的结果，如下图。

逻辑是遍历E2:G10区域每个单元格，当遍历某个单元格时，公式里返回true，则给格式填充。当遍历到E2时，公式是=AND($D2=$B$3,E$1=$B$4)，当遍历到F2时，公式变成=AND($D2=$B$3,F$1=$B$4)，当遍历到E3时，公式变成=AND($D3=$B$3,E$1=$B$4)，当遍历到F3时，公式变成=AND($D3=$B$3,F$1=$B$4)，而D3=B3，F1=B4，返回True，就给F3单元格填充格式。

当B3和B4单元格发生变化时，公式返回的结果也会相应变化，找到某个单元格所在行的型号=B3，且所在列的规格=B4时，给这个单元格填充格式。

难点：单元格的混合引用。

如果有用，给个赞和关注哈，谢谢~