Win10专业版使用VBA来求和Excel值的技巧

文章如何轻松地在Excel中按单元格的背景颜色求和值，解释了如何组合颜色和内置过滤器，以单元格的填充颜色对值进行求和。 将颜色转化为有意义的信息是一种简单的方法。 一个简单的过滤技术并不总是足够的，特别是如果你重新使用其他表达式的总和。 如果需要独立总和，则可以使用两个VBA用户定义函数（UDF）中的一个。 一个依靠一个帮手专栏; 另一个没有。 既不需要大量的代码或专业知识。 不利的一面是两者都有局限性：UDF既不能使用条件格式，也不能动态使用。 但是，如果这是您所需要的，那么您可以忍受这些限制。

我在win10 64位系统上使用Excel 2016（桌面），但两个UDF都可以在旧版本中使用。 但是，浏览器版本不支持VBA。 您可以使用自己的数据或下载演示.xlsm，.xls，.cls和.bas文件。

启用宏的文件

在开始之前，请确保将您正在使用的工作簿保存为已启用宏的文件，如下所示：

1.单击文件选项卡，然后在左侧窗格中选择另存为。

2.从第二个下拉列表中选择启用Excel宏的工作簿（* .xlsm）。

3.点击保存。

可下载的演示文件已经是这种格式。 如果您使用的是Excel的菜单版本，则此步骤不是必需的。 如果SUMIF（）没有返回预期的结果，请确保标题单元格中的填充颜色与我们数据集中的填充颜色相同。

一个简单的UDF和帮助列

如果使用直接格式而不是条件格式，则可以使用简单（但有限）的VBA UDF按单元格的填充颜色对值进行求和。 图A中的示例表显示了B列中的一些直接格式：如果值为$ 1,000或更高，则为红色，如果值为$ 200或更低，则为绿色。 现在，当然，你可能会在这种情况下使用条件格式，所以请原谅这个人为的例子。

图A

列B中的直接格式标识了最高和最低值。

第一步是将清单A中所示的简单UDF添加到工作簿中。 为此，请按Alt + F11打开Visual Basic编辑器（VBE）。 从插入菜单中选择模块。 然后，手动输入ColorSum（）或导入可下载的.bas文件。 不要从这个网页复制和粘贴。

清单A

Function ColorSum(CellColor As Range)

'Sum values by cell's background color.

ColorSum = CellColor.Interior.ColorIndex

End Function

ColorSum（）将当前单元格作为范围对象传递，并返回ColorIndex属性设置。 在D2中输入以下函数并将其复制到其余的单元格中，如图B所示：

=ColorSum(B2)

图B

调用UDF。

UDF引用列B中的单元格，并返回每个单元格填充颜色的索引值：绿色是48，红色是3.我们不关心没有指定颜色的单元格（-4142）。

接下来，在E2中输入以下SUMIF（），并将其复制到其余的单元格中：

=SUMIF($D$2:$D$18,$D2,$B$2:$B$18)

如图C所示，SUMIF（）函数分别返回绿色和红色单元的总数：3,600和602。 第一个范围是指UDF在D列中返回的颜色索引值。对$ D2的引用使用当前索引值作为标准 - 我们正在寻找D2：D18中与D2中索引值匹配的索引值。 最后的范围参考标识了前两个值匹配时功能总和的值。

图C

SUMIF（）返回绿色和红色单元格的总和。

坦率地说，这有点乱。 即使它有效，你可能也不会对这个安排感到满意。 图D显示了一个更合理的设置。 手动添加填充颜色到G1和H1; 我们将使用它们作为标题单元格和标准。 然后，在G1中输入以下函数并将其复制到H1：

=SUMIF($D$2:$D$18,ColorSum(G$1),$B$2:$B$18)

第一个范围是指由UDF返回的索引值。 第二个参数ColorSum（）返回标题单元格的颜色索引值; 在这种情况下，单元格为G1，索引值为48.当前两个参数的值匹配时，此函数将B2：B18中的相应值相加。 如果您不想看到D列中的索引值，请隐藏该列。 因此，列E中的值不是必需的。

图D

他简单的矩阵简化了求和。

一个改进的UDF

第一个UDF依赖于帮助列 - 列D中的颜色索引值。您可以使用清单B中显示的UDF来消除此列。图E显示了此UDF在简单矩阵中的结果，但在第3行中。输入 跟随功能到G3并复制到H3：

=ColorSum2(G$1,$B$2:$B$18)

这个UDF使用语法

=COLORSUM2(criteria,range)

其中标准引用您匹配的颜色，范围表示您正在求和的值。 简而言之，此UDF将当前单元格（B2：B18）的填充颜色与条件单元格（G1）中的填充颜色进行比较。 当这些值匹配时，该函数将当前单元格中的值添加到lSum。 在检查过的范围（rng）中的所有单元格之后，UDF返回lSum的最终值。

清单B

Function ColorSum2(CellColor As Range, rng As Range)

'Sum values by cell's background color.

Dim lSum As Long

Dim iIndex As Integer

Dim cclr As Variant

iIndex = CellColor.Interior.ColorIndex

Debug.Print iIndex

For Each cclr In rng

If cclr.Interior.ColorIndex = iIndex Then

lSum = WorksheetFunction.Sum(cclr, lSum)

End If

Next cclr

ColorSum2 = lSum

End Function

图E

这个改进的UDF消除了帮助列。

动态的，有点

两个UDF都可以工作，但都不是动态的。 图F显示了改变B3-UDF更新的颜色的结果。 在更改，添加或删除颜色之后，必须通过按Ctrl + Alt + F9强制重新计算文件。 您可以在UDF中将挥发性属性设置为True，但是您仍然必须按F9键; 那还不算好，好吧，这根本就没有好处。

图F

UDF不更新; 这是一次性交易。

要使UDF动态化并不容易。 清单C包含一个依赖于Worksheet_SelectionChange事件的事件过程。 当您选择一个新的单元格时，此过程强制文件计算填充颜色更改。 再次，手动输入代码或导入可下载的.cls文件。 这段代码放在工作表的模块中，而不是之前添加的模块。 （您可以在VBE的Project Explorer中选择工作表。

列表C

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Compares color after selection change to force calculaton.

'Forces UDFs, ColorSum() and ColorSum2() to recalculate

'when fill colors are changed.

Static LastRange As Range

Static LastColorIndex As String

If LastRange.Cells.Interior.ColorIndex <> LastColorIndex Then

Application.CalculateFull

End If

Set LastRange = Target

LastColorIndex = Target.Cells.Interior.ColorIndex

End Sub

虽然它有帮助，但它不是一个优雅的解决方案。你必须记得移动选择来触发它，所以它仍然是有问题的。我提供这个最后的程序是全面的;它不会真的改善这种情况，因为无论如何你必须记得强制重新计算。另外，每次你选择一个不同的单元时，你都会触发这个过程。它会在繁忙的工作表中减慢速度。我不能真正推荐它的使用，但你自己决定。无论好坏，我更喜欢Ctrl + Alt + F9按键到工作表事件过程。我建议寻找一种方法在自动化过程中结合这个UDF，所以你可以添加一个重新计算方法，而不需要记住手工这样做。

敬请关注

除了它们的非易失性状态，两个UDF都有一个更大的限制 - 既不适用于条件格式。在以后的文章中，我将提供一个类似的解决方案，使用条件格式。我不关心这些UDF，因为它们不是动态的，但是我不知道如何使用VBA来获得这些结果，而无需创建捕获格式更改的类模块，这已经超出了短文的范围。