Excel - 转置列对 [英] Excel - transpose pairs of columns

问题描述

我试图转置 - 如果这是术语的正确应用 - 列对重复行。具体来说，我需要从这里开始：

  Thing1 6 0.29 5 0.23 7 0.19 8 0.11 
  

到：

  Thing1 6 0.29 
 Thing1 5 0.23 
 Thing1 7 0.19 
 Thing1 8 0.11 
  

这个操作会发生在数百个东西的至少7对列上。我不知道的部分是如何分组/锁定要被视为一个单位的对。

在某些方面，我正在做与正常做的相反。一个例子是：

您可能想要更改outpu我在代码中显示的表单名称。

我使用 Sheet2 放置转置的数据，但是可以使用您想要的任何东西。

完成此操作后，您可以关闭编辑器，并使用未转置的数据选择工作表。

按 Alt + F8 运行宏，点击宏，然后按运行

Sheet2 应包含您要查找的结果。

$ b $你可以调用这个任何你想要的
Dim EndCol，OutSheet，OutRow，c，x
Application.ScreenUpdating = False
EndCol = ActiveSheet.UsedRange.columns.Count

'我把这些值放在什么表上？
Set OutSheet = Sheets（Sheet2）'将名称放在引号

OutSheet.Cells.Delete xlShiftUp'这将清除输出表。
OutRow = 1
对于每个c在Intersect（ActiveSheet.UsedRange，ActiveSheet.Range（A：A））
对于x = 2 To EndCol步骤2
OutSheet。细胞（OutRow，1）= c.Value
OutSheet.Cells（OutRow，2）= Cells（c.Row，x）
OutSheet.Cells（OutRow，3）= Cells（c.Row， x + 1）
OutRow = OutRow + 1
下一个x
下一个c
OutSheet.Select
Application.ScreenUpdating = True
End Sub

输入：

输出：

编辑：如果你想添加一个额外的列到开头，也只是显示在一边，你可以改变这样的代码：

 对于每个c在Intersect（ActiveSheet.UsedRange，ActiveSheet.Range（A：A））
对于x = 3到EndCol步骤2'更改2到3 
 OutSheet.Cells（OutRow，1）= c.Value 
 OutSheet.Cells（OutRow，2）= Cells（c.Row，2）'添加此行
 OutSheet.Cells（OutRow，3）= Cells（c.Row，x）'更改为Col 3 
 OutSheet.Cells（OutRow，4）= Cells（c.Row，x + 1）'更改为Col 4 
 OutRow = OutRow + 1 
下一个x 
下一步c 
  

为了更好地解释这个循环，

它从顶部到底部通过列 A 中的每个单元格。

内循环一次超过2列。

所以我们从列 B 开始，接下来是 D ，接下来是 F ..等等。

所以一次我们有这个价值，我们也抓住它的价值。

这就是 Cells（c.Row，x）和单元格（c.Row，x + 1）。

OutSheet.Cells（OutRow，1）= c.Value 说 - 只是让第一列与第一列匹配。

当我们添加第二列时， OutSheet.Cells（OutRow，2 ）=单元格（c.Row，2）'添加了这行我们说的是，也匹配第二列。

希望我做了体面的工作解释。

I'm attempting to transpose - if that's the right application of the term - pairs of columns into repeating rows. In concrete terms, I need to go from this:

Thing1     6    0.29    5   0.23    7   0.19    8   0.11

to this:

Thing1     6    0.29
Thing1     5    0.23
Thing1     7    0.19
Thing1     8    0.11

This operation will occur with at least 7 pairs of columns for several hundred "things." The part I can't figure out is how to group/lock the pairs to be treated as one unit.

In some ways, I'm trying to do the opposite of what is normally done. One example is here: Transpose and group data but it doesn't quite fit, even if I attempt to look at it backwards.

EDIT: Another example that is similar, but I need to do almost the reverse: How to transpose one or more column pairs to the matching record in Excel?

My VBA kung fu is weak, but I'm willing to try whatever your collective wisdom suggests.

Ideas are welcome, and in any case, thank you for reading.

解决方案

Here is a VBA solution.

To implement this, press Alt+F11 to open the VBA editor.

Right click to the left side and select "Insert Module"

Paste the code into the right side of this.

You may want to change the output sheet name as I have shown in the code.

I use Sheet2 to place the transposed data, but you can use whatever you want.

After you have done this, you may close the editor and select the sheet with your non-transposed data.

Run the macro by pressing Alt+F8, clicking on the macro, and pressing Run

Sheet2 should contain the results you are looking for.

Sub ForJeremy() 'You can call this whatever you want
Dim EndCol, OutSheet, OutRow, c, x
Application.ScreenUpdating = False
EndCol = ActiveSheet.UsedRange.columns.Count

'What sheet do I put these values on?
Set OutSheet = Sheets("Sheet2") 'Put the name in the quotes

OutSheet.Cells.Delete xlShiftUp 'This clears the output sheet.
OutRow = 1
For Each c In Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("A:A"))
    For x = 2 To EndCol Step 2
        OutSheet.Cells(OutRow, 1) = c.Value
        OutSheet.Cells(OutRow, 2) = Cells(c.Row, x)
        OutSheet.Cells(OutRow, 3) = Cells(c.Row, x + 1)
        OutRow = OutRow + 1
    Next x
Next c
OutSheet.Select
Application.ScreenUpdating = True
End Sub

Input:

Output:

Edit: If you wanted to add an additional column to the beginning that would also just display to the side, you would change the code like this:

For Each c In Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("A:A"))
    For x = 3 To EndCol Step 2 'Changed 2 to 3
        OutSheet.Cells(OutRow, 1) = c.Value
        OutSheet.Cells(OutRow, 2) = Cells(c.Row, 2) 'Added this line
        OutSheet.Cells(OutRow, 3) = Cells(c.Row, x) 'Changed to Col 3
        OutSheet.Cells(OutRow, 4) = Cells(c.Row, x + 1) 'Changed to Col 4
        OutRow = OutRow + 1
    Next x
Next c

To better explain this loop,

It goes through each cell in column A from the top to the bottom.

The inner loop scoots over 2 columns at a time.

So we start at column B, and next is D, and next is F .. and so on.

So once we have that value, we grab the value to the right of it as well.

That's what the Cells(c.Row, x) and Cells(c.Row, x + 1) does.

The OutSheet.Cells(OutRow, 1) = c.Value says - just make the first column match the first column.

When we add the second one, OutSheet.Cells(OutRow, 2) = Cells(c.Row, 2) 'Added this line we are saying, match the second column too.

Hope I did a decent job explaining.

这篇关于Excel - 转置列对的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！