Excel如何按颜色求和:整理了4 种方法,你会用哪些?

编按:说实话,小窝是第一次做颜色求和,因为我几乎都用条件格式标识数据,颜色求和就是伪需求。但是问了身边朋友,以及看了一些学员的提问,原来真存在按颜色求和的。在此,整理了4种方法。

颜色求和实际是个伪命题!

不信?

那就往下看!

1、直接用SUM或者SUMIF求和

分别求绿色与粉色单元格之和。

绿色单元格之和:

=SUM((B3:G9>500)*B3:G9)

粉色单元格之和:

=SUM((B3:G9<250)*B3:G9)

“对吗?”

你肯定有疑惑:感觉“颜色”条件都没有使用就完成了求和,这结果对吗?

结果是否对,继续看就知道了。

2、查找法求和

来到Sheet2中,同样分别求绿色和粉色单元格之和。

步骤:

(1)按CTRL+F打开“查找和替换”对话框

(2)单击“选项”—“格式”—“从单元格格式”,然后吸取绿色单元格。

(3)单击“查找全部”。

(4)按CTRL+A全选,然后点“关闭”。

(5)在名称框中输入“绿色”。

(6)同样的操作选中粉色单元格,在名称框中输入“粉色”。

(7)输入公式=SUM(绿色)或=SUM(粉色)完成求和。

用到颜色条件了,并且求出来的和与前方是一样的!

回到Sheet1中。

请用查找法做颜色求和。

请一定试试!!

试了后你会发现无法用查找颜色的方法求和,或者说其结果是错误的。

咋回事呢?

我们在表中用颜色标识不同的数据都是基于具体规则进行的,譬如所有大于500的填充绿色,小于250的填充粉色。Excel的条件格式可以帮我们自动完成标识。

下图就是Sheet1中的条件格式。

它包含两条规则:<250填充粉色,>500填充绿色。

知道了颜色出现的规则,那么颜色求和也就是按条件规则求和而已,与具体的颜色无关。

如此处,绿色之和=SUM((B3:G9>500)*B3:G9),粉色之和=SUM((B3:G9<250)*B3:G9)。

用条件格式显示出来的单元格填色并不等于单元格实质填充了颜色。因此,你无法用查找颜色的方式来求和;无法用下面将要介绍的宏表函数,以及更牛的VBA自定义函数完成颜色求和。

查找法、宏表函数法、VBA自定义格式法,它们都要利用具体填色信息,只能求——

逐个手动填色的数字的和!

颜色标识数字,肯定用条件格式;

用条件格式,就无法通过识别颜色来求和;

能按颜色求和的都是手动填色的,

可谁会自己手欠找麻烦呢?

因此,

按颜色求和就是伪命题!

或许你说,“我就是手动标色的 —— 啊,不,是那个安排做事的人随手标的,然后要求我求和”。

太坏了!

看来还得做颜色求和。下面是其他的方法。

3、宏表函数法

到Sheet3。提供两种宏表函数法:一个是公式简单的,但有辅助列(行);一个是不用辅助列(行)的,但是公式复杂。

1)简单公式

步骤:

(1)单击“公式”—“定义名称”,输入名称“color”(名称须是唯一的,不能与已有名称相同)。引用位置处输入公式“=get.cell(63,sheet3!b3)”。

Get.cell()是宏表函数,用于获取单元格的某类信息。具体信息类型由数字指定,数字范围1~66。其中,63代表单元格背景颜色。

(2)在B11输入公式“=color”并右拉下拉获取单元格的颜色值。

可以看到当前绿色颜色值36,粉色颜色值40。

(3)写公式完成颜色求和。

输入公式“=SUMIF($B$11:$G$17,A19,$B$3:$G$9)”并下拉即可。

能去掉辅助行或列吗?

可以!只不过定义名称中的公式就复杂了。

2)复杂公式

步骤:

(1)重新定义名称。

定义名称,新创建一个名称“color_2”,然后在引用位置输入如下公式:

=SUM((GET.CELL(63,INDIRECT("r"&ROW(Sheet3!$B$3:$G$9)&"c"&COLUMN(Sheet3!$B$3:$G$9),0))=GET.CELL(63,Sheet3!A19))*Sheet3!$B$3:$G$9)

(2)在B19处输入公式“=color_2”下拉即可。

公式说明:

①INDIRECT("r"&ROW(Sheet3!$B$3:$G$9)&"c"&COLUMN(Sheet3!$B$3:$G$9),0),用INDIRECT分别引用B3:G9中的每个单元格。之所以要分别引用,而不是直接写成GET.CELL(63, Sheet3!$B$3:$G$9),是因为GET.CELL函数不支持数据区域。

②GET.CELL(63, ①)得到每个单元格的颜色值。

余下的部分不说你也明白。

4、“很牛很牛”的自定义函数法

到Sheet4。

在B13中输入公式“=SumColor($B$3:$G$9,A13)”下拉即可。

非常简单,很灵活,可以在当前文件的任何表格中使用。

SUMCOLOR是自定义函数,第一参数选择要求和的区域,第二参数选择颜色条件单元格。

这个自定义函数怎么来的呢?

按ALT+F11打开VBA编辑器。

(1)单击“插入”—“模块”命令。

(2)在插入的模块中输入如下代码(可以复制此处代码进行粘贴。能实现颜色求和功能的代码有多种,下方只是相对简单的一种。)

Function SumColor(sum_range As Range, ref_rang As Range)

Dim x As Range

For Each x In sum_range

If x.Interior.ColorIndex = ref_rang.Interior.ColorIndex Then

SumColor = Application.Sum(x) + SumColor

End If

Next x

End Function

(3)返回工作表即可用函数SUMCOLOR进行求和了。

附上代码解析:

注意:使用了宏表函数,以及VBA自定义函数后,文件需要保存为支持宏的xlsm格式。

小结

1.如果是利用条件格式赋予单元格颜色的,(只能)直接用规则进行条件求和,与颜色无关。

2.如果真是手动为单元格填充颜色的,那查找法、宏表函数法、自定义函数法都可以。

做Excel高手,快速提升工作效率,部落窝教育Excel精品好课任你选择!

学习交流请加微信hclhclsc进群领取资料

用SUM函数条件求和比SUMIF还方便

SUMIF函数用法集

条件格式效果错误的原因

INDIRECT函数的R1C1样式用法

版权申明:

本文作者小窝;部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。