Excel表格中不重复单元格的个数(Excel怎么统计表格中相同内容的个数)
看看下面这个问题,一看就是一个高难度的问题,一个彩票号码中不重复的个数,B2为啥是5?A1单元格的彩票号9376343,除了3重复之外,9,7,6,4都不重复,在加上3,一共是五个数字。
牛闪闪估计也搞不定,还好这位同学的老师牛,出了一个招,牛闪闪也学习了一下,不敢独享,与大家分享一下。
场景:彩票分析,重复项统计、数据分析等的办公人士。
问题:如何统计单元格中不重复的数字个数?
解答:利用count find函数嵌套搞定
具体操作如下:
直接在下图C4单元格,输入如下公式:
=COUNT(FIND(ROW($A$1:$A$11)-1,B4))
注意公式中A1:A11的范围正好是1-11这个数值范围。为什么呢?过会牛闪闪给大家解释。
输入该公式后,需要按住Ctrl Shift,然后在敲击“Enter回车键”即可。(Ctrl Shift enter是数组公式的录入按键),敲击完成后,注意公式会前后两段加上大括号{}。(下图箭头处)大括号是说明该公式为数组公式。
输入完毕后,双击C4单元格右下角的数据填充柄(上图箭头1所示),即可完成公式复制,算出每行B列彩票的不重复的数字个数。大家可以验证一下这个数值是否正确。
我们需要搞清楚这个公式的用意,才能做到举一反三。
=COUNT(FIND(ROW($A$1:$A$11)-1,B4))
上面这个公式用了三个函数,先来看看ROW函数。
ROW返回单元格所在的行数,举例如下,求B25单元格所在的行数,那肯定是25。
那ROW($A$1:$A$11)是什么鬼?
如果你输入进去试试,你会发现行数为1.效果如下图:
也就是说ROW正常情况下只取第一个单元格的行数,那为什么要选择区域呢?主要为了数组公式配合。那是数值公式,简单的讲就是按数据区域(面)来进行统计的方式。如果按照数组的思路,那么ROW(A1:A11),就能统计出A1到A11单元格每一行的行数,即1,2,3,4,5,6,7,8,9,10,11。
那ROW($A$1:$A$11)-1,是什么就是每一行都减去1行,就变成0,1,2,3,4,5,6,7,8,9,10。其实这个函数主要为了产生1-10的一个数字。
接下来学习一下FIND函数。FIND函数主要是取得某个数在某个单元格的开始位置。举例如下:
比如要找6这个数字在B4单元格中数值的位置,返回是4。表明6在9376343这个数字的从左往右数是第四个。
FIND(ROW($A$1:$A$11)-1,B4)
那么把ROW嵌套到FIND里面,无非就是为了告诉FIND,要找0,1,2,3,4,5,6,7,8,9,10在单元格中的位置。如果这个数字有,就会返回一个位置数字。
=COUNT(FIND(ROW($A$1:$A$11)-1,B4))
而在外面在套一个count函数,就是对有位置的数字进行计数统计,从而算出不重复的数字的个数。牛闪闪用“公式求值功”能来追踪一下,
选中下图C4单元格,点击“公式—公式求值”,在新弹窗口中点击“求值”,就可以进行逐步运算。
看,分别寻找0-10,在B4中的位置,分别返回位置数。
在继续点求值,就会出下下图效果,可以看到0,1,2在9376343中没有,所以是#value!(错误值)。3在9376343中为第二位,所以返回是2,4在在9376343中为第六位,所以是6,以此类推算出能在9376343找到的数字的位数。
最后利用count函数的特性,只统计数字的个数,自动忽略错误值,所以算出不重复的数字的个数。如下图所示:
如果你理解的话,你会发现上面的公式写得不是那么完美,其实只要判断0-9的数字就可以了,干嘛要到10呢。所以把公式改改:把A1:A11改成A1:A10也是可以的,记住将光标停在编辑栏,在按Ctrl Shift enter键进行公式录入,才有效。
甚至可以直接改成数组的写法,记得用大括号括起来,这样直接敲回车enter就可以录入公式。
总结:这个案例是不是有点小难度,但如果你理解数组其实按区域面积进行统计后,你就会发现其实想象中那么难。通常简单函数的“高端”用法就是数组思路解决问题。希望对大家有所帮助。