2.1 傳統Excel數據透視表的工作原理

本書的案例素材是一份假想的小型書店的銷售數據庫，盡管是虛擬數據，但這份數據是根據企業實際情況合理構造出來的，為了方便學習，做了專門的設計，比真實生產數據更適合Power Pivot學習使用。

我們之所以使用虛擬數據而不使用真實數據，第一個原因是使數據盡量涵蓋學習Power Pivot的所有方面；第二個原因是避免真實數據中存在的干擾數據，使讀者將有限的學習精力集中于最重要的內容上。

假設在這個小型書店中，店員每銷售一本書，計算機都會在圖書銷售系統數據庫中生成一條銷售記錄。雖然原始數據存儲于銷售系統數據庫中，但由于大部分業務系統都有數據導出功能，因此，為了使讀者快速熟悉數據，我們將圖書銷售系統數據庫中的數據導出到了Excel文件中，如下圖所示。

在上圖中的圖書銷售數據中，第一行是表的列標題（又稱為字段名稱），從第二行開始，表中的每行（又稱為每條記錄）都代表一次銷售事件。表中的每列內容都是由列標題表示的圖書銷售數據的不同屬性。該數據表中每列的具體內容如下：

A.所售圖書所屬的圖書大類（T0大類K）。

B.所售圖書所屬的圖書子類（T1子類K）。

C.所售圖書的圖書名稱，這里用書號表示（T2書號K）。

D.圖書銷售事件的發生時間（T3銷售日期）。

E.銷售事件的銷售款到賬日期（T3到賬日期）。由于Power Pivot的學習需要，我們假設書店不一定能在銷售事件發生當日立即收到銷售款。

F.所售圖書的原始單價（T4原始單價）。

G.所售圖書的實際售價（T3銷售單價）。

H.銷售事件的圖書銷售冊數（T3銷售冊數）。

I.所售圖書的作者（T4作者）。

J.所售圖書的封面顏色（T4封面顏色）。這一列內容純粹是為了學習Power Pivot設計的，真實的圖書銷售系統一般不會專門記錄這個屬性。

下面，我們基于該數據源制作一個傳統Excel數據透視表，具體方法如下：

選中數據源，選擇“插入”→“表”→“數據透視表”命令，在新生成的Excel工作表中插入數據透視表，并且按照下圖，在Excel數據透視表界面右側的“數據透視表字段”視圖中，分別在行區域、列區域、值區域、篩選區域用鼠標拖入相應字段。

Excel數據透視表是可以直接與外部數據源動態連接的，因此在當前案例中，為了方便學習，我們將圖書銷售數據從外部的圖書銷售系統數據庫中導入本地Excel中，得到如下圖所示的傳統Excel數據透視表。該數據透視表的分析目的是查看每個圖書子類（數據透視表的行標題）中不同原始單價的圖書（數據透視表的列標題）的銷售總冊數。

上圖是比較常見的數據透視表布局，事實上，我們還可以進行更炫酷的數據透視表布局設置。例如，將鼠標光標放置于數據透視表中，選擇“分析”→“篩選”→“插入切片器”命令，在數據透視表中插入切片器控件；對于日期類型的字段，選擇“分析”→“篩選”→“插入日程表”命令，在數據透視表中插入日程表控件。經過以上操作，新的數據透視表布局如下圖所示。

為了更深刻地理解數據透視表的工作原理，我們需要介紹兩個概念，分別是數據透視表值區域和數據透視表篩選環境，這兩個概念可以分別簡稱為透視表值區域和透視表篩選環境。

數據透視表值區域是指數據透視表的“數據匯總區”。要計算數據透視表值區域中每個單元格中的數值，首先確定每個單元格所對應的數據透視表篩選環境，然后以這個數據透視表篩選環境為篩選條件對數據源進行篩選，從而得到若干個數據源子集，最后根據數據透視表值區域中指定的計算方式（如求和、計算平均值、計數等）對這些數據源子集進行匯總計算。

數據透視表篩選環境是指在數據透視表中，所有對數據透視表的數據源起篩選作用的各種數據透視表周邊設置，包括對數據透視表的行標題、列標題的設置，對篩選字段的設置，以及對數據透視表切片器控件和日程表控件的設置，等等。數據透視表篩選環境針對的是數據透視表值區域中的每個單元格，即數據透視表值區域中的每個單元格都對應著不同的數據透視表篩選環境，請參照下圖。

在上圖中的數據透視表中，我們除了對行標題、列標題和篩選字段進行了常規設置，還通過日程表控件將“T3銷售日期”字段的日期限制為2015年，并且通過切片器控件將“T4封面顏色”字段的圖書封面顏色限制為“藍”。新的數據透視表篩選環境下的數據透視表布局如下圖所示。

下面以Excel數據透視表值區域中數值為6的單元格（Excel數據透視表D6位置的單元格）為例進行研究。

這個數值6是怎么得來的呢？根據前面提到的關于數據透視表的重要事實可以知道：這個值是先由該單元格所對應的數據透視表篩選環境對數據源進行篩選，得到一個數據源子集，然后根據數據透視表值區域定義的匯總方式對這個數據源子集進行匯總得到的。

換一個簡潔點兒的表述就是，這個數值6是由其單元格所對應的數據透視表篩選環境（篩選條件）對數據源進行篩選后匯總得到的。在上圖中，數值6所在的單元格所對應的數據透視表篩選環境如下：

行標題“T1子類K”為“21課內”。

列標題“T4原始單價”為“70”。

日程表“T3銷售日期”為“2015”年。

切片器“T4封面顏色”為“藍”。

篩選字段“T0大類K”為“全部”，即無篩選限制。

在本案例中，數據透視表對值區域中的“T3銷售冊數”字段進行的匯總操作是“求和”，從而得到數值6。這里，我們需要清楚的是，數據透視表對其值區域中的每個單元格都進行相同邏輯的操作，只是不同單元格因為位置不同，所以對應的數據透視表篩選環境也不相同。

數據透視表值區域中的每個單元格都對應著一個數據源子集，下面用數據透視表中的“顯示明細數據”來驗證這個觀點。

在數據透視表的默認設置下，雙擊數據透視表值區域中任意一個單元格，會立即在Excel中生成一個新的工作表。該工作表中的內容是從數據源中提取出來的，對應所雙擊單元格的數據源子集的明細數據。也就是說，當雙擊數據透視表值區域中任意一個單元格時，“顯示明細數據”功能會對數據源應用該單元格所對應的數據透視表篩選環境中定義的篩選條件，并且用生成一個新工作表的方式展示所雙擊單元格對應的數據源子集。

雙擊D6單元格，得到該單元格對應的數據源子集明細數據如下圖所示。

由上圖可知，這個數據源子集完全符合D6單元格對應的數據透視表篩選環境。D6單元格所對應的數據透視表篩選環境有如下幾點。

● 行標題“T1子類K”為“21課內”。

● 列標題“T4原始單價”為“70”。

● 篩選字段“T0大類K”為全部，即無篩選限制。

● 日程表“T3銷售日期”為“2015年”。

● 切片器“T4封面顏色”為“藍”。

我們雖然只研究了數據透視表值區域中的一個單元格，但是要知道，數據透視表值區域中的每個單元格都對應著一個應用該單元格所對應的數據透視表篩選環境的數據源子集。

在傳統Excel數據透視表中，我們幾乎不能對數據透視表值區域所對應的數據透視表篩選環境進行任何修改，從而滿足更加靈活的數據分析需求，這也是傳統Excel數據透視表在數據分析能力上的最大短板。

在Power Pivot中，我們可以使用DAX表達式對Power Pivot超級數據透視表值區域中單元格所對應的數據透視表篩選環境進行修改，從而滿足復雜的數據分析需求，這是Power Pivot之所以Power（強大）的最主要原因。