按名称引用透视表Excel 2010 [英] Reference pivot table by name Excel 2010

问题描述

我的任务是重组带有几十个数据透视表的极其混乱的excel文件.重组的一部分将涉及在同一张纸上和新纸上移动数据透视表，从而为它们随时间的增长创造空间.但是，使用getpivotdata()函数的数据透视表引用被硬编码到数据透视表的位置.

I've been tasked with reorganizing an extremely messy excel file with a few dozen pivot tables. Part of the reorganization will involve moving the pivot tables around, both on the same sheet and onto new sheets to create room for them to grow over time. However, pivot table references using the getpivotdata() function are hardcoded to the location of the pivot table.

我一直在尝试通过使用自定义函数来接受数据透视表的名称并返回其位置的方法来解决此问题，以供在getpivotdata()函数中使用，以允许我在不引用数据的情况下移动数据透视表桌子破了.我尝试返回位置的字符串版本和指定位置的范围对象，但是这两个都给我带来了参考错误.到目前为止，我最成功的函数已返回一个数据透视表对象，尽管它没有给我带来参考错误，但无论我尝试什么，它都仅返回0作为数据

I've been trying to get around this by using a custom function to accept the name of the pivot table and return its location, for use within the getpivotdata() function to allow me to move the pivot tables around without references to the table breaking. I tried returning a string version of the location and a range object specifying the location but both of those give me reference errors. My most successful function so far has returned a pivottable object, and while it doesn't give me a reference error, it only returns 0 as the data no matter what I try

这是当前代码:

Public Function pivotNameToLoc(PTName As String) As PivotTable
Dim WS As Worksheet, PT As PivotTable

For Each WS In ActiveWorkbook.Worksheets
    For Each PT In WS.PivotTables
        If PT.Name = PTName Then
            pivotNameToLoc = PivotTable
            Exit Function
        End If
    Next PT
Next WS
End Function

这是在单元格公式中调用函数的方式

Here's how the function is being called in cell formulas

=GETPIVOTDATA("Data field",pivotNameToLoc("Name of table"),"other","fields")

推荐答案

您需要返回一个范围:

Public Function pivotNameToLoc(PTName As String) As Range
Dim WS As Worksheet, PT As PivotTable

For Each WS In ActiveWorkbook.Worksheets
    For Each PT In WS.PivotTables
        If PT.Name = PTName Then
            Set pivotNameToLoc = PT.TableRange1.Cells(1)
            Exit Function
        End If
    Next PT
Next WS
End Function

这篇关于按名称引用透视表Excel 2010的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！