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

lyf7276

1 =SUM(SUMIFS(D161:D172,B161:B172,{"人事部";"宣传部"},C161:C172,{"<50"}))
2 =SUM(SUMIF(A179:A185,{"A","C"},B179))
3/1 =SUMPRODUCT((LEN(SUBSTITUTE(A189:A204,",",))/2)*B189:B204)/SUM(LEN(SUBSTITUTE(A189:A204,",",))/2)
3/2 =AVERAGEIF(A189:A204,"*02*",B189:B204)
3/3 =SUMIF(A189:A204,"*02*4*",B189:B204)
4 =COUNTIF(A212:A220,"<60")+COUNTIF(A212:A220,">90")
5 =COUNTIF(A224:A228,"*妖*")
7 =(SUM(A250:D256)-SUM(LARGE(A250:D256,{1,2}))-SUM(SMALL(A250:D256,{1,2,3})))/23
8= SUM(SUMIF(A264:A270,"广州",OFFSET(A264,,MATCH({"上*二*","本*二*"},B263:E263,))))
9 =SUMPRODUCT((ROUNDUP((A$277:A$284/3),)=ROW(A1))*1,B$277:B$284)

木果果木

{:14:}{:14:}{:14:}{:14:}
yuyu这几天特殊情况，交作业交晚了，请yuyu收了吧
【函中201301班】- B11-木果果木
学委辛苦{:24:}
第1题：

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

复制代码

第2题：

1. =SUMIF(A20:A26,"<>B",B20:B26)
   
2. =SUM(SUMIFS(B20:B26,A20:A26,{"A","C"}))

复制代码

第3题：

1. =SUM(B30:B45)/(LEN(SUBSTITUTE(PHONETIC(A30:A45),",",""))/2)
   
2. =AVERAGEIF(A30:A45,"*02*",B30:B45)
   
3. =SUMIF(A30:A45,"*02*04*",B30:B45)

复制代码

第4题：

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

复制代码

第5题：数组公式

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

复制代码

第6题：没做出来

第7题：

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

复制代码

第8题：

1. =SUMPRODUCT((ROW(B105:E111)-104=MATCH("广州",$A$105:$A$111,0))*(MOD(COLUMN(B105:E111)-1,2)=0)*B105:E111)

复制代码

第9题：公式下拉

1. =SUMIFS($B$118:$B$125,$A$118:$A$125,">="&(1+3*(ROW(A1)-1)),$A$118:$A$125,"<="&(3*ROW(A1)))

复制代码

gaawx

【函中201301班】- B19-GAAWX
由于没有安2010，只有2003，这次的作业做不出来

wangjianff

【函中201301班】- B12-wangjianff
第一题：

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

复制代码

第二题：

1. =SUMPRODUCT(((A179:A185=A179)+(A179:A185=A182))*B179:B185)

复制代码

第三题：

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

复制代码

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

复制代码

1. =2*AVERAGEIFS(B189:B204,A189:A204,"*02*",A189:A204,"*4*")

复制代码

第四题：

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

复制代码

第五题：

1. {=SUM(LEN(A224:A228)-LEN(SUBSTITUTE(A224:A228,"小妖",1)))}

复制代码

第六题：

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

复制代码

第七题：

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

复制代码

第八题：

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

复制代码

最近比较忙，作业交晚了，不好意思啊

365221582

学习一下。