vba Excel - 将多列合并为一列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2977660/
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
Excel - Combine multiple columns into one column
提问by Akib
I have multiple lists that are in separate columns in excel. What I need to do is combine these columns of data into one big column. I do not care if there are duplicate entries, however I want it to skip row 1 of each column.
我有多个列表，它们位于 excel 的不同列中。我需要做的是将这些数据列组合成一个大列。我不在乎是否有重复的条目，但是我希望它跳过每列的第 1 行。
Also what about if ROW1 has headers from January to December, and the length of the columns are different and needs to be combine into one big column?
另外如果ROW1有1月到12月的表头，列的长度不一样，需要合并成一个大列怎么办？
ROW1| 1 2 3
ROW2| A D G
ROW3| B E H
ROW4| C F I
should combine into
应该结合成
A
B
C
D
E
F
G
H
I
The first row of each column needs to be skipped.
每列的第一行需要跳过。
回答by Alex P
Try this. Click anywhere in your range of data and then use this macro:
尝试这个。单击数据范围内的任意位置，然后使用此宏：
Sub CombineColumns()
Dim rng As Range
Dim iCol As Integer
Dim lastCell As Integer
Set rng = ActiveCell.CurrentRegion
lastCell = rng.Columns(1).Rows.Count + 1
For iCol = 2 To rng.Columns.Count
Range(Cells(1, iCol), Cells(rng.Columns(iCol).Rows.Count, iCol)).Cut
ActiveSheet.Paste Destination:=Cells(lastCell, 1)
lastCell = lastCell + rng.Columns(iCol).Rows.Count
Next iCol
End Sub
回答by CristinaP
You can combine the columns without using macros. Type the following function in the formula bar:
您可以在不使用宏的情况下组合列。在公式栏中键入以下函数：
=IF(ROW()<=COUNTA(A:A),INDEX(A:A,ROW()),IF(ROW()<=COUNTA(A:B),INDEX(B:B,ROW()-COUNTA(A:A)),IF(ROW()>COUNTA(A:C),"",INDEX(C:C,ROW()-COUNTA(A:B)))))
The statement uses 3 IF functions, because it needs to combine 3 columns:
该语句使用了 3 个 IF 函数，因为它需要组合 3 列：
- For column A, the function compares the row number of a cell with the total number of cells in A column that are not empty. If the result is true, the function returns the value of the cell from column A that is at row(). If the result is false, the function moves on to the next IF statement.
- For column B, the function compares the row number of a cell with the total number of cells in A:B range that are not empty. If the result is true, the function returns the value of the first cell that is not empty in column B. If false, the function moves on to the next IF statement.
- For column C, the function compares the row number of a cell with the total number of cells in A:C range that are not empty. If the result is true, the function returns a blank cell and doesn't do any more calculation. If false, the function returns the value of the first cell that is not empty in column C.
- 对于 A 列，该函数将单元格的行号与 A 列中非空的单元格总数进行比较。如果结果为真，则该函数返回 A 列中位于 row() 处的单元格的值。如果结果为假，则函数继续执行下一个 IF 语句。
- 对于列 B，该函数将单元格的行号与 A:B 范围内非空的单元格总数进行比较。如果结果为真，则函数返回 B 列中第一个非空单元格的值。如果为假，函数继续执行下一个 IF 语句。
- 对于列 C，该函数将单元格的行号与 A:C 范围内非空的单元格总数进行比较。如果结果为真，该函数将返回一个空白单元格并且不再进行任何计算。如果为 false，则该函数返回 C 列中第一个非空单元格的值。
回答by Kenny LJ
I created an example spreadsheet here of how to do this with simple Excel formulae, and without use of macros (you will need to make your own adjustments for getting rid of the first row, but this should be easy once you figure out how my example spreadsheet works):
我在此处创建了一个示例电子表格，说明如何使用简单的 Excel 公式执行此操作，并且不使用宏（您需要自行调整以删除第一行，但是一旦您弄清楚我的示例如何电子表格有效）：
回答by Steven Dorfmeister
Not sure if this completely helps, but I had an issue where I needed a "smart" merge. I had two columns, A & B. I wanted to move B over only if A was blank. See below. It is based on a selection Range, which you could use to offset the first row, perhaps.
不确定这是否完全有帮助，但我遇到了一个需要“智能”合并的问题。我有两列，A 和 B。只有当 A 为空时，我才想将 B 移过去。见下文。它基于一个选择范围，您可能可以使用它来偏移第一行。
Private Sub MergeProjectNameColumns()
Dim rngRowCount As Integer
Dim i As Integer
'Loop through column C and simply copy the text over to B if it is not blank
rngRowCount = Range(dataRange).Rows.Count
ActiveCell.Offset(0, 0).Select
ActiveCell.Offset(0, 2).Select
For i = 1 To rngRowCount
If (Len(RTrim(ActiveCell.Value)) > 0) Then
Dim currentValue As String
currentValue = ActiveCell.Value
ActiveCell.Offset(0, -1) = currentValue
End If
ActiveCell.Offset(1, 0).Select
Next i
'Now delete the unused column
Columns("C").Select
selection.Delete Shift:=xlToLeft
End Sub
回答by Mark Baker
Function Concat(myRange As Range, Optional myDelimiter As String) As String
Dim r As Range
Application.Volatile
For Each r In myRange
If Len(r.Text) Then
Concat = Concat & IIf(Concat <> "", myDelimiter, "") & r.Text
End If
Next
End Function
