今天继续学习通过单元格填充颜色来进行数据的统计和计算。

关于如何统计带颜色单元格的个数,我们在上个教文进行了较为详细的介绍,我们可以观看《Excel表格怎么统计带颜色单元格的个数?用公式就可以计算出来》。

而以单元格颜色为条件进行求和,是在此文基础上更进一步的学习。

我们看一下表格数据图:

excel有颜色的单元格求和(Excel表格以单元格颜色为条件进行求和)(1)

在表格中数量列包含了多种填充颜色,有蓝色、绿色和黄色,现在要计算各颜色的总数量,效果如下图:

excel有颜色的单元格求和(Excel表格以单元格颜色为条件进行求和)(2)

下面进入操作步骤。

首先添加一个辅助列,如下图中“颜色值”的辅助列,目的是将C列单元格的填充颜色转换为数值。

excel有颜色的单元格求和(Excel表格以单元格颜色为条件进行求和)(3)

而如何转换,我们已经有了经验,便是通过定义名称间接地利用宏表函数,来完成单元格指定格式的引用。

我们点击公式工具栏下的“定义名称”,然后在弹出的设置框内输入名称为“颜色”,引用位置为“=GET.CELL(63,横排文字转换为竖排!$I11)

excel有颜色的单元格求和(Excel表格以单元格颜色为条件进行求和)(4)

这里引用位置输入的是一个宏表函数公式,get.cell是一个宏表函数,通过不同的参数值来返回指定的单元格格式,这里63表示的是返回单元格的背景颜色,即填充颜色。

然后我们继续设置定义名称,再次设置一个名称为“颜色值”的宏表函数公式。

excel有颜色的单元格求和(Excel表格以单元格颜色为条件进行求和)(5)

这个定义名称的公式是为了进行辅助列颜色值的引用,如上图中,公式编辑栏显示的就是一个定义名称的公式。

关于定义名称的含义和使用方法,我们有介绍过,但还不够详细,在作者的后续专栏里,会专门地通过不同的示例来讲解它的详细用法。

回到表格中,我们填充颜色值列的公式,得出了C列数量对应的单元格颜色值。

此时,只需使用基本的条件求和函数公式,如下图所示,写入公式:=SUMIF($G$3:$G$18,颜色,$C$3:$C$18)

excel有颜色的单元格求和(Excel表格以单元格颜色为条件进行求和)(6)

SUMIF函数,是单条件求和公式,但它其实也可以作多条件求和的设置。在公式中,第2参数是一个文本“颜色”,但它没有使用双引号引用,因为这个“颜色”不是普通文本,而是之前我们设置的一个定义名称的宏表函数公式。

所以它所代表的结果其实是I列三个示例单元格的颜色值,它的计算结果与定义名称“颜色值”是相同逻辑的。

辅助列“颜色值”作为sumif函数的第1参数,即条件区域,然后定义名称“颜色”作为第2参数的条件,第3参数则是数据列C列的数量求和区域。

最后运算公式,得到了指定颜色的单元格求和。

excel是一个数据处理工具,拥有非常多的内置功能和应用,包括函数在内,我们还有许多的知识没有接触和掌握到的,作者会随童鞋们一道,一起学习更多的excel技能,来帮助提高日常工作效率。

所以欢迎关注作者,我们下期再见!

往期回顾:


Excel表格进阶小知识:通配符的几大用法

Excel如何计算应收账款的账龄和账龄等级

Excel表格实用功能查找和选择的基础介绍

,