将多行转换为Excel中的单个堆叠列

Question

我有一份包含各个品牌市场份额的大量数据，格式如下：

1111 2222 3333 4444
5555      7777 8888
9999 0001 0002
0004 0005 0006 0007

有哪些宏代码可以用来获取以下输出：

1111
2222
3333
4444
5555
<emptyCell>
7777
8888
9999
0001
0002
<emptyCell>
0004
0005
0006
0007

必须考虑空单元格。

是否有可能在其他工作表中获得输出？

- Ankur Chandel

---

输出和原始数据有什么区别？您应该更具体地说明原始数据的外观以及在应用宏之后希望它如何呈现。 - hoooman

@Ankur - 你应该展示一下你已经尝试过的内容，这样我们就不会重复做已经完成的工作。理想情况下，你只需要循环遍历每一行，然后继续下一行。但是，你如何知道你已经到达了一行的末尾呢？ - LittleBobbyTables - Au Revoir

两个空单元格可以相邻吗？ - hoooman

所有行的宽度都是固定为4吗？ - hoooman

https://dev59.com/VW3Xa4cB1Zd3GeqPbSNg - Hiten004

Answer 1

为了减少处理器的负荷，将其更改为INDEX版本

在你想要复制数据的任何工作表的第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))

)

如果您不是从第一行开始，请将 ROW() 更改为 ROW()-X，其中 X 是距离顶部的行数（例如，第二行为 1，第三行为 2，第 800 行为 799）
如果有不同数量的列，请将 4 更改为适当的数字。

- SeanC

---

我本来也想回答类似的问题，但因为你的回答而取消了：+1！唯一的改变是：使用INDEX代替OFFSET - 它将提供相同的结果，但是非挥发性，因此在第一次计算运行后速度更快！！！ - Peter Albert

@PeterAlbert，好想法。我把我的公式修正为INDEX。 - SeanC

我没有看到提到这是一个数组公式，并且需要使用Ctrl+Shift+Enter方法输入...我错了吗？ - Code Jockey

@CodeJockey，这不是一个数组公式。它使用当前行的计算来确定从哪里获取数据。INDEX将允许您指示数组中的位置，就像VBA数组一样。 - SeanC

Answer 2

这是从 SeanC 的答案中修改而来（感谢他），以便成为通用的用法，使其他范围维度和起始单元格的人也可以使用：

将“$RANGE$”替换为您范围的引用 将“$CELL$”替换为输出列的第一个单元格的引用：

=INDEX( $RANGE$ ,INT((ROW()-ROW( $CELL$ ))/COLUMNS( $RANGE$ ))+1,MOD(ROW()-ROW( $CELL$ ),COLUMNS( $RANGE$ ))+1)

将此拖动到下面。当然，确保$RANGE$和$CELL$的行和列上都带有'$'符号。

- Rob360

---

(ROW()-ROW($CELL$)) 从0开始，而第一列的 ROW() 从1开始，所以应该是 (ROW()-ROW($CELL$) + 1)。编辑：我现在注意到他有一个你删除了的-1，所以我的评论无效。 - Jonathan Camilleri

非常好！我已经用范围名称替换了$Range$和$Cell$，现在这个公式成为一个很好的实用工具，无需自定义。 - Phil

Answer 3

假设你的范围是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

你可以使用字典对象并将数组转置为列，但这更简单，而且字典对象在Mac上不起作用。您也可以使用“Range（“ E”& k）”而不是“Cells（k，5）”，但Cells（）稍微快一些，因为它不需要连接。请注意，关闭屏幕更新后，运行速度会更快。

- Gaijinhunter

Answer 4

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

- Ethan Edens