[公式题目]产品名称下面插个空

lisachen

1. =IF(SUM(-(ROW()=MMULT(N(ROW($1:$8)>=COLUMN(A:H)),B$2:B$9)+1)),"",LOOKUP(ROW()-1,SUMIF(INDIRECT("b1:b" & ROW($1:$9)),"<>")+1,A$2:A$9))&“”

复制代码

心电感应

公式很长···110字符。

1. =INDIRECT(TEXT(SMALL(IF(COLUMN(1:1)>B$2:B$9,4^4,ROW($2:$9))*10+(COLUMN(1:1)=B$2:B$9)*3+1,ROW(A1)),"R0C0"),)&""

复制代码

wjc2090742

wjc2090742 发表于 2012-6-18 17:31
这样可以吗？算不算跟原答案相同？多单元格数组公式，比如F2:F240。

用合适的数，可以不用sign的，多单元格77字符：

1. =INDEX(A:A,MOD(SMALL(79^(COLUMN(A:AD)-B2:B9)*9+ROW(2:9)*101,ROW()-1),100))&""

复制代码

clockqg

1. =INDEX(A:A,RIGHT(SMALL(IF(COLUMN($1:$1)<B$2:B$9,ROW($2:$9)+ROW($2:$9)%,IF(COLUMN($1:$1)=B$2:B$9,ROW($2:$9)+99%)),ROW(A1)),2))&""

复制代码

，没有低于80，还得努力。

11005046

按要求另开贴交个

1. =CHAR(SUM(--TEXT(SUBTOTAL(9,OFFSET(B$2,,,ROW($1:$65)))-ROW(A1),"1;2;90")))

复制代码

lisachen

1. =IF(SUMIF(A:A,I1,B:B)-COUNTIF(I$1:I1,I1)=1,"",LOOKUP(ROW()-1,MMULT(N(ROW($1:$8)>COLUMN(A:H)),B$2:B$9),A$2:A$9))

复制代码

1. =IF(SUMIF(A:A,I1,B:B)-COUNTIF(I$1:I1,I1)=1,"",LOOKUP(ROW(),SUMIF(INDIRECT("b1:b" & ROW($1:$9)),"<>")+2,A$2:A$9))

复制代码

离要求还差得远{:soso_e127:}

chenhh803

勉强达标:
=LOOKUP(,0/FREQUENCY(ROW(),SUBTOTAL(9,OFFSET(B$1,,,COLUMN(A:I)))+{0;1}),A:A)&""

都市幻想

1. =OFFSET(A$1,SUBSTITUTE(SMALL(IF(COLUMN(1:1)>B$2:B$9,4^4,ROW($1:$8))/1%+(COLUMN(1:1)=B$2:B$9)*9,ROW(A1)),0,),)&""

复制代码

wangyuhu

125字符
=IF(OR(ROW()-1=SUBTOTAL(9,OFFSET(B$2,,,ROW($1:$8),))),"",INDEX(A:A,SMALL(IF(COLUMN(A:aZ)>B$2:B$9,99,ROW($2:$9)),ROW()-1))&"")

123字符
=INDEX(A:A,RIGHT(SMALL(--MID(REPT(ROW($2:$9)&"   ",B$2:B$9-1)&ROW($2:$9)+1/3&REPT(9,99),COLUMN(A:AD)*4-3,4),ROW(A1)),2))&""

参与一下吧

52baby

字符长，129个。

1. =CHOOSE(MMULT(N(ROW(1:99)>{0,5,6,15,16,33,34,45,46,75,76,83,84,94,95}),ROW(1:15)^0),A2,"",A3,"",A4,"",A5,"",A6,"",A7,"",A8,"",A9)

复制代码