[开_153]显示筛选后的唯一属性（开帖待总结）

l8588173

1. =IF(SUM(IFERROR(FIND(OFFSET(B2,MATCH(1,SUBTOTAL(3,OFFSET(B2,ROW(1:24),0)),0),),IF(SUBTOTAL(3,OFFSET(B2,ROW(1:24),0,,)),B3:B26,),1),0))=SUM(SUBTOTAL(3,OFFSET(B2,ROW(1:24),0,,))),LOOKUP(1,0/SUBTOTAL(3,OFFSET(B2,ROW(1:24),0)),B3:B26),"")

复制代码

数组公式

补充内容 (2017-11-28 15:18):
=IF(AVEDEV(IF(SUBTOTAL(3,OFFSET(B2,ROW(1:24),))>0,MATCH(B3:B26,B3:B26,))),"",LOOKUP(1,0/SUBTOTAL(3,OFFSET(B2,ROW(1:24),)),B3:B26))
数组公式

丢丢表格

1. =IFNA(LOOKUP(MODE(IF(SUBTOTAL(3,OFFSET(A2,$A3:$A26,)),MATCH(B3:B26,B3:B26,)*{1,1,0,0})),$A:B),"")

复制代码

anonym

1. =IF(SUM(COUNTIF(OFFSET(B2,SUBTOTAL(5,OFFSET(A2,ROW(1:24),)),),OFFSET(B2,SUBTOTAL(5,A3:A26),)))=SUBTOTAL(3,A3:A26),OFFSET(B2,SUBTOTAL(5,A3:A26),),"")

复制代码

niko88819

{=IF(COUNT(0/(MATCH(B3:B26,IF(SUBTOTAL(3,OFFSET(B2,ROW(1:24),)),B3:B26),)=ROW(1:24)))=1,OFFSET(B2,MATCH(1,SUBTOTAL(3,OFFSET(B2,ROW(1:24),)),),),"")}

sdc3211772

=IF(OR(IF(B3:B26=OFFSET(B2,SUBTOTAL(5,$A:$A),),,SUBTOTAL(3,OFFSET($A2,ROW(1:24),)))),"",OFFSET(B2,SUBTOTAL(5,$A:$A),))

sdc3211772

不懂规则，重复撤回

nsxlyj

=IF(COUNT(1/(MATCH(D3:D30,IF(SUBTOTAL(3,OFFSET(D2,ROW(1:28),)),D3:D30),)=ROW(1:28)))=1,OFFSET(D2,SUBTOTAL(4,$A3:$A30),),"")
数组公式，右拉完成。

袁勇

常规思维：

1. =IF(COUNT(0/(MATCH(B3:B26,IF(SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),B3:B26),)=$A3:$A26))=1,LOOKUP(1,0/SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),B3:B26),"")

复制代码

horselyq

1. =OFFSET(B2,SUM(QUARTILE(IF(SUBTOTAL(3,OFFSET(B2,ROW(1:24),)),MATCH(B3:B26,B3:B26,)),{4,4,0})*{1,99,-99}),)&""

复制代码

mmlzh

水平有限，公式较长，先放上，老师检查是否正确。有更好的再改。
B2

1. =IFERROR(LOOKUP(MATCH(SUBTOTAL(3,A3:A26),FREQUENCY(SUBTOTAL(3,OFFSET(A2,ROW(1:24),))*MATCH(B3:B26,B3:B26,),ROW(1:25)-1),)-1,$A:$A,B:B),"")

复制代码

1. =IF(MAX(SUBTOTAL(3,OFFSET($A2,ROW(1:24),))-(B3:B26=OFFSET(B2,SUBTOTAL(4,$A:A),))),"",OFFSET(B2,SUBTOTAL(5,$A:A),))

复制代码

1. =IFERROR(VLOOKUP(SUM(SUBTOTAL(4,OFFSET($A2,ROW(1:24),))*MATCH(B3:B26,B3:B26,))/SUBTOTAL(9,$A:$A),$A:B,COLUMN(B1),),"")

复制代码

1. =IF(AVEDEV(IF(SUBTOTAL(3,OFFSET(A2,ROW(1:24),)),MATCH(B3:B26,B3:B26,))),"",OFFSET(B2,SUBTOTAL(5,$A:$A),))

复制代码

1. =IF(DEVSQ(IF(SUBTOTAL(3,OFFSET(A2,ROW(1:24),)),MATCH(B3:B26,B3:B26,))),"",OFFSET(B2,SUBTOTAL(5,$A:$A),))

复制代码

1. =IFERROR(VLOOKUP(GEOMEAN(IF(SUBTOTAL(3,OFFSET(A2,ROW(1:24),)),MATCH(B3:B26,B3:B26,))),$A:B,COLUMN(B1),),"")

复制代码