如何在Excel中按颜色求和(公式和VBA)

如何在Excel中按颜色求和

Excel 有一些非常神奇的功能，但它没有一个可以根据单元格颜色对单元格值求和的功能。

例如，我有如下所示的数据集，我想获取所有橙色和黄色单元格的总和。

不幸的是，没有内置函数可以做到这一点。

但永不说永不！

在本教程中，我将向您展示三种简单的技巧，您可以使用它们来 Excel 中按颜色求和。

使用筛选器和 SUBTOTAL 按颜色对单元格求和

让我们从最简单的开始。

下面我有一个数据集，其中包含员工姓名和他们的销售数字。

在这个数据集中，我想要获取所有黄色和橙色单元格的总和。

虽然 Excel 中没有内置函数可以根据单元格颜色对值进行求和，但有一个简单的解决方法，依赖于您可以根据单元格颜色过滤单元格这一事实。

对于此方法，在单元格 B17（或彩色单元格数据集下方同一列中的任何单元格）中输入以下公式。

=SUBTOTAL(9,B2:B15)

在上面的 SUBTOTAL 公式中，我使用 9 作为第一个参数，它告诉函数我想要获取作为第二个参数给出的范围的总和。

但是为什么不直接使用 SUM 公式呢？

这是因为当我有 SUBTOTAL 公式并过滤单元格以仅显示具有特定颜色的单元格时，SUBTOTAL 公式将仅显示可见单元格的总和（SUM 公式无法做到这一点）。

因此，一旦您有了 SUBTOTAL 公式，请按照以下步骤根据单元格颜色获取 SUM：

选择数据集中的任意单元格
单击数据选项卡
在“排序和过滤”组中，单击“过滤”图标。这将对数据集应用过滤，您将能够在标题中看到过滤图标
在销售标题单元格中，单击筛选器图标
转到“按颜色过滤”选项
选择您想要根据其过滤数据集的颜色。

一旦您执行此操作，您会注意到小计结果会发生变化，现在它只会为您提供可见单元格的总和（这些单元格仅具有您过滤数据集的颜色）。

类似地，如果你按数据集中的其他颜色进行过滤（比如橙色而不是黄色），SUBTOTAL 函数也会相应调整，并给出所有橙色单元格的总和

专家提示：将过滤器应用于数据集的键盘快捷键是 Control + Shift + L（按住 Control 和 Shift 键，然后按 L 键）。如果使用 Mac，请使用 Command + Shift + L

使用 VBA 按颜色对单元格求和

我提到过，Excel 中没有内置公式可以根据单元格颜色值进行求和。但是，您可以使用 VBA 创建自己的公式来执行此操作。

使用 VBA，您可以创建一个可保留在后端的自定义函数，然后像工作表中的任何其他常规函数一样使用它。

下面是 VBA 代码，它将创建自定义函数，允许您在 Excel 中按颜色求和。

'Code created by Sumit Bansal from 
'This VBA code created a function that can be used to sum cells based on color
Function SumByColor(SumRange As Range, SumColor As Range)
Dim SumColorValue As Integer
Dim TotalSum As Long
SumColorValue = SumColor.Interior.ColorIndex
Set rCell = SumRange
For Each rCell In SumRange
If rCell.Interior.ColorIndex = SumColorValue Then
TotalSum = TotalSum + rCell.Value
End If
Next rCell
SumByColor = TotalSum
End Function

要使用此 VBA 自定义函数，您首先必须复制此代码并将其粘贴到 VB 编辑器的后端。

完成后，您将能够在工作表中使用此功能。

下面是将此代码添加到 VB 编辑器的步骤。

单击功能区中的“开发工具”选项卡（如果没有看到“开发工具”选项卡，请单击此处了解如何获取）
单击 Visual Basic 图标。这将打开 Excel 的 Visual Basic 编辑器。
点击菜单中的插入选项
单击“模块”。这将插入一个新模块，您将能够在项目资源管理器（左侧显示所有对象的窗格）中看到它。如果您没有看到“项目资源管理器”窗格，请单击“查看”，然后单击“项目资源管理器”
复制上述 VBA 代码并将其粘贴到新插入的模块代码窗口中
关闭 VB 编辑器

现在您有了 Excel 后端的代码，您将能够在工作表中使用我们创建的函数 (SumByColor)。

为了使此功能正常工作，我需要工作表中一个单元格，其中包含我想要获取总和的相同颜色。

在我们的示例中，我对单元格 D2 和 D3 执行了此操作，其中 D2 为黄色，D3 为橙色。

现在我可以在这些单元格中使用以下公式：

=SumByColor($B$2:$B$15,D2)

上述公式采用两个参数：

具有我想要添加的颜色的单元格范围
引用任何包含颜色的单元格（以便公式可以选择颜色索引并将其用作添加值的条件）

请注意，公式是动态的，如果您对数据集进行任何更改（例如更改任何值或应用/删除某些单元格的颜色），公式就会自动更新。如果您发现公式没有更新，请按 F9 键，公式就会更新。

由于我们在工作簿中使用了 VBA 代码，因此需要将其保存为启用宏的工作簿（带有 .XLSM 扩展名）。

专家提示：如果您经常需要根据背景颜色添加单元格，我建议您复制并粘贴此 VBA 代码以用于个人宏工作簿中的自定义公式。这样，系统上的所有工作簿都可以使用该代码。

使用 GET.CELL 按颜色对单元格求和

我想要向您展示的最后一种方法包括一个隐藏的 Excel 公式（很多人都不知道）。

此方法使用GET.CELL函数，它可以得到彩色单元格的颜色索引值。

一旦我们获得了每个单元格的颜色索引值，我们就可以使用一个简单的总和公式来获取其中具有特定颜色的单元格的总和。

它不像我之前介绍的 VBA 自定义函数那么优雅，但如果您不想使用 VBA，那么这可能是您的最佳选择。

GET.CELL 是一个旧的宏 4 函数，由于兼容性原因，它保留在 Excel 中，但您找不到有关它的很多详细信息（因为它很少使用）。

下面我有一个数据集，其中有我想要求和的彩色单元格。

为了使该技术发挥作用，我们首先需要创建一个命名范围，该范围将使用 GET.CELL 函数提供彩色单元格的颜色值。

以下是执行此操作的步骤：

单击功能区中的“公式”选项卡
在“定义的名称”组中，单击“名称管理器”
在名称管理器对话框中，单击新建
在“新名称”对话框中，输入名称 – SumColor
在引用字段中，输入以下公式：=GET.CELL(38,$B2)
单击“确定”
关闭名称管理器对话框

上述步骤创建了一个命名范围，我们现在可以在工作簿中使用它。

注意：GET.CELL 函数有两个参数，第一个是数字，它告诉函数我们需要什么信息，第二个是该单元格本身的单元格引用。在本例中，我将 38 作为第一个参数，它将为我们提供所引用单元格的颜色索引值。

现在第二步是获取 B 列中所有颜色的颜色索引值。

以下是执行此操作的步骤：

在单元格 C1 中，输入标题 – 颜色索引（或任何你想叫的名字）
在单元格 C2 中，输入以下公式：=SumColor
对 C 列中的所有单元格应用相同的公式（您可以使用填充柄或简单地复制粘贴单元格 C2）

上述步骤将为您提供一个值，该值代表 B 列（左侧单元格）单元格的颜色索引。

SumColor 是我们创建的命名范围，它使用 GET.CELL 函数获取左侧单元格的颜色索引值。您可以在任何列中使用此公式，但它应始终从该列中的第二个单元格开始。例如，您可以在 H 列或 J 列中使用此公式，而不是 C 列，但从这些列中的第二个单元格开始。

现在我们为每种颜色都有一个唯一的编号，我们可以使用它根据单元格的颜色获取单元格的总和。

以下是执行此操作的步骤：

在单元格 E2 和 E3 中，为单元格指定要计算总和的颜色。在我的例子中，单元格 E2 中为黄色，单元格 E3 中为橙色。
在单元格 F2 中，输入以下公式：=SUMIF(C2:C15,SumColor,B2:B15)
复制单元格并粘贴到单元格 F3 中（这也可以复制公式并调整引用）。

上述步骤将为您提供相邻销售列 F 中具有颜色的所有单元格的总和。

我们使用了 SUMIF 公式，如果左侧单元格（在 F 列）的颜色索引与 C 列中的颜色索引相同，则它会将 B 列中的所有值相加。

尽管这肯定是一种按颜色对单元格求和的稍长方法（与 SUBTOTAL 或 VBA 相比），但它可以完成工作，并且您只需进行一次此设置。

请注意，虽然公式是动态的，但如果您进行任何更改（例如更改 B 列中的颜色单元格或从中删除颜色），更改可能不会立即反映在 SUMIF 公式中。一个简单的解决方法是转到包含公式的单元格，单击 F2 键进入机场，然后按 Enter 键。这将强制公式重新计算，您将获得更新的结果。

因此，以上就是在 Excel 中按颜色求和的三种方法。虽然 SUBTOTAL 非常简单直接，但我个人更喜欢 VBA 方法。