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

顺⑦.zì繎。

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

先开帖。。日后评分。。

笨笨四

【函中201301班】-D09-笨笨四
1。=SUMIFS(D161:D172,B161:B172,"<>销*",C161:C172,"<50")
2。=SUMIF(A179:A185,"<>B",B179)
3.1。=SUM(B189:B204*LEN(A189:A204&1))/SUM(LEN(A189:A204&1))
3.2。=AVERAGEIF(A189:A204,"*02*",B189)
3.3。=SUMIF(A189:A204,"*2*4*",B189)
4。=SUM((A212:A220>={0,60,90})*{1,-1,1})
    =SUM(N((A212:A220-75)^2>225))
5。=COUNT(FIND("小妖",A:A))-1
6。=SUMIF(B$232:H238,A241,A$232)
7。=TRIMMEAN(LARGE(A250:D256,ROW(1:27)),5/28)
     =AVERAGE(LARGE(A250:D256,ROW(3:25)))
8。=SUM((RIGHT(B263:E263&A264:A270,5)="二季度广州")*B264:E270)
9。=SUM((LEN(2^A$277:A$284)=ROW(A1))*B$277:B$284)
     =SUM((LOOKUP(A$277:A$284,{1,4,7,10})+2=ROW(A1)*3)*B$277:B$284)

林玲玲

【函中201301班】-D11-林玲玲
1、=SUM(SUMIFS(D161:D172,B161:B172,{"人事部","宣传部"},C161:C172,"<50"))
2、=SUM(SUMIFS(B179:B185,A179:A185,{"A","C"}))
3.1、=SUM(B189:B204*(ROUNDUP(LEN(A189:A204)/3,)))/SUM(ROUNDUP(LEN(A189:A204)/3,))
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、=COUNTIFS(A224:A228,"*小妖*")
6、=SUMIF(B$232:H$238,A241,A$232)
7、=(SUM(A250:D256)-SUM(LARGE(A250:D256,{1,2}))-SUM(SMALL(A250:D256,{1,2,3})))/(28-5)
8、=SUM((A264:A270=A267)*(C264:C270+E264:E270))
9、=SUM(SUMIFS(B$277:B$284,A$277:A$284,ROW(1:3)+ROW(A1)*2-2))

岁月静好

【函中201301班】-D24-岁月静好  真不容易第一次 全部做出来。
1、=SUMIFS(D161:D172,B161:B172,"<>销售部",C161:C172,"<50")
   =SUM(SUMIFS(D161:D172,C161:C172,"<50",B161:B172,{"宣传部","人事部"}))
2、=SUMIF(A179:A185,"<>B",B179)
     =SUM(SUMIFS(B179:B185,A179:A185,{"A","C"}))
3.1 、=SUMPRODUCT((LEN(SUBSTITUTE(A189:A204,",",))/2)*B189:B204)/SUM(LEN(SUBSTITUTE(A189:A204,",",))/2)  数组
3.2、=AVERAGEIF(A189:A204,"*02*",B189)
        =SUMIF(A189:A204,"*02*",B189)/COUNTIF(A189:A204,"*02*")
3.3、=SUMIF(A189:A204,"*02*04*",B189)
4、=SUM(COUNTIFS(A212:A220,{"<60";">90"}))
5、=COUNTIFS(A224:A228,"*妖*")
6、=SUMIF($B$232:$I$238,A241,$A$232)
7、=(SUM(A250:D256)-SUM(LARGE(A250:D256,{1,2}))-SUM(SMALL(A250:D256,{1,2,3})))/(COUNT(A250:D256)-5)
8、=SUMPRODUCT(((A264:A270="广州")*(B263:E263="上年二季度"))*B264:E270)+SUMPRODUCT(((A264:A270="广州")*(B263:E263="本年二季度"))*B264:E270)
9、=SUM(SUMIFS($B$277:$B$284,$A$277:$A$284,({1,2,3}+(ROW(A1)-1)*3)))

晓晓的秋天

1. 1、=SUM(SUMIFS(D2:D13,B2:B13,{"人事部","宣传部"},C2:C13,"<50"))
   
2. 2、=SUM(SUMIFS(B20:B26,A20:A26,{"A","C"}))
   
3. 3、=SUM(B30:B45)/(SUM(LEN(A30:A45&","))-SUM(LEN(SUBSTITUTE(A30:A45&",",",",))))
   
4. 4、=SUM(COUNTIFS(A53:A61,{"<60",">90"}))
   
5. 5、=LEN(PHONETIC(A65:A69))-LEN(SUBSTITUTE(PHONETIC(A65:A69),"小妖",1))
   
6. 6、=INDEX(A$73:H$79,MIN((IF(A$73:H$79=A82,ROW($1:$7)))),MIN((IF(A$73:H$79=A82,COLUMN(A:H))))-1)
   
7. 7、=AVERAGEIFS(A91:D97,A91:D97,"<"&LARGE(A91:D97,2),A91:D97,">"&SMALL(A91:D97,3))
   
8. 8、=SUM(VLOOKUP("广州",A104:E111,MATCH({"上年二季度","本年二季度"},A104:E104,),))
   
9. 9、=SUMIFS(B$118:B$125,A$118:A$125,"<="&3*ROW(A1),A$118:A$125,">"&3*(ROW(A1)-1))

复制代码

lucking551

【函中201301班】-D05-lucking551

第一题：

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

复制代码

第二题：

1. =SUMPRODUCT((A20:A26<>"B")*(B20:B26))

复制代码

第三题：
A：数组

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

复制代码

B：数组

1. =SUM(IF(IF(ISERROR(FIND("02",A30:A45)),0,FIND("02",A30:A45))>0,B30:B45))/SUM((IF(ISERROR(FIND("02",A30:A45)),0,FIND("02",A30:A45))>0)*1)

复制代码

C：数组

1. =SUM((LEN(A30:A45)-LEN(SUBSTITUTE(SUBSTITUTE(A30:A45,"02",""),"04",""))=4)*(B30:B45))

复制代码

第四题：

1. =COUNTIF(A53:A61,"<60")+COUNTIF(A53:A61,">90")

复制代码

第五题：

1. =LEN(PHONETIC(A65:A69))-LEN(SUBSTITUTE(PHONETIC(A65:A69),"小妖","啊"))

复制代码

第六题：数组

1. =INDEX($A$73:$H$79,MAX(IF(ISERROR(FIND(A82,$A$73:$H$79)),,ROW($A$73:$H$79)-72)),MAX(IF(ISERROR(FIND(A82,$A$73:$H$79)),,COLUMN($A$73:$H$79)-1)))

复制代码

第七题：数组

1. =AVERAGE(IF(((A91:D97>large(A91:D97,3))+(A91:D97<small(A91:D97,4))),"A",A91:D97))

复制代码

第八题：数组

1. =SUM(IF(A105:A111="广州",1)*IF((B104:E104="上年二季度")+(B104:E104="本年二季度"),1)*(B105:E111))

复制代码

第九题：数组

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

复制代码

cg372101

【函中201301班】-D18-cg372101  第7讲作业：
题1：（数组公式）

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

复制代码

题2：（数组公式）

1. =SUM(SUMIF(A20:A26,{"A","C"},B20))

复制代码

题3：（1）（数组公式）

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

复制代码

题3：（2）（数组公式）

1. =AVERAGE(IF(NOT(ISERR(FIND("02",A30:A45))),B30:B45))

复制代码

题3：（3）（数组公式）

1. =SUM(B30:B45*NOT(ISERR(SEARCH("02*4",A30:A45))))

复制代码

题4：（数组公式）

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

复制代码

题5：（数组公式）

1. =SUM(LEN(65:69))-SUM(LEN(SUBSTITUTE(65:69,"小妖",0)))

复制代码

题6：

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

复制代码

题7：（1）（数组公式）

1. =TRIMMEAN(IF(91:97>SMALL(91:97,1),91:97),4/(COUNT(A91:D97)-1))

复制代码

题7：（2）（数组公式）

1. =AVERAGE(IF((91:97>SMALL(91:97,3))*(91:97<LARGE(91:97,2)),91:97))

复制代码

题7：（3）

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

复制代码

题8：

1. =SUMPRODUCT((A105:A111="广州")*(ISERR(FIND("一",B104:E104)))*B105:E111)

复制代码

题9：（数组公式）

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

复制代码

liziyuliziyu7

【函中201301班】-d04-liziyuliziyu7
=SUM(SUMIFS(D161:D172,B161:B172,{"宣传部","人事部"},C161:C172,"<50"))
=SUM(SUMIF(A179:A185,{"C","A"},B179:B185))
=SUM(B189:B204)/SUM((LEN(A189:A204&",")-LEN(SUBSTITUTE(A189:A204&",",",",))))
=SUM(ISNUMBER(FIND("02",A189:A204))*B189:B204/(LEN(A189:A204&",")-LEN(SUBSTITUTE(A189:A204&",",",",))))/SUM(--ISNUMBER(FIND("02",A189:A204)))
=SUM(ISNUMBER(FIND("02",A189:A204)*FIND("04",A189:A204))*B189:B204)
=SUM(COUNTIFS(A212:A220,{"<60",">90"}))
=(LEN(PHONETIC(A224:A228))-LEN(SUBSTITUTE(PHONETIC(A224:A228),"小妖",)))/2
=INDEX($A$232:$H$238,MAX(ISNUMBER(FIND($A241,$A$232:$H$238))*ROW($1:$7)),MAX(ISNUMBER(FIND($A241,$A$232:$H$238))*COLUMN($A:$H))-1)
=(SUM(A250:D256)-SUM(LARGE(A250:D256,{1,2,26,27,28})))/23
=SUM(N(OFFSET(A263,MATCH("广州",A264:A270,),{2,4})))
=SUMIF(A$277:A$284,"<="&ROW(A1)*3,B$277)-SUM(D$285:D285)

veryloveep

【函中201301班】-D22-veryloveep

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

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

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

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

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

4.   =COUNT(A212:A220)-COUNTIFS(A212:A220,"<90",A212:A220,">60")

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

6.  =INDEX($A$232:$F$238,SUM(--(($A$232:$F$238)=A241)*ROW($A$232:$F$238))-231,SUM(--(($A$232:$F$238)=A241)*COLUMN($A$232:$F$238))-1)

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

8.   =SUMIFS(C263:C270,A263:A270,A267)+SUMIFS(E263:E270,A263:A270,A267)

9.   =SUM(SUMIFS($B$277:$B$284,$A$277:$A$284,{-1,-2,0}+3*ROW(A1)))

Shelvee

【函中201301班】-D15—Shelvee
1.=SUMPRODUCT(((B2:B13=B2)+(B2:B13=B5))*(C2:C13<50)*D2:D13)

2.=SUM(SUMIF(A20:A26,{"A","C"},B20:B26))

3.=SUM(B30:B45)/SUMPRODUCT(LEN(SUBSTITUTE(A30:A45,",",11))-LEN(A30:A45)+1)
  =AVERAGEIF(A30:A45,"*02*",B30:B45)
  =SUMIF(A30:A45,"*02*4*",B30:B45)

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

5.=SUM(LEN(A65:A69)-LEN(SUBSTITUTE(A65:A69,"小妖",1)))

6.

7.=SUMPRODUCT((A91:D97<LARGE(A91:D97,2))*(A91:D97>SMALL(A91:D97,3))*A91:D97)/(COUNT(A91:D97)-5)
  =AVERAGEIFS(A91:D97,A91:D97,"<"&LARGE(A91:D97,2),A91:D97,">"&SMALL(A91:D97,3))

8.=SUM(INDEX(A104:E111,MATCH("广州",A104:A111,0),MATCH("上年二季度",A104:E104,0)),INDEX(A104:E111,MATCH("广州",A104:A111,0),MATCH("本年二季度",A104:E104,0)))

9.