excel - 使用 Excel 数据透视表或 Power Pivot 将多个变量转换为值

Question

我必须使用Excel来解决这个问题。我需要知道如何从一个非常大的数据集创建一个数据透视表，其简化结构如下所示:

      week 1    week 2  week 3

第 1 行
第 2 行
第 3 行
第 4 行

我需要使用切片器仅使用特定的周，但只需更改周即可。如果我不受 Excel 行数的限制，我将创建一个“日期”变量，并让“第 1 周、第 2 周、第 3 周...”作为该变量的值，但这会导致太多行(使用 52 周，因此 number_of_rows*52 行很快就会达到 100 万行)。

是否可以在 Excel 中创建一个数据透视表，并在“第 X 周”上使用切片器，以便显示所需周的每行值，而无需创建新变量并显着增加行数？

我可以用另一种语言来完成此操作，但我受到其他人的工作技能的限制，因此需要一种方法来通过一个简单的工具来完成此操作，供他们使用。

Answer 1

懂一点 VBA 就可以了。从数据源创建一个新的数据透视表，其中只包含您想要在切片器中显示的周。即“第 1 周”、“第 2 周”等。为该数据透视表创建一个切片器，当用户单击它时，捕获生成的 PivotTable_Update 事件并使用它来更改现有主数据透视表中显示的周字段。

---编辑---看起来是这样的:

...如果我从 Weeks 切片器中选择单个字段，会发生以下情况:

...正如您所看到的，当您单击“虚拟”切片器时，数据透视表中的字段将被切换以匹配切片器选择。

这是可以让您执行此操作的代码。此代码位于标准代码模块中:

Option Explicit

Sub Pivots_SwitchFields(target As PivotTable)
Dim rngSelection As Range
Dim wks As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim lngOrientation As Long
Dim varFields As Variant
Dim varItem As Variant
Dim strInstructions As String
Dim lCalculation As Long
Dim bEnableEvents As Boolean
Dim bScreenUpdating As Boolean


With Application
    lCalculation = .Calculation
    bEnableEvents = .EnableEvents
    bScreenUpdating = .ScreenUpdating
    .EnableEvents = False
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With


strInstructions = target.Name
Set rngSelection = target.RowRange
Set wks = ActiveWorkbook.Worksheets(Split(strInstructions, "|")(1))
Set pt = wks.PivotTables(Split(strInstructions, "|")(2))
pt.ManualUpdate = True

Select Case Split(strInstructions, "|")(3)
Case "Values": lngOrientation = xlDataField
Case "Rows": lngOrientation = xlRowField
Case "Columns": lngOrientation = xlColumnField
Case "Filters": lngOrientation = xlPageField
End Select

'Clear out the old field(s)
Select Case lngOrientation
    Case xlDataField
        For Each pf In pt.DataFields
            pf.Orientation = xlHidden
        Next pf

    Case Else
        For Each pf In pt.PivotFields
            With pf
                If .Orientation = lngOrientation Then
                    If Not .AllItemsVisible Then .ClearAllFilters
                    .Orientation = xlHidden
                End If
            End With
        Next pf
End Select

'Add in the new field(s)
varFields = target.RowRange
On Error Resume Next
For Each varItem In varFields
    With pt.PivotFields(varItem)
    .Orientation = lngOrientation
    If lngOrientation = xlDataField Then .Name = .SourceName & " "
End With

Next varItem
On Error GoTo 0

With Application
    .EnableEvents = bEnableEvents
    .ScreenUpdating = bScreenUpdating
    .Calculation = lCalculation
End With
pt.ManualUpdate = False

End Sub

...此代码位于 ThisWorkbook 模块中:

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal target As PivotTable)
If Split(target.Name, "|")(0) = "SwitchFields" Then Pivots_SwitchFields target
End Sub

请注意，我已为隐藏枢轴指定以下名称:切换字段|工作表 1|数据透视表 1|值...那在哪里|字符是同时按下 Shift 和\得到的管道字符。

您需要修改此名称以提供相关工作表名称、数据透视表名称以及要切换的字段的位置(即值、行、列、筛选器)

另一种选择是使用我在 http://dailydoseofexcel.com/archives/2013/11/21/unpivot-shootout/ 发布的代码使用一些 SQL 将交叉表直接转换为数据透视表。查找我在那篇文章中“2013 年 11 月 26 日更新”标题下发布的很长的例程。如果交叉表适合工作表，该例程会将其转换为平面文件，否则通过 SQL 将其直接转换为数据透视表(尽管可能需要几分钟才能完成)。