excel聚光灯应用实例——行列及区域聚光应用

szpwiry

excel聚光灯的应用主要在多数据情况下的突出显示，便于看出某列某行或者某区域的关联性。比如在查找某人的多列信息时，经常会有查找到姓名，需要关联的其他信息在同一行的多列之后，往往拉到后面之后又难以看出是否是该姓名的对应关系，这个时候如果使用聚光灯效果就可以清楚看到数据的关联性。其他很多地方也需要用到这种关联性数据的突出显示功能，以上均可使用excel聚光灯。这里以数独的9宫为例，把行、列、区域的关联数据突出显示功能分别做一个示范。为了让初次接触这个功能的更容易理解，这里先不使用VBA，只使用条件格式，此时单元格需要双击进入编辑状态才能触发条件格式的判定。需要单击单元格也实现条件格式的，只需要加上以下VBA语句即可。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Application.CutCopyMode = False Then

Application.Calculate

End If

End Sub

1、条件格式的数据范围选定：把需要的实现聚光灯效果的区域选定，再点选条件格式，以数独为例，就是选定9*9的单元格区域即可，太多反而造成干扰。
2、行、列关联数据的条件格式：需要实现的目标是该区域内某单元格选定后，所在行、所在列的其他数据均突出显示。
条件格式的实现——选定9*9的目标区域——条件格式——管理规则——新建规则——选择使用公式确定——在公式中输入=(CELL("row")=ROW())+(CELL("col")=COLUMN())，然后选定你需要的颜色格式即可。
公式解析：由于之前选定了9*9的目标区域，cell("row")是对整个9*9区域内的单元格起作用的，目标区域内所有单元格都返回对应行标，row（）是当前进入编辑状态下的单元格对应行标，(CELL("row")=ROW())整个语句就是判定目标区域内的所有行标是否等于当前选定单元格的行标，相等就是同一行，并返回逻辑值1，不等就返回逻辑值0
(CELL("col")=COLUMN())语句的作用类似的就是判断是否同一列，并返回相应逻辑值。
最后两个语句相加，实际相当于两个逻辑判断的逻辑或运算，当然也可以用OR（）来进行逻辑或的运算，直接相加是更便于大家的理解，整个=(CELL("row")=ROW())+(CELL("col")=COLUMN())语句最后返回的是0值（非同行又非同列）、1值（同行或者同列）、2值（同行又同列，实际就是我们选定的这个单元格才会返回2值）。所有的0值均是逻辑假的效果，设定的条件格式不会生效，所有的非0值均是逻辑真的效果，设定的条件格式会对这些单元格生效——改变单元格颜色。
3、区域关联数据的条件格式:这个要根据大家的实际应用去写条件格式语句，这里以数独为例，就是要对数独的九宫进行判断，是否是同一个宫的数据。以a1单元格为九宫起点的话，则i9单元格为九宫终点。数独九宫为9*9的区域，每个宫都是3*3的区域，接下来将九宫蛇形排序确定第几宫，以第一宫为例，实际是a1：c3的区域，该区域的行标为1到3，列标为1到3；第二宫为d1：f3区域，行标1到3，列标4到6；第三宫为g1：i3区域，行标1到3，列标7到9；第四宫为a4：c6区域，行标4到6，列标1到3，之后类推，可以很容易看出九宫实际是行列数每隔3个数字为一个宫
现在我们要解决的是要把处在同一宫的单元格行标及列标的共同点提取出来，并以逻辑判断的手段去判断并返回逻辑值。由于九宫实际是行列数每隔3个数字为一个宫，这里也就是同一宫单元格的行标列标的共同之处。
这里先把语句放出来=(ROUNDUP(CELL("row")/3,0)=ROUNDUP(ROW()/3,0))*(ROUNDUP(CELL("col")/3,0)=ROUNDUP(COLUMN()/3,0))，把9*9单元格内可以都先填入这个语句
以第一宫为例解析公式，语句的前半部分(ROUNDUP(CELL("row")/3,0)=ROUNDUP(ROW()/3,0))，roundup函数是对目标值进行向上取值，第二参数设为0是小数位保留0位，即进行向上取整数的作用。目标值CELL("row")/3即所有单元格的行标除以3，此时9*9区域内单元格的结果应该为0.33；0.66；1；1.33；1.66；2；2.33；2.66；3九个值，使用ROUNDUP(CELL("row")/3,0)向上取整后，将是1；1；1；2；2；2；3；3；3；的数值，实际也就将9*9的单元格在行方向上先划分成3部分，所有1值必定是在1到3宫，所有2值必定是在4到6宫，所有3值必定是在7到9宫。同时利用ROUNDUP(ROW()/3,0)选定的单元格也对行标进行除以3之后向上取整，并返回1到3的整值，利用(ROUNDUP(CELL("row")/3,0)=ROUNDUP(ROW()/3,0))进行逻辑判断，如果相等，说明在行的分区上是与所选定的单元格同区，并返回逻辑真，也即数值1，如果不等，则返回逻辑假0值。
语句的后半部分(ROUNDUP(CELL("col")/3,0)=ROUNDUP(COLUMN()/3,0))，也就是对列方向进行3个分区，并判断是否等于选定单元格的列分区值，逻辑真返回1值，逻辑假返回0值。
至此，语句的前后两部分返回值都是1或者0，最后九宫的代码实际为行列分区值的数对第一宫（1,1），第二宫（1,2），第三宫（1,3），第四宫（2,1）……，我们的目标是要确定9*9的单元格是否与所选定单元格在同一宫，此时也即要求行分区值等于选定单元格行分区值，同时列分区值也等于选定单元格列分区值，在逻辑运算上也就是逻辑与的运算，可以用and（）来实现，我在这里直接使用的乘法运算符连接了语句的前后两部分，最后返回的必定是1或者0，值为1说明两个部分都是返回了逻辑真，也即同时满足了两个条件，即单元格与选定单元格在同一宫。值为0说明至少不满足其中一个条件，也即不在同一宫。
最后强调：我们是以a1单元格为起点来写公式的，如果在选定区域列方向前面插入1列，则会出现错误，原因是第1宫的列坐标实际是2,3,4，则利用除以3向上取整后，返回值分别是1,1,2了，此时的同一宫的列分区值不相等了，解决办法就是把列分区的语句(ROUNDUP(CELL("col")/3,0)=ROUNDUP(COLUMN()/3,0))，先进行分别减1再除以3向上取整的运算，此时会返回正确的结果。选定区域行方向前面插入也会有这个问题，同样的处理。在选定区域之后的行列插入不会影响到选定区域的行列值，所以不用管。以及如果插入的3的整数倍的行列，也不会对判定结果产生影响，理由简单就不赘诉了。
如果不确定是否会产生插入行列的操作，可以把选定区域的起点单元格行列值分别减去1作为可能的差值，然后改写公式，这里以行分区语句为例，差值表达式为row（$a$1）-1，注意这里对初始单元格要使用绝对引用，行分区语句改写成(ROUNDUP((CELL("row")-row（$a$1）+1)/3,0)=ROUNDUP((ROW()-row（$a$1）+1)/3,0))即可，列坐标也类似改写即可，实际上也就是将区域内的单元格行列值强制转换成以1行1列为起点的新行列坐标，这样改写后无论怎样去插入行列都不会影响条件格式的正确生效了。
4、突显当前选定单元格操作：可能存在需要把当前选定单元格以其他颜色突显的需求，条件格式语句为=(CELL("row")=ROW())*(CELL("col")=COLUMN())，这里用的是乘法，也就是逻辑与运算的效果，有前面的解析，这里实际也很简单了，就不再解释语句了。5、已写入VBA语句，如果你们的办公软件不支持VBA，此时条件格式的持续生效必须是双击单元格，否则单击单元格只能确保第一次生效，其后的操作无效，另为了方便大家看到我样表的效果，请把excel选项中的enter键换行功能关闭，然后双击单元格后回车就可以看到每次的变化，以及相应的逻辑判断返回值了。另附上WPS2019的VBA支持安装包https://www.jyrd.com/xiazai/2/46/6490.html#xzdz，需要的自己去按1,2,3,4的顺序逐个安装就可以在WPS2019中支持VBA语句了，其他版本的请自行搜索VBA支持包。只要支持VBA的，那么就可以实现单击单元格由条件格式反馈聚光灯效果了。
这里的聚光灯样表已经包含了行、列、区域、选定单元格的聚光应用，并且在表格中把这几种应用分别用语句写出在D列，大家实际应用可能并不需要这么多，只需要挑选其中你所需的进行组合就可以了。

szpwiry

另样表中已把行列及九宫判断语句写入单元格，方便大家点击后看到条件格式是如何根据反馈的逻辑值来实现聚光效果，每个判断语句均单独在d列写出，条件格式也是采取的3个公式分别输入，便于大家查看分析。

han-ying123

很好的东西，谢谢分享