>我有一组数据
如下 _A_ _B__ __C__ ___D___ _E_ ____F_____
1 Jan 2014 apple 5 units $10 $1.25 cost
2 Feb 2014 apple 7 units $14 $1.75 cost
我想创建一个可以使用切片器过滤值(即单位或$s)的图表。我知道数据必须如下所示,但无法弄清楚如何操作。
_A_ _B__ __C__ ___D___ __E___
1 jan 2014 apple units 5.00
2 jan 2014 apple $ volume 10.00
3 jan 2014 apple cost 1.25
4 feb 2014 apple units 7.00
5 feb 2014 apple $ volume 14.00
6 feb 2014 apple cost 1.75
将以下宏分配给工作表上顶部数据集所在的按钮。单击按钮(分配宏后)将转换为底行。如有任何问题,请回复。
Sub SortData()
' Assign the following macro to a button or shape to achieve your result
Dim StartingSheet, val As String
StartingSheet = ActiveSheet.Name
With Sheets.Add
For i = 1 To Sheets(StartingSheet).Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To 3
.Cells(((i - 1) * 3 + (j Mod 3) + 1), 1).Value = Sheets(StartingSheet).Cells(i, 1).Value
.Cells(((i - 1) * 3 + (j Mod 3) + 1), 2).Value = Sheets(StartingSheet).Cells(i, 2).Value
.Cells(((i - 1) * 3 + (j Mod 3) + 1), 3).Value = Sheets(StartingSheet).Cells(i, 3).Value
If j = 1 Then
val = "$ volume"
.Cells(((i - 1) * 3 + (j Mod 3) + 1), 5).Value = Replace(Sheets(StartingSheet).Cells(i, 5).Value, "$", "")
End If
If j = 2 Then
val = "cost"
.Cells(((i - 1) * 3 + (j Mod 3) + 1), 5).Value = Replace(Replace(Sheets(StartingSheet).Cells(i, 6).Value, "$", ""), " cost", "")
End If
If j = 3 Then
val = "units"
.Cells(((i - 1) * 3 + (j Mod 3) + 1), 5).Value = Replace(Sheets(StartingSheet).Cells(i, 4).Value, " units", "")
End If
.Cells(((i - 1) * 3 + (j Mod 3) + 1), 4).Value = val
Next
Next
End With
End Sub