如何按条件统计次数,同一日期只计算一次。兰色不知道这位同学的具体工作,就猜想她是一位漂亮的客服MM,需要统计客户的来访次数。由此,兰色做了一个示例:

【例】下图AB为客户MM做的来访明细表,要求统计每个客户来访问的总天数,相同日期只统计一次,结果如EF列所示。

Office Excel 如何按条件统计次数,同一日期只计算一次

 

分析:这是一个很常见的多条件统计次数难题,估计会自已设置Excel公式的同学很少,因为公式看上去太复杂了。

一、1 次性统计

如果你只是一次性统计结果、数据不再更新。你可以选择用删除重复值和数据透视表的方法完成。

设置方法:

选取表格 – 数据 – 删除重复值

Office Excel 如何按条件统计次数,同一日期只计算一次

 

插入-数据透视表 – 把客户名称分别拖到行标签和数据标签框中。

Office Excel 如何按条件统计次数,同一日期只计算一次

 

二、自动更新的统计

如果源数据不断更新,这就需要设置Excel公式了。不过公式看上去真的有些复杂:

{=COUNT(0/(MATCH(A$1:A43&D2,A$1:A43&B$1:B43,)=ROW(A$1:A43)))}

Office Excel 如何按条件统计次数,同一日期只计算一次

 

这个公式让你怕了吗?客服MM没有,她下定决心要学好这个公式。兰色今天写这篇教程也不只是给个公式,最终目的是教会同学设置多条件计数的套路。静下心,跟兰色一起从0开始设置公式吧。

首先,某行的值是不是重复出现,可以用MATCH验证:

=MATCH(A2,A$1:A43,0)=ROW(A2)

原理:由于MATCH只能查找返回第1个相同的位置(比如在A列查找A3、A4的日期,都是返回A2(该日期第1次出现)的行数,所以再和ROW(单元格)当前行的行数对比,如果相同则是第一次出现返回值TRUE,否则重复出现,返回值FALSE。

Office Excel 如何按条件统计次数,同一日期只计算一次

 

多条件判断怎么办?比如判断客户A的当天是否重复?

只需要让当前行值&“A”,查找区域&客户列

=MATCH(A2&"A",A$1:A43&B$1:B43,0)=ROW(A2)

注:由于A$1:A43&B$1:B43 涉及成组运算,公式最终要用数据形式输入(按Ctrl +Shift +Enter后公式两边自动生成大括号{})

Office Excel 如何按条件统计次数,同一日期只计算一次

 

我们要用一个公式统计出 A的不重复天数,而不是一列公式(如上图C列),所以公式要改造一下:把A2替换成整个区域A$1:A43

=MATCH(A2&"A",A$1:A43&B$1:B43,0)=ROW(A2)

替换为:

=MATCH(A$1:A43&"A",A$1:A43&B$1:B43,0)=ROW(A$1:A43)

选中这个公式按F9,可以看到结果是一个数组,符合条件的值是TRUE,不符合的是FALSE,非A的行是错误值。

Office Excel 如何按条件统计次数,同一日期只计算一次

 

我们最终统计是符合条件(客户A的总天数)的TRUE个数。所以我们就通过任意数字/的方法把0值也转换为错误值。

Office Excel 如何按条件统计次数,同一日期只计算一次

 

最后一步,就需要统计出数字的个数。COUNT函数正好可以完成这个任务

Office Excel 如何按条件统计次数,同一日期只计算一次

 

最终的公式我们也设置出来了:(数组公式)

{=COUNT(0/(MATCH(A$1:A43&D2,A$1:A43&B$1:B43,)=ROW(A$1:A43)))}

Office Excel 如何按条件统计次数,同一日期只计算一次

 

虽然对新手有点绕,但这个不重复计数的老套路很是有用,你看明白了吗?如果客户MM真的掌握这个公式的写法,可以打败你们公司所有Excel高手了。

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注