使用Excel数据透视表作为另一个数据透视表的数据源

I have a Pivot table in excel that is using a raw table as its data source. This pivot table is doing a bunch of grouping and summing of rows.

我在excel中有一个Pivot表，它使用一个原始表作为数据源。这个pivot表执行一系列的分组和行求和。

I'd like to now use the result of this new pivot table as the data source for a new pivot table which will further modify this data.

现在我想使用这个新的pivot表的结果作为一个新的pivot表的数据源，这个数据源将进一步修改这个数据。

Is this possible with excel? I suppose you could call it 'nested pivot tables'

用excel可以吗?我想你们可以叫它"嵌套数据透视表"

7 个解决方案

#1

In a new sheet (where you want to create a new pivot table) press the key combination (Alt+D+P). In the list of data source options choose "Microsoft Excel list of database". Click Next and select the pivot table that you want to use as a source (select starting with the actual headers of the fields). I assume that this range is rather static and if you refresh the source pivot and it changes it's size you would have to re-size the range as well. Hope this helps.

在新表中(要创建新的pivot表)，按下键组合(Alt+D+P)。在数据源选项列表中选择“Microsoft Excel数据库列表”。单击Next并选择要用作源的pivot表(从字段的实际头开始)。我假设这个范围是静态的如果你刷新源枢轴它改变了它的大小你也必须重新调整范围的大小。希望这个有帮助。

#2

• Make your first pivot table.

  制作第一个数据透视表。

• Select the first top left cell.

  选择第一个左上角单元格。

• Create a range name using offset:

  使用偏移量创建一个范围名称:

  OFFSET(Sheet1!$A$3;0;0;COUNTA(Sheet1!$A:$A)-1;COUNTA(Sheet1!$3:$3))

  抵消(Sheet1 ! $ 3美元;0,0;COUNTA(Sheet1 !一个美元:一美元)1;COUNTA(Sheet1 ! 3美元:3美元))

• Make your second pivot with your range name as source of data using F3.

  使用F3将您的范围名称作为数据来源的第二个枢轴。

If you change number of rows or columns from your first pivot, your second pivot will be update after refreshing pivot

如果从第一个主元更改行或列的数量，则第二个主元将在刷新后更新

GFGDT

GFGDT

#3

You have to convert the pivot to values first before you can do that:

你必须先将主元转换为值，然后才能这样做:

• Remove the subtotals
• 删除小计
• Repeat the row items
• 重复的行项目
• Copy / Paste values
• 复制/粘贴值
• Insert a new pivot table
• 插入一个新的pivot表

#4

As @nutsch implies, Excel won't do what you need directly, so you have to copy your data from the pivot table to somewhere else first. Rather than using copy and then paste values, however, a better way for many purposes is to create some hidden columns or a whole hidden sheet that copies values using simple formulae. The copy-paste approach isn't very useful when the original pivot table gets refreshed.

正如@nutsch所暗示的，Excel不能直接完成所需的工作，因此必须首先将数据从pivot表复制到其他地方。然而，与使用复制然后粘贴值相比，更好的方法是创建一些隐藏列或整个隐藏表，使用简单的公式复制值。当原始的pivot表被刷新时，复制-粘贴方法并不是很有用。

For instance, if Sheet1 contains the original pivot table, then:

例如，如果Sheet1包含原始的pivot表，则:

• Create Sheet2 and put =Sheet1!A1 into Sheet2!A1
• 创建Sheet2并put =Sheet1!A1 Sheet2 ! A1
• Copy that formula around as many cells in Sheet2 as required to match the size of the original pivot table.
• 复制该公式在Sheet2中尽可能多的单元格，以匹配原始数据透视表的大小。
• Assuming that the original pivot table could change size whenever it is refreshed, you could copy the formula in Sheet2 to cover the whole of the potential area the original pivot table could ever take. That will put lots of zeros in cells where the original cells are currently empty, but you could avoid that by using the formula =IF(Sheet1!A1="","",Sheet1!A1) instead.
• 假设原始的pivot表可以在每次刷新时更改大小，您可以在Sheet2中复制公式，以覆盖原始的pivot表可能占用的整个潜在区域。这将在原来的单元格为空的单元格中放入大量的零，但是您可以使用公式=IF(Sheet1!A1="，"，Sheet1!A1)来避免。
• Create your new pivot table based on a range within Sheet2, then hide Sheet2.
• 根据Sheet2中的范围创建新的pivot表，然后隐藏Sheet2。

#5

Personally, I got around this in a slightly different way - I had a pivot table querying an SQL server source and I was using the timeline slicer to restrict the results to a date range - I then wanted to summarise the pivot results in another table.

就我个人而言，我以一种稍微不同的方式来解决这个问题——我有一个pivot表查询SQL server源，我使用时间轴切片器将结果限制在一个日期范围内——然后我想在另一个表中总结pivot结果。

I selected the 'source' pivot table and created a named range called 'SourcePivotData'.

我选择了“source”pivot表并创建了一个名为“SourcePivotData”的命名范围。

Create your summary pivot tables using the named range as a source.

使用命名范围作为源创建汇总表。

In the worksheet events for the source pivot table, I put the following code:

在源数据透视表的工作表事件中，我输入了以下代码:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

'Update the address of the named range
ThisWorkbook.Names("SourcePivotData").RefersTo = "='" & Target.TableRange1.Worksheet.Name & "'!" & Target.TableRange1.AddressLocal

'Refresh any pivot tables that use this as a source
Dim pt As PivotTable
Application.DisplayAlerts = False
For Each pt In Sheet2.PivotTables
    pt.PivotCache.Refresh
Next pt
Application.DisplayAlerts = True

End Sub

Works nicely for me! :)

很适合我!:)

#6

I guess your end goal is to show Distinct (unique) values inside your original Pivot table.

我猜您的最终目标是在原始的Pivot表中显示不同的(惟一的)值。

For example you could have data set with OrderNumber, OrderDate, OrderItem, orderQty

例如，可以有OrderNumber、OrderDate、OrderItem、orderQty的数据集

First pivot table will show you OrderDate and sum of OrderQty and you probably want to see Count of unique orders in the same Pivot. You woudln't be able to do so within standard pivot table

第一个pivot表将显示OrderDate和OrderQty的总和，并且您可能希望看到同一个pivot中唯一订单的计数。你不能在标准透视表中这样做

If you want to do it, you would need Office 2016 (or perhaps pover Pivot might work). In office 2016 select your data > Insert > Pivot Table > choose tick "Add this data to the Data Model"

如果你想这样做的话，你需要2016年的办公室(或者pover Pivot可以工作)。在office 2016中选择您的数据>插入>数据透视表>选择tick“将该数据添加到数据模型中”

After that, you would be able to select grouping method as Distinct (Count)

在那之后，您将能够选择分组方法作为不同的(计数)

#7

here is how I've done this before.

这是我以前的做法。

1. put a dummy column "X" off to the right of your source pivot table.
2. 在源数据透视表的右侧放置一个虚拟列“X”。
3. click in that cell and start your pivot table.
4. 单击该单元格并启动pivot表。
5. once the dialogue box pops up you can edit the data range to include your pivot table.
6. 对话框弹出后，您可以编辑数据范围以包含数据透视表。
7. this may require you to Refresh the source table first and then refresh your secondary pivot table...or do refresh all twice
8. 这可能需要您先刷新源表，然后刷新次要数据透视表……或者刷新两次