Excel 技巧:按颜色(背景、字体、条件格式)统计/求和单元格
在日常任务中,颜色标记是一种流行的方法,可以快速区分和突出显示关键数据。但是,我们如何根据特定颜色(填充颜色、字体颜色、条件格式)来统计或求和单元格数据呢?默认情况下,Excel 并未提供直接按颜色统计或求和的功能。然而,通过一些技巧和间接方法,我们仍然可以实现这一目标。本文将探讨如何按颜色统计或求和数据。
基于背景色统计和求和单元格
例如,如果你有一个数据区域,其中的值填充了不同的背景颜色,如下图所示。要根据特定颜色统计或求和单元格,Excel 并未提供直接按背景色统计或求和单元格的功能。然而,通过一些巧妙的方法和实用技巧,你可以完成此任务。让我们在本节中探索一些有用的方法。
通过用户自定义函数按背景色统计和求和单元格
在这里,我们将向你展示如何创建和使用这样的用户自定义函数来解决此任务。请按照以下步骤操作:
步骤 1:打开 VBA 模块编辑器并复制代码
- 按下 Alt + F11 键打开 Microsoft Visual Basic for Applications 窗口。
- 在打开的窗口中,点击 插入 > 模块 创建一个新的空白模块。
- 然后,将下面的代码复制并粘贴到空白模块中。
VBA 代码:基于背景色统计和求和单元格Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean = False) As Variant 'Updateby Extendoffice Dim rCell As Range Dim lCol As Long Dim vResult As Double lCol = rColor.Interior.ColorIndex vResult = 0 If SUM Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = vResult + rCell.Value End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = vResult + 1 End If Next rCell End If ColorFunction = vResult End Function
步骤 2:创建公式以按背景色统计和求和单元格
粘贴上述代码后,关闭模块窗口,然后应用以下公式:
- 基于特定背景色统计单元格:
将下面提供的公式复制或输入到你想要显示结果的单元格中。然后向下拖动填充柄以获取其他结果。参见截图:
=colorfunction(G2,$B$2:$E$12,FALSE)
注意:在此公式中,G2 是具有你想要匹配的特定背景颜色的参考单元格;$B$2:$E$12 是你想要统计 G2 颜色单元格数量的范围;FALSE 用于统计颜色匹配的单元格。 - 基于特定背景色求和单元格:
将下面提供的公式复制或输入到你想要显示结果的单元格中。然后向下拖动填充柄以获取其他结果。参见截图:
=colorfunction(G2,$B$2:$E$12,TRUE)
注意:在此公式中,G2 是具有你想要匹配的特定背景颜色的参考单元格;$B$2:$E$12 是你想要统计 G2 颜色单元格数量的范围;TRUE 用于求和颜色匹配的单元格。
通过强大功能按背景色统计和求和单元格
对于不熟悉编程的人来说,VBA 可能显得相当复杂。在这里,我们将介绍一个强大的工具 - Kutools for Excel,其按颜色统计功能允许你仅需几次点击即可轻松计算(统计、求和、平均值等)基于背景色的数据。令人印象深刻的是,按颜色统计功能不仅限于背景颜色 – 它还可以根据字体颜色和条件格式进行区分和计算。
下载并安装 Kutools for Excel 后,首先选择你想要基于特定背景颜色统计或求和单元格的数据范围。接下来,导航至 Kutools Plus 并选择按颜色统计。
在按颜色统计对话框中,请指定操作:
- 从颜色方法下拉列表中选择标准格式化;
- 从统计类型下拉列表中指定背景,并且你可以在对话框中预览每种背景颜色的统计结果;
- 最后,点击生成报告将计算结果导出到新的工作簿。
结果:
现在,你将获得包含统计数据的新工作簿。参见截图:
- 该 按颜色统计 功能还支持基于标准字体颜色、背景或条件格式的字体颜色以及填充和条件格式颜色组合来统计和求和单元格。
- 对该功能感兴趣,请点击下载以获取 30 天免费试用。
通过筛选和 SUBTOTAL 函数按背景色统计和求和单元格
假设我们有如下截图所示的水果销售表,并且我们将统计或求和 金额 列中的彩色单元格。
步骤 1:应用 SUBTOTAL 函数
选择空白单元格以输入 SUBTOTAL 函数。
- 要统计所有具有相同背景颜色的单元格,请输入以下公式:
=SUBTOTAL(102, F2:F16)
- 要对所有具有相同背景颜色的单元格求和,请输入以下公式:
=SUBTOTAL(109, F2:F16)
- 注意:在上述公式中,102 表示在过滤列表中统计数值,同时排除隐藏单元格;109 表示在过滤列表中求和数值,同时排除隐藏单元格;F2:F16 是将要进行统计或求和的范围。
步骤 2:基于特定颜色筛选单元格
- 选择表格的标题栏,并点击 数据 > 筛选。参见截图:
- 点击 筛选 图标
在 金额 列的标题单元格中,并点击 按颜色筛选 以及你将依次统计的指定颜色。参见截图:
结果:
筛选后,SUBTOTAL 公式会自动统计和求和 金额 列中的彩色单元格。参见截图:
基于字体颜色统计和求和单元格
想在 Excel 中根据字体颜色统计或求和单元格吗?假设你有如给定截图所示的数据,单元格中包含红色、蓝色、橙色和黑色文本。默认情况下,Excel 并不容易做到这一点。但别担心!在本节中,我们将向你展示一些简单的技巧来实现这一目标。
通过用户自定义函数按字体颜色统计和求和单元格
要统计和求和具有特定字体颜色的单元格,以下用户自定义函数可能帮助你解决此任务。请按照以下步骤操作:
步骤 1:打开 VBA 模块编辑器并复制代码
- 按下 Alt + F11 键打开 Microsoft Visual Basic for Applications 窗口。
- 在打开的窗口中,点击 插入 > 模块 创建一个新的空白模块。
- 然后,将下面的代码复制并粘贴到空白模块中。
VBA 代码:基于字体颜色统计和求和单元格Function ProcessByFontColor(pRange1 As Range, pRange2 As Range, FunctionType As String) As Double 'Updateby Extendoffice Application.Volatile Dim rng As Range Dim xTotal As Double Dim xCount As Double xTotal = 0 xCount = 0 For Each rng In pRange1 If rng.Font.Color = pRange2.Font.Color Then If UCase(FunctionType) = "SUM" Then xTotal = xTotal + rng.Value ElseIf UCase(FunctionType) = "COUNT" Then xCount = xCount + 1 End If End If Next If UCase(FunctionType) = "SUM" Then ProcessByFontColor = xTotal ElseIf UCase(FunctionType) = "COUNT" Then ProcessByFontColor = xCount Else ProcessByFontColor = CVErr(xlErrValue) End If End Function
步骤 2:创建公式以按字体颜色统计和求和单元格
粘贴上述代码后,关闭模块窗口,然后应用以下公式:
- 基于特定字体颜色统计单元格:
将下面提供的公式复制或输入到你想要显示结果的单元格中。然后向下拖动填充柄以获取其他结果。参见截图:
=ProcessByFontColor($B$2:$E$12,G2, "COUNT")
注意:在此公式中,G2 是具有你想要匹配的特定字体颜色的参考单元格;$B$2:$E$12 是你想要统计 G2 颜色单元格数量的范围。 - 基于特定字体颜色求和单元格:
将下面提供的公式复制或输入到你想要显示结果的单元格中。然后向下拖动填充柄以获取其他结果。参见截图:
=ProcessByFontColor($B$2:$E$12,G2, "SUM")
注意:在此公式中,G2 是具有你想要匹配的特定字体颜色的参考单元格;$B$2:$E$12 是你想要统计 G2 颜色单元格数量的范围。
通过简单功能按字体颜色统计和求和单元格
希望在 Excel 中毫不费力地根据字体颜色统计或求和单元格值吗?深入了解 Kutools for Excel 的 按颜色统计 功能!借助这个智能工具,按特定字体颜色统计和求和单元格变得轻而易举。发现 Kutools 如何改变你的 Excel 体验。
下载并安装 Kutools for Excel 后,首先选择你想要基于特定字体颜色统计或求和单元格的数据范围。然后,点击 Kutools Plus > 按颜色统计 打开 按颜色统计 对话框。
在按颜色统计对话框中,请指定操作:
- 从颜色方法下拉列表中选择标准格式化;
- 从统计类型下拉列表中指定字体,并且你可以在对话框中预览每种字体颜色的统计结果;
- 最后,点击生成报告将计算结果导出到新的工作簿。
结果:
现在,你有一个新工作簿显示基于字体颜色的详细统计数据。参见截图:
基于条件格式颜色统计和求和单元格
在 Excel 中,你通常使用条件格式为满足某些条件的单元格应用特定颜色,使数据可视化直观。但是,如果需要统计或求和那些特殊格式化的单元格怎么办?虽然 Excel 并未提供直接方法,这里有一些绕过此限制的方法。
通过 VBA 代码统计和求和条件格式化单元格
在 Excel 中统计和求和条件格式化单元格并不容易使用内置函数。然而,你可以使用 VBA 代码完成此任务。让我们看看如何使用 VBA 来实现:
步骤 1:打开 VBA 模块编辑器并复制代码
- 按下 Alt + F11 键打开 Microsoft Visual Basic for Applications 窗口。
- 在打开的窗口中,点击 插入 > 模块 创建一个新的空白模块。
- 然后,将下面的代码复制并粘贴到空白模块中。
VBA 代码:基于条件格式颜色统计和求和单元格Sub SumCountByConditionalFormat() 'Updateby Extendoffice Dim sampleColor As Range Dim selectedRange As Range Dim cell As Range Dim countByColor As Long Dim sumByColor As Double Dim refColor As Long Set selectedRange = Application.InputBox("Select a range to evaluate:", _ "Kutools for Excel", _ Type:=8) If selectedRange Is Nothing Then Exit Sub Set sampleColor = Application.InputBox("Select a conditional formatting color:", _ "Kutools for Excel", _ Type:=8) If Not sampleColor Is Nothing Then refColor = sampleColor.Cells(1, 1).DisplayFormat.Interior.color For Each cell In selectedRange If cell.DisplayFormat.Interior.color = refColor Then countByColor = countByColor + 1 sumByColor = sumByColor + cell.Value End If Next cell MsgBox "Count: " & countByColor & vbCrLf & _ "Sum: " & sumByColor, _ vbInformation, "Results based on Conditional Format Color" End If End Sub
步骤 2:执行此 VBA 代码
- 粘贴代码后,按下 F5 键运行此代码,将出现一个提示框,请选择你想要基于条件格式统计和求和单元格的数据范围。然后,点击 确定。参见截图:
- 在另一个提示框中,选择你想要统计和求和的特定条件格式颜色,并点击 确定 按钮,参见截图:
结果:
现在,包括指定条件格式颜色单元格的数量和总和的结果将显示在弹出框中。参见截图:
通过智能功能统计和求和条件格式化单元格
如果你正在寻找其他快速简便的方法来统计和求和条件格式化单元格,Kutools for Excel 是你的首选解决方案。其按颜色统计功能只需几次点击即可解决此任务。深入探索 Kutools 能为你的工作流程带来的效率和精确性。
下载并安装 Kutools for Excel 后,首先选择你想要基于特定条件格式颜色统计或求和单元格的数据范围。然后,点击 Kutools Plus > 按颜色统计 打开 按颜色统计 对话框。
在按颜色统计对话框中,请指定操作:
- 从颜色方法下拉列表中选择条件格式 ;
- 从统计类型下拉列表中指定背景,并且你可以在对话框中预览每种条件格式颜色的统计结果;
- 最后,点击生成报告将计算结果导出到新的工作簿。
结果:
现在,你有一个新工作簿显示基于条件格式颜色的详细统计数据。参见截图:
相关文章:
- 如果字体颜色是红色则返回特定文本
- 如何在另一个单元格的字体颜色为红色时返回特定文本,如下截图所示?在本文中,我将介绍一些基于 Excel 中红色字体文本进行操作的小技巧。
- 按多种颜色筛选数据
- 通常,在 Excel 中,你可以快速筛选只有一种颜色的行,但你是否考虑过同时筛选具有多种颜色的行?本文将为你提供快速解决此问题的技巧。
- 为下拉列表添加颜色
- 在 Excel 中,创建下拉列表可以帮助你很多,有时你需要根据相应的选择为下拉列表值着色。例如,我创建了一个水果名称的下拉列表,当我选择苹果时,我希望单元格自动变为红色,当我选择橙子时,单元格可以变为橙色。
- 为合并单元格交替着色
- 在大数据中交替格式化行以不同颜色对我们扫描数据非常有帮助,但有时你的数据中可能会有一些合并单元格。为了像下面截图所示那样为合并单元格交替高亮显示行,如何在 Excel 中解决这个问题?
最佳 Office 办公效率工具
🤖 | Kutools AI 助手:基于智能执行,彻底革新数据分析 |生成代码|创建自定义公式|分析数据并生成图表|调用 Kutools Functions… |
热门功能:查找、选中项的背景色或标记重复项|删除空行|合并列或单元格且不丢失数据|四舍五入(无公式)... | |
高级 LOOKUP:多条件查找 (VLookup)|多值查找 (VLookup)|多表查找 (VLookup Across Multiple Sheets)|模糊查找 (Fuzzy Lookup)... | |
高级下拉列表:快速创建下拉列表|依赖型下拉列表|多选下拉列表... | |
列管理器:添加指定数量的列 |移动列 |切换隐藏列的可见状态| 比较区域及列... | |
特色功能:网格聚焦|设计视图|增强编辑栏|工作簿 & 工作表管理器|资源库(自动文本)|日期提取|合并数据|加密/解密单元格|按列表发送电子邮件|超级筛选|特殊筛选(筛选粗体/倾斜/删除线等)... | |
热门15 大工具集:12 款文本工具(添加文本、删除特定字符等)|50+ 种图表 类型(甘特图等)|40+ 实用公式(基于生日计算年龄等)|19 款插入工具(插入二维码、按路径插入图片等)|12 种转换工具(小写金额转大写、汇率转换等)|7 款合并与分割工具(高级合并行、分割单元格等)|...更多精彩等你发现 |
用 Kutools for Excel 加速你的 Excel 技能,体验前所未有的高效办公。 Kutools for Excel 提供300 多项高级功能,助您提升效率,节省大量时间。点击此处,获取你最需要的功能...
Office Tab 为 Office 带来标签式界面,让你的工作更加轻松
- 在 Word、Excel、PowerPoint 启用标签式编辑和阅读
- 在同一窗口的新标签中打开和创建多个文档,无需新建窗口。
- 办公效率提升50%,每天帮你减少上百次鼠标点击!