重新构造数据的排放列表

一片绿叶

<table cellspacing="0" cellpadding="0" width="288" border="0" xstr="" style="WIDTH: 216pt; BORDER-COLLAPSE: collapse;"><colgroup><col span="4" width="72" style="WIDTH: 54pt;"></col></colgroup><tbody><tr height="16" style="HEIGHT: 12pt; mso-height-source: userset;"><td class="xl22" width="72" height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;"></td><td class="xl22" width="72" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"></td><td valign="top" align="left" width="72" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><line id="_x0000_s2049" oinsetmode="auto" strokecolor="windowText [64]" coordsize="21600,21600" to="64.5pt,36pt" from="1.5pt,7.5pt" style="Z-INDEX: 1; POSITION: absolute; flip: y;"><stroke endarrow="block"></stroke></line><span style="mso-ignore: vglayout2;"><table cellspacing="0" cellpadding="0"><tbody><tr><td class="xl22" width="72" height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;"></td></tr></tbody></table></span></td><td class="xl22" width="72" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2">生成效果表</font></td></tr><tr height="16" style="HEIGHT: 12pt; mso-height-source: userset;"><td class="xl22" height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2">数据基础表</font></td><td class="xl22" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2"></font></td><td class="xl22" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2"></font></td><td class="xl23" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2">静海</font></td></tr><tr height="16" style="HEIGHT: 12pt; mso-height-source: userset;"><td class="xl23" height="16" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2">项目</font></td><td class="xl23" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2">列数</font></td><td class="xl22" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2"></font></td><td class="xl23" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2">静海</font></td></tr><tr height="16" style="HEIGHT: 12pt; mso-height-source: userset;"><td class="xl23" height="16" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2">静海</font></td><td class="xl23" xnum="" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2">2</font></td><td class="xl22" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2"></font></td><td class="xl23" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2">津南</font></td></tr><tr height="16" style="HEIGHT: 12pt; mso-height-source: userset;"><td class="xl23" height="16" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2">津南</font></td><td class="xl23" xnum="" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2">3</font></td><td class="xl22" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2"></font></td><td class="xl23" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2">津南</font></td></tr><tr height="16" style="HEIGHT: 12pt; mso-height-source: userset;"><td class="xl23" height="16" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2">西青</font></td><td class="xl23" xnum="" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2">4</font></td><td class="xl22" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2"></font></td><td class="xl23" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2">津南</font></td></tr><tr height="16" style="HEIGHT: 12pt; mso-height-source: userset;"><td class="xl22" height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2"></font></td><td class="xl22" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2"></font></td><td class="xl22" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2"></font></td><td class="xl23" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2">西青</font></td></tr><tr height="16" style="HEIGHT: 12pt; mso-height-source: userset;"><td class="xl22" height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2"></font></td><td class="xl22" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2"></font></td><td class="xl22" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2"></font></td><td class="xl23" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2">西青</font></td></tr><tr height="16" style="HEIGHT: 12pt; mso-height-source: userset;"><td class="xl22" height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2"></font></td><td class="xl22" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2"></font></td><td class="xl22" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2"></font></td><td class="xl23" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2">西青</font></td></tr><tr height="16" style="HEIGHT: 12pt; mso-height-source: userset;"><td class="xl22" height="16" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12pt; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2"></font></td><td class="xl22" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2"></font></td><td class="xl22" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2"></font></td><td class="xl23" style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent;"><font face="宋体" size="2">西青</font></td></tr></tbody></table><p>以上提供了“数据基础表”和“生成效果表”，将“数据基础表”中的项目列的内容按列数规定的数量生成纵向的内存数组。</p><p>为了大家方便回答，把项目列定为A1单元格，列数为B1单元格，效果区域的第一个单元格为C1单元格</p><p>&nbsp;</p>

Luckyguy2008

内存数组，C1:C9直接填入公式，三键结束<br/>=T(OFFSET(A2,MMULT(N(ROW(INDIRECT("1:"&amp;SUM(B2:B4)))&gt;TRANSPOSE(SUBTOTAL(9,OFFSET(B2,,,ROW(B2:B4)-1)))),ROW(B2:B4)^0),))<br/>=T(OFFSET(A2,MMULT(N(ROW(INDIRECT("1:"&amp;SUM(B2:B4)))&gt;TRANSPOSE(SUMIF(OFFSET(B2,,,ROW(B2:B4)-1),"&gt;0"))),ROW(B2:B4)^0),))<br/>=T(INDIRECT("A"&amp;SMALL(IF(COLUMN(1:1)&lt;=B$2:B$4,ROW(B2:B4)),ROW(INDIRECT("1:"&amp;SUM(B2:B4))))))<br/>=T(OFFSET(A2,SMALL(IF(COLUMN(1:1)&lt;=B$2:B$4,ROW(B2:B4)-1),ROW(INDIRECT("1:"&amp;SUM(B2:B4))))-1,))<br/>=LOOKUP(ROW(INDIRECT("1:"&amp;SUM(B2:B4)))-1,SUM(B2:B4)-SUMIF(OFFSET(B4,,,ROW(B2:B4)-ROW(B4)-1),"&gt;0"),A2:A4)<br/><br/>在C1填入公式，三键，把公式填充到C9<br/>=INDEX(A:A,1+MATCH(1,(--(ROW(A1)&lt;=SUMIF(OFFSET(B$2,,,ROW(B$2:B$4)-1),"&gt;0"))),0))<br/>=INDEX(A:A,SMALL(IF(COLUMN(1:1)&lt;=B$2:B$4,ROW(B$2:B$4)),ROW(A1)))<br/>=INDEX(A:A,SMALL(IF(COLUMN(1:1)&lt;=B$2:B$4,ROW(B$2:B$4),50000),ROW(A1)))&amp;""（加了容错处理）<br/>

[此贴子已经被作者于2007-9-12 15:09:10编辑过]

一片绿叶

<p>高人啊</p><p></p>

不看不看

<p><br/><br/></p><p>普通公式的</p>

[此贴子已经被作者于2007-9-16 20:43:34编辑过]

不看不看

<p>再改一下 在D2中输入 下拉</p><p>=IF(ROW()=2,$A$3,IF(COUNTIF($D$1:D1,D1)&lt;INDIRECT("r"&amp;MATCH(D1,$A$3:$A$5,0)+2&amp;"c2",),D1,INDIRECT("r"&amp;MATCH(D1,$A$3:$A$5,0)+3&amp;"c1",)))</p>

[此贴子已经被作者于2007-9-16 20:47:50编辑过]

65122836

你看明白了吗?

80008

哎，晕了

Luckyguy2008

不看不看版主原来的一个思路，我稍微调整了一下<br/><br/>=T(INDIRECT("A"&amp;LOOKUP(ROW(INDIRECT("1:"&amp;SUM(B2:B4))),SUM(B2:B4)-MMULT(N(ROW(B2:B4)&lt;=TRANSPOSE(ROW(B2:B4))),B2:B4)+1,ROW(INDIRECT("1:"&amp;SUM(B2:B4)))+1)))<br/><br/>

不看不看

<div class="msgheader">QUOTE:</div><div class="msgborder"><b>以下是引用<i>Luckyguy2008</i>在2007-9-16 21:23:34的发言：</b><br/>不看不看版主原来的一个思路，我稍微调整了一下<br/><br/>=T(INDIRECT("A"&amp;LOOKUP(ROW(INDIRECT("1:"&amp;SUM(B2:B4))),SUM(B2:B4)-MMULT(N(ROW(B2:B4)&lt;=TRANSPOSE(ROW(B2:B4))),B2:B4)+1,ROW(INDIRECT("1:"&amp;SUM(B2:B4)))+1)))<br/><br/></div><p></p>[em17][em17][em17]

hai

=T(INDIRECT("a"&amp;SMALL(IF(TRANSPOSE(ROW(INDIRECT("1:"&amp;SUM(B2:B4))))&lt;=B2:B4,B2:B4,60),ROW(INDIRECT("1:"&amp;SUM(B2:B4))))))