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