vba 如何将多个 Excel 列中的数据全部合并为一列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4480227/
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
How to Consolidate Data from Multiple Excel Columns All into One Column
提问by Learning
Lets say I have an excel sheet with 4 columns of data & 20,000 rows of data in each column.
假设我有一个 Excel 表，其中包含 4 列数据和每列 20,000 行数据。
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).
获得它的最有效方法是什么，以便我将所有数据合并到一列中（IE - A 列中的 80,000 行数据，而不是分布在 4 列中的 20,000 行数据）。
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?
此外，如何实施该解决方案。我的意思是，如果您的解决方案不是“公式”而是 VBA，我该如何实现该解决方案？
Thanks!
谢谢！
回答by Dick Kusleika
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
按 Alt+F11 打开 VBE
Press Control+R to view the Project Explorer
按 Control+R 查看项目资源管理器
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
按F5运行代码
回答by Iam_Karthikeyan
Best and Simple solution to follow:
要遵循的最佳和简单的解决方案：
Select the range of the columns you want to be copied to single column
选择要复制到单列的列范围
Copy the range of cells (multiple columns)
复制单元格范围（多列）
Open Notepad++
打开记事本++
Paste the selected range of cells
粘贴选定的单元格范围
Press Ctrl+H, replace \t by \n and click on replace all
按 Ctrl+H，用 \n 替换 \t，然后单击全部替换
all the multiple columns fall under one single column
所有多列都属于一个单列
now copy the same and paste in excel
现在复制相同并粘贴到excel中
Simple and effective solution for those who dont want to waste time coding in VBA
对于不想在 VBA 中浪费时间编码的人来说，简单而有效的解决方案
回答by Kenny LJ
Here is how you do it with some simple Excel formulae, and no fancy VBA needed. The trick is to use the OFFSET formula. Please see this example spreadsheet:
以下是使用一些简单的 Excel 公式进行操作的方法，不需要花哨的 VBA。诀窍是使用 OFFSET 公式。请参阅此示例电子表格：
回答by Chase
You didn't mention if you are using Excel 2003 or 2007, but you may run into an issue with the # of rows in Excel 2003 being capped at 65,536. If you are using 2007, the limit is 1,048,576.
您没有提到您使用的是 Excel 2003 还是 2007，但您可能会遇到 Excel 2003 中的行数上限为 65,536 的问题。如果您使用的是 2007，则限制为 1,048,576。
Also, can I ask what your end goal is for your analysis? If you need to perform many statistical calculations on your data, I would recommend moving out of the Excel environment into something that is more directly suited for data manipulation and analysis, such as R.
另外，我能问一下您分析的最终目标是什么吗？如果您需要对您的数据执行许多统计计算，我建议您将 Excel 环境移到更直接适合数据操作和分析的环境中，例如R。
There are a variety of options for connecting R to Excel, including
将 R 连接到 Excel 有多种选择，包括
Regardless of what you choose to use to move data in/out of R, the code to change from wide to long format is pretty trivial. I enjoy the melt()function from the reshape package. That code would look like:
无论您选择使用什么将数据移入/移出 R，从宽格式更改为长格式的代码都非常简单。我喜欢reshape 包中的melt()功能。该代码如下所示：
library(reshape)
#Fake data, 4 columns, 20k rows
df <- data.frame(foo = rnorm(20000)
, bar = rlnorm(20000)
, fee = rnorm(20000)
, fie = rlnorm(20000)
)
#Create new object with 1 column, 80k rows
df.m <- melt(df)
From there, you can perform any number of statistical or graphing operations. If you use the RExcel plugin above, you can fire all of this up and run it within Excel itself. The R community is very active and can help address any and all questions you may encounter.
从那里，您可以执行任意数量的统计或绘图操作。如果您使用上面的 RExcel 插件，您可以启动所有这些并在 Excel 中运行它。R 社区非常活跃，可以帮助解决您可能遇到的任何和所有问题。
Good luck!
祝你好运！
回答by Paul Karayan
Take a look at Blockspring- you do need to install the plugin, but then it's just another function you call like this:
看看Blockspring- 您确实需要安装该插件，但它只是您调用的另一个函数，如下所示：
=BLOCKSPRING("twodee-array-reduce","input_array",D5:F7)
The source code and other details are here.
If this doesn't suit and/or you want to build off my solution, you can forkmy function (Python) or use another supported scripting language (Ruby, R, JS, etc...).
源代码和其他详细信息在这里。如果这套衣服没有和/或你想建立了我的解决方案，可以fork我的函数（Python）或使用其他支持的脚本语言（Ruby，R，JS，等...）。
回答by Pekka
The formula
公式
=OFFSET(Sheet1!$A,MOD(ROW()-1,COUNT(Sheet1!$A:$A000)),
(ROW()-1)/COUNT(Sheet1!$A:$A000))
placed into each cell of your second workbook will retrieve the appropriate cell from the source sheet. No macros, simple copying from one sheet to another to reformat the results.
放入第二个工作簿的每个单元格将从源工作表中检索适当的单元格。没有宏，只需从一张纸复制到另一张纸即可重新格式化结果。
You will need to modify the ranges in the COUNT function to match the maximum number of rows in the source sheet. Adjust for column headers as required.
您将需要修改 COUNT 函数中的范围以匹配源工作表中的最大行数。根据需要调整列标题。
If you need something other than a 0 for empty cells, you may prefer to include a conditional.
如果空单元格需要除 0 以外的其他值，您可能更喜欢包含条件。
A script to reformat the data may well be more efficient, but 20k rows is no longer a real limit in a modern Excel workbook.
重新格式化数据的脚本可能更有效，但 20k 行在现代 Excel 工作簿中不再是真正的限制。
