【函中20130301】第七讲D组作业上交帖

1982zyh

【函中201301班】-D16-1982zyh
1.=SUM(SUMIFS(D161:D172,B161:B172,{"人事部";"宣传部"},C161:C172,"<50"))
2.=SUM(SUMIFS(B179:B185,A179:A185,{"A","C"}))
3.
a.=SUMPRODUCT(B189:B204*INT(LEN(A189:A204&",")/3))/SUMPRODUCT(INT(LEN(A189:A204&",")/3))
b.=AVERAGEIFS(B189:B204,A189:A204,"*02*")
c.=SUMIFS(B189:B204,A189:A204,"*02,*04*")
4.=COUNTIFS(A212:A220,"<60")+COUNTIFS(A212:A220,">90")
5.=COUNTIFS(A224:A228,"*小妖*")
6.{=OFFSET($A$232,MAX(IF($A$232:$H$238=A241,ROW($A$232:$H$238)))-ROW($A$232),MAX(IF($A$232:$H$238=A241,COLUMN($A$232:$H$238))-2))}
7.=(SUM(A250:D256)-LARGE(A250:D256,1)-LARGE(A250:D256,2)-SMALL(A250:D256,1)-SMALL(A250:D256,2)-SMALL(A250:D256,3))/(COUNT(A250:D256)-5)
=AVERAGEIFS(A250:D256,A250:D256,">"&SMALL(A250:D256,3),A250:D256,"<"&LARGE(A250:D256,2))
8.=AVERAGEIF(A264:A270,"广州",OFFSET(A263,1,MATCH("上年二季度",B263:E263,)))+AVERAGEIF(A264:A270,"广州",OFFSET(A263,1,MATCH("本年二季度",B263:E263,)))
9.=SUMPRODUCT($B$277:$B$284*(ROUNDUP($A$277:$A$284/3.1,0)=ROW(A1)*1))

07mickey

【函中201301班】-D23-07mickey
1.

1. <FONT size=2>=SUM(SUMIFS(D161:D172,B161:B172,{"人事部","宣传部"},C161:C172,"<50"))</FONT>

复制代码

2.

1. =SUM(SUMIF(A179:A185,{"A","C"},B179:B185))

复制代码

3.第一个为数组公式

1. =SUM(B189:B204*INT(FIND("A",A189:A204&",A")/3))/SUM(INT(FIND("A",A189:A204&",A")/3))

复制代码

1. =AVERAGEIF(A189:A204,"*02*",B189:B204)

复制代码

1. =SUM(SUMIF(A189:A204,{"*02*04*"},B189:B204))

复制代码

4.

1. =SUM(COUNTIF(A212:A220,{">90","<60"}))

复制代码

5.

1. =COUNTIF(A224:A228,"*小妖*")

复制代码

6.数组公式

1. =OFFSET(A$231,MAX(IF(B$232:H$238=A241,ROW($1:$7))),MAX(IF(B$232:H$238=A241,COLUMN($B:$H)))-2)

复制代码

7、8答案和参考答案不一样，暂且定为不会吧。。
9.数组公式

1. =SUM(SUMIF(A$277:A$284,ROW($1:$3)+(ROW(A1)-1)*3,B$277:B$284))

复制代码

yunqingzi

1、=SUM(SUMIFS(D2:D13,B2:B13,{"人事部","宣传部"},C2:C13,"<50"))
2、=SUM(SUMIF(A20:A26,{"A","C"},B20:B26))
3.1｛=SUM(B30:B45)/SUM(LEN(A30:A45)-LEN(SUBSTITUTE(A30:A45,",",""))+1)｝
3.2=AVERAGEIF(A30:A45,"*02*",B30:B45)
3.3=SUMIF(A30:A45,"*2*4*",B30:B45)
4、=SUM(COUNTIF(A53:A61,{"<60";">90"}))
5、=COUNTIF(A65:A69,"*妖*")
6、=SUMIF(B$73:H$79,A82,A$73)向下双击填充柄
7、｛=(SUM(A91:D97)-SUM(LARGE(A91:D97,ROW(1:2)),SMALL(A91:D97,ROW(1:3))))/(COUNT(A91:D97)-5)｝
8、=SUMIF(B104:E104,"*二季度",B108)
9、=SUMPRODUCT(((CEILING(A$118:A$125,3)/3)=ROW(1:1))*B$118:B$125)向下填充公式

xxm893032160

【函中201301班】-d17-xxm893032160
1.=SUM((B161:B172<>"销售部")*(C161:C172<50)*D161:D172)  
2.=SUM((A179:A185<>"B")*B179:B185)
4.=SUM((A212:A220<60)+(A212:A220>90))
5.=COUNTIF(A224:A228,"*小妖*")
6.=SUMIF(B$232:H$238,A241,A$232:H$238)

xcnclzy

【函中201301班】-d19-xcnclzy

1. 1 =SUM(SUMIFS(D161:D172,B161:B172,{"宣传部","人事部"},C161:C172,"<50"))
   
2. 2 =SUM(SUMIFS(B179:B185,A179:A185,{"A","C"}))
   
3. 3 =SUM(B189:B204)/SUM(LEN(A189:A204)-LEN(SUBSTITUTE(A189:A204,",",""))+1)
   
4. =SUMPRODUCT((ISNUMBER(FIND("02",A189:A204))*B189:B204))/((SUM(LEN(A189:A204))-SUM(LEN(SUBSTITUTE(A189:A204,"02",))))/2)
   
5. =SUMPRODUCT((ISNUMBER(FIND("02",A189:A204))*(ISNUMBER(FIND("04",A189:A204))*B189:B204)))
   
6. 4 =SUM(COUNTIF(A212:A220,{"<60",">90"}))
   
7. 5 =LEN(PHONETIC(A224:A228))-LEN(SUBSTITUTE(PHONETIC(A224:A228),"小妖",1))
   
8. 6 =SUMIFS($A$232:$G$238,$B$232:$H$238,A241)
   
9. 7 =(SUM(A250:D256)-SUM(LARGE(A250:D256,ROW(1:2)))-SUM(SMALL(A250:D256,ROW(1:3))))/(COUNT(A250:D256)-5)
   
10. 8 =SUMIFS(C264:C270,A264:A270,"广州")+SUMIFS(E264:E270,A264:A270,"广州")
    
11. 9 =SUM((ROW(A1)=ROUNDUP($A$277:$A$284/3,))*$B$277:$B$284)
    

复制代码

wangsunye3

函中201301班作业7-D03-wangsunye3
=SUMIFS($D$161:$D$172,B161:B172,"人事部",$C$161:$C$172,"<50")+SUMIFS($D$161:$D$172,B161:$B$172,"宣传部",$C$161:$C$172,"<50")

=SUMIFS($B$179:$B$185,A179:A185,"A")+SUMIFS($B$179:$B$185,$A$179:$A$185,"c")

=COUNTIFS(A212:A220,"<60")+COUNTIF(A212:A220,">90")

lion_zjy

【函中201301班】-D10-lion_zjy
1.=SUM(SUMIFS($D$2:$D$13,B2:B13,"<>销*",C2:C13,"<50"))
   =SUM(SUMIFS($D$2:$D$13,B2:B13,{"人事部","宣传部"},C2:C13,"<50"))
   =SUMPRODUCT(((B2:B13=B2)+(B2:B13=B5))*(C2:C13<50)*D2:D13)


2.=SUM(SUMIFS(B20:B26,A20:A26,{"A","c"}))
   =SUMPRODUCT(B20:B26*(A20:A26={"a","C"}))
  =SUM(SUMIF(A20:A26,{"A","C"},B20:B26))
=SUMIF(A20:A26,"<>B",B20:B26)

3.1:=SUM(((LEN(A30:A45)+1)/3)*(B30:B45))/SUM((LEN(A30:A45)+1)/3)
3.2:=AVERAGEIF(A30:A45,"*02*",B30:B45)
3.3:=SUMIFS(B30:B45,A30:A45,{"*02*,*04*"})        =SUMIF(A30:A45,{"*02*04*"},B30:B45)


4.=SUM(COUNTIF(A53:A61,{"<60",">90"}))
   =COUNTIF(A53:A61,"<60")+COUNTIF(A53:A61,">90")
  =SUMPRODUCT((A53:A61<60)+(A53:A61>90))

5.=(LEN(A65&A66&A67&A68&A69)-LEN(SUBSTITUTE(A65&A66&A67&A68&A69,"小妖",1)))


6.=SUMIF($B$73:$H$79,A82,$A$73:$G$79)


7.=TRIMMEAN(LARGE(A91:D97,ROW(1:27)),4/27)


8.=SUMIF(A105:A111,A108,C105:C111)+(SUMIF(A105:A111,A108,E105:E111))
   =SUMPRODUCT((A105:A111=A108)*((B104:E104=C104)*C105:C111+(B104:E104=E104)*E105:E111))

9.=SUM((ROUNDUP($A$118:$A$125/3,0)=ROW(A1))*($B$118:$B$125))

365221582

学习一下。