1、1EXCEL 之之 數(shù)數(shù) 據(jù)據(jù) 透透 視視 表表從入門到精通從入門到精通2013年02月25日信息系統(tǒng)信息系統(tǒng) 第八期第八期 培訓課程培訓課程2產(chǎn)品銷售記錄單產(chǎn)品銷售記錄單我們從中可得到到些什么數(shù)據(jù)？我們從中可得到到些什么數(shù)據(jù)？3為什么使用數(shù)據(jù)透視表？“產(chǎn)品銷售記錄單產(chǎn)品銷售記錄單”記錄的是 2006 和 2007 年2008 年某公司訂單銷售情況的表格。其中包括訂單銷售日期，訂單編號，地區(qū)，城市，產(chǎn)品名稱，單價，數(shù)量，金額，銷售人員。我們希望根據(jù)此表快速計算出如下匯總信息：1.每種產(chǎn)品銷售金額的總計是多少？2.每個地區(qū)的銷售金額總計是多少？3.每個城市的銷售金額總計是多少？4.每個雇員的銷售

2、金額總計是多少？5.每個城市中每種產(chǎn)品的銷售金額合計是多少？.諸多的問題，使用數(shù)據(jù)透視表可以輕松解決。4什么是數(shù)據(jù)透視表? 1、數(shù)據(jù)透視表是一種可以快速匯總、分析大量數(shù)據(jù)表格的交互式工具。2、使用數(shù)據(jù)透視表可以按照數(shù)據(jù)表格的不同字段從多個角度進行透視，并建立交叉表格，用以查看數(shù)據(jù)表格不同層面的匯總信息、分析結果以及摘要數(shù)據(jù)。3、使用數(shù)據(jù)透視表可以深入分析數(shù)值數(shù)據(jù)，以幫助用戶發(fā)現(xiàn)關鍵數(shù)據(jù)，并做出有關企業(yè)中關鍵數(shù)據(jù)的決策。5數(shù)據(jù)透視表是針對以下用途特別設計的1. 以友好的方式，查看大量的數(shù)據(jù)表格。2. 對數(shù)值數(shù)據(jù)快速分類匯總，按分類和子分類查看數(shù)據(jù)信息。3. 展開或折疊所關注的數(shù)據(jù)，快速查看摘要數(shù)

3、據(jù)的明細信息。4. 建立交叉表格（將行移動到列或將列移動到行），以查看源數(shù)據(jù)的不同匯總。5. 快速的計算數(shù)值數(shù)據(jù)的匯總信息、差異、個體占總體的百分比信息等。6數(shù)據(jù)透視表對數(shù)據(jù)源的要求若要創(chuàng)建數(shù)據(jù)透視表，要求數(shù)據(jù)源必須是比較規(guī)則的數(shù)據(jù)，也只有比較大量的數(shù)據(jù)才能體現(xiàn)數(shù)據(jù)透視表的優(yōu)勢1. 表格的第一行是字段名稱，字段名稱不能為空2. 數(shù)據(jù)記錄中最好不要有空白單元格或各并單元格3. 一字段只能包含一個信息。4. 每個字段中數(shù)據(jù)的數(shù)據(jù)類型必須一致（如，“訂單日期”字段的值即有日期型數(shù)據(jù)又有文本型數(shù)據(jù)，則無法按照“訂單日期”字段進行組合）數(shù)據(jù)越規(guī)則，數(shù)據(jù)透視表使用起來越方便。7以下數(shù)據(jù)是否滿足創(chuàng)建數(shù)據(jù)要求

4、 ?8以下數(shù)據(jù)是否滿足創(chuàng)建數(shù)據(jù)要求 ?9創(chuàng)建數(shù)據(jù)透視表盡管數(shù)據(jù)透視表的功能非常強大，但是創(chuàng)建的過程卻是非常簡單。1. 將光標點在表格數(shù)據(jù)源中任意有內容的單元格，或者將整個數(shù)據(jù)區(qū)域選中。2. 選擇“插入”選項卡，單擊“數(shù)據(jù)透視表”命令。103、在彈出的“創(chuàng)建數(shù)據(jù)透視表”對話框中，“請選擇要分析的數(shù)據(jù)”一項已經(jīng)自動選中了光標所處位置的整個連續(xù)數(shù)據(jù)區(qū)域，也可以在此對話框中重新選擇想要分析的數(shù)據(jù)區(qū)域（還可以使用外部數(shù)據(jù)源，請參閱后面內容）。“選擇放置數(shù)據(jù)透視表位置”項，可以在新的工作表中創(chuàng)建數(shù)據(jù)透視表，也可以將數(shù)據(jù)透視表放置在當前的某個工作表中。創(chuàng)建數(shù)據(jù)透視表114. 單擊確定。Excel 自動創(chuàng)建了

5、一個空的數(shù)據(jù)透視表。創(chuàng)建數(shù)據(jù)透視表12上圖中左邊為數(shù)據(jù)透視表的報表生成區(qū)域，會隨著選擇的字段不同而自動更新；右側為數(shù)據(jù)透視表字段列表。創(chuàng)建數(shù)據(jù)透視表后，可以使用數(shù)據(jù)透視表字段列表來添加字段。如果要更改數(shù)據(jù)透視表，可以使用該字段列表來重新排列和刪除字段。默認情況下，數(shù)據(jù)透視表字段列表顯示兩部分：上方的字段部分用于添加和刪除字段，下方的布局部分用于重新排列和重新定位字段。右下方為數(shù)據(jù)透視表的 4 個區(qū)域，其中“報表篩選”、“列標簽”、“行標簽”區(qū)域用于放置分類字段，“數(shù)值”區(qū)域放置數(shù)據(jù)匯總字段。當將字段拖動到數(shù)據(jù)透視表區(qū)域中時，左側會自動生成數(shù)據(jù)透視表報表。創(chuàng)建數(shù)據(jù)透視表報表篩選行標簽數(shù)值列標簽1

6、3我們來解決前面提到的第一個問題。每種產(chǎn)品銷售金額的總計是多少？只需要在數(shù)據(jù)透視表字段列表中選中“產(chǎn)品名稱”字段和“金額”字段即可。這時候“產(chǎn)品名稱”字段自動出現(xiàn)在“行標簽”區(qū)域；由于“金額”字段是“數(shù)字”型數(shù)據(jù)，自動出現(xiàn)在數(shù)據(jù)透視表的“數(shù)值”區(qū)域。創(chuàng)建數(shù)據(jù)透視表14同理，計算每個地區(qū)的銷售金額總計是多少？只需要在數(shù)據(jù)透視表字段列表中選中“地區(qū)”字段和“金額”字段即可。其他依此類推創(chuàng)建數(shù)據(jù)透視表15在 Excel 2007 的數(shù)據(jù)透視表中，如果勾選的字段是文本類型，字段默認自動出現(xiàn)在行標簽中，如果勾選的字段是數(shù)值類型的，字段默認自動出現(xiàn)在數(shù)值區(qū)域中。我們也可以將關注的字段直接拖動到相應的區(qū)域中

7、。如：希望創(chuàng)建反映各地區(qū)每種產(chǎn)品銷售額金額總計的數(shù)據(jù)透視表，可以將地區(qū)和產(chǎn)品名稱拖動到行標簽區(qū)域，將金額拖動到數(shù)值區(qū)域。創(chuàng)建數(shù)據(jù)透視表16數(shù)據(jù)透視表的優(yōu)秀之處就是非常靈活如果我們希望獲取每種產(chǎn)品在各個地區(qū)銷售金額的匯總數(shù)據(jù)。只需要在行標簽區(qū)域中，將產(chǎn)品名稱字段拖動到地區(qū)字段上面，金額拖動到數(shù)值上。創(chuàng)建數(shù)據(jù)透視表17如果將不同字段分別拖動到行標簽區(qū)域和列標簽區(qū)域，就可以很方便的創(chuàng)建交叉表格。創(chuàng)建數(shù)據(jù)透視表18報表篩選字段的使用將“地區(qū)”字段拖動到“報表篩選”區(qū)域，將“城市”字段拖動到“列標簽”區(qū)域，將“產(chǎn)品名稱”字段拖動到“行標簽”區(qū)域，將“金額”字段拖動到“數(shù)值”區(qū)域，則可以按地區(qū)查看每種產(chǎn)品

8、在各個城市的金額銷售合計情況。創(chuàng)建數(shù)據(jù)透視表19在“報表篩選”區(qū)域，可以對報表實現(xiàn)篩選，查看所關注的特定地區(qū)的詳細信息。直接單擊“報表篩選”區(qū)域中“地區(qū)”字段右邊的下拉鍵頭，即可對數(shù)據(jù)透視表實現(xiàn)篩選。創(chuàng)建數(shù)據(jù)透視表20使用數(shù)據(jù)透視表展開或折疊分類數(shù)據(jù)以及查看摘要數(shù)據(jù)的明細信息。在上面數(shù)據(jù)透視表的基礎之上，可以顯示更詳細的信息。比如，要查看每種產(chǎn)品由不同雇員的銷售情況。創(chuàng)建數(shù)據(jù)透視表21如果希望查看此匯總信息的詳細構成，只需要雙擊 8404.16 所在的數(shù)據(jù)單元格，Excel 會自動在一個新的工作表顯示夠成此合計信息的每一筆詳細記錄。創(chuàng)建數(shù)據(jù)透視表22使用數(shù)據(jù)透視表組合數(shù)據(jù)使用數(shù)據(jù)透視表組合數(shù)據(jù)

9、 1 1 、組合日期數(shù)據(jù)前面我們使用地區(qū)、城市、產(chǎn)品名稱、雇員等字段作為分類字段查看數(shù)據(jù)匯總信息。那么如果我們按照訂單日期做為分類字段來查看匯總信息會是什么樣呢？將“訂單日期”字段拖動到“行標簽”區(qū)域，所生成數(shù)據(jù)透視表如下圖所示創(chuàng)建數(shù)據(jù)透視表23使用數(shù)據(jù)透視表組合數(shù)據(jù)使用數(shù)據(jù)透視表組合數(shù)據(jù) 1 1 、組合日期數(shù)據(jù)以上數(shù)據(jù)透視表顯示的是每一天的金額合計，顯然不是我們所期望的結果。如果我們希望按照年、季度、月份等來計算金額的匯總信息又該如何實現(xiàn)呢？可以直接在日期字段上單擊鼠標右鍵，選擇“組合”。創(chuàng)建數(shù)據(jù)透視表24使用數(shù)據(jù)透視表組合數(shù)據(jù)使用數(shù)據(jù)透視表組合數(shù)據(jù) 1 1 、組合日期數(shù)據(jù)創(chuàng)建數(shù)據(jù)透視表如果

10、要取消日期的組合，在日期字段上單擊鼠標右鍵，選擇如果要取消日期的組合，在日期字段上單擊鼠標右鍵，選擇“取消組合取消組合”即可。即可。25使用數(shù)據(jù)透視表組合數(shù)據(jù)使用數(shù)據(jù)透視表組合數(shù)據(jù) 2 2、數(shù)值數(shù)據(jù)的分段組合除了日期字段可以組合，那么還有什么字段可以組合呢？如想得到單次訂單金額在 0 至 1000,1000至2000、2000至3000等等 的訂單的金額合計，占了所有訂單金額的百分比。將“金額”字段拖動到“行標簽”區(qū)域（此時，“行標簽”區(qū)域和“數(shù)值”區(qū)域都是“金額”字段），創(chuàng)建如下數(shù)據(jù)透視表創(chuàng)建數(shù)據(jù)透視表26使用數(shù)據(jù)透視表組合數(shù)據(jù)使用數(shù)據(jù)透視表組合數(shù)據(jù) 2 2、數(shù)值數(shù)據(jù)的分段組合如果作為企業(yè)的

11、決策層，會更重視金額在 3000 以內的，這些看起來銷量很小的訂單，而那些高于 5000 的訂單，看起來單次銷量很大，但由于訂單數(shù)有限，占真?zhèn)€企業(yè)總銷量的百分比很低。創(chuàng)建數(shù)據(jù)透視表27使用數(shù)據(jù)透視表組合數(shù)據(jù)使用數(shù)據(jù)透視表組合數(shù)據(jù) 3 3、對文本字段的分類組合除了可以對日期字段、數(shù)值字段的組合，還可以對文本類字段進行組合分類。如，將產(chǎn)品名稱作為行字段。但我們希望將產(chǎn)品進行分類，如白米、糙米等屬于谷類，鹽、醬油等屬于調味品。我們可以使用組合功能將產(chǎn)品進行分類。按住 Ctrl 鍵的同時選擇白米、糙米、黃豆、小米等，單擊鼠標右鍵選擇組合創(chuàng)建數(shù)據(jù)透視表28數(shù)據(jù)透視表的數(shù)據(jù)匯總方式數(shù)據(jù)透視表的數(shù)據(jù)匯總方式

12、1 1、改變數(shù)據(jù)匯總方式創(chuàng)建數(shù)據(jù)透視表在匯總方式中有一共有在匯總方式中有一共有 11 種函數(shù)，包括：求和、計數(shù)、數(shù)值計數(shù)、平均值、最大值、最種函數(shù)，包括：求和、計數(shù)、數(shù)值計數(shù)、平均值、最大值、最小值、乘積、標準偏差、總體標準偏差、方差、總體方差。小值、乘積、標準偏差、總體標準偏差、方差、總體方差。29數(shù)據(jù)透視表的數(shù)據(jù)匯總方式數(shù)據(jù)透視表的數(shù)據(jù)匯總方式2 2、以百分比方式顯示數(shù)據(jù)如，顯示的是每種產(chǎn)品在各個地區(qū)銷售金額合計數(shù)，但如果我們將每種產(chǎn)品的合計作為一個總量，希望查看產(chǎn)品分布在不同地區(qū)的比例是多少，就可以通過改變值顯示方式來實現(xiàn)創(chuàng)建數(shù)據(jù)透視表30數(shù)據(jù)透視表的數(shù)據(jù)匯總方式數(shù)據(jù)透視表的數(shù)據(jù)匯總方式

13、2 2、以百分比方式顯示數(shù)據(jù)如果我們突然改變了主意，希望將地區(qū)作為總和，計算每個產(chǎn)品的金額占地區(qū)合計的百分比是多少，這時候我們只需將“值顯示方式”改為“占同列數(shù)據(jù)總和的百分比”就可以了。創(chuàng)建數(shù)據(jù)透視表31數(shù)據(jù)透視表的數(shù)據(jù)匯總方式數(shù)據(jù)透視表的數(shù)據(jù)匯總方式3 3、在數(shù)據(jù)透視表中顯示多個計算字段創(chuàng)建數(shù)據(jù)透視表32數(shù)據(jù)透視表的數(shù)據(jù)匯總方式數(shù)據(jù)透視表的數(shù)據(jù)匯總方式4 4、重命名字段創(chuàng)建數(shù)據(jù)透視表33數(shù)據(jù)透視表的數(shù)據(jù)匯總方式數(shù)據(jù)透視表的數(shù)據(jù)匯總方式5 5、不同匯總方式的數(shù)據(jù)比較如果我們希望同時查看雇員在每個月的發(fā)生額以及以往月份的累加額合計，該如何得到呢？創(chuàng)建數(shù)據(jù)透視表34數(shù)據(jù)透視表的數(shù)據(jù)匯總方式數(shù)據(jù)透視

14、表的數(shù)據(jù)匯總方式5 5、不同匯總方式的數(shù)據(jù)比較雇員在每個月的發(fā)生額以及以往月份的累加額合計創(chuàng)建數(shù)據(jù)透視表35數(shù)據(jù)透視表的數(shù)據(jù)匯總方式數(shù)據(jù)透視表的數(shù)據(jù)匯總方式6 6、創(chuàng)建、編輯或刪除數(shù)據(jù)透視表公式在使用數(shù)據(jù)透視報表的時候，如果匯總函數(shù)和自定義計算（值顯示方式）沒有提供所需的結果，則可在計算字段或計算項中創(chuàng)建自己的公式。如需在數(shù)據(jù)透視表顯示每個月銷售金額的合計信息。但又需要按照銷售金額的 6%計算稅金。可以使用計算字段，向數(shù)據(jù)透視表添加一個新的字段“稅金”。方法：1.光標點中數(shù)據(jù)透視表，在數(shù)據(jù)透視表的“選項”工具面板，“工具”組，選擇“計算字段”。2.在“插入計算字段”對話框“名稱”位置輸入“稅金

15、”，在“公式”位置輸入公式“ =金額*6%”，點擊添加，即為數(shù)據(jù)透視表添加了“稅金”字段。單擊確定后“稅金”字段自動添加到數(shù)據(jù)透視表中。創(chuàng)建數(shù)據(jù)透視表36數(shù)據(jù)透視表的數(shù)據(jù)匯總方式數(shù)據(jù)透視表的數(shù)據(jù)匯總方式6 6、創(chuàng)建、編輯或刪除數(shù)據(jù)透視表公式創(chuàng)建數(shù)據(jù)透視表37數(shù)據(jù)透視表的數(shù)據(jù)匯總方式數(shù)據(jù)透視表的數(shù)據(jù)匯總方式6 6、創(chuàng)建、編輯或刪除數(shù)據(jù)透視表公式創(chuàng)建數(shù)據(jù)透視表38數(shù)據(jù)透視表的數(shù)據(jù)匯總方式數(shù)據(jù)透視表的數(shù)據(jù)匯總方式7 7、在數(shù)據(jù)透視表中使用計算項下圖中顯示的是每個雇員銷售每種產(chǎn)品的金額匯總表。那么能否通過數(shù)據(jù)透視表計算雇員銷量的平均水平是多少呢？我們可以通過數(shù)據(jù)透視表計算項來實現(xiàn)。創(chuàng)建數(shù)據(jù)透視表39數(shù)

16、據(jù)透視表的數(shù)據(jù)匯總方式數(shù)據(jù)透視表的數(shù)據(jù)匯總方式7 7、在數(shù)據(jù)透視表中使用計算項我們希望為“雇員”字段中添加一項名為“雇員平均水平”的項所以首先要將光標定位在“雇員”字段上，然后在數(shù)據(jù)透視表的“選項”工具面板，“工具”組，選擇“計算字項”。計算項對話框顯示為：在“雇員”中插入計算字段。在對話框的名稱位置輸入“雇員平均水平”，在公式位置輸入“=AVERAGE(張穎,王偉,李芳,鄭建杰,趙軍,孫林,金士鵬,劉英玫,張雪眉)”。單擊添加，“雇員平均水平”項即添加到數(shù)據(jù)透視表的“雇員”字段中。創(chuàng)建數(shù)據(jù)透視表40數(shù)據(jù)透視表的數(shù)據(jù)匯總方式數(shù)據(jù)透視表的數(shù)據(jù)匯總方式7 7、在數(shù)據(jù)透視表中使用計算項創(chuàng)建數(shù)據(jù)透視表

17、41數(shù)據(jù)透視表的數(shù)據(jù)匯總方式數(shù)據(jù)透視表的數(shù)據(jù)匯總方式7 7、在數(shù)據(jù)透視表中使用計算項創(chuàng)建數(shù)據(jù)透視表若要刪除添加的計算字段或計算項，在計算字段或計算字段對話框的名稱中，選擇若要刪除添加的計算字段或計算項，在計算字段或計算字段對話框的名稱中，選擇要刪除的字段，單擊對話框右側的刪除按鈕即可。要刪除的字段，單擊對話框右側的刪除按鈕即可。42數(shù)據(jù)透視表的布局和格式數(shù)據(jù)透視表的布局和格式在我們創(chuàng)建數(shù)據(jù)透視表之后，可能會出現(xiàn)如下圖兩種不同的格式。究竟是為什么呢？其實如果是基于以前版本的Excel 文件創(chuàng)建的數(shù)據(jù)透視表，則會顯示為如左邊的格式。如果是基于 Excel 2007 格式的文件創(chuàng)建的數(shù)據(jù)透視表，則會

18、顯示為如右邊的格式。創(chuàng)建數(shù)據(jù)透視表43數(shù)據(jù)透視表的布局和格式數(shù)據(jù)透視表的布局和格式如果希望更改數(shù)據(jù)透視表的布局，可以選擇“設計”工具面板的“布局”組，單擊“報表布局”命令，從下拉列表中選擇相應的版式即可。創(chuàng)建數(shù)據(jù)透視表44數(shù)據(jù)透視表的布局和格式數(shù)據(jù)透視表的布局和格式我們還可以通過選擇“設計”工具面板的“布局”組，“分類匯總”命令來改變匯總顯示方式。創(chuàng)建數(shù)據(jù)透視表45數(shù)據(jù)透視表的布局和格式數(shù)據(jù)透視表的布局和格式如果對數(shù)據(jù)透視表默認的格式不喜歡，我們還可以通過選擇“設計”工具面板的“數(shù)據(jù)透視表樣式”組中選擇數(shù)據(jù)透視表的格式。從而很容易設計出漂亮的數(shù)據(jù)透視表創(chuàng)建數(shù)據(jù)透視表46數(shù)據(jù)透視表的布局和格式數(shù)

19、據(jù)透視表的布局和格式通過數(shù)據(jù)透視表選項控制布局創(chuàng)建數(shù)據(jù)透視表47數(shù)據(jù)透視表的數(shù)據(jù)源控制與刷新數(shù)據(jù)透視表的數(shù)據(jù)源控制與刷新我們可以使用數(shù)據(jù)透視表功能，在數(shù)據(jù)源的基礎上生成各種不同的報表，以滿足不同層次的需求。但當我們使用數(shù)據(jù)透視表生成報告后，如果數(shù)據(jù)源變化了，數(shù)據(jù)透視表報告的信息會不會自動跟著變化呢？當更改數(shù)據(jù)源后，默認情況下數(shù)據(jù)透視表并不會自動更新。當數(shù)據(jù)源的值更改過以后，我們可以通過數(shù)據(jù)透視表的“選項”工具面板里的“數(shù)據(jù)”組的“刷新”命令來刷新數(shù)據(jù)透視表。這樣數(shù)據(jù)透視表報告就變成最新的了。創(chuàng)建數(shù)據(jù)透視表48數(shù)據(jù)透視表的數(shù)據(jù)源控制與刷新數(shù)據(jù)透視表的數(shù)據(jù)源控制與刷新但是“刷新”命令只是刷新數(shù)據(jù)透

20、視表所引用的數(shù)據(jù)源的值。當數(shù)據(jù)源的范圍變化了以后（比如數(shù)據(jù)源增加了新的紀錄）則使用刷新命令就不能將數(shù)據(jù)范圍一起更新了。可以通過數(shù)據(jù)透視表的“選項”工具面板里的“數(shù)據(jù)”組的“更改數(shù)據(jù)源”命令來重新選擇數(shù)據(jù)透視表所引用的數(shù)據(jù)創(chuàng)建數(shù)據(jù)透視表49在“分析”選項卡上的“操作”組中，單擊“選擇”，然后單擊“整個數(shù)據(jù)透視表”。按 Delete。 刪除數(shù)據(jù)透視表50測試一下測試一下測試題測試題51構建數(shù)據(jù)透視表后，將不能更改布局。（選擇一個答案。）構建數(shù)據(jù)透視表后，將不能更改布局。（選擇一個答案。）1.對。2.錯。測試題測試題 152構建數(shù)據(jù)透視表后，將不能更改布局。構建數(shù)據(jù)透視表后，將不能更改布局。答案：錯您隨時可以更改答案或重新開始。測試題測試題 153什么是數(shù)據(jù)透視表字段？（選擇一個答案。）什么是