第五课预习答案贴

飞雨飘

因为此星期做题空缺，所以先将课件预习做题，欢迎大家回复参与。

飞雨飘

楼层先抢好。公式都未精减，只做思路。

qingfeng_qf

1、

1. =MMULT(10^(4-COLUMN(A:D)),A6:A9)
   
2. =SUM(A6:A9*10^(4-ROW(1:4)))

复制代码

2、

1. =SUM(MOD(LARGE((A14:A21=A23)*100*COLUMN(A:C)+D14:F21,ROW(1:3)*3-2),100))

复制代码

3、

1. =SUM(N(MMULT($B$28:$J$38,10^(9-ROW($1:$9)))>MMULT(B28:J28,10^(9-ROW($1:$9)))))+1

复制代码

4、
5、等待中。。。

qingfeng_qf

第二题公式有点问题改了改：

1. =SUM(MOD(LARGE(IF(A14:A21=A23,D14:F21+{200,100,0},0),(ROW(1:3)-1)*COUNTIF(A14:A21,A23)+1),100))

复制代码

wjc2090742

先答下，空点再仔细研究。均为数组公式或多单元格数组公式。
1.

1. =SUM(A6:A9*10^{3;2;1;0})

复制代码

2.

1. =SUM(MOD(LARGE(D14:F21*(A23=A14:A21)+{1,2,3}/1%,{1,9,17}),100))

复制代码

3.

1. =MATCH(MMULT(B28:J38,10^-ROW(1:9)),LARGE(MMULT(B28:J38,10^-ROW(1:9)),ROW(1:11)),)

复制代码

4.

1. =MOD(MAX(MMULT(SUBTOTAL(9,OFFSET(A45,,ROW(1:12)*2-{1,0},5)),{1;-1})/1%+ROW(1:12)),100)&"月"

复制代码

5.

1. =MMULT(B61:J63*N(INDIRECT("r"&MATCH(B59:J59,A:A,)&"c"&MATCH(B60:J60,65:65,),)),ROW(1:9)^0)

复制代码

CheryBTL

1. =SUM(A6:A9*10^{3;2;1;0})
   
2. =SUM(LARGE((A14:A21=A23)*D14:F21+{1,2,3}/1%,{1;9;17}))-600
   
3. =SUM(N(MMULT(B$28:J$38/10^COLUMN(A:I),ROW(1:9)^0)>=SUM(B28:J28/10^COLUMN(A:I))))
   
4. =RIGHT(MAX(MMULT(SUBTOTAL(9,OFFSET(B45:B50,,ROW(1:12)*2-{2,1})),{1;-1})/1%+ROW(1:12)))&"月"
   
5. =SUM((A$61:A$63=A72)*N(OFFSET(A$65,MATCH(B$59:J$59,A$66:A$68,),MATCH(B$60:J$60,B$65:D$65,)))*B$61:J$63)

复制代码

zhouxialing

第一题：
=SUM((A6:A9)*10^(4-ROW(1:4)))
第二题：
=SUM(MOD(LARGE(N(IF(A14:A21=A23,D14:F21))+COLUMN(A:C)*10000,25-COLUMN(A:C)*8),10000))
第三题：
=MMULT(N(TRANSPOSE(MMULT(B28:J38,10^(9-ROW(1:9))))>MMULT(B28:J38,10^(9-ROW(1:9)))),ROW(1:11)^0)+1
第四题：
=(RIGHT(LARGE(IF(MOD(COLUMN(C:Y),2)=0,-9^9,MMULT(COLUMN(A:B)^0,IF({1;0},MMULT(COLUMN(A:F)^0,B45:X50),MMULT(-1*COLUMN(A:F)^0,C45:Y50))))*100+COLUMN(A:W),1),2)+1)/2&"月"

tyxh0916

第一题

1. =SUM(A6:A9*10^(4-ROW(1:4)))

复制代码

第二题

1. =SUM(MOD(LARGE({1,2,3}*100+(A14:A21=A23)*D14:F21,{1,9,17}),100))

复制代码

第三题

1. =SUM(N($B$28:$B$38+MMULT($C$28:$J$38,10^-ROW($1:$8))>B28+SUM(C28:J28*10^-COLUMN($A:$H))))+1

复制代码

第四题

1. =MOD(MAX(MMULT({1,-1},SUBTOTAL(9,OFFSET(A45:A50,,COLUMN(A:L)*2-{1;0})))*100+COLUMN(A:L)),100)&"月"

复制代码

第五题

1. =SUM(INDEX(B61:J61,,N(IF(1,MATCH(A$66:A$68&B$65:D$65,B$59:J$59&B$60:J$60,))))*B$66:D$68)

复制代码

qingfeng_qf

5、

1. MMULT(B61:J63*1,TRANSPOSE(N(INDIRECT(TEXT(MMULT(COLUMN(A:C)^0,(A66:A68=B59:J59)*ROW(66:68)*10+(TRANSPOSE(B65:D65)=B60:J60)*ROW(2:4)),"r0c0"),))))

复制代码

qingfeng_qf

1. =MMULT(B61:J63*1,N(INDIRECT(TEXT(MMULT(TRANSPOSE((B59:J59=A66:A68)*ROW(66:68))*10+(TRANSPOSE(B60:J60)=B65:D65)*COLUMN(B:D),ROW(1:3)^0),"r0c0"),)))

复制代码