张同天
这个问题,其实不复杂,很多人之所以不会操作,主要只是不知道如何取得单元格背景颜色的值而已,如果取得这个数值,那就可各种数据统计了,操作方法如下,简单2步操作即可:
第一步,新建一个名称管理器,用来转换背景颜色
方法:在Excel中,依次点“公式”、“名称管理器”;
在管理器中,新建一个“名称”:“color”。
在“引用位置”处输入字符串:“=GET.CELL(63,Sheet1!G2)+RAND()*0”。
第二步,获得填充颜色数值
增加一个辅助列,在数值处输入“=color”,如下图,这样即可获得背景颜色的数值。
有了这个数值,想怎么统计分析,就看你喜欢了~
思维导图PPT
比如有这样的一张成绩表,现需要求出所有黄色单元格数据之和。
借助“高级查找”和“定义名称”进行统计
首先,按Ctrl+F键调出【查找和替换】对话框,点击【选项】调出高级选项,然后选择【格式】【从单元格选择格式】,然后点击数据区域内任何一个黄色单元格。
然后,点击【查找全部】按钮,全选所有查找结果,这样就选中了所有的黄色区域。
然后,在名称栏输入“黄色”并确定,对选区进行命名。
最后,在I2单元格输入公式=SUM(黄色)即可统计所有黄色单元格数据之和。
使用VBA进行统计
在【开发工具】菜单找到【Visual Basic】,打开VBA代码编辑窗口。新建一个模块,并输入如下代码,然后按F5键运行代码就可以在I2单元格统计出所有黄色单元格数据之和。
代码中用For Each循环,统计A1:G9区域背景色为RGB(255,255,0)即黄色的单元格值之和,并将这个值 赋值给单元格I2单元格!
师说新语
关于根据颜色求和,有两种方案,
一种是VBA自定义函数,计算颜色求和
一种是使用
get.cell
宏表函数,计算颜色,求和VBA自定义函数
颜色函数,要自行定义:
背景颜色函数:
Public Function BackColor(ce As Range)
BackColor = ce.Interior.Color
End Function
文本颜色函数:
Public Function TextColor(ce As Range)
TextColor = ce.Font.Color
End Function
函数的功能是计算出颜色的代码,然后根据代码列,用SUMIFS求和
get.cell宏表函数
首先要启用宏,然后再自定义名称,同样也是计算出颜色的代码,然后用SUMIFS函数根据颜色代码来求和。
以上两种方法都不能计算条件格式产生的颜色,
条件格式,顾名思义,由条件产生的格式颜色,那就可以根据条件求和,不需要获取颜色代码。
EXCEL数据处理与分析
已经有大神分享了使用vba以及宏表函数,我再来分享一种更加简单,但是使用起来稍显笨拙的方法,但是关键是容易操作,
首先我们按Ctrl+F调出查找窗口,然后点击选项找到格式,点击格式,选择从单元格选择格式,如下图所示
当选择后鼠标会变成一个类似吸管的工具,然后点击想要统计颜色的单元格,在预览中会发生改变,比如在这里我们统计红色单元格的个数,当选择后预览就会变红
然后我们点击全部查找,在窗口的左下角会显示查找到的单元格个数,就是我们要统计的个数
使用这种方法简单,但是需要手动输入结果,比较麻烦,数据少还行,如果数据多的话,还是建议使用宏表函数或者vba
我是excel从零到一,关注我持续分享更多excel技巧
Excel从零到一
我是想釜底抽薪:尽量避免手动标记乱七八糟的颜色然后来求和。如果手头已有这类表格,先整理后再求和。
当然,很多时候我们无法改变别人,那就没辙了,老老实实手算或用vba吧。