Excel中如何按单元格颜色求和,这五种牛批的方法,值得学习

点赞再看,养成习惯;文武之道,一张一弛。

微信搜索【亦心Excel】关注这个不一样的自媒体人。

本文 GitHub https://github.com/hugogoos/Excel 已收录,包含Excel系统学习指南系列文章,以及各种Excel资料。

不知道大家有没有遇到过这样的需求,就是根据单元格填充颜色对其数据进行求和处理。今天我在这里和大家分享五种按颜色求和小技巧。

 

1、查找法

查找法主要利用颜色格式查找出全部满足条件的单元格,然后使用Excel自身计算好的结果。

首先按快捷键「Ctrl+F」调出查找和替换对话框。

 

然后点击「选项」,再点击「格式」下拉列表,选择「从单元格选择格式」。

 

此时鼠标会变成白色十字+吸管状,然后吸取我们要求和单元格的颜色。

 

然后点击「查找全部」,这时在查找和替换对话框下方会查出所有满足条件的单元格。然后按快捷键「Ctrl+A」全选满足条件的单元格,如下图,这时在任务栏上即可看到所有黄色单元格值的和。

 

动态演示如下:

 

2、查找+定义名称法

对于上面的查找法还有些许瑕疵:①最后需要自己手动输入结果;②如果单元格内容变了和不会自动跟着变,还需要自己手动操作一次。

为了解决查找法的缺陷,我们可以使用查找+定义名称法。操作方法也很简单,在查找法最后查找出全部黄色单元格以后,按快捷键「Ctrl+F3」,调出名称管理器对话框。

 

点击「新建」调出新建名称对话框。

 

在名称栏里我们可以自定义输入:黄色,然后点击确定按钮,关闭名称管理器对话框。

然后在求和单元格中输入公式:「=SUM(黄色)」。

 

看看动态图:

 

3、筛选法

筛选法主要利用按颜色筛选功能筛选出单元格再使用函数求和。

首先选中数据区域,点击「数据」→「筛选」,然后点击筛选倒三角按钮,选择按颜色筛选,选择黄色。

 

然后在D6单元格中输入公式:「=SUBTOTAL(109,B4:B10)」即可。

 

这里需要解释一下「=SUBTOTAL(109,B4:B10)」公式,首先SUBTOTAL函数是返回列表或数据库中的分类汇总的意思。109就是表示SUM求和的意思,并且会忽略隐藏值即隐藏行的值会被忽略,如果是9则不会忽略隐藏值即隐藏行的值也会被计算在内,这里的隐藏行都是指手动隐藏行,而筛选掉的行始终不会被计算在内,所以这个例子里用「=SUBTOTAL(9,B4:B10)」或「=SUBTOTAL(109,B4:B10)」结果都是一样的。

动态效果如下:

 

4、宏表函数法

宏表函数法主要利用GET.CELL获取单元格背景色值,然后再使用函数计算和。

宏表函数GET.CELL是在早期低版本Excel中使用的函数,其作用是返回引用单元格的信息。它仍可以在高版本的工作表中使用,不过不能直接用在单元格中,而只能通过定义的名称的方式来使用。

首先点击「公式」→「定义名称」调出新建名称对话框,

 

在名称栏自定义输入:颜色;引用位置输入公式:「=GET.CELL(63,宏表函数法!A4)」。其中参数63表示获取单元格的背景颜色。

然后在D4单元格中输入公式:「=颜色」,并填充D4:F7单元格区域。

 

然后在D9单元格中输入公式「=SUMIF(D4:F7,6,A4:C7)」即可。

 

公式「=SUMIF(D4:F7,6,A4:C7)」的函数是计算D4:F7单元格区域值等于6的对应A4:C7单元格区域值之和。

具体演示如下:

 

5、VBA法

VBA的强大相信大家都是知道的,因此也可以VBA也可以实现按颜色求和。具体操作如下。

点击「开发工具」→「Visual Basic」调出VBA编辑窗口,然后右击「Microsoft Excel 对象」下当前工作表,选择「插入」→「模块」。

 

然后在编辑框里输入如下代码:

Function SumColor(color As Range, sumRange As Range) As Double
    Dim icell As Range
    For Each icell In sumRange
        If icell.Interior.ColorIndex = color.Interior.ColorIndex Then
             SumColor = Application.Sum(icell) + SumColor
        End If
    Next icell
End Function

 

点击保存,关闭VBA窗口。

然后在D9单元格输入公式:「=SumColor(C9,B4:E7)」即可,其中第一个参数C9表示要求和的颜色单元格,第二个参数B4:E7表示数据区域。

 

我们来看看整个操作过程:

 

今天的分享到这里就结束了,但是学习的道路才刚刚开始,希望我们可以在学习的道路上不断地前进,坚持不懈。

如果你有感兴趣的功能,可以告诉小编哦,小编会为你写一篇相应的文章。当然是先到先写哈,我会列一个计划表,尽量满足大家的需求,所以如果下一篇不是你要的文章,请不要着急,可能就在下下篇。记得告诉小编你想学习的功能哦。

文章持续更新,可以微信搜索「 亦心Excel 」第一时间阅读,本文 GitHub https://github.com/hugogoos/Excel 已经收录,包含Excel系统学习指南系列文章,欢迎Star。

原文地址:https://www.cnblogs.com/hugogoos/p/14239651.html