|
【函中201301班】-C10-雨后的风
第一题:- SUMPRODUCT(B3:B18*(LEN(A3:A18)>FIND(4,A3:A18&4)))
复制代码- SUMPRODUCT(LEN(A3:A18)-LEN(SUBSTITUTE(A3:A18,4,)),B3:B18)
复制代码 第二题:
人数- =SUMPRODUCT((A$25:A$48=D25)*(B$25:B$48>LARGE(B$25:B$48,6)))
复制代码- =COUNTIFS(A$25:A$48,D25,B$25:B$48,">"&LARGE(B$25:B$48,6))
复制代码 总分- =SUMPRODUCT((A$25:A$48=D34)*(B$25:B$48>LARGE(B$25:B$48,6))*B$25:B$48)
复制代码- =SUMIFS(B$25:B$48,B$25:B$48,">"&LARGE(B$25:B$48,6),A$25:A$48,D34)
复制代码 第三题:- =SUMPRODUCT((--B$54:B$60>LARGE(--B$54:B$60,6))*(A$54:A$60=D54))
复制代码- =SUMPRODUCT((A$54:A$60=D54)*(-B$54:B$60<SMALL(-B$54:B$60,6)))
复制代码 第四题:- =SUMPRODUCT((A64:A70="A")+(A64:A70="C"),B64:B70)
复制代码- =SUMPRODUCT((A64:A70={"A","C"})*(B64:B70<"0")*B64:B70)
复制代码 |
评分
-
查看全部评分
|