|
|
楼主 |
发表于 2015-8-12 01:00
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
本帖最后由 13732120571 于 2015-8-12 14:42 编辑
第八篇 单条件提取不重复项&多条件计数1
例题取自本论坛去重复再加双条件取不重复个数
http://club.excelhome.net/thread-1205185-1-1.htm
FREQUENCY——单条件提取不重复项&多条件计数
一、去重复
1、 LOOKUP+FREQUENCY+COUNTIF
1） COUNTIF(I$8:I8,C$4:C$325) ={0;0;0;……;0}
C$4:C$325在I$8:I8区域中的个数，在I$8:I8下拉时，已经提取的客户来源的为1，否则返回0
2） (A$4:A$325=K$1) ={FALSE;FALSE;……;FALSE;TRUE;……;TRUE;FALSE;……;FALSE}
A$4:A$325日期是否和K$1的日期一样，是的返回TRUE、不是返回FALSE；在参与加减乘等计算时，TRUE相当于值1、FALSE相当于值0
3） (A$4:A$325=K$1)-COUNTIF(I$8:I8,C$4:C$325) ={0;0;……;0;1;1;……;1;1;1;0;0;0;……;0}
加权处理：符合A$4:A$325=K$1（TRUE=1）和和未被提取过COUNTIF(I$8:I8,C$4:C$325)=0的结果为：TRUE-0 =1-0 =1
符合A$4:A$325=K$1（TRUE=1）和已经提取过的COUNTIF(I$8:I8,C$4:C$325)=1的结果为：TRUE-1 =1-1 =0
不符合A$4:A$325=K$1（FALSE=0）和未被提取过的COUNTIF(I$8:I8,C$4:C$325)=0的结果为：FALSE-0 =0-0 =0
不符合A$4:A$325=K$1（FALSE=0）和已经提取过的COUNTIF(I$8:I8,C$4:C$325)=1的结果为：FALSE-1 =0-1 =-1（不会出现已经提取过这种情况，所以不用考虑）
4） FREQUENCY(1,(A$4:A$325=K$1)-COUNTIF(I$8:I8,C$4:C$325)) ={0;0;……0;0;0;1;0;0;……;0}
FREQUENCY 参数一的值1 在 参数二(A$4:A$325=K$1)-COUNTIF(I$8:I8,C$4:C$325)第一个为1 的值并 再相对位置计数为1
如果参数二没有值1，1都大于参数二所有值，那么在参数二多1个的位置计数1。可以用到后续容错处理
5） 0/FREQUENCY(1,(A$4:A$325=K$1)-COUNTIF(I$8:I8,C$4:C$325)) ={#DIV/0!;#DIV/0!;…………;;#DIV/0!}
0/是为FREQUENCY产生的结果计数为1的返回0，计数0的返回错误值#DIV/0!
6） LOOKUP(, =LOOKUP(0,
参数省略写法lookup参数一的值，实际值为0。
7) )&""
把引用的真空单元格落地值变成空文本""
8) C$4:C5 =C$4:C65536
lookup参数三的扩展性
C$4:C5&"" 不等同C$4:C$65536&""(这个属于数组形式，不是单元格区域，不能扩展）
《lookup向量形式 》扩展性及向量方向问题
9） LOOKUP(,0/FREQUENCY(1,(A$4:A$325=K$1)-COUNTIF(I$8:I8,C$4:C$325)),C$4:C5)&"" ="内部员工-NC"
用lookup函数用0值定位参数二唯一的0值，并引用参数三现对位置的单元格，用&"" 把引用单元格变成文本 真空单元格的落地值0变成""
一般查找最后值都用lookup(1,0/；经测试，在这个例子中用0比1去查找唯一值0更快，原因未研究出来。
2、 LOOKUP+FREQUENCY+MATCH
思路和COUNTIF一样，就是处理加权的方式有点不一样。这里就不详细解说了
优点：MATCH相对COUNTIF速度快很多
二、多条件不重复计数
1、 COUNT+FREQUENCY+ROW
1) MATCH(B$4:B$325&"",B$4:B$325&"",) ={1;2;2;2;2;6;7;8;9;10;10;……;319;319;319;322}
用MATCH函数求B$4:B$325&""分别在B$4:B$325&""位置的值（自然数）。&"" 容错处理，MATCH真空会返回错误值#N/A。
2） (A$4:A$325=K$1)*(C$4:C$325=J9) ={0;0;0;0;0;……;0;0;1;1;1;0;0;0;0;1;1;1;1;1;1;0;……;0;0}
日期A$4:A$324等于K$1并且顾客来源C$4:C$324等于J9的返回1，否则返回0
3） MATCH(B$4:B$325&"",B$4:B$325&"",)*(A$4:A$325=K$1)*(C$4:C$325=J9) ={0;0;……;0;0;0;32;32;32;0;0;0;0;39;40;41;41;……;0;0}
位置数（自然数）相对符合日期和顾客来源这两个条件的返回原值，否者返回0
4） ROW(A:A) ={1;2;3;4;5;……;65536}
ROW最小值大于0且ROW最小值小于等于MATCH查找的位置数最小值，ROW最大值大于MATCH查找的位置数
5） FREQUENCY(ROW(A:A),MATCH(B$4:B$325&"",B$4:B$325&"",)*(A$4:A$325=K$1)*(C$4:C$325=J9))
={0;……;0;32;0;0;0;0;0;0;7;1;1;0;0;3;0;0;0;4;1;0;0;0;4;0;0;……;0;0;65466}
用FREQUENCY函数把ROW(A:A)的值分别在符合条件的位置数上计数，参数二相同的值只计数在第一个出现的值的位置
ROW(A:A)大于参数二所有值的计数在FREQUENCY最后一个多出来的位置（这个位置肯定大于0值）
6） 0/FREQUENCY(ROW(A:A),MATCH(B$4:B$325&"",B$4:B$325&"",)*(A$4:A$325=K$1)*(C$4:C$325=J9))
={#DIV/0!;……;0;#DIV/0!;……#DIV/0!;0;#DIV/0!;0}
FREQUENCY结果出来大于0的为0，等于0的返回错误值#DIV/0!
7） COUNT(0/FREQUENCY(ROW(A:A),MATCH(B$4:B$325&"",B$4:B$325&"",)*(A$4:A$325=K$1)*(C$4:C$325=J9)))-1 =12
用COUNT忽略错误值、文本统计纯数值个数，这里是统计0值的个数。
COUNT(……)-1 =减去FREQUENCY结果最后一个多余的计数（多加一个，再减一个）。
下面的就当做习题 单条件不重复项
******如何提取不重复的规格？******
http://club.excelhome.net/thread-1209549-1-1.html
(出处: ExcelHome技术论坛)
FREQUENCY——习题
|
|
[复制链接]
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2015-6-27 13:04
发表于 2015-6-27 14:16
