1. 小媛啾首页
  2. 技术教程
  3. excel使用小技巧

Excel小技巧– 用一行公式计算不重复资料数量,公式看不懂该怎么办?

平常我在Excel 里大多是在计算针对某个值的重复数量是多少,第一次有人问我:不重复的资料有几笔要怎么算,我思考了一下,大致上有二种做法,计算有资料的储存格数量有多少,再去扣掉重复的资料,或者找出重复值有多少,再利用一点点逻辑运算就可以搞定,今天要教大家的就是第二个方法,顺便训练一下大家脑袋计算能力,后面再加码一下,如果平常看别人的公式不太理解时,该怎么拆解看每步骤的值,往下来看。

Excel教程:计算不重复资料数量

首先,我们要做的就是像这样,从一整排资料里计算出不重复的资料有哪些,往下来看。

Excel小技巧– 用一行公式计算不重复资料数量,公式看不懂该怎么办?

小媛啾用的公式长这样

=SUMPRODUCT(1/COUNTIF(C2:C13,C2:C13))

一行,二个函数搞定,分开来看大家应该都知道这二个函数的用途,但这样合起来的意思是什么呢,我们往下来拆解一下。

Excel小技巧– 用一行公式计算不重复资料数量,公式看不懂该怎么办?

一般大家在拆解公式时,一定会先从最内层开始一个个看结果,比如COUNTIF 那个函数是用来计算指定条件的数值,像这样二个参数都放C2:C13,代表的是每个值都会比对过一次,最后获得的是5,代表有5 个值是相同的,到这里还算简单,就可以用计算储存格有资料数量的函数COUNTA 之后再减掉COUNTIF 那个函数后+ 1 (加这个1 是因为有五个值相同,所以算成1 个,所以我们要加回来1 个)。

公式会变成,这是第二种做法,当然这是题外话,我们继续往下来看第一个做法的拆解。

=COUNTA(C2:C13)-COUNTIF(C2:C13,C2:C13)+1

Excel小技巧– 用一行公式计算不重复资料数量,公式看不懂该怎么办?

在第一个公式里,有段1/COUNTIF(C2:C13,C2:C13),为什么要用1 来除,直接解说大家会看不懂,我们往下来教大家,如果看不懂公式时该怎么拆解步骤。

Excel小技巧– 用一行公式计算不重复资料数量,公式看不懂该怎么办?

点选要拆解公式值的那一栏位后,进入公式> 选择「评估值公式」。

然后就会跳出一个框框,里面会是你输入的公式,按下评估值,往下来看。

Excel小技巧– 用一行公式计算不重复资料数量,公式看不懂该怎么办?

当你按第一下时,会帮你把最内层的公式化成值显示出来,里面变成1;5;1;1;1;1;5;5;1;1;5;5,回想一下COUNTIF,C2:C13 就是循环代进去比对值相同有几个,第一笔资料是1,只有1 个,所以是1,第二笔相同的有5 个所以是5,以此类推,看懂了之后,我们往下再按一次评估值。

Excel小技巧– 用一行公式计算不重复资料数量,公式看不懂该怎么办?

?会看到计算了第二层,也就是用1 去除,里面的5 通通变成了0.2,最后用SUMPRODUCT 全加起来,答案就是8,大家有看懂了吗?

思考一下第二个做法,是将重复的值找出来后去扣掉再加1,而这个做法其实就是反过来,我们用1 除了哪些重复的值数量,不论你值重复几次,只要用1除了之后再全部加回来,还是会等于1,有5 个值重复就会出现5 个0.2,加起来等于1,再加上原本的7 个1 不就是8 了吗?

当你遇上公式有点看不懂会变成什么的时候,就用评估值公式这个功能一个个按下去,就会知道每一层的结果啰。

Excel小技巧– 用一行公式计算不重复资料数量,公式看不懂该怎么办?

发布者:yuanyuan,转转请注明出处:https://www.xiaoyuanjiu.com/12973.html