当前位置：&>&&>& & Win10:如何使用VBA通过填充颜色来求和Excel值
Win10:如何使用VBA通过填充颜色来求和Excel值
来源：Win10系统之家
扫描二维码查看并分享给您的朋友
&文章如何轻松地在Excel中按单元格的背景颜色求和值，解释了如何组合颜色和内置过滤器，以单元格的填充颜色对值进行求和。 将颜色转化为有意义的信息是一种简单的方法。 一个简单的过滤技术并不总是足够的，特别是如果你重新使用其他表达式的总和。 如果需要独立总和，则可以使用两个VBA用户定义函数（UDF）中的一个。 一个依靠一个帮手专栏; 另一个没有。 既不需要大量的代码或专业知识。 不利的一面是两者都有局限性：UDF既不能使用条件格式，也不能动态使用。 但是，如果这是您所需要的，那么您可以忍受这些限制。
我在&64位系统上使用Excel 2016（桌面），但两个UDF都可以在旧版本中使用。 但是，浏览器版本不支持VBA。 您可以使用自己的数据或下载演示.xlsm，.xls，.cls和.bas文件。
启用宏的文件
在开始之前，请确保将您正在使用的工作簿保存为已启用宏的文件，如下所示：
1.单击文件选项卡，然后在左侧窗格中选择另存为。
2.从第二个下拉列表中选择启用Excel宏的工作簿（* .xlsm）。
3.点击保存。
可下载的演示文件已经是这种格式。 如果您使用的是Excel的菜单版本，则此步骤不是必需的。 如果SUMIF（）没有返回预期的结果，请确保标题单元格中的填充颜色与我们数据集中的填充颜色相同。
一个简单的UDF和帮助列
如果使用直接格式而不是条件格式，则可以使用简单（但有限）的VBA UDF按单元格的填充颜色对值进行求和。 图A中的示例表显示了B列中的一些直接格式：如果值为$ 1,000或更高，则为红色，如果值为$ 200或更低，则为绿色。 现在，当然，你可能会在这种情况下使用条件格式，所以请原谅这个人为的例子。
列B中的直接格式标识了最高和最低值。
第一步是将清单A中所示的简单UDF添加到工作簿中。 为此，请按Alt + F11打开Visual Basic编辑器（VBE）。 从插入菜单中选择模块。 然后，手动输入ColorSum（）或导入可下载的.bas文件。 不要从这个网页复制和粘贴。
Function ColorSum(CellColor As Range)
'Sum values by cell's background color.
ColorSum = CellColor.Interior.ColorIndex
End Function
ColorSum（）将当前单元格作为范围对象传递，并返回ColorIndex属性设置。 在D2中输入以下函数并将其复制到其余的单元格中，如图B所示：
=ColorSum(B2)
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中索引值匹配的索引值。 最后的范围参考标识了前两个值匹配时功能总和的值。
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中的值不是必需的。
他简单的矩阵简化了求和。
一个改进的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的最终值。
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)
ColorSum2 = lSum
End Function
这个改进的UDF消除了帮助列。
动态的，有点
两个UDF都可以工作，但都不是动态的。 图F显示了改变B3-UDF更新的颜色的结果。 在更改，添加或删除颜色之后，必须通过按Ctrl + Alt + F9强制重新计算文件。 您可以在UDF中将挥发性属性设置为True，但是您仍然必须按F9键; 那还不算好，好吧，这根本就没有好处。
UDF不更新; 这是一次性交易。
要使UDF动态化并不容易。 清单C包含一个依赖于Worksheet_SelectionChange事件的事件过程。 当您选择一个新的单元格时，此过程强制文件计算填充颜色更改。 再次，手动输入代码或导入可下载的.cls文件。 这段代码放在工作表的模块中，而不是之前添加的模块。 （您可以在VBE的Project Explorer中选择工作表。
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
Set LastRange = Target
LastColorIndex = Target.Cells.Interior.ColorIndex
虽然它有帮助，但它不是一个优雅的解决方案。你必须记得移动选择来触发它，所以它仍然是有问题的。我提供这个最后的程序是全面的;它不会真的改善这种情况，因为无论如何你必须记得强制重新计算。另外，每次你选择一个不同的单元时，你都会触发这个过程。它会在繁忙的工作表中减慢速度。我不能真正推荐它的使用，但你自己决定。无论好坏，我更喜欢Ctrl + Alt + F9按键到工作表事件过程。我建议寻找一种方法在自动化过程中结合这个UDF，所以你可以添加一个重新计算方法，而不需要记住手工这样做。
除了它们的非易失性状态，两个UDF都有一个更大的限制 - 既不适用于条件格式。在以后的文章中，我将提供一个类似的解决方案，使用条件格式。我不关心这些UDF，因为它们不是动态的，但是我不知道如何使用VBA来获得这些结果，而无需创建捕获格式更改的类模块，这已经超出了短文的范围。
上一篇： 下一篇：没有了
看过本文的人还看过
| 简体中文
30.6 MB | 简体中文
20.9 MB | 简体中文
12.2 MB | 简体中文
26.2 MB | 简体中文
5 MB | 简体中文
62.55 MB | 简体中文
54.18 MB | 简体中文
35.88 MB | 简体中文
本站资源均收集整理于互联网，其著作权归原作者所有，如果有侵犯您权利的资源，请来信告知，我们将及时撤销相应资源。
Copy 2017 ghost580.net. All Rights Reserved. 由提供技术支持附件vba代码不能在win10系统excel2013下运行，求高手解答。_百度知道
附件vba代码不能在win10系统excel2013下运行，求高手解答。
要求运行环境：win10系统下，excel2013。目的：把“图1：数据分别填写到”图2“的各个excel内固定单元格B2内。图2里各个文件内容如”图3“，相应的“市场2”文件的都是“市场2”名称，以此类推到市场5.，求vba高手解答。注：图2的5个excel文件单独在一个文件夹...
此问题已得到解答，谢谢各位的热情。
答题抽奖
首次认真答题后
即可获得3次抽奖机会，100%中奖。
把VBE6EXT.OLB后放进C:\Program Files\Common Files\microsoft shared\VBA\VBA7试试
采纳率：62%
来自团队：
为您推荐：
其他类似问题
换一换
回答问题，赢新手礼包
个人、企业类
违法有害信息,请在下方选择后提交
色情、暴力
我们会通过消息、邮箱等方式尽快将举报结果通知您。503 Service Temporarily Unavailable
503 Service Temporarily Unavailable都是看心情投稿啦投稿：8粉丝：3分享--dynmicweibozoneqqbaidu将视频贴到博客或论坛视频地址复制嵌入代码复制微信扫一扫分享收藏0硬币--稍后看马克一下~用手机看转移阵地~用或其他应用扫描二维码手机下视频请使用扫码若未安装客户端，可直接扫此码下载应用看过该视频的还喜欢正在加载...miniOFF}