OFFICE能力提升課程百變數據透視表制作：蔡曙東2014年4月根據盧景德《EXCEL2007數據透視表應用大全》改編制作初識數據透視表什么是數據透視表呢？數據透視表其實就是基于數據源記錄表根據不同需要生成的不同報表。記錄表是用來記錄原始數據的，而數據透視表則是用來分類匯總的分析報表。在開始介紹數據透視表之前，先來仔細看一看后面的二張銷售記錄表，請大家分析思考一下，這兩張表你認為哪一張表做得更好、更合理？如果是你做記錄表，你會做成哪一種形式？數據透視表的用途—記錄表A數據透視表的用途—記錄表B數據透視表的用途數據透視表是一種可以快速匯總、分析大量數據表格的交互式工具。使用數據透視表可以按照數據表格的不同字段從多個角度進行透視，并建立交叉表格，用以查看數據表格不同層面的匯總信息、分析結果以及摘要數據。使用數據透視表可以深入分析數值數據，以幫助用戶發現關鍵數據，并做出有關企業中關鍵數據的決策。數據透視表的用途以友好的方式，查看大量的數據表格。對數值數據快速分類匯總，按分類和子分類查看數據信息。展開或折疊所關注的數據，快速查看摘要數據的明細信息。建立交叉表格（將行移動到列或將列移動到行），以查看源數據的不同匯總。快速的計算數值數據的匯總信息、差異、個體占總體的百分比信息等。對數據源（記錄表）的要求若要創建數據透視表，要求數據源必須是比較規則的數據，也只有比較大量的數據才能體現數據透視表的優勢。如：表格的第一行是字段名稱，字段名稱不能為空；數據記錄中最好不要有空白單元格或合并單元格；每個字段中數據的數據類型必須一致（如，“訂單日期”字段的值即有日期型數據又有文本型數據，則無法按照“訂單日期”字段進行組合）。數據越規則，數據透視表使用起來越方便。正確的數據源記錄表數據透視表的作用前面這張表大家覺得怎么樣，看起是不是很累？我們怎樣才能方便地找出表中的下列信息呢？1.每種產品銷售金額的總計是多少？2.每個地區的銷售金額總計是多少？3.每個城市的銷售金額總計是多少？4.每個雇員的銷售金額總計是多少？5.每個城市中每種產品的銷售金額合計是多少？……數據透視表能幫你解決這些問題！選擇數據源中任意有內容的單元格或整個數據區域。選擇“插入”選項卡。單擊“數據透視表”命令。創建數據透視表132創建數據透視表選擇要分析的數據—自動選中，也可人為選擇區域。選擇放置數據透視表位置。單擊確定。54選擇“新工作表”時系統會自動建立1張新表；選擇“現有工作表”時，需要在當前工作表中選擇放置數據透視表的位置。6創建數據透視表OK！一張空的數據透視表就這樣建好了！可這是空的呀，怎么往里添加數據呢？往下看！數據透視表字段的使用將字段拖動到“行標簽”區域，則此字段中的每類項目會成為一行；我們可以將希望按行顯示的字段拖動到這個區域。將字段拖動到“列字段”區域，則此字段種的每類項目會成為列；我們可以將希望按列顯示的字段拖動到此區域。將字段拖動到“數值”區域，則會自動計算此字段的匯總信息（如求和、計數、平均值、方差等等）；我們可以將任何希望匯總的字段拖動到此區域。將字段拖動到“報表篩選”區域，則可以根據此字段對報表實現篩選，可以顯示每類項目相關的報表。我們可以將較大范圍的分類拖動到此區域，以實現報表篩選。僅使用行標簽的簡單數據透視表數值金額求和行標簽產品名稱交叉數據透視表（行標簽+列標簽）行標簽列標簽如果將不同字段分別拖動到行標簽區域和列標簽區域，就可以很方便的創建交叉表格。篩選字段的使用將地區作為篩選字段，產品名稱作為行標簽、城市作為列標簽，生成的數據透視表如下圖篩選字段選擇不同的地區看看結果如何？使用數據透視表查看摘要與明細信息要查看每種產品由不同雇員的銷售情況。其方法：將“雇員”字段拖到行標簽區域的“產品名稱”的下面。通過點擊“+”/“-”來展開/折疊明細信息。使用數據透視表查看摘要與明細信息如果要想查看下表中白奶酪在各城市的銷售信息，只需要雙擊白奶酪在各城市銷售的總計數（Z14單元格）1雙擊使用數據透視表查看摘要與明細信息系統將生成一張新的工作表，此工作表詳細地列明了白奶酪在各個城市銷售的信息，這張表類似于篩選操作，這種透視表的優點在于可以單獨保存并不改源數據表的顯示。2生成明細表組合數據—組合日期數據下圖是按日期列出的銷售信息，這樣列出的數據太多，看著很累，如果想要按年匯總列出，應該怎么做呢？1.單擊訂單日期任一單元格2.選擇數據透視表工具的選項卡3.單擊將字段分組工具4.選中月、季度、年5.單擊確定組合數據—組合日期數據此時數據透視表就按年、季度、月分組匯總（如下圖）。注意：如果僅想按月組合時，在分組對話框的步長選項中必須同時選中年，否則數據透視表將會把不同年份的相同月份匯總到同一個組！數值數據的分段組合再試一下按單筆銷售金額大小組合數據。先以金額同時作為行標簽和求和項建立一個數據透視表。然后按下圖步驟進行組合。1.單擊行標簽字段中的任一單元格2.選擇數據透視表工具的選項卡3.單擊將字段分組工具4.設置起始為0，步長為10005.單擊確定數值數據的分段組合下圖即是按單筆銷售金額的大小組合匯總的數據。注意圖中0—1000中的1000實際意義是<1000，而1000—2000中的1000是=1000！對文本字段的分類組合除了可以對日期字段、數值字段的組合，還可以對文本類字段進行組合分類。如下圖，將產品名稱進行分類。1.按住Ctrl鍵，同時選擇多個需要組合為同一類的產品名稱2.選擇數據透視表工具的選項卡3.單擊將所選內容分組工具對文本字段的分類組合按前圖所述操作后，數據透視表顯示為下圖所示。此時的組合結果命名為數據組1，數據組2……，可以直接在單元格內將名字改為產品分類名字即可，如谷類、點心等……數據透視表的數據匯總方式數據透視表的優勢在于，我們可以很方便的從不同的角度，對數據進行不同方式的匯總統計。前面我們創建的數據透視表都是以求和的方式計算金額合計。那么當我們希望匯總的信息不是求和，而是計算平均值或者計數那么該如何處理呢？改變數據匯總方式比如，我們希望統計每種產品被銷售的次數。可以將“產品名稱”字段分別拖拽到“行標簽”區域和“數值”區域，由于“產品名稱”字段的內容是文本型數據，當把其拖拽到“數值”區域中時，匯總方式自動變為計數。如圖所示：可直接在單元格中改為“銷售次數”改變數據匯總方式如果我們希望查看每種產品的平均單價呢？這時照樣還是將“產品名稱”字段拖拽到“行標簽”區域，而將“單價”字段拖拽到“數值”區域，由于“單價”字段的數據類型是數值型，匯總方式會自動為求和。而我們所希望的是計算每種產品的平均單價，那么該如何將匯總方式從求和改為平均值呢？2.點擊數據匯總依據1.右鍵單擊字段名單價單元格3.選擇平均值改變數據匯總方式在匯總方式中有一共有11種函數，包括：求和、計數、數值計數、平均值、最大值、最小值、乘積、標準偏差、總體標準偏差、方差、總體方差。具體功能如下表：?匯總方式功能求和對數值求和。是數字數據的默認函數。相當于Sum函數計數數據值的個數。作用與COUNTA工作表函數相同。“計數”匯總是數據（數字除外）的默認函數平均值求數值平均值。相當于AVERAGE函數最大值求最大值。相當于MAX函數最小值求最小值。相當于MIN函數?乘積求數值的乘積。相當于PRODUCT函數數值計數數字數據值的個數。用與COUNT工作表函數相同。標準偏差估算總體的標準偏差，樣本為總體的子集。相當于STDEV函數總體標準偏差計算總體的標準偏差。匯總的所有數據為總體。相當于STDEVP函數方差估計總體方差，樣本為總體的子集。相當于VAR函數總體方差計算總體的方差。匯總的所有數據為總體。相當于VARP函數改變數據透視表的值顯示方式通過改變數據透視表的值顯示方式，可以幫助我們對數據按照不同顯示方式進行比較。值顯示方式具體含義如下表：?值顯示方式結果差異顯示的值為與“基本字段”中“基本項”值的差。百分比顯示的值為“基本字段”中“基本項”值的百分比。差異百分比顯示的值為與“基本字段”中“基本項”值的百分比差值。按某一字段匯總顯示的值為按照“基本字段”中的值連續累加的匯總。占同行數據總和的百分比顯示每一行或每個類別的值相對于該行或該類別總計的百分比。占同列數據總和的百分比顯示每一列或每個系列的所有值為該列或該系列總計的百分比。占總和的百分比顯示的值為報表中所有值或所有數據點的總計的百分比。指數按下式計算值：((單元格中值)x(總計))/((行總計)x(列總計))改變數據透視表的值顯示方式例如，要將求和項：金額數值顯示為百分比數據的操作步驟如下圖所示：2.點擊數據透視表工具中的選項卡1.選中求和項：金額數據中的任一單元格3.點擊字段設置5.選擇6.單擊確定4.點值顯示方式選項卡改變數據透視表的值顯示方式如下圖，即是將金額的顯示方式改變為占同行數據總和（本產品在各地區銷售總額）的百分比。改變數據透視表的值顯示方式假如我們突然改變主意，要想把銷售金額顯示為占同列數據總和（本地區各種產品銷售總額）的百分比時，只需要按下圖改變“值顯示方式”的選項即可，其他操作與前面相同。改變選項在數據透視表中顯示多個計算字段如果我們希望同時查看計算字段的百分比和金額合計能不能做到呢？答案很簡單，只需要在字段列表中將“金額”字段再次拖到數值區域就可以了。此時數值區域添加了一個“金額2”，列標簽區自動添加了一個“數值”字段。在數據透視表中顯示多個計算字段前面的操作結果百分比和合計數是橫向排列的；如果將數值字段從列標簽區域拖動到行標簽區域，就可以將百分比和合計數變成上下縱向排列了。注意：將數值字段拖放到產品名稱上方和下方顯示的結果是不一樣的請自行拖放查看效果。不同匯總方式的數據比較用前面學過的方法，以訂單日期作為行標簽、以雇員作為列標簽，建立一張透視表。并將訂單日期按年、月進行組合。不同匯總方式的數據比較再次把金額字段拖到數值區域，并將字段名稱分別改為當月發生額和金額累計。將匯總方式改為按某一字段匯總，基本字段選擇訂單日期。創建數據透視表公式在使用數據透視報表的時候，如果匯總函數和自定義計算（值顯示方式）沒有提供所需的結果，則可在計算字段或計算項中創建自己的公式。先了解什么是字段、項、計算字段和計算項：字段：數據表中的每一列，我們稱之為字段。如：地區、城市、產品名稱、金額等..項：字段里面的每個值，我們稱之為項。如：地區字段中的東北、華北、西北；城市字段中的北京、上海、廣州都稱之為項。計算字段：數據透視表中的字段，該字段使用用戶創建的公式。計算字段可使用數據透視表中其他字段中的內容執行計算。計算項：數據透視表字段中的項，該項使用用戶創建的公式。計算項使用數據透視表中相同字段的其他項的內容進行計算。在數據透視表中使用計算字段先建立每月金額合計的透視表，然后按下圖步驟操作：2.點擊數據透視表工具中的選項卡1.選中數據透視表中的任一單元格3.點擊公式4.點擊計算字段5.輸入“稅金”6.輸入公式8.確定7.添加在數據透視表中使用計算字段將計算字段“稅金”添加到數值區域，數據透視表中就增加了一列“求和項:稅金”，如下圖：在數據透視表中使用計算項下圖中顯示的是每個雇員銷售每種產品的金額匯總表。那么能否通過數據透視表計算雇員銷量的平均水平是多少呢？我們可以通過數據透視表計算項來實現。先在“雇員”字段中添加一項名為“雇員平均水平”的項。具體操作步驟如下圖：1.先將光標定位在雇員字段2.點擊數據透視表工具中的選項卡3.點擊公式4.點擊計算項在數據透視表中使用計算項在對話框的名稱位置輸入“雇員平均水平”。在公式位置輸入：“=AVERAGE(張穎,王偉,李芳,鄭建杰,趙軍,孫林,金士鵬,劉英玫,張雪眉)”。單擊添加并確定。5.輸入“雇員平均水平”6.輸入公式8.確定7.添加在數據透視表中使用計算項“雇員平均水平”項即添加到數據透視表的“雇員”字段中。刪除添加的計算字段或計算項若要刪除添加的計算字段或計算項，在計算字段或計算字段對話框的名稱中，選擇要刪除的字段，單擊對話框右側的刪除按鈕即可。2.選擇需刪除的計算字段3.點刪除1.點擊右側三角4.點確認2.選擇需刪除的計算項3.點刪除1.點擊右側三角4.點確認刪除計算字段刪除計算項利用計算項進行比較想要查看每個雇員與雇員平均水平的差異，可以通過更改值顯示方式來實現。2.點擊數據透視表工具中的選項卡1.把光標定位在值字段3.點擊字段設置4.點擊值顯示方式5.基本字段選擇“雇員”6.基本項選擇“雇員平均水平”7.確定利用計算項進行比較下圖即是每個雇員與雇員平均水平差異比較的透視表。數據透視表的數據源控制與刷新我們可以使用數據透視表功能，在數據源的基礎上生成各種不同的報表，以滿足不同層次的需要。但當我們使用數據透視表生成報告后，如果數據源變化了，數據透視表報告的信息會不會自動跟著變化呢？當更改數據源后，默認情況下數據透視表并不會自動更新。當數據源的值更改過以后，我們可以通過數據透視表的“選項”工具面板里的“數據”組的“刷新”命令來刷新數據透視表。這樣數據透視表報告就變成最新的了。但是“刷新”命令只是刷新數據透視表所引用的數據源的值。當數據源的范圍變化了以后（比如數據源增加了新的紀錄）則使用刷新命令就不能將數據范圍一起更新了。我們可以通過數據透視表的“選項”工具面板里的“數據”組的“更改數據源”命令來重新選擇數據透視表所引用的數據范圍。數據源數值改變后的更新2.點擊數據透視表工具中的選項卡1.把光標定位在值字段3.點擊刷新下拉箭頭注意：用刷新更新的數據透視表只能用在源數據表只更改了數據的值，而未增加新的記錄時用不用，當數據表增加新的記錄后需要更新時的方法按下一頁操作。4.點擊刷新工具數據源增加新的記錄的更新2.點擊數據透視表工具中的選項卡1.把光標定位在值字段3.點擊更改數據源右側箭頭4.點擊更改數據源5.選中包含新增加記錄的源數據表區域6.點確認