如何使用VBA来重塑excel中的数据 [英] How to use VBA to reshape data in excel

问题描述

我有一个数据集看起来像这样：

  ID |日期1 | Thing1 | DATE2 | Thing2 | DATE3 | Thing3 | Date4 | Thing4 | 
 1 | a | xx | b | xx | c | xx | d | xx | 
 2 | e | xx | f | xx | g | xx | h | xx | 
  

我想将其更改为如下长表：

  ID |日期|事情| 
 1 | a | xx | 
 1 | b | xx | 
 1 | c | xx | 
 1 | d | xx | 
 2 | e | xx | 
 2 | f | xx | 
 2 | g | xx | 
 2 | h | xx | 
  

我知道如何在R中这样做，但对我来说真的很困惑。 >

任何人都可以帮我设置一些vba代码？

提前感谢

解决方案

侧面

  Sub test（）
 Dim Key，Dic As Object，cl As Range，Data As Range，i& ，n& 
 Set Dic = CreateObject（Scripting.Dictionary）
 Dic.CompareMode = vbTextCompare 
i =单元格（Rows.Count，A）。End（xlUp）.Row 
n = 1 
设置数据=范围（B2：B&，&D2：D&，&F2：F& ;，&H2：H& i）
 Dic.Add| ID，Date | Thing
对于每个cl在数据
如果Cells行，A）<> 然后
 Dic.Add n& | &安培;细胞（cl.Row，A），cl.Text& | &安培; cl.Offset（，1）.Text 
n = n + 1 
结束如果
下一个cl 
n = 1 
对于Dic中的每个键
单元格n，K）=拆分（Key，|）（1）
单元格（n，L）=拆分（Dic（Key），|）（0）
单元格（n，M）=拆分（Dic（Key），|）（1）
n = n + 1 
下一个键
 End Sub 
  

输出

I have a data set looks like this:

ID   | Date1| Thing1| Date2| Thing2| Date3| Thing3| Date4| Thing4|
1    |  a   |  xx   | b    |   xx  | c    | xx    | d    |  xx   |
2    |  e   |  xx   | f    |   xx  | g    | xx    | h    |  xx   |

I would like to change that to a long table like this:

ID   | Date| Thing|
 1   |  a  | xx   |
 1   |  b  | xx   |
 1   |  c  | xx   |
 1   |  d  | xx   |
 2   |  e  | xx   |
 2   |  f  | xx   |
 2   |  g  | xx   |
 2   |  h  | xx   |

I know how to do that in R but it is really confused for me in excel.

Can anyone help me set up some vba code?

Thanks in advance

解决方案

here one of the variant from my side

Sub test()
    Dim Key, Dic As Object, cl As Range, Data As Range, i&, n&
    Set Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = vbTextCompare
    i = Cells(Rows.Count, "A").End(xlUp).Row
    n = 1
    Set Data = Range("B2:B" & i & "," & "D2:D" & i & "," & "F2:F" & i & "," & "H2:H" & i)
    Dic.Add "|ID", "Date|Thing"
    For Each cl In Data
        If Cells(cl.Row, "A") <> "" Then
            Dic.Add n & "|" & Cells(cl.Row, "A"), cl.Text & "|" & cl.Offset(, 1).Text
            n = n + 1
        End If
    Next cl
    n = 1
    For Each Key In Dic
        Cells(n, "K") = Split(Key, "|")(1)
        Cells(n, "L") = Split(Dic(Key), "|")(0)
        Cells(n, "M") = Split(Dic(Key), "|")(1)
        n = n + 1
    Next Key
End Sub

output

这篇关于如何使用VBA来重塑excel中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！