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

ws278106483

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

石岗

【函中201301班】-A23-石岗
第一题：=SUMIFS(D161:D172,B161:B172,"<>销售部",C161:C172,"<50")
            =SUM(SUMIFS(D161:D172,B161:B172,{"宣传部","人事部"},C161:C172,{"<50","<50"}))
            =SUMPRODUCT(D161:D172*(B161:B172={"宣传部","人事部"})*(C161:C172<50))
第二题：=SUM(SUMIFS(B179:B185,A179:A185,{"A","C"}))
            =SUMPRODUCT(B179:B185*(A179:A185={"A","C"}))
第三题：月均产值：=SUMPRODUCT(((LEN(SUBSTITUTE(A189:A204,",",""))/2)*B189:B204))/SUMPRODUCT((LEN(SUBSTITUTE(A189:A204,",",""))/2)*1)
           二月平均产值：=SUMIFS(B189:B204,A189:A204,"*02*")/COUNTIF(A189:A204,"*02*")或=AVERAGEIFS(B189:B204,A189:A204,"*02*")
           二月和四月产值和：=SUMIFS(B189:B204,A189:A204,"*2*4*")
第四题：=COUNTIF(A212:A220,">90")+COUNTIF(A212:A220,"<60")或=COUNT(A212:A220)-COUNTIFS(A212:A220,"<90",A212:A220,">60")
第五题：=COUNTIFS(A224:A228,"*小妖*")或=LEN(PHONETIC(A224:A228))-LEN(SUBSTITUTE(PHONETIC(A224:A228),"小妖","1"))
第六题：=SUMIF(B$232:H$238,A241,A$232:G$238)
第七题：=AVERAGEIFS(A250:D256,A250:D256,">"&SMALL(A250:D256,3),A250:D256,"<"&LARGE(A250:D256,2))
第八题：=SUMIF(A264:A270,"广州",C264:C270)+SUMIF(A264:A270,"广州",E264:E270)
第九题：=SUMIFS(B$277:B$284,A$277:A$284,">"&(ROW(A1)*3-3),A$277:A$284,"<"&(3*ROW(A2)-2))
请老师批改。因为下周工作关系，估计都在外面跑，所以有些题可能没有仔细思考。

daichaodaichao

【函中20130301】A09-daichaodaichao

1. 1. =SUMPRODUCT(((B161:B172="宣传部")+(B161:B172="人事部"))*
   
2. 
   
3. (C161:C172<50)*D161:D172)
   
4. =SUMIFS(D161:D172,B161:B172,"宣传部",C161:C172,"<50")+SUMIFS
   
5. 
   
6. (D161:D172,B161:B172,"人事部",C161:C172,"<50")
   
7. =SUMPRODUCT(((B161:B172={"宣传部","人事部"}))*(C161:C172<50)
   
8. 
   
9. *D161:D172)
   
10. 2. =SUM(SUMIFS(B179:B185,A179:A185,{"A","C"}))
    
11. =SUMPRODUCT(((A179:A185="A")+(A179:A185="C"))*B179:B185)
    
12. 3. =SUMPRODUCT((LEN(SUBSTITUTE(A189:A204,",",""))/2)*B189:B204)/
    
13. 
    
14. (SUMPRODUCT(LEN(SUBSTITUTE(A189:A204,",","")))/2)
    
15. =AVERAGEIF(A189:A204,"*02*",B189)
    
16. =SUMIF(A189:A204,"*02*04*",B189)
    
17. =SUMPRODUCT((LEN(A189:A204)<>LEN(SUBSTITUTE(A189:A204,"02","")))
    
18. 
    
19. *B189:B204)/(SUMPRODUCT(LEN(A189:A204))-SUMPRODUCT(LEN(SUBSTITUTE
    
20. 
    
21. (A189:A204,"02",1))))
    
22. =SUMPRODUCT((LEN(A189:A204)<>LEN(SUBSTITUTE(A189:A204,"02","")))
    
23. 
    
24. *B189:B204)/COUNTIF(A189:A204,"*02*")
    
25. 4. =COUNTIF(A212:A220,"<60")+COUNTIF(A212:A220,">90")
    
26. =SUM(COUNTIF(A212:A220,{"<60",">90"}))
    
27. 5. =SUM(LEN(A224:A228)-LEN(SUBSTITUTE(A224:A228,"小妖",1)))
    
28. =SUMPRODUCT(LEN(A224:A228)-LEN(SUBSTITUTE(A224:A228,"小妖",1)))
    
29. =LEN(PHONETIC(A224:A228))-LEN(SUBSTITUTE(PHONETIC(A224:A228),"小
    
30. 
    
31. 妖",1))
    
32. =COUNTIF(A224:A228,"*小妖*")
    
33. =SUM(N(MID(A224:A228,COLUMN(A:Z),2)="小妖"))
    
34. 6. =SUMIF($B$232:$I$238,A241,$A$232)
    
35. 7. =(SUM(A250:D256)-LARGE(A250:D256,1)-LARGE(A250:D256,2)-SMALL
    
36. 
    
37. (A250:D256,1)-SMALL(A250:D256,2)-SMALL(A250:D256,3))/(COUNT(A250:D256)
    
38. 
    
39. -5)
    
40. =TRIMMEAN(A250:E256,6/COUNT(A250:E256))
    
41. =AVERAGE(SMALL(A250:D256,ROW(4:26)))
    
42. 8. =SUMIF(A264:A270,"广州",C264:C270)+SUMIF(A264:A270,"广州",E264)
    
43. 9. =SUM(SUMIFS(B277:B284,A277:A284,{1,2,3}))
    
44. =SUM(SUMIFS(B277:B284,A277:A284,{4,5,6}))
    
45. =SUM(SUMIFS(B277:B284,A277:A284,{7,8,9}))
    
46. =SUM(SUMIFS(B277:B284,A277:A284,{10,11,12}))
    

复制代码

风林火山

1         =SUM((B161:B172<>"销售部")*(C161:C172<50)*D161:D172)                                                       
2         =SUM((A179:A185<>"B")*B179:B185)                                                       
3.1         =SUM(B189:B204*LEN(A189:A204&1))/SUM(LEN(A189:A204&1))                                                       
3.2         =SUMIF(A189:A204,"*02*",B189)/COUNTIF(A189:A204,"*02*")                                                       
3.3         =SUMIF(A189:A204,"*02*04*",B189)                                                       
4         =SUM((A212:A220<60)+(A212:A220>90))                                                       
5         =COUNTIF(A224:A228,"*小妖*")                                                       
6         =SUMIF(B$232:H$238,A241,A$232:H$238)                                                       
7         =TRIMMEAN(LARGE(A250:D256,ROW(1:27)),5/27)                                                       
8         =SUM((A264:A270=A267)*(MOD(COLUMN(B264:E264),2)=1)*B264:E270)                                                       
9         =SUM((LEN(2^$A$277:$A$284)=ROW(A1))*B$277:B$284)                                                       

fcyxiaoyuer

1. =SUMIFS(D161:D172,C161:C172,"<50",B161:B172,"<>销售部")  第一题

复制代码

1. =TEXT(SUMPRODUCT(B179:B185*(A179:A185<>"B")),"0") 第二题

复制代码

1. =SUM(B189:B204)/(SUM(LEN(A189:A204)+1)/3) 第三题1 数组公式

复制代码

1. =AVERAGEIF(A189:A204,"*02*",B189) 第三题2

复制代码

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

复制代码

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

复制代码

1. =COUNTIF(A224:A228,"*小妖*") 第五题 数组公式

复制代码

1. 第六题空

复制代码

1. =(TRIMMEAN(A250:D256,4/COUNT(A250:D256))*(COUNT(A250:D256)-4)-SMALL(A250:D256,3))/(COUNT(A250:D256)-5) 第七题

复制代码

1. 第八题空

复制代码

1. =SUMPRODUCT($B$277:$B$284*(ROUNDUP($A$277:$A$284/3,0)=ROW(A1))) 第九题 1

复制代码

zxh_273

【函中201301班】-A07-zxh_273
第一题：

1. =SUM(((B2:B13="人事部")+(B2:B13="宣传部"))*(C2:C13<50)*D2:D13)

复制代码

三键
第二题：

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

复制代码

三键
第三题：月均产值

1. =SUM(LEN(SUBSTITUTE(A30:A45,",",""))/2*(B30:B45))/(SUM(LEN(SUBSTITUTE(A30:A45,",",""))/2))

复制代码

三键
二月平均产值

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

复制代码

月份列即有二月又有四月对应产值和

1. =SUMIF(A30:A45,"*02*04*",B30:B45)

复制代码

第四题：

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

复制代码

第五题：

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

复制代码

三键
第六题：

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

复制代码

第七题：

1. =AVERAGE(LARGE(A91:D97,ROW(3:25)))

复制代码

第八题：

1. =SUMIF(A105:A111,"广州",C105)+SUMIF(A105:A111,"广州",E105)

复制代码

第九题：

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

复制代码

十五的月亮

【函中201301班】-A14-十五的月亮
1题

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

复制代码

2题

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

复制代码

3题

1. =SUM(B189:B204*((LEN(A189:A204)+1)/3))/SUM((LEN(A189:A204)+1)/3)
   
2. =AVERAGEIFS(B189:B204,A189:A204,"*02*")
   
3. =SUMIF(A189:A204,"*02*04*",B189)

复制代码

4题

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

复制代码

5题

1. =COUNTIF(A224:A228,"*小妖*")
   
2. =SUM(LEN(A224:A228)-LEN(SUBSTITUTE(A224:A228,"妖","")))

复制代码

6题

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

复制代码

7题

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

复制代码

8题

1. SUM((RIGHT(B263:E263,3)="二季度")*B264:E270*(A264:A270=A267))
   
2. =SUM((RIGHT(B263:E263,3)="二季度")*OFFSET(B263:E263,MATCH(A267,A264:A270,),))

复制代码

9题

1. =SUM((TEXT(LEN(2^(A$277:A$284)),"[DBNum1]")&"季度"=A286)*B$277:B$284)

复制代码

ddmyykdzh

【函中201301班】-A11-ddmyykdzh

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

复制代码

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

复制代码

1. =SUM(B30:B45)/(LEN(SUBSTITUTE(PHONETIC(A30:A45),",",""))/2)
   
2. 
   
3. =AVERAGE(LARGE((IFERROR(FIND("02",A30:A45),0)<>0)*B30:B45,ROW(INDIRECT("1:"&COUNT(FIND("02",A30:A45))))))
   
4. 
   
5. =SUM(IFERROR((IFERROR(FIND("02",A30:A45),0)*IFERROR(FIND("04",A30:A45),0))<>0,0)*B30:B45)

复制代码

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

复制代码

1. =(LEN(PHONETIC(A65:A69))-LEN(SUBSTITUTE(PHONETIC(A65:A69),"小妖","")))/2
   
2. 
   
3. =SUM(--IFERROR(IFERROR(FIND("小妖",A65:A69),0)<>0,))

复制代码

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

复制代码

1. =SUM(N(OFFSET(A104,MATCH("广州",A105:A111,),MATCH({"上年二*","本年二*"},B104:E104,))))

复制代码

1. =SUMIFS(B118:B125,A118:A125,">"&ROW(A1)*3-3,A118:A125,"<"&ROW(A1)*3+1)

复制代码

漫天独舞

1. 【函中201301班】-A04-漫天独舞
   
2. 1、=SUMIFS(D161:D172,C161:C172,"<50",B161:B172,"<>销售部")
   
3. 2、=SUMIF(A179:A185,"<>B",B179:B185)
   
4. 3、（1） =SUM(B189:B204*(LEN(SUBSTITUTE(A189:A204,",",))/2))/SUM(LEN(SUBSTITUTE(A189:A204,",",))/2)
   
5. （2）=SUM((MID(SUBSTITUTE(A189:A204,",",),FIND("02",SUBSTITUTE(A189:A204,",",)&"02"),2)="02")*B189:B204)/COUNT(IFERROR(FIND("02",SUBSTITUTE(A189:A204,",",)),""))
   
6. （3）=SUM(IFERROR(((MID(A189:A204,FIND("04",A189:A204)+1,1)="4")*(MID(A189:A204,FIND("02",A189:A204)+1,1)="2")*B189:B204),""))
   
7. 4、=SUM(IF((A212:A220<60)+(A212:A220>90),1,0))
   
8. 5、=SUM(LEN(A224:A228)-LEN(SUBSTITUTE(A224:A228,"小妖",)))/2
   
9. 6、不会
   
10. 7、=(SUM((A250:D256>SMALL(A250:D256,3))*A250:D256)+SUM((A250:D256<LARGE(A250:D256,2))*A250:D256)-SUM(A250:D256))/(COUNT(A250:D256)-5)
    
11. 8、=SUM(INDEX(A264:E270,MATCH(A267,A264:A270,0),MATCH(C263,B263:E263,0)+1)+INDEX(A264:E270,MATCH(A267,A264:A270,0),MATCH(E263,B263:E263,0)+1))
    
12. 9、=SUM(($A$277:$A$284<(MOD(ROW(),5)*3+1))*$B$277:$B$284)-SUM(F$285:F285)
    
13. 交了再说

复制代码

myszcpf

【函中201301班】-A15-MYSZCPF
第1题：

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

复制代码

第2题：

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

复制代码

第3题：

1. 月均产值:{=SUM(B30:B45)/(SUM(LEN(SUBSTITUTE(A30:A45,",","")))/2)}

复制代码

1. 二月平均产值=SUMIFS(B30:B45,A30:A45,"*02*")/COUNTIFS(A30:A45,"*02*")

复制代码

1. 月份列即有二月又有四月对应产值和=SUMIFS(B30:B45,A30:A45,"*02*04*")

复制代码

第4题：

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

复制代码

第5题：

1. =SUM(COUNTIF(A65:A69,"*小妖*"))

复制代码

第6题： E82单元格下拉

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

复制代码

第7题：

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

复制代码

第8题：

1. =SUMPRODUCT((A105:A111=A108)*(C105:C111))+SUMPRODUCT((A105:A111=A108)*(E105:E111))

复制代码

第9题：    E127单元格下拉

1. {=SUM(SUMIF($A$118:$A$125,ROW(A1:A3)+(ROW(A1)*2-2),$B$118:$B$125))}

复制代码