2015年【函初1班】第二讲作业D组提交贴（已开）

神隐汀渚 · 发表于 2015-6-30 11:28

本帖最后由神隐汀渚于 2015-7-8 13:46 编辑

注意：
1、本次作业按代码方式提交公式即可，注明学号以及ID；
2、不接受附件上传，回帖方式不正确不评分或者扣分；
3、回复不要重复占楼，修改请在原楼层，不要回复学委点评，本帖已设置回复仅作者可见；
4、非本组学员勿跟帖，违者扣分删帖处理；
5、作业截止时间：下周一（7月6日）18:00
格式机票：http://www.excelpx.com/thread-365873-1-1.html

酒醒梦已残 · 发表于 2015-7-2 14:37

本帖最后由酒醒梦已残于 2015-7-3 17:08 编辑

D11：酒醒梦已残.rar (73.91 KB, 下载次数: 10)

2015-7-3 17:08 上传
点击文件名下载附件
D11:酒醒梦已残
第一题
1.=SUM(B6:D10 D9:F12)</p>

复制代码

2.数组公式：

=SUM(--B15:B19)
=SUM(VALUE(B15:B19))
=SUM(1*B15:B19)，除以1，加0，减0

复制代码

3.

=SUM(--ISTEXT(B23:B27))，三键结束
=COUNTA(B23:B27)-COUNT(B23:B27)

复制代码

4.

=SUM((C$32:C$37=C32)*(ROW($1:$1)))，数组公式
=SUM(--(C32:C37=LEFT(D36,1))),数组公式
=SUMPRODUCT((C$32:C$37=C32)*(ROW($1:$1)))
=COUNTIF(C32:C37,LEFT(D36,1))

复制代码

5.数组公式

=SUM((B$41:B$46=MID(E45,1,2))*(D$41:D$46=MID(E45,3,1)))
=SUM((B$41:B$46=B41)*(D$41:D$46=D41))
=SUM(--(B41:B46&D41:D46=LEFT(E45,3)))

复制代码

6.

=SUM((B$50:B$55=LEFT(E54,2))*(C$50:C$55>3))，数组公式
=SUMPRODUCT((B$50:B$55=LEFT(E54,2))*(C$50:C$55>3))

复制代码

7.

=SUM((B$59:B$64=LEFT(E63,2))*(C$59:C$64>3)*D$59:D$64)
=SUMPRODUCT((B$59:B$64=LEFT(E63,2))*(C$59:C$64>3)*D$59:D$64)

复制代码

8.9.看不懂
10.

=SUM(B77:F83)-SUM(B77:D81 D80:F83)

复制代码

第二题
1.

=SUMIFS(F88:F103,E88:E103,">80",F88:F103,">=3000")-SUMIFS(F88:F103,E88:E103,">80",F88:F103,">=5000")
=SUMPRODUCT((E88:E103>80)*(F88:F103>=3000)*(F88:F103))-SUMPRODUCT((E88:E103>80)*(F88:F103>=5000)*(F88:F103))
=SUM((E88:E103>80)*(F88:F103>=3000)*(F88:F103))-SUM((E88:E103>80)*(F88:F103>=5000)*(F88:F103))，数组公式

复制代码

2.

=SUMIF(D107:D112,">80",D107:D112)
=SUM((D107:D112>80)*(D107:D112))，数组公式
=SUM(IF(D107:D112>80,D107:D112,""))，数组公式

复制代码

3.

=SUMIFS(D116:D121,C116:C121,C116,D116:D121,">80")
=SUMPRODUCT((C116:C121=C116)*(D116:D121>80)*(D116:D121))
=SUM((C116:C121=C116)*(D116:D121>80)*(D116:D121))，三键

复制代码

4.

=SUMIF(B125:B133,B125,D125:D133)+SUMIF(B125:B133,B132,D125:D133)
=SUM((B125:B133={"手机","彩电"})*(D125:D133))，三键

复制代码

5.

=SUMIFS(D137:D145,D137:D145,">=5",D137:D145,"<7")
=SUM((D137:D145={5,6})*(D137:D145))，数组取巧

复制代码

6.

=SUMIF(B149:B158,"A*",C149:C158)
=SUM((LEFT(B149:B158,1)="A")*C149:C158)，三键

复制代码

7.

=SUMIFS(C162:C171,B162:B171,"*A")
=SUM((RIGHT(B162:B171,1)="A")*C162:C171)，三键

复制代码

8.

=SUMIFS(C175:C184,B175:B184,"A*A")
=SUMIF(B175:B184,"A*A",C175:C184)
=SUM((RIGHT(B175:B184,1)&LEFT(B175:B184,1)="AA")*C175:C184)，三键

复制代码

9.

=SUMIFS(F188:F203,E188:E203,80,F188:F203,">3000")-SUMIFS(F188:F203,E188:E203,80,F188:F203,">5000")

复制代码

10.

=SUMIFS(F208:F223,D208:D223,"*1",E208:E223,">80",F208:F223,">3000")-SUMIFS(F208:F223,D208:D223,"*1",E208:E223,">80",F208:F223,">5000")

复制代码

第三题
1.

=SUMPRODUCT((E227:E242>80)*(F227:F242>3000),(F227:F242))-SUMPRODUCT((E227:E242>80)*(F227:F242>5000),(F227:F242))

复制代码

2.

=SUMPRODUCT((C248:C259=C248)*(D246:D257))
=SUM((C248:C259="男")*(D246:D257))，三键

复制代码

3。

=SUMPRODUCT((C265:C276=C265)*(D263:D274>450)*(D263:D274))
=SUM((C265:C276=C265)*(D263:D274>450)*(D263:D274)) ，三键

复制代码

4.

=SUMPRODUCT((C265:C276=C265)*(D263:D274>450))
=SUM((C265:C276=C265)*(D263:D274>450))，三键

复制代码

5.

=SUMPRODUCT((MOD(ROW(B296:D304),2)=1)*(B296:D304))
=SUM((MOD(ROW(B296:D304),2)=1)*(B296:D304))，三键

复制代码

6.

=SUMPRODUCT((MOD(COLUMN(B307:D315),2)=1)*(B307:D315))
=SUM((MOD(COLUMN(B307:D315),2)=1)*(B307:D315))，三键

复制代码

7.

=SUMPRODUCT((MOD(COLUMN(B318:D326),2)=1)*(MOD(COLUMN(B318:D326),4)=0)*(B318:D326))
=SUM((MOD(COLUMN(B318:D326),2)=1)*(MOD(COLUMN(B318:D326),4)=0)*(B318:D326))，三键

复制代码

8.

=SUMPRODUCT((MOD(ROW(B329:D337),4)=0)*(MOD(COLUMN(B329:D337),4)=0)*(B329:D337))
=SUM((MOD(ROW(B329:D337),4)=0)*(MOD(COLUMN(B329:D337),4)=0)*(B329:D337))，三键

复制代码

9.

=SUMPRODUCT((MOD(B340:D348,4)=0)*(B340:D348))
=SUM((MOD(B340:D348,4)=0)*(B340:D348))，三键

复制代码

10.

=SUMPRODUCT((--RIGHT(B351:D359)=0)*(B351:D359))
=SUM((--RIGHT(B351:D359)=0)*(B351:D359))，三键

复制代码

附加题
1.

=SUMPRODUCT(LARGE((0+(B363:B371>50))*(B363:B371),ROW(1:3)))
=SUM(LARGE((0+(B363:B371>50))*(B363:B371),ROW(1:3))) ，三键
<p> </p>

复制代码

2.数组公式

=SUM(1/COUNTIF(B374:B382,B374:B382))
=SUM(IF(MATCH(B374:B382,B374:B382,0)=ROW(1:9),1,))
=COUNT(0/(MATCH(B374:B382,B374:B382,0)=ROW(1:9)))
=SUM(1*(MATCH(B374:B382,B374:B382,0)=ROW(1:9)))，除以1，加0，减0
=SUM(N(MATCH(B374:B382,B374:B382,0)=ROW(1:9)))
=SUM(--(MATCH(B374:B382,B374:B382,0)=ROW(1:9)))

复制代码

每次作业都这么多题吗

心正意诚身修 · 发表于 2015-7-2 21:01

D组长：心正意诚身修
附加题一

=SUM(LARGE(B363:B371,ROW(A1:A3)))

复制代码

附加题二

=SUM(1/COUNTIF(B374:B382,B374:B382))

复制代码

jeans0408 · 发表于 2015-7-3 11:47

本帖最后由 jeans0408 于 2015-7-4 17:40 编辑

D13:jeans0408

第一题：

1.

01.=SUM(B6:D10 D9:F12)

复制代码

2.

01.=SUM(--(B15:B19))

复制代码

3.

01.不会

复制代码

4.

方法一：
01.=SUM((C32:C37="男")*1)
02.=SUM((C32:C37="女")*1)；
方法二：
01.=COUNTIF(C$$$$$$$$$$$$$$$$32:C$$$$$$$$$$$$$$$$37,"男")
02.=COUNTIF(C$$$$$$$$$$$$$$$$32:C$$$$$$$$$$$$$$$$37,"女")

复制代码

5.

方法一：
01.=SUM((B41:B46="一组")*(D41:D46="男"))
02.=SUM((B41:B46="二组")*(D41:D46="女"))
方法二：
01.=COUNTIFS(B41:B46,"一组",D41:D46,"男")，
02.=COUNTIFS(D41:D46,"女",B41:B46,"二组")

复制代码

6.

方法一：
01.=SUM(($$$$B$$$$50:$$$$B$$$$55="一组")*($$$$C$$$$50:$$$$C$$$$55>3)*$$$$D$$$$50:$$$$D$$$$55)
02=SUM(($$$$B$$$$50:$$$$B$$$$55="二组")*($$$$C$$$$50:$$$$C$$$$55>3)*$$$$D$$$$50:$$$$D$$$$55)
方法二：
01.=SUMIFS(D50:D55,B50:B55,"一组",C50:C55,">3")
02.=SUMIFS(D50:D55,B50:B55,"二组",C50:C55,">3")

复制代码

7.

方法一：
01=SUM(($$$$B$$$$59:$$$$B$$$$64="一组")*($$$$C$$$$59:$$$$C$$$$64>3)*($$$$D$$$$59:$$$$D$$$$64>30)*$$$$D$$$$59:$$$$D$$$$64)
02=SUM(($$$$B$$$$59:$$$$B$$$$64="二组")*($$$$C$$$$59:$$$$C$$$$64>3)*($$$$D$$$$59:$$$$D$$$$64>30)*$$$$D$$$$59:$$$$D$$$$64)
方法二：
01.=SUMIFS(D59:D64,B59:B64,"一组",C59:C64,">3",D59:D64,">30")
02.=SUMIFS(D59:D64,B59:B64,"二组",C59:C64,">3",D59:D64,">30")

复制代码

8.

01.=SUM(B67,C68,D67,E67)

复制代码

9.

=SUM(B71:B73,D71:D74)

复制代码

10.

01.=SUM(B77:F83)-SUM(D80:D81)

复制代码

第二题：
1.

01.=SUMIFS(F88:F103,E88:E103,">80",F88:F103,">=3000",F88:F103,"<=5000")

复制代码

2.

01.=SUMIF(D107:D112,">80",D107)

复制代码

3.

01.=SUMIFS(D107:D112,C107:C112,"男",D107:D112,">80")

复制代码

4.

01.=SUMIF(B125:B133,"手机",D125:D133)+SUMIF(B125:B133,"彩电",D125:D133)

复制代码

5.

01.=SUM(SUMIF(D137:D145,">=5",D137:D145)-SUMIF(D137:D145,">=7",D137:D145))*{1,-1}

复制代码

6.

01.=SUMIF(B149:B158,"A*",C149:C158)

复制代码

7.

01.=SUMIF(B162:B171,"*A",C162:C171)

复制代码

8.

01.=SUMIF(B175:B184,"A*A",C175:C184)

复制代码

9.

01.不会

复制代码

10.

01.不会

复制代码

第三题：
1.

01.不会

复制代码

2.

01.=SUMPRODUCT((C248:C259=C248)*D246:D257)

复制代码

3.

01.=SUMPRODUCT((C265:C276=C265)*(D263:D274>450),D263:D274)

复制代码

4.

01.=SUMPRODUCT((C282:C293=C283)*(D280:D291>450))

复制代码

5.

01.=SUMPRODUCT((B296:D304)*MOD(ROW(B296:D304),2))

复制代码

6.

01.=SUMPRODUCT((B307:D315)*MOD(COLUMN(B307:D315),2))

复制代码

7.

01.=SUMPRODUCT((B318:D326)*MOD(COLUMN(B318:D326),4))

复制代码

8.

01.=SUMPRODUCT((B329:D337)*MOD(ROW(B329:D337),4))

复制代码

9.

01.=SUMPRODUCT((B340:D348)*MOD(B340:D348,4))

复制代码

10.

01.不会

复制代码

附加题：

01.不会
02.不会

复制代码

回答完毕

o豆苗 · 发表于 2015-7-3 12:25

本帖最后由 o豆苗于 2015-7-6 09:02 编辑

D09:o豆苗
第一题
1.

=SUM(6:12 B:F)

复制代码

2.

=SUM(--B15:B19)

复制代码

3.

=SUM(--ISTEXT(B23:B27))

复制代码

4.

=SUM((C32:C37="男")*1)
=SUM((C32:C37="女")+0)

复制代码

5.

=SUM((B41:B46="一组")*(D41:D46="男")*1)
=SUM((B41:B46="二组")*(D41:D46="女")+0)

复制代码

6.

=SUM((B50:B55="一组")*(C50:C55>3)+0)
=SUM((B50:B55="二组")*(C50:C55>3)*1)

复制代码

7.

=SUM((D59:D64>30)*(B59:B64="一组")*(C59:C64>3)*D59:D64)
=SUM((D59:D64>30)*(B59:B64="二组")*(C59:C64>3)*D59:D64)

复制代码

8.

=SUM(B67:E68)

复制代码

9.

=SUM(B71:D74)

复制代码

10.

=SUM(B77:F83)-SUM(D80:D81)

复制代码

第二题
1.

=SUMIFS(F88:F103,E88:E103,">80",F88:F103,">=3000",F88:F103,"<5000")

复制代码

2.

=SUMIF(D107:D112,">80")

复制代码

3.

=SUMIFS(D116:D121,C116:C121,"男",D116:D121,">80")

复制代码

4.解法一

=SUM(SUMIF(B125:B133,"手机",D125:D133),SUMIF(B125:B133,"彩电",D125:D133))

复制代码

解法二

=SUMPRODUCT(((B125:B133="手机")+(B125:B133="彩电"))*D125:D133)

复制代码

解法三

=SUM((B125:B133="手机")*D125:D133,(B125:B133="彩电")*D125:D133)

复制代码

5.解法一

=SUM(SUMIF(D137:D145,{">=5",">=7"})*{1,-1})

复制代码

解法二

=SUM((D137:D145>=5)*(D137:D145<7)*D137:D145)

复制代码

6.解法一

=SUMIF(B149:B158,"A*",C149:C158)

复制代码

解法二

=SUM((LEFT(B149:B158,1)="A")*C149:C158)

复制代码

7.解法一

=SUMIF(B162:B171,"*A",C162:C171)

复制代码

解法二

=SUM((RIGHT(B162:B171,1)="A")*C162:C171)

复制代码

8.解法一

=SUMIF(B175:B184,"A*A",C175:C184)

复制代码

解法二

=SUM((LEFT(B175:B184,1)="A")*(RIGHT(B175:B184,1)="A")*C175:C184)

复制代码

9.解法一

=SUMIFS(F188:F203,E188:E203,"80",F188:F203,">3000",F188:F203,"<5000")

复制代码

解法二

=SUM((E188:E203=80)*(F188:F203>3000)*(F188:F203<5000)*F188:F203)

复制代码

10.解法一

=SUMIFS(F208:F223,D208:D223,"?1",E208:E223,">80",F208:F223,">3000",F208:F223,"<5000")

复制代码

解法二

=SUM(ISNUMBER((FIND("1",D208:D223))*(E208:E223>80)*(F208:F223>3000)*(F208:F223<5000)*F208:F223))

复制代码

第三题
1.

=SUMPRODUCT((E227:E242>80)*(F227:F242>3000)*(F227:F242<5000))

复制代码

2.

=SUMPRODUCT((C248:C259="男")*D246:D257)

复制代码

3.

=SUMPRODUCT((C282:C293="男")*(D280:D291>450)*D280:D291)

复制代码

4.

=SUMPRODUCT((C282:C293="男")*(D280:D291>450))

复制代码

5.

=SUMPRODUCT(MOD(ROW(296:304),2)*B296:D304)

复制代码

6.

=SUMPRODUCT(MOD(COLUMN(B:D),2)*B307:D315)

复制代码

7.

=SUMPRODUCT(MOD(COLUMN(B:D),2)*(MOD(B318:D326,4)=0)*B318:D326)

复制代码

8.

=SUMPRODUCT((MOD(ROW(329:337),2)=0)*(MOD(COLUMN(B:D),4)=0)*B329:D337)

复制代码

9.

=SUMPRODUCT((MOD(B340:D348,4)=0)*B340:D348)

复制代码

10.

=SUMPRODUCT((RIGHT(B351:D359,1)="0")*B351:D359)

复制代码

附加题
1.

=SUM(LARGE(IF(B363:B371>50,B363:B371),ROW($1:$3)))

复制代码

2.

=SUM(1/COUNTIF(B374:B382,B374:B382))

复制代码

回答完毕。学委辛苦了！

gongyicheng · 发表于 2015-7-3 13:50

本帖最后由 gongyicheng 于 2015-7-6 13:31 编辑

D02：gongyicheng
第一题：
1、

=SUM(B6:F12)

复制代码

2、

{=SUM((B15:B19)*1)}

复制代码

3、

{=SUM((B23:B27)/B23:B27*1)}

复制代码

4、

{=SUM((C32:C37="男")*1)}
{=SUM((C32:C37="女")*1)}

复制代码

5、

{=SUM((B41:B46=B41)*(D41:D46=D41))*1}
{=SUM((B41:B46=B43)*(D41:D46=D42))*1}

复制代码

6、

{=SUM((B50:B55=B50)*(C50:C55>3))*1}
{=SUM((B50:B55=B52)*(C50:C55>3))*1}

复制代码

7、

{=SUM((B59:B64=B59)*(D59:D64>30))*1}
{=SUM((B59:B64=B61)*(D59:D64>30))*1}

复制代码

8、

=SUM(B67:E68)

复制代码

9、

=SUM(B71:D74)

复制代码

10、

=SUM(B:F 77:83)-SUM(D80:D81)

复制代码

第二题：
1、

=SUMIFS(F88:F103,E88:E103,">80",F88:F103,">=3000",F88:F103,"<=5000")

复制代码

2、

=SUMIF(D107:D112,">80")

复制代码

3、

=SUMIFS(D116:D121,C116:C121,"男",D116:D121,">80")

复制代码

4、

=SUMIF(B125:B133,"手机",D125:D133)+SUMIF(B125:B133,"彩电",D125:D133)

复制代码

5、

=SUMIFS(D137:D145,D137:D145,">=5",D137:D145,"<7")

复制代码

6、

=SUMIF(B149:B158,"A*",C149:C158)

复制代码

7、

=SUMIF(B162:B171,"*A",C162:C171)

复制代码

8、

=SUMIFS(C175:C184,B175:B184,"A*",B175:B184,"*A")

复制代码

9、

=SUMIFS(F188:F203,E188:E203,"=80",F188:F203,">=3000",F188:F203,"<=5000")

复制代码

10、

=SUMIFS(F208:F223,E208:E223,">80",F208:F223,">=3000",F208:F223,"<=5000",D208:D223,"1")

复制代码

第三题：
1、

=SUMPRODUCT((E227:E242>80)*(F227:F242>=3000)*(F227:F242<5000)*F227:F242)

复制代码

2、

=SUMPRODUCT((C248:C259="男")*D246:D257)

复制代码

3、

=SUMPRODUCT((C265:C276="男")*(D263:D274>450)*D263:D274)

复制代码

4、

=SUMPRODUCT(((C282:C293="男")*(D29:D280>450))*1)

复制代码

5-10

不会做

复制代码

附加题

不会做

复制代码

keyan2003 · 发表于 2015-7-3 15:23

本帖最后由 keyan2003 于 2015-7-6 17:10 编辑

D20：keyan2003

fuexcel · 发表于 2015-7-4 15:48

本帖最后由 fuexcel 于 2015-7-6 11:31 编辑

D08：fuexcel
第一题：SUM练习
1、

=SUM(B6:D10 D9:F12)

复制代码

2、

{=SUM(--B15:B19)}

复制代码

3、

{=SUM(1*(--B23:B27>0))}

复制代码

4、

{=SUM((C32:C37="男")*1)}
{=SUM((C32:C37="女")*1)}

复制代码

5、

{=SUM((B41:B46="一组")*(D41:D46="男"))}
{=SUM((B41:B46="二组")*(D41:D46="女"))}

复制代码

6、

{=SUM(((B50:B55="一组")*(C50:C55>3))*1)}
{=SUM(((B50:B55="二组")*(C50:C55>3))*1)}

复制代码

7、

{=SUM((B59:B64="一组")*(D59:D64>30)*1)}
{=SUM((B59:B64="二组")*(D59:D64>30)*1)}

复制代码

8、

=SUM(B67,C68,D67,E67)

复制代码

9、

=SUM(B71:B73,D71:D74)

复制代码

10、

=SUM(B77:F83)-SUM(D80:D81)

复制代码

第二题：SUMIF和SUMIFS练习
1、

=SUMIFS(F88:F103,E88:E103,">80",F88:F103,">2999",F88:F103,"<5001")

复制代码

2、

=SUMIF(D107:D112,">80")

复制代码

3、

=SUMIFS(D116:D121,C116:C121,"男",D116:D121,">80")

复制代码

4、

=SUMIF(B125:B133,"手机",D125:D133)+SUMIF(B125:B133,"彩电",D125:D133)

复制代码

5、

=SUMIFS(D137:D145,D137:D145,">5",D137:D145,"<7")

复制代码

6、

=SUMIF(B149:B158,"A*",C149)

复制代码

7、

=SUMIF(B162:B171,"*A",C162)

复制代码

8、

=SUMIF(B175:B184,"A*",C175)+SUMIF(B175:B184,"*A",C175)-SUMIF(B175:B184,"A*A",C175)

复制代码

9、

=SUMIFS(F188:F203,E188:E203,">80",F188:F203,">2999",F188:F203,"<5001")

复制代码

10、

=SUMIFS(F208:F223,D208:D223,"*1*",E208:E223,">80",F208:F223,">=3000",F208:F223,"<=5000")

复制代码

第三题：SUMPRODUCT练习
1、

=SUMPRODUCT((E227:E242>80)*(F227:F242>=3000)*(F227:F242<=5000)*F227:F242)

复制代码

2、

=SUMPRODUCT((C248:C259="男")*D246:D257)

复制代码

3、

=SUMPRODUCT((C265:C276="男")*(D263:D274>450)*D263:D274)

复制代码

4、

=SUMPRODUCT((C282:C293="男")*(D280:D291>450))

复制代码

5、

=SUMPRODUCT(MOD(ROW(B296:D304)-1,2)*B296:D304)

复制代码

6、

=SUMPRODUCT(MOD(COLUMN(B307:D315)-1,2)*B307:D315)

复制代码

7、

=SUMPRODUCT((MOD(MOD(COLUMN(B318:D326)-1,2),4)=0)*B318:D326)

复制代码

8、

=SUMPRODUCT((MOD(MOD(ROW(B329:D337),2),4)=0)*B329:D337)

复制代码

9、

=SUMPRODUCT((MOD(B340:D348,4)=0)*B340:D348)

复制代码

10、

=SUMPRODUCT((RIGHT(B351:D359,1)="0")*B351:D359)

复制代码

附加题
1、

{=SUM((B363:B371>=LARGE(B363:B371,3))*(B363:B371))}

复制代码

2、
方法一

{=SUM(1/COUNTIF(B374:B382,B374:B382))}

复制代码

方法二

=SUMPRODUCT(1/COUNTIF(B374:B382,B374:B382))

复制代码

回答完毕。

jipei237582527 · 发表于 2015-7-4 23:27

本帖最后由 jipei237582527 于 2015-7-4 23:48 编辑

D10:jipei237582527
第一题

=SUM(B6:D10,D9:F12)

复制代码

{=SUM(--B15:B19)}

复制代码

{=SUM(ISTEXT(B23:B27)*1)}

复制代码

{=SUM((C32:C37=C32)*1)}
{=SUM((C32:C37=C33)*1)}

复制代码

{=SUM((B41:B46=B41)*(D41:D46=D41))}
{=SUM((B41:B46=B43)*(D41:D46=D42))}

复制代码

{=SUM((C50:C55>3)*(B50:B55=B50))}
{=SUM((C50:C55>3)*(B50:B55=B52))}

复制代码

{=SUM((B59:B64=B59)*(D59:D64>30)*(D59:D64))}
{=SUM((B59:B64=B61)*(D59:D64>30)*(D59:D64))}

复制代码

=SUM(B67:E68)

复制代码

=SUM(B71:B73,D71:D74)

复制代码

=SUM(B:F 77:83)-SUM(B77:D81 D80:F83)

复制代码

第二题

=SUMIFS(F88:F103,E88:E103,">80",F88:F103,">3000",F88:F103,"<5000")

复制代码

=SUMIF(D107:D112,">80")

复制代码

=SUMIFS(D116:D121,C116:C121,C116,D116:D121,">80")

复制代码

=SUMIF(B125:B133,B125,D125:D133)+SUMIF(B125:B133,B132,D125:D133)

复制代码

=SUMIF(D137:D145,">=5")-SUMIF(D137:D145,">=7")

复制代码

=SUMIF(B149:B158,"A*",C149:C158)

复制代码

=SUMIF(B162:B171,"*A",C162:C171)

复制代码

=SUMIF(B175:B184,"A*A",C175:C184)

复制代码

=SUMIFS(F188:F203,E188:E203,">80",F188:F203,">3000",F188:F203,"<5000")

复制代码

=SUMIFS(F208:F223,D208:D223,"*1*",E208:E223,">80",F208:F223,">3000",F208:F223,"<5000")

复制代码

第三题

=SUMPRODUCT((E227:E242>80)*(F227:F242>3000)*(F227:F242<5000)*(F227:F242))

复制代码

=SUMPRODUCT((C248:C259=C248)*(D246:D257))

复制代码

=SUMPRODUCT((C265:C276=C265)*(D263:D274>450)*(D263:D274))

复制代码

=SUMPRODUCT((C282:C293=C283)*(D280:D291>450))

复制代码

=SUMPRODUCT((MOD(ROW(A1:A9),2))*(B296:D304))

复制代码

=SUMPRODUCT((MOD(COLUMN(A1:C1),2))*(B307:D315))

复制代码

=SUMPRODUCT((MOD(COLUMN(A1:C1),2)=1)*((MOD(B318:D326,4))=0)*B318:D326)

复制代码

=SUMPRODUCT((MOD(ROW(A1:A9),2)=0)*((MOD(B329:D337,4))=0)*B329:D337)

复制代码

=SUMPRODUCT((MOD(B340:D348,4)=0)*(B340:D348))

复制代码

=SUMPRODUCT((RIGHT(B351:D359,1)="0")*(B351:D359))

复制代码

附加题

=SUMPRODUCT((B363:B371>50)*(B363:B371>=LARGE(B363:B371,3))*B363:B371)

复制代码

{=SUM(IF(COUNTIF(B374:B382,B374:B382)=1,1,0))}

复制代码

szbaodao · 发表于 2015-7-5 20:17

本帖最后由 szbaodao 于 2015-7-6 14:32 编辑

【函初1班】作业二.rar (73.53 KB, 下载次数: 8)

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

[习题] 2015年【函初1班】第二讲作业D组提交贴（已开）

评分

评分

点评

评分

RE: 2015年【函初1班】第二讲作业D组提交贴

点评

评分

点评

评分

点评

评分

点评

评分

点评

评分

点评

评分

点评

评分