小课堂:销售数据去重后统计数量,这个公式套路千万记住
今日目标:
昨天遇到这样一个问题,让我很头疼。
头疼的原因有3点:
1- 问题描述的不清晰,理解起来困难
2- 去重复计算数量,函数公式实现难度大
3- 提问的是个男生
我尝试着,把问题精简了一下,是这样的,你就凑活着看吧。
一列「用户ID」,一列「活动日期」,现在想统计,每个用户参加活动的天数。
因为用户可能在1天中参加多次活动,所以要根据「用户ID」对「活动日期」去除重复,然后在计数。
明白了吗?
明白了,咱们就开始干!
方法1
关于去除重复计数,也就是统计唯一值,Excel中有一个经典的用法,使用SUMRODUCT和COUNTIF/COUNTIFS函数完成
= SUMPRODUCT(1/COUNTIF(统计区域,统计区域))
现在看不懂没关系,我们通过这个案例,一起走一遍这个过程。
1- COUNTIFS统计数量
首先是统计数量,因为这里有「用户ID」「活动日期」两列数据,所以我们用COUNTIFS函数。
完成公式如下:
=COUNTIFS(C2:C16,C2:C16,B2:B16,H2)
计算结果
= {0;0;0;0;0;0;0;0;0;0;0;1;1;1;1}
注意这里有一个数组的用法,在判断条件的参数中,使用数组那么计算的结果,也是对应数量的数组。
2- 数量求倒数
接下来,用1除以计数结果,获取对应的倒数。
完成公式如下:
=1/COUNTIFS(C2:C16,C2:C16,B2:B16,H2)
计算结果
= {#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;1;1;1}
因为1/0会出现#DIV/0!的错误,所以公式外面,在加一个IFERROR容错。
=IFERROR(1/COUNTIFS(C2:C16,C2:C16,B2:B16,H2),0)
计算结果
={0;0;0;0;0;0;0;0;0;0;0;1;1;1;1}
这一步是非重复计数的关键操作,结合下一步倒数求和,会更容易理解。
3- SUMPRODUCT倒数求和
因为SUM函数不支持数组操作,所以这里使用SUMPROUDCT进行求和。
完成公式如下:
=SUMPRODUCT(IFERROR(1/COUNTIFS(C2:C16,C2:C16,B2:B16,H2),0))
计算结果
= 4
到这一步,你可能就明白求倒数的意义了。
如果相同数据出现了2次,那么计数过程就是1/2 + 1/2 =2,如果出现了3次,就是3个1/3相加=3,其他次数以此类推,即实现了非重复计数。
4- 增加用户ID判断
但是上一步计算结果,显然错的,QY1的去重计数,应该是1才对啊。
这是因为计数的过程,没有对用户进行限制,以内QY1有10/4的记录,所有的10/4都被统计到QY1用户上了。
所以需要再增加一个用户条件的判断,这里使用EXACT函数实现。
完整公式如下:
=EXACT(B2:B16,H2)
计算结果
={FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}
EXACT的作用,是判断两个数值是否相等。
因为EXACT中也是引用了区域,所以计算结果是一个TRUE和FALSE的数组。
接下来,是把EXACT的计算结果,作为条件添加到前面的公式中,方法很简单,和第1步的计数过程相乘就可以了。
完成公式如下:
=SUMPRODUCT(IFERROR(1/COUNTIFS(C2:C16,C2:C16,B2:B16,H2)*EXACT(B2:B16,H2),0))
计算结果
=1
这样就把非当前用户的统计给去掉了,也就得到了最终的结果。
方法2
方法1是传统的方法,经过一番折腾,最终算是圆满完成了需求,但是正如你所感受到的。
传统函数公式的思路太古怪,一般人很难想到用1/次数的方法,来做去重计数。
这也是函数公式难学的主要原因。
正常用户的思路,不应该是这样的嘛?
1- 筛选用户ID
2- 去除重复值
3- 统计数量
这个正常的思路,用传统公式是很难实现的。
但是Excel365中新增的FILTER和UNIQUE函数,让这个过程变的简单,变的正常了。
1- 筛选用户ID
使用新增的FILTER函数,可以轻松的根据「用户ID」筛选对应的记录。
完整公式如下:
=FILTER($C$2:$C$16,$B$2:$B$16=H4)
计算结果
={43739;43739;43739;43740;43741;43742}
FILTER的作用就是筛选符合条件的记录(日期返回的是数字格式,所以变成了43739的样子)。
2- 去除重复值
Excel365中新增的UNIQUE函数,就是用来去除重复值的。
完整公式如下:
=UNIQUE(FILTER($C$2:$C$16,$B$2:$B$16=H4))
计算结果
={43739;43740;43741;43742}
注意到了吗?FILTER筛选出来的重复值,被UNIQUE函数一下子去除掉了。
3- 统计数量
有了去重后的筛选结果,统计数量太简单,就是普通的COUNTA函数嘛。
完整公式如下:
=COUNTA(UNIQUE(FILTER($C$2:$C$16,$B$2:$B$16=H4)))
计算结果
= 4
简单的3个步骤,符合常规思路,你肯定一下子就学会了,不是吗?
案例文件下载
关注公众号:拉小登,后台回复:SJ869,下载本节案例。
总结
温馨提示
目前FILTER和UNIQUE函数,只有Office365的版本才有,而且需要参加「预览版体验计划」。
FILTER、UNIQUE教程
之前我也专门写过FILTER和UNIQUE的教程,点击下面的图片,就可以进入学习。
UNIQUE函数,参考这篇文章
FILTER函数,参考这篇文章
今天的非重复计数学会了吧,别忘了点「在看」,签到打卡!
我是拉小登,一个会设计表格的Excel老师