[已解决]Excel函数高手快来解决自动查找八属性用

baksy

vbyou127 发表于 2016-1-4 10:02
还可以怎么样

赞你一个进取心！

huangcaiguang

我也凑一个思路 =INDEX($Q:$Q,8.9+MATCH(1,FREQUENCY(C10,--$R$9:$V$16),)/5)
=INDEX($Q:$Q,0.9+MATCH(1,FREQUENCY(C10,--$R:$V),)/5)
=OFFSET($Q$1,MATCH(1,FREQUENCY(C10,--$R:$V),)/5-1%,)
=INDEX($Q:$Q,MAX(COUNTIF(C10,$R$9:V16)*ROW($9:16)))
=LOOKUP(MATCH(1,FREQUENCY(C10,--$R:$V),),ROW(Q:Q)*5-4,$Q:$Q)
=VLOOKUP(MATCH(1,FREQUENCY(C10,--$R:$V),),IF({1,0},ROW(Q:Q)*5-4,$Q:$Q),2)
=INDEX($Q:$Q,MATCH(1,COUNTIF(OFFSET($R$8,ROW(A:A),,,5),C10),)+8)

huangcaiguang

海鲜补充思路=LOOKUP(,0/MMULT(N($R$1:$V16=C10),W1:W5+1),$Q:$Q)
=INDEX($Q:$Q,MODE($R$9:$V$16%+ROW($9:$16),C10%+ROW($9:$16)))
=HLOOKUP("*",$Q:Q&"",0.9+MATCH(1,FREQUENCY(C10,--$R:$V),)/5,)