本帖最后由 007feiren 于 2013-6-21 16:16 编辑
【函中201301班】-A22-007feiren:
第1题:- =SUM(SUMIFS(D2:D13,C2:C13,"<50",B2:B13,{"人事部","宣传部"}))
- =SUMPRODUCT((C2:C13<50)*(B2:B13={"宣传部","人事部"})*D2:D13)
- =SUMIFS(D2:D13,C2:C13,"<50",B2:B13,"<>销售部")
复制代码 第2题:- =SUM(SUMIFS(B20:B26,A20:A26,{"A","C"}))
复制代码 第3题:- =SUM(B30:B45)/(SUM(LEN(SUBSTITUTE(A30:A45,",",)))/2)三键结束
- =SUMPRODUCT(IFERROR((FIND("02",A30:A45)>0),)*B30:B45)/COUNT(FIND("02",A30:A45))三键结束
- =SUM((IFERROR(FIND("02",A30:A45)>0,)*(IFERROR(FIND("04",A30:A45)>0,)*(B30:B45))))三键结束
复制代码 第4题:- =SUM(COUNTIFS(A53:A61,{"<60",">90"}))
复制代码 第5题:- =SUM(LEN(A65:A69)-LEN(SUBSTITUTE(A65:A69,"小妖",)))/2三键结束
复制代码 第6题:- =INDEX(A$1:H$79,SMALL(IF(A$73:H$79=A82,ROW(A$73:H$79),65536),1),SMALL(IF(A$73:H$79=A82,COLUMN($A$73:$H$79),65536),1)-1)三键结束
复制代码 第7题:- =(SUM(A91:D97)-SUM(LARGE(A91:D97,{1,2}))-SUM(SMALL(A91:D97,{1,2,3})))/(COUNT(A91:D97)-5)三键结束
复制代码 第8题:- =SUM(INDEX(B105:E111,MATCH("广州",A105:A111,0),MATCH("上年二季度",B104:E104,0))+INDEX(B105:E111,MATCH("广州",A105:A111,0),MATCH("本年二季度",B104:E104,0)))
复制代码 第9题:- =SUMIF(A$118:A$125,"<="&ROW(A1)*3,B$118:B$125)-SUM(E$126:E126)
复制代码 |