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

feedyu · 发表于 2013-6-19 09:33

本帖最后由 CheryBTL 于 2013-6-27 22:28 编辑

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

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

bere · 发表于 2013-6-19 09:46

看看，学习一下。

MYHERE131 · 发表于 2013-6-19 09:49

【函中201301班】- B02-MYHERE131
第一题：=SUMPRODUCT((B2:B13={"宣传部","人事部"})*(C2:C13<50)*D2:D13)
第二题：=SUMPRODUCT((A20:A26={"A","C"})*B20:B26)
第三题：
1）=SUMPRODUCT((LEN($A$30:$A$45)-LEN(SUBSTITUTE($A$30:$A$45,",",""))+1^16)*$B$30:$B$45)/SUMPRODUCT((LEN($A$30:$A$45)-LEN(SUBSTITUTE($A$30:$A$45,",",""))+1^16)*1)
2）=SUMPRODUCT((LEN($A$30:$A$45)-LEN(SUBSTITUTE($A$30:$A$45,"02","")))*$B$30:$B$45)/SUMPRODUCT((LEN($A$30:$A$45)-LEN(SUBSTITUTE($A$30:$A$45,"02","")))*1)
3）=SUMPRODUCT((LEN($A$30:$A$45)*2-LEN(SUBSTITUTE($A$30:$A$45,"02",""))-LEN(SUBSTITUTE($A$30:$A$45,"04",""))=4)*$B$30:$B$45)
第四题：=SUMPRODUCT(($A$53:$A$61<60)+($A$53:$A$61>90))
第五题：=SUMPRODUCT((LEN($A$65:$A$69)-LEN(SUBSTITUTE($A$65:$A$69,"小妖",""))))/LEN("小妖")
第六题：=IFERROR(OFFSET($A$72,MATCH(A82,$B$73:$B$79,0),,),0)+IFERROR(OFFSET($C$72,MATCH(A82,$D$73:$D$79,0),,),0)+IFERROR(OFFSET($E$72,MATCH(A82,$F$73:$F$79,0),,),0)+IFERROR(OFFSET($G$72,MATCH(A82,$H$73:$H$79,0),,),0)
第七题：
法一：=(SUM($A$91:$D$97)-LARGE($A$91:$D$97,1)-LARGE($A$91:$D$97,2)-SMALL($A$91:$D$97,1)-SMALL($A$91:$D$97,2)-SMALL($A$91:$D$97,3))/(COUNT($A$91:$D$97)-5)
法二：=(SUM($A$91:$D$97)-SUM(LARGE($A$91:$D$97,{1;2}))-SUM(SMALL($A$91:$D$97,{1;2;3})))/(COUNT($A$91:$D$97)-5)
第八题：
法一：=VLOOKUP("广州",$A$105:$E$111,3,0)+VLOOKUP("广州",$A$105:$E$111,5,0)
法二：=OFFSET($B$105,MATCH("广州",$A$105:$A$111,0)-1,1,1,1)+OFFSET($B$105,MATCH("广州",$A$105:$A$111,0)-1,3,1,1)
第九题：
第一季度=SUMPRODUCT(($A$118:$A$125={1,2,3})*$B$118:$B$125)
第二季度=SUMPRODUCT(($A$118:$A$125={4,5,6})*$B$118:$B$125)
第三季度=SUMPRODUCT(($A$118:$A$125={7,8,9})*$B$118:$B$125)
第四季度=SUMPRODUCT(($A$118:$A$125={10,11,12})*$B$118:$B$125)

云影 · 发表于 2013-6-19 09:54

本帖最后由云影于 2013-6-19 09:55 编辑

【函中201301班】- B15-云影
1.

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

复制代码

2.

=SUMPRODUCT(SUMIFS(B179:B185,A179:A185,{"A","C"}))

复制代码

3.

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

复制代码

4.

=COUNTIF(A212:A220,"<60")+COUNTIF(A212:A220,">90")

复制代码

5.

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

复制代码

6.

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

复制代码

7.

=AVERAGE(LARGE(A250:D256,ROW(3:25)))

复制代码

8.

=SUM(VLOOKUP(A267,A263:F270,IF(RIGHT(B263:E263,3)="二季度",COLUMN(B:E),6),))

复制代码

9.

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

复制代码

opqazxc · 发表于 2013-6-19 15:05

【函中201301班】- B18-opqazxc
1

=SUM(SUMIFS(D160:D172,C160:C172,"<50",B160:B172,{"人事部";"宣传部"}))

复制代码

2

=SUM(SUMIF(A178:A185,{"C";"A"},B178))

复制代码

3.1数组公式

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

复制代码

=AVERAGE(IF(MID(A189:A204,COLUMN(A:Z)*3-2,2)>"",B189:B204))

复制代码

3.2

=AVERAGEIF(A188:A204,"*02*",B188)

复制代码

3.3

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

复制代码

4

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

复制代码

5

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

复制代码

6

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

复制代码

7数组公式

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

复制代码

=AVERAGE(IF(ABS(RANK(A250:D256,A250:D256)-COUNT(A250:D256)/2)<12,A250:D256))

复制代码

普通公式

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

复制代码

8

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

复制代码

9

=SUMIFS(B$277:B284,A$277:A284,">"&ROW(A1)*3-3,A$277:A284,"<"&ROW(A1)*3+1)

复制代码

stone1969 · 发表于 2013-6-19 16:54

第一题 =SUM(SUMIFS(D2:D13,B2:B13,{"宣传部","人事部"},C2:C13,"<50"))
第二题 =SUM(SUMIFS(B20:B26,A20:A26,{"A","C"}))
第三题（1）=(SUMPRODUCT(B30:B45*LEN(SUBSTITUTE(A30:A45,",",""))/2))/SUMPRODUCT(LEN(SUBSTITUTE (A30:A45,",",""))/2*1)
（2）
=SUMPRODUCT(ISNUMBER(FIND("02",A30:A45))*B30:B45)/SUMPRODUCT(ISNUMBER(FIND("02",A30:A45))*1)
（3）=SUMPRODUCT(ISNUMBER(FIND("02",A30:A45)*FIND("04",A30:A45))*B30:B45)
第四题 =COUNTIF(A53:A61,"<60")+COUNTIF(A53:A61,">90")
第五题=(LEN(A65&A66&A67&A68&A69)-LEN(SUBSTITUTE(A65&A66&A67&A68&A69,"小妖",0)))
第六题=SUMIF(B$73:H$79,A82,A$73:G$79)
第七题第一种方法 =(SUM(A91:D97)-LARGE(A$91:D$97,1)-LARGE(A$91:D$97,2)-SMALL(A$91:D$97,1)-SMALL(A$91:D$97,2)-SMALL(A$91:D$97,3))/(COUNTIF(A91:D97,"<>")-2-3)
第二种方法 =TRIMMEAN(A91:D97,5/(COUNTA(A91:D97)+COUNTBLANK(A91:D97)))
第八题 =SUMIFS(C105:C111,A105:A111,A108)+SUMIFS(E105:E111,A105:A111,A108)
第九题 =SUMPRODUCT((MONTH(A$118:A$125*10)=ROW(A1))*B$118:B$125)

ykfexcel · 发表于 2013-6-19 17:19

【函中201301班】- B20-ykfexcel
1,=SUM(SUMIFS(D161:D172,B161:B172,{"人事部";"宣传部"},C161:C172,"<50"))
2,=SUM(SUMIFS(B179:B185,A179:A185,{"A";"C"}))
=SUM(SUMIF(A179:A185,{"A","C"},B179)) 三键
3,=SUM(B189:B204)/SUM(INT((LEN(A189:A204)+1)/3))三键
=AVERAGEIF(A189:A204,"*02*",B189)
=SUMIFS(B189:B204,A189:A204,"*02*",A189:A204,"*04*")
4,=SUM(COUNTIF(A212:A220,{"<60";">90"}))
5,=COUNTIF(A224:A228,"*小妖*")
6,不会
7,=SUM(A250:D256,-LARGE(A250:D256,{1;2}),-SMALL(A250:D256,{1;2;3}))/(COUNT(A250:D256)-5)
8,=SUMIFS(B267:E267,B263:E263,C263)
9,=SUM(SUMIFS($B$277:$B$284,$A$277:$A$284,{1;2;3}+(ROW(A1)-1)*3)) 三键

ldxhzy · 发表于 2013-6-19 20:21

本帖最后由 ldxhzy 于 2013-6-19 21:06 编辑

【函中201301班】- B22-ldxhzy
1、=SUMPRODUCT((B2:B13="宣传部")+(B2:B13="人事部"),(C2:C13<50)*D2:D13)
2、=SUMPRODUCT(--(A20:A26<>"B"),B20:B26)
3、
4、=COUNTIF(A53:A61,">90")+COUNTIF(A53:A61,"<60")
5、=(SUMPRODUCT(LEN(A65:A69))-SUMPRODUCT(LEN(SUBSTITUTE(A65:A69,"小妖",""))))/2
6、
7、数组公式 =TRIMMEAN((A91:D97,INDIRECT(ADDRESS(MAX((A91:D97=LARGE(A91:D97,1))*ROW(A91:D97)),MAX((A91:D97=LARGE(A91:D97,1))*COLUMN(A91:D97))))),6/(COUNT(A91:D97)+1))
8、
9、C127:C130 数组公式 =SUMIFS(B118:B125,A118:A125,">" & {0;3;6;9},A118:A125,"<" & {4;7;10;13})

风花雪月8888 · 发表于 2013-6-19 21:29

1、=SUM(SUMIFS(D161:D172,B161:B172,{"宣传部","人事部"},C161:C172,{"<50"}))
2、=SUM(SUMIFS(B179:B185,A179:A185,{"A","C"}))
3、
4、=SUM(COUNTIF(A212:A220,{"<60",">90"}))

【函中201301班】- B07-风花雪月8888

zx_wl · 发表于 2013-6-19 22:00

本帖最后由 zx_wl 于 2013-6-20 22:30 编辑

【函中201301班】- B05-zx_wl
1、

=SUMIFS(D161:D172,B161:B172,B161,C161:C172,"<50")+SUMIFS(D161:D172,B161:B172,B164,C161:C172,"<50")

复制代码

2、

=SUMIF(A179:A185,A179,B179)+SUMIF(A179:A185,A182,B179)

复制代码

3、

{=SUM(B189:B204)/SUM(LEN(SUBSTITUTE(A189:A204,",",))/2)}

复制代码

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

复制代码

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

复制代码

4、

=COUNTIF(A212:A220,"<60")+COUNTIF(A212:A220,">90")

复制代码

5、

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

复制代码

6、

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

复制代码

7、

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

复制代码

8、

=SUMIF(B263:E263,"*二*",OFFSET(A263,MATCH(A267,A264:A270,),1,,4))

复制代码

9、

{=SUM((ROUNDUP(A277:A284/3,0)=row(a1))*B277:B284)}

复制代码

		自动登录	找回密码
密码			注册

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

相关帖子

评分

点评

评分

评分

评分

点评

评分

点评

评分

评分

评分

评分