Note: The other languages of the website are Google-translated. Back to English

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

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


通过过滤器和小计对有色单元进行计数和求和

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

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

  1. 要计算具有相同背景色的所有单元格,请输入公式 =小计(102,E2:E20);
  2. 要对所有具有相同背景色的单元格求和,请输入公式 =小计(109,E2:E20);


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

2。 选择表的标题,然后单击 数据 > 筛选器。 看截图:

3。 点击过滤器图标  在“金额”列的标题单元格中,然后单击 通过彩色滤光片 以及您将依次计算的指定颜色。 看截图:

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

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

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

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

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

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

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

1。 点击 公式 > 定义名称。 看截图:

2。 在“新名称”对话框中,请执行以下显示的屏幕截图:
(1)在“名称”框中键入名称;
(2)输入公式 = GET.CELL(38,Sheet4!$ E2) 在“引用”框中(注意: 在公式, 38 表示返回单元格代码,并且 Sheet4!$ E2 是“金额”列中的第一个单元格,但您需要根据表格数据更改列标题。)
(3)点击 OK 按钮。

3。 现在,在原始表的右侧添加一个新的“颜色”列。 接下来输入公式 = NumColor ,然后拖动自动填充手柄将公式应用于“颜色”列中的其他单元格。 看截图:
备注: 在公式, 数字颜色 是我们在前两个步骤中指定的命名范围。 您需要将其更改为您设置的指定名称。

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

4。 复制并列出活动工作表中空白区域中的填充颜色,并在其旁边键入公式,如下所示:
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

3。 然后保存代码,并应用以下公式:
A.计算有色细胞: = colorfunction(A,B:C,FALSE)
B.对有色单元格求和: = colorfunction(A,B:C,TRUE)

注意:在上述公式中, A 是您要计算计数和总和的具有特定背景颜色的单元格,并且 公元前 是您要计算计数和总和的单元格范围。

4。 以以下屏幕截图为例,输入公式= colorfunction(A1,A1:D11,FALSE) 计算黄色细胞。 并使用公式 = colorfunction(A1,A1:D11,TRUE) 总结黄色细胞。 看截图:

5。 如果要对其他有色单元格进行计数和求和,请重复步骤4。然后您将获得以下结果:


使用Kutools函数根据特定的填充颜色对单元格进行计数和求和

Kutools for Excel还支持一些有用的功能来帮助Excel用户进行特殊计算,例如按单元格背景颜色计数,按字体颜色计数之和等等。

Kutools for Excel -包括300多个用于Excel的便捷工具。 全功能30天免费试用,无需信用卡! 立即免费试用!

1。 选择放置计数结果的空白单元格,然后单击 库工具 > Kutools函数 > 统计与数学 > 单色。 看截图:

2。 在“函数参数”对话框中,请指定要计算的彩色单元格范围。 参考 框中,选择由指定背景颜色填充的单元格 color_index_nr 框,然后单击 OK 按钮。 看截图:

笔记:
(1)您也可以输入指定的Kutools函数 = COUNTBYCELLCOLOR($ A $ 1:$ E $ 20,G2)  直接在空白单元格或公式栏中获取计数结果;
(2)点击 库工具 > Kutools函数 > 统计与数学 > 超级手机颜色 或输入 = SUMBYCELLCOLOR($ A $ 1:$ E $ 20,G2) 直接在空白单元格中根据指定的背景色对单元格求和。
应用 单色 超级手机颜色 分别为每种背景色提供功能,您将获得如下屏幕截图所示的结果:

Kutools函数 包含许多内置函数来帮助Excel用户轻松计算,包括 计数/总和/平均可见单元格, 按单元格颜色计数/求和, 按字体颜色计数/求和, 计数字符, 按字体粗体计数等等。 免费试用!


使用Kutools for Excel根据特定的填充颜色对单元格进行计数和求和

使用上面的用户定义函数,您需要一个一个地输入公式,如果有很多不同的颜色,此方法将很繁琐且耗时。 但是如果你有 Kutools for Excel按颜色计数 实用程序,您可以快速生成彩色单元格的报告。 您不仅可以对有色单元格进行计数和求和,还可以获取有色范围的平均值,最大值和最小值。

Kutools for Excel -包括300多个用于Excel的便捷工具。 全功能30天免费试用,无需信用卡! 立即免费试用!

1。 选择您要使用的范围,然后单击 Kutools 加 > 按颜色计数,请参见屏幕截图:

2. 而在 按颜色计数 对话框,请按如下所示进行截图:
(1)选择 标准格式 来自 上色方式 下拉列表;
(2)选择 背景 来自 计数类型 下拉列表。
(3)单击生成报告按钮。

备注:要按特定的条件格式颜色对有色单元格进行计数和求和,请选择 条件格式 来自 上色方式 对话框上方的下拉列表,或选择 标准和条件格式 从下拉列表中计算指定颜色填充的所有单元格。

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

冥界 按颜色计数 功能通过背景颜色或字体颜色计算(计数,总和,平均值,最大值等)单元格。 免费试用!


相关文章:


演示:根据背景和条件格式颜色对单元格进行计数和求和:


Kutools for Excel 包括适用于Excel的300多种便捷工具,可以在30天之内免费试用。 立即下载并免费试用!

最佳办公效率工具

Kutools for Excel解决了您的大多数问题,并使您的生产率提高了80%

  • 重用: 快速插入 复杂的公式,图表 以及您以前使用过的任何东西; 加密单元 带密码 创建邮件列表 并发送电子邮件...
  • 超级公式栏 (轻松编辑多行文本和公式); 阅读版式 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不会丢失数据; 拆分单元格内容; 合并重复的行/列...防止细胞重复; 比较范围...
  • 选择重复或唯一 行; 选择空白行 (所有单元格都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择...
  • 确切的副本 多个单元格,无需更改公式参考; 自动创建参考 到多张纸; 插入项目符号,复选框等...
  • 提取文字,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级滤镜 (将过滤方案保存并应用于其他工作表); 高级排序 按月/周/日,频率及更多; 特殊过滤器 用粗体,斜体...
  • 结合工作簿和工作表; 根据关键列合并表; 将数据分割成多个工作表; 批量转换xls,xlsx和PDF...
  • 超过300种强大功能。 支持Office / Excel 2007-2019和365。支持所有语言。 在您的企业或组织中轻松部署。 完整功能30天免费试用。 60天退款保证。
kte选项卡201905

Office选项卡为Office带来了选项卡式界面,使您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
officetab底部
按评论排序
注释 (234)
还没有评分。 成为第一位评论!
该评论由网站上的主持人最小化
非常有用的工具,非常感谢
戈里桑卡尔
该评论由网站上的主持人最小化
插入函数后,当我们更改值时,总和不会自动更新,任何建议
戈里桑卡尔
该评论由网站上的主持人最小化
Alt-Ctrl-F9 将重新计算
韦斯特伍德迈克
该评论由网站上的主持人最小化
如果其他人正在使用该文件并且他们不知道 Alt-Ctrl-F9,您可以添加和更新按钮。 只需将这行代码添加到宏“Application.CalculateFull”中。 它与刚刚编程到按钮中的 Alt-Ctrl-F9 相同。
迈克·布兰尼根
该评论由网站上的主持人最小化
在第一行正下方添加第二行,表示 Application.volitile 它使其在更新某些内容后重新计算
12345678998765432154
该评论由网站上的主持人最小化
我试着把 应用.挥发性 在第一行没有发生任何事情:(
贾罗德
该评论由网站上的主持人最小化
[quote]在第一行正下方添加第二行,表示 Application.volitile 它使它在更新某些内容后重新计算通过12345678998765432154[/quote] Application.Volatile 是正确的函数不是 Application.volitile
阿布鲁格
该评论由网站上的主持人最小化
我没有看到任何说 application.volatile 的行。 我在哪里可以找到它?
Jo
该评论由网站上的主持人最小化
我用 ColorFunction 公式双击单元格,然后按 Enter。 它更新了。
杰奎
该评论由网站上的主持人最小化
很有用。 谢谢
HR
该评论由网站上的主持人最小化
我也试过这个公式。 适用于第一排,不适用于第二排和第三排。 ???
安妮子
该评论由网站上的主持人最小化
我同意贾罗德的观点。 我的函数的前两行说: Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Application.Volatile 问题是只有 Alt-Ctrl-F9 会重新计算。 我们正在寻找更自动化的解决方案。 想法?
纪念
该评论由网站上的主持人最小化
像这样尝试: 1. 确保您已按描述保存函数 2. 允许我使用示例 * 我有数百行数据 * 我使用 A 到 AB 列的更多数据 * 每当我遇到问题时数据,我用黄色突出显示 * 我使用这个很酷的公式来“计算”每行的突出显示数量 3. 如何计算每行突出显示的数量 a。 数字 单元格范围可能有您想要计算(或求和)的亮点 * 对我来说,我想计算每行(我的范围)上标记的亮点的数量 b。 选择一个单元格,您将为我报告计数(或总和)*,我将其放在数据的最右侧...在 AE c 列中。 在您在 b 项(上图)中选择的单元格中插入以下公式 =colorfunction(AE3,A3:AB3,FALSE) * 对我来说,我将此公式放在单元格 AE3 中(行尾)并且我突出显示了相同公式单元格 黄色 d. 然后,我将这个公式复制到我的所有数据行 4。我观察到 a。 没有计数。 (坏的)这里有 5 个关键问题: 我按下 CTL+ ALT + F9 。 普雷斯托!!! 有用。! 希望这个玩笑能有所帮助。 干杯:ABCD 1 2 3 4
纪念
该评论由网站上的主持人最小化
非常好,非常感谢!!!
切赫
该评论由网站上的主持人最小化
很有用的文章,非常感谢
依莫格鲁
该评论由网站上的主持人最小化
精彩的!!! 非常感谢!!!
拉克斯曼纳拉亚南
该评论由网站上的主持人最小化
太好了,非常感谢
尚基
该评论由网站上的主持人最小化
这个工具不错,谢谢分享! 我确实有一个问题:我注意到当您将单元格颜色从一种颜色更改为另一种颜色时,宏公式不会自行更新。 有没有办法让它在单元格颜色发生变化时自动更新? 如果我双击带有加法公式的单元格并按 Enter 键,它会更新,但我有一张包含许多这些单元格的表格,并且不想每次都手动更新它。
大卫·科
该评论由网站上的主持人最小化
谢谢您的回复,很抱歉,在目前的情况下,VBA代码无法解决您指出的问题,需要手动更新。
天阳
该评论由网站上的主持人最小化
我还发现使用格式刷更新单元格颜色会导致公式按预期工作。 仍然不完美。
jako
该评论由网站上的主持人最小化
你好,我试过了。 并且工作表只需要一个“Enter”无论哪个单元格。 因此,在为您的单元格着色后,只需在彩色单元格上键入一个备忘录并“输入”
哈皮球
该评论由网站上的主持人最小化
尝试 CTL+ALT+f9 它将立即使用添加公式更新所有单元格。 不是很自动,但至少比必须单独单击每个带有添加公式的单元格要好。
Louise(英国)
该评论由网站上的主持人最小化
这对我没有任何作用:(
奥利
该评论由网站上的主持人最小化
你好,报错。 编译错误,模棱两可的错误来了。
桑卡尔·纳拉亚南
该评论由网站上的主持人最小化
我应用了公式,但单元格显示名称? 我需要解决的问题
拉蒙
该评论由网站上的主持人最小化
您好 RAMON,可能您没有将上述代码复制到模块中。 您必须先将代码复制到模块中并保存,然后再应用公式。 你可以试试看。 如果对您没有帮助,请告诉我。
天阳
该评论由网站上的主持人最小化
你好,它第一次工作,但是现在,当我必须在总和中添加一个单元格时......我尝试再次引入代码,保存,再次编写公式......它说“名字?”
亚历山德拉
该评论由网站上的主持人最小化
我遇到了同样的问题——起初工作得很好,但在添加另一个单元格后出现了 NAME 错误。 已尝试删除模块并重新添加,重新输入功能...不高兴。
达里娅
该评论由网站上的主持人最小化
您可能需要再次启用 Marcos。 应根据 excel 版本在屏幕顶部闪烁。 :)
加里_9991
该评论由网站上的主持人最小化
Skyyang,如果我给你发一份文件,你能帮我吗? 麦克风
迈克
该评论由网站上的主持人最小化
非常感谢! 这是天才,感谢您的帮助!
肉汁
该评论由网站上的主持人最小化
非常感谢,我整天都在寻找一个成功的结果,现在我们有了一个:)
阿林梅杰夫
该评论由网站上的主持人最小化
谢谢。 如前所述,它对我有用。 但是,在我下次重新打开文件后,使用此公式的所有单元格都显示错误。 我不得不再次重新复制编码。 有什么我想念的吗? 我需要与其他人共享文件,他们将无法“解决”问题。 谢谢,克尔。
克蒂斯
该评论由网站上的主持人最小化
您需要将其保存为 excel 宏工作簿
哈皮球
该评论由网站上的主持人最小化
做得好..但是..它如何在条件格式单元格颜色中工作???
利宾
该评论由网站上的主持人最小化
太棒了,谢谢!
CEZA
该评论由网站上的主持人最小化
它做得很好!
查明达
该评论由网站上的主持人最小化
亲爱的先生,事实上这是很棒的代码,但我遇到了一个问题,即使我们改变了单元格颜色,它也会继续计算单元格
阿里
该评论由网站上的主持人最小化
谢谢先生,这是一项了不起的工作,对我帮助很大。
安妮丝
该评论由网站上的主持人最小化
我有一个包含许多彩色单元格的 Excel 文件。 有没有办法将这些颜色转换为数据? 因此,红色单元格中将包含“红色”作为数据,蓝色单元格中将包含“蓝色”等等? 詹姆士
詹姆斯B
该评论由网站上的主持人最小化
及时而贴切的文章,例如击中正在寻找的目标:)谢谢
斯里兰瓦利
该评论由网站上的主持人最小化
我似乎无法让它工作。 它仅适用于手动着色的单元格吗? 我需要计算通过条件格式着色的单元格,但目前它没有发挥作用。
Ian
该评论由网站上的主持人最小化
我和 Ian 有同样的问题,我试图根据条件格式设置的颜色规则对数字求和,但这似乎没有抓住。 有什么建议么?
瑞安
该评论由网站上的主持人最小化
我们将尝试在即将发布的版本中对其进行增强。 :-)
admin_jay
该评论由网站上的主持人最小化
我抛出了一个 Powershell 脚本,它作为一种解决方法:“ #setup Excel $excelApp = New-Object -comobject Excel.Application $excelApp.Quit() $excelApp.Visible = $True $workbook = $excelApp.Workbooks .Open("H:\Desktop\test.xlsx")#将其更改为您的 EXCEL 文件地址。$worksheet = $workbook.Worksheets.Item("Sheet1")#如果您的工作表未调用“Sheet1”,则更改此#static variables $row = 1 $column = 1#将此变量更改为您要搜索的列 $totalRow = $worksheet.UsedRange.Rows.Count do{ $currentCell = $worksheet.cells.item($row, $column ) if($currentCell.text -eq "SEARCH_FOR_THIS") { $worksheet.cells.item($row,$column).Interior.ColorIndex = 44#改变这个数字来改变新单元格的颜色 "$row, $column =空白。 着色" } $row++ $row } while($row -lt $totalRow) "脚本完成。" "正在保存..." $excelApp.Save $excelApp.Quit() [System.Runtime.Interopservices.Marshal]::ReleaseComObject ($excelApp) " 此脚本将搜索您的电子表格,如果找到包含“SEARCH_FOR_THIS”的单元格(您可能应该更改脚本中的该部分),那么它将将该单元格转换为脚本中的任何颜色编号(当前设置为 44,一种看起来很奇怪的橙色/棕褐色)。 脚本完成后,它会尝试告诉 Excel 它要保存,但 Excel 不是一个非常值得信赖的程序,因此会要求您单击保存。 一旦你这样做了,它就会关闭。 然后,打开文件备份并执行此页面上列出的所有业务,确保我的脚本查找它的颜色与此页面上列出的脚本查找的颜色相同。 希望这可以帮助!
该评论由网站上的主持人最小化
传奇! 谢谢工作的魅力
安迪
这里还没有评论
加载更多
留下你的意见
以访客身份发帖
×
评价此帖子:
0  产品特性
建议地点