本帖最后由 东门吹雪 于 2012-9-3 09:50 编辑
“不重复数统计”在EXCEL中就象爱情一样，是个永恒的主题。 对于筛选不重复数，大多采用技巧“高级筛选－选择不重复记录”、“合并计算”等方法，2010版中又新增了“删除重复项”工具，使得这一工作大大简化。 而对于统计不重复数，大多采用公式“COUNTIF(区域，区域)”、“MATCH(区域，区域，)”和“FREQUENCY(区域，区域)”来解决。 我还曾在博客文章“ 带条件不重复数统计”中，举了一个多条件不重复统计的例子，使用的是2007版之后新增函数COUNTIFS。 今天遇到的是一个新的问题，忽略隐藏区域对不重复数进行统计。 如图所示，共有十个数据，A列设置了“自动筛选”，现在想统计的是在A列筛选状态下，B列数据的不重复个数。
对于隐藏区域，首先想到的函数是SUBTOTAL，此函数可以忽略隐藏行，进行数据统计。但是SUBTOTAL函数只可进行11种简单的数据统计，其中并不包括条件统计COUNTIF。如下图，使用公式“=SUBTOTAL(3,B2:B11)”显然没能得到“不重复数统计”，B列有两个24被重复统计了。
现在的重点是能否忽略隐藏行，得到一个新的数组{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),))”并不会得到一个求和值，而是得到了十个。
此公式中SUM并不能忽略隐藏行，但SUBTOTAL可以，而且SUBTOTAL也可以求和，我们用SUBTOTAL替换掉SUM。
剩下的问题就好解决了，公式 “=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),))
忽略隐藏区域的不重复统计.zip
(7.5 KB, 下载次数: 49)
| 
IP卡
狗仔卡
发表于 2012-8-29 08:09
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
抢沙发
千斤顶
显身卡
发表于 2012-8-29 08:28
发表于 2012-11-29 17:57
