數據透視表不僅僅是與數據進行交互的一種方式。您也可以將它們用作常規報告和分析的豐富數據源……而無需使用弱函數GETPIVOTDATA。您可以將數據透視表用作數據庫，就像使用 簡單表或Excel表一樣。但是，我從未見過有關如何執行此操作的描述。這太糟糕了，因為Excel 2010使Excel用戶能夠使用一個或多個數據透視表作為龐大而強大的電子表格數據庫。如果不將數據透視表設置為數據庫，則通常必須使用 GETPIVOTDATA函數從中返回數據。這限制了您的能力，因為GETPIVOTDATA是一種“屏幕抓取器”功能。也就是說，功能不是非常強大的函數只能返回您在屏幕上看到的數字和文本。另一方面，如果確實將數據透視表設置為數據庫，則可以將Excel的更強大功能（如SUMIFS，SUMPRODUCT，INDEX，MATCH等）與數據庫一起使用。在以后的文章中，我將向您展示這些功能如何為您提供更大的功能，以便從電子表格數據庫返回結果。將數據透視表用作數據庫具有許多一般優勢。但是有一個特殊的優勢值得考慮……PowerPivot的優勢微軟對PowerPivot的介紹為使用數據透視表作為電子表格數據庫提供了新的重要優勢。PowerPivot允許工作簿在工作簿中存儲數百萬行數據。但是不幸的是，一個工作簿中的公式和數據透視表無法直接從存儲在另一工作簿中的PowerPivot數據集中返回數據。這是一個問題，因為每次您保存另一代PowerPivot報表時，以及每次創建新的PowerPivot報表工作簿時，都必須保存PowerPivot數據的另一個副本。因此，在不知不覺中，您就被多代相同的數據所掩埋。在SharePoint下使用Excel Services可以幫助您管理此問題。但是，如果您不使用SharePoint，請執行以下操作：僅設置一個工作簿來包含每個PowerPivot數據集。在每個這些工作簿中，將一個或多個數據透視表設置為電子表格數據庫。設置報表工作簿，以從每個PowerPivot工作簿中的數據透視表數據庫返回數據。使用這種方法，您只需維護一個工作簿即可包含每個PowerPivot數據集。但是，您可以創建任意數量的報表工作簿，這些報表工作簿可以從任意數量的PowerPivot工作簿中包含的任意數量的數據透視表數據庫中返回數據。使用這種方法，您可以為每個報表工作簿提供訪問大量數據的權限！您只需要管理每個PowerPivot數據集的一個副本。但是現在，讓我們在電子表格中設置數據透視表數據庫…將數據透視表設置為電子表格數據庫為了方便起見，我定義了該數據透視表，以從我在介紹Excel的三種電子表格數據庫中介紹的Excel表中獲取數據 。但是您的數據透視表可能會鏈接到數據倉庫，Access或其他“真實”數據庫。
默認情況下，您的初始數據透視表將類似于此圖。您不能將此版本的數據透視表用作電子表格數據庫，因為布局太復雜了。它不是簡單的行和列。在這里，使用GETPIVOTDATA是返回其數據的唯一實用選擇。但是Excel 2010允許您選擇將此數據透視表的格式更改為可以用作數據庫的格式。為此，首先在數據透視表中選擇任何單元格。然后在數據透視表工具中，選擇…設計，布局，小計，不顯示小計設計，布局，總計，行和列為關閉設計，布局，報告布局，以表格形式顯示設計，布局，報告布局，重復所有項目標簽設計，布局，空白行，每個項目后刪除空白行選項，顯示，+ /-按鈕（隱藏按鈕）完成這六項更改后，數據透視表將如下所示。
（默認的數據透視表樣式將這些邊界應用于A列中每組項目之間的邊界。由于它們不會妨礙您的工作，因此我從不費心尋找可以消除它們的樣式。）此處，數據透視表的排列方式非常類似于Excel表格。它具有規則的行和列，您的公式可以輕松引用，但有一個例外：公式沒有實際方法可以實際引用此數據透視表中的數據。原因如下：在Excel表格中，Excel會自動命名表格及其中的每一列。這使我們的公式可以按名稱輕松引用數據。但是對于數據透視表，Excel不能為我們的公式提供任何幫助。因此，我們必須使用范圍命名技巧，以允許工作表公式輕松引用數據透視表。因此，讓我們設置名稱…設置范圍名稱，以便公式可以輕松引用數據透視表讓我們仔細定義此步驟。否則，您將需要付出更多的努力。數據透視表字段列表使您可以很好地控制數據透視表的形狀。它可以控制表格的水平和垂直尺寸。但是，當您將數據透視表用作數據庫時，如果可以將更改限制在水平維度上，則可以使工作變得更加輕松。另一方面，您的解決方案必須適應垂直尺寸的變化。限制對水平尺寸的更改Excel使您能夠添加或刪除數據透視表字段，這會導致數據透視表水平擴展或收縮。您還可以更改表中字段的順序。但是，當您使用數據透視表數據庫時，您應該計劃限制這兩個操作。顯然，如果刪除公式依賴的字段，則會破壞公式。而且沒有辦法避免這個問題。另一方面，如果在數據透視表數據庫中添加或重新排列字段，則可以設置自動適應這些更改的范圍名稱。但是，這需要更多的工作來設置。因此，在本文中，我假設您總是將新字段添加到數據透視表的最右邊，并且您不會重新排列現有字段。適應垂直尺寸的變化當您在數據透視表中更改過濾器時，它必然會垂直擴展或收縮。這正是您想要的。Excel公式可以輕松適應這些更改。您只需要使用動態范圍名稱。如何為數據透視表設置動態范圍名稱像大多數普通范圍名稱一樣，動態范圍名稱也會引用工作表中的區域。但是與普通范圍名稱不同，動態范圍名稱可以響應于數據更改而更改其引用。例如，如果數據透視表高十行，則動態范圍名稱只能引用這十行。但是，如果數據透視表的高度增長到一百萬行，則動態范圍名稱可以自動調整以引用所有這些一百萬行。動態范圍名稱是使用返回引用的電子表格函數（通常為 OFFSET或 INDEX函數）定義的。這些函數返回的引用指向工作表的新區域，以響應其參數值的更改。在下圖中，我將向您展示如何使用OFFSET函數定義Sales.DateTime范圍名稱 。但是首先，我需要設置一個公式來返回數據透視表中的行數。設置數據透視表的Sales.NumRows范圍首先，我在數據透視表上方插入了幾行。我將數據透視表的名稱更改為Sales。我添加了下面的單元格A1中顯示的文本。我使用“創建名稱”對話框將單元格A1中的名稱分配給單元格B1。然后，在顯示的單元格中輸入以下公式：B1：= COUNT（$ A：$ A）此公式依賴于Excel的兩個特征。首先，COUNT函數僅計算單元格中的數字，而不計算文本。（另一方面，COUNTA函數同時計算數字和文本。）其次，日期是數字。
因此，由于此表中的每一行都有一個日期，因此我們可以通過計算A列中的日期數來計算數據透視表中的行數。關于此設置，您還應該了解三件事：首先，請注意，日期與COUNTA公式位于不同的列中。這是避免循環計算錯誤的必要條件。其次，如果您的數據中不包含日期也可以。通常，您可以在一列數字值中計算數字。如果那不可能，則可以使用COUNTA函數對一列中的數字和文本進行計數。但是，在這種情況下，應減去數據庫中未包含的內容的單元格數。為了說明，您還可以在顯示的單元格中使用此公式：B1：= COUNTA（$ A：$ A）-3在這里，我減去3可以得出單元格A1，A3和A4中內容的計數。第三，請注意，我為單元格B1分配了名稱Sales.NumRows。通過以“ Sales”開頭與該數據透視表相關的所有名稱（其中“ Sales”為數據透視表的名稱），您的范圍名稱將在Excel中更易于管理。另外，將另一個數據透視表添加到工作簿時，基于新表的名稱，您可以具有相似的范圍。例如，您可能具有Costs.NumRows，SKUs.NumRows等。以下是您可能會想到的三個問題的答案……在范圍名稱中使用句點是否安全？是。Excel將范圍名稱中的句點與其他任何字符一樣對待。但是通過如圖所示使用它們，您可以更輕松地識別與每個數據透視表數據庫關聯的名稱。如果我們更改數據透視表數據庫的名稱，是否需要更改范圍名稱的名稱？不必要。但是，如果您確實使名稱保持同步，則公式將更容易理解。這是因為，如果您使用以“ Sales”開頭的范圍名稱從名為“ Sales”的數據透視表中返回數據，則公式會更清晰。特別是當您在創建公式幾個月后查看公式時。但是，Excel對您為范圍名稱分配什么名稱確實沒有關系。為什么需要在單獨的單元格中設置COUNT或COUNTA計算？我不能僅將它用作定義動態范圍的公式的一部分嗎？由于COUNT和COUNTA必須檢查它們引用的范圍內的每個單元格，因此它們需要花費較長的時間來計算。也就是說，如果我們要在以下公式中使用它們，則每次使用該公式時都需要重新計算。但是，當我們將計算放在一個單元格中時，它只需要計算一次-這樣可以大大減少計算時間。設置動態范圍名稱現在，您可以使用OFFSET函數設置Sales.DateTime動態范圍名稱。通常，該函數采用以下形式：=偏移（參考，行，列，高度，寬度）要定義動態名稱，首先通過選擇“公式”，“定義的名稱”，“定義名稱”來啟動“新名稱”對話框。在“新名稱”對話框中，在“名稱”編輯框中輸入Sales.DateTime，然后輸入公式…= OFFSET（數據！$ A $ 4,1,0，Sales.NumRows，1）…在“引用”編輯框中。然后選擇確定。在此公式中，該函數從單元格A4開始，該單元格是數據透視表的左上角單元格。引用指向下一行（第二個參數），并停留在同一列（第三個參數）。然后，引用將按Sales.NumRows值指定的行數垂直擴展。最后，參考設置為一列寬。您可以類似地設置其他行。完成后，將為該工作表定義名稱，如下所示：Sales.DateTime = OFFSET（數據！$ A $ 4,1,0，Sales.NumRows，1）
Sales.Product = OFFSET（數據！$ B $ 4,1,0，Sales.NumRows，1）
Sales.Color = OFFSET（數據！$ C $ 4,1,0，Sales.NumRows，1）
Sales.Amount = OFFSET（數據！$ D $ 4,1,0，Sales.NumRows，1）（這是為每個名稱設置公式的一種簡單方法：設置名字時，將“偏移”公式復制到“新名稱”對話框的“引用”框中。接下來，在創建其他名稱時，粘貼公式放入每個新名稱的“引用”框中，然后根據需要在第一個參數中編輯列字母。）采取后續步驟既然已經設置了名稱，您的公式就可以引用數據透視表數據庫，就像它們引用其他數據庫一樣，如以下示例所示：簡單表： = SUM（Amount）Excel表格： = SUM（Table1 [Amount]）數據透視表： = SUM（Sales.Amount）但是，當然，您通常需要使用更多的電子表格功能，而不僅僅是SUM。這就是我在 必須知道的兩個函數中從Excel表和數據庫返回值的內容另外，如果您正在尋找有關此主題的其他幫助，我可以通過三種方式為您提供幫助。
