|
本帖最后由 蓝粆 于 2015-7-8 23:45 编辑
C10 - apiu 作业二:
第一题:
1.2.3.思路很特别- =SUM(--(B23:B27<>--B23:B27))
复制代码 4.二参也省算了- =SUM(--(C$32:C$37=LEFT(D36,1)))
复制代码 5.眼尖,挺省的- =SUM(--(B$41:B$46&D$41:D$46=LEFT(E45,3)))
复制代码 6.中规中矩- =SUM((B$50:B$55=LEFT(E54,2))*(C$50:C$55>3)*D$50:D$55)
复制代码 7.- =SUM((B$59:B$64=LEFT(E63,2))*(D$59:D$64>30))
复制代码 8.9.10.思维很好- =SUM(B77:D81,D80:F83)-2*SUM(B77:D81 D80:F83)
复制代码 第二题:
1.不知道你会不会按下三键- =SUM(SUMIFS(F88:F103,E88:E103,">80",F88:F103,{">=3000",">5000"})*{1,-1})
复制代码 2.3.- =SUMIFS(D116:D121,C116:C121,"男",D116:D121,">80")
复制代码 4.- =SUM(SUMIF(B125:B133,{"手机","彩电"},D125:D133))
复制代码 5.- =SUM(SUMIF(D137:D145,{">=5",">=7"})*{1,-1})
复制代码 6.- =SUMIF(B149:B158,"A*",C149:C158)
复制代码 7.- =SUMIF(B162:B171,"*A",C162)
复制代码 8.- =SUMIF(B175:B184,"A*A",C175)
复制代码 9.- =SUM(SUMIFS(F188:F203,E188:E203,80,F188:F203,{">=3000",">5000"})*{1,-1})
复制代码 10.- =SUM(SUMIFS(F208:F223,D208:D223,"*1*",E208:E223,">80",F208:F223,{">=3000",">5000"})*{1,-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="男")*(D280:D291>450))
复制代码 5.- =SUMPRODUCT(MOD(ROW(B296:B304),2)*B296:D304)
复制代码 6.- =SUMPRODUCT(MOD(COLUMN(B307:D307),2)*B307:D315)
复制代码 7.- =SUMPRODUCT(MOD(COLUMN(B318:D318),2)*(MOD(B318:D326,4)=0)*B318:D326)
复制代码 8.- =SUMPRODUCT((MOD(ROW(B329:B337),2)=0)*(MOD(COLUMN(B329:D329),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(B363:B371,ROW(1:3)))- =SUMPRODUCT((RANK(B363:B371,B363:B371)<=3)*(B363:B371>50)*B363:B371)
复制代码 2.- =SUM(--(COUNTIF(B374:B382,B374:B382)=1))
复制代码 回答完毕。
-----------------------------------------------------------------------------------------------------------------
规规范范的解法,标准答案!
|
评分
-
查看全部评分
|