第一题:
=MMULT({1,1,1,1},B8:D11)
=MMULT(1,SUBTOTAL(9,OFFSET(A8:A11,,{1,2,3})))
=MMULT(1,SUMIF(OFFSET(A8:A11,,{1,2,3}),"<>"))
下面两个转来转去是凑数的:=TRANSPOSE(MMULT(TRANSPOSE(SUMIF(OFFSET(A8:A11,,{1,2,3}),"<>")),1))
=TRANSPOSE(MMULT(TRANSPOSE(B8:D11),{1;1;1;1}))
第二题:=MMULT(N(B18:B24<=TRANSPOSE(B18:B24)),ROW(1:7)^0) 普通排名(不知道算不算美式排名)
=MMULT(N(B18:B24<TRANSPOSE(B18:B24)),ROW(1:7)^0)+1 普通排名(美式排名)
=MMULT(N((B18:B24-ROW(1:7)%%)<TRANSPOSE(B18:B24-ROW(1:7)%%)),ROW(1:7)^0)+1 不重复排名(先出现者靠前)
=MMULT(N(B18:B24<TRANSPOSE(B18:B24)),N(MATCH(B18:B24,B18:B24,)=ROW(1:7)))+1 中式排名
第三题:
=TRANSPOSE(MMULT({1,1,1},MMULT(N(B30:D32<>""),{1;1;1})*({2,3,4}>{1;2;3})))
=TRANSPOSE(MMULT({1,1,1},COUNTIF(OFFSET(B29:D29,{1;2;3},),"√")*({2,3,4}>{1;2;3})))
|