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

准提部林

也佔個樓層～～
數組．１０６字：大約測，未多做驗證
=IFERROR(OFFSET(B2,AVERAGE(IF(SUBTOTAL(3,OFFSET($A2,ROW(1:24),)),MATCH(B3:B26,B3:B26,)^3))^(1/3)&".",),"")　
　
逃不過浮點．１１５字：
=IFERROR(OFFSET(B2,ROUND(AVERAGE(IF(SUBTOTAL(3,OFFSET($A2,ROW(1:24),)),MATCH(B3:B26,B3:B26,)^3))^(1/3),9)&".",),"")
　
換個，１０８字：
=IFERROR(OFFSET(B2,(HARMEAN(IF(SUBTOTAL(3,OFFSET($A2,ROW(1:24),)),MATCH(B3:B26,B3:B26,)+1%%))-1%%)&".",),"")
　
　

jivy1212

=IF(AVERAGE(CODE(T(OFFSET(B2,AGGREGATE(15,5,$A$3:$A$26,ROW(INDIRECT("1:"&SUBTOTAL(2,A3:$A$26)))),))))=CODE(OFFSET(B2,SUBTOTAL(5,$A$3:$A$26),)),OFFSET(B2,SUBTOTAL(5,$A$3:$A$26),),"")数组公式右拉181字节，暂时只有又长又臭的，AGGREGATE经过提醒才用到，不然没思路

Look我在这

重在参与，咳咳咳

1. =IF(SUM(LEN(SUBSTITUTE(SUBSTITUTE(T(OFFSET(B2,SUBTOTAL(3,OFFSET(A2,ROW(1:24),))*ROW(1:24),)),INDEX(B:B,2+SUBTOTAL(4,$A:$A)),),B2,))),"",INDEX(B:B,2+SUBTOTAL(4,$A:$A)))

复制代码

user23

=INDEX(B:B,RIGHT(AVERAGE(IF(SUBTOTAL(2,OFFSET($A2,ROW(1:24),)),MATCH(B3:B26,B3:B26,)^5))^0.2+2,4))&""
=OFFSET(B2,RIGHT(AVERAGE(IF(SUBTOTAL(2,OFFSET($A2,$A3:$A26,)),MATCH(B3:B26,B3:B26,)^5))^0.2,5),)&""


补充内容 (2017-12-1 16:04):
=IFNA(OFFSET(B2,MODE(QUARTILE(IF(SUBTOTAL(3,OFFSET(A2,$A3:$A26,)),MATCH(B3:B26,B3:B26,)),{0,4})),),"")

补充内容 (2017-12-1 16:05):
=IFNA(OFFSET(B2,MODE(AGGREGATE({14,15},6,MATCH(B3:B26,B3:B26,)/SUBTOTAL(3,OFFSET(A2,$A3:$A26,)),1)),),"")

wn_li

1. =IF(VAR.P(IF(SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),MATCH(B3:B26,B3:B26,))),"",OFFSET(B2,SUBTOTAL(5,$A:A),))

复制代码

七夕、

1. =OFFSET(B2,SUBTOTAL(5,$A:A)+DEVSQ(IF(SUBTOTAL(3,OFFSET(A2,$A3:$A26,)),MATCH(B3:B26,B3:B26,)))*99,)&""

复制代码

说不好

常规套路字符太长，试个其他的106

1. =IF(DEVSQ(IF(SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),MATCH(B3:B26,B3:B26,))),"",OFFSET(B2,SUBTOTAL(5,$A3:$A26),))

复制代码

耍点赖101

1. =OFFSET(B2,SUBTOTAL(5,$A:A)+88*DEVSQ(IF(SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),MATCH(B3:B26,B3:B26,))),)&""

复制代码

换个函数100，不知道有没有BUG

1. =OFFSET(B2,SUBTOTAL(5,$A:A)+88*VARP(IF(SUBTOTAL(3,OFFSET(A2,$A3:$A26,)),MATCH(B3:B26,B3:B26,))),)&""

复制代码

补充内容 (2017-12-4 12:44):
换个思路写法，109

1. =IFERROR(INDIRECT("r"&10^GEOMEAN(IF(SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),LOG(MATCH(B3:B26,B3:B26,)+2)))&"c",),"")

复制代码

sucx007

=IF(COUNT(0/FREQUENCY(ROW(1:99999),CODE(T(OFFSET(B2,SUBTOTAL(4,OFFSET($A2,,,ROW($1:$24))),)))))=3,OFFSET(B2,MAX(SUBTOTAL(4,OFFSET($A2,,,ROW($1:$24)))),),"")  2个w就错了=IF(COUNT(0/FREQUENCY(ROW(1:24),MATCH(T(OFFSET(B2,SUBTOTAL(4,OFFSET($A2,,,ROW($1:$24))),)),T(OFFSET(B2,SUBTOTAL(4,OFFSET($A2,,,ROW($1:$24))),)),)))=3,OFFSET(B2,MAX(SUBTOTAL(4,OFFSET($A2,,,ROW($1:$24)))),),"")，太长了~。~，等开帖好膜拜下！

zopey

1. =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),)),),),"")

复制代码

笨笨四

1. REPT(OFFSET(B2,SUBTOTAL(5,$A:$A),),0=VAR.P(IF(SUBTOTAL(3,OFFSET(B2,$A3:$A26,)),MATCH(B3:B26,B3:B26,))))

复制代码