使用excel数据透视表或power pivot将多个变量转换为值

I have to use excel for this problem. I need to know how to create a pivot table from a very large dataset that's simplified structured looks like this:

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

      week 1    week 2  week 3

row 1
row 2
row 3
row 4

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

I need to use a slicer to use only specific weeks but change the week simply. If I weren't constrained by excels number of rows, I would create a "DATE" variable, and have "week 1, week 2, week 3..." be values for that variable, but this results in too many rows (using 52 weeks, so number_of_rows*52 rows quickly maxes out to 1 million).

我需要使用切片器仅使用特定周,但只需更改一周。如果我不受excels行数限制,我会创建一个“DATE”变量,并将“第1周,第2周,第3周...”作为该变量的值,但这会导致行数过多(使用52周,所以number_of_rows * 52行快速达到100万)。

Is it possible to create a pivot table in excel with a slicer on "week X" such that the values for each row are displayed for the desired week without creating a new variable and using increasing the number of rows significantly?

是否可以在excel中使用切片器在“第X周”创建一个数据透视表,以便显示每行的值,而不创建新变量并使用显着增加行数?

I can do this in another language, I'm constrained by the work skills of others, and thus need a way to do this in a simple tool for them to use.

我可以用另一种语言来做这件事,我受到其他人的工作技能的限制,因此需要一种方法来在他们使用的简单工具中做到这一点。

1 个解决方案

#1

A bit of VBA will get you there. Create a new PivotTable from a data source that contains nothing but the weeks that you want to show up in the slicer. i.e. 'Week 1', 'Week 2' etc. Create a slicer for that PivotTable, and when a user clicks on it, catch the resulting PivotTable_Update event and use it to change which weeks fields shows up in your existing master PivotTable.

一点VBA会让你到那里。从数据源创建一个新的数据透视表,该数据源只包含您希望在切片器中显示的周数。即'第1周','第2周'等。为该数据透视表创建一个切片器,当用户点击它时,捕获生成的PivotTable_Update事件并使用它来更改现有主数据透视表中显示的周数字段。

---Edit--- Here's how that looks:

---编辑---这是看起来如何:

...and here's what happens if I select a singe field from that Weeks slicer:

......如果我从Weeks切片器中选择一个单个字段,会发生什么:

...so as you can see, when you click the 'dummy' slicer then the fields get switched out in the PivotTable to match the slicer selection.

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

Here's the code that lets you do that. This code goes in a standard code module:

这是允许您这样做的代码。此代码位于标准代码模块中:

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

...and this code goes in the ThisWorkbook module:

...此代码包含在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

Note that I've given the Hidden Pivot the following name: SwitchFields|Sheet1|PivotTable1|Values ...where that | character is the pipe character you get by pressing Shift and \ together.

请注意,我给Hidden Pivot提供了以下名称:SwitchFields | Sheet1 | PivotTable1 | Values ... where | character是通过按Shift和\得到的管道字符。

You will need to amend this name to provide the relevant sheet name, PivotTable name, and location of the fields you want to switch out (i.e. Values, Rows, Columns, Filters)

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

Another option is to use the code I posted at http://dailydoseofexcel.com/archives/2013/11/21/unpivot-shootout/ to turn the crosstab directly into a PivotTable using some SQL. Look for the very long routine I posted below the heading "Update 26 November 2013" in that article. That routine will turn the crosstab to a flat file if it fits in the sheet, and otherwise turn it directly into a PivotTable via SQL (although it might take a couple of minutes to do so).

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

#1