Excel 2013:一张表(以下)上的多个数据透视表

I need to create dashboard with multiple pivot table in the same excel sheet one below the other.

我需要在同一个excel表中创建一个包含多个pivot表的仪表板。

The problem is that When the Pivot Table refreshes it may be longer (more rows) so it gives a warning that the rows below what it needs will be overwritten.

问题是，当Pivot表刷新时，它可能会更长(更多行)，因此它发出警告，下面的行将被覆盖。

I'd like to know how to configure Excel pivot table for adding row in pivot table without overwrite the following.

我想知道如何配置Excel pivot表，以便在pivot表中添加行，而不覆盖以下内容。

I have already seen an example when Microsoft Techdays 2013 but I can not remember the method. (There is a check box to enable in Excel 2013)

我已经看到一个例子，当微软Techdays 2013，但我不记得方法。(在Excel 2013中有一个复选框可以启用)

Thank you.

谢谢你！

2 个解决方案

#1

Whenever I have had more than one pivot table per worksheet I either

每当我在每个工作表中有一个以上的数据透视表时，我也是如此

1) restrict the number of rows a pivot table eg I had report that need rolling 6 month values so I always had 6 rows but the values advanced each month. In this case you need to use VBA to 'check' and 'uncheck' the values that show. Use methods such as those found in this Google search to do this:

限制数据透视表的行数例如，我有报告，需要滚动6个月的值，所以我总是有6行，但是每个月的值都在增加。在这种情况下，您需要使用VBA来“检查”和“取消检查”显示的值。使用谷歌搜索中找到的方法来完成以下操作:

https://www.google.com/search?q=find+last+used+row#q=excel+pivot+table+vba+to+filter+row+values

使用https://www.google.com/search?q=find +在+ +行# q = excel表+ vba + +主+ + +过滤+行值

2) upon each refresh, recreate each pivot table from scratch, positioning each pivot table accordingly, by deleting existing pivot tables, and then recreating pivot tables from top down. For this method you need to determine which is last row in pivot table so you can recreate next one below it. Use methods such as those in Google search to find the last pivot table row:

2)每次刷新时，从头重新创建每个pivot表，通过删除现有的pivot表，然后从上到下重新创建pivot表，从而相应地定位每个pivot表。对于这个方法，您需要确定pivot表中的最后一行，以便在它下面重新创建下一行。使用谷歌搜索中的方法查找最后一个pivot表行:

https://www.google.com/search?q=find+last+pivot+table+row

https://www.google.com/search?q=find + + +主+表行

#2

I came across this issue lately, and built the following. Of course to have it work, the prerequisite must be met that in principle the two tables do fit on one sheet. What I than do, is build in sufficient space (=rows) between the two tables, and after refresh hide the rows that are left between the tables. So it does require VBA, and can for example be executed on the PivotTable_Update event.

我最近遇到了这个问题，并构建了以下内容。当然，要使它发挥作用，必须满足这样的先决条件，即在原则上，两个表适合于一个表。我要做的是在两个表之间建立足够的空间(=行)，刷新后隐藏表之间的行。因此，它确实需要VBA，并且可以在PivotTable_Update事件上执行示例。

Private Sub hideRowsBetweenListObjects(sheetName As String)
Dim tblRowPosition1 As Integer
Dim tblNrOfRows1 As Integer
Dim tblRowPosition2 As Integer
Dim tblNrOfRows2 As Integer

'Initialize
Application.ScreenUpdating = False

With Worksheets(sheetName).ListObjects(1)
    tblRowPosition1 = .Range.Row
    tblNrOfRows1 = .Range.Rows.Count
End With

With Worksheets(sheetName).ListObjects(2)
    tblRowPosition2 = .Range.Row
    tblNrOfRows2 = .Range.Rows.Count
End With

With Worksheets(sheetName)
    If tblRowPosition1 < tblRowPosition2 Then
        .Range(.Cells(tblRowPosition1 + tblNrOfRows1, 1), .Cells(tblRowPosition2 - 4, 1)).EntireRow.Hidden = True
    ElseIf tblRowPosition2 < tblRowPosition1 Then
        .Range(.Cells(tblRowPosition2 + tblNrOfRows2, 1), .Cells(tblRowPosition1 - 4, 1)).EntireRow.Hidden = True
    End If
End With

End Sub

终止子

#1