[Excel]不同颜色单元格该如何分别计数?

在Excel表格中,会有童鞋喜欢给不同的内容标上不同的色块以示区分。但问题就来了,到底该怎么对不同的色块进行简单统计,比如求个和、计个数之类的呢? 本篇将介绍三种方法来对不同底色的单元格进行计数操作: 1) 使用 筛选SUBTOTAL 函数 2) 使用 GET.CELL函数 3) 使用

01 筛选+SUBTOTAL

该法包含了两部分:

  • 基于不同底色,对单元格进行筛选

  • 使用SUBTOTAL函数对可见的单元格计数(筛选后)

    假设,我们现在有这么一个原数据表格,其中分别有绿色和橙色两种颜色的高亮单元格。接着就看下,该如何实现对不同颜色单元格的计数。

使用SUBTOTAL函数

  • 在数据下方的单元格中输入公式命令:=SUBTOTAL(102,E1:E20)

  • 公式中102代表,计数但忽略隐藏值,往下走你就能明白它的厉害之处。

根据单元格背景颜色,进行筛选

一旦你按照单元格颜色筛选后,就能看到下图的效果:

  • 使用SUBTOTAL函数的计数结果变成了4,忽略了其他筛掉的单元格。

  • 使用COUNT函数的计数结果依然维持在19。

02 GET.CELL

首先要说明的是,GET.CELL是一个在早期Excel使用的函数。主要是为了提取单元格相关的属性参数,在目前的Excel版本中必须利用“定义名称”功能进行使用。

创建一个定义名称

  • 点击 公式->定义名称

  • 在弹出的对话框中输入以下信息: 名称: GetColor 范围:可以使用默认的 工作薄 引用位置:=GET.CELL(38,’COUNT USING GET.CELL’!$A2) 38在这里意味着提取的是单元格的背景色(具体此参数的其他设置法,在此就不展开了),而’COUNT USING GET.CELL’!$A2则表示在这张’COUNT USING GET.CELL‘表中以A列为绝对引用。

在每行末尾单元格尝试下GetColor的效果

在F列输入=GetColor这么一个公式,结果就是没有背景色的返回值为0,橙色的返回值为40,绿色为50。

利用COUNTIF+GetColor计算不同颜色的单元格

在B22/B23单元格中分别输入=COUNTIF($F$2:$F$20,GetColor),最终便计算出绿色数量为3,橙色数量为4。

  • 为什么可以这样呢? COUNTIF函数利用GetColor这个自定义的名称作为判断条件,在提取了A22/A23单元格的背景色参数后,对比F2:F20这个区域的参数。

03 VBA

你得先利用VBA创建一个自定义函数,然后将以下代码加到一个新的模块中: Function GetColorCount(CountRange As Range, CountColor 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 GetColorCount = TotalCount End Function 这个名为GetColorCount的自定义函数有两个参数,分别:

  • CountRange是为了来定义需要计数某颜色单元格的区域

  • CountColor则是为了确定这一颜色 在单元格G3中输入=GetColorCount($A$2:$A$20,G3),也就是说需要在A2:A20这个区域找到与G3背景色相同的单元格数量。

三招你都掌握了吗?