实用Excel技巧分享:两个神仙技巧,带你看破统计不重复数的秘密!

时间:2023-05-05
阅读:

如何统计不重复数据的个数?估计很多小伙伴也看过不少类似的文章,但大多都是给出公式并稍微讲解一番,当时看了是懂了,等遇到问题的时候又懵了,归根结底还是没明白这公式的原理。其实理解这个公式的原理,并没有大家想象的那么难,只要你会这两个神仙技巧,就可以破解公式的秘密了。

实用Excel技巧分享:两个神仙技巧,带你看破统计不重复数的秘密!

统计不重复数据的个数,相信不少小伙伴在工作中都遇到过这样的问题。

通常的做法都是先把不重复的数据提取出来,再去统计个数。而提取不重复数据的方法之前也分享过,基本有三种方法:高级筛选、数据透视表和删除重复项。

其实使用公式来统计不重复数据的个数也是很方便的,例如下图中的这种情况,需要统计出不重复的客户数:

实用Excel技巧分享:两个神仙技巧,带你看破统计不重复数的秘密!

通常统计不重复数据个数的公式有两种套路,今天就先和大家分享第一种套路的原理。

套路1:SUMPRODUCT与COUNTIF的组合

首先来看看公式的输入过程:

实用Excel技巧分享:两个神仙技巧,带你看破统计不重复数的秘密!

操作并不难,难的是很多人不理解公式=SUMPRODUCT(1/COUNTIF(B2:B23,B2:B23))的原理。

单个函数都理解,合到一起就蒙圈,相信这是很多初学者都有的感受。其实要理解这个公式的原理,并没有大家想象的那么难,只要你会使用一个叫公式求值的工具和一个叫F9的功能键,就可以破解公式的秘密了。下面就来介绍具体的过程。

选中公式所在的单元格,点击公式求值这个按钮。

实用Excel技巧分享:两个神仙技巧,带你看破统计不重复数的秘密!

这个功能的用法很简单,如图所示:

实用Excel技巧分享:两个神仙技巧,带你看破统计不重复数的秘密!

有下划线的部分表示即将显示出结果的位置,从图中可以看出,首先要计算的是COUNTIF(B2:B23,B2:B23)这部分,点“求值”看看能得到什么结果。

实用Excel技巧分享:两个神仙技巧,带你看破统计不重复数的秘密!

我们得到了一组数字,表示每一个客户代码出现的次数,例如第一个5,就表示42337这个客户出现了五次,这也是COUNTIF最基本的功能。

实用Excel技巧分享:两个神仙技巧,带你看破统计不重复数的秘密!

继续点“求值”,可以看到1/COUNTIF的结果,如图所示:

实用Excel技巧分享:两个神仙技巧,带你看破统计不重复数的秘密!

1除以COUNTIF得到的每个数字,都是一些小数,例如出现五次的,得到的结果就是1/5,也就是0.2。

这一步纯属数字运算,理解起来没什么难度。再点求值就可以得到最终结果了。

实用Excel技巧分享:两个神仙技巧,带你看破统计不重复数的秘密!

这个5是怎么来的?

这是被问得最多的一个问题,其实说白了,还是数学问题。

例如42337这个客户,一共出现了5次,这是COUNTIF计算出来的结果。接着再用1/,就得到5个0.2,再求和变成1。

这个计算过程才是公式的核心所在,每个客户最终求和结果都是1,全部加起来就是不重复客户的个数了。

以上是使用公式求值破解公式原理的步骤,对于一些老司机来说,更喜欢用F9这个功能键去破解公式。

有必要说明的一点是,对于某些键盘来说,必须在按住Fn键的同时再去按F9才行。

下面就说说F9该怎么用。

要使用F9,就得了解公式中计算的顺序,或者说,你想了解哪一部分,就得在编辑栏选中对应的内容后,按F9键。

例如要理解COUNIF这部分,就可以这样操作:

实用Excel技巧分享:两个神仙技巧,带你看破统计不重复数的秘密!

对于F9的使用,精准的选择计算内容很重要,多一个括号都会提示错误,例如不小心选到最后一个括号,按F9就会提示公式有问题。

实用Excel技巧分享:两个神仙技巧,带你看破统计不重复数的秘密!

使用F9显示结果后,可以点击编辑栏左边的✖,或者按Esc键退出。如果不慎按下了回车,要返回原公式的话可以用撤销,或者Ctrl+Z组合键。

套路2:COUNT和MATCH的组合

这个公式的难度就稍微有点大了,一起看看操作过程。

实用Excel技巧分享:两个神仙技巧,带你看破统计不重复数的秘密!

这个公式是数组公式,完成输入后记得按Ctrl+Shift+回车键,公式两边会自动出现大括号。

公式=COUNT(1/(MATCH(B2:B23,B2:B23,0)=ROW(1:22)))用到了三个函数,COUNT、MATCH和ROW,不管操作还是原理,这个公式都要难于第一个公式。

那么为什么还要介绍这个公式呢?

这是因为在这个公式中,用到的一些思路和方法,会在很多强大的公式中反复遇到的,因此理解第二个套路,有助于提升公式运用的能力。

言归正传,还是用公式求值来破解这个公式的原理吧。

实用Excel技巧分享:两个神仙技巧,带你看破统计不重复数的秘密!

简单来说MATCH有三个参数,查找值、查找区域和查找方式,公式得到的是查找值在查找区域内首次出现的位置,点一下求值就能看到结果。

实用Excel技巧分享:两个神仙技巧,带你看破统计不重复数的秘密!

还是看客户42337,一共出现了五次,MATCH函数得到的结果都是1,说明这个客户首次出现的位置是1。

要强调一下,这个1是查找范围中的位置,我们的查找范围是从第二行开始的。

对于MATCH得到的这一组数据,一定要理解其含义。继续点求值可以得到ROW这部分的结果。

实用Excel技巧分享:两个神仙技巧,带你看破统计不重复数的秘密!

ROW可以得到参数对应的行号,例如ROW(A1),结果就是1,而ROW(1:22),得到的就是前22行的行号,也就是1到22这一组数字。

注意公式MATCH(B2:B23,B2:B23,0)=ROW(1:22)中的范围是不同的,MATCH是2到23行,实际是22行数据,而ROW的范围是以实际数据的行数为依据的。

继续点求值,公式会判断MATCH得到的这一组数据与ROW得到的这一组数据是否一致,结果是一组逻辑值。

实用Excel技巧分享:两个神仙技巧,带你看破统计不重复数的秘密!

从结果可以发现,每个客户首次出现的位置,公式结果就是TRUE。

这里有必要普及一下逻辑值的知识了。

在Excel中有六个比较的符号,=(等于)、>(大于)、(小于)、>=(大于或等于)、(小于或等于)、(不等于),本例中用的是等于。

比较的结果就是逻辑值,逻辑值有两个,分别是TRUE和FALSE,TRUE表示结果正确,FALSE表示结果不正确。

例如1>2,这个比较的结果就是FALSE。

逻辑值在和数字进行加减乘除等运算时,TRUE相当于1,FALSE相当于0。

实用Excel技巧分享:两个神仙技巧,带你看破统计不重复数的秘密!

在这一步计算中,就是用数字1和这一组逻辑值进行计算,当分母为TRUE时,1/1得到1;当分母为FALSE时,1/0会得到错误值,分母为零。

点求值就能看到这个结果。

实用Excel技巧分享:两个神仙技巧,带你看破统计不重复数的秘密!

如果理解了以上原理,最后的结果就很好理解。

因为COUNT只做一件事,统计有几个数字。在这一组结果中,只有五个1是数字,因此最终的结果就是5。

实用Excel技巧分享:两个神仙技巧,带你看破统计不重复数的秘密!

很多时候,1/都用0/代替了,也许这是高手们的一种习惯吧。

当你真正的懂得了公式原理之后,1/和0/将不会再是造成你困扰的原因。

对第二个公式的原理就分析到这了,在这个公式中,用到了很多高级公式常用的技能,例如使用ROW得到一个数组,使用各种比较运算得到一组逻辑值,进而通过对逻辑值的计算得到一些错误值(错误值并不是一点用都没有哦)。而使用0/还是1/,除了一部分特殊情况之外,大多数情况是没有区别的。

好了,对于统计不重复数据个数的两个公式分析就告一段落,如果你还遇到什么无法破解的公式,可以留言告诉小编,咱们一起来搞明白。

返回顶部
顶部