【函高201501】第三讲C组作业上交贴

JLxiangwei

交作业了

Roy.Mastang

第一题

1. =MMULT(1^COLUMN(A:D),B8:D11)

复制代码

第二题

1. =MMULT(N(B18:B24<=TRANSPOSE(B18:B24)),1^ROW(18:24))

复制代码

第三题

1. =MMULT(N(ROW(1:3)>=COLUMN(A:C)),MMULT(--TEXT(B30:D32,";;0;\1"),1^ROW(1:3)))

复制代码

附加题：多单元格数组公式

1. =MOD(SMALL(--RIGHT(SMALL(--(COUNTIF(OFFSET(A39,,,ROW(1:15)),"*")&C39:C53&ROW(39:53)),ROW(1:15))+(6-RIGHT(B39:B53))%,5),ROW(1:15)),1)/1%

复制代码

尛ぃ謉

C11-尛ぃ謉
1.1

1. =MMULT(COLUMN(A:D)^0,B8:D11)

复制代码

1.2

1. =TRANSPOSE(MMULT(TRANSPOSE(B8:D11),ROW(1:4)^0))

复制代码

2

1. =MMULT(N(B18:B24<TRANSPOSE(B18:B24)),B18:B24^0)+1

复制代码

3

1. =MMULT(-(ROW(2:4)>COLUMN(A:C)),MMULT(-(B30:D32<>""),ROW(1:3)^0))

复制代码

附加

1. =MMULT((SUBTOTAL(3,OFFSET(A39,,,ROW(1:15)))=SUBTOTAL(3,OFFSET(A39,,,COLUMN(A:O))))*(C39:C53<TRANSPOSE(C39:C53)),ROW(1:15)^0)+1

复制代码

附加取巧下拉

1. =RANK(C39,OFFSET(C39,-MOD(ROW(A5),5),,5))

复制代码

``

满坛皆为吾师

题一

1. =MMULT({1,1,1,1},B8:D11)

复制代码

题二

1. =MMULT(N(B18:B24<=TRANSPOSE(B18:B24)),ROW(1:7)^0)

复制代码

题三

1. =MMULT(N({1;2;3}>={1,2,3}),MMULT(N(B30:D32>0),{1;1;1}))

复制代码

题四

1. =MMULT(N(INT(ROW(5:19)/5)&C39:C53<=TRANSPOSE(INT(ROW(5:19)/5)&C39:C53)),ROW(1:15)^0)-(2-INT((ROW(1:15)-1)/5))*5

复制代码

1. =RANK(C39,OFFSET(C$38,INT(ROW(A5)/5)*5-4,,5))

复制代码

hsl215

第1题

1. =MMULT({1,1,1,1},B8:D11)
   
2. =MMULT(TRANSPOSE(ROW(A8:A11)^0),B8:D11)
   
3. =SUBTOTAL(9,OFFSET(A7,1,{1,2,3},4))
   
4. =SUMIF(OFFSET(A7,1,{1,2,3},4),"<>")

复制代码

第2题

1. =MMULT(N(B18:B24<=TRANSPOSE(B18:B24)),ROW(B18:B24)^0)

复制代码

第3题

1. =MMULT(N(ROW(A30:A32)>=TRANSPOSE(ROW(A30:A32))),MMULT(N(B30:D32="完成"),{1;1;1}))

复制代码

附加题

1. =MMULT(N(INT((ROW(C39:C53)-34)/5)*100+C39:C53<=TRANSPOSE(INT((ROW(C39:C53)-34)/5)*100+C39:C53)),ROW(C39:C53)^0)-5*(3-INT((ROW(C39:C53)-34)/5))
   
2. =MMULT(N(INT((ROW(C39:C53)-34)/5)/1%+C39:C53<=TRANSPOSE(INT((ROW(C39:C53)-34)/5)/1%+C39:C53)),ROW(C39:C53)^0)-CEILING(49-ROW(C39:C53),5)

复制代码

附加题通用公式

1. =MMULT(N(LOOKUP(ROW(B39:B53),IF(A39:A53<>"",ROW(B39:B53)))/1%%+C39:C53<=TRANSPOSE(LOOKUP(ROW(B39:B53),IF(A39:A53<>"",ROW(B39:B53)))/1%%+C39:C53)),ROW(B39:B53)^0)-MMULT(N(LOOKUP(ROW(B39:B53),IF(A39:A53<>"",ROW(B39:B53)))<TRANSPOSE(LOOKUP(ROW(B39:B53),IF(A39:A53<>"",ROW(B39:B53))))),ROW(B39:B53)^0)

复制代码

ldxhzy

C13:ldxhzy

1、目标区域数组公式：=MMULT({1,1,1,1},B8:D11)
      B12数组公式：=MMULT(TRANSPOSE(B8:B11),{1;1;1;1})   ，右拉

2、=MMULT(N(TRANSPOSE(B18:B24)>B18:B24),{1;1;1;1;1;1;1})+1

3、=MMULT(N(TRANSPOSE(ROW(B30:D32))<=ROW(B30:D32)),MMULT(N(B30:D32="完成"),{1;1;1}))

附加题：
    事业1部排名公式：=MMULT(N(TRANSPOSE(C39:C43)>C39:C43),{1;1;1;1;1})+1
    将公式复制到2部3部。

天空的雨

第一题答：

1. =MMULT(TRANSPOSE(ROW(B8:B11)-ROW(B7:B10)),B8:D11)

复制代码

1. =SUM(INDEX(B8:D11,,COLUMN(A1:C1)))

复制代码

第二题答：

1. =MMULT(N(TRANSPOSE(B18:B24)>=B18:B24),ROW(C18:C24)-ROW(C17:C23))

复制代码

1. =MMULT(N(TRANSPOSE(IF(MATCH(B18:B24,B18:B24,)=(ROW(A18:A24)-17),B18:B24,0))>=B18:B24),ROW(C18:C24)-ROW(C17:C23))

复制代码

第三题答：

1. =MMULT(N(ROW(B30:B32)>=TRANSPOSE(ROW(B30:B32))),MMULT(G30:I32,ROW(B30:B32)-ROW(B29:B31)))

复制代码

出错：INDEX，为什么？
       MMULT(N(ROW(B30:B32)>=TRANSPOSE(ROW(B30:B32))),SUM(INDEX(N(B30:D32="完成"),ROW(A1:A3),)))

第四题答：

1. =MMULT(N(TRANSPOSE(CEILING(ROW(B39:B53)-38,5)*10000+C39:C53)>=CEILING(ROW(B39:B53)-38,5)*10000+C39:C53),ROW(C39:C53)-ROW(C38:C52))-(COUNT(D39:D53)*(ROW(D39:D53)>0)-CEILING(ROW(B39:B53)-38,5))

复制代码

好又多绍兴店

C05:好又多绍兴店
1.

1. =MMULT({1,1,1,1},B8:D11)
   
2. =MMULT(TRANSPOSE(ROW(B8:B11)^0),B8:D11)

复制代码

2。

1. =MMULT(N(B18:B24<=TRANSPOSE(B18:B24)),ROW(B18:B24)^0)

复制代码

3.

1. =MMULT((ROW(1:3)>=COLUMN(A:C))*1,MMULT((B30:D32="完成")*1,ROW(B30:B32)^0))

复制代码

木偶fenzi

第一题

1. =MMULT({1,1,1,1},B8:D11)

复制代码

1. =TRANSPOSE(MMULT(TRANSPOSE(B8:D11),{1;1;1;1}))

复制代码

第二题

1. =MMULT(N(B19:B25<=TRANSPOSE(B19:B25)),ROW(1:7)^0)

复制代码

1. =TRANSPOSE(MMULT(COLUMN(A:G)^0,N(TRANSPOSE(B19:B25)<=B19:B25)))

复制代码

无需数组三键结束

1. =TRANSPOSE(MMULT(COLUMN(A:G)^0,N(TRANSPOSE(B$19:B$25)<=B$19:B$25)))

复制代码

第三题

1. =COUNTA(B$31:D31)

复制代码

1. =SUM(INDEX(MMULT(N(ROW(B31:D33)>=TRANSPOSE(ROW(B31:D33))),1-(B31:D33="")),ROW(1:3),))

复制代码

1. =MMULT(N(ROW(B31:D33)>=TRANSPOSE(ROW(B31:D33))),SUBTOTAL(3,OFFSET(A30,ROW(1:3),,,3)))

复制代码

第四题

1. =MMULT(N(RIGHT(PHONETIC(OFFSET(A40,,,ROW(1:15))),4)=TRANSPOSE(RIGHT(PHONETIC(OFFSET(A40,,,ROW(1:15))),4)))*(C40:C54<=TRANSPOSE(C40:C54)),ROW(1:15)^0)

复制代码

珺珺妈

向学委辛苦啦，附加题不会做，其他的也不知道对不对，交作业不送飞机票。