跳到主要内容

Excel 提示:按颜色(背景、字体、条件格式)对单元格进行计数/求和

作者:晓阳 最后修改时间:2023-11-03

在日常任务中,颜色标记是快速区分和突出显示关键数据的流行方法。 但是,我们如何根据特定颜色(填充颜色、字体颜色、条件格式)对单元格数据进行计数或求和? 默认情况下,Excel 不提供按颜色进行计数或求和的直接功能。 尽管如此,通过一些技巧和间接方法,我们仍然可以实现这一目标。 本文将探讨如何按颜色对数据进行计数或求和。

根据背景色对单元格进行计数和求和

根据字体颜色对单元格进行计数和求和

根据条件格式颜色对单元格进行计数和求和


视频:根据颜色对单元格进行计数和求和


根据背景色对单元格进行计数和求和

例如,如果您有一系列数据,其中的值填充有不同的背景颜色,如下面的屏幕截图所示。 要根据特定颜色对单元格进行计数或求和,Excel 不提供根据背景颜色对单元格进行计数或求和的直接功能。 然而,只要有一点聪明才智和一些方便的技术,您就可以完成这项任务。 让我们在本节中探索一些有用的方法。


使用用户定义的函数按背景颜色对单元格进行计数和求和

在这里,我们将向您展示如何创建和使用这样的用户定义函数来解决 Excel 中的此任务。 请按照以下步骤进行:

第 1 步:打开 VBA 模块编辑器并复制代码

  1. 媒体 Alt + F11键 键打开 Microsoft Visual Basic应用程序 窗口。
  2. 在打开的窗口中,单击 插页 > 模块 创建一个新的空白模块。
  3. 然后,将以下代码复制并粘贴到空白模块中。
    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 加 并选择 按颜色计数.

按颜色计数 对话框中,请指定操作:

  1. 选择 标准格式 来自 上色方式 下拉列表;
  2. 指定 背景 来自 计数类型 下拉列表,可以在对话框中预览每种背景颜色的统计结果;
  3. 最后点击 生成报告 将计算结果导出到新工作簿。

结果:

现在,您将获得一个包含统计信息的新工作簿。 看截图:

Tips:
  1. 我们推荐使用 按颜色计数 该功能还支持基于标准字体颜色、背景或条件格式的字体颜色以及填充和条件格式颜色的组合对单元格进行计数和求和。
  2. 对此功能感兴趣,请 点击下载即可免费试用 30 天.

使用过滤器和小计功能按背景颜色对单元格进行计数和求和

假设我们有一个水果销售表,如下图所示,我们将对表中的彩色单元格进行计数或求和 金额 列。

步骤 1:应用 SUBTOTAL 函数

选择空白单元格进入小计功能。

  • 要对具有相同背景颜色的所有单元格进行计数,请输入公式:
    =SUBTOTAL(102, F2:F16)
  • 要将所有具有相同背景颜色的单元格相加,请输入公式;
    =SUBTOTAL(109, F2:F16)
  • 备注: 在上面的公式中, 102 表示对过滤列表中的数值进行计数,同时排除隐藏单元格; 109 表示对过滤列表中不包括隐藏单元格的值进行求和; F2:F16 是计算计数或总和的范围。

第 2 步:根据特定颜色过滤单元格

  1. 选择表格标题,然后单击 时间 > 筛选器。 看截图:
  2. 点击 筛选器 图标  在标题单元格中 金额 列,然后单击 通过彩色滤光片 以及您将依次计算的指定颜色。 看截图:

结果:

过滤后,SUBTOTAL 公式会自动对表格中的彩色单元格进行计数和求和 金额 柱。 看截图:

备注:此方法需要将要计数或求和的彩色单元格放在同一列中。

根据字体颜色对单元格进行计数和求和

想要根据 Excel 中的字体颜色对单元格进行计数或求和吗? 假设您有数据,如给定的屏幕截图所示,其中单元格包含红色、蓝色、橙色和黑色文本。 默认情况下,Excel 并不容易做到这一点。 但别担心! 在本节中,我们将向您展示一些简单的技巧来做到这一点。


使用用户定义函数根据字体颜色对单元格进行计数和求和

要对具有特定字体颜色的单元格进行计数和求和,以下用户定义函数可以帮助您解决此任务。 请按照以下步骤进行:

第 1 步:打开 VBA 模块编辑器并复制代码

  1. 媒体 Alt + F11键 键打开 Microsoft Visual Basic应用程序 窗口。
  2. 在打开的窗口中,单击 插页 > 模块 创建一个新的空白模块。
  3. 然后,将以下代码复制并粘贴到空白模块中。
    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 加 > 按颜色计数 打开 按颜色计数 对话框。

按颜色计数 对话框中,请指定操作:

  1. 选择 标准格式 来自 上色方式 下拉列表;
  2. 指定 字体 来自 计数类型 下拉列表,可以在对话框中预览每种字体颜色的统计结果;
  3. 最后点击 生成报告 将计算结果导出到新工作簿。

结果:

现在,您有一个新工作簿,显示基于字体颜色的详细统计信息。 看截图:

Tips: 对这个功能感兴趣,请 点击下载即可免费试用 30 天.

根据条件格式颜色对单元格进行计数和求和

在 Excel 中,您通常可以使用条件格式将特定颜色应用于满足特定条件的单元格,使数据可视化直观。 但是,如果您需要对那些特殊格式的单元格进行计数或求和怎么办? 虽然 Excel 没有为此提供直接的方法,但可以通过以下方法绕过此限制。


使用 VBA 代码对条件格式的单元格进行计数和求和

使用内置函数对 Excel 中的条件格式单元格进行计数和求和并不简单。 但是,您可以使用 VBA 代码完成此任务。 让我们看一下如何使用 VBA 来实现此目的:

第 1 步:打开 VBA 模块编辑器并复制代码

  1. 媒体 Alt + F11键 键打开 Microsoft Visual Basic应用程序 窗口。
  2. 在打开的窗口中,单击 插页 > 模块 创建一个新的空白模块。
  3. 然后,将以下代码复制并粘贴到空白模块中。
    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 代码

  1. 粘贴代码后,按 F5 键运行此代码,会出现提示框,请根据条件格式选择要对单元格进行计数和求和的数据范围。 然后,单击 OK,请参见屏幕截图:
  2. 在另一个提示框中,选择要计数和求和的特定条件格式颜色,然后单击 OK 按钮,请参见屏幕截图:

结果:

现在,结果(包括具有指定条件格式颜色的单元格的计数和总和)将显示在弹出框中。 看截图:


使用智能功能对条件格式的单元格进行计数和求和

如果您正在寻找其他快速简便的方法来对条件格式的单元格进行计数和求和, Kutools for Excel 是您的首选解决方案。 它是 按颜色计数 只需点击几下即可解决此任务。 深入了解 Kutools 可以为您的工作流程带来的效率和精度。

下载并安装 Kutools for Excel,首先,根据特定的条件格式颜色选择要对单元格进行计数或求和的数据范围。 然后,单击 Kutools 加 > 按颜色计数 打开 按颜色计数 对话框。

按颜色计数 对话框中,请指定操作:

  1. 选择 条件格式 来自 上色方式 下拉列表;
  2. 指定 背景 来自 计数类型 下拉列表,可以在对话框中预览每种条件格式颜色的统计结果;
  3. 最后点击 生成报告 将计算结果导出到新工作簿。

结果:

现在,您有一个新工作簿,显示基于条件格式颜色的详细统计信息。 看截图:

Tips: 对这个功能感兴趣,请 点击下载即可免费试用 30 天.

相关文章:

  • 如果字体颜色为红色则返回特定文本
  • 如果字体颜色在另一个单元格中为红色,如下图所示,您如何返回特定文本? 在本文中,我将介绍一些基于 Excel 中的红色字体文本进行一些操作的技巧。
  • 按多种颜色过滤数据
  • 通常,在Excel中,您可以快速仅过滤一种颜色的行,但是,您是否曾经考虑过同时过滤多种颜色的行? 本文,我将为您介绍解决此问题的快速技巧。
  • 添加颜色到下拉列表
  • 在 Excel 中,创建下拉列表可以为您提供很多帮助,有时,您需要根据相应的选择对下拉列表值进行颜色编码。 例如,我创建了一个水果名称的下拉列表,当我选择苹果时,我需要将单元格自动着色为红色,当我选择橙色时,单元格可以自动着色为橙色。
  • 为合并单元格的交替行着色
  • 在大数据中用不同颜色格式化交替行对我们扫描数据很有帮助,但有时,您的数据中可能会有一些合并的单元格。 要使用不同颜色交替突出显示合并单元格的行,如下图所示,您如何在 Excel 中解决此问题?
Comments (239)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
HI, i want sum all data with green color, and when I add 1 cell with green color the total will be increase qty, pls help me. Thank you.
This comment was minimized by the moderator on the site
Saya sudah copy VBA, dan pakai petunjuk sesuai di atas, untuk sum font color, tapi hasilnya #NAME. Knpa ya?
This comment was minimized by the moderator on the site
嗨~版主好,我用了VBA 模塊,但是完全沒有動靜,沒有出現顏色儲存格的統計數量,Count欄一片空白~~請問是為什麼呢?跟office版本有關嗎?謝謝
This comment was minimized by the moderator on the site
嗨,MINA,
文章中的VBA代碼,微軟office版本基本上都可以適用,我這代碼可以正常使用。 如果你那邊還用不了,可以上傳你的文件,我們可以幫忙看看哪裡的問題,謝謝!
This comment was minimized by the moderator on the site
I am using =IF(D272>F272,D272-F272,if(F272>D272,F272-D272,"")) formula for subtraction, and I want it will coloured also??
This comment was minimized by the moderator on the site
Hi namrata,
Do you want to fill color for the result of your formula?
So, if the result is D272-F272, you want it, say, red; If the result is F272-D272, you want it, say, green; If blank, blank?
Amanda
This comment was minimized by the moderator on the site
I ran into problems when trying to run the function. Macro errors telling me: No RETURN() or HALT() function found on macro sheet. perhaps somebody could assist here. ThanksPaul
This comment was minimized by the moderator on the site
Hi, How to make a diagram based on the colors in the table? For example, I want to count all the red, green and yellow colors in the cells in a table and make a diagram. How to do this? Please
This comment was minimized by the moderator on the site
Is there a way to count different color backgrounds from conditional formatting? The current code as of 7/14/2020 counts them all as default yellow or not at all.
This comment was minimized by the moderator on the site
Hi Dusty,
You can try the Count by Color feature of Kutools for Excel. This feature will help you quickly calculate (count, sum, average, etc.) cells by cell background color or font color, no matter they are formatted by conditional formatting or solidly format.
This comment was minimized by the moderator on the site
Did anyone find a solution to auto-refresh? I have to manually refresh for it to update. Otherwise, it works great!
This comment was minimized by the moderator on the site
Hi Dennis,
By default, formulas are calculated in Excel until you are turning off the Automatic Formula Calculation. You can enable it by clicking Formulas > Calculation Options > Automatic.
This comment was minimized by the moderator on the site
Anyone have tips on a max by color VBA?
This comment was minimized by the moderator on the site
Hi Natasha,VBA is good but hard to apply. But below methods may solve your work easily too.
Method 1: Use Find & Replace feature to select and statistic the color cells(1) Press Ctrl + H keys to open the Find and Replace dialog, and then enable the Find tab.
(2) In the dialog, click Options to show advanced find options.
(3) Then click Format > Choose Format From Cell, and select one of the specified color cells.
(4) Click Find All. Now all cells with the same fill color are found out and listed at the bottom of the dialog.
(5) Select one of found cells, and press Ctrl + A to select all found cells, so that these cells are selected in the worksheet.
(6) Now you can get the count, average, sum, min, max, etc. of these cells in the task bar.
Note: If a certain statistic result cannot be found one the taskbar, you can right click the task bar, and then tick the specified item to show it.

Method 2: Kutools for Excel
Kutools for Excel supports 30-day free trial. Therefore, you can download it and try its Count by color feature to solve your problem with several clicks only.
This comment was minimized by the moderator on the site
awesome fix! count by color over an entire sheet was just what i was looking for and your VBA code was tighter than others that i have looked at. Works like a charm. Thank you, and again, well done.
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations