excel用函数实现：多行多列按顺序转换为多行一列

Question

有一excel表格（多列，每列的个数不一定一样，具体的列数在每行的A1位置，最大可为300列，最小为1列；多行：达200行）：

转换成（一列）：

可以参考：=OFFSET(B$2,(ROW(1:1)-1)/5,(MOD(ROW(1:1)-1,5)))，但该函数仅限定为固定列数，即函数中的5列（更适合矩阵式的数据）。导致结果是：不够5列的都填充了"0"，不符合我的要求。请高手帮忙破解！

Answer 1

试下=INDIRECT(TEXT(SMALL(IF($A$1:$C$4="",4^8,(ROW($1:$4)*10+COLUMN($A:$C))),ROW(A1)),"R0C0"),)&""

数组公式：

追问

非常感谢。能做些解释吗，看不大懂。
这是A,B,C最大三列，1,2,3,4最多四行。要是最大100列，200行呢，需要修改哪些东西！

追答

1、把$A$1:$C$4这些改为你相应的单元格就行了；

2、工式有点复杂，你最好手动在表里面一步步运行下；
IF($A$1:$C$4="",4^8,(ROW($1:$4)*10+COLUMN($A:$C)))
如果等于空，就为65535 ，等会要用的，如果不会空就为所对应的行*10+列数；这样出来就把数字由小到大排序了；
SALL这里是把数字从小到大出出来；

TEXT是把数字值设置为ROCO格式；
&"" 是为了不返回0，公式本来是会返回0的，连接一个空，就会返回空；

追问

我试过了，功能实现的确很不错！但有两个问题请教：
1、行数或列数多的时候，计算机处理速度明显慢很多（我设的是1500行，300列，大约要将近20分钟处理）；
2、个别地方会出现错误，如#REF！、其他列的数据、或者孔值的问题，是怎么回事？我用的是excel2007！

麻烦您了！

追答

1、因为是数组公式；如果数据量很大的话。电脑性能又不是很好的话，是很慢的。这个是正常情况；建议，分次处理。或换搞点性能的电脑或用VBA了。
2、应该不会出现错误呀? 最少不会出现在中间吧。应该是最后。就是数据已填充完后。直接删了就可以了。
如果出现在中间，那你检查下公式是不是输入正确 了。我测试正常。

追问

帮看一下，哪里错了？谢谢。

公式设在第一列，A1单元格内容为：=INDIRECT(TEXT(SMALL(IF($C$1:$Z$4="",4^8,(ROW($1:$4)*10+COLUMN($C:$Z))),ROW(C1)),"R0C0"),)&""

输完后，按“ctrl+shift+enter”键，回车。然后下拉得到的结果如图。

 

追答

因为列数太多了，要稍改下，=INDIRECT(TEXT(SMALL(IF($C$1:$Z$4="",4^8,(ROW($1:$4)/1%+COLUMN($C:$Z))),ROW(A1)),"R0C00"),)&""

采纳可以给我了吧！嘿嘿！

Answer 2

用公式很纠结。宏比较容易：

Sub test()
Dim arr()
ReDim arr(1 To 1)
brr = Selection
k = 1
For i = 1 To UBound(brr)
For j = 1 To UBound(brr, 2)
If brr(i, j) = "" Then Exit For
arr(k) = brr(i, j)
k = k + 1
ReDim Preserve arr(1 To k)
Next
Next
Sheets(2).[A1].Resize(k - 1, 1) = Application.Transpose(arr)
End Sub

选中要转换的数据区域，运行宏test，结果在sheet2

本回答被网友采纳

Answer 3

令A1=0，可用以下公式转换
=OFFSET(A$1,LOOKUP(ROW(A1)-1,MMULT(--(ROW($1:$200)>=COLUMN(INDIRECT("C1:C200",0))),A$1:A$200),ROW($1:$200)),IF(ROW(A1)<=A$2,ROW(A1),MOD(ROW(A1)-1,LOOKUP(ROW(A1)-1,MMULT(--(ROW($1:$200)>=COLUMN(INDIRECT("C1:C200",0))),A$1:A$200)))+1))下拉填充

Answer 4

选种所有数据区域，然后复制——选择性贴粘——贴粘为数据（并选择转置）