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

明亮610

A07：明亮610
第一题

1. 1.=SUM(B:F 6:12)
   
2. 2.=SUM(--(B15:B19))
   
3. 3.=SUM(B23:B27=" ")
   
4. 4.=SUM((C32:C37="男")*1)
   
5. 4.=SUM((C32:C37="女")*1)
   
6. 5.=SUM((B41:B46="一组")*(D41:D46="男"))
   
7. 5.=SUM((B41:B46="二组")*(D41:D46="女"))
   
8. 6.=SUM((B50:B55="一组")*(C50:C55>3))
   
9. 6.=SUM((B50:B55="二组")*(C50:C55>3))
   
10. 7.=SUM((B59:B64="一组")*(D59:D64>30))
    
11. 7.=SUM((B59:B64="二组")*(D59:D64>30))
    
12. 8.=SUM(B67,C68,D67,E67 )
    
13. 9.=SUM(B71:B73,D71:D74)
    
14. 10.=SUM(B77:F83)-SUM(D80:D81)
    

复制代码

第二题

1. 1.=SUMIFS(F88:F103,E88:E103,">80",F88:F103,">=3000",F88:F103,"<=5000")
   
2. 2.=SUMIF(D107:D112,">80")
   
3. 3.=SUMIFS(D116:D121,C116:C121,"男",D116:D121,">80")
   
4. 4.=SUMIFS(D125:D133,B125:B133,"手机",B125:B133,"彩电")
   
5. 5.=SUMIFS(D137:D145,D137:D145,">5",D137:D145,"<7")
   
6. 6.不会做
   
7. 7.不会做
   
8. 8.不会做
   
9. 9.不会做
   
10. 10.不会做

复制代码

第三题

1. 1.=SUMPRODUCT((E227:E242>80)*(F227:F242>=3000)*(F227:F242<=5000),F227:F242)
   
2. 2.=SUMPRODUCT((C248:C259="男")*1,D246:D257)
   
3. 3.不会做
   
4. 4.不会做
   
5. 5.不会做
   
6. 6.不会做
   
7. 7.不会做
   
8. 8.不会做
   
9. 9.不会做
   
10. 10.不会做

复制代码

附加题

1. 1.不会做
   
2. 2.不会做

复制代码

July-007

A02：July-007

shengmingmima

A12：shengmingmima
作业

风中浪子jack

你好学委，作业在附件中，有几道题不会做。

wxy985

A20：wxy985
第一题：
1、=SUM(B6:F12)
2、={sum(--B15:B19)}
3、=COUNTA(B23:B27)
4、=COUNTIF(C32:C37,"男")，=COUNTIF(C32:C37,"女")
5、=COUNTIFS(D41:D46,"男",B41:B46,"一组")，=COUNTIFS(D41:D46,"女",B41:B46,"二组")
6、=SUMIFS(D50:D55,C50:C55,">3",B50:B55,"一组")，=SUMIFS(D50:D55,C50:C55,">3",B50:B55,"二组")
7、=SUMIFS(D59:D64,D59:D64,">30",B59:B64,"一组")，==SUMIFS(D59:D64,D59:D64,">30",B59:B64,"二组")
8、=SUM(B67:E68)
9、=SUM(B71:D74)
10、=SUM(B77:D81)+SUM(D80:F83)-SUM(B77:F83)

第二题：
1、=SUMIFS(F88:F103,E88:E103,">80",F88:F103,">3000",F88:F103,"<5000")
2、=SUMIF(D107:D112,">80")
3、=SUMIFS(D116:D121,D116:D121,">80",C116:C121,"男")
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、=SUMIF(B175:B184,"A*A",C175:C184)
9、=SUMIFS(F188:F203,E188:E203,">80",F188:F203,">3000",F188:F203,"<5000")
10、=SUMIFS(F208:F223,D208:D223,"*1*",E208:E223,">80",F208:F223,">3000",F208:F223,"<5000")

第三题
1、=SUMPRODUCT((E227:E242>80)*(F227:F242>3000)*(F227:F242<5000),F227:F242)
2、3、4  不会
5、=SUMPRODUCT((B296:D304)*MOD(ROW(B296:D304),2))
6、=SUMPRODUCT((B307:D315)*MOD(COLUMN(B307:D315),2))
7、=SUMPRODUCT((B318:D326)*NOT(MOD(B318:D326,4))*MOD(COLUMN(B318:D326),2))
8、=SUMPRODUCT((B329:D337)*NOT(MOD(B329:D337,4))*NOT(MOD(ROW(B329:D337),2)))
9、=SUMPRODUCT((B340:D348)*NOT(MOD(B340:D348,4)))
10、=SUMPRODUCT((B351:D359)*NOT(MOD(B351:D359,10)))

附加题：
1、不会
2、=SUMPRODUCT(1/COUNTIF(B374:B382,B374:B382))

王晓朱

A14王晓朱【函初1班】作业二.rar (42.53 KB, 下载次数: 4)

2015-7-6 18:01 上传

点击文件名下载附件

雪在飞

第一题：=SUM(D9:D10)
第二题：=SUM(B15:B19*1)
第三题：=COUNTA(B23:B27)
第四题：
=COUNTIFS($B$41:$B$46,B43,$D$41:$D$46,D42)
=COUNTIFS($B$41:$B$46,B42,$D$41:$D$46,D41)
第五题：