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

CheryBTL

1、本贴已设置回贴仅作者可见，直接跟贴回复即可；
2、上交时直接贴公式，只上传附件的不予批改；
3、答题时必须注明【函中201301班】-组号-论坛ID,如【函中201301班】-C99-CheryBTL；
4、截止时间2013年6月19日18:00；
5、请误重复占楼，非本组同学跟贴回复者扣分。

下附课程相关链接：
函中201301班第七讲课件及作业

xiaoni

【函中201301班】-C15-xiao上交第七讲作业
第1题：=SUM(SUMIFS(D161:D172,B161:B172,{"人事部","宣传部"},C161:C172,"<50"))
           =SUMPRODUCT((B161:B172={"宣传部","人事部"})*(C161:C172<50)*D161:D172)
第2题：=SUM(SUMIFS(B179:B185,A179:A185,{"a","c"}))
           =SUM(SUMIF(A179:A185,{"a","c"},B179))
第3题第1问：=SUM((LEN(A189:A204)-LEN(SUBSTITUTE(A189:A204,",",""))+1)*B189:B204)/SUM((LEN(A189:A204)-LEN(SUBSTITUTE(A189:A204,",",""))+1))
第3题第2问：=AVERAGEIFS(B189:B204,A189:A204,"*02*")
第3题第3问：=SUMIFS(B189:B204,A189:A204,{"*02,*04*"})
第4题：=SUM(COUNTIFS(A212:A220,{"<60",">90"}))
第5题：=COUNTIF(A224:A228,"*小妖*")
第6题：=SUMIF(B$232:H$238,A241,A$232)公式下拉
第七题：=TRIMMEAN(LARGE(A250:D256,ROW(1:27)),4/27)三键结束
第八题：=SUM(VLOOKUP("广州",A264:E270,MATCH({"本年二*","上年二*"},A263:E263,)))
第九题：=SUMPRODUCT((ROUNDUP($A$277:$A$284/3,)=ROW(A1))*B$277:B$284)

qqyyh

【函中201301班】-C06-qqyyh
1.=SUM(SUMIFS(D161:D172,B161:B172,{"宣传部","人事部"},C161:C172,"<50"))
   =SUMIFS(D161:D172,B161:B172,"宣传部",C161:C172,"<50")+SUMIFS(D161:D172,B161:B172,"人事部",C161:C172,"<50")
   =SUM((B161:B172={"宣传部","人事部"})*(C161:C172<50)*D161:D172)数组公式
  =SUMPRODUCT((B161:B172={"宣传部","人事部"})*(C161:C172<50)*D161:D172)
2.=SUM(SUMIFS(B179:B185,A179:A185,{"A","C"}))
   =SUMIFS(B179:B185,A179:A185,"A")+SUMIFS(B179:B185,A179:A185,"C")
   =SUM((A179:A185={"A","C"})*B179:B185)数组公式
  =SUMPRODUCT((A179:A185={"A","C"})*B179:B185)
3.=SUM(((LEN(SUBSTITUTE(A189:A204,",",""))/2)*(B189:B204)))/SUM(((LEN(SUBSTITUTE(A189:A204,",",""))/2)))数组公式
  =AVERAGEIFS(B189:B204,A189:A204,"*02*")
   =SUMIFS(B189:B204,A189:A204,"*02*",A189:A204,"*04*")
4.=SUM(COUNTIFS(A212:A220,{"<60",">90"}))
   =COUNTIF(A212:A220,"<60")+COUNTIF(A212:A220,">90")
   =SUMPRODUCT(((A212:A220<60)+(A212:A220>90)))
5.=SUM((LEN(A224:A228)-LEN(SUBSTITUTE(A224:A228,"小妖",0))))数组公式
  =COUNTIFS(A224:A228,"*小妖*")
   =COUNTIF(A224:A228,"*小妖*")
6.=SUMIF($B$232:$H$238,A241,$A$232:$G$238)下拉
7.=TRIMMEAN(LARGE(A250:D256,ROW(1:27)),5/(COUNT(A250:D256)-1))数组公式
  =AVERAGEIFS(A250:D256,A250:D256,"<"&LARGE(A250:D256,2),A250:D256,">"&SMALL(A250:D256,3))
   =(SUM(A250:D256)-SUM(LARGE(A250:D256,ROW(1:2)))-SUM(SMALL(A250:D256,ROW(1:3))))/(COUNT(A250:D256)-5)数组公式
8.=SUMIFS(C264:C270,A264:A270,"广州")+SUMIFS(E264:E270,A264:A270,"广州")
   =SUM(((A264:A270=A267)*(C264:C270))+((A264:A270=A267)*(E264:E270)))数组公式
  =SUMPRODUCT(((A264:A270=A267)*(C264:C270))+((A264:A270=A267)*(E264:E270)))
9.=SUM(SUMIFS($B$277:$B$284,$A$277:$A$284,ROW($1:$3)+3*(ROW(A1)-1)))数组公式 下拉

小志

【函中201301班】-C16-小志
第一题：

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

复制代码

第二题：

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

复制代码

第三题：
1、

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

复制代码

2、

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

复制代码

3、

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

复制代码

第四题：

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

复制代码

第五题：老方法。呵呵。

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

复制代码

第六题:老方法。。

1. =INDIRECT(TEXT(MAX(($A$232:$H$238=A241)*(ROW($A$232:$H$238)*10+COLUMN($A$232:$H$238))-1),"R000C0"),)

复制代码

第七题：

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

复制代码

第八题：

1. =SUM(VLOOKUP("广州",A264:E270,{3,5},))

复制代码

第九题：

1. =SUMIF(A277:A284,"<="&(ROW(A1))*3,B277:B284)-SUMIF(A277:A284,"<="&(ROW(A1)-1)*3,B277:B284)

复制代码

学委，第九题，用那个{1,-1}怎么来弄啊？条件中有变量，弄不出来。

雨后的风

【函中201301班】-C10-雨后的风
第一题：

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

复制代码

第二题：

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

复制代码

第三题：
月均产值

1. =AVERAGE(IFERROR((--MID(A189:A204,COLUMN(A:L)*3-2,2)>0)*B189:B204,""))

复制代码

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

复制代码

二月平均产值

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

复制代码

既有二月又有四月对应产值和

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

复制代码

第四题：

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

复制代码

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

复制代码

第五题：

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

复制代码

1. =COUNT(FIND("小妖",A224:A228))

复制代码

第六题：

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

复制代码

1. =SUM(IFERROR((B$232:H$238=A241)*A$232:G$238,))

复制代码

第七题：

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

复制代码

1. =TRIMMEAN(IF(A250:D256>MIN(A250:D256),A250:D256),5/COUNT(A250:D256))

复制代码

第八题：

1. =SUM(SUMIF(A264:A270,"广州",OFFSET(A264,,{2,4})))

复制代码

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

复制代码

第九题：

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

复制代码

1. =SUM(OFFSET(B$276,COUNTIF(A$277:A$284,"<="&ROW(A1)*3),,-COUNTIFS(A$277:A$284,">"&ROW(A1)*3-3,A$277:A$284,"<="&ROW(A1)*3)))

复制代码

womohe

1 =SUMIFS(D161:D172,B161:B172,"<>"&B163,C161:C172,"<50")
2 =SUMIF(A179:A185,"<>"&A181,B179:B185)
3  =SUM((LEN(A189:A204)-LEN(SUBSTITUTE(A189:A204,",",""))+1)*B189:B204)/SUM(LEN(A189:A204)-LEN(SUBSTITUTE(A189:A204,",",""))+1)
   =AVERAGEIF(A189:A204,"*02*",B189:B204)
  =SUM(SUMIF(A189:A204,"*02*04*",B189:B204))
4 =SUM(COUNTIF(A212:A220,{"<60",">90"}))
5 =SUM(LEN(A224:A228)-LEN(SUBSTITUTE(A224:A228,"小妖","1")))
6 =INDEX($A$232:$H$238,MAX(($A$232:$H$238=A241)*ROW($1:$7)),MAX(($A$232:$H$238=A241)*(COLUMN($A:$H)-1)))
7 =AVERAGE(LARGE(A250:D256,ROW(A3:A25)))
  =SUM(SUMIF(A250:D256,LARGE(A250:D256,ROW(A3:A25)),A250))/23
8 =SUMPRODUCT((A264:A270=A267)*(MID(B263:E263,3,1)="二")*B264:E270)
9 =SUM((MATCH(LEFT(A286),TEXT(ROW($A$1:$A$4),"[dbnum1]"),)=ROUNDUP(A$277:A$284/3,))*B$277:B$284)

xuyouxm

【函中201301班】-C21-xuyouxm

01:

1. =SUMIFS($D$2:$D$13,$B$2:$B$13,"<>销售部",$C$2:$C$13,"<50")

复制代码

02:

1. =SUMIFS($B$20:$B$26,$A$20:$A$26,"<>B")

复制代码

03:
如果求值为：10199.5576923077，用下面这个公式：

1. 如果求值为：10199.5576923077，用下面这个公式：
   
2. =SUM($B$30:$B$45)/(LEN(SUBSTITUTE(PHONETIC($A$30:$A$45),",",""))/2)
   
3. 如果求值为：37014.4038461538，用下面这个公式：
   
4. =SUM(($B$30:$B$45*(LEN(SUBSTITUTE(A30:A45,",",""))/2))/(LEN(SUBSTITUTE(PHONETIC($A$30:$A$45),",",""))/2))
   
5. =SUM(B30:B45*((LEN(A30:A45)+1)/3))/SUM((LEN(A30:A45)+1)/3)
   
6. 
   

复制代码

1. =SUM((IF(ISERR(MID(A30:A45,FIND("02",$A$30:$A$45),2)),"",MID(A30:A45,FIND("02",$A$30:$A$45),2))="02")*($B$30:$B$45))/COUNT(--IF(ISERR(MID(A30,FIND("02",$A$30:$A$45),2)),"",MID(A30,FIND("02",$A$30:$A$45),2)))

复制代码

1. =SUM((ISNUMBER(--FIND("02",$A$30:$A$45)+(--FIND("04",$A$30:$A$45)))=TRUE)*($B$30:$B$45))

复制代码

04:

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

复制代码

05:

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

复制代码

06:

1. =OFFSET($A$73:$H$79,MAX(IF(ISERR(FIND(A82,$A$73:$H$79)),"",ROW($A$73:$H$79)))-73,MAX(IF(ISERR(FIND(A82,$A$73:$H$79)),"",COLUMN($A$73:$H$79)))-2)

复制代码

07:

1. =(SUM(A91:D97)-(SUM(LARGE(A91:D97,{1,2}))+SUM(SMALL(A91:D97,{1,2,3}))))/(COUNTA(A91:D97)-5)

复制代码

08:
法1：

1. =SUM((A105:A111=A108)*((B104:E104=C104)*(C105:C111)+(B104:E104=E104)*(E105:E111)))

复制代码

法2：

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

复制代码

09:

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

复制代码

zhs5483

【函中201301班】-C11-zhs5483
1、 =SUM(SUMIFS(D161:D172,B161:B172,{"宣传部","人事部"},C161:C172,"<50"))
2、 =SUM(SUMIF(A179:A185,{"A","C"},B179:B185))
3、月均产值(10199.56) {=SUM(B189:B204)/SUM(ROUNDUP(LEN(A189:A204)/3,0))}
     月均产值(37042.71) =AVERAGE(AVERAGEIFS($B$189:$B$204,$A$189:$A$204,{"*01*","*02*","*03*","*04*","*05*","*06*","*07*","*08*","*09*","*10*","*11*","*12*"}))
     二月平均产值 =AVERAGEIF(A189:A204,"*02*",B189)
     即有二月又有四月的产值和  =SUMIF(A189:A204,"*02*04*",B189)
4、 =SUM(COUNTIFS(A212:A220,{"<60",">90"}))
5、 =COUNTIF(A224:A228,"*小妖*")
6、 =SUMIF($B$232:$H$238,A241,$A$232)     下拉
7、 =AVERAGEIFS(A250:D256,A250:D256,"<"&LARGE(A250:D256,2),A250:D256,">"&SMALL(A250:D256,3))
8、 =VLOOKUP("广州",A263:E270,MATCH("上年二季度",A263:E263,0),0)+VLOOKUP("广州",A263:E270,MATCH("本年二季度",A263:E263,0),0)
9、一季度 =SUM(SUMIF($A$277:$A$284,{1,2,3},$B$277))
     二季度 =SUM(SUMIF($A$277:$A$284,{4,5,6},$B$277))
    三季度 =SUM(SUMIF($A$277:$A$284,{7,8,9},$B$277))
     四季度 =SUM(SUMIF($A$277:$A$284,{10,11,12},$B$277))

wangzan

第一题：=SUM((B161:B172={"人事部","宣传部"})*(C161:C172<50)*D161:D172)
第二题：=SUMIF(A179:A185,"A",B179)+SUMIF(A179:A185,"C",B179)
第三题：=SUM(B189:B204)/(SUM((LEN(A189:A204)+1))/3)
           =SUMIF(A189:A204,"*02*",B189:B204)/COUNTIF(A189:A204,"*02*")
           =SUMIF(A189:A204,"*02*04*",B189:B204)
第四题：=COUNTIF(A212:A220,"<60")+COUNTIF(A212:A220,">90")
第五题：=SUM((IFERROR(FIND("小妖",A224:A228),0)>0)*1)
           =SUM(LEN(A224:A228))-SUM(LEN(SUBSTITUTE(A224:A228,"小妖","1")))
第六题：=SUMIF(B$232:H$238,A241,A$232)
第七题：=SUMIFS(A250:D256,A250:D256,"<"& LARGE(A250:D256,2),A250:D256,">"&SMALL(A250:D256,3))/COUNTIFS(A250:D256,"<"& LARGE(A250:D256,2),A250:D256,">"&SMALL(A250:D256,3))
           =AVERAGEIFS(A250:D256,A250:D256,"<"& LARGE(A250:D256,2),A250:D256,">"& SMALL(A250:D256,3))
第八题:=SUMIFS(C264:C270,A264:A270,"广州")+SUMIFS(E264:E270,A264:A270,"广州")
第九题：=SUMIFS(B$277:B$284,A$277:A$284,"<="&ROW(A1)*3,A$277:A$284,">"&ROW(A1)*3-3)
【函中201301班】-C09-wangzan

水上漂123

【函中201301班】-C05-水上漂123
第一题
=SUMPRODUCT(((B161:B172=B161)+(B161:B172=B164))*(C161:C172<50)*D161:D172)
第二题
=SUMPRODUCT(((A179:A185=A179)+(A179:A185=A182))*B179:B185)
第三题

第四题
=COUNTIF(A212:A221,"<60")+COUNTIF(A212:A220,">90")
第五题

第六题

第七题

第八题

第九题