Excel实战:统计非重复数据个数

非重计数

本期依旧实战分享。

群友提问:怎么用公式统计一列不重复的规格型号有多少个,除了透视之外,有没有一个公式可以直接统计出来的。

统计

源数据有好几万条,示例简单点,13条就好。

如上图,我们先肉眼统计一下,方便核对答案。

5分钟后......

不行了,眼花了,还是直接写公式吧。


仔细审题

公式惯例第一步:辅助列。

第1列 countif,

第2列 1/countif,

第3列 iferror,

第4列 sum。

打完收工!

公式 ???

特意删除重复数据,检查下结果:这不很对嘛,可以交卷了......

草率了

这就很尴尬了......

百度一下

算了,偷个懒,百度一下,糊弄糊弄得了。

百度

这不挺简单嘛

SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))

数据好几万条,把A一千改成A十万,不就OK?

于是结果:#DIV/0!

#DIV/0!......

为啥不对呢,改成精确区域试试,查看源数据有37271条数据,于是修改公式为:

SUMPRODUCT(1/COUNTIF(A:A,A1:A37271))-1

这下出结果了。


就这?

上面的公式显然不好,还得先查看下数据条数,再手动修改公式,这波操作还不如用透视,要它何用!

赶紧改进公式。改进前的几个小问题:

为啥把A一千改成A十万,会报错?

怎么定位最后一条数据位置?

定位的结果如何嵌入到公式中?

结果为啥减去1?

尚待解决的问题

灵光一闪,想到最开始的辅助列操作,有一步是屏蔽错误值——空单元格,导致“A十万”报错。

如何定位最后一条数据?

MAX(IF(A:A="",0,ROW(A:A))

思路:是空单元格,返回0;不是空单元格,则返回行号;那么最大的行号即为最后一条数据的位置。

如何将定位结果嵌入公式?

EVALUATE函数。

为啥减1?

略。

最终公式为:

=SUM(IFERROR(1/COUNTIF(A:A,EVALUATE("A1:A"&MAX(IF(A:A="",0,ROW(A:A))))),0))-1

数组公式,三键结束输入。

结语

不禁感想:为啥原本一个很简单的问题,最后公式会变得这么复杂?

啊,是懒啊!

推荐阅读更多精彩内容