王德宝,王德宝讲师,王德宝联系方式,王德宝培训师-【中华讲师网】
51
鲜花排名
0
鲜花数量
扫一扫加我微信
王德宝:Excel有关颜色这点事:按颜色排序/求和/计数等
2016-01-20 20704

我们通过条件格式或手工设置了单元格产生不同的颜色(背景色或前景色),接下来我们希望更进一步的操作或统计,例如:按照一定的颜色顺序排序、相同颜色的单元格累加求和、或者计算某一颜色的单元格有多少个等。

 

关于排序,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

全部评论 (0)

Copyright©2008-2024 版权所有 浙ICP备06026258号-1 浙公网安备 33010802003509号 杭州讲师网络科技有限公司
讲师网 www.jiangshi.org 直接对接10000多名优秀讲师-省时省力省钱
讲师网常年法律顾问:浙江麦迪律师事务所 梁俊景律师 李小平律师