工作中有时会遇到根据单元格格式,统计数据个数或汇总的要求,比如下图案例,要求统计表格中涂红色数据的个数,并对涂色数据进行汇总:
常用方法:宏表函数get.cell(63,ref)
因为我们不知道涂色的规律,所以无法利用规律走捷径,只能用单元格是否涂色作为判断条件。常用的方法就是利用宏表函数get.cell,第一个参数63用来返回填充色值,函数用法为get.cell(63,ref),ref为单元格或单元格区域,案例具体用法为:
1、鼠标选中A12单元格,点击菜单公式——定义名称——输入名称(gs,可随自己意愿取)——引用位置:输入公式=get.cell(63,sheet1!a2:e10)——确定。
2、鼠标框选a12:e20,在编辑栏输入公式=gs,按住ctr键回车,得到辅助区域数据。
3、利用辅助数据进行统计:统计个数,H2单元格输入公式:=COUNTIF(A12:E20,3)
统计合计,H3单元格输入公式:=SUMIF(A12:E20,3,A2:E10)
过程视频:
对于熟练掌握了这个函数的人来说,用起来也不难。但对多数人来说,这个函数不好理解,容易出错。因为宏表函数只能通过设置公式而不能直接在单元格使用,而且公式对引用的目标单元格在单元格公式里无法直接看到,和一般函数用法习惯不同,增大了理解掌握的难度,很多人都觉得太复杂,不喜欢用。
升级用法:自定义函数heji(range,range,n,k)
处理数据,简单才是硬道理。今天给大家分享一个自定义函数,专门针对根据单元格格式(填充色、字体色)统计汇总数据。只需提前导入文末提供的代码,就能在单元格直接使用,不需辅助数据。而且同一个函数只需修改一下参数,就可实现计数、累计两种功能。来一起看看。
使用本自定义函数实现上面宏表函数实现的涂红色数据计数、汇总功能,公式很简单:
统计个数:heji(A2:E10,A2,1,1)
数据汇总:heji(A2:E10,A2,1,2)
来看看参数用法说明,函数共有4个参数,分别为:
第一个参数A2:E10:要统计的数据区域;
第二个参数A2:指定目标条件单元格。
第三个参数:可选1或者2。1:根据目标单元格填充色进行统计。2:根据目标单元格字体颜色进行统计。可扩展,增加3、4.....。
第四个参数:可选1或者2。1:计数统计。2:数据累加汇总。可扩展。
知道了函数的用法,我们来看一下实际使用效果。
1、想统计填充色为黄色的个数和汇总数,只需将公式中的第二个参数修改为B2即可,因为B2填充色就是黄色,当然第二个参数也可改为C2或D2都行。
2、想统计字体为红色的个数和汇总数,将第2个参数修改为数据区域中的想统计的字体颜色的某个单元格,再将第三个参数改为2即可。
总结
自定义函数heji()的优点:
1、解决了宏表函数不能直接在单元格使用的不足,将公式变得直观,容易理解掌握。
2、设置目标单元格参数,将公式功能调整变得很方便灵活。
3、设置第三、四个参数,实现一个函数完成不同的任务,而且还可扩展实现更多功能。
完整代码
打开代码编辑器,插入模块,在模块里粘贴下面的代码,就可以在表格里使用该函数了。
Public Function heji(rg As Range, rg1 As Range, k1 As Integer, k As Integer)
Dim di()
n = rg.Rows.Count
m = rg.Columns.Count
n1 = 0: n2 = 0
For i = 1 To n
For j = 1 To m
If k1 = 1 Then
If Cells(rg.Row + i - 1, rg.Column + j - 1).Interior.Color = rg1.Interior.Color Then
n1 = n1 + 1
n2 = n2 + Cells(rg.Row + i - 1, rg.Column + j - 1).Value
End If
ElseIf k1 = 2 Then
If Cells(rg.Row + i - 1, rg.Column + j - 1).Font.Color = rg1.Font.Color Then
n1 = n1 + 1
n2 = n2 + Cells(rg.Row + i - 1, rg.Column + j - 1).Value
End If
End If
Next
Next
Select Case k
Case 1
heji = n1
Case 2
heji = n2
End Select
End Function