馬竹青

摘要：利用Excel數(shù)據(jù)透視表或數(shù)據(jù)透視圖這類數(shù)據(jù)分析工具實現(xiàn)數(shù)據(jù)分析，是一種易學、簡捷的平臺，也是快速分析數(shù)據(jù)的工具，隨著Excel版本更新，將具有更強大的數(shù)據(jù)分析能力。

關(guān)鍵詞：數(shù)據(jù)分析；數(shù)據(jù)透視表

中圖分類號：TP37 文獻標識碼：A 文章編號：1009-3044（2015）21-0058-02

數(shù)據(jù)透視表是Excel平臺上一種交互式報表，也是對大量數(shù)據(jù)快速匯總、建立交叉列表的交互式動態(tài)表格，幫助用戶更好地分析與組織數(shù)據(jù)，快速排序、匯總、篩選，實現(xiàn)數(shù)據(jù)的計算和統(tǒng)計，并按照數(shù)據(jù)表格的不同字段從多個視角進行透視；特點是從大量無關(guān)聯(lián)的數(shù)據(jù)中尋找內(nèi)在的聯(lián)系，以體現(xiàn)“透視”表格的能力，數(shù)據(jù)透視表的操作便捷快速，只需要拖拽鼠標，就可以獲得不同的報表，使復(fù)雜問題簡單化，提高工作效率，數(shù)據(jù)透視表是Excel強大功能之一。使用數(shù)據(jù)透視表可以深入分析數(shù)值數(shù)據(jù)，以幫助用戶挖掘關(guān)鍵數(shù)據(jù)，做出企業(yè)的決策。

1 數(shù)據(jù)透視表

1.1數(shù)據(jù)透視表的數(shù)據(jù)源

若要創(chuàng)建數(shù)據(jù)透視表，要求數(shù)據(jù)源是較規(guī)則的數(shù)據(jù)，例如，表格的第一行是字段名稱，字段名稱不能為空；數(shù)據(jù)中最好不要有空白單元格或布局里不要有合并單元格；每個字段中數(shù)據(jù)的數(shù)據(jù)類型必須一致，數(shù)據(jù)越規(guī)則，數(shù)據(jù)透視表使用起來越方便。如果數(shù)據(jù)量較大時使用更能體現(xiàn)數(shù)據(jù)透視表的優(yōu)勢。

數(shù)據(jù)源可以來自于Excel工作簿，也可以是其他格式的數(shù)據(jù)源，例如，Access數(shù)據(jù)庫、SQL Sever數(shù)據(jù)庫、純文本文件、OLAP聯(lián)機事務(wù)處理等，如圖1所示。

1.2創(chuàng)建數(shù)據(jù)透視表

數(shù)據(jù)透視表的格局類似于三維空間的立體效應(yīng)，如圖2所示，它可以動態(tài)地改變其版面布局，以便按照不同模式分析數(shù)據(jù)，也可以重新安排行標簽、列標簽和頁字段（報表篩選）。每一次改變字段位置時，數(shù)據(jù)透視表會立即重新統(tǒng)計匯總數(shù)據(jù)。另外，如果原始數(shù)據(jù)發(fā)生更改，也可以更新數(shù)據(jù)透視表來反映變化。

數(shù)據(jù)透視表字段列表有四個區(qū)域：

報表篩選：添加字段到報表篩選區(qū)可以使該字段包含在數(shù)據(jù)透視表的篩選區(qū)域中，以便對其獨特的數(shù)據(jù)項進行篩選；

列標簽：添加一個字段到“列標簽”區(qū)域可以在數(shù)據(jù)透視表頂部顯示來自該字段的獨特的值；

行標簽：添加一個字段到“行標簽”區(qū)域可以在數(shù)據(jù)透視表左邊的整個區(qū)域顯示來自該字段的獨特的值；

數(shù)值：添加一個字段到“數(shù)值”區(qū)域，可以使該字段包含在數(shù)據(jù)透視表的數(shù)值區(qū)域中，并使用該字段中的值進行指定的計算，例如，求和、計數(shù)、平均值、最大值等。

若將字段拖動到“行標簽”區(qū)域，則此字段中的每類項目會成為一行；若將字段拖動到“列字段”區(qū)域，則此字段種的每類項目會成為一列；若將字段拖動到“數(shù)值”區(qū)域，則會自動計算此字段的匯總信息；若將字段拖動到“報表篩選”區(qū)域，則可以根據(jù)此字段對報表實現(xiàn)篩選，也可以理解為水平面方向截取不同截面獲取不同統(tǒng)計結(jié)果，一般就將較大范圍的分類拖動到此區(qū)域，以實現(xiàn)報表篩選，如圖3所示。

1.3數(shù)據(jù)透視表的布局

Excel 2013提供了3種布局樣式，新樣式“壓縮布局”是用戶的數(shù)據(jù)透視表的默認樣式：

以壓縮形式顯示：默認情況下所有數(shù)據(jù)透視表都使用壓縮布局，在這種布局中，行標簽區(qū)域中的多個字段擠壓到一列中，壓縮形式適合使用“展開”和“折疊”圖標；默認情況下匯總顯示在每組的頂部；

以大綱形式顯示：此顯示使最外層的行字段填充第一列，其他的字段占據(jù)第二、三列等。若希望將數(shù)據(jù)透視表的數(shù)值復(fù)制到新的位置，這種布局比較合適；默認情況下匯總顯示在每組的頂部；

以表格形式布局：是傳統(tǒng)的顯示方式，使用分類匯總出現(xiàn)在組的底部，若希望在后續(xù)分析中得到的匯總數(shù)據(jù)，表格形式的布局也許是最好的布局。

1.4數(shù)據(jù)透視表的值顯示方式

默認的值顯示方式是“普通”，Excel還提供了更多的顯示方式，有：差異、百分比、差異百分比、按某一字段匯總、占同行數(shù)據(jù)總和的百分比、占同列數(shù)據(jù)總和的百分比、占總和的百分比、指數(shù)等。

2 數(shù)據(jù)透視表的操作

2.1 一般操作

1）數(shù)據(jù)透視表的格式化

為了修飾與美化數(shù)據(jù)透視表，即對字體、邊框、填充等設(shè)置格式，方法類似于工作表單元格格式化的設(shè)置，包含自動套用格式、條件格式等，同時，Excel數(shù)據(jù)透視表工具也提供了其他方法來實現(xiàn)格式化，例如，在“設(shè)計”選項卡的“數(shù)據(jù)透視表樣式”組；

2）數(shù)據(jù)透視表的排序

數(shù)據(jù)透視表具有排序功能，分：升序、降序、手動拖曳方法，數(shù)字和字母排序一般是按ASCII碼的大小來排序；漢字默認情況下按拼音排序，也可以設(shè)置按筆畫排序；若以上排序規(guī)則達不到需要，可以使用手動排序，達到所需要的排序結(jié)果。

3）數(shù)據(jù)透視表的刷新

更新數(shù)據(jù)透視表或數(shù)據(jù)透視圖中的內(nèi)容以便反映基本源數(shù)據(jù)改變而帶來變化。

4）數(shù)據(jù)透視表的復(fù)制或移動

若需要使用同一數(shù)據(jù)源創(chuàng)建另一個數(shù)據(jù)透視表，只需要復(fù)制第一個生成的數(shù)據(jù)透視表即可，免去從頭開始創(chuàng)建的一系列操作，以提高效率；另外可以在同一工作表中復(fù)制多個數(shù)據(jù)透視表，實現(xiàn)數(shù)據(jù)比較和對照等。

5）數(shù)據(jù)透視表的分組

Excel通過對數(shù)字、日期、文本等不同數(shù)據(jù)類型的數(shù)據(jù)項提供了多種組合方式，極大增加了數(shù)據(jù)透視表分類匯總的靈活性。

6）數(shù)據(jù)透視表的多個分類匯總項

若需要對同類數(shù)據(jù)實現(xiàn)不同的匯總方式，可以利用“字段設(shè)置”對話框的自定義功能實現(xiàn)，以便獲得同類數(shù)據(jù)的平均值、總和、最大值等。

7）數(shù)據(jù)再透視

將數(shù)據(jù)源先按照大的分類字段單獨創(chuàng)建數(shù)據(jù)透視表，然后將各個分類字段的數(shù)據(jù)透視表作為數(shù)據(jù)源再創(chuàng)建一個多重合并計算數(shù)據(jù)區(qū)域的數(shù)據(jù)透視表，以提供不同數(shù)據(jù)匯總結(jié)果。

2.2整理數(shù)據(jù)透視表字段

1）重命名字段

當建立數(shù)據(jù)透視表時，所建立的字段名稱被Excel改名了，類似于“銷售總額”修改為“求和項：銷售總額”或“平均值項：銷售總額”，使列寬增加，影響表格美觀。如果用戶需要修改為“銷售總額”，會失敗的，因為它已經(jīng)是數(shù)據(jù)的字段名稱，只要略有不同就可以，方法是單擊此單元格，輸入“銷量總額”，依次修改其他字段名稱。

2）刪除字段

對不需要在數(shù)據(jù)透視表中顯示的字段，可以選中字段名稱，鼠標右擊，在打開的快捷菜單中選擇“刪除‘字段名”命令即可實現(xiàn)刪除該字段。

3）隱藏字段標題

若不需要顯示字段標題，可以通過“數(shù)據(jù)透視表工具”的“選項”選項卡里的“字段標題”命令實現(xiàn)。

4）折疊與展開字段

通過“數(shù)據(jù)透視表工具”的“選項”選項卡的“活動字段”組里的“展開整個字段”或“折疊整個字段”命令實現(xiàn)。

3 數(shù)據(jù)透視表的計算

數(shù)據(jù)透視表一旦建立后，不允許手工更改，例如，插入新的單元格，或者添加公式計算。實際上，如果增加了計算功能，數(shù)據(jù)透視表將具有更強的功能。在Excel提供了計算字段和計算項來實現(xiàn)。

3.1計算字段和計算項

計算字段是通過對數(shù)據(jù)透視表中現(xiàn)有的字段執(zhí)行計算后得到的新字段，該字段使用用戶創(chuàng)建的公式。計算字段可使用數(shù)據(jù)透視表中其他字段中的數(shù)據(jù)計算而生成。公式中不僅可以實現(xiàn)加、減、乘、除簡單運算，還可以使用SUM、IF、AND、NOT、OR、COUNT、AVERAGE、TEXT等函數(shù)的復(fù)雜運算，但是不能使用單元格引用和名稱。

計算項是在數(shù)據(jù)透視表的現(xiàn)有字段中插入新的項，通過對該字段的其他項執(zhí)行計算后得到該項的值。

二者區(qū)別：計算字段是對不同列已有字段計算而添加的新字段；而計算項是對同一列中不同行的數(shù)據(jù)計算而添加的新項，即在同一字段中，不同項之間的運算而產(chǎn)生。

3.2數(shù)據(jù)透視表函數(shù)

數(shù)據(jù)透視表函數(shù)用于檢索存儲在數(shù)據(jù)透視表中的匯總數(shù)據(jù)。

語法：GETPIVOTDATA（data_field，pivot_table，field1，item1，field2，item2，...），其中：

Data_field ：包含要檢索的數(shù)據(jù)的字段名稱，用引號括起來；

Pivot_table ：用于決定哪個數(shù)據(jù)透視表含有要檢索的數(shù)據(jù)，一般是數(shù)據(jù)透視表的左上角單元格地址；

Field1， Item1， Field2， Item2 ：用于描述要檢索數(shù)據(jù)的字段名稱和項名稱，用引號引起來，可以按任何順序排列，可以使用1 到 126 對。例如，"[產(chǎn)品]"，"[產(chǎn)品].[所有產(chǎn)品].[食品].[烤制食品]"

通俗含義如圖4所示：3.3數(shù)據(jù)透視表的動態(tài)功能。

當生成數(shù)據(jù)透視表的數(shù)據(jù)源區(qū)域發(fā)生變化，例如，增刪行或列的數(shù)據(jù)時，只要是數(shù)據(jù)區(qū)域連續(xù)的增刪，也希望數(shù)據(jù)透視表隨著新的數(shù)據(jù)源區(qū)域變化而變化，這就需要使用動態(tài)技術(shù)。這種技術(shù)就是利用函數(shù)定義名稱，由名稱代替改變的數(shù)據(jù)源區(qū)域。

4 數(shù)據(jù)分析案例

這是一個公司2011年1至6月份各部門采暖費、電費、水費等項目使用情況數(shù)據(jù)表，如圖5所示是部分數(shù)據(jù)，針對這個數(shù)據(jù)表，進行各部門費用使用情況分析，了解變化趨勢等。

圖5

我們需要創(chuàng)建兩個數(shù)據(jù)透視圖和相關(guān)切片器，如圖6所示，通過切片器選擇單個或多個費用或部門，以獲取不同費用在不同部門的情況、變化趨勢或結(jié)構(gòu)。

圖6

利用數(shù)據(jù)透視表和數(shù)據(jù)透視圖實現(xiàn)數(shù)據(jù)分析，方便簡捷，易于掌握，對初學者更加容易上手。（由中國青年政治學院資助）