Excel 提示:按颜色(背景、字体、条件格式)对单元格进行计数/求和
在日常任务中,颜色标记是快速区分和突出显示关键数据的流行方法。 但是,我们如何根据特定颜色(填充颜色、字体颜色、条件格式)对单元格数据进行计数或求和? 默认情况下,Excel 不提供按颜色进行计数或求和的直接功能。 尽管如此,通过一些技巧和间接方法,我们仍然可以实现这一目标。 本文将探讨如何按颜色对数据进行计数或求和。
根据背景色对单元格进行计数和求和
例如,如果您有一系列数据,其中的值填充有不同的背景颜色,如下面的屏幕截图所示。 要根据特定颜色对单元格进行计数或求和,Excel 不提供根据背景颜色对单元格进行计数或求和的直接功能。 然而,只要有一点聪明才智和一些方便的技术,您就可以完成这项任务。 让我们在本节中探索一些有用的方法。
使用用户定义的函数按背景颜色对单元格进行计数和求和
在这里,我们将向您展示如何创建和使用这样的用户定义函数来解决 Excel 中的此任务。 请按照以下步骤进行:
第 1 步:打开 VBA 模块编辑器并复制代码
- 媒体 Alt + F11键 键打开 Microsoft Visual Basic应用程序 窗口。
- 在打开的窗口中,单击 插页 > 模块 创建一个新的空白模块。
- 然后,将以下代码复制并粘贴到空白模块中。
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 可能显得相当复杂。 在这里,我们将介绍一个强大的工具—— Excel的Kutool,它的 按颜色计数 该功能使您只需点击几下即可根据背景颜色轻松计算(计数、总和、平均值等)。 令人印象深刻的是, 按颜色计数 功能不仅仅是背景颜色——它还可以根据字体颜色和条件格式进行区分和计算。
后 下载并安装 Kutools for Excel,首先,选择要根据特定背景颜色对单元格进行计数或求和的数据范围。 接下来,导航至 Kutools 加 并选择 按颜色计数.
在 按颜色计数 对话框中,请指定操作:
- 选择 标准格式 来自 上色方式 下拉列表;
- 指定 背景 来自 计数类型 下拉列表,可以在对话框中预览每种背景颜色的统计结果;
- 最后点击 生成报告 将计算结果导出到新工作簿。
结果:
现在,您将获得一个包含统计信息的新工作簿。 看截图:
- 按颜色计数 该功能还支持基于标准字体颜色、背景或条件格式的字体颜色以及填充和条件格式颜色的组合对单元格进行计数和求和。
- 对此功能感兴趣,请 点击下载即可免费试用 30 天.
使用过滤器和小计功能按背景颜色对单元格进行计数和求和
假设我们有一个水果销售表,如下图所示,我们将对表中的彩色单元格进行计数或求和 金额 列。
步骤 1:应用 SUBTOTAL 函数
选择空白单元格进入小计功能。
- 要对具有相同背景颜色的所有单元格进行计数,请输入公式:
=SUBTOTAL(102, F2:F16)
- 要将所有具有相同背景颜色的单元格相加,请输入公式;
=SUBTOTAL(109, F2:F16)
- 备注: 在上面的公式中, 102 表示对过滤列表中的数值进行计数,同时排除隐藏单元格; 109 表示对过滤列表中不包括隐藏单元格的值进行求和; F2:F16 是计算计数或总和的范围。
第 2 步:根据特定颜色过滤单元格
- 选择表格标题,然后单击 时间 > 筛选器。 看截图:
- 点击 筛选器 图标 在标题单元格中 金额 列,然后单击 通过彩色滤光片 以及您将依次计算的指定颜色。 看截图:
结果:
过滤后,SUBTOTAL 公式会自动对表格中的彩色单元格进行计数和求和 金额 柱。 看截图:
根据字体颜色对单元格进行计数和求和
想要根据 Excel 中的字体颜色对单元格进行计数或求和吗? 假设您有数据,如给定的屏幕截图所示,其中单元格包含红色、蓝色、橙色和黑色文本。 默认情况下,Excel 并不容易做到这一点。 但别担心! 在本节中,我们将向您展示一些简单的技巧来做到这一点。
使用用户定义函数根据字体颜色对单元格进行计数和求和
要对具有特定字体颜色的单元格进行计数和求和,以下用户定义函数可以帮助您解决此任务。 请按照以下步骤进行:
第 1 步:打开 VBA 模块编辑器并复制代码
- 媒体 Alt + F11键 键打开 Microsoft Visual Basic应用程序 窗口。
- 在打开的窗口中,单击 插页 > 模块 创建一个新的空白模块。
- 然后,将以下代码复制并粘贴到空白模块中。
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's 按颜色计数 特征! 有了这个智能工具,按特定字体颜色对单元格进行计数和求和变得轻而易举。 了解如何 库工具 可以改变您的 Excel 体验。
后 下载并安装 Kutools for Excel,首先,根据特定字体颜色选择要计数或求和单元格的数据范围。 然后,单击 Kutools 加 > 按颜色计数 打开 按颜色计数 对话框。
在 按颜色计数 对话框中,请指定操作:
- 选择 标准格式 来自 上色方式 下拉列表;
- 指定 字体 来自 计数类型 下拉列表,可以在对话框中预览每种字体颜色的统计结果;
- 最后点击 生成报告 将计算结果导出到新工作簿。
结果:
现在,您有一个新工作簿,显示基于字体颜色的详细统计信息。 看截图:
根据条件格式颜色对单元格进行计数和求和
在 Excel 中,您通常可以使用条件格式将特定颜色应用于满足特定条件的单元格,使数据可视化直观。 但是,如果您需要对那些特殊格式的单元格进行计数或求和怎么办? 虽然 Excel 没有为此提供直接的方法,但可以通过以下方法绕过此限制。
使用 VBA 代码对条件格式的单元格进行计数和求和
使用内置函数对 Excel 中的条件格式单元格进行计数和求和并不简单。 但是,您可以使用 VBA 代码完成此任务。 让我们看一下如何使用 VBA 来实现此目的:
第 1 步:打开 VBA 模块编辑器并复制代码
- 媒体 Alt + F11键 键打开 Microsoft Visual Basic应用程序 窗口。
- 在打开的窗口中,单击 插页 > 模块 创建一个新的空白模块。
- 然后,将以下代码复制并粘贴到空白模块中。
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 键运行此代码,会出现提示框,请根据条件格式选择要对单元格进行计数和求和的数据范围。 然后,单击 OK,请参见屏幕截图:
- 在另一个提示框中,选择要计数和求和的特定条件格式颜色,然后单击 OK 按钮,请参见屏幕截图:
结果:
现在,结果(包括具有指定条件格式颜色的单元格的计数和总和)将显示在弹出框中。 看截图:
使用智能功能对条件格式的单元格进行计数和求和
如果您正在寻找其他快速简便的方法来对条件格式的单元格进行计数和求和, Kutools for Excel 是您的首选解决方案。 它是 按颜色计数 只需点击几下即可解决此任务。 深入了解 Kutools 可以为您的工作流程带来的效率和精度。
后 下载并安装 Kutools for Excel,首先,根据特定的条件格式颜色选择要对单元格进行计数或求和的数据范围。 然后,单击 Kutools 加 > 按颜色计数 打开 按颜色计数 对话框。
在 按颜色计数 对话框中,请指定操作:
- 选择 条件格式 来自 上色方式 下拉列表;
- 指定 背景 来自 计数类型 下拉列表,可以在对话框中预览每种条件格式颜色的统计结果;
- 最后点击 生成报告 将计算结果导出到新工作簿。
结果:
现在,您有一个新工作簿,显示基于条件格式颜色的详细统计信息。 看截图:
相关文章:
- 如果字体颜色为红色则返回特定文本
- 如果字体颜色在另一个单元格中为红色,如下图所示,您如何返回特定文本? 在本文中,我将介绍一些基于 Excel 中的红色字体文本进行一些操作的技巧。
- 按多种颜色过滤数据
- 通常,在Excel中,您可以快速仅过滤一种颜色的行,但是,您是否曾经考虑过同时过滤多种颜色的行? 本文,我将为您介绍解决此问题的快速技巧。
- 添加颜色到下拉列表
- 在 Excel 中,创建下拉列表可以为您提供很多帮助,有时,您需要根据相应的选择对下拉列表值进行颜色编码。 例如,我创建了一个水果名称的下拉列表,当我选择苹果时,我需要将单元格自动着色为红色,当我选择橙色时,单元格可以自动着色为橙色。
- 为合并单元格的交替行着色
- 在大数据中用不同颜色格式化交替行对我们扫描数据很有帮助,但有时,您的数据中可能会有一些合并的单元格。 要使用不同颜色交替突出显示合并单元格的行,如下图所示,您如何在 Excel 中解决此问题?
最佳办公生产力工具
🤖 | Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行 | 生成代码 | 创建自定义公式 | 分析数据并生成图表 | 调用 Kutools 函数... |
热门特色: 查找、突出显示或识别重复项 | 删除空白行 | 合并列或单元格而不丢失数据 | 不使用公式进行四舍五入 ... | |
超级查询: 多条件VLookup | 多值VLookup | 跨多个工作表的 VLookup | 模糊查询 .... | |
高级下拉列表: 快速创建下拉列表 | 依赖下拉列表 | 多选下拉列表 .... | |
列管理器: 添加特定数量的列 | 移动列 | 切换隐藏列的可见性状态 | 比较范围和列 ... | |
特色功能: 网格焦点 | 设计图 | 大方程式酒吧 | 工作簿和工作表管理器 | 资源库 (自动文本) | 日期选择器 | 合并工作表 | 加密/解密单元格 | 按列表发送电子邮件 | 超级筛选 | 特殊过滤器 (过滤粗体/斜体/删除线...)... | |
前 15 个工具集: 12 文本 工具 (添加文本, 删除字符,...) | 50+ 图表 类型 (甘特图,...) | 40+ 实用 公式 (根据生日计算年龄,...) | 19 插入 工具 (插入二维码, 从路径插入图片,...) | 12 转化 工具 (小写金额转大写, 货币兑换,...) | 7 合并与拆分 工具 (高级组合行, 分裂细胞,...) | ... 和更多 |
使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。 单击此处获取您最需要的功能...
Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松
- 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
- 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
- 每天将您的工作效率提高50%,并减少数百次鼠标单击!