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

圖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ù)透視表的操作,，這種操作會導致單元格格式的變化，尤其是透視表間的間距太小時,。

當完成數(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．數(shù)據(jù)透視圖中圖表元素的格式化設(shè)置,，往往隨交互按鈕的觸發(fā)操作而導致丟失，因為每次的數(shù)據(jù)交互,，數(shù)據(jù)透視圖都進行了再次完全重繪,。

3．數(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ù)量,、多指標基礎(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ù)拆分成更細節(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ù)或者某特定指標進行分析,，如選擇圖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　透視圖表在操作上的不友好,，導致我們必須要考慮一個非常現(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案例圖表是為了學習如何將數(shù)據(jù)透視表和定義名稱結(jié)合，以及如何使用選項按鈕和復(fù)選框工作表控件,。該案例圖表僅比直接使用數(shù)據(jù)透視圖的可視化效果好,，但并不是一個值得稱道的圖表。

小技巧

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

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

注：本章所有的案例,，均對圖表的數(shù)值坐標進行了統(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來處理,。