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

yjeeee

【函中201301班】-C07-yjeeee
请学委点评：
第1题

1. =SUM(SUMIFS(D161:D172,B161:B172,{"人事部","宣传部"},C161:C172,"<50"))
   
2. =SUMPRODUCT(((B161:B172="人事部")+(B161:B172="宣传部"))*(C161:C172<50),D161:D172)

复制代码

第2题

1. =SUMIF(A179:A185,"<>b",B179:B185)
   
2. =SUMPRODUCT(((A179:A185="a")+(A179:A185="c")),B179:B185)

复制代码

第3题

第4题

1. =COUNT(A212:A220)-COUNTIFS(A212:A220,"<=90",A212:A220,">=60")
   
2. =SUMPRODUCT((A212:A220<60)+(A212:A220>90))

复制代码

第5题

1. {=SUM(IFERROR(SEARCH("*小妖*",A224:A228),0))}

复制代码

第6题
第7题
第8题
第9题

lisa19860622

【函中20130301】-c19-lisa19860622
题一:
法1:

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

复制代码

法2数组)

1. =SUM((B161:B172={"人事部","宣传部"})*(C161:C172<50)*D161:D172)

复制代码

题二:
法1:

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

复制代码

法2:

1. =SUM(B179:B185)-SUMIF(A179:A185,"b",B179:B185)

复制代码

法3:

1. =SUMPRODUCT((A179:A185={"a","c"})*B179:B185)

复制代码

题三:
1.

1. =SUM(B189:B204)/(LEN(SUBSTITUTE(PHONETIC(A189:A204),",",""))/2)

复制代码

2.法1:

1. =SUMIF(A189:A204,"*02*",B189:B204)/COUNTIF(A189:A204,"*02*")

复制代码

法2:

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

复制代码

3.

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

复制代码

题四:
法1:

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

复制代码

法2:

1. =SUMPRODUCT(((A212:A220<60)+(A212:A220>90)))

复制代码

题五:
法1:

1. =LEN(PHONETIC(A224:A228))-LEN(SUBSTITUTE(PHONETIC(A224:A228),"小妖",1))

复制代码

法2只适用于本题每个单元格只出现一次)

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

复制代码

题六只适用于本题,代码为数值型)

1. =SUMIF($B$232:$H$238,A241,$A$232)

复制代码

题七:
法1数组)

1. =(SUM(A250:D256)-SUM(LARGE(A250:D256,ROW(1:2)),SMALL(A250:D256,ROW(1:3))))/(COUNT(A250:D256)-5)

复制代码

法2:

1. =AVERAGEIFS(A250:D256,A250:D256,"<"&LARGE(A250:D256,2),A250:D256,">"&SMALL(A250:D256,3))

复制代码

题八:

1. =SUMPRODUCT(SUMIF(B263:E263,"*二*",OFFSET(B263,MATCH(A267,A264:A270,),)))

复制代码

题九:
法1:

1. =SUM(OFFSET($B$277,MATCH(3*(ROW(A1)-1),{0,1,2,3,4,6,7,11,12})-1,,MATCH(3*ROW(A1),$A$277:$A$284,1)-(MATCH(3*(ROW(A1)-1),{0,1,2,3,4,6,7,11,12})-1)))

复制代码

法2:

1. =SUMIF($A$277:$A$284,"<="&ROW(A1)*3,$B$277:$B$284)-SUMIF($A$277:$A$284,"<="&(ROW(A1)-1)*3,$B$277:$B$284)

复制代码

csmccdh

【函中201301班】-C01-csmccdh
第一题：
方法一：=SUM(((B161:B172="宣传部")+(B161:B172="人事部"))*(C161:C172<50)*D161:D172)（数组）
方法二：=SUMIFS(D161:D172,B161:B172,"人事部",C161:C172,"<50")+SUMIFS(D161:D172,B161:B172,"宣传部",C161:C172,"<50")
方法三：=SUMPRODUCT(((B161:B172="宣传部")+(B161:B172="人事部"))*(C161:C172<50)*D161:D172)
方法四：=SUM(SUMIFS(D161:D172,B161:B172,{"人事部","宣传部"},C161:C172,"<50"))

第二题：
方法一：=SUMPRODUCT(B179:B185*(A179:A185={"A","C"}))
方法二：=SUM(SUMIF(A179:A185,{"A","C"},B179:B185))

第三题：
月均产值：=SUM(B189:B204)/SUM(LEN(A189:A204)-LEN(SUBSTITUTE(A189:A204,",",))+1)（数组）
二月平均产值：=AVERAGEIF(A189:A204,"*02*",B189:B204)
月份列即有二月又有四月对应产值和：=SUMIF(A189:A204,"*"&2&"*"&4&"*",B189:B204)

第四题：
方法一：=COUNTIF(A212:A220,"<60")+COUNTIF(A212:A220,">90")
方法二：=SUMPRODUCT((A212:A220<60)+(A212:A220>90))
方法三：=SUM(COUNTIF(A212:A220,{"<60",">90"}))

第五题：
方法一：=SUM((LEN(A224:A228)-LEN(SUBSTITUTE(A224:A228,"小妖",)))/2)（数组）
方法二：=COUNTIF(A224:A228,"*小妖*")
方法三：=(LEN(PHONETIC(A224:A228))-LEN(SUBSTITUTE(PHONETIC(A224:A228),"小妖",)))/2

第六题：
方法一：=INDIRECT(TEXT(MAX((A$232:H$238=A241)*(ROW($232:$238)&COLUMN(A:H)))-1,"R000C0"),)（数组、下拉）
方法二：=INDEX(A$232:H$238,MAX((A$232:H$238=A241)*ROW($1:$7)),MAX((A$232:H$238=A241)*COLUMN(A:H))-1)（数组、下拉）
方法三：=OFFSET(A$231,MAX((A$232:H$238=A241)*ROW($1:$7)),MAX((A$232:H$238=A241)*COLUMN(A:H))-2)（数组、下拉）

第七题
方法一：=(SUM(A250:D256)-SUM(LARGE(A250:D256,{1,2}))-SUM(SMALL(A250:D256,{1,2,3})))/(COUNT(A250:D256)-5)
方法二：=(SUM(A250:D256)-SUM(LARGE(A250:D256,{1,2,26,27,28})))/23

第八题
方法一：=SUMIF(B263:E263,"*二季度",OFFSET(B263,MATCH("广州",A264:A270,),))
方法二：=SUM(OFFSET(B263:E263,MATCH("广州",A264:A270,),)*IFERROR(FIND("二季度",B263:E263)^0,))（数组）

第九题：
方法一：=SUMIF(A$277:A$284,"<"&ROW(A1)*3+1,B$277:B$284)-SUM(D$285:D285)（下拉）
方法二：=SUM((A$277:A$284<(3*ROW(A1)+1))*B$277:B$284)-SUM(E$285:E285)（数组、下拉）
方法三：=SUM((A$277:A$284<(3*ROW(A1)+1))*(A$277:A$284>(ROW(A1)*3-3))*B$277:B$284)（数组、下拉）
方法四：=SUM(SUMIF(A$277:A$284,"<"&ROW(A1)*3+{1,-2},B$277:B$284)*{1,-1})

kouhogliang

【函中201301班】-C08-kouhogliang

1

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

复制代码

三键结束
2

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

复制代码

三键结束
4

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

复制代码

三键结束

孤单无所谓

【函中201301班】-C04孤单无所谓
1

1. =SUMIFS(D2:D13,B2:B13,"<>销售部",C2:C13,"<50")

复制代码

2

1. =SUMIF(A20:A26,"<>B",B20:B26)

复制代码

3

1. =SUM(B30:B45*((LEN(A30:A45)+1)/3))/SUM((LEN(A30:A45)+1)/3)

复制代码

1. =AVERAGEIF(A30:A45,"*02*",B30)

复制代码

1. =SUMIF(A30:A45,"*2*4*",B30)

复制代码

4

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

复制代码

5

1. =COUNTA(A65:A69)-SUM(N(ISERROR(FIND("小妖",A65:A69)>0)))

复制代码

6

1. =OFFSET(A$73,MAX((B$73:H$79=A82)*ROW($1:$7))-1,MAX((B$73:H$79=A82)*COLUMN(A:G))-1,)

复制代码

7

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

复制代码

8

1. =SUM((RIGHT(B104:E104&A105:A111,5)="二季度广州")*B105:E111)

复制代码

9

1. =SUM((($A$118:$A$125)<=(ROW(A1)*3))*(($A$118:$A$125)>=ROW(A1)*3-2)*($B$118:$B$125))

复制代码

一江秋水

【函中201301班】-C03-一江秋水
作业1

1. =SUM((B161:B172={"宣传部","人事部"})*(C161:C172<50)*D161:D172)

复制代码

作业2

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

复制代码

作业3

1. =AVERAGE(AVERAGEIF(A189:A204,{"=*01*","=*02*","=*03*","=*04*","=*05*","=*06*","=*07*","=*08*","=*09*","=*10*","=*11*","=*12*"},B189:B204))

复制代码

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

复制代码

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

复制代码

作业4

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

复制代码

作业5

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

复制代码

pang1688

【函中201301班】-C14-pang1688
时间太紧了，只能尽力去做！
1、

1. =SUM(SUMIFS(D2:D13,B2:B13,{"宣传部","人事部"},C2:C13,"<50"))

复制代码

2、

1. =SUM(SUMIFS(B20:B26,A20:A26,{"a","c"}))

复制代码

3、不会
4、

1. =SUM(COUNTIFS(A53:A61,{"<60",">90"}))

复制代码

5、

1. =COUNT(FIND("小妖",A65:A69))

复制代码

数组
6、不会
7、

1. =TRIMMEAN(A91:D97,5/28)

复制代码

8、

1. =SUMIFS(C105:C111,A105:A111,A108)+SUMIFS(E105:E111,A105:A111,A108)

复制代码

9、

1. =SUM(SUMIFS($B$118:$B$125,$A$118:$A$125,IF(LEFT(A127)="一",{1,2,3},IF(LEFT(A127)="二",{4,5,6},IF(LEFT(A127)="三",{7,8,9},{10,11,12})))))

复制代码

cir.s

【函中201301班】-C12-Cir.s
第一题：=SUMPRODUCT(((B161:B172=B161)+(B161:B172=B164))*(C161:C172<50)*D161:D172)
第二题：=SUMPRODUCT(((A179:A185=A179)+(A179:A185=A182))*B179:B185)
第三题：1）=SUMPRODUCT(((LEN(A189:A204)+1)/3)*B189:B204)/SUM((LEN(A189:A204)+1)/3)数组公式
      2）=SUMPRODUCT((IFERROR((FIND("02",A189:A204)<>0),0)*B189:B204))/SUM(IFERROR((FIND("02",A189:A204)<>0)*1,0))数组公式
       3）=SUMPRODUCT(IFERROR((FIND("02",A189:A204)<>0)*FIND("04",A189:A204)<>0,0)*1*B189:B204)数组公式
第四题：=SUM(IFERROR(A212:A220>90,0)+IFERROR(A212:A220<60,0))数组公式
第五题：=SUM(LEN(A224:A228)-LEN(SUBSTITUTE(A224:A228,"小妖",0)))数组公式
       =SUM(IFERROR(FIND("小妖",A224:A228)<>0,0)*1)数组公式
第六题：=SUMIF($B$232:$B$238,A241,$A$232:$A$238)+SUMIF($D$232:$D$238,A241,$C$232:$C$238)+SUMIF($F$232:$F$238,A241,$E$232:$E$238)+SUMIF($H$232:$H$238,A241,$G$232:$G$238)
第七题：=(SUM(A250:D256)-SUM(LARGE(A250:D256,ROW(1:2)))-SUM(SMALL(A250:D256,ROW(1:3))))/(COUNT(A250:D256)-5)数组公式
第八题：=INDEX(A263:E270,MATCH(A267,A263:A270,0),MATCH(C263,A263:E263,0))+INDEX(A263:E270,MATCH(A267,A263:A270,0),MATCH(E263,A263:E263,0))
第九题：=SUMIFS($B$277:B$284,A$277:A$284,">"&(ROW(A1)-1)*3,$A$277:$A$284,"<"&ROW(A1)*3+1)

diymywork

对不起又这么晚才交作业，哎
1、

1. =SUMPRODUCT(((B2:B13=$B$2)+(B2:B13=$B$5))*(C2:C13<50)*D2:D13)

复制代码

2

1. =SUMPRODUCT(((A20:A26=$A$20)+(A20:A26=$A$23))*B20:B26)

复制代码

4

1. =SUMPRODUCT((A53:A61<60)+(A53:A61>90))

复制代码

貌似只会用sumproduct

不信这样不重名

【函中201301班】-C13-不信这样不重名
最近工作太忙，QQ也忘上了，作业差点忘交了，真心不应该，好在尚未开帖，只是初看了下视频做的作业，里面几个问题用新函数应是简单许多，明天开帖前若有空再改吧，trimmean那个处理单数的方法还要再看下视频，学委有空就看看，明天希望工作别加班可以早点回来听作业讲解{:141:}
1
=SUMPRODUCT((B2:B13={"宣传部","人事部"})*(C2:C13<50)*D2:D13)
=SUM(SUMIFS(D2:D13,B2:B13,{"宣传部","人事部"},C2:C13,"<50"))
2
=SUM(SUMIF(A20:A26,{"C","A"},B20:B26))
3
=SUM((LEN(A30:A45)-LEN(SUBSTITUTE(A30:A45,",",""))+1)*B30:B45)/SUM(LEN(A30:A45)-LEN(SUBSTITUTE(A30:A45,",",""))+1)

=AVERAGEIF(A30:A45,"*02*",B30:B45)

=SUMIF(A30:A45,"*02*04*",B30:B45)
4
=COUNTA(A53:A61)-SUMPRODUCT((A53:A61>=60)*(A53:A61<=90)*1)
5
=(LEN(PHONETIC(A65:A69))-LEN(SUBSTITUTE(PHONETIC(A65:A69),"小妖","")))/2
6
=SUMIF($B$73:$H$79,A82,$A$73)
7
=TRIMMEAN(A91:D97,7/COUNT(A91:D97))
8
=SUM(((A105:A111)="广州")*IFERROR(FIND("二季度",B104:E104),)/3*B105:E111)
9
=SUM(SUMIFS($B$118:$B$125,$A$118:$A$125,"<="&ROW(A1)*3,$A$118:$A$125,">"&(ROW(A1)-1)*3))