Excel 如何计算有颜色的单元格

Excel 如何计算有颜色的单元格

在Excel中，有时我们需要计算具有特定背景颜色的单元格数量。本文将详细介绍三种方法来实现这一功能：使用GET.CELL函数、筛选和分类汇总功能以及VBA代码。

计算有颜色的单元格的方法

在本章中，我们将讨论三种不同的方法来计算Excel中的有颜色的单元格。

1. 使用GET.CELL函数
2. 使用筛选和小计方法
3. 使用VBA（通过创建自定义公式）

在其他网站上，可能还有更多的方法。在这里，我们选择了最简单的方法。

方法1：使用GET.CELL函数

GET.CELL是Excel中特殊类型的函数，可用于计算有颜色的单元格。它基本上是一个宏函数。它的工作方式不同于Excel中的其他常规函数。

GET.CELL函数是通过使用用于创建颜色范围以获取颜色代码的命名区域来工作的。然后，该颜色代码有助于找到有颜色的单元格的数量。

使用GET.CELL函数计算有颜色的单元格的步骤

按照以下说明在Excel工作表中计算有颜色的单元格。通常，需要三个最重要的步骤才能获得结果。

1. 首先，使用GET.CELL函数创建一个命名区域。
2. 现在，使用该命名区域来获取颜色代码。
3. 借助颜色编号（代码），计算有颜色的单元格的总数。

让我们通过一个示例来理解这些步骤是如何执行的。

示例1

步骤1：我们有一个包含一些简单单元格和一些有颜色的单元格的工作表。

现在，我们将详细介绍这三个步骤。

创建色彩范围

步骤 2：转到公式选项卡，并单击功能区中的定义名称选项。

步骤3：将打开一个对话框，在其中输入以下详细信息，然后点击确定：

名称：GetColor
范围：选择范围为工作簿。
引用：
=GET.CELL(38, Sheet1!$A2)

在Refers to字段中，我们使用了GET.CELL()公式，其中第一个参数值为38，它是一个参数代码。这里，38表示信息类型。它指的是您想要从GET.CELL方法中获取哪种类型的信息。在这里，38表示我想要获取背景颜色信息。

第二个参数值为Sheet1!$A2，表示您想要提取信息的位置。您可以定义包含有彩色单元格的列的引用。

获取每个单元格的颜色代码

现在，我们将分别获取每一行的颜色代码，并将它们存储在一个新列中。

步骤4：在每一行的相邻单元格中使用以下公式=GetColor，然后按下Enter键。

如果单元格包含背景颜色，它将返回一个特定的数字。否则，将返回0。

步骤5：请查看下面的截图，黄色的单元格返回了一个代码36。

让我们看看这个公式对于前三个单元格分别具有黄色、绿色和无颜色的情况。在所有相应的行中应用相同的=GetColor公式。

步骤6：对于下一个行号，GetColor返回了0，因为它不包含任何背景颜色。

步骤7：对于背景为绿色的行，GetColor返回的颜色代码数字为50。

同样地，逐个查找所有行的颜色代码并进行最后一步操作。

步骤8：查找包含某些数据的所有行的颜色代码后，查看工作表。

使用颜色代码计算有颜色的单元格数量

最后一步是计算具有任何背景颜色的单元格的总数。所以，请继续。

步骤9:在数据集下创建两个额外的单元格，并将它们着色为与您的工作表已有的相同颜色。如下所示。

步骤10：现在，在新创建的单元格的相邻行中使用以下COUNTIF()公式。

=COUNTIF(G2:G11,GetColor)

步骤11：按下Enter键并查看返回的结果。请注意，它返回了5个黄色单元格。

此Excel工作表中有五行以黄色着色。

步骤12：现在，在绿色单元格的相邻行中应用相同的公式。

=COUNTIF(G2:G11,Getcolor)

步骤13：查看下方输出中以绿色标记的行返回的结果，即2。表示有两行被标记为绿色。

在这个Excel工作表中，有两行被涂上了绿色。

结论：有5行是黄色的，2行是绿色的。因此，这个工作表中一共有7行是有颜色的。

现在，让我们看看下一种方法！

方法2：使用筛选和分类汇总功能

在Excel工作表中计算有颜色的单元格的第二种方法是- 使用筛选和分类汇总，它们是Excel的内置功能。我们将向您展示如何使用它们。使用筛选和分类汇总功能来计算Excel工作表中有颜色的单元格只需要三个步骤。

1. 首先，使用subtotal()函数来计算范围内的所有可见单元格。
2. 然后，对标题应用筛选并对不同颜色的行进行排序。
3. 筛选后，可见的有颜色的单元格的subtotal值将自动更改为新值。找出每种颜色的subtotal，然后将它们相加以获取有颜色的单元格的总数。

例子2

我们将使用以下数据集来应用这些步骤，其中包含一些有颜色的和普通的单元格。

在这个工作表中，使用了两种颜色：黄色和绿色。

步骤1：在数据集下方选择任意单元格，并在其中写入以下公式。

=SUBTOTAL(102,F2:F11)

这里，102表示COUNT()函数，F2:F11是单元格范围。

步骤2：查看使用SUBTOTAL()函数计算出的数据集结果。

步骤3：现在，选择工作表的标题（即用户定义的标题，例如A列），并导航至数据选项卡。

步骤4：在“排序与筛选”部分中，单击Excel功能区中的筛选选项，将该筛选应用于所有标题。

新增下拉类型按钮到表头的每个单元格中，如下所示：

步骤5：点击任何一个筛选器下拉按钮，例如薪水下拉按钮。一个列表会打开，在列表中点击按颜色排序。

由于数据集中使用了两种颜色来突出显示单元格，因此在这里展示这两个颜色以对数据进行排序。

步骤6：选择一种颜色来选择数据，并查看排序后的SUBTOTAL()计算结果的更新值。

首先，我们将选择绿色并查看绿色单元格的总数，即2个。

然后，我们将选择黄色并查看总共有多少个绿色单元格，即5个。

获取两个结果的和（5+2=7）。这意味着在这个Excel表格中总共有七个单元格被着色。因此，使用这种方法，您可以计算Excel工作表中着色行的数量。

方法3：使用VBA代码

上述两种方法是使用Excel的内置方法对Excel数据进行的操作。现在，我们将通过编写VBA代码来计算着色单元格的数量。对于那些习惯于编写代码并且可以使用VBA的人来说，这种方法是最简单的一种。

在上述的两种方法中，我们使用了内置函数。使用VBA，我们现在可以直接创建一个自定义函数来计算着色单元格的数量。它的工作方式类似于COUNTIF方法。看看它是如何完成的：

VBA代码

Function countColoredCells(CountColor as Range, CountRange as Range)
Dim CountColorValue as Integer
Dim TotalCount as Integer
CountColorValue = CountColor.Interior.ColorIndex
Set rCell = CountRange
For Each rCell in CountRange
   If rCell.Interior.ColorIndex = CountColorValue Then
       TotalCount = TotalCount+1
   End If
Next rCell
countColoredCells = TotalCount
End Function

这里，代码的第一行是countColoredCells，这是函数名。这段代码是一个用户定义的函数（UDF），而不是SUB程序来运行。您也可以从这里复制代码并将其写入您自己的代码并运行以计算有色单元格的数量。

此函数的参数

这个用户定义的函数将有两个参数。

Color –我们需要计算的颜色。这意味着提供包含该颜色的单元格的引用，例如F2。
Range –我们希望计算指定颜色的单元格所在的单元格范围，例如D2:D15。

语法

以下是这个创建函数的语法 –

countColoredCells(color, range)

返回什么

这个自定义函数会返回具有特定背景颜色的单元格的总数。

打开Excel VBA编辑器的步骤

以下是进入ExcelVBA编辑器并在其中编写代码的步骤。

步骤1：在活动的Excel工作簿上，按下Alt+F11快捷键，打开VBA代码编辑器，就像这样。

步骤 2：在菜单栏中，导航到插入 -> 模块，这将插入一个新模块以编写代码。

在VBA代码编辑器中，将添加一个名为”模块1（默认）”的模块到当前活动的工作簿中。

步骤3：将上面的用户定义代码复制并粘贴到这个新模块窗口中。

现在，countColoredCells()函数已经添加到此工作表中。

注意：您不需要立即保存VBA代码。当前，只需最小化VBA代码编辑器面板并按照后续步骤进行操作。

步骤4：由于您的工作表包含两种颜色，因此在一个新的列中创建两个单元格，并用这些颜色进行染色，如下所示。

步骤5：现在，首先在H2单元格中使用创建的函数countColoredCells来计算黄色单元格的总数。

=countColoredCells(G2,F2:F11)

步骤6：按下Enter键获取计算结果。它将返回以你选择的颜色着色的单元格数量。

看到它返回值为5，这意味着所选择的范围内有5行被标记为黄色。

步骤7：现在，我们将计算被绿色标记的单元格的数量，使用以下countColoredCells公式。这次选择G3单元格作为绿色标记，并将该公式写入H3单元格。

=countColoredCells(G3,F2:F11)

步骤8:按下Enter键以获得计算结果并查看返回的结果，即2。这意味着在选定的范围内有2个单元格被涂上了绿色。

步骤9：将两个结果相加得到总和。在H4单元格中写入以下加法公式：

=H2+H3并按Enter键。

步骤10：该工作表中的彩色单元格总共有7个。

现在关闭VB编辑器。VBA代码是宏的一个功能。因此，这个功能无法保存在普通的Excel文件（无宏的工作簿）中。当你正常保存时，会弹出一个窗口给你显示。

要保存此文件，请点击No并在文件类型列表中选择启用宏，然后再次保存。