在实际工作中,我们经常遇到这样的情况,给你一个几千条几万条记录的表格,让你统计每个元素出现的次数,或者是让你统计一下总计有多少个不重复的元素。现在来简单介绍一下怎么处理这个问题,方法学了一次,就可以使用一辈子了。例子如下:
一,统计元素出现次数
使用公式
=COUNTIF($B$2:$B$19,B2)
函数说明:COUNTIF函数主要用于条件计数。
格式:COUNTIF(range,criteria),第一个参数表示范围,可以是B2:B19,第二个参数表示值,可以是数,也可以是字符串;主要作用是统计某个范围内,某个数据出现的总次数。
COUNTIF($B$2:$B$19,B2:B19)将对B2:B19单元格区域中的每个数据进行个数统计,也就是B2:B19单元格区域中B2单元格数值的个数是COUNTIF(B2:B19,B2),B2:B16单元格区域中B3单元格数值的个数是COUNTIF(B2:B19,B3)。。。以此类推,相当于=COUNTIF(B2:B19,{B2;B3;...;B19}),最后的结果就是将每一单元格数值个数组成一个集合{4;3;6;6;...;6)
二,统计不重复元素的个数
我们可以在D2单元格输入如下公式,即可统计出B列B2:B19单元格区域的不重复值的个数。
=SUMPRODUCT(1/COUNTIF($B$2:$B$19,B2:B19))
在具体解释这个公式之前,我们先介绍一个例子:
假如有一个字母序列:ABCCDCABFCADEAECBC....,允许出现重复的元素,那么,怎么统计其中出现了几个不同的元素呢?
程序设计中,常规的做法是创建一个set,把所有元素都扔到set里面去;因为set不允许重复,所以最后只要统计set中元素的个数就好了。但是这种方法比较麻烦,还需要写代码。我们来思考一个巧妙的方法:
假如字母x出现了n次,那么每个单独的x,都是在总量中占据了1/n份;于是,对这n个1/n求和,就得到了1。因为x为任意字母,n为任意数字,所以这种接法适用于任意情况,不论n是1,是2,还是3,或者是10000。牛逼吧,这就是规律。
根据这个规律,我们可以将该问题经过两次转化而轻松解决:
1,求出每个元素出现次数,作为第三列。
2,增加第四列,每个元素出现次数的倒数。通过对倒数求和来得到不重复元素的个数。
第一步可以通过COUNTIF函数来完成;第二步可以通过SUMPRODUCT函数来实现。
函数说明:SUMPRODUCT函数有很多功能,其中一个功能是按照条件进行求和。
回到主题,COUNTIF($B$2:$B$19,B2:B19),就是统计每个元素的出现次数;1/COUNTIF($B$2:$B$19,B2:B19),就是对出现次数求倒数;SUMPRODUCT(1/COUNTIF($B$2:$B$19,B2:B19)),就是对这些倒数求和;^_^。
参考文章:
百科
万能函数Sumproduct能解决4大类统计问题,你相信吗?
原文链接:/GoOnDrift/article/details/109249086