微软Excel中6种计算彩色单元格的方法

2021-09-07

您可能在Excel数据中使用过颜色编码,或者在必须使用的工作簿中见过。

这是一种可视化数据的流行方式!

虽然彩色单元格是突出显示数据以快速吸引某人注意力的好方法,但它们不是存储数据的好方法。

不幸的是,许多用户会给单元格上色以表示某个值,而不是用该值创建另一个数据点。

例如,他们可能会将单元格涂成绿色,以表示项目已获批准,而不是用文本创建另一个数据点批准

当您实际需要了解有多少项目获得批准时,这会导致很多问题。Excel不提供内置的计算彩色单元格的方法。

在这篇文章中,我将向你展示6种方式查找并计算数据中的任何彩色单元格。

使用“查找并选择”命令对彩色单元格进行计数

Excel有一个很棒的功能,可以根据格式查找单元格。这也包括任何颜色的细胞!

你可以找到某一种颜色的所有细胞,然后计数它们。

去医院首页点击➜找到和选择命令➜ 然后选择发现从选项。

还有一个很好的键盘快捷键。新闻Ctrl+F打开查找和替换菜单。

单击列表中的小向下箭头总体安排按钮并选择从单元格中选择格式

单击页面的主要部分总体安排按钮将打开“查找格式”菜单,您可以在其中选择要搜索的任何格式组合。

如果你知道你要搜索的颜色,这是完美的,但更多情况下,你最好通过举例来设置格式。格式可能会有微妙的不同,这可能会导致您无法找到正确的数据!

当你点击里面的小箭头总体安排按钮,它将显示更多选项,包括通过选择单元格设置格式的能力。

一旦你选择了格式,然后点击“查找全部”按钮。

“查找和替换”对话框的下部将显示所有匹配格式的单元格,在左下方将显示计数。

新闻Ctrl+一个要选择所有单元格,然后按“关闭”按钮,然后可以更改所有这些单元格的颜色或更改任何其他格式。

如果您只想返回给定列或范围中的单元格,这是可能的。在按下“查找全部”按钮之前,选择工作表中的范围以将搜索限制在所选范围内。

赞成的意见

  • 易于使用。
  • 您可以使用它来搜索其他类型的格式,而不仅仅是填充颜色。
  • 可以使用此功能搜索选定范围、整个工作表或整个工作簿。

欺骗

  • 这个解决方案不是动态的,每次想要获得计数时都需要重复。

使用滤镜和小计功能来计数有色单元格

这个方法依赖于您可以基于单元格颜色进行筛选。

第一步,您需要向数据中添加过滤器。

选择您的数据,然后转到数据选项卡,然后选择过滤器命令。

这将为数据的每个列标题添加一个排序和过滤图标,这将允许您以许多不同的方式过滤数据。

还有一个方便的键盘快捷键,可以从数据中添加或删除过滤器。选择您的数据并按下Ctrl+转变+l在你的键盘上。

添加过滤器的另一个选项是将数据转换为Excel表。我写了一篇文章所有关于Excel表格的内容以及它们带来的伟大功能。

您可以使用这两种方法中的任何一种将数据转换为表。

  • 选择数据➜中的一个单元格,转到Insert选项卡➜,单击Table命令。
  • 选择数据中的一个单元格➜按键盘上的Ctrl + T。

默认情况下,你的表格应该带有过滤器。如果没有,请进入表格按下并检查过滤器按钮中的选项表样式选项部分。

=小计(3,订单[订单ID])

现在可以添加上面的内容了小计计算非空单元格的公式,其中订单ID包含要计数的彩色单元格的列。

的第一个论点小计函数告诉它返回一个计数,而第二个参数告诉它计数什么。

这里的特殊技巧是小计函数将只计数可见的单元格,因此它将根据过滤的数据更新计数。

这意味着你可以过滤上的有色细胞,你将得到这些有色细胞的计数!

现在您可以按颜色过滤数据。

  1. 单击包含有色单元格的列的排序和筛选开关。
  2. 选择按颜色过滤从菜单选项。
  3. 选择你想要过滤的颜色。

现在,小计结果将更新,你可以快速找到你的有色细胞计数。

如果调整颜色,请在表中添加或删除数据。您需要重新应用过滤器,因为它们不会动态更新。

去医院数据选项卡并单击重新申请按钮排序和过滤部分。

赞成的意见

  • 易于使用。

欺骗

  • 需要手动过滤数据。
  • 过滤器不会更新,当您更改数据时需要重新应用它们。
  • 由于计数基于筛选,因此在工作簿上协作时,每个用户的结果可能不同。

使用GET.CELL Macro4函数对彩色单元格进行计数

Excel确实有一个获取单元格填充颜色的函数,但它是一个传统的Macro 4函数。

它们比VBA更早出现,是基于Excel公式的脚本语言。

虽然它们被认为已弃用,但仍然可以在名称管理器中使用它们。

有一个得到的。细胞函数,该函数将根据单元格的填充颜色返回颜色代码。

属性可以创建一个使用该属性的相对命名范围公式选项卡,点击定义的名字

这将打开New Name菜单,您可以定义引用。

给你的定义名a的名字喜欢ColorCode.这是您在工作簿中引用它的方式。

=。单元格(38,订单[@[订单ID]])

将上述公式添加到参考部分。对于这个公式,您的数据将需要在一个名为订单列名为订单ID,但您可以更改这些以适应您的数据。

这个公式将始终引用它所引用的当前行中的Order ID单元格。

=GET.CELL(38,$B3)

如果数据不在表中,则可以使用上述公式,其中B是包含要计数的填充颜色的列。这使用固定列和相对行引用来始终引用当前行的列B。

= ColorCode

通过定义名称,您现在可以在数据中使用上面的公式创建另一列,以计算每一行的颜色代码。

结果将是基于中单元格的填充颜色的整数值订单ID列。

=COUNTIFS(订单[颜色代码],B14)

现在,您可以使用上面的COUNTIFS公式计算彩色单元格的数量。

此公式将计算ColorCode列中具有匹配代码的单元格数。在本例中,它统计所有10与绿色相对应的值。

赞成的意见

  • 您可以计算每一行数据的填充颜色,当您更改数据或数据的填充颜色时,填充颜色将动态更新。

欺骗

  • 此方法使用Macro4遗留函数,它们可能不会继续支持我的微软。
  • 难以实现。
  • 您需要将工作簿保存为启用xlsm宏的格式。
  • 如果在命名范围内使用列表示法,则不能移动引用的列。
  • 如果在命名范围内使用表表示法,则不能更改列名。
  • 您需要创建一个额外的列,并使用COUNTIFS函数来获取计数。

使用LAMBDA函数来计数彩色单元格

这将使用与前面方法相同的GET.CELL Macro4函数,但您可以创建自定义LAMBDA函数在工作簿中使用它。

LAMBDA函数是一个特殊函数,允许您通过名称管理器构建自定义函数。

这是一个新功能,所以它不是普遍可用的,你需要在写这篇文章的时候在微软365办公室内部程序。

转到“公式”选项卡,单击“定义名称”,打开“新名称”菜单。

= LAMBDA (cell, GET。CELL (38, CELL))

给定义名一个类似GETCOLORCODE的名称,并将上面的公式添加到部分。

这将创建一个新的GETCOLORCODE函数,您可以在工作簿中使用它。它将接受一个名为cell的参数并返回cell的填充颜色代码。

=GETCOLORCODE([@[Order ID]])

现在,您所要做的就是创建一列,使用上述公式计算每行的颜色代码。

=COUNTIFS(订单[颜色代码],B14)

同样,您可以使用上面的COUNTIFS公式来计算单元格的数量或颜色。

赞成的意见

  • 您可以构建一个函数来计算给定单元格的颜色代码。
  • 允许您直接引用单元格以获得颜色代码。

欺骗

  • 此方法使用Excel中的LAMBDA函数用于M365,目前还不能普遍使用。
  • 难以实现。
  • 此方法使用今后可能不支持的Macro4遗留函数。
  • 您需要将工作簿保存为启用xlsm宏的格式。
  • 需要创建额外的列并使用COUNTIFS函数来获取计数。

使用VBA计数彩色细胞

函数COLORCOUNT (CountRange范围,FillCell范围)暗FillColor作为整数昏暗算作整数FillColor每个c CountRange如果c.Interior.ColorIndex = = FillCell.Interior.ColorIndex FillColor然后数=计数+ 1结束如果下c COLORCOUNT = Count函数结束

赞成的意见

  • 您可以创建一个函数,对某个范围内的彩色单元格进行计数。
  • 当您编辑数据或更改填充颜色时,结果将更新。
  • 用户友好的选择使用一旦它被设置。

欺骗

  • 使用VBA,要求文件以xlsm格式保存。
  • 用户设置不太友好。

使用Office脚本计算彩色单元格

Office脚本是Excel中自动化任务的全新方法。

但它只适用于Excel在线和企业计划。如果您有一个企业计划,那么您可以使用这种方法!

首先,您需要设置两个代码将引用的命名单元格。

选择任意单元格并键入类似的名称色数进入名称框,然后按输入. 这将创建一个可在代码中引用的命名范围。

这意味着我们可以移动单元格,代码将引用它的新位置。

您还需要创建一个颜色命名范围为输入的颜色计数。

现在,您可以创建一个新的office脚本。去医院自动化选项卡,单击新的脚本命令。

function main(workbook: ExcelScript.Workbook) {let selectedSheet = workbook. getworksheet ("Sheet1");让myID = selectedSheet。getRange(“订单(订单ID)”);让myIDCount = selectedSheet。getRange(“订单(订单ID)”).getCellCount ();让myColorCode = selectedSheet.getRange("Color").getFormat().getFill().getColor();让counter = 0;令I = 0;我< myIDCount;i++) {if (myID。getCell(i, 0).getFormat().getFill().getColor() == myColorCode ) { counter = counter + 1; } } selectedSheet.getRange("ColorCount").setValue(counter); }

这将打开新的窗口代码编辑器你可以粘贴上面的代码并保存脚本。

运行按钮,代码将执行,然后填充色数命名范围与发现的彩色细胞计数订单ID列。

赞成的意见

  • 这是最新的方法,今后将得到支持。
  • 该脚本可以从Power automation中运行,以实现无单击解决方案。

欺骗

  • 这种方法很难建立起来。
  • 这需要企业许可证,您需要在线使用Excel。

结论

希望微软有一天能创建一个标准的Excel函数,它可以返回单元格的属性,比如填充颜色。

在这之前,至少您有一些可用的选项,允许您在不手动计数的情况下查找彩色单元格的计数。

你是否有这里没有列出的首选方法?请在评论中告诉我!

关于作者

约翰MacDougall

约翰MacDougall

John是Microsoft MVP、自由职业顾问和培训师,擅长Excel、Power BI、Power Automation、Power Apps和SharePoint。你可以在John的博客或YouTube频道上找到其他有趣的文章。

订阅

广告

相关文章

评论

0评论

提交评论

您的电子邮件地址将不会被公布。必填字段已标记

这个网站使用Akismet来减少垃圾邮件。了解如何处理您的评论数据

获取最新消息

关注我们

在社交媒体上关注我们,了解Excel中的最新提示!

把它钉在Pinterest上

分享这