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

蓝火雨

【函中201301班】-A21-蓝火雨
1、=SUM(SUMIFS(D2:D13,B2:B13,{"宣传部","人事部"},C2:C13,"<50"))
2、=SUM(SUMIFS(B20:B26,A20:A26,{"A","C"}))
3、=AVERAGEIFS(B30:B45,A30:A45,"*02*")
=SUMIFS(B30:B45,A30:A45,"*02*04*")
4、=SUM(COUNTIFS(A53:A61,{"<60",">90"}))
5、=SUM(COUNTIFS(A65:A69,"*小妖*"))
6、
7、=AVERAGEIFS(A91:D97,A91:D97,"<"&LARGE(A91:D97,2),A91:D97,">"&SMALL(A91:D97,3))
8、=AVERAGEIF(A105:A111,"广州",OFFSET(A104,1,MATCH("上年二季度",B104:E104,)))+AVERAGEIF(A105:A111,"广州",OFFSET(A104,1,MATCH("本年二季度",B104:E104,)))
9、=SUMIFS($B$118:$B$125,$A$118:$A$125,"<="&ROW(1:1)*3)-SUM($D$126:D126)

as0810114

【函中201301班】-A12-as0810114
1.=SUM(SUMIFS(D161:D172,B161:B172,{"宣传部","人事部"},C161:C172,"<50"))
   数组  =SUM((B161:B172<>"销售部")*(C161:C172<50)*D161:D172)
2.  =SUM(SUMIF(A179:A185,{"A","C"},B179))
     =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,"*02*4*",B189)
4.   =SUM(COUNTIF(A212:A220,{">90","<60"}))
      数组=SUM((A212:A220>90)+(A212:A220<60))
5.  数组 =COUNT(1/FIND("小妖",A224:A228))
    =COUNTIF(A224:A228,"*小妖*")
6.  =SUMIF(B$232:H$238,A241,A$232)
7.数组=TRIMMEAN(LARGE(A250:D256,ROW(1:27)),4/27)
  数组=AVERAGE(SMALL(A250:D256,ROW(4:26)))
8.  =SUM(SUMIF(A264:A270,A267,OFFSET(A264,,{2,4})))
9.  F286输入公式       =SUMIF(A$277:A284,"<="&3*ROW(A1),B$277)-SUM(F$285:F285)
    E286输入公式   数组   =SUM((LEN(2^$A$277:$A$284)=ROW(A1))*B$277:B$284)

rlz680926

【函中201301班】—A24
        第一题： 方法一：=SUMPRODUCT((B2:B13={"宣传部","人事部"})*(C2:C13<50)*D2:D13)
                      方法二：=SUM(SUMIFS(D2:D13,B2:B13,{"宣传部","人事部"},C2:C13,"<50"))
        第二题：方法一：=SUM((A20:A26={"A","C"})*B20:B26)三键组合
                      方法二：=SUM(SUMIFS(B20:B26,A20:A26,{"A","C"}))
        第三题：一小题：=SUM(B30:B45)/SUM(LEN(SUBSTITUTE(A30:A45,",",))/2)三键组合
                     二小题：方法一：=SUM((IF(IFERROR(FIND("02",A30:A45),0)>0,1,0)>0)*B30:B45)/SUM(IF(IFERROR(FIND("02",A30:A45),0)>0,1,0))三键组合
                                  方法二：=AVERAGEIFS(B30:B45,H30:H45,">0")
                   三小题：=SUM((IFERROR(FIND("02",A30:A45),0)>0)*(IFERROR(FIND("04",A30:A45),0)>0)*B30:B45)三键组合
                    
第四题：=SUM(COUNTIF(A53:A61,{"<60",">90"}))
第五题：方法一：=COUNTIF(A65:A69,"*小妖*")
             方法二：=SUM((LEN(A65:A69)-LEN(SUBSTITUTE(A65:A69,"小妖",)))/2)三键组合
第六题：方法一：=INDEX($A$73:$H$79,SUM(($A$73:$H$79=A82)*ROW($1:$7)),SUM(($A$73:$H$79=A82)*COLUMN(A:H))-1)三键组合，下拉
             方法二：=SUMIF($B$73:$H$79,A82,$A$73)下拉
第七题：方法一：=AVERAGEIFS(A91:D97,A91:D97,">"&SMALL(A91:D97,3),A91:D97,"<"&LARGE(A91:D97,2))
             方法二：=SUM((A91:D97>SMALL(A91:D97,3))*(A91:D97<LARGE(A91:D97,2))*A91:D97)/23三键组合
第八题：=SUM(SUM(OFFSET(A104,MATCH("广州",A105:A111,0),{2;4},1,1)))
第九题   =SUM(SUMIF($A$118:$A$125,3*ROW(A1)-{0,1,2},$B$118:$B$125))下拉

dididing

【函中201301班】-A18-dididing

　第一题

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

复制代码

第二题 ：

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

复制代码

第三题
3.1

1. =SUMPRODUCT(ROUNDUP(LEN(A189:A204)/3,),B189:B204)/SUM(ROUNDUP(LEN(A189:A204)/3,))

复制代码

3.2

1. =SUMPRODUCT(IFERROR(MID(A189:A204,IFERROR(FIND("02",A189:A204,1),0),2)="02",0)*1,B189:B204)/COUNTIF(A189:A204,"*02*")

复制代码

重交一个

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

复制代码

3.3

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

复制代码

第四题

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

复制代码

第五题

1. =COUNT(0/(MID(PHONETIC(A224:A228),ROW(1:99),2)="小妖"))

复制代码

第六题

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

复制代码

第七题

1. =TRIMMEAN(LARGE(A250:D256,ROW(1:27)),4/27)

复制代码

第八题

1. =SUMPRODUCT((A264:A270="广州")*(MID(B263:E263,3,1)="二"),B264:E270)

复制代码

明天再交一个SUM的公式 ，

第九题

1. =SUM((-INT(-A$277:A$284/3)=ROW(A4))*B$277:B$284)

复制代码

hjcotton

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

zjyxp

【函中201301班A10-zjyxp上交第七讲作业，请老师指导批改
第一题，数组公式
=SUM(SUMIFS(D161:D172,B161:B172,{"人事部";"宣传部"},C161:C172,"<50"))
第二题 数组公式
=SUM(SUMIF(A179:A185,{"A";"C"},B179:B185))
第三题
数组1=SUM(((LEN(A189:A204)+1)/3)*B189:B204)/SUM((LEN(A189:A204)+1)/3)
数组2=AVERAGE(IF(FIND("02",A189:A204&"0002")<=4,B189:B204))
      2=AVERAGEIF(A189:A204,"*02*",B189:B204)
     3=SUMIF(A189:A204,"*02*04*",B189:B204)
第四题 数组
=SUM(COUNTIF(A212:A220,{">90";"<60"}))
第五题
=COUNTIF(A224:A228,"*小妖*")
第六题
=SUMIF(B$232:H$238,A241,A$232)
第七题：数组
=AVERAGE(IF((A250:D256<LARGE(A250:D256,2))*(A250:D256>SMALL(A250:D256,3)),A250:D256))
第八题 数组
=SUM(SUBTOTAL(9,OFFSET(A263,MATCH("广州",A264:A270,),{2;4})))
第九题 数组
=SUM((LOOKUP(A$277:A$284,{0,"一季度";4,"二季度";7,"三季度";10,"四季度"})=A286)*B$277:B$284)

xiaox0124

A01-xiaox0124
1.

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

复制代码

2.

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

复制代码

3.

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

复制代码

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

复制代码

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

复制代码

4.

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

复制代码

5.

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

复制代码

6.

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

复制代码

7.

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

复制代码

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

复制代码

8.

1. =SUMIF(B104:E111,"*二*",INDIRECT("B"&ROW(A108)))

复制代码

9.

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

复制代码

augustzhu1983

【函中201301班】-A08-augustzhu1983
第一题

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

复制代码

1. =SUM((B161:B172<>"销售部")*(C161:C172<50)*D161:D172)

复制代码

第二题

1. =SUMIF(A179:A185,"<>B",B179)

复制代码

第三题

1. =SUM((LEN(A189:A204)+1)*B189:B204)/SUM(LEN(A189:A204)+1)

复制代码

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

复制代码

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

复制代码

第四题

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

复制代码

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

复制代码

第五题

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

复制代码

第六题

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

复制代码

第七题

1. =TRIMMEAN(IF(A250:D256>MIN(A250:D256),A250:D256),4/27)

复制代码

第八题

1. =SUMIF(A263:E263,"*二季度",OFFSET(A263,MATCH("广州",A264:A270,),,,5))

复制代码

第九题

1. =SUM((LEN(2^A$277:A$284)=ROW(A1))*B$277:B$284)

复制代码

jerryzhouah

【函中201301班】-A13-jerryzhouah

第一题：

1. =SUMPRODUCT((((B161:B172=B161)+(B161:B172=B164))*(C161:C172<=50))*D161:D172)

复制代码

第二题：

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

复制代码

第三题：


第四题：

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

复制代码

第八题：

1. =SUMIF(A264:A270,A267,C264)+SUMIF(A264:A270,A267,E264)

复制代码

bere