我如何在excel中获得函数来堆叠排列在多行中的数据，以堆叠在另一个工作表上的单个颜色中(How do I get a function in excel to stack data arranged

我如何在excel中获得函数来堆叠排列在多行中的数据，以堆叠在另一个工作表上的单个颜色中(How do I get a function in excel to stack data arranged across in several rows to stack in a single coloumn on another worksheet)

我一直试图将当前排列成行的大量数据放入excel中的单独工作表中，并将其叠加到一个单独的coloumn中，以便我可以上传到Microsoftrategy进行分析。 理想情况下，我想要

a1 b1 c1 d1 a2 b2 c2 d2 a3 b3 c3 d3

成为

a1 b1 c1 d1 a2 b2 c2 d2 a3 b3 c3 d3

我有一些非常大的数据集，需要定期做这个，所以希望创建一个VBA脚本来实现这一点，这个脚本可以很容易地修改并应用到其他工作表中。 我也不想复制和粘贴值，但有链接到其他工作表，所以当源更新时，目的地也是如此。 我有尽可能管理得到a1 b1 c1 d1使用下面的代码

Sub test() Dim i As Integer, ii As Integer Dim ws As Worksheet Set ws = Worksheets("sheet2") For i = 1 To 4 ws.Cells(1 +ii, 1).FormulaR1C1 = "='Sheet1'!R1C" & i ii = ii + 1 Next End Sub

当试图制定一个循环来考虑行的变化时，我的问题就出现了。 迄今为止我的尝试已经证明没有结果，令人沮丧; 我设法得到a1 b2 c3的一个对角线或a1 b2 a3 b4 a5 b6的一堆，但似乎无法达到所需的结果。 我觉得这不是非常困难，但制定正确的循环迄今为止已经回避了我。 任何帮助将不胜感激。 谢谢

I've been attempting to pull a large amount of data currently arranged in rows into a separate worksheet in excel and have it stack into a single coloumn such that I can upload to microstrategy to analyse. Ideally I want

a1 b1 c1 d1 a2 b2 c2 d2 a3 b3 c3 d3

to become

a1 b1 c1 d1 a2 b2 c2 d2 a3 b3 c3 d3

I have some quite large data sets and would need to do this regularly so was hoping to create a VBA script to achieve this that could be easily modified and applied to other worksheets. I also would prefer not to copy and paste values but have links to the other worksheet so when the source is updated so is the destination. I've got as far as managing to get a1 b1 c1 d1 Using the following code

Sub test() Dim i As Integer, ii As Integer Dim ws As Worksheet Set ws = Worksheets("sheet2") For i = 1 To 4 ws.Cells(1 +ii, 1).FormulaR1C1 = "='Sheet1'!R1C" & i ii = ii + 1 Next End Sub

My problem arises when trying to formulate a loop to factor in the change of rows. My attempts so far have proved fruitless and frustrating; I've managed to get a diagonal of a1 b2 c3 or a stack of a1 b2 a3 b4 a5 b6 but can't seem to achieve the desired result. I feel it's not incredibly difficult but formulating the correct loop has evaded me so far. Any help would be greatly appreciated. Thanks

最满意答案

看看这是否适合你：

Option Explicit Sub test() With thisworkbook ' The first cell to stack downward from (will include the cell you specify), plus sheet name.' Dim OutputSheetRange as range Set OutputSheetRange = .worksheets("Sheet2").range("A1") ' Change this to the worksheet containing the cells that need to be stacked.' With .Worksheets("Sheet1") Dim FormulaPrefix as string FormulaPrefix = "='" & .name & "'!" ' Code assumes range below, but change to whatever you need.' With .range("A1:D4") Dim RowCount as long RowCount = .rows.count Dim ColumnCount as long ColumnCount = .columns.count Dim ColumnIndex as long Dim RowIndex as long Dim OutputIndex as long OutputIndex = 0 For ColumnIndex = 1 to ColumnCount For RowIndex = 1 to RowCount OutputSheetRange.offset(OutputIndex,0).formula = FormulaPrefix & .cells(rowindex,columnindex).address OutputIndex = OutputIndex + 1 Next RowIndex Next Columnindex End With End with End Sub

对于更大的数据集（假设它可行），此代码可能需要一段时间。 将输入范围读入二维数组，将输入范围堆栈在内存中，然后在单个操作中将堆栈值写回到表单中 - 但这不会在重新计算时保留任何链接/更新会更有效率。

See if this works for you:

Option Explicit Sub test() With thisworkbook ' The first cell to stack downward from (will include the cell you specify), plus sheet name.' Dim OutputSheetRange as range Set OutputSheetRange = .worksheets("Sheet2").range("A1") ' Change this to the worksheet containing the cells that need to be stacked.' With .Worksheets("Sheet1") Dim FormulaPrefix as string FormulaPrefix = "='" & .name & "'!" ' Code assumes range below, but change to whatever you need.' With .range("A1:D4") Dim RowCount as long RowCount = .rows.count Dim ColumnCount as long ColumnCount = .columns.count Dim ColumnIndex as long Dim RowIndex as long Dim OutputIndex as long OutputIndex = 0 For ColumnIndex = 1 to ColumnCount For RowIndex = 1 to RowCount OutputSheetRange.offset(OutputIndex,0).formula = FormulaPrefix & .cells(rowindex,columnindex).address OutputIndex = OutputIndex + 1 Next RowIndex Next Columnindex End With End with End Sub

This code might take a while for larger data sets (assuming it works). It would be far more efficient to read the input range into a two dimensional array, stack the values within memory, then write stacked values back to the sheet in a single operation -- but this wouldn't preserve any links/update upon recalculation.