关键词不能为空

当前您在: 主页 > 高中公式大全 >

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

作者:高考题库网
来源:https://www.bjmy2z.cn/gaokao
2020-10-01 20:31
tags:筛选重复数据公式

妈妈的作文-高中物理试卷

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)中的方法就可以统计隐藏
行的不重复数了。

浙江省高考作文-写给


西安高考复读学校-杭怎么读


are的否定形式-沈阳市职业技术学院


大一新生对大学的感受-星期六的英文


近视眼怎么自然恢复-回环的修辞手法


负指数幂的运算法则-服务正能量的句子


2013年江西高考-金考卷答案


比例尺的三种形式-涧的意思



本文更新与2020-10-01 20:31,由作者提供,不代表本网站立场,转载请注明出处:https://www.bjmy2z.cn/gaokao/409213.html

word自动筛选后不重复计数的相关文章