word自动筛选后不重复计数.doc

自动筛选后的不重复计数 小编这里提供两种办法： 一、此方法过于复杂实在太难（不能理解请选第二种） “不重复数统计”在EXCEL中就象爱情一样，是个永恒的主题。 对于筛选不重复数，大多采用技巧“高级筛选－选择不重复记录”、“合并计算”等方法，2010版中又新增了“删除重复项”工具，使得这一工作大大简化。 而对于统计不重复数，大多采用公式“COUNTIF 区域，区域 ”、“MATCH 区域，区域， ”和“FREQUENCY 区域，区域 ”来解决。 我还曾在博客文章“带条件不重复数统计”中，举了一个多条件不重复统计的例子，使用的是2007版之后新增函数COUNTIFS。 今天遇到的是一个新的问题，忽略隐藏区域对不重复数进行统计。 如图所示，共有十个数据，A列设置了“自动筛选”，现在想统计的是在A列筛选状态下，B列数据的不重复个数。 2012-8-29 08:05 上传 下载附件 23.48 KB 对于隐藏区域，首先想到的函数是SUBTOTAL，此函数可以忽略隐藏行，进行数据统计。但是SUBTOTAL函数只可进行11种简单的数据统计，其中并不包括条件统计COUNTIF。如下图，使用公式“ SUBTOTAL 3,B2:B11 ”显然没能得到“不重复数统计”，B列有两个24被重复统计了。 2012-8-29 08:06 上传 下载附件 20.04 KB 现在的重点是能否忽略隐藏行，得到一个新的数组 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 , ”并不会得到一个求和值，而是得到了十个。 2012-8-29 08:07 上传 下载附件 39.13 KB 此公式中SUM并不能忽略隐藏行，但SUBTOTAL可以，而且SUBTOTAL也可以求和，我们用SUBTOTAL替换掉SUM。 2012-8-29 08:07 上传 下载附件 43.86 KB 剩下的问题就好解决了，公式 “ 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