龙空技术网

Excel数据分析技巧之不重复计数

跟表哥学Excel 209

前言:

现时大家对“countdistinct算法”大概比较关心,同学们都想要分析一些“countdistinct算法”的相关资讯。那么小编同时在网络上搜集了一些关于“countdistinct算法””的相关内容,希望各位老铁们能喜欢,我们快快来了解一下吧!

Excel中不重复计数可以说是一个经久不衰的话题。可以用函数解决,也可以用超级透视表解决。本文详细介绍在超级透视表中如何实现不重复计数,并且详细揭示背后的原理。

数据以及函数实现方法

数据如下图:

我们想统计一下有多少不同的售点,和不同的产品。

简单的使用Counta是不行的,必须借助辅助列。在表格最右边添加辅助列,输入公式:

=1/(COUNTIF($C$3:$C$2138,C3))

得到了一列莫名奇妙的小数。

然后这个不重复计数就很简单了:

=SUM(E3:E2138)

当然,你也可以不用辅助列,直接使用数组公式:

{=SUM(1/COUNTIF($C$3:$C$2138,$C$3:$C$2138))}

不管什么方法,理解起来都没有那么直接。

用普通的数据透视表实现

用数据透视表当然可以显示计数,但是这个计数是不对的:

要想用普通透视表实现,必须在创建透视表的时候,选择“将此数据添加到数据模型”

然后将产品添加到值字段,并且修改汇总方式:

点击“确定”后,就得到了产品的不重复计数。

超级透视表中的实现

如果只想简单的得到不重复计数,用上面介绍的普通透视表就可以实现了。但是如果想深入了解不重复计数,并且应用到更多场景下,就需要了解在超级透视表中如何实现不重复计数。

DISTINCTCOUNT

首先,将我们的数据表转换为表格(Table,Ctrl+T转换)。然后基于这个表格超级透视表(具体方法参见这里)

然后在“Power Pivot”选项卡下,创建新的度量值,公式为:

=DISTINCTCOUNT('表2'[产品])

可以看到这个公式的使用跟在Excel中是一样的。

我们来看一看这个度量值出现在什么地方了:

从这里我们可以看到,新建的度量值出现在字段列表中。因此,我们就可以这么理解度量值了:

(我们可以形象的理解为:)实际上,度量值就可以理解为在透视表的数据源上添加了一列(注意,实际不是这么回事,只是便于初学者理解)。作用相当于在透视表字段中加了一个新的字段,就跟原来的数量列一样。

把售点拖拽到行字段中,得以得到每个售点售卖的不同产品个数:

我们还可以看出,在超级透视表中,不重复计数是通过建立新的度量值来实现的。这种方式也带来更加强大的能力。

另外一种不重复计数

为了便于比较,我们先改变透视表的显示内容,将数量拖拽到行字段,得到如下的透视表:

可以看到每个门店每种销量下的不同产品个数都是1,符合我们对不重复的定义。

现在我们增加一个新的度量值如下:

点击确定,然后将新添加的度量值放在值字段中:

得到了新的透视表。你可以看到在新的透视表中。两个不重复计数是不同的。新添加的度量值不管在任何行上,显示的都是本售点的产品的不重复计数。

这是因为我们在计算DISTINCTCOUNT时,使用了ALLEXCEPT筛选函数。

总结

从我们的例子可以看出,使用Power Pivot的不重复技术,可以适应各种各种场景的要求。当然,这就需要用到在创建度量值时的那些函数,要充分发挥超级透视表的功能,就有必要了解这些函数。

像我们今天介绍的DISTHNCTCOUNT,还有用到的CALCULATE,以及ALLEXCEPT都是超级透视表常用的DAX函数。很多人一听函数就头晕,再看到这个陌生的DAX就更晕了。其实都很简单,甚至比Excel中的函数还简单。我们会在后面的章节中详细介绍一些常用的DAX函数,相信大家掌握好会喜欢上超级透视表的。

取得本文模板文件的方式:私信

标签: #countdistinct算法