Excel2007數(shù)據(jù)透視表應(yīng)用詳解

1、數(shù)據(jù)透視表應(yīng)用大全 本文從創(chuàng)建數(shù)據(jù)透視表到使用數(shù)據(jù)透視表查看、匯總、分析數(shù)據(jù)，還包括 數(shù)據(jù)透視表的布局控制，數(shù)據(jù)透視表的數(shù)據(jù)源更新與鏈接等功能都做了詳盡的介紹。 希望通過本文學(xué)習(xí)，讓大家初步掌握數(shù)據(jù)透視表的應(yīng)用！Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 數(shù)據(jù)透視表和數(shù)據(jù)透視圖表 A. 數(shù)據(jù)透視表介紹 A.1 什么是數(shù)據(jù)透視表? 數(shù)據(jù)透視表是一種可以快速匯總、分析大量數(shù)據(jù)表格的交互式工具。使用數(shù)據(jù)透視表可以按 照數(shù)據(jù)表格的不同字段從多個(gè)角度進(jìn)行透視，并建立交叉表格，用以查看數(shù)據(jù)表格不同層面 的匯總信息、分析結(jié)果以及摘要數(shù)據(jù)。 使用數(shù)據(jù)透視表可以深入分析數(shù)值數(shù)據(jù)，以幫助用戶發(fā)現(xiàn)關(guān)鍵數(shù)據(jù)，并做出有關(guān)

2、企業(yè)中關(guān)鍵 數(shù)據(jù)的決策。 數(shù)據(jù)透視表是針對(duì)以下用途特別設(shè)計(jì)的： 以友好的方式，查看大量的數(shù)據(jù)表格。 對(duì)數(shù)值數(shù)據(jù)快速分類匯總，按分類和子分類查看數(shù)據(jù)信息。 展開或折疊所關(guān)注的數(shù)據(jù)，快速查看摘要數(shù)據(jù)的明細(xì)信息。 建立交叉表格（將行移動(dòng)到列或?qū)⒘幸苿?dòng)到行），以查看源數(shù)據(jù)的不同匯總。 快速的計(jì)算數(shù)值數(shù)據(jù)的匯總信息、差異、個(gè)體占總體的百分比信息等。 若要?jiǎng)?chuàng)建數(shù)據(jù)透視表，要求數(shù)據(jù)源必須是比較規(guī)則的數(shù)據(jù)，也只有比較大量的數(shù)據(jù)才能體現(xiàn) 數(shù)據(jù)透視表的優(yōu)勢(shì)。如：表格的第一行是字段名稱，字段名稱不能為空；數(shù)據(jù)記錄中最好不 要有空白單元格或各并單元格；每個(gè)字段中數(shù)據(jù)的數(shù)據(jù)類型必須一致（如，"訂單日期&quo

3、t;字 段的值即有日期型數(shù)據(jù)又有文本型數(shù)據(jù)，則無法按照"訂單日期"字段進(jìn)行組合）。數(shù)據(jù)越 規(guī)則，數(shù)據(jù)透視表使用起來越方便。 如上圖中的表格屬于交叉表，不太適合依據(jù)此表創(chuàng)建數(shù)據(jù)透視表（不是不能使用數(shù)據(jù)透視表， 只是使用上表創(chuàng)建數(shù)據(jù)透視表某些功能無法體現(xiàn)）。因?yàn)槠湓路荼环譃?12 個(gè)字段，互相比較 2 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 起來比較麻煩。 最好將其改為如下結(jié)構(gòu)： 上表只使用一個(gè)"月份"字段，而 12 個(gè)月作為月份字段的值，這樣互相比較起來比較容易。 使用此結(jié)構(gòu)的表格，通過數(shù)據(jù)透視表，很容易創(chuàng)建上圖所示的交叉表格，但反之則很麻煩。

4、 因此，創(chuàng)建數(shù)據(jù)透視表之前，要注意表格的結(jié)構(gòu)問題。越簡(jiǎn)單越好，就類似數(shù)據(jù)庫的存儲(chǔ)方 式?；蛘撸芸v向排列的表格就不要橫向排列。 A.1.1 為什么使用數(shù)據(jù)透視表？ 如下表，"產(chǎn)品銷售記錄單"記錄的是 2006 和 2007 年某公司訂單銷售情況的表格。其中包 括訂單日期，產(chǎn)品名稱，銷往的地區(qū)、城市，以及產(chǎn)品的單價(jià)、數(shù)量、金額等。 我們希望根據(jù)此表快速計(jì)算出如下匯總信息： 1. 2. 3. 4. 5. 每種產(chǎn)品銷售金額的總計(jì)是多少？ 每個(gè)地區(qū)的銷售金額總計(jì)是多少？ 每個(gè)城市的銷售金額總計(jì)是多少？ 每個(gè)雇員的銷售金額總計(jì)是多少？ 每個(gè)城市中每種產(chǎn)品的銷售金額合計(jì)是多少？ 諸多的

5、問題，使用數(shù)據(jù)透視表可以輕松解決。 3 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 B. 使用數(shù)據(jù)透視表 B.1 創(chuàng)建數(shù)據(jù)透視表 盡管數(shù)據(jù)透視表的功能非常強(qiáng)大，但是創(chuàng)建的過程卻是非常簡(jiǎn)單。 1. 2. 將光標(biāo)點(diǎn)在表格數(shù)據(jù)源中任意有內(nèi)容的單元格，或者將整個(gè)數(shù)據(jù)區(qū)域選中。 選擇"插入"選項(xiàng)卡，單擊"數(shù)據(jù)透視表"命令。 3. 在彈出的"創(chuàng)建數(shù)據(jù)透視表"對(duì)話框中，"請(qǐng)選擇要分析的數(shù)據(jù)"一項(xiàng)已經(jīng)自動(dòng)選中了 光標(biāo)所處位置的整個(gè)連續(xù)數(shù)據(jù)區(qū)域，也可以在此對(duì)話框中重新選擇想要分析的數(shù)據(jù)區(qū)域 （還可以使用外部數(shù)據(jù)源，請(qǐng)參閱后面

6、內(nèi)容）。"選擇放置數(shù)據(jù)透視表位置"項(xiàng)，可以在 新的工作表中創(chuàng)建數(shù)據(jù)透視表，也可以將數(shù)據(jù)透視表放置在當(dāng)前的某個(gè)工作表中。 4 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 4. 單擊確定。Excel 自動(dòng)創(chuàng)建了一個(gè)空的數(shù)據(jù)透視表。 上圖中左邊為數(shù)據(jù)透視表的報(bào)表生成區(qū)域，會(huì)隨著選擇的字段不同而自動(dòng)更新；右側(cè)為數(shù)據(jù) 透視表字段列表。創(chuàng)建數(shù)據(jù)透視表后，可以使用數(shù)據(jù)透視表字段列表來添加字段。如果要更 改數(shù)據(jù)透視表，可以使用該字段列表來重新排列和刪除字段。默認(rèn)情況下，數(shù)據(jù)透視表字段 列表顯示兩部分：上方的字段部分用于添加和刪除字段，下方的布局部分用于重新排列和重 新定位字段?？梢?/p>

7、將數(shù)據(jù)透視表字段列表?？吭诖翱诘娜我庖粋?cè)，然后沿水平方向調(diào)整其大 小；也可以取消?？繑?shù)據(jù)透視表字段列表，此時(shí)既可以沿垂直方向也可以沿水平方向調(diào)整其 大小。 右下方為數(shù)據(jù)透視表的 4 個(gè)區(qū)域，其中"報(bào)表篩選"、"列標(biāo)簽"、"行標(biāo)簽"區(qū)域用于放置分 類字段，"數(shù)值"區(qū)域放置數(shù)據(jù)匯總字段。當(dāng)將字段拖動(dòng)到數(shù)據(jù)透視表區(qū)域中時(shí)，左側(cè)會(huì)自 動(dòng)生成數(shù)據(jù)透視表報(bào)表。 5 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 B.2 數(shù)據(jù)透視表字段的使用 將字段拖動(dòng)到"行標(biāo)簽"區(qū)域，則此字段中的每類項(xiàng)目會(huì)成為一行；我

8、們可以將希望按行顯 示的字段拖動(dòng)到此區(qū)域。 將字段拖動(dòng)到"列字段"區(qū)域，則此字段種的每類項(xiàng)目會(huì)成為列；我們可以將希望按列顯示 的字段拖動(dòng)到此區(qū)域。 將字段拖動(dòng)到"數(shù)值"區(qū)域，則會(huì)自動(dòng)計(jì)算此字段的匯總信息（如求和、計(jì)數(shù)、平均值、方 差等等）；我們可以將任何希望匯總的字段拖動(dòng)到此區(qū)域。 將字段拖動(dòng)到"報(bào)表篩選"區(qū)域，則可以根據(jù)此字段對(duì)報(bào)表實(shí)現(xiàn)篩選，可以顯示每類項(xiàng)目相 關(guān)的報(bào)表。我們可以將較大范圍的分類拖動(dòng)到此區(qū)域，以實(shí)現(xiàn)報(bào)表篩選。 使用行、列標(biāo)簽區(qū)域 如，我們來解決前面提到的第一個(gè)問題。每種產(chǎn)品銷售金額的總計(jì)是多少？ 只需要在數(shù)據(jù)透視表字

9、段列表中選中"產(chǎn)品名稱"字段和"金額"字段即可。這時(shí)候"產(chǎn)品 名稱"字段自動(dòng)出現(xiàn)在"行標(biāo)簽"區(qū)域；由于"金額"字段是"數(shù)字"型數(shù)據(jù)，自動(dòng)出現(xiàn)在 數(shù)據(jù)透視表的"數(shù)值"區(qū)域。如下圖： 可見通過數(shù)據(jù)透視表創(chuàng)建數(shù)據(jù)分類匯總信息是如此方便簡(jiǎn)單。 同理，計(jì)算每個(gè)地區(qū)的銷售金額總計(jì)是多少？只需要在數(shù)據(jù)透視表字段列表中選中"地區(qū)" 字段和"金額"字段即可。其他依此類推 6 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 在 Ex

10、cel 2007 的數(shù)據(jù)透視表中，如果勾選的字段是文本類型，字段默認(rèn)自動(dòng)出現(xiàn)在行標(biāo)簽中， 如果勾選的字段是數(shù)值類型的，字段默認(rèn)自動(dòng)出現(xiàn)在數(shù)值區(qū)域中。 我們也可以將關(guān)注的字段直接拖動(dòng)到相應(yīng)的區(qū)域中。如：希望創(chuàng)建反映各地區(qū)每種產(chǎn)品銷售 金額總計(jì)的數(shù)據(jù)透視表，可以將地區(qū)和產(chǎn)品名稱拖動(dòng)到行標(biāo)簽區(qū)域，將金額拖動(dòng)到數(shù)值區(qū)域。 結(jié)果如圖 7 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 數(shù)據(jù)透視表的優(yōu)秀之處就是非常靈活，如果我們希望獲取每種產(chǎn)品在各個(gè)地區(qū)銷售金額的匯 總數(shù)據(jù)，只需要在行標(biāo)簽區(qū)域中，將產(chǎn)品名稱字段拖動(dòng)到地區(qū)字段上面即可。如圖，其他字 段的組合亦是如此 如果將不同字段分別拖動(dòng)到行標(biāo)簽區(qū)域和

11、列標(biāo)簽區(qū)域，就可以很方便的創(chuàng)建交叉表格。 報(bào)表篩選字段的使用 將"地區(qū)"字段拖動(dòng)到"報(bào)表篩選"區(qū)域，將"城市"字段拖動(dòng)到"列標(biāo)簽"區(qū)域，將"產(chǎn) 8 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 品名稱"字段拖動(dòng)到"行標(biāo)簽"區(qū)域，將"金額"字段拖動(dòng)到"數(shù)值"區(qū)域，則可以按地區(qū) 查看每種產(chǎn)品在各個(gè)城市的金額銷售合計(jì)情況。 在"報(bào)表篩選"區(qū)域，可以對(duì)報(bào)表實(shí)現(xiàn)篩選，查看所關(guān)注的特定地區(qū)的詳細(xì)信息。直接單擊 "

12、報(bào)表篩選"區(qū)域中"地區(qū)"字段右邊的下拉鍵頭，即可對(duì)數(shù)據(jù)透視表實(shí)現(xiàn)篩選。 9 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 C. 使用數(shù)據(jù)透視表查看摘要與明細(xì)信息 使用數(shù)據(jù)透視表展開或折疊分類數(shù)據(jù)以及查看摘要數(shù)據(jù)的明細(xì)信息。 在上面數(shù)據(jù)透視表的基礎(chǔ)之上，可以顯示更詳細(xì)的信息。比如，要查看每種產(chǎn)品由不同雇員 的銷售情況。可以有兩種方法： 1. 直接雙擊要查看詳細(xì)信息的產(chǎn)品名稱。 如 A5 單元格中的產(chǎn)品是白米，雙擊 A5 單元格后會(huì)彈出"顯示明細(xì)數(shù)據(jù)"對(duì)話框，在 其中選擇要顯示在"產(chǎn)品名稱"下一級(jí)別的字段"雇員&

13、quot;字段即可（依此類推，鼠標(biāo)雙 擊雇員名字還可以選擇要查看的下一級(jí)別字段）。但這個(gè)時(shí)候只是把產(chǎn)品"白米"下的 詳細(xì)信息顯示出來了，如果想查看其它產(chǎn)品的詳細(xì)信息，單擊產(chǎn)品名稱左邊的"加號(hào)" 即可展開，此時(shí)"加號(hào)"變?yōu)榱?quot;減號(hào)"，單擊"減號(hào)"可以將詳細(xì)信息折疊而只顯示 摘要信息。如果要顯示所有產(chǎn)品由各個(gè)雇員銷售情況的詳細(xì)信息，可以在"產(chǎn)品名稱" 字段上點(diǎn)擊鼠標(biāo)右鍵選擇"展開/折疊"，再選擇"展開整個(gè)字段"，這樣就可以顯示各 個(gè)雇員的銷

14、售金額匯總信息了。 10 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 2. 直接將"雇員"字段拖動(dòng)到"行標(biāo)簽"區(qū)域產(chǎn)品名稱的下面。 這樣就可以顯示每種產(chǎn)品由各個(gè)雇員銷售金額的匯總信息了。同樣在每個(gè)產(chǎn)品項(xiàng)目的左 面也會(huì)出現(xiàn)"減號(hào)"標(biāo)記，單擊"減號(hào)"標(biāo)記可以折疊詳細(xì)信息。 如上圖所示，白奶酪在所有城市銷售金額合計(jì)是 8404.16，如果希望查看此匯總信息的詳細(xì) 構(gòu)成，只需要雙擊 8404.16 所在的 i14 單元格，Excel 會(huì)自動(dòng)在一個(gè)新的工作表顯示夠成此合 計(jì)信息的每一筆詳細(xì)記錄。 11 / 46 Ex

15、cel 2007 數(shù)據(jù)透視表應(yīng)用詳解 使用此方法可以查看重慶的白米產(chǎn)品的明細(xì)記錄。 D. 使用數(shù)據(jù)透視表組合數(shù)據(jù) D.1 組合日期數(shù)據(jù) 前面我們使用地區(qū)、城市、產(chǎn)品名稱、雇員等字段作為分類字段查看數(shù)據(jù)匯總信息。那么如 果我們按照訂單日期做為分類字段來查看匯總信息會(huì)是什么樣呢？將"訂單日期"字段拖動(dòng) 到"行標(biāo)簽"區(qū)域，所生成數(shù)據(jù)透視表如下圖所示： 12 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 此數(shù)據(jù)透視表顯示的是每一天的金額合計(jì)，顯然不是我們所期望的結(jié)果。如果我們希望按照 年、季度、月份等來計(jì)算金額的匯總信息又該如何實(shí)現(xiàn)呢？ 可以直接在日期字段

16、上單擊鼠標(biāo)右鍵，選擇"組合"。 或者在工具面板上選擇"選項(xiàng)"選項(xiàng)卡，選擇"將字段分組"命令。 13 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 彈出如下圖所示"分組"對(duì)話框，在"分組"對(duì)話框選擇要分組的步長(zhǎng)，年、季度、月單擊 確定即可。 生成數(shù)據(jù)透視表如下，按照年、季度、月顯示匯總信息。 注意：要將日期字段進(jìn)行組合，要求此字段里所有數(shù)據(jù)必須都為日期值，任何一個(gè)單元格的 值不正確都會(huì)導(dǎo)致不能組合；并且此字段中不能有空值，任何一個(gè)單元格為空也將導(dǎo)致不能 組合。 另外，很多歐洲國家喜歡以周作為

17、間隔來查看數(shù)據(jù)，但在數(shù)據(jù)透視表中并沒有提供以周作為 組合的方式；不過我們可以按照每 7 天組合為一組，但這種方法不能顯示此數(shù)據(jù)是一年中的 第幾周。可以在原表的基礎(chǔ)上使用 WeekNum 函數(shù)先根據(jù)日期計(jì)算出周數(shù)，再根據(jù)此表創(chuàng)建 數(shù)據(jù)透視表。 如果要取消日期的組合，在日期字段上單擊鼠標(biāo)右鍵，選擇"取消組合"即可。 14 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 D.2 數(shù)值數(shù)據(jù)的分段組合 除了日期字段可以組合，那么還有什么字段可以組合呢？將"金額"字段拖動(dòng)到"行標(biāo)簽" 區(qū)域（此時(shí)，"行標(biāo)簽"區(qū)域和"

18、;數(shù)值"區(qū)域都是"金額"字段），創(chuàng)建如下數(shù)據(jù)透視表，此 數(shù)據(jù)透視表顯然無法提供對(duì)企業(yè)決策有意的信息。 但我們可以將行標(biāo)簽字段進(jìn)行組合，直接在"行標(biāo)簽"區(qū)域的"金額"字段上單擊鼠標(biāo)右鍵 選擇"組合"。 在彈出的"組合"對(duì)話框中，"起始于"位置輸入 0，終止于位置保持不變，步長(zhǎng)值保持為 1000，單擊確定。 15 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 生成數(shù)據(jù)透視表如下 從以上數(shù)據(jù)透視表不難看出，單次訂單金額在 0 至 1000 的訂單的金額合計(jì)，占了所有

19、訂單 金額總和的一半以上，而金額在 0 至 3000 之間的訂單的金額總和占所有訂單金額總和的 80% 以上。那么作為企業(yè)的決策層，更應(yīng)該重視金額在 3000 以內(nèi)的這些看起來銷量很小的訂單， 而那些高于 5000 的訂單，看起來單次銷量很大，但由于訂單數(shù)有限，占真?zhèn)€企業(yè)總銷量的 百分比很低。 若要取消組合，直接在數(shù)據(jù)透視表行標(biāo)簽字段上單擊鼠標(biāo)右鍵，選擇"取消組合"。 D.3 對(duì)文本字段的分類組合 除了可以對(duì)日期字段、數(shù)值字段的組合，還可以對(duì)文本類字段進(jìn)行組合分類。 如下圖，將產(chǎn)品名稱作為行字段。但我們希望將產(chǎn)品進(jìn)行分類，如白米、糙米等屬于谷類， 鹽、醬油等屬于調(diào)味品。我們

20、可以使用組合功能將產(chǎn)品進(jìn)行分類。 16 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 按住 Ctrl 鍵的同時(shí)選擇白米、糙米、黃豆、小米等，單擊鼠標(biāo)右鍵選擇組合 此時(shí)已經(jīng)將白米、糙米、黃豆等合并為一組，使用同樣的方法可以將奶酪、餅干、蛋糕等組 合為另一組。 17 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 但此時(shí)的組合結(jié)果命名為數(shù)據(jù)組 1，數(shù)據(jù)組 2；可以直接在單元格內(nèi)將名字改為產(chǎn)品分類名 字即可，如谷類、點(diǎn)心等 E. 數(shù)據(jù)透視表的數(shù)據(jù)匯總方式 數(shù)據(jù)透視表的優(yōu)勢(shì)在于，我們可以很方便的從不同的角度，對(duì)數(shù)據(jù)進(jìn)行不同方式的匯總統(tǒng)計(jì)。 前面我們創(chuàng)建的數(shù)據(jù)透視表都是以求和的方式計(jì)算金額合計(jì)

21、。那么當(dāng)我們希望匯總的信息不 是求和，而是計(jì)算平均值或者計(jì)數(shù)那么該如何處理呢？ 18 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 E.1 改變數(shù)據(jù)匯總方式 比如，我們希望統(tǒng)計(jì)每種產(chǎn)品被銷售的次數(shù)。這時(shí)候就需要對(duì)產(chǎn)品進(jìn)行計(jì)數(shù)統(tǒng)計(jì)。 我們可以將"產(chǎn)品名稱"字段分別拖拽到"行標(biāo)簽"區(qū)域和"數(shù)值"區(qū)域，由于"產(chǎn)品名稱" 字段的內(nèi)容是文本型數(shù)據(jù)，當(dāng)把其拖拽到"數(shù)值"區(qū)域中時(shí)，匯總方式自動(dòng)變?yōu)橛?jì)數(shù)。如圖 所示： 另一個(gè)問題，如果我們希望查看每種產(chǎn)品的平均單價(jià)呢？ 這時(shí)照樣還是將"產(chǎn)品名稱&

22、quot;字段拖拽到"行標(biāo)簽"區(qū)域，而將"單價(jià)"字段拖拽到"數(shù)值" 區(qū)域，由于"單價(jià)"字段的數(shù)據(jù)類型是數(shù)值型，匯總方式會(huì)自動(dòng)為求和。而我們所希望的是 計(jì)算每種產(chǎn)品的平均單價(jià)，那么該如何將匯總方式從求和改為平均值呢？ 我們可以有兩種方法： 1. 2. 光標(biāo)選中數(shù)據(jù)透視表中的"單價(jià)"字段，然后在"選項(xiàng)"工具面板的"活動(dòng)字段"組中 單擊"字段設(shè)置"命令，在彈出的"值字段設(shè)置"對(duì)話框的"匯總方式"選項(xiàng)卡中

23、選擇 "平均值"，單擊確定即可。 直接在數(shù)據(jù)透視表的計(jì)算字段"單價(jià)"上單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇"數(shù) 據(jù)匯總依據(jù)"，選擇所要的計(jì)算方式"平均值"即可。 19 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 在匯總方式中有一共有 11 種函數(shù)，包括：求和、計(jì)數(shù)、數(shù)值計(jì)數(shù)、平均值、最大值、最小 值、乘積、標(biāo)準(zhǔn)偏差、總體標(biāo)準(zhǔn)偏差、方差、總體方差。 具體功能如下表： 匯總方式 求和 計(jì)數(shù) 平均值 最大值 最小值 功能 對(duì)數(shù)值求和。是數(shù)字?jǐn)?shù)據(jù)的默認(rèn)函數(shù)。相當(dāng)于 Sum 函數(shù) 數(shù)據(jù)值的個(gè)數(shù)。作用與 COUNTA

24、工作表函數(shù)相同。"計(jì)數(shù)"匯總是 數(shù)據(jù)（數(shù)字除外）的默認(rèn)函數(shù)。 求數(shù)值平均值。相當(dāng)于 AVERAGE 函數(shù) 求最大值。相當(dāng)于 MAX 函數(shù) 求最小值。相當(dāng)于 MIN 函數(shù) 20 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 乘積 數(shù)值計(jì)數(shù) 標(biāo)準(zhǔn)偏差 總體標(biāo)準(zhǔn)偏差 方差 總體方差 求數(shù)值的乘積。相當(dāng)于 PRODUCT 函數(shù) 數(shù)字?jǐn)?shù)據(jù)值的個(gè)數(shù)。用與 COUNT 工作表函數(shù)相同。 估算總體的標(biāo)準(zhǔn)偏差，樣本為總體的子集。相當(dāng)于 STDEV 函數(shù) 計(jì)算總體的標(biāo)準(zhǔn)偏差。匯總的所有數(shù)據(jù)為總體。相當(dāng)于 STDEVP 函數(shù) 估計(jì)總體方差，樣本為總體的子集。相當(dāng)于 VAR 函數(shù) 計(jì)算總體的

25、方差。匯總的所有數(shù)據(jù)為總體。相當(dāng)于 VARP 函數(shù) "數(shù)值"區(qū)域中的數(shù)據(jù)默認(rèn)情況下通過以下方法對(duì)數(shù)據(jù)透視表中的基礎(chǔ)源數(shù)據(jù)進(jìn)行匯總：對(duì) 于數(shù)值使用"求和"匯總方式，對(duì)于文本值使用"計(jì)數(shù)"匯總方式。但是，我們可以手動(dòng)更 改匯總方式還可以自定義計(jì)算方式。 注意：如果將"單價(jià)"字段拖動(dòng)到"數(shù)值"區(qū)域中的時(shí)候，匯總方式自動(dòng)變?yōu)橛?jì)數(shù)，那么就 說明此字段中一定有文本型的數(shù)據(jù)，哪怕只有一個(gè)單元格是文本型的數(shù)據(jù)，也會(huì)影響整個(gè)字 段的計(jì)算方式。 E.2 改變數(shù)據(jù)透視表的值顯示方式 前面我們改變的數(shù)據(jù)透視表的匯總方式

26、其實(shí)就相當(dāng)于使用工作表函數(shù)對(duì)數(shù)據(jù)的統(tǒng)計(jì)匯總。其 實(shí)通過改變數(shù)據(jù)透視表的值顯示方式，還可以對(duì)數(shù)據(jù)按照不同字段做相對(duì)比較。 方法：將光標(biāo)選中數(shù)據(jù)透視表中的數(shù)值字段，然后在"選項(xiàng)"工具面板的"活動(dòng)字段"組中 單擊"字段設(shè)置"命令，在彈出的"值字段設(shè)置"對(duì)話框的選擇"值顯示方式"選項(xiàng)卡，然 后在下拉列表中選擇相應(yīng)的值顯示方式即可。 值顯示方式具體信息如下表： 值顯示方式 差異 百分比 差異百分比 按某一字段匯總 占同行數(shù)據(jù)總和的百分比 占同列數(shù)據(jù)總和的百分比 占總和的百分比 指數(shù) 結(jié)果 顯示的值為與&q

27、uot;基本字段"中"基本項(xiàng)"值的差。 顯示的值為"基本字段"中"基本項(xiàng)"值的百分比。 顯示的值為與"基本字段"中"基本項(xiàng)"值的百分比差值。 顯示的值為按照"基本字段"中的值連續(xù)累加的匯總。 顯示每一行或每個(gè)類別的值相對(duì)于該行或該類別總計(jì)的百分比。 顯示每一列或每個(gè)系列的所有值為該列或該系列總計(jì)的百分比。 顯示的值為報(bào)表中所有值或所有數(shù)據(jù)點(diǎn)的總計(jì)的百分比。 按下式計(jì)算值： (單元格中值) x (總計(jì)) / (行總計(jì)) x (列總計(jì)) E.2.1 以百分比方式顯示數(shù)據(jù)

28、 例 :如下圖，顯示的是每種產(chǎn)品在各個(gè)地區(qū)銷售金額合計(jì)數(shù)，但如果我們將每種產(chǎn)品的合計(jì) 作為一個(gè)總量，希望查看產(chǎn)品分布在不同地區(qū)的比例是多少，就可以通過改變值顯示方式來 實(shí)現(xiàn)。 21 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 將光標(biāo)選中數(shù)據(jù)透視表中的數(shù)值字段（即"求和項(xiàng)：金額"字段，也可以點(diǎn)中值區(qū)域中任何 一個(gè)位置），然后在"選項(xiàng)"工具面板的"活動(dòng)字段"組中單擊"字段設(shè)置"命令，在彈出 的"值字段設(shè)置"對(duì)話框的選擇"值顯示方式"選項(xiàng)卡，在下拉列表中選擇"占同

29、行數(shù)據(jù)總 和的百分比"，按確定關(guān)閉"值字段設(shè)置"對(duì)話框。結(jié)果如下： 22 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 如果我們突然改變了主意，希望將地區(qū)作為總和，計(jì)算每個(gè)產(chǎn)品的金額占地區(qū)合計(jì)的百分比 是多少，這時(shí)候我們只需將"值顯示方式"改為"占同列數(shù)據(jù)總和的百分比"就可以了。 23 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 E.3 在數(shù)據(jù)透視表中顯示多個(gè)計(jì)算字段 此時(shí)在數(shù)據(jù)透視表的"值"區(qū)域只有一個(gè)計(jì)算字段。要么顯示金額的合計(jì)，要么顯示百分比 等。但上表中如果我們希望同時(shí)查看計(jì)算字段的

30、百分比和金額合計(jì)能不能做到呢？ 答案很簡(jiǎn)單，只需要在字段列表中將"金額"字段再次拖動(dòng)到數(shù)值區(qū)域就可以了。 這個(gè)時(shí)候會(huì)發(fā)現(xiàn)在"數(shù)值"區(qū)域會(huì)顯示兩個(gè)求和項(xiàng)，并且在列標(biāo)簽區(qū)域多了一個(gè)"數(shù)值"字 段。在數(shù)據(jù)透視表區(qū)域中在"地區(qū)"字段下顯示出產(chǎn)品金額的百分比和合計(jì)數(shù)，百分比和合 24 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 計(jì)數(shù)是左右橫向排列的；將"數(shù)值"字段從"列標(biāo)簽"區(qū)域拖動(dòng)到"行標(biāo)簽"區(qū)域，就可以 將"百分比"和"合計(jì)

31、數(shù)"上下縱向排列了。（注意：將"數(shù)值"字段拖放到"產(chǎn)品名稱" 上方和下方顯示的結(jié)果是不一樣的，讀者可以自行拖放以查看效果。） 如果希望查看更多的數(shù)值匯總方式，可以繼續(xù)將要匯總的字段（如金額、單價(jià)、數(shù)量等等） 拖動(dòng)到"數(shù)值"區(qū)域。如果將數(shù)字類型字段拖動(dòng)到"數(shù)值"區(qū)域，默認(rèn)匯總方式為求和，如 果將文本類型字段拖動(dòng)到"數(shù)值"區(qū)域則默認(rèn)匯總方式為計(jì)數(shù)。如果我們希望改變顯示的匯 總方式，可以再按照上面的方法：將光標(biāo)選中數(shù)據(jù)透視表中的要改變的值字段，然后在"選 項(xiàng)"工具面板的&

32、quot;活動(dòng)字段"組中單擊"字段設(shè)置"命令，在彈出的"值字段設(shè)置"對(duì)話框 的選擇相應(yīng)的匯總方式就可以了。 E.3.1 重命名字段 當(dāng)在數(shù)據(jù)透視表"數(shù)值"區(qū)域有多個(gè)計(jì)算字段的時(shí)候，字段名稱都叫做"求和項(xiàng) XX"，要想 改變字段的名稱，可以在字段設(shè)置對(duì)話框的"自定義名稱"中直接更改；也可以在數(shù)據(jù)透視 表中名稱標(biāo)簽單元格中直接更改。 25 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 如果我們需要使用數(shù)據(jù)透視表來查看每個(gè)人在 2006-2007 年每個(gè)月的金額銷售合計(jì)數(shù)，可以 將&q

33、uot;日期"段拖動(dòng)到"行標(biāo)簽"區(qū)域并按照年、月將日期字段組合分組，將"雇員"字段拖 動(dòng)到"列標(biāo)簽"區(qū)域，將"金額"字段拖動(dòng)到"數(shù)值"區(qū)域。生成數(shù)據(jù)透視表如下： 26 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 E.3.2 不同匯總方式的數(shù)據(jù)比較 如果我們希望同時(shí)查看雇員在每個(gè)月的發(fā)生額以及以往月份的累加額合計(jì)，該如何得到呢？ 首先將"金額"字段再次拖拽到"數(shù)值"區(qū)域，這時(shí)候在數(shù)據(jù)透視表中有兩個(gè)關(guān)于金額的求 和項(xiàng)。然后，我們可以通過改變值

34、顯示方式將第二個(gè)金額求和項(xiàng)改變?yōu)轱@示金額累計(jì)。 將光標(biāo)選中數(shù)據(jù)透視表中的"求和項(xiàng)：金額 2"數(shù)值字段，然后在"選項(xiàng)"工具面板的"活 動(dòng)字段"組中單擊"字段設(shè)置"命令，在彈出的"值字段設(shè)置"對(duì)話框的選擇"值顯示方式" 選項(xiàng)卡，在下拉列表中選擇"按某一字段匯總"，在"基本字段"中選擇要匯總累計(jì)的字段 "訂單日期"，單擊確定生成數(shù)據(jù)透視表。 另外可以通過"值顯示方式"選項(xiàng)卡中的"數(shù)字格式&qu

35、ot;修飾字段格式，使其保留 2 位小數(shù)。 將數(shù)值字段名稱"求和項(xiàng)：金額"和"求和項(xiàng)：金額 2"分別改為"當(dāng)月發(fā)生額"和"金額 27 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 累計(jì)"，生成表如下： E.4 創(chuàng)建、編輯或刪除數(shù)據(jù)透視表公式 創(chuàng)建公式 在使用數(shù)據(jù)透視報(bào)表的時(shí)候，如果匯總函數(shù)和自定義計(jì)算（值顯示方式）沒有提供所需的結(jié) 果，則可在計(jì)算字段或計(jì)算項(xiàng)中創(chuàng)建自己的公式。 字段：數(shù)據(jù)表中的每一列，我們稱之為字段。如：地區(qū)、城市、產(chǎn)品名稱、金額等 項(xiàng)：字段里面的每個(gè)值，我們稱之為項(xiàng)。如：地區(qū)字段中的東北、華

36、北、西北；城市字段中 的北京、上海、廣州都稱之為項(xiàng)。 計(jì)算字段：數(shù)據(jù)透視表中的字段，該字段使用用戶創(chuàng)建的公式。計(jì)算字段可使用數(shù)據(jù)透視表 中其他字段中的內(nèi)容執(zhí)行計(jì)算。 計(jì)算項(xiàng)：數(shù)據(jù)透視表字段中的項(xiàng)，該項(xiàng)使用用戶創(chuàng)建的公式。計(jì)算項(xiàng)使用數(shù)據(jù)透視表中相同 字段的其他項(xiàng)的內(nèi)容進(jìn)行計(jì)算。 E.4.1 在數(shù)據(jù)透視表中使用計(jì)算字段 下圖數(shù)據(jù)透視表顯示每個(gè)月銷售金額的合計(jì)信息。但又需要按照銷售金額的 6%計(jì)算稅金。 可以使用計(jì)算字段，向數(shù)據(jù)透視表添加一個(gè)新的字段"稅金"。 方法： 1. 2. 光標(biāo)點(diǎn)中數(shù)據(jù)透視表，在數(shù)據(jù)透視表的"選項(xiàng)"工具面板，"工具"

37、組，選擇"計(jì)算字 段"。 在"插入計(jì)算字段"對(duì)話框"名稱"位置輸入"稅金"，在"公式"位置輸入公式"= 金額*6%"，點(diǎn)擊添加，即為數(shù)據(jù)透視表添加了"稅金"字段。單擊確定后"稅金"字 段自動(dòng)添加到數(shù)據(jù)透視表中。 28 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 29 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 E.4.2 在數(shù)據(jù)透視表中使用計(jì)算項(xiàng) 下圖中顯示的是每個(gè)雇員銷售每種產(chǎn)品的金額匯總表。那么能否通過數(shù)據(jù)透視表計(jì)算

38、雇員銷 量的平均水平是多少呢？我們可以通過數(shù)據(jù)透視表計(jì)算項(xiàng)來實(shí)現(xiàn)。 我們希望為"雇員"字段中添加一項(xiàng)名為"雇員平均水平"的項(xiàng)。所以首先要將光標(biāo)定位在 "雇員"字段上，然后在數(shù)據(jù)透視表的"選項(xiàng)"工具面板，"工具"組，選擇"計(jì)算字項(xiàng)"。 計(jì)算項(xiàng)對(duì)話框顯示為：在"雇員"中插入計(jì)算字段。在對(duì)話框的名稱位置輸入"雇員平均水 平"，在公式位置輸入"= AVERAGE(張穎,王偉,李芳,鄭建杰,趙軍,孫林,金士鵬,劉英玫,張雪眉)"

39、;。 單擊添加，"雇員平均水平"項(xiàng)即添加到數(shù)據(jù)透視表的"雇員"字段中。 30 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 若要?jiǎng)h除添加的計(jì)算字段或計(jì)算項(xiàng)，在計(jì)算字段或計(jì)算字段對(duì)話框的名稱中，選擇要?jiǎng)h除的 字段，單擊對(duì)話框右側(cè)的刪除按鈕即可。 如果要查看每個(gè)雇員與"雇員平均水平"的差異，可以通過更改值顯示方式來實(shí)現(xiàn)。 光標(biāo)定位在數(shù)據(jù)透視表的值字段，然后在"選項(xiàng)"工具面板的"活動(dòng)字段"組中單擊"字段 設(shè)置"命令，在彈出的"值字段設(shè)置"對(duì)話框的選擇&qu

40、ot;值顯示方式"選項(xiàng)卡，在下拉列表中 選擇"差異"，在基本字段中選擇"雇員"，在基本項(xiàng)中選擇"雇員平均水平"，單擊確定。 數(shù)據(jù)透視表的值已顯示為，每個(gè)雇員的銷售額與"雇員平均水平"的差異值，正數(shù)表示此雇 員的銷售額高于雇員平均水平，負(fù)數(shù)表示此雇員的銷售額低于雇員平均水平。 31 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 F. 數(shù)據(jù)透視表的布局和格式 在我們創(chuàng)建數(shù)據(jù)透視表之后，可能會(huì)出現(xiàn)如下圖兩種不同的格式。究竟是為什么呢？ 其實(shí)如果是基于以前版本的 Excel 文件創(chuàng)建的數(shù)據(jù)透視表，則會(huì)顯示為

41、如下圖左邊的格式。 如果是基于 Excel 2007 格式的文件創(chuàng)建的數(shù)據(jù)透視表，則會(huì)顯示為如下圖右邊的格式。 32 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 如果希望更改數(shù)據(jù)透視表的布局，可以選擇"設(shè)計(jì)"工具面板的"布局"組，單擊"報(bào)表布 局"命令，從下拉列表中選擇相應(yīng)的版式即可。 壓縮形式布局 大綱形式布局 表格形式布局 我們還可以通過選擇"設(shè)計(jì)"工具面板的"布局"組，"分類匯總"命令來改變匯總顯示方 33 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 式

42、。 如果對(duì)數(shù)據(jù)透視表默認(rèn)的格式不喜歡，我們還可以通過選擇"設(shè)計(jì)"工具面板的"數(shù)據(jù)透視 表樣式"組中選擇數(shù)據(jù)透視表的格式。從而很容易設(shè)計(jì)出漂亮的數(shù)據(jù)透視表。 通過數(shù)據(jù)透視表選項(xiàng)控制布局: 34 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 35 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 36 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 G. 數(shù)據(jù)透視表的數(shù)據(jù)源控制與刷新 G.1 數(shù)據(jù)源的刷新與更改 我們可以使用數(shù)據(jù)透視表功能，在數(shù)據(jù)源的基礎(chǔ)上生成各種不同的報(bào)表，以滿足不同層次的 需求。但當(dāng)我們使用數(shù)據(jù)透視表生成報(bào)告后，如果數(shù)據(jù)源變化

43、了，數(shù)據(jù)透視表報(bào)告的信息會(huì) 不會(huì)自動(dòng)跟著變化呢？ 當(dāng)更改數(shù)據(jù)源后，默認(rèn)情況下數(shù)據(jù)透視表并不會(huì)自動(dòng)更新。當(dāng)數(shù)據(jù)源的值更改過以后，我們 可以通過數(shù)據(jù)透視表的"選項(xiàng)"工具面板里的"數(shù)據(jù)"組的"刷新"命令來刷新數(shù)據(jù)透視表。 這樣數(shù)據(jù)透視表報(bào)告就變成最新的了。 但是"刷新"命令只是刷新數(shù)據(jù)透視表所引用的數(shù)據(jù)源的值。當(dāng)數(shù)據(jù)源的范圍變化了以后（比 如數(shù)據(jù)源增加了新的紀(jì)錄）則使用刷新命令就不能將數(shù)據(jù)范圍一起更新了?？梢酝ㄟ^數(shù)據(jù)透 視表的"選項(xiàng)"工具面板里的"數(shù)據(jù)"組的"更改數(shù)據(jù)源"命令來重新選擇數(shù)據(jù)透視表所引 用的數(shù)據(jù)范圍。 G.2 鏈接外部數(shù)據(jù)源 但如果數(shù)據(jù)源需要頻繁的增加新的記錄，每次更新數(shù)據(jù)透視表的時(shí)候都需要更新數(shù)據(jù)源，顯 然很麻煩。 37 / 46 Excel 2007 數(shù)據(jù)透視表應(yīng)用詳解 例如，如果系統(tǒng)每月生成一個(gè)基礎(chǔ)信息表，而我們希