vba 在Excel中将多行转换为单个堆叠列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14463940/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert Multiple Rows into Single Stacked Column in Excel
提问by Ankur Chandel
I have this huge data of Market Share of various brands which looks like this:
我有各种品牌的市场份额的庞大数据，如下所示：
1111 2222 3333 4444
5555 7777 8888
9999 0001 0002
0004 0005 0006 0007
What macro code can be used to get output of:
哪些宏代码可用于获取以下输出：
1111
2222
3333
4444
5555
<emptyCell>
7777
8888
9999
0001
0002
<emptyCell>
0004
0005
0006
0007
The Empty cells must also be considered.
还必须考虑空单元格。
Also is there a possibility for getting the output in other Sheet ?
还有可能在其他 Sheet 中获得输出吗？
回答by SeanC
Changed to INDEXfor a less processor intensive version
更改INDEX为处理器密集程度较低的版本
in row 1 of whatever sheet you want to copy the data in to:
在您要将数据复制到的任何工作表的第 1 行中：
=INDEX($A:$D,INT((ROW()-1)/4)+1,MOD(ROW()-1,4)+1)
copy this down, and once zeros start appearing, you are at the end. (This is the only issue - blank cells will become zero with this. if you wish to preserve the blanks too, then you need this:
把这个复制下来，一旦零开始出现，你就结束了。（这是唯一的问题 - 空白单元格将变为零。如果您也希望保留空白，那么您需要：
=IF(ISBLANK(INDEX($A$1:$D$4,INT((ROW()-1)/4)+1,MOD(ROW()-1,4)+1)),"",INDEX($A$1:$D$4,INT((ROW()-1)/4)+1,MOD(ROW()-1,4)+1))
=IF(ISBLANK(INDEX($A$1:$D$4,INT((ROW()-1)/4)+1,MOD(ROW()-1,4)+1)),"",INDEX( $A$1:$D$4,INT((ROW()-1)/4)+1,MOD(ROW()-1,4)+1))
)
)
if you are not starting at the first row, then change the ROW()to ROW()-Xwhere X is the number of rows down from the top (i.e. 1 for row 2, 2 for row 3, 799 for row 800)
If there are a different number of columns, change the 4 to the appropriate number
如果您不是从第一行开始，则更ROW()改为ROW()-X其中 X 是从顶部向下的行数（即第 2 行为 1，第 3 行为 2，第 800 行为 799）
如果列数不同, 将 4 改为适当的数字
回答by Rob360
Modified from SeanC's answer (thanks buddy) to turn into a generalized usage so that people with other range dimensions and starting cells can use it:
从 SeanC 的答案（感谢好友）修改为通用用法，以便具有其他范围维度和起始单元格的人可以使用它：
Replace '$RANGE$' with references to your range Replace '$CELL$' with reference to the first cell of the output column:
将 '$RANGE$' 替换为对您范围的引用 将 '$CELL$' 替换为对输出列的第一个单元格的引用：
=INDEX( $RANGE$ ,INT((ROW()-ROW( $CELL$ ))/COLUMNS( $RANGE$ ))+1,MOD(ROW()-ROW( $CELL$ ),COLUMNS( $RANGE$ ))+1)
Drag this down. Of course, make sure both $RANGE$ and $CELL$ are fixed with '$' signs on both the row and column.
把这个拖下来。当然，请确保 $RANGE$ 和 $CELL$ 在行和列上都用“$”符号固定。
回答by Ethan Edens
Sub Makealist()
Application.ScreenUpdating = False
Dim rng As Range
' Destination of List
Worksheets("SomeWorksheet1").Activate
Worksheets("SomeWorksheet1").Range("SomeRange1").Select
' Range to Convert to list
Set rng = Worksheets("SomeWorksheet2").Range("SomeRange2")
' Makes sure that all "Blank cells are really Blank"
For Each c In rng.Cells
If Len(c) = 0 Then
c.Value = ""
End If
Next
' Creates the list
For Each c In rng.Cells
If IsEmpty(c.Value) = False Then
Selection.Value = c.Value
Selection.Offset(1, 0).Select
End If
Next
Application.ScreenUpdating = True
End Sub
回答by aevanko
Assuming your range is A1:D4, here is a VBA macro that can do it (just putting the value down column E).
假设您的范围是 A1:D4，这里有一个 VBA 宏可以做到这一点（只需将值放在 E 列下）。
Sub RangeToColumn()
Dim varray As Variant
Dim i As Long, j As Long, k As Long
Application.ScreenUpdating = False
k = 1
varray = Range("A1:D4").value
For i = 1 To UBound(varray, 1)
For j = 1 To UBound(varray, 2)
Cells(k, 5).value = varray(i, j)
k = k + 1
Next
Next
Application.ScreenUpdating = True
End Sub
You could get fancy and use the dictionary object and transpose the array onto a column, but this is more simple, and the dictionary object does not work on Macs. You could also use "Range("E" & k)" instead of "Cells(k, 5)" but the Cells() is slightly faster since it does not require concatenation.
您可能会喜欢并使用字典对象并将数组转置到列上，但这更简单，并且字典对象在 Mac 上不起作用。您也可以使用 "Range("E" & k)" 而不是 "Cells(k, 5)" 但 Cells() 稍微快一些，因为它不需要连接。
Please also note that by turning off the screen updating, this will run much faster.
另请注意，通过关闭屏幕更新，这会运行得更快。
