Excel數(shù)據(jù)透視表是強(qiáng)大的數(shù)據(jù)整合處理引擎，可非常方便和靈活地對(duì)數(shù)據(jù)進(jìn)行匯總、加工、處理。架構(gòu)在數(shù)據(jù)透視表基礎(chǔ)上的數(shù)據(jù)透視圖，具有靈活的數(shù)據(jù)查詢功能，是Excel中最為直接的交互式圖表?？山柚鷶?shù)據(jù)透視表，結(jié)合定義名稱，使用常規(guī)圖表來(lái)制作交互圖表。
在實(shí)際作業(yè)中，我們往往需要處理未經(jīng)加工處理的雜亂數(shù)據(jù)。圖14.3-1的表格是一個(gè)混合了多種產(chǎn)品、多狀態(tài)、多屬性的進(jìn)貨與銷售數(shù)據(jù)表，共使用了761條記錄，其次該數(shù)據(jù)表的數(shù)據(jù)日期分布很零散，需按年按季度來(lái)匯總統(tǒng)計(jì)。這樣的數(shù)據(jù)若使用圖表表達(dá)，就必須借助如圖14.3-2所示的數(shù)據(jù)透視表功能。否則，使用一般方法來(lái)處理數(shù)據(jù)將非常費(fèi)事，且很難快速制作出有價(jià)值的圖表。
圖14.3-1 數(shù)據(jù)透視圖案例原始數(shù)據(jù)
圖14.3-2 原始數(shù)據(jù)整理后的數(shù)據(jù)透視表
在使用原始數(shù)據(jù)來(lái)生成圖14.3-2的數(shù)據(jù)透視表時(shí)，該透視表的布局如圖14.3-3和圖14.3-4所示。當(dāng)生成透視表后，需選中透視表的“發(fā)生日期”字段，單擊鼠標(biāo)右鍵，在彈出菜單中：Excel 2003選“組及顯示明細(xì)數(shù)據(jù)”> “組合”；Excel 2007/2010選“創(chuàng)建組”。在彈出的對(duì)話框（如圖14.3-5所示）中，點(diǎn)選“季度”與“年”，Excel 2007/2010另需在“年”字段的設(shè)置“布局和打印”中勾選“以表格形式顯示項(xiàng)目標(biāo)簽”。
圖14.3-3 Excel 2003數(shù)據(jù)透視表布局
圖14.3-4 Excel 2007/2010數(shù)據(jù)透視表布局
圖14.3-5 字段分組設(shè)置對(duì)話框
由于數(shù)據(jù)透視表在處理該案例的“數(shù)量”和“金額”兩項(xiàng)求和項(xiàng)時(shí)，交互作業(yè)無(wú)法直接使用圖表上的交互按鈕來(lái)完成，必須借助“數(shù)據(jù)透視表字段列表”窗格來(lái)處理，因此這個(gè)案例中分別使用如圖14.3-6所示的兩個(gè)數(shù)據(jù)透視表來(lái)處理“數(shù)量”和“金額”兩項(xiàng)求和項(xiàng)。
圖14.3-6 原始數(shù)據(jù)整理后的最終數(shù)據(jù)透視表
注：兩個(gè)數(shù)據(jù)表間要保留足夠的間隔單元格，因?yàn)閿?shù)據(jù)透視圖的交互展示，是直接對(duì)數(shù)據(jù)透視表的操作，這種操作會(huì)導(dǎo)致單元格格式的變化，尤其是透視表間的間距太小時(shí)。
當(dāng)完成數(shù)據(jù)透視表的布局后，制作數(shù)據(jù)透視圖就變得非常簡(jiǎn)單。直接選中相應(yīng)的數(shù)據(jù)透視表，然后制作簇狀柱形圖，依次可生成如圖14.3-7所示的兩個(gè)數(shù)據(jù)透視圖，這兩個(gè)圖表的交互作業(yè)完全依賴于數(shù)據(jù)透視圖的交互按鈕。
圖14.3-7 最終數(shù)據(jù)透視圖
雖然此類數(shù)據(jù)透視圖表制作起來(lái)非常簡(jiǎn)單，但可視化效果并不好。以下列舉了一些數(shù)據(jù)透視圖表的局限，供讀者使用數(shù)據(jù)透視圖表時(shí)參考：
1．按鈕類元素所占的圖表面積太大且無(wú)法移動(dòng)位置，由于數(shù)據(jù)透視圖的數(shù)據(jù)信息一般都比較大，因此展示效果給人較強(qiáng)的局促感。
2．?dāng)?shù)據(jù)透視圖中圖表元素的格式化設(shè)置，往往隨交互按鈕的觸發(fā)操作而導(dǎo)致丟失，因?yàn)槊看蔚臄?shù)據(jù)交互，數(shù)據(jù)透視圖都進(jìn)行了再次完全重繪。
3．?dāng)?shù)據(jù)透視圖對(duì)組合類圖表的支持非常有限。有時(shí)在交互操作后，系列的圖表類型會(huì)發(fā)生變化，尤其是在改變圖表系列個(gè)數(shù)時(shí)。
數(shù)據(jù)透視表可以實(shí)現(xiàn)數(shù)據(jù)處理中經(jīng)常被提及的OLAP的多維分析操作，包括鉆?。―rill-down）、上卷（Roll-up）、切片（Slice）、切塊（Dice）以及旋轉(zhuǎn)（Pivot）。這是一組對(duì)普通人而言不知是何物的術(shù)語(yǔ)，為了更好地使大家理解這些概念，以下將以圖14.3-1的數(shù)據(jù)表展開(kāi)簡(jiǎn)單說(shuō)明。
名詞解釋：
■ OLAP（On-line Analytical Processing，聯(lián)機(jī)分析處理）：
基于大數(shù)據(jù)量、多指標(biāo)基礎(chǔ)上的各種數(shù)據(jù)整理與分析方式集合，簡(jiǎn)單理解就是：Excel中基于數(shù)據(jù)透視表的各類透視過(guò)程。對(duì)于復(fù)雜的大型數(shù)據(jù)集而言，Excel的處理能力不足應(yīng)付時(shí)，可以使用微軟專門基于SQL Server的工具集Microsoft SQL Server OLAP Services，該工具是為處理數(shù)據(jù)的OLAP應(yīng)用而生。
?數(shù)據(jù)立方體（Data Cube）：
其實(shí)就是需要分析的原始數(shù)據(jù)集合，此處圖片只是示意，實(shí)際的數(shù)據(jù)維度遠(yuǎn)不止3維。此處可以理解為圖14.3-1的數(shù)據(jù)表。
?鉆?。―rill-down）：
將匯總數(shù)據(jù)拆分成更細(xì)節(jié)的數(shù)據(jù)，比如圖14.3-2的透視表在未進(jìn)行分組時(shí)的日期狀態(tài)。
?旋轉(zhuǎn)（Pivot）：
即互換維的位置，簡(jiǎn)單而言就是行列轉(zhuǎn)換，Excel中的轉(zhuǎn)置功能即屬于此操作。如基于圖14.3-2的數(shù)據(jù)表透視表將“發(fā)生日期”和“品種”兩個(gè)字段進(jìn)行相應(yīng)的交換。
?切塊（Dice）：
選擇特定區(qū)間數(shù)據(jù)或者某特定指標(biāo)進(jìn)行分析，如選擇圖14.3-1數(shù)據(jù)表中所有產(chǎn)品上半年的銷售數(shù)量與銷售金額。
?切片（Slice）：
選取特定維度的數(shù)據(jù)進(jìn)行分析，如只選擇圖14.3-1數(shù)據(jù)表中各季度產(chǎn)品A的銷售數(shù)量數(shù)據(jù)。
?上卷（Roll-up）：
鉆取的逆操作，簡(jiǎn)單地理解就是Excel的分類匯總，如只將圖14.3-1數(shù)據(jù)表中日期銷售數(shù)量匯總為各年度數(shù)據(jù)。
這些概念也許顯得晦澀和難懂，但其實(shí)在制作交互式圖表的過(guò)程中，我們都有意或無(wú)意在使用這些操作方法，因?yàn)榻换ナ綀D表依賴這樣的數(shù)據(jù)操作。如果單純使用函數(shù)公式來(lái)處理如此龐雜的數(shù)據(jù)，將變得不具可操作性，因此借助Excel的數(shù)據(jù)透視表功能就顯得非常必要。隨著Excel的推陳出新，Excel在不斷加強(qiáng)數(shù)據(jù)透視表功能，在Excel 2010中，已經(jīng)添加了切片器功能，該功能基于數(shù)據(jù)透視表和SQL應(yīng)用。
圖14.3-7 透視圖表在操作上的不友好，導(dǎo)致我們必須要考慮一個(gè)非?，F(xiàn)實(shí)的問(wèn)題：如何借助常規(guī)圖表結(jié)合數(shù)據(jù)透視表來(lái)制作交互式圖表。有個(gè)非常令人困擾的問(wèn)題是：如果圖表引用的數(shù)據(jù)來(lái)源自Excel的數(shù)據(jù)透視表，該圖表會(huì)被Excel強(qiáng)制變?yōu)閿?shù)據(jù)透視圖。
Excel 2003中，可以先使用圖表向?qū)?lái)生成一個(gè)空白圖表，然后使用數(shù)據(jù)拖曳方式添加系列，但該方法并不非?？煽浚诓淮_定的狀況下，該圖表依然會(huì)被Excel自動(dòng)設(shè)置為數(shù)據(jù)透視圖。在Excel 2007和2010中則完全無(wú)望，拖曳功能也不再被支持。但并非沒(méi)有辦法來(lái)實(shí)現(xiàn)這樣的操作，借助定義名稱的方法，傳統(tǒng)Excel圖表和數(shù)據(jù)透視表可以被有機(jī)地結(jié)合在一起。圖14.3-8所示的案例，就是圖14.3-7的常規(guī)圖表演繹，該圖僅使用了一個(gè)圖表來(lái)展示“數(shù)量”和“金額”兩項(xiàng)求和項(xiàng)。
圖14.3-8 數(shù)據(jù)透視表和定義名稱結(jié)合的案例
這個(gè)圖表的制作過(guò)程基于圖14.3-7案例數(shù)據(jù)透視表，通過(guò)使用4個(gè)數(shù)據(jù)透視表來(lái)實(shí)現(xiàn)，這些透視表和圖14.3-3的結(jié)構(gòu)基本相同。制作方法說(shuō)明如下：
1．參照?qǐng)D14.3-6數(shù)據(jù)透視表的生成過(guò)程，生成如圖14.3-9所示的4個(gè)數(shù)據(jù)透視表。
圖14.3-9 圖14.3-8案例4個(gè)數(shù)據(jù)透視表
2．新增“類別”、“狀態(tài)”兩個(gè)輔助列，相關(guān)內(nèi)容參照?qǐng)D14.3-8設(shè)置即可。再依次新增4個(gè)觸發(fā)鏈接列“狀態(tài)”、“類別”、“年度”、“分類”，除“分類”為5個(gè)單元格外，其余皆為1個(gè)單元格。
3．分別添加以下工作表控件：兩個(gè)組合框、兩個(gè)分組框、3個(gè)選項(xiàng)按鈕、5個(gè)復(fù)選框。分組框并不參與觸發(fā)作業(yè)，僅為視覺(jué)分組，排布和外觀設(shè)定參照?qǐng)D14.3-8設(shè)置即可，數(shù)據(jù)源區(qū)域和單元格鏈接從上到下依次為：
1.組合框1中數(shù)據(jù)源區(qū)域?yàn)?C$5:$C$6，單元格鏈接為$F$6；
2.組合框2中數(shù)據(jù)源區(qū)域?yàn)?D$5:$D$6，單元格鏈接為$G$6；
3.3個(gè)選項(xiàng)按鈕的單元格鏈接為$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個(gè)定義名稱，公式如下所示：
5．生成無(wú)數(shù)據(jù)的簇狀柱形圖，依次添加6個(gè)圖表系列，這6個(gè)圖表系列的SERIES公式依次為：
6．將系列6“刻度”的圖表類型改為XY散點(diǎn)圖，并將圖表按照?qǐng)D14.3-8右側(cè)圖表樣式美化即可。
圖14.3-8案例圖表是為了學(xué)習(xí)如何將數(shù)據(jù)透視表和定義名稱結(jié)合，以及如何使用選項(xiàng)按鈕和復(fù)選框工作表控件。該案例圖表僅比直接使用數(shù)據(jù)透視圖的可視化效果好，但并不是一個(gè)值得稱道的圖表。
小技巧
交互式圖表動(dòng)態(tài)設(shè)置數(shù)值坐標(biāo)刻度，使其保持一致。
由于涉及“金額”和“數(shù)量”兩個(gè)不同量綱，圖表的數(shù)值坐標(biāo)無(wú)法直接手工設(shè)定為統(tǒng)一刻度，圖14.3-8的系列6“刻度”即是為保證刻度的一致性而設(shè)（圖表中并未隱去，實(shí)際作業(yè)中可設(shè)置其為無(wú)標(biāo)記點(diǎn)）。
注：本章所有的案例，均對(duì)圖表的數(shù)值坐標(biāo)進(jìn)行了統(tǒng)一設(shè)置。
文檔14.3-2是圖14.3-8案例使用列表框工作表控件的復(fù)選選項(xiàng)實(shí)例，文檔中獲取復(fù)選數(shù)值使用了xlm4.0宏表函數(shù)。該文檔僅限在Excel 2003中使用，在Excel 2007/2010中已不可使用，因?yàn)樵摴δ芤呀?jīng)不被支持，若需使用應(yīng)借助VBA來(lái)處理。
聯(lián)系客服
