【函高201501】第三讲C组作业上交贴
本帖最后由 JLxiangwei 于 2015-7-29 19:23 编辑交作业了 第一题=MMULT(1^COLUMN(A:D),B8:D11)第二题=MMULT(N(B18:B24<=TRANSPOSE(B18:B24)),1^ROW(18:24))第三题=MMULT(N(ROW(1:3)>=COLUMN(A:C)),MMULT(--TEXT(B30:D32,";;0;\1"),1^ROW(1:3)))附加题：多单元格数组公式=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=MMULT(COLUMN(A:D)^0,B8:D11)1.2=TRANSPOSE(MMULT(TRANSPOSE(B8:D11),ROW(1:4)^0))2=MMULT(N(B18:B24<TRANSPOSE(B18:B24)),B18:B24^0)+13=MMULT(-(ROW(2:4)>COLUMN(A:C)),MMULT(-(B30:D32<>""),ROW(1:3)^0))附加=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附加取巧下拉=RANK(C39,OFFSET(C39,-MOD(ROW(A5),5),,5))`` 题一=MMULT({1,1,1,1},B8:D11)题二=MMULT(N(B18:B24<=TRANSPOSE(B18:B24)),ROW(1:7)^0)题三=MMULT(N({1;2;3}>={1,2,3}),MMULT(N(B30:D32>0),{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=RANK(C39,OFFSET(C$38,INT(ROW(A5)/5)*5-4,,5)) 本帖最后由 hsl215 于 2015-7-24 23:14 编辑
第1题=MMULT({1,1,1,1},B8:D11)
=MMULT(TRANSPOSE(ROW(A8:A11)^0),B8:D11)
=SUBTOTAL(9,OFFSET(A7,1,{1,2,3},4))
=SUMIF(OFFSET(A7,1,{1,2,3},4),"<>")第2题=MMULT(N(B18:B24<=TRANSPOSE(B18:B24)),ROW(B18:B24)^0)第3题=MMULT(N(ROW(A30:A32)>=TRANSPOSE(ROW(A30:A32))),MMULT(N(B30:D32="完成"),{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))
=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)附加题通用公式=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 于 2015-7-28 11:16 编辑
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部。 本帖最后由 天空的雨 于 2015-7-26 14:10 编辑
第一题答：=MMULT(TRANSPOSE(ROW(B8:B11)-ROW(B7:B10)),B8:D11)=SUM(INDEX(B8:D11,,COLUMN(A1:C1)))第二题答：=MMULT(N(TRANSPOSE(B18:B24)>=B18:B24),ROW(C18:C24)-ROW(C17:C23))=MMULT(N(TRANSPOSE(IF(MATCH(B18:B24,B18:B24,)=(ROW(A18:A24)-17),B18:B24,0))>=B18:B24),ROW(C18:C24)-ROW(C17:C23))第三题答：=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),)))
第四题答：=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.=MMULT({1,1,1,1},B8:D11)
=MMULT(TRANSPOSE(ROW(B8:B11)^0),B8:D11)2。=MMULT(N(B18:B24<=TRANSPOSE(B18:B24)),ROW(B18:B24)^0)3.=MMULT((ROW(1:3)>=COLUMN(A:C))*1,MMULT((B30:D32="完成")*1,ROW(B30:B32)^0)) 本帖最后由 木偶fenzi 于 2015-7-27 16:46 编辑
第一题=MMULT({1,1,1,1},B8:D11)=TRANSPOSE(MMULT(TRANSPOSE(B8:D11),{1;1;1;1}))第二题=MMULT(N(B19:B25<=TRANSPOSE(B19:B25)),ROW(1:7)^0)=TRANSPOSE(MMULT(COLUMN(A:G)^0,N(TRANSPOSE(B19:B25)<=B19:B25)))无需数组三键结束=TRANSPOSE(MMULT(COLUMN(A:G)^0,N(TRANSPOSE(B$19:B$25)<=B$19:B$25)))第三题=COUNTA(B$31:D31)=SUM(INDEX(MMULT(N(ROW(B31:D33)>=TRANSPOSE(ROW(B31:D33))),1-(B31:D33="")),ROW(1:3),))=MMULT(N(ROW(B31:D33)>=TRANSPOSE(ROW(B31:D33))),SUBTOTAL(3,OFFSET(A30,ROW(1:3),,,3)))第四题=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) 向学委辛苦啦，附加题不会做，其他的也不知道对不对，交作业不送飞机票。):P):P):P
页:
[1]
2