在做客户分析的时候经常会遇到客户数的计算: 是两个不同的概念,一段时间内购买商品的客户数是一个需要去掉重复项的计数,因为可能存在,同一位客户多次购买的情况;一段时间内购买商品的次数,是一个简单的计数,统计过往销售的次数。 今天我们的问题就是如何计算,一段时间内购买商品的客户数? 两个小问题: 通常大家比较关注如何使用公式来计算出结果,Excel中编写公式来计算非重复计数,对于初学者来说是个不小的难题,其实也不用太纠结与公式的原理,重在结果。 不懂原理又要能计算出结果,最好的办法就是模仿别人的公式,网上搜索一下,就会有很多答案,可以找来几个模仿一下。 我认为有两种公式可以拿来试一试: 这两个组合各有特色,SUMPRODUCT组合不用三键的数组公式,但是需要选择好区域,不然会出错;COUNT组合需要用CTRL+SHIFT+ENTER三键数组公式,不用太在意选取区域。 第一个小问题:客户数,相当于没有条件来计算非重复客户计数。 =SUMPRODUCT(1/COUNTIFS(B2:B23,B2:B23)) =COUNT(0/(MATCH(B2:B23,B2:B23,)=ROW(1:22))) 两个组合都能够计算出正确结果,但是使用SUMPRODUCT要注意数据区域选取。 第二个问题:A、B商品的购买客户数 =SUMPRODUCT(($C$2:$C$23=I7)/COUNTIFS($B$2:$B$23,$B$2:$B$23,$C$2:$C$23,$C$2:$C$23)) =COUNT(0/(($C$2:$C$23=I7)*MATCH($B$2:$B$23&$C$2:$C$23,$B$2:$B$23&$C$2:$C$23,)=ROW($1:$22))) 根据条件来计算客户的非重复计数: 条件的写法也不同,一种是等于单值的样子,一种是全范围选取。 Power Query中计算非重复计数 第一个问题:客户非重复计数 第一步:删除其他列,添加自定义列 第二步:分组 第二问题:购买A、B类客户的非重复计数 删除其他列只保留,客户名称和商品分类,分组 Power Query中计算非重复计数,要比用公式简单的多,只是简单的操作就可以得到结果,当然,后台使用的是M函数Table.Group、Table.Distinct、Table.RowCount 不用管函数是如何运作的,只要知道在分组操作中,选取非重复行计数,就能够获得想要的结果。 Power Pivot 中计算非重复计数 Power Pivot中只需要写一个度量值就可以了,在Power Pivot中由专门的分重复计数的DAX函数DISTINCTCOUNT,也可以通过COUNTROWS+VALUES组合函数来计算非重复计数。 客户数:=DISTINCTCOUNT([客户名称]) 客户数1:=countrows(VALUES('表1'[客户名称])) 这个度量值的结果,可以通过透视表来检验: 总结一下,对销售数据进行分析,Excel中最好的办法就是使用Power Pivot来建模分析,所以非重复计数,DAX函数最简单,其次是Power Query中的分组操作,最后才是Excel公式,大家可以根据自己的应用场景来选择非重复计数的方法。 也许不久的将来,微软也会给Excel添加Distinct函数,来进行非重复计数 |
|