rps指标公式word自动筛选后不重复计数

2020年10月1日发(作者：戴敦邦)
自动筛选后的不重复计数
小编这里提供两种办法：
一、此方法过于复杂实在太难（不能理解请选第二种）
“不重复数统计”在EXCEL中就象爱情一样，是个永恒的主题。
对于筛选不重复数，大多 采用技巧“高级筛选－选择不重复记录”、“合并计算”等方法，
2010版中又新增了“删除重复项” 工具，使得这一工作大大简化。
而对于统计不重复数，大多采用公式“COUNTIF(区域 ，区域)”、“MATCH(区域，区域，)”
和“FREQUENCY(区域，区域)”来解决。
我还曾在博客文章“带条件不重复数统计”中，举了一个多条件不重复统计的例子，使用
的是2007版之后新增函数COUNTIFS。
今天遇到的是一个新的问题，忽略隐藏区域对不重复数进行统计。
如图所示，共有十个数据， A列设置了“自动筛选”，现在想统计的是在A列筛选状态下，
B列数据的不重复个数。


对于隐藏区域，首先想到的函数是SUBTOTAL，此函数可以忽略隐藏行，进行 数据统计。
但是SUBTOTAL函数只可进行11种简单的数据统计，其中并不包括条件统计COUN TIF。
如下图，使用公式“=SUBTOTAL(3,B2:B11)”显然没能得到“不重复数统计 ”，B列有两个24
被重复统计了。


现在的重点是能否忽略隐 藏行，得到一个新的数组{62;24;24;57;63}，如果能得到这样一
个数组，剩下的事儿就 好办多了，只需动用MATCH或FREQUENCY函数即可统计不重复。
可SUBTOT AL虽然能够忽略隐藏行，但天生没有罗列数据的功能，用了它就必须对数
据进行“求和、平均、计数” 等统计。
了解函数三维引用的人应该知道，对不同维度的内存数组进行“统计”，实际上是无 效的，
相当于罗列数据。
如上图中的5个数据，分处不同维度，用SUM求和，实际 上并不能把5个数据加在一
起，求和实际上只是分别对五个维度的数据进行求和，如果每个维度的数据只 有一个，那么
求和就相当于罗列数据。
有了这样的思路，剩下的就是想办法把上图5个数据弄成5个维度了。
这需要三维引用公式“ OFFSET(B1,ROW(1:11),)”，原数据区域一共有10个数据，
ROW(1:11) 确保数据由B1单元格向下移动10次，第一次ROW(1)时，B1移动1位，变成
B2；第二次RO W(2)时，向下移动2位，变成B3；……第10次也就是ROW(11)时，向下移
动10位，变成 B11。
因为OFFSET函数的第二个参数使用了数组，因此这是一个三维引用公式，移动 10次，
就好似建立了十个层次（维度），如果对这十个层次求和，并不能把十层的数据都加在一起，< br>而是分别对十层求和，应该得到十个求和值。
如输入数组公式“=SUM(OFFSE T(B1,ROW(1:10),))”并不会得到一个求和值，而是得到了
十个。


此公式中SUM并不能忽略隐藏行，但SUBTOTAL可以，而且SUBTOTA L也可以求和，
我们用SUBTOTAL替换掉SUM。


剩下的问题就好解决了，公式
“=SUM((FREQUENCY(SUBTOTAL(9, OFFSET(B1,ROW(1:10),)),SUBTOTAL(9,OFFSET(
B1,RO W(1:10),)))>0)*1)-1”即可得出不重复数统计。只所以最后要减去1，是因为核心数据“OFFSET(B1,ROW(1:10),)”中包含了那么被忽略的数据区域，这些区域的数据因为被
SUBTOTAL忽略，所以求和值为0，FREQUENCY函数会统计这些0有多少个。因此最后< br>减1就没错了。
由于B列数据区域很特别，都是大于0，小于99的数据，因此，公式 可以做些简化。把
FREQUENCY函数的第一个参数改为ROW(1:99)，最后公式可写成
=SUM((FREQUENCY(ROW(1:99),SUBTOTAL(9,OFFSE T(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),))

二、此方法分两种情况讨论：（统计姓名不重复数）
（1）中间没有隐藏行如图

行的序号都是连续的：
只需要输入使用公式=SUM(1COUNTIF(E6954:E6 964,E6954:E6964))然后必须按
下Ctrl+Shift+Enter(数组公式)不 然会出现错误。


（2）中间有隐藏行的如图：（统计姓名不重复数）

在打开一个excel表格将上述数据复制到里面去（其实只需要将姓名行复制过去
就行了）， 中间的隐藏行自然就不存在了，在利用（1）中的方法就可以统计隐藏
行的不重复数了。