Excel -将多个列合并到一个列中

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月有header，那么列的长度是不同的，需要合并成一个大的列吗?

ROW1| 1   2   3    
ROW2| A   D   G    
ROW3| B   E   H    
ROW4| C   F   I

should combine into

应该结合成

The first row of each column needs to be skipped.

需要跳过每列的第一行。

5 个解决方案

#1

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

#2

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)))))

=如果(行()< = COUNTA(一个),指数(一个,行()),如果(行()< = COUNTA(A,B),指数(B:B,行()-COUNTA(一个),如果(行()> COUNTA(C)、“”,指数(C:C,行()-COUNTA(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.
对于列A，函数将单元格的行数与列中未空的单元格总数进行比较。如果结果为真，函数将从行()的A列返回单元格的值。如果结果为false，则函数继续移动到下一个If语句。
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.
对于B列，函数将一个单元格的行号与a:B范围内不为空的单元格总数进行比较。如果结果为真，函数将返回第一个单元格的值，该值在b列中不是空的。如果为假，函数将移动到下一个If语句。
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.
对于C列，函数将单元格的行号与a:C范围内未为空的单元格总数进行比较。如果结果为真，函数将返回一个空单元格，不再进行任何计算。如果为false，函数将返回第一个单元格的值，该值在C列中为空。

#3

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公式进行此操作，并且不使用宏(您需要自己进行调整，以删除第一行，但是一旦您理解了我的示例电子表格是如何工作的，这应该很容易):

https://docs.google.com/a/umich.edu/spreadsheet/ccc?key=0AuSyDFZlcRtHdGJOSnFwREotRzFfM28tWElpZ1FaR2c#gid=0

https://docs.google.com/a/umich.edu/spreadsheet/ccc?key=0AuSyDFZlcRtHdGJOSnFwREotRzFfM28tWElpZ1FaR2c gid = 0

#4

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，我想把B移过去只有当A是空的时候。见下文。它基于一个选择范围，您可以使用它来抵消第一行。

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

#5

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

#1