我们通过条件格式或手工设置了单元格产生不同的颜色(背景色或前景色),接下来我们希望更进一步的操作或统计,例如:按照一定的颜色顺序排序、相同颜色的单元格累加求和、或者计算某一颜色的单元格有多少个等。
关于排序,Excel2007版以后内置了按颜色排序的功能,这里不多说。下面探讨如何根据颜色求和或计数。
解决思路:先得到颜色值,再利用相应的函数(SUMIFS,COUNTIFS)进行统计。
一、根据颜色取颜色值
通常有两个解决办法:利用GET.CELL函数,或自定义函数法。建议采用增加辅助列的方法,先计算出颜色代码,再据此求和或计数。
1、GET.CELL 取单元格信息的宏表函数,关于这个函数的详细参数大家百度吧。
需要注意的是这个函数不能在工作表单元格中直接使用!那怎么用呢?在名称里。你要定义名称:选中B2单元格,公式->定义名称->起个名额如MyColor->引用位置里面输入 =get.cell(38,Sheet1!A2),然后确定。
然后在B2输入=MyColor,则就会返回A列同行单元格的背景色的颜色代码,往下拉公式填充。
2、自定义函数法。进入VBA窗口,插入模块,输入以下代码 :
'返回单元格的背景颜色代码
Function GetColor(rng As Range) As Double
GetColor = rng.Interior.ColorIndex
End Function
然后就可以在单元格里像其它函数一样使用这个GetColor函数了。
举一个实例,如下图,通过上述两个方法可以分别得到颜色代码。
二、用函数统计
求得颜色值之后,要想统计某个颜色的单元格之和或个数,利用sumifs函数和countifs函数,对颜色代码进行计算即可。上图中B20、C20、D20的公式分别为:
取颜色值公式:
B20 =GetColor(A20)
按颜色求和公式:
C20 =SUMIFS($C$2:$C$16,$C$2:$C$16,B20)
按颜色计数公式:
D20 =COUNTIFS($C$2:$C$16,B20)
至此,这类问题基本都能迎刃而解。
注意:细心的朋友可能会发现,对于同一个单元格,两个函数算出的颜色值可能不一样的。导致这个问题的原因可能是两个函数能处理的颜色的多少是不同的,具体还没仔细研究。
要很好的避免这个问题,推荐使用第二种自定义函数的方法来求颜色值。
德宝老师博客原文:https://blog.debao.name/excel_color.html?j=1