G3{=INDEX(A:A,MATCH(1=1,COUNTIF(G$2:G2,A$2:A$19)=0,)+1)&""
另解 G3=LOOKUP(1,0/FREQUENCY(0,COUNTIF(G$2:G2,A$2:A50)),A$2:A50)&""
H3=IF(G3="","",SUMPRODUCT((A$2:A$15=G3)/COUNTIFS(A:A,A$2:A$15,C:C,C$2:C$15)))
K3:M7=IF($J3="","",SUMPRODUCT(($A$2:$A$15=$J3)*($B$2:$B$15=K$2)/COUNTIFS($A:$A,$A$2:$A$15,$B:$B,$B$2:$B$15,$C:$C,$C$2:$C$15)))
另解K3:M7=IF(J3="","",SUMPRODUCT(($A$2:$A50=$J3)*($B$2:$B50=K$2)/TEXT(COUNTIFS($A:$A,$A$2:$A50,$B:$B,$B$2:$B50,$C:$C,$C$2:$C50),"[>0]0;!1")))
留言
張貼留言