本帖最后由 huangcaiguang 于 2014-4-24 20:45 编辑
D2:E29区域数组
=TEXT(MID(4752316,SMALL(IF(B2:B8>{0,1,2,3},ROW(1:7),8),ROW()-1),1),{"aaaa","ddd"})
=TEXT(MID(4752316,MATCH(ROW()-2,SUMIF(OFFSET(B1,,,ROW(1:8)),"<>")),1),{"aaaa","ddd"})
=TEXT(MID(4752316,MATCH(ROW()-2,SUBTOTAL(9,OFFSET(B1,,,ROW(1:8)))),1),{"aaaa","ddd"})
=TEXT(LOOKUP(ROW()-2,SUBTOTAL(9,OFFSET(B1,,,ROW(1:8))),{4;7;5;2;3;1;6;""}),{"aaaa","ddd"})
=TEXT(MID(4752316,MATCH(ROW()-2,MMULT(N(COLUMN(A:G)<ROW(1:8)),B2:B8)),1),{"aaaa","ddd"})
=TEXT(LOOKUP(ROW()-2,MMULT(N(COLUMN(A:G)<ROW(1:8)),B2:B8),{4;7;5;2;3;1;6;""}),{"aaaa","ddd"})
=TEXT(INDEX({4;7;5;2;3;1;6;""},MATCH(ROW()-2,SUBTOTAL(9,OFFSET(B1,,,ROW(1:8)))),1),{"aaaa","ddd"})
|