|
本帖最后由 酒醒梦已残 于 2015-7-3 17:08 编辑
-
D11:酒醒梦已残.rar
(73.91 KB, 下载次数: 10)
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)))
复制代码 每次作业都这么多题吗
|
评分
-
查看全部评分
|