Excel 中按颜色求和四种方法介绍

原文标题:《按颜色求和,学会这 4 招,走遍天下都不怕!》

这个世界繁花似锦,多姿多彩,五颜六色!

Excel 表格里的颜色也不例外。

在表格中使用颜色看上去非常醒目,而且直观,增加美感!

但是在表格中用颜色来标注一些单元格之后,如果要对这些加了颜色的单元格来求和(或者求平均,求最大值等等)

就是一件比较棘手和麻烦的事了!

如图:

(这里只是简单的例举了几条数据,以方便讲解。无论数据多少,方法都是类似的!)

如果不懂方法的话,那只能一个单元格一个单元格的加总在一起,比如:

=sum(C2,C4,C6,C8)

或者要么这样:

=C2+C4+C6+C8

要么手动输入单元格地址,要么用鼠标点选。

如果数据很多,不仅效率非常低下,而且还有可能会出错,所以不推建大家使用这种方法!

那么有没有其他的方法,可以快速而且准确的统计出有颜色的单元格中的值呢?

当然有,下面我们来看看 4 种按颜色求和的方法。

效率高,而且不易出错!

辅助列法

推荐指数:★★★★★

难易程度:★☆☆☆☆

适用场景:颜色单一或者不多的情况下

适用版本:所有版本

这种方法不仅仅适用于颜色求和,在很多场合下,都可以将问题或者函数公式简化,从而化繁为简,将不可能变成可能!

❶ 先对 C 列数据,按单元格颜色进行筛选,把有颜色的单元格筛选出来;

❷ 在 D 列添加一个辅助列,然后都写上 1,如下图:

❸ 取消筛选,把公式写在 E1 单元格里面,公式:

=SUMIF(D:D,1,C:C)
(这里可以根据自己需要放在想要的单元格中。)

公式大概的意思是:

对条件区域 D 列,按照条件为数字 1 的单元格,对 C 列符合条件的单元格进行求和。

看上去还是挺简单的吧。

PS:在辅助列输入的内容,大家可以根据情况来录入,方便识别就可以!比如:销售组别 + 颜色等。

查找与定义名称法

推荐指数:★★★★★

难易程度:★★☆☆☆

适用场景:颜色单一或者不多的情况下

适用版本:所有版本

运用两种或者两种以上方法相结合,也是化繁为简一种非常好的思路。

❶ 按【Ctrl+F】打开【查找和替换】对话框,点击【格式】旁边的黑色三角按钮,点击其中的【格式】。

此外有时也可以选择【从单元格选择格式】这个选项,但这两种方法的结果可能不一样。

比如有的单元格即加了颜色又设置了加粗,有的单元格却没有,会导致统计结果不一样,大家可以自行尝试摸索下。

❷ 打开【查找格式】对话框,找到【填充】选项卡下面的颜色点击下。

点选之后,会自动把颜色显示到【预览】处,如下图:

❸ 点击【查找全部】,选中其中一条数据,按【Ctrl+A】全选有颜色的单元格;

然后在【名称框】里面输入一个名称,比如:我的名称 1。

PS:当然在这里也可以定义名称为「绿色」,如果颜色有两种或者以上的话,可以分别定义成实际的颜色名称 + 备注。

❹ 在 E1 单元格写入公式:

=SUM我的名称 1)

Sum 就是求和函数,对「我的名称 1」这个名称代表的多个单元格里面的值求和。

这样结果就出来了!

宏表函数法

推荐指数:★★★★☆

难易程度:★★★☆☆

适用场景:颜色不限

适用版本:所有版本

宏表函数对于很多人来说,可能比较陌生。

大家在工作中接触最多的是工作表函数,工作表函数可以直接在单元格中使用。

而宏表函数必须先定义一个名称,然后就可以像工作表函数一样在单元格中使用了。

❶ 选中有颜色单元格的旁边的 D2 单元格,点击【公式】→【定义名称】打开【新建名称】对话框;

(或者按【Ctrl+F3】打开名称管理器,也可以新建名称。)

在【名称】文本框中输入「我的名称 2」,【引用位置】输入:

=GET.CELL(63,Sheet1!C2)

公式大概意思是:获得单元格的填充颜色的值。

(参数 63 表示返回单元格的填充颜色的值。)

❷ 在 D2 单元格输入公式:

=我的名称 2

并向下填充到最后一个单元格 D9。

❸ 接下来就可以像我们第一种方法一样用 Sumif 来求和了。

当然这里也可以把颜色放在公式旁边,如果颜色有两种或者两种以上的话,可以用下面这个公式:

=SUMIF(D:D我的名称 2,C:C)

如果有颜色增加或者减少的话,可以对原来的宏表函数修改下:

=GET.CELL(63,Sheet1!C2)+NOW()*0

修改之后,如果颜色有变化,增加或者减少颜色的话,直接按【F9】就可以刷新了,不用再重新输入一次公式。

PS:这里必须要按【F9】来进行刷新,否则计算结果可能会出错!因为这个宏表函数不会自动刷新噢!

有些宏表函数可以达到工作表函数无法完成的工作。

对于某些场合下,不会 VBA 的小伙伴们,还是值得学一学的。

VBA 编程法

推荐指数:★★★☆☆

难易程度:★★★★★

适用场景:颜色不限

适用版本:所有版本

这种方法对于大部分人来说,都不会。

因为涉及到了编程,相对来说比较难点。

但是,大部分情况下,我们其实并不需要知道代码怎么编写,只需要会用,会操作,就行。

❶ 按下【Alt+F11】,打开 VBA 编辑界面;

然后在左侧的工程窗口中,右键点击插入一个模块,会生成【模块 1】。

❷ 把代码复制到右边的代码窗口中,就可以了。

❸ 在工作表中,输入公式:

=颜色求和C2:C9,E1)

结果就出来了。

以下是代码,供大家复制使用!

Function 颜色求和rng1 As Range, rng2 As Range)
  Dim r As Range, s As Double
  '请选择你要求和的单元格区域!
  Set rng1 = Intersect(ActiveSheet.UsedRange, rng1)
  For Each r In rng1
    '如果目标单元格与第二参数单元格的填充色相同,就进行累加。
    If r.Interior.Color = rng2.Interior.Color Then
    s = s + r.Value
    End If
  Next
  颜色求和 = s
End Function

我们这里用的是 VBA 中的自定义函数,也可以编写个 Sub 子过程,然后把这个子过程附到一个按钮上面也可以的。

这种方法,如果大家有精力、有兴趣的话,可以学习点录制宏,自己再进行一些简单的修改,就可以完成一些自动化的工作了,省时省力。

以上就是按颜色求和的 4 种主要方法。

总结一下

除了以上 4 种主要的方法之外,实际工作中还可能有以下几种情况,比如:

❶ 是否是隔行(或者隔列)加了颜色,然后对隔行(隔列)进行求和?

❷ 是否是对于高于或者低于某一些数值的数据加了颜色,然后用 Sumif 或者 Sumifs 等函数设置下单条件或者多条件求和?

❸ 是否是针对某个部门或者某些人、某个时间段等加了颜色,然后用相应的函数求和?

……

工作中,可以根据实际情况,找出其中的规律,加以分析判断,并做出选择。

本文来自微信公众号:秋叶 Excel (ID:excel100),作者:明镜在心

Published by

风君子

独自遨游何稽首 揭天掀地慰生平

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注