一招破解图标集的相对引用难题
前言
本篇文章由徐长玉同学提供思路，老崔进行了重新整理，并加入了VBA解决方案:
在项目进度报告中,要体现项目的提前和落后,往往需要计算实际与计划的差值,然后利用条件格式中的图标集箭头可以直观的显示项目的提前落后情况.如下图为源数据：
选中D2:D6区域, 依次点击开始-条件格式-图标集-方向,然后选择第一个图标集
选中D2:D6区域区域，然后点击条件格式的管理规则按钮
弹出如下对话框
选中规则后，点击编辑规则
我们将默认的百分比改成数值,把条件改成
当值>0时,显示向上的箭头
当值<=0且>=0时,显示水平箭头
当值<0时,显示向下的箭头
确定,结果如下图所示:
图标集的相对引用
大家有没有发现,不管是数字还是百分比,图标集都是对于选中的这一列数据来比较的,和其它列无关.
如果我不想借助差值列,如何在实际值这一列显示同样的效果?这就用到相对引用的概念,因为C列的每个数都要和B列的相应值比较,来显示提前和落后
但是当我们将条件改为相对引用时,EXCEL却弹出一则不友好的信息:
在用于色阶\数据栏和图标设置的条件格式中不能使用相对引用!
另辟蹊径
用绝对引用的方式来表达相对引用:
我们选中C2单元格,插入图标集,编辑规则,在数值框写入下列公式:
=INDIRECT(ADDRESS(ROW(),COLUMN()-1))
注释:
这个公式没有引用任何单元格，但是却起到了相对引用的作用！
ROW():指当前单元格所在的行
COLUMN()-1: 指当前单元格所在的列减1,相当于当前单元格向左偏移一个单元格
ADDRESS(ROW(),COLUMN()-1):相当于当前单元格所对应的计划值所在的单元格地址
INDIRECT(ADDRESS(ROW(),COLUMN()-1)):间接引用该地址的内容
确定后,如下:
然后,选中C2单元格,双击格式刷,逐个点击C3,C4,C5…….,切忌不要一拉到底,结果如下:
那么逐个格式刷和一起刷有什么不同呢?
我们可以同时选择C2:C6区域,再点击管理规则,可以看到我们刚才为每一个单元格建立了一个规则,而不是整列应用于同一个规则.
如果用格式刷一拉到底,则是整个区域使用同一个规则,所有的数值比较的基准点仍然为第一个计划值,这也是容易出错的地方,如下图所示:
C3:C6使用同一个规则,导致C5单元格的比较对象明显为B3单元格,所以错误的出现水平箭头.
进阶
如果单元格有很多很多,可以考虑VBA,命令如下，本命令基于上述表格:
Sub 图标集()
For i = 2 To 6
Cells(i, 3).Select '选中单元格
Selection.FormatConditions.Delete '删除原来的格式
Selection.FormatConditions.AddIconSetCondition '增加条件格式
With Selection.FormatConditions(1)
.ReverseOrder = False '是否翻转图标次序为否
.ShowIconOnly = False '是否仅显示图标为否
.IconSet = ActiveWorkbook.IconSets(xl3Arrows) '设置图标集为3种箭头格式
End With
With Selection.FormatConditions(1).IconCriteria(2) '设置大于条件
.Type = xlConditionValueNumber '设置类型为数字
.Value = "=$B$" & i '设置应用单元格数值
.Operator = 7
End With
With Selection.FormatConditions(1).IconCriteria(3) '设置等于条件
.Type = xlConditionValueNumber '设置类型为数字
.Value = "=$B$" & i '设置应用单元格数值
.Operator = 5
End With
Next
End Sub
点击运行即可！
知识点
Indirect的用法，可参照：
用绝对引用表示相当引用
=INDIRECT(ADDRESS(ROW(),COLUMN()-1))
双击格式刷可以连续刷刷刷
图标集的应用