新浪博客

21•只要我的惟一---忽略隐藏行不重复数统计

2012-08-24 11:02阅读：

http://blog.sina.cn/dpool/blog/u/2897079124

“不重复数统计”在EXCEL中就象爱情一样，是个永恒的主题。
对于筛选不重复数，大多采用技巧“高级筛选－选择不重复记录”、“合并计算”等方法，2010版中又新增了“删除重复项”工具，使得这一工作大大简化。
而对于统计不重复数，大多采用公式“COUNTIF(区域，区域)”、“MATCH(区域，区域，)”和“FREQUENCY(区域，区域)”来解决。
我还曾在博客文章“带条件不重复数统计”中，举了一个多条件不重复统计的例子，使用的是2007版之后新增函数COUNTIFS。
今天遇到的是一个新的问题，忽略隐藏区域对不重复数进行统计。
如图所示，共有十个数据，A列设置了“自动筛选”，现在想统计的是在A列筛选状态下，B列数据的不重复个数。
21•只要我的惟一---忽略隐藏行不重复数统计

对于隐藏区域，首先想到的函数是SUBTOTAL，此函数可以忽略隐藏行，进行数据统计。但是SUBTOTAL函数只可进行11种简单的数据统计，其中并不包括条件统计COUNTIF。如下图，使用公式“=SUBTOTAL(3,B2:B11)”显然没能得到“不重复数统计”，B列有两个24被重复统计了。
21•只要我的惟一---忽略隐藏行不重复数统计
现在的重点是能否忽略隐藏行，得到一个新的数组{62;24;24;57;63}，如果能得到这样一个数组，剩下的事儿就好办多了，只需动用MATCH或FREQUENCY函数即可统计不重复。
可SUBTOTAL虽然能够忽略隐藏行，但天生没有罗列数据的功能，用了它就必须对数据进行“求和、平均、计数”等统计。
了解函数三维引用的人应该知道，对不同维度的内存数组进行“统计”，实际上是无效的，相当于罗列数据。
如上图中的5个数据，分处不同维度，用SUM求和，实际上并不能把5个数据加在一起，求和实际上只是分别对五个维度的数据进行求和，如果每个维度的数据只有一个，那么求和就相当于罗列数据。
有了这样的思路，剩下的就是想办法把上图5个数据弄成5个维度了。
这需要三维引用公式“OFFSET(B1,ROW(1:11),)”，原数据区域一共有10个数据，ROW(1:11)确保数据由B1单元格向下移动10次，第一次ROW(1)时，B1移动1位，变成B2；第二次ROW(2)时，向下移动2位，变成B3；……第10次也就是ROW(11)时，向下移动10位，变成B11。
因为OFFSET函数的第二个参数使用了数组，因此这是一个三维引用公式，移动10次，就好似建立了十个层次（维度），如果对这十个层次求和，并不能把十层的数据都加在一起，而是分别对十层求和，应该得到十个求和值。
如输入数组公式“=SUM(OFFSET(B1,ROW(1:10),))”并不会得到一个求和值，而是得到了十个。
21•只要我的惟一---忽略隐藏行不重复数统计
此公式中SUM并不能忽略隐藏行，但SUBTOTAL可以，而且SUBTOTAL也可以求和，我们用SUBTOTAL替换掉SUM。
21•只要我的惟一---忽略隐藏行不重复数统计
剩下的问题就好解决了，公式
“=SUM((FREQUENCY(SUBTOTAL(9,OFFSET(B1,ROW(1:10),)),SUBTOTAL(9,OFFSET(B1,ROW(1:10),)))>0)*1)-1”即可得出不重复数统计。只所以最后要减去1，是因为核心数据“OFFSET(B1,ROW(1:10),)”中包含了那么被忽略的数据区域，这些区域的数据因为被SUBTOTAL忽略，所以求和值为0，FREQUENCY函数会统计这些0有多少个。因此最后减1就没错了。
由于B列数据区域很特别，都是大于0，小于99的数据，因此，公式可以做些简化。把FREQUENCY函数的第一个参数改为ROW(1:99)，最后公式可写成
=SUM((FREQUENCY(ROW(1:99),SUBTOTAL(9,OFFSET(B1,ROW(1:10),)))>0)*1)-1
三维引用可以解决问题，最后的公式也不至于太长太复杂，但理解起来确实不容易，尤其对于初学者来说。在EXCEL2010版中，又新增了一个忽略隐藏行的函数AGGREGATE，这个函数在忽略隐藏行的同时也要进行统计，但它的统计多了LARGE和SMALL，因此可以突破“仅罗列未被隐藏数据”的瓶颈。
核心公式是“AGGREGATE(14,5,B2:B11,ROW(1:10))”，14代表LARGE统计；5代表忽略隐藏行；ROW(1:10)是LARGE函数的第二个参数，也就是第1至第10个最大值。
最终公式简化为
=SUM((FREQUENCY(ROW(1:99),AGGREGATE(14,5,B2:B11,ROW(1:10)))>0)*1)-1
或
=SUM(IFERROR(N(MATCH(ROW(1:99),AGGREGATE(14,5,B2:B11,ROW(1:10)),)>0),)) 21•只要我的惟一---忽略隐藏行不重复数统计

我的更多文章

下载客户端阅读体验更佳

APP专享