函数中级班练习题：多列转一列!

hai

<p>&nbsp;结果请看附件,数据没规律,要求生成内存数组!<br/></p><br/> jwZ5KcaT.rar (1.41 KB, 下载次数: 122)

2008-5-17 11:25 上传

点击文件名下载附件
函数中级班练习题：多列转一列! <br/>

[此贴子已经被作者于2008-5-17 11:24:44编辑过]

小笨蛋

<>[hide]=OFFSET(INDIRECT("r"&amp;SMALL({1;2;3}+{0,0,0},ROW(1:9))&amp;"c"&amp;MOD(SMALL({1;2;3}*100+{1,2,3},ROW(1:9)),100),),,)&amp;""[/</P>
<>有点取巧了!</P>[em01][em01]<BR><BR><SPAN style="COLOR: rgb(0,0,255)">结果非内存数组</SPAN><BR>
<DIV align=right><FONT color=#000066>[此贴子已经被Luckyguy2008于2008-5-17 12:50:17编辑过]</FONT></DIV>

一剑惊心

[hide]跟一次作业不是差不多的吗?错误[em06]<BR>
<DIV align=right><FONT color=#000066>[此贴子已经被作者于2008-5-17 14:41:41编辑过]</FONT></DIV>

杰

[hide]=INDEX(A1:C3,CEILING(ROW(1:9),3)/3,(MOD(ROW(1:9)-1,3)+1))&""

杰

<P>看了HAI的.....</P>
<P>[hide]</P>
<P>=OFFSET(INDIRECT(TEXT((MOD(ROW(1:9)-1,3)+1)+CEILING(ROW(1:9),3)/3*10,"r0c0"),),,)&amp;"" 84</P>
<P>=OFFSET(INDIRECT("r"&amp;CEILING(ROW(1:9),3)/3&amp;"c"&amp;(MOD(ROW(1:9)-1,3)+1),),,)&amp;"" 76</P>

杰

<p>也都不是内存数组.......</p>[em06]

xinanjiang

[hide]=INDEX($A$1:$C$3,INT(SMALL(ROW($1:$3)+COLUMN($A$1:$C$1)/1000,ROW(1:9))),--RIGHT(SMALL(ROW($1:$3)+COLUMN($A$1:$C$1)/1000,ROW(1:9)),3))&""

[此贴子已经被小妖于2008-5-17 16:34:12编辑过]

xinanjiang

[hide]=INDEX($A$1:$C$3,INT(SMALL(ROW($1:$3)+COLUMN($A$1:$C$1)/1000,ROW(1:9))),--RIGHT(SMALL(ROW($1:$3)+COLUMN($A$1:$C$1)/1000,ROW(1:9)),3))&""[此贴子已经被小妖于2008-5-17 16:34:26编辑过]

GB16888

kk

hai

<>[hide]=IF(ROW(1:9)&lt;=3,LOOKUP(ROW(1:9),ROW(1:9),A1:C1)&amp;"",IF(ROW(1:9)&lt;=3*2,LOOKUP(ROW(1:9),ROW(4:9),A2:C2)&amp;"",IF(ROW(1:9)&lt;=3*3,LOOKUP(ROW(1:9),ROW(7:9),A3:C3)&amp;"")))</P>
<>终于找到方法拉!</P>[em01][em01][em01]