如何在Excel中根据背景色对单元格进行计数和求和？

网友投稿 240 2023-12-20

如何在Excel中根据背景色对单元格进行计数和求和？

假设您有一系列具有不同背景颜色的单元格，例如红色，绿色，蓝色等，但是现在您需要计算该范围内有特定背景颜色的单元格的数量，并对具有相同特定颜色的彩色单元格求和。在Excel中，没有直接公式可以计算颜色单元的总数和计数，这里我将向您介绍一些解决此问题的方法。

根据特定的填充颜色，通过“过滤器”和“小计”对单元格进行计数和求和 通过GET.CELL函数根据特定的填充颜色对单元格进行计数和求和 使用用户定义的功能根据特定的填充颜色对单元格进行计数和求和 根据特定的填充颜色对单元格进行计数和求和 Kutools 主要工作内容 根据特定的填充颜色（或条件格式颜色）对单元格进行计数和求和 Kutools for Excel 通过过滤器和小计对有色单元进行计数和求和

假设我们有一个水果销售表，如下图所示，我们将对“金额”列中的彩色单元格进行计数或求和。在这种情况下，我们可以按颜色过滤“金额”列，然后在Excel中通过SUBTOTAL函数轻松地对已过滤的彩色单元格进行计数或求和。

1。选择空白单元格以输入SUBTOTAL功能。

要计算具有相同背景色的所有单元格，请输入公式 =小计（102，E2：E20）; 要对所有具有相同背景色的单元格求和，请输入公式 =小计（109，E2：E20）;

备注：在两个公式中，E2：E20是包含彩色单元格的Amount列，您可以根据需要进行更改。

2。选择表的标题，然后单击 Data > 筛选器

。看截图：

3。点击过滤器图标在“金额”列的标题单元格中，然后单击 通过彩色滤光片

以及您将依次计算的指定颜色。看截图：

过滤后，两个SUBTOTAL公式都会自动对“金额”列中所有过滤的颜色单元进行计数和求和。看截图：

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

一键计算，汇总和平均Excel中的彩色单元格

随着优秀 按颜色计数 的特点 Kutools for Excel，您只需在Excel中一键即可按指定的填充颜色或字体颜色对单元格进行快速计数，求和和平均。此外，此功能还将通过填充颜色或字体颜色找出单元格的最大值和最小值。

全功能免费试用 30-天！

Kutools for Excel - 包括 300 多个方便的 Excel 工具。全功能免费试用 30-天，无需信用卡！ 立即行动吧!

通过GET.CELL函数对有色单元格进行计数或求和

在此方法中，我们将使用GET.CELL函数创建命名范围，获取单元格的颜色代码，然后在Excel中轻松按颜色代码进行计数或求和。请执行以下操作：

1。点击公式 > 定义名称

。看截图：

。在“新名称”对话框中，请执行以下显示的屏幕截图：

（1）在“名称”框中键入名称；

（2）输入公式 = GET.CELL（38，Sheet4！$ E2） 在“引用”框中（注意

：在公式， 38 表示返回单元格代码，并且 Sheet4！$ E2 是“金额”列中的第一个单元格，但您需要根据表格数据更改列标题。）

（3）点击 OK

按钮。

3。现在，在原始表的右侧添加一个新的“颜色”列。接下来输入公式 = NumColor

，然后拖动自动填充手柄将公式应用于“颜色”列中的其他单元格。看截图：

备注

：在公式，数字颜色是我们在前两个步骤中指定的命名范围。您需要将其更改为您设置的指定名称。

现在，“金额”列中每个单元格的颜色代码将在“颜色”列中返回。看截图：

。复制并列出活动工作表中空白区域中的填充颜色，并在其旁边键入公式，如下所示：

A.要按颜色对单元格进行计数，请输入公式 = COUNTIF（$ F $ 2：$ F $ 20，NumColor）

;

B.要按颜色对单元格求和，请输入公式 = SUMIF（$ F $ 2：$ F $ 20，NumColor，$ E $ 2：$ E $ 20）

备注：在两个公式中 $ F $ 2：$ F $ 20 是“颜色”列，数字颜色是指定的命名范围， $ E $ 2：$ E $ 20 是“金额”列，您可以根据需要进行更改。

现在，您将看到“数量”列中的单元格已被计数并通过其填充颜色求和。

使用用户定义的功能根据特定的填充颜色对单元格进行计数和求和

假设有色单元散布在如下所示的屏幕快照范围内，则上述两种方法都无法对有色单元进行计数或求和。在此，此方法将介绍VBA以解决该问题。

1。按住 ALT + F11 键，然后打开 Microsoft Visual Basic应用程序 窗口。

2。点击插页 > 模块，然后将以下代码粘贴到“模块窗口”中。

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

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell, vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End Function