如何将来自多个Excel列的数据整合到一列中 [英] How to Consolidate Data from Multiple Excel Columns All into One Column

查看:223
本文介绍了如何将来自多个Excel列的数据整合到一列中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

让我们说,我有一个excel表与4列的数据&每列中的20,000行数据。



获取它的最有效的方法是什么,以便将所有数据整合到一列(IE - 80,000行列A中的数据,而不是2行数据分布在4列中)。



另外,如何实现该解决方案。我的意思是,如果你的解决方案不是一个公式,而是VBA,那我该如何实现呢?



谢谢!

解决方案

保存工作簿。如果这段代码没有做你想要的,返回的唯一方法是关闭而不保存并重新打开。



选择要在一列中列出的数据。必须是连续列。可能包含空白单元格。



按Alt + F11打开VBE



按Control + R查看Project Explorer



导航到您的工作簿的项目,然后选择插入 - 模块



将代码粘贴到代码中窗格

  Sub MakeOneColumn()

Dim vaCells As Variant
Dim vOutput()As变量
Dim i As Long,j As Long
Dim lRow As Long

如果TypeName(Selection)=Range然后
如果Selection.Count> 1然后
如果Selection.Count< = Selection.Parent.Rows.Count然后
vaCells = Selection.Value

ReDim vOutput(1到UBound(vaCells,1)* UBound(vaCells,2),1到1)

对于j = LBound(vaCells,2)到UBound(vaCells,2)
对于i = LBound(vaCells,1) (vaCells,1)
如果Len(vaCells(i,j))> 0然后
lRow = lRow + 1
vOutput(lRow,1)= vaCells(i,j)
End If
Next i
Next j

Selection.ClearContents
Selection.Cells(1).Resize(lRow).Value = vOutput
End If
End If
End If

End Sub

按F5运行代码


Lets say I have an excel sheet with 4 columns of data & 20,000 rows of data in each column.

What is the most efficient way to get it so that I have all of that data consolidated into one column (I.E. - 80,000 rows of data in column A instead of 20,000 rows of data spread out across 4 columns).

Also, how to implement that solution. What I mean is, if your solution isn't a "formula" but VBA, how do I implement that solution?

Thanks!

解决方案

Save your workbook. If this code doesn't do what you want, the only way to go back is to close without saving and reopen.

Select the data you want to list in one column. Must be contiguous columns. May contain blank cells.

Press Alt+F11 to open the VBE

Press Control+R to view the Project Explorer

Navigate to the project for your workbook and choose Insert - Module

Paste this code in the code pane

Sub MakeOneColumn()

    Dim vaCells As Variant
    Dim vOutput() As Variant
    Dim i As Long, j As Long
    Dim lRow As Long

    If TypeName(Selection) = "Range" Then
        If Selection.Count > 1 Then
            If Selection.Count <= Selection.Parent.Rows.Count Then
                vaCells = Selection.Value

                ReDim vOutput(1 To UBound(vaCells, 1) * UBound(vaCells, 2), 1 To 1)

                For j = LBound(vaCells, 2) To UBound(vaCells, 2)
                    For i = LBound(vaCells, 1) To UBound(vaCells, 1)
                        If Len(vaCells(i, j)) > 0 Then
                            lRow = lRow + 1
                            vOutput(lRow, 1) = vaCells(i, j)
                        End If
                    Next i
                Next j

                Selection.ClearContents
                Selection.Cells(1).Resize(lRow).Value = vOutput
            End If
        End If
    End If

End Sub

Press F5 to run the code

这篇关于如何将来自多个Excel列的数据整合到一列中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆