使用VBA枢轴数据 [英] Pivot data using VBA
本文介绍了使用VBA枢轴数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我具有以下格式的数据
Col1 Col2 Col3 Col4
ServerA 1002 CPU 1
ServerA 1003 Cores 4
ServerA 1005 Memory 16
我需要数据显示为
Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
ServerA 1002 CPU 1003 Cores 4 1005 Memory 16
是否可以使用Excel VBA实现此目的?
Is there a way to achieve this using Excel VBA?
推荐答案
您可以尝试以下操作:
不是很干净,但我认为它应该符合您的目的.
You can try this:
Not very clean but I think it should serve your purpose.
Option Explicit
Sub Sample()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim RngToUnstack As Range, cel As Range, cel1 As Range
Dim i As Long
Set ws1 = ThisWorkbook.Sheets("Sheet2")
Set ws2 = ThisWorkbook.Sheets("Sheet3")
Set RngToUnstack = ws1.UsedRange
'~~> just an alternative to .UsedRange
'Set RngToUnstack = ws1.Range("A1", "D" & ws1.Range("A" & _
ws1.Rows.Count).End(xlUp).Row)
'~~> construct your unique ID's in Worksheet 2
With ws2
RngToUnstack.Resize(, 1).Copy .Range("A1")
.Range("A1", .Range("A" & .Rows.Count).End(xlUp).Address).RemoveDuplicates 1, xlNo
End With
'~~> loop to populate the ID's
For Each cel1 In ws2.Range("A1", ws2.Range("A" & ws2.Rows.Count).End(xlUp).Address)
i = 0
For Each cel In RngToUnstack.Resize(, 1)
If cel.Value = cel1.Value Then
cel.Resize(, 3).Offset(0, 1).Copy cel1.Offset(0, (3 * i) + 1)
i = i + 1
End If
Next cel
Next cel1
End Sub
结果:
假设您的样本数据如下所示:
Suppose your sample data looks like this:
运行宏后将变为:
After running the macro will become like this:
这篇关于使用VBA枢轴数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文