将多个变量转换为具有excel数据透视表或power pivot的值 [英] Converting multiple variables into values with excel pivot tables or power pivot

问题描述

我必须使用excel来解决这个问题。我需要知道如何从一个非常大的数据集中创建一个可以简化的结构化样式的数据集：

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:

      week 1    week 2  week 3

行1

行2

行3

第4行

row 1
row 2
row 3
row 4

我需要使用切片器才能使用特定的星期，但只需更改周。如果我不受限于行数很多，我将创建一个DATE变量，并将第1周，第2周，第3周...作为该变量的值，但这会导致行数太多使用52周，所以number_of_rows * 52行快速最大到100万）。

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).

是否可以在excel中创建一个数据透视表，在X周这样每个行的值都会显示为所需的周，而不需要创建一个新的变量，并且显着增加行数？

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?

我可以用另一种语言来做，我受到别人的工作技能的限制，因此需要一个简单的工具来使用它们。

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.

推荐答案

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

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.

---编辑---
这是怎么样的：

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

...如果我从该周切片器中选择一个单一字段，会发生什么：

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

...您可以看到，当您单击虚拟切片器时，字段将在数据透视表中切换以匹配切片器选择。

...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

...此代码在ThisWorkbook模块中：

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

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

请注意，我给了隐藏数据透视表如下：
SwitchFields | Sheet1 | PivotTable1 | values
...那里|字符是您通过按Shift和\在一起获得的管道字符。

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.

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

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)

另一个选项是使用我在 http://dailydoseofexcel.com/archives/2013/11/21/unpivot-shootout/ 使用一些SQL将交叉表直接转换为数据透视表。在这篇文章中，我找到了一个很长的例子，我发布在2013年11月26日更新的标题下。该例程会将交叉表转换成平面文件，如果它符合表单，否则通过SQL将其直接转换为数据透视表（尽管可能需要几分钟的时间）。

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).

这篇关于将多个变量转换为具有excel数据透视表或power pivot的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！