Excel數(shù)據(jù)透視表是強大的數(shù)據(jù)整合處理引擎，可非常方便和靈活地對數(shù)據(jù)進行匯總、加工、處理。架構(gòu)在數(shù)據(jù)透視表基礎(chǔ)上的數(shù)據(jù)透視圖，具有靈活的數(shù)據(jù)查詢功能，是Excel中最為直接的交互式圖表。可借助數(shù)據(jù)透視表，結(jié)合定義名稱，使用常規(guī)圖表來制作交互圖表。

傳統(tǒng)應(yīng)用

在實際作業(yè)中，我們往往需要處理未經(jīng)加工處理的雜亂數(shù)據(jù)。圖14.3-1的表格是一個混合了多種產(chǎn)品、多狀態(tài)、多屬性的進貨與銷售數(shù)據(jù)表，共使用了761條記錄，其次該數(shù)據(jù)表的數(shù)據(jù)日期分布很零散，需按年按季度來匯總統(tǒng)計。這樣的數(shù)據(jù)若使用圖表表達，就必須借助如圖14.3-2所示的數(shù)據(jù)透視表功能。否則，使用一般方法來處理數(shù)據(jù)將非常費事，且很難快速制作出有價值的圖表。

圖14.3-1　數(shù)據(jù)透視圖案例原始數(shù)據(jù)

圖14.3-2　原始數(shù)據(jù)整理后的數(shù)據(jù)透視表

在使用原始數(shù)據(jù)來生成圖14.3-2的數(shù)據(jù)透視表時，該透視表的布局如圖14.3-3和圖14.3-4所示。當(dāng)生成透視表后，需選中透視表的“發(fā)生日期”字段，單擊鼠標(biāo)右鍵，在彈出菜單中：Excel 2003選“組及顯示明細(xì)數(shù)據(jù)”> “組合”；Excel 2007/2010選“創(chuàng)建組”。在彈出的對話框（如圖14.3-5所示）中，點選“季度”與“年”，Excel 2007/2010另需在“年”字段的設(shè)置“布局和打印”中勾選“以表格形式顯示項目標(biāo)簽”。

圖14.3-3　Excel 2003數(shù)據(jù)透視表布局

圖14.3-4　Excel 2007/2010數(shù)據(jù)透視表布局

圖14.3-5　字段分組設(shè)置對話框

由于數(shù)據(jù)透視表在處理該案例的“數(shù)量”和“金額”兩項求和項時，交互作業(yè)無法直接使用圖表上的交互按鈕來完成，必須借助“數(shù)據(jù)透視表字段列表”窗格來處理，因此這個案例中分別使用如圖14.3-6所示的兩個數(shù)據(jù)透視表來處理“數(shù)量”和“金額”兩項求和項。

圖14.3-6　原始數(shù)據(jù)整理后的最終數(shù)據(jù)透視表

注：兩個數(shù)據(jù)表間要保留足夠的間隔單元格，因為數(shù)據(jù)透視圖的交互展示，是直接對數(shù)據(jù)透視表的操作，這種操作會導(dǎo)致單元格格式的變化，尤其是透視表間的間距太小時。

當(dāng)完成數(shù)據(jù)透視表的布局后，制作數(shù)據(jù)透視圖就變得非常簡單。直接選中相應(yīng)的數(shù)據(jù)透視表，然后制作簇狀柱形圖，依次可生成如圖14.3-7所示的兩個數(shù)據(jù)透視圖，這兩個圖表的交互作業(yè)完全依賴于數(shù)據(jù)透視圖的交互按鈕。

圖14.3-7　最終數(shù)據(jù)透視圖

雖然此類數(shù)據(jù)透視圖表制作起來非常簡單，但可視化效果并不好。以下列舉了一些數(shù)據(jù)透視圖表的局限，供讀者使用數(shù)據(jù)透視圖表時參考：

1．按鈕類元素所占的圖表面積太大且無法移動位置，由于數(shù)據(jù)透視圖的數(shù)據(jù)信息一般都比較大，因此展示效果給人較強的局促感。

2．?dāng)?shù)據(jù)透視圖中圖表元素的格式化設(shè)置，往往隨交互按鈕的觸發(fā)操作而導(dǎo)致丟失，因為每次的數(shù)據(jù)交互，數(shù)據(jù)透視圖都進行了再次完全重繪。

3．?dāng)?shù)據(jù)透視圖對組合類圖表的支持非常有限。有時在交互操作后，系列的圖表類型會發(fā)生變化，尤其是在改變圖表系列個數(shù)時。

數(shù)據(jù)的OLAP操作

數(shù)據(jù)透視表可以實現(xiàn)數(shù)據(jù)處理中經(jīng)常被提及的OLAP的多維分析操作，包括鉆�。―rill-down）、上卷（Roll-up）、切片（Slice）、切塊（Dice）以及旋轉(zhuǎn)（Pivot）。這是一組對普通人而言不知是何物的術(shù)語，為了更好地使大家理解這些概念，以下將以圖14.3-1的數(shù)據(jù)表展開簡單說明。

名詞解釋：

■　OLAP（On-line Analytical Processing，聯(lián)機分析處理）：

基于大數(shù)據(jù)量、多指標(biāo)基礎(chǔ)上的各種數(shù)據(jù)整理與分析方式集合，簡單理解就是：Excel中基于數(shù)據(jù)透視表的各類透視過程。對于復(fù)雜的大型數(shù)據(jù)集而言，Excel的處理能力不足應(yīng)付時，可以使用微軟專門基于SQL Server的工具集Microsoft SQL Server OLAP Services，該工具是為處理數(shù)據(jù)的OLAP應(yīng)用而生。

?數(shù)據(jù)立方體（Data Cube）：

其實就是需要分析的原始數(shù)據(jù)集合，此處圖片只是示意，實際的數(shù)據(jù)維度遠不止3維。此處可以理解為圖14.3-1的數(shù)據(jù)表。

?鉆取（Drill-down）：

將匯總數(shù)據(jù)拆分成更細(xì)節(jié)的數(shù)據(jù)，比如圖14.3-2的透視表在未進行分組時的日期狀態(tài)。

?旋轉(zhuǎn)（Pivot）：

即互換維的位置，簡單而言就是行列轉(zhuǎn)換，Excel中的轉(zhuǎn)置功能即屬于此操作。如基于圖14.3-2的數(shù)據(jù)表透視表將“發(fā)生日期”和“品種”兩個字段進行相應(yīng)的交換。

?切塊（Dice）：

選擇特定區(qū)間數(shù)據(jù)或者某特定指標(biāo)進行分析，如選擇圖14.3-1數(shù)據(jù)表中所有產(chǎn)品上半年的銷售數(shù)量與銷售金額。

?切片（Slice）：

選取特定維度的數(shù)據(jù)進行分析，如只選擇圖14.3-1數(shù)據(jù)表中各季度產(chǎn)品A的銷售數(shù)量數(shù)據(jù)。

?上卷（Roll-up）：

鉆取的逆操作，簡單地理解就是Excel的分類匯總，如只將圖14.3-1數(shù)據(jù)表中日期銷售數(shù)量匯總為各年度數(shù)據(jù)。

這些概念也許顯得晦澀和難懂，但其實在制作交互式圖表的過程中，我們都有意或無意在使用這些操作方法，因為交互式圖表依賴這樣的數(shù)據(jù)操作。如果單純使用函數(shù)公式來處理如此龐雜的數(shù)據(jù)，將變得不具可操作性，因此借助Excel的數(shù)據(jù)透視表功能就顯得非常必要。隨著Excel的推陳出新，Excel在不斷加強數(shù)據(jù)透視表功能，在Excel 2010中，已經(jīng)添加了切片器功能，該功能基于數(shù)據(jù)透視表和SQL應(yīng)用。

與定義名稱結(jié)合

圖14.3-7　透視圖表在操作上的不友好，導(dǎo)致我們必須要考慮一個非常現(xiàn)實的問題：如何借助常規(guī)圖表結(jié)合數(shù)據(jù)透視表來制作交互式圖表。有個非常令人困擾的問題是：如果圖表引用的數(shù)據(jù)來源自Excel的數(shù)據(jù)透視表，該圖表會被Excel強制變?yōu)閿?shù)據(jù)透視圖。

Excel 2003中，可以先使用圖表向?qū)�來生成一個空白圖表，然后使用數(shù)據(jù)拖曳方式添加系列，但該方法并不非�？煽�，在不確定的狀況下，該圖表依然會被Excel自動設(shè)置為數(shù)據(jù)透視圖。在Excel 2007和2010中則完全無望，拖曳功能也不再被支持。但并非沒有辦法來實現(xiàn)這樣的操作，借助定義名稱的方法，傳統(tǒng)Excel圖表和數(shù)據(jù)透視表可以被有機地結(jié)合在一起。圖14.3-8所示的案例，就是圖14.3-7的常規(guī)圖表演繹，該圖僅使用了一個圖表來展示“數(shù)量”和“金額”兩項求和項。

圖14.3-8　數(shù)據(jù)透視表和定義名稱結(jié)合的案例

這個圖表的制作過程基于圖14.3-7案例數(shù)據(jù)透視表，通過使用4個數(shù)據(jù)透視表來實現(xiàn)，這些透視表和圖14.3-3的結(jié)構(gòu)基本相同。制作方法說明如下：

1．參照圖14.3-6數(shù)據(jù)透視表的生成過程，生成如圖14.3-9所示的4個數(shù)據(jù)透視表。

圖14.3-9　圖14.3-8案例4個數(shù)據(jù)透視表

2．新增“類別”、“狀態(tài)”兩個輔助列，相關(guān)內(nèi)容參照圖14.3-8設(shè)置即可。再依次新增4個觸發(fā)鏈接列“狀態(tài)”、“類別”、“年度”、“分類”，除“分類”為5個單元格外，其余皆為1個單元格。

3．分別添加以下工作表控件：兩個組合框、兩個分組框、3個選項按鈕、5個復(fù)選框。分組框并不參與觸發(fā)作業(yè)，僅為視覺分組，排布和外觀設(shè)定參照圖14.3-8設(shè)置即可，數(shù)據(jù)源區(qū)域和單元格鏈接從上到下依次為：

1. 組合框1中數(shù)據(jù)源區(qū)域為$C$5:$C$6，單元格鏈接為$F$6；
2. 組合框2中數(shù)據(jù)源區(qū)域為$D$5:$D$6，單元格鏈接為$G$6；
3. 3個選項按鈕的單元格鏈接為$H$6；
4. 復(fù)選框1的單元格鏈接為$I$6；
5. 復(fù)選框2的單元格鏈接為$I$7；
6. 復(fù)選框3的單元格鏈接為$I$8；
7. 復(fù)選框4的單元格鏈接為$I$9；
8. 復(fù)選框5的單元格鏈接為$I$10。

4．分別定義“AData”、“BData”、“CData”、“DData”、“EData”、“分類”、“刻度”7個定義名稱，公式如下所示：

5．生成無數(shù)據(jù)的簇狀柱形圖，依次添加6個圖表系列，這6個圖表系列的SERIES公式依次為：

6．將系列6“刻度”的圖表類型改為XY散點圖，并將圖表按照圖14.3-8右側(cè)圖表樣式美化即可。

圖14.3-8案例圖表是為了學(xué)習(xí)如何將數(shù)據(jù)透視表和定義名稱結(jié)合，以及如何使用選項按鈕和復(fù)選框工作表控件。該案例圖表僅比直接使用數(shù)據(jù)透視圖的可視化效果好，但并不是一個值得稱道的圖表。

小技巧

交互式圖表動態(tài)設(shè)置數(shù)值坐標(biāo)刻度，使其保持一致。

由于涉及“金額”和“數(shù)量”兩個不同量綱，圖表的數(shù)值坐標(biāo)無法直接手工設(shè)定為統(tǒng)一刻度，圖14.3-8的系列6“刻度”即是為保證刻度的一致性而設(shè)（圖表中并未隱去，實際作業(yè)中可設(shè)置其為無標(biāo)記點）。

注：本章所有的案例，均對圖表的數(shù)值坐標(biāo)進行了統(tǒng)一設(shè)置。

文檔14.3-2是圖14.3-8案例使用列表框工作表控件的復(fù)選選項實例，文檔中獲取復(fù)選數(shù)值使用了xlm4.0宏表函數(shù)。該文檔僅限在Excel 2003中使用，在Excel 2007/2010中已不可使用，因為該功能已經(jīng)不被支持，若需使用應(yīng)借助VBA來處理。