２８．１關(guān)于數(shù)據(jù)透視表
靈氣透視表是用來從Ｅxcel數(shù)據(jù)列表、關(guān)系數(shù)據(jù)庫文件或OLAP多維數(shù)據(jù)集中的特殊字段中總結(jié)信息的分析工具。它是一種交互式報(bào)表，可以快速分類匯總和比較大量的數(shù)據(jù)，并可以隨時(shí)選擇其中頁、行和列中的不同元素，以快速查看源數(shù)據(jù)的不同統(tǒng)計(jì)結(jié)果，同時(shí)還可以隨意顯示和打印出你所感興趣區(qū)域的明細(xì)數(shù)據(jù)。
靈氣透視表有機(jī)地綜合了數(shù)據(jù)排序、篩選、分類匯總等數(shù)據(jù)分析的優(yōu)點(diǎn)，可方便地調(diào)整分類匯總的方式，靈活地以多種不同方式展示數(shù)據(jù)的特征。一張“數(shù)據(jù)透視表”僅靠鼠標(biāo)移到字段位置，即可變換出各種類型的報(bào)表。同時(shí)，數(shù)據(jù)透視表也是解決函數(shù)公式速度瓶頸的手段之一。因此，該工具是最常用、功能最全的Excel數(shù)據(jù)分析工具之一。
２８．１．１數(shù)據(jù)透視表的用途
數(shù)據(jù)透視表是一種大量數(shù)據(jù)快速匯總和建立交叉列表的交互式動(dòng)態(tài)表格，能幫助用戶分析、組織數(shù)據(jù)。例如，計(jì)算平均數(shù)、標(biāo)準(zhǔn)差，建立列聯(lián)表、計(jì)算百分比、建立新的數(shù)據(jù)了集等。建好數(shù)據(jù)透視表后，可以對數(shù)據(jù)透視表重新安排，以便從不同的角度查看數(shù)據(jù)。數(shù)據(jù)透視表的名字來源于它具有“透視”表格的能力，從大量看似無關(guān)的數(shù)據(jù)中尋找背后的聯(lián)系，從而將紛繁的數(shù)據(jù)轉(zhuǎn)化為有價(jià)值的信息，以供研究和決策所用。
總之，合理運(yùn)用數(shù)據(jù)透視表進(jìn)行計(jì)算與分析，能使許多復(fù)雜的問題簡單化并極大地提高工作效率。
２８．１．２ 一個(gè)簡單的例子
圖所示的數(shù)據(jù)列表顯示了一家貿(mào)易公司的銷售數(shù)據(jù)清單。清單中包括年份、季度、銷售地區(qū)、品名、數(shù)量、單價(jià)、金額；時(shí)間跨度為８個(gè)季度（２００５～２００６年）。只需幾步簡單操作，就可以將這張“平庸”的數(shù)據(jù)列表變成能夠提供有用信息的數(shù)據(jù)透視表。
銷售地區(qū) 銷售人員 品名 數(shù)量 單價(jià)￥ 銷售金額￥ 銷售年份 銷售季度
北京 蘇珊 按摩椅 13 800 10,400 2005 2
北京 蘇珊 顯示器 98 1,500 147,000 2005 3
北京 蘇珊 顯示器 49 1,500 73,500 2005 4
北京 蘇珊 顯示器 76 1,500 114,000 2005 1
北京 蘇珊 顯示器 33 1,500 49,500 2005 2
北京 蘇珊 液晶電視 53 5,000 265,000 2005 3
北京 蘇珊 液晶電視 47 5,000 235,000 2005 4
北京 蘇珊 液晶電視 1 5,000 5,000 2005 1
北京 白露 液晶電視 43 5,000 215,000 2005 2
北京 白露 液晶電視 34 5,000 170,000 2005 3
北京 白露 微波爐 27 500 13,500 2005 4
北京 白露 微波爐 69 500 34,500 2005 1
北京 白露 微波爐 24 500 12,000 2006 1
北京 白露 按摩椅 28 800 22,400 2006 2
北京 白露 按摩椅 45 800 36,000 2006 3
北京 趙琦 按摩椅 20 800 16,000 2006 4
北京 趙琦 按摩椅 68 800 54,400 2006 1
北京 趙琦 顯示器 77 1,500 115,500 2006 2
數(shù)據(jù)列表，用來創(chuàng)建一個(gè)數(shù)據(jù)透視表
求和項(xiàng):銷售金額￥ 品名
銷售人員 銷售年份 銷售季度 按摩椅 跑步機(jī) 微波爐 顯示器 液晶電視 總計(jì)
白露 2005 1 34,500 34,500
2 215,000 215,000
3 170,000 170,000
4 13,500 13,500
2005 匯總 48,000 385,000 433,000
2006 1 12,000 12,000
2 22,400 22,400
3 36,000 36,000
2006 匯總 58,400 12,000 70,400
白露 匯總 58,400 60,000 385,000 503,400
畢春艷 2006 1 11,000 36,000 460,000 507,000
2 19,500 114,000 120,000 253,500
3 38,000 73,500 45,000 156,500
4 67,200 79,500 225,000 371,700
2006 匯總 67,200 68,500 303,000 850,000 1,288,700
畢春艷 匯總 67,200 68,500 303,000 850,000 1,288,700
高偉 2006 1 187,000 9,500 43,500 340,000 580,000
2 2,400 74,800 9,500 86,700
3 74,400 100,500 174,900
4 162,800 106,500 90,000 359,300
2006 匯總 76,800 424,600 19,000 250,500 430,000 1,200,900
高偉 匯總 76,800 424,600 19,000 250,500 430,000 1,200,900
何慶 2006 1 94,600 94,600
2 123,200 123,200
3 66,000 66,000
2006 匯總 217,800 66,000 283,800
何慶 匯總 217,800 66,000 283,800
李兵 2006 1 228,800 228,800
2 66,000 66,000
3 132,000 132,000
4 15,400 2,500 17,900
2006 匯總 442,200 2,500 444,700
李兵 匯總 442,200 2,500 444,700
根據(jù)數(shù)據(jù)列表創(chuàng)建的數(shù)據(jù)透視表
此數(shù)據(jù)透視表顯示了不同銷售人員在不同年份所銷售的各種產(chǎn)品的銷售額匯總，數(shù)據(jù)透視表最后一行是所有銷售人員所銷售的各種產(chǎn)品的銷售額總計(jì)。
從圖中可以很容易找出原始數(shù)據(jù)清單所記錄的大多數(shù)信息，沒有顯示的數(shù)據(jù)信息僅為銷售數(shù)量和銷售地區(qū)。只要再將數(shù)據(jù)透視表做進(jìn)一步調(diào)整，就可以將這些信息也顯示出來。將銷售年份、銷售季度、銷售地區(qū)移到數(shù)據(jù)區(qū)以外，使銷售數(shù)量與銷售金額并排顯示，只需簡單地從銷售年份、季度、地區(qū)字段標(biāo)題左側(cè)的下拉列表框中選擇相應(yīng)的年份、季度、銷售地區(qū)即可查看不同時(shí)期和不同銷售地區(qū)的數(shù)據(jù)。如圖所示。
品名 (全部)
數(shù)量 (全部)
銷售年份 (全部)
銷售地區(qū) (全部)
數(shù)據(jù)
銷售人員 銷售季度 數(shù)量 銷售金額￥ 求和項(xiàng):銷售金額￥
白露 1 93 46500 46500
2 71 237400 237400
3 79 206000 206000
4 27 13500 13500
白露 匯總 270 503400 503400
畢春艷 1 138 507000 507000
2 139 253500 253500
3 134 156500 156500
4 182 371700 371700
畢春艷 匯總 593 1288700 1288700
高偉 1 201 580000 580000
2 56 86700 86700
3 160 174900 174900
4 163 359300 359300
高偉 匯總 580 1200900 1200900
何慶 1 43 94600 94600
2 56 123200 123200
3 44 66000 66000
何慶 匯總 143 283800 283800
李兵 1 104 228800 228800
2 30 66000 66000
3 60 132000 132000
4 12 17900 17900
李兵 匯總 206 444700 444700
林茂 1 161 354200 354200
2 37 23000 23000
3 113 169500 169500
4 56 71900 71900
林茂 匯總 367 618600 618600
蘇珊 1 77 119000 119000
2 46 59900 59900
3 151 412000 412000
4 96 308500 308500
蘇珊 匯總 370 899400 899400
楊光 1 27 135000 135000
2 52 78000 78000
3 129 334500 334500
4 105 157500 157500
楊光 匯總 313 705000 705000
趙琦 1 122 324400 324400
2 117 175500 175500
3 106 237500 237500
4 72 94000 94000
趙琦 匯總 417 831400 831400
總計(jì) 3259 6775900 6775900
２８．１．３ 數(shù)據(jù)透視表的數(shù)據(jù)組織
用戶可以從４種類型的數(shù)據(jù)源中來創(chuàng)建數(shù)據(jù)透視表。
１）Excel數(shù)據(jù)列表
２）外部數(shù)據(jù)源
３）進(jìn)行多重合并計(jì)算的獨(dú)立的Excel數(shù)據(jù)列表
４）其他的數(shù)據(jù)透視表
注意：如果以Excel數(shù)據(jù)列表作為數(shù)據(jù)源，則標(biāo)題行不能有空白單元格或合并的單元格，否則不能生成數(shù)據(jù)透視表，付出現(xiàn)錯(cuò)誤提示。
數(shù)據(jù)透視表中的術(shù)語：
數(shù)據(jù)源 從中創(chuàng)建數(shù)據(jù)透視表的數(shù)據(jù)列表或多維數(shù)據(jù)集。
軸 數(shù)據(jù)透視表中的一維，如行、列、頁。
列字段 信息的種類，造價(jià)于數(shù)據(jù)列表中列。
行字段 在數(shù)據(jù)透視表中具有行方向的字段。
頒頁符 數(shù)據(jù)透視表中進(jìn)行分頁的字段。
字段標(biāo)題 描述字段內(nèi)容的標(biāo)志?？梢酝ㄟ^拖動(dòng)字段標(biāo)題對數(shù)據(jù)透視表進(jìn)行透視。
項(xiàng)目 組成字段的成員。如圖中，２００５和２００６就是組成年份字段的項(xiàng)。
組 一組項(xiàng)目的集合，可以自動(dòng)或手動(dòng)地為項(xiàng)目組合。
透視 通過改變一個(gè)或多個(gè)字段的位置來重新安排數(shù)據(jù)透視表。
匯總函數(shù) Excel計(jì)算表格中數(shù)據(jù)的值的函數(shù)。文本和數(shù)值的默認(rèn)匯總函數(shù)為計(jì)數(shù)和求和。
分類匯總 數(shù)據(jù)透視表中對一行或一列單元格的分類匯總。
刷新 重新計(jì)算數(shù)據(jù)透視表，反映目前數(shù)據(jù)源的狀態(tài)。
２８．１．４ 數(shù)據(jù)透視表的工具欄
數(shù)據(jù)透視表的所有功能和特性都可以通過數(shù)據(jù)透視表工具欄來實(shí)現(xiàn)。
顯示數(shù)據(jù)透視表的工具欄有２種方法。
１）選擇“視圖”——“工具欄”——選擇數(shù)據(jù)透視表菜單項(xiàng)。
２）鼠標(biāo)右擊數(shù)據(jù)透視表——在彈出的菜單中選擇“顯示數(shù)據(jù)透視表工具欄”。
２８．２ 創(chuàng)建數(shù)據(jù)透視表
使用數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)б詣?chuàng)建數(shù)據(jù)透視表，啟用此向?qū)У姆椒ㄊ菃螕鬍xcel菜單欄中的“數(shù)據(jù)”——“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”。在該向?qū)У闹笇?dǎo)下，用戶只要按部就班地一步一步進(jìn)行操作，就可以輕松地完成數(shù)據(jù)透視表的創(chuàng)建。
它的操作步驟共分為３步。
步驟１．選擇數(shù)據(jù)源類型。
步驟２．選擇數(shù)據(jù)源區(qū)域。
步驟３．指定數(shù)據(jù)透視表位置。
２８．２．１步驟１指定數(shù)據(jù)源的類型
選定數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)镜匿N售數(shù)據(jù)清單中任意一個(gè)數(shù)據(jù)單元格，單擊菜單“數(shù)據(jù)”——“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”，出現(xiàn)“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А巢襟E之１”對話框。
該步驟幫助用戶確定數(shù)據(jù)源類型和報(bào)表類型。單擊不同選項(xiàng)的選項(xiàng)按鈕，對話框左側(cè)的圖像將會(huì)產(chǎn)生相應(yīng)變化。此處保留對默認(rèn)選項(xiàng)的選擇，即數(shù)據(jù)源類型為Excel數(shù)據(jù)列表，報(bào)表類型為數(shù)據(jù)透視表。
２８．２．２步驟２指定數(shù)據(jù)源的位置
指定了數(shù)據(jù)源類型后，單擊“下一步”按鈕，向?qū)@示第２個(gè)對話框，“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А巢襟E之２”，要求指定數(shù)據(jù)源的位置。
該步驟用于選定數(shù)據(jù)源區(qū)域。由于數(shù)據(jù)列表都是位于某個(gè)連續(xù)的單元格區(qū)域，所以，一般情況下Excel會(huì)自動(dòng)識(shí)別數(shù)據(jù)源所在的單元格區(qū)域，并填入到“選定區(qū)域”框。
如果Excel識(shí)別的數(shù)據(jù)源區(qū)域不正確，則需要用戶重新選定區(qū)域，單擊“選定區(qū)域”的折疊按鈕選定整個(gè)數(shù)據(jù)源。
如果數(shù)據(jù)源是當(dāng)前未打開的數(shù)據(jù)列表，可以單擊“瀏覽”按鈕打開另一個(gè)工作表，并選擇范圍。
２８．２．３步驟３指定數(shù)據(jù)透視表的顯示位置
在向?qū)У淖詈笠粋€(gè)對話框中，需要指定數(shù)據(jù)透視表的顯示位置。
如果要將數(shù)據(jù)透視表顯示到新的工作表上，可以選擇“新建工作表”選項(xiàng)按鈕，Excel將為數(shù)據(jù)透視表插入一個(gè)新的工作表。否則，可以選擇“現(xiàn)有工作表”選項(xiàng)按鈕，并且在文本框中指定開始單元格位置。
單擊“完成”按鈕之前，可以單擊“選項(xiàng)”按鈕對數(shù)據(jù)透視表格式和數(shù)據(jù)提前進(jìn)行設(shè)置。但是建議用戶在完成數(shù)據(jù)透視表的創(chuàng)建以后再使用“數(shù)據(jù)透視表選項(xiàng)”對話框設(shè)置這些選項(xiàng)，后者更加靈活方便。］
有兩種方法可以設(shè)置數(shù)據(jù)透視表的布局，這也是創(chuàng)建數(shù)據(jù)透視表過程中最關(guān)鍵的下一步。
方法１．在“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А巢襟E之３”對話框中單擊“布局”按鈕，在“布局”對話框中設(shè)置數(shù)據(jù)透視表的結(jié)構(gòu)。
方法２．單擊“完成”按鈕，創(chuàng)建一個(gè)空的數(shù)據(jù)透視表，然后使用“數(shù)據(jù)透視表字段列表”工具欄來布局?jǐn)?shù)據(jù)透視表。
１．使用對話框布局?jǐn)?shù)據(jù)透視表
當(dāng)用戶在“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А巢襟E之３”對話框內(nèi)單擊“布局”按鈕，會(huì)出現(xiàn)“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А季?#8221;對話框。
銷售數(shù)據(jù)清單中各列標(biāo)題作為按鈕出現(xiàn)在對話框的右半部分。用鼠標(biāo)拖支這些按鈕，將其按自己的設(shè)計(jì)要求放置在左邊圖中相應(yīng)的位置就可以構(gòu)造出數(shù)據(jù)透視表。
從結(jié)構(gòu)上看，數(shù)據(jù)透視表分為４個(gè)部分。
１）頁：此標(biāo)志區(qū)域中按鈕將作為數(shù)據(jù)透視表的分頁符。
２）行：此標(biāo)志區(qū)域中按鈕將作為數(shù)據(jù)透視表的行字段。
３）列：此標(biāo)志區(qū)域中按鈕將作為數(shù)據(jù)透視表的顯示匯總的數(shù)據(jù)。
４）數(shù)據(jù)：此標(biāo)志區(qū)域中按鈕將作為數(shù)據(jù)透視表的顯示匯總的數(shù)據(jù)。
將“銷售人員”、“銷售年份”、“銷售季度”字段按鈕拖動(dòng)到“行”區(qū)域；將“品名”字段按鈕拖動(dòng)到“列”區(qū)域；將“銷售金額”字段按鈕拖動(dòng)到“數(shù)據(jù)”區(qū)域。
單擊“確定”按鈕關(guān)閉“布局”對話框，最后單擊“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А巢襟E之３”對話框的“完成”按鈕，即可創(chuàng)建出數(shù)據(jù)透視表。］
２．使用“數(shù)據(jù)透視表字段列表”工具欄布局?jǐn)?shù)據(jù)透視表
用戶可以使用“數(shù)據(jù)透視表字段列表”工具欄在工作表中直接來布局?jǐn)?shù)據(jù)透視表。
如果用戶在“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А巢襟E之３”對話框中沒有利用“布局”命令來做相關(guān)設(shè)置，而是直接單擊“完成”按鈕，則Excel將顯示一張空的數(shù)據(jù)透視表。單擊空表區(qū)域內(nèi)的任意位置，將會(huì)出現(xiàn)放置不同字段類型的提示，并且顯示“數(shù)據(jù)透視表字段列表”。
在“數(shù)據(jù)透視表字段列表”內(nèi)將“銷售人員”，“銷售年份”，“銷售季度”依次拖入“將行字段拖至此處”的區(qū)域。
如果字段列表對話框沒有出現(xiàn)，可以先選中數(shù)據(jù)透視表，然后再單擊數(shù)據(jù)透視表工具欄“顯示字段列表”按鈕，也可以在數(shù)據(jù)透視表內(nèi)單擊鼠標(biāo)右鍵，選擇顯示字段列表，激活字段列表對話框。
在“數(shù)據(jù)透視表字段列表”內(nèi)將“品名”拖入“將列字段拖至此處”的區(qū)域；將“銷售金額￥”拖入“請將數(shù)據(jù)項(xiàng)拖至此處”的區(qū)域。
此外，還可以使用“數(shù)據(jù)透視表字段列表”工具欄的“添加到”按鈕把字段添加進(jìn)報(bào)表。單擊“數(shù)據(jù)透視表字段列表”中的“數(shù)量”，在下拉框中將字段位置從“行區(qū)域”改為“數(shù)據(jù)區(qū)域”，然后單擊“添加到”按鈕。
２８．２．４創(chuàng)建動(dòng)態(tài)的數(shù)據(jù)透視表
用戶在完成數(shù)據(jù)透視表后，如果數(shù)據(jù)源增加了新的行或列，即使刷新數(shù)據(jù)透視表，新增的數(shù)據(jù)仍無法出現(xiàn)在數(shù)據(jù)透視表中。為了避免這種情況的發(fā)生，可以為數(shù)據(jù)源定義名稱或使用數(shù)據(jù)列表功能來獲得動(dòng)態(tài)的數(shù)據(jù)源，從而來創(chuàng)建動(dòng)態(tài)的數(shù)據(jù)透視表。
１．定義名稱法創(chuàng)建動(dòng)態(tài)的數(shù)據(jù)透視表
使用定義名稱的方法來創(chuàng)建動(dòng)態(tài)的數(shù)據(jù)透視表，首先要使用一個(gè)動(dòng)態(tài)的公式定義數(shù)據(jù)透視表的數(shù)據(jù)源。當(dāng)一個(gè)新的記錄添加到表格中時(shí)，數(shù)據(jù)源會(huì)自動(dòng)擴(kuò)展。然后將定義的名稱范圍用于數(shù)據(jù)透視表，從而創(chuàng)建動(dòng)態(tài)的數(shù)據(jù)透視表。
示例２８．１定義名稱創(chuàng)建動(dòng)態(tài)數(shù)據(jù)透視表
在圖所示的銷售明細(xì)表中定義名稱data=OFFSET（銷售明細(xì)表!$A$1,0,0,COUNTA（銷售明細(xì)表!$A:$A),COUNTA(銷售明細(xì)表!$1:$!))。
銷售地區(qū) 銷售人員 品名 數(shù)量 單價(jià)￥ 銷售金額￥ 銷售年份 銷售季度
北京 蘇珊 按摩椅 13 800 10,400 2005 2
北京 蘇珊 顯示器 98 1,500 147,000 2005 3
北京 蘇珊 顯示器 49 1,500 73,500 2005 4
北京 蘇珊 顯示器 76 1,500 114,000 2005 1
北京 蘇珊 顯示器 33 1,500 49,500 2005 2
北京 蘇珊 液晶電視 53 5,000 265,000 2005 3
北京 蘇珊 液晶電視 47 5,000 235,000 2005 4
北京 蘇珊 液晶電視 1 5,000 5,000 2005 1
北京 白露 液晶電視 43 5,000 215,000 2005 2
北京 白露 液晶電視 34 5,000 170,000 2005 3
北京 白露 微波爐 27 500 13,500 2005 4
北京 白露 微波爐 69 500 34,500 2005 1
北京 白露 微波爐 24 500 12,000 2006 1
北京 白露 按摩椅 28 800 22,400 2006 2
北京 白露 按摩椅 45 800 36,000 2006 3
北京 趙琦 按摩椅 20 800 16,000 2006 4
北京 趙琦 按摩椅 68 800 54,400 2006 1
北京 趙琦 顯示器 77 1,500 115,500 2006 2
北京 趙琦 液晶電視 41 5,000 205,000 2006 3
北京 趙琦 顯示器 52 1,500 78,000 2006 4
北京 趙琦 液晶電視 54 5,000 270,000 2006 1
北京 趙琦 顯示器 40 1,500 60,000 2006 2
定義動(dòng)態(tài)數(shù)據(jù)源
有關(guān)定義名稱的更多內(nèi)容可以參閱第１１章。
將定義的名稱范圍用于數(shù)據(jù)透視表。
步驟１．單擊銷售明細(xì)表中的任意一個(gè)有效數(shù)據(jù)單元格，單擊菜單“數(shù)據(jù)”——“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”。
步驟２．在“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А巢襟E之１”對話框中選擇“Microsoft office Excel數(shù)據(jù)列表或數(shù)據(jù)庫”并單擊“下一步”
步驟３．在“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А巢襟E之２”對話框的選定區(qū)域框內(nèi)，輸入范圍名稱data，單擊“完成”按鈕。
步驟４．在工作表中，將“數(shù)據(jù)透視表字段列表”對話框內(nèi)的字段按鈕拖至數(shù)據(jù)透視表中行、列和數(shù)據(jù)區(qū)域。
如此即完成了動(dòng)態(tài)數(shù)據(jù)透視表的創(chuàng)建。用戶可以向作為數(shù)據(jù)源的銷售明細(xì)表中添加一些新記錄，如新增一條記錄，“銷售地區(qū)”為“深圳”、“銷售人員”為“張林波”。在數(shù)據(jù)透視表中單擊鼠標(biāo)右鍵，在彈出的快捷菜單中單擊“刷新數(shù)據(jù)”命令，即可見到新增的數(shù)據(jù)。
注意：由于在數(shù)據(jù)源“銷售明細(xì)表”中添加的新記錄只有銷售地區(qū)和銷售人員的數(shù)據(jù)，而沒有相應(yīng)的增加銷售年份、銷售季度、品名以及數(shù)量、金額數(shù)據(jù)，因此數(shù)據(jù)透視表中銷售年份等字段會(huì)顯示“（空白）”。
２．使用數(shù)據(jù)列表功能創(chuàng)建動(dòng)態(tài)的數(shù)據(jù)透視表
列表功能Excel 2003中新增功能，利用列表對數(shù)據(jù)源的自動(dòng)擴(kuò)展可以創(chuàng)建動(dòng)態(tài)的數(shù)據(jù)透視表。
示例２８．２ 使用數(shù)據(jù)列表功能創(chuàng)建動(dòng)態(tài)數(shù)據(jù)透視表
在圖（上圖）所示的銷售明細(xì)表中操作如下。
步驟１．在“銷售明細(xì)表”中單擊任意一個(gè)有效數(shù)據(jù)單元格，單擊菜單“數(shù)據(jù)”——“列表”——鼠標(biāo)指向“創(chuàng)建列表”并單擊它。
步驟２．單擊“確定”按鈕即可創(chuàng)建一張列表。
步驟３．在創(chuàng)建的列表內(nèi)單擊任意一個(gè)有效數(shù)據(jù)單元格，在菜單欄上依次單擊“數(shù)據(jù)”——“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”，在“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А巢襟E之１”對話框中選擇“Microsoft Office Excel數(shù)據(jù)列表或數(shù)據(jù)庫”，單擊“完成”。
這樣，利用列表創(chuàng)建的動(dòng)態(tài)數(shù)據(jù)透視表完成，用戶可以向銷售明細(xì)表中添加一些新記錄，如：新增記錄中的“銷售地區(qū)”為“深圳”、“銷售人員”為“張林波”。在數(shù)據(jù)透視表中單擊右鍵，在彈出的快捷菜單中單擊“刷新數(shù)據(jù)”命令，即可見到新增的數(shù)據(jù)。
２８．３改變數(shù)據(jù)透視表的布局
對于已經(jīng)完成的數(shù)據(jù)透視表，用戶在任何時(shí)候都只需拖支字段按鈕就可以重新安排透視布局，滿足新的要求。例如，用戶想生成按銷售年份統(tǒng)計(jì)銷售金額的報(bào)表，以圖所示的數(shù)據(jù)透視表為例，將“銷售季度”字段按鈕拖至“銷售人員”的左側(cè)，“銷售年份”字段按鈕拖至“銷售季度”的左側(cè)。
現(xiàn)在，“銷售人員”出現(xiàn)在“銷售年份”及“銷售季度”的組合中，并且按照“銷售年份”、“銷售季度”進(jìn)行分類匯總。
２８．３．１數(shù)據(jù)透視表頁面區(qū)域的使用
當(dāng)字段顯示在列區(qū)域行區(qū)域上時(shí)，滾動(dòng)數(shù)據(jù)透視表就可以看到字段中的所有項(xiàng)。然而，當(dāng)字段位于頁面區(qū)域中時(shí)，則一次只能顯示一項(xiàng)。要查看字段的其他項(xiàng)，可以在該字段的下拉列表框中依次進(jìn)行選擇。
１．顯示頁面區(qū)域中字段的匯總
數(shù)據(jù)透視表中每個(gè)字段的下拉列表框中，首先項(xiàng)都是“全部”。選擇該選項(xiàng)可以顯示發(fā)球該字段所有項(xiàng)目的信息。例如，將圖所示數(shù)據(jù)透視表頁面區(qū)域中的“銷售地區(qū)”、“品名”、“銷售年份”字段都選擇“全部”，則可以得到每個(gè)銷售人員在所有年份和銷售地區(qū)銷售所有品名的匯總數(shù)據(jù)。
２．?dāng)?shù)據(jù)透視表的分頁顯示功能
雖然數(shù)據(jù)透視表可以包含多個(gè)頁面區(qū)域，但通常情況下只顯示其中的一個(gè)頁面數(shù)據(jù)。利用數(shù)據(jù)透視表的分面顯示功能，用戶就可以創(chuàng)建一系列鏈接在一起的數(shù)據(jù)透視表，每張表顯示頁字段的一項(xiàng)。
示例２８．３分頁顯示數(shù)據(jù)透視表
要在圖所示的數(shù)據(jù)透視表中創(chuàng)建分頁顯示，可以參照以下步驟。
步驟１．單擊數(shù)據(jù)透視表工具欄中的“數(shù)據(jù)透視表”。
步驟２．在彈出的快捷菜單中單擊“分頁顯示”命令。
步驟３．在“分頁顯示”對話框中單擊“銷售地區(qū)”。
步驟４．最后單擊“確定”按鈕，可將“銷售地區(qū)”字段中的每個(gè)銷售地區(qū)分頁顯示在不同的工作表中，并且按照“銷售地區(qū)”字段的各項(xiàng)對工作表命名。
２８．３．２整理數(shù)據(jù)透視表字段
數(shù)據(jù)透視表完成后，用戶可以通過對數(shù)據(jù)透視表字段的整理來滿足自己對數(shù)據(jù)透視表格式上的需求。
１．整理復(fù)合字段
示例２８．４設(shè)置數(shù)據(jù)透視表的數(shù)據(jù)字段并排顯示
如果數(shù)據(jù)透視表的數(shù)據(jù)區(qū)域中垂直顯示“銷售金字￥”、“數(shù)量”兩個(gè)字段，為了便于讀取和比較數(shù)據(jù)，用戶可以重新安排數(shù)據(jù)透視表的字段。
單擊“數(shù)據(jù)”按鈕，按著鼠標(biāo)左鍵并拖拉“數(shù)據(jù)”按鈕到有“匯總”一詞的單元格，松開鼠標(biāo)左鍵。
此時(shí)兩個(gè)數(shù)據(jù)字段成水平位置排列。
２．重命名字段
當(dāng)用戶向數(shù)據(jù)區(qū)域添加字段后，它們都將被重命名，例如“銷售金額￥”變成了“求和項(xiàng)：銷售金額￥”或“計(jì)數(shù)項(xiàng)”銷售金額￥”，這樣就會(huì)加大字段所在列的列寬，影響表格的美觀。
下面介紹兩種字段重命名的方法：
１）單擊數(shù)據(jù)透視表中的標(biāo)題單元格“求和項(xiàng)：銷售金額￥”，輸入新標(biāo)題“金額合計(jì)”，按下ＥＮＴＥＲ鍵，這種方法是最簡便易行的
２）如果用戶要保持原有字段的名稱不變，可以采用替換的方法。選中數(shù)據(jù)透視表的標(biāo)題單元格，在菜單欄上依次單擊“編輯”——替換，在彈出的“查找和替換對話框中的“查找內(nèi)容文本框中輸入“求和項(xiàng)”，在“替換為”文本框中輸入一個(gè)空格，單擊“全部替換”，完成標(biāo)題重命名。
注意：數(shù)據(jù)透視表中每個(gè)字段的名稱必須唯一，Excel不接受任意兩個(gè)字段具有相同的名稱。
２８．３．３整理數(shù)據(jù)透視表數(shù)據(jù)
１．顯示所有數(shù)據(jù)項(xiàng)
在數(shù)據(jù)透視表中，每個(gè)字段右側(cè)都有一個(gè)小箭頭，單擊它可打開一個(gè)數(shù)據(jù)項(xiàng)下拉列表，選擇“（全部顯示）”復(fù)選框?qū)@示全部數(shù)據(jù)項(xiàng)。
２．隱藏?cái)?shù)據(jù)項(xiàng)
如果用戶不需要顯示所有數(shù)據(jù)項(xiàng)的數(shù)據(jù)，可以將不需要的數(shù)據(jù)項(xiàng)隱藏。單擊數(shù)據(jù)項(xiàng)下拉列表中“（全部顯示）”復(fù)選框，至少選中其中一個(gè)數(shù)據(jù)項(xiàng)的復(fù)選框，并單擊“確定”按鈕。
３．隱藏頁字段數(shù)據(jù)
頁字段下拉列表中不是復(fù)選框，用戶不能同時(shí)選中多個(gè)數(shù)據(jù)項(xiàng)，只能選擇“全部”項(xiàng)或可視數(shù)據(jù)項(xiàng)中的一項(xiàng)。下面介紹兩種方法可以隱藏或顯示多個(gè)數(shù)據(jù)項(xiàng)。
示例２８．５隱藏?cái)?shù)據(jù)透視表的頁字段數(shù)據(jù)
方法１．利用“數(shù)據(jù)透視表字段”對話框中的“隱藏?cái)?shù)據(jù)項(xiàng)”。
步驟１．雙擊頁字段按鈕“品名”調(diào)出“數(shù)據(jù)透視表字段”對話框。
步驟２．在“隱藏?cái)?shù)據(jù)項(xiàng)”列表中單擊你想要隱藏的數(shù)據(jù)項(xiàng)，如“按摩椅”和“微波爐”。
步驟３．單擊“確定”按鈕完成。
方法２ 利用頁字段的下拉列表框。
步驟１．將頁字段按鈕“品名”拖到行區(qū)域。
步驟２．單擊“品名”字段的下拉按鈕，在出現(xiàn)的下拉列表框中取消勾選“全部顯示”，再分別取消“按摩椅”和“微波爐”的勾選。
步驟３．單擊“確定”按鈕，將頁字段按鈕“品名”拖回原處，頁字段“品名”的選項(xiàng)變?yōu)?#8220;多項(xiàng)”。
４．清除數(shù)據(jù)透視表原有數(shù)據(jù)項(xiàng)
數(shù)據(jù)透視表的數(shù)據(jù)源改變后，會(huì)導(dǎo)致字段下拉列表中有些無用的數(shù)據(jù)項(xiàng)存在。例如有些銷售人員已經(jīng)離開公司，但他們的名字仍然會(huì)在數(shù)據(jù)透視表的數(shù)據(jù)項(xiàng)中存在。即使用戶對數(shù)據(jù)透視表進(jìn)行刷新，這些名字仍然會(huì)與新名字同時(shí)顯示。
示例２８．６徹底清除數(shù)據(jù)透視表中不應(yīng)存在的數(shù)據(jù)項(xiàng)
在下面的數(shù)據(jù)項(xiàng)下拉列表中，“李兵”已經(jīng)被“劉坤”代替，但他的名字仍然存在。
清除原有的數(shù)據(jù)項(xiàng)的操作方法如下：
步驟１．將數(shù)據(jù)透視字段“銷售人員”字段拖到數(shù)據(jù)透視表以外的區(qū)域。
步驟２．在數(shù)據(jù)透視表內(nèi)單擊右鍵，刷新數(shù)據(jù)。
步驟３．最后將“銷售人員”字段拖回到原來的行區(qū)域位置。
２８．４數(shù)據(jù)透視表的刷新
源數(shù)據(jù)發(fā)生變化后，數(shù)據(jù)透視表本身并不會(huì)自動(dòng)刷新。要對數(shù)據(jù)透視表進(jìn)行刷新，只需在數(shù)據(jù)透視表內(nèi)右鍵，單擊“刷新數(shù)據(jù)”即可。
２８．４．１在打開文件時(shí)刷新
用戶如果希望Excel在每次打開數(shù)據(jù)透視表所在的工作表時(shí)都進(jìn)行數(shù)據(jù)刷新，則可以這樣設(shè)置：
步驟１．右鍵單擊數(shù)據(jù)透視表，在彈出的快捷菜單中單擊“表格選項(xiàng)”。
步驟２．在“數(shù)據(jù)透視表選項(xiàng)”對話框中勾選“數(shù)據(jù)選項(xiàng)”的“打開時(shí)刷新”復(fù)選框。
步驟３．單擊“確定”按鈕。
２８．４．２刷新鏈接在一起的數(shù)據(jù)透視表
當(dāng)數(shù)據(jù)透視表用作其他數(shù)據(jù)透視表的數(shù)據(jù)源時(shí)，對其中任何一張數(shù)據(jù)透視表進(jìn)行刷新，都會(huì)引起所有鏈接在一起的數(shù)據(jù)透視表進(jìn)行刷新。
２８．４．３刷新引用外部數(shù)據(jù)的數(shù)據(jù)透視表
如果創(chuàng)建的數(shù)據(jù)透視表是基于對外部數(shù)據(jù)的查詢，Excel可以在用戶工作時(shí)在后臺(tái)中執(zhí)行數(shù)據(jù)查詢。
步驟１．右鍵單擊數(shù)據(jù)透視表，在彈出的快捷菜單中單擊“表格選項(xiàng)”。
步驟２．在“數(shù)據(jù)透視表選項(xiàng)”對話框中勾選“外部數(shù)據(jù)選項(xiàng)”中的“后臺(tái)查詢”復(fù)選框。
步驟３．單擊“確定”按鈕。
注意：“外部數(shù)據(jù)選項(xiàng)”只對由外部數(shù)據(jù)創(chuàng)建的數(shù)據(jù)透視表可用，否則“外部數(shù)據(jù)選項(xiàng)”下的復(fù)選框均為灰色。
２８．４．４定時(shí)刷新
如果要讓數(shù)據(jù)透視表自動(dòng)地定時(shí)刷新，可以這樣設(shè)置：
步驟１．右鍵單擊數(shù)據(jù)透視表，在彈出的快捷菜單中單擊“表格選項(xiàng)”。
步驟２．在“數(shù)據(jù)透視表選項(xiàng)”對話框中勾選“數(shù)據(jù)源選項(xiàng)”中的“刷新頻率”復(fù)選框，并選擇以分鐘為單位指定刷新的時(shí)間間隔。
步驟３．單擊“確定”按鈕。
這一選項(xiàng)也只對由外部數(shù)據(jù)創(chuàng)建的數(shù)據(jù)透視表適用。
２８．５設(shè)置數(shù)據(jù)透視表的格式
用戶可用設(shè)置單元格格式的方法來修改數(shù)據(jù)透視表中單元格的外觀。例如，改變字體、字號、設(shè)置數(shù)字格式、填充顏色等。如果要避免刷新數(shù)據(jù)后發(fā)生格式丟失，可按如下步驟加以解決。
步驟１．右鍵單擊數(shù)據(jù)透視表。
步驟２．在彈出的快捷菜單中單擊“表格選項(xiàng)”。
步驟３．在“靈氣透視表選項(xiàng)”對話框的“格式選項(xiàng)”中勾選“保留格式”復(fù)選框。
步驟４．最后單擊“確定”按鈕。
２８．５．１數(shù)據(jù)透視表自動(dòng)套用格式化
Excel為數(shù)據(jù)透視表提供了超過20種的自動(dòng)套用格式選項(xiàng)。要應(yīng)用這些格式，可在數(shù)據(jù)透視表中選擇任意單元格，然后單擊數(shù)據(jù)透視表工具欄上的“設(shè)置報(bào)告格式”按鈕，在“自動(dòng)套用格式”對話框中選擇一種報(bào)表格式。此外，也可以單擊Excel菜單“格式”——“自動(dòng)套用格式”來設(shè)置。
要取消現(xiàn)有數(shù)據(jù)透視表中的自動(dòng)套用格式，右鍵單擊數(shù)據(jù)透視表，在彈出的快捷菜單中單擊“表格選項(xiàng)”，在“數(shù)據(jù)透視表選項(xiàng)”對話框中取消勾選“自動(dòng)套用格式”復(fù)選框，單擊“確定”按鈕。
２８．５．２改變數(shù)據(jù)透視表中所有單元格的數(shù)字格式
如果用戶要改變數(shù)據(jù)透視表中所有單元格的數(shù)字格式，只需選中這些單元格再設(shè)置單元格格式，可以參見以下步驟。
步驟１．鼠標(biāo)單擊數(shù)據(jù)透視表。
步驟２．按下CTRL+A組合鍵，選中整個(gè)數(shù)據(jù)透視表，單擊鼠標(biāo)右鍵。
步驟３．在彈出的快捷菜單中單擊“設(shè)置單元格格式”。
步驟４．在彈出“單元格格式”對話框中選擇“數(shù)字”選項(xiàng)卡進(jìn)行相關(guān)的數(shù)字格式設(shè)置。
２８．６在數(shù)據(jù)透視表中排序
２８．６．１使用手動(dòng)排序
用戶可以使用Excel中的“排序”功能對數(shù)據(jù)透視表的字段項(xiàng)進(jìn)行排序，該字段項(xiàng)所有的實(shí)例都將會(huì)進(jìn)行排序，并在改變數(shù)據(jù)透視表布局時(shí)保持既定的排列順序。
示例２８．７ 在數(shù)據(jù)透視表中進(jìn)行手動(dòng)排序
品名 (全部)
數(shù)量 (全部)
銷售年份 (全部)
銷售地區(qū) (全部)
數(shù)據(jù)
銷售人員 銷售季度 數(shù)量 銷售金額￥ 求和項(xiàng):銷售金額￥
白露 1 93 46500 46500
2 71 237400 237400
3 79 206000 206000
4 27 13500 13500
白露 匯總 270 503400 503400
畢春艷 1 138 507000 507000
2 139 253500 253500
3 134 156500 156500
4 182 371700 371700
畢春艷 匯總 593 1288700 1288700
高偉 1 201 580000 580000
2 56 86700 86700
3 160 174900 174900
4 163 359300 359300
高偉 匯總 580 1200900 1200900
要對圖所示的數(shù)據(jù)透視表中按“銷售季度”項(xiàng)進(jìn)行降序排序，可以通過以下步驟來實(shí)現(xiàn)。］
步驟１．單擊單元格Ｂ６（銷售季度）。
步驟２．在菜單欄上依次單擊“數(shù)據(jù)”——“排序”。
步驟３．在“排序”對話框中選擇“降序”。
步驟４．單擊“確定”按鈕結(jié)束。
Excel會(huì)重新排列“銷售季度”的顯示順序，在數(shù)據(jù)透視表中任何出現(xiàn)“銷售季度”的地方均按“4”、3、2、1 的順序排列。
２８．６．２ 使用自動(dòng)排序
用戶還可以對希望排序的字段使用自動(dòng)排序。自動(dòng)排序特性與標(biāo)準(zhǔn)的排序功能一樣，都會(huì)對該字段項(xiàng)所有的實(shí)例進(jìn)行排序，只是增加了附加選項(xiàng)，可以按照數(shù)據(jù)的值對字段項(xiàng)進(jìn)項(xiàng)排序。
示例２８．８在數(shù)據(jù)透視表中進(jìn)行自動(dòng)排序
數(shù)據(jù)透視表中按“銷售人員”字段進(jìn)行降序的自動(dòng)排序，可按以下步驟進(jìn)行。
步驟１．雙擊“銷售人員”字段按鈕，出現(xiàn)“數(shù)據(jù)透視表字段”對話框。
步驟２．單擊“高級”按鈕，出現(xiàn)“數(shù)據(jù)透視表字段高級選項(xiàng)”對話框。
步驟３．自動(dòng)排序選項(xiàng)選擇“降序”單選按鈕，“使用字段”下拉列表框中選擇“銷售人員”，單擊“確定”按鈕。
２８．６．３使用非標(biāo)準(zhǔn)順序排序
用戶如果既不希望按降序又不希望按升序排序，可以使用自定義的排序方法進(jìn)行排序。
２８．７數(shù)據(jù)透視表的項(xiàng)目組合
數(shù)據(jù)透視表能夠自動(dòng)地在外部字段的標(biāo)題下對內(nèi)部字段的項(xiàng)進(jìn)行組合，并可按照用戶的要求來創(chuàng)建各個(gè)內(nèi)部字段的分類匯總。在圖中“銷售人員”發(fā)球外部字段，而“銷售季度”則發(fā)球內(nèi)部字段。
項(xiàng)目組合是數(shù)據(jù)透視表的一個(gè)非常有用的特性，Excel提供了以下幾種對項(xiàng)進(jìn)行組合的選擇。
２８．７．１組合數(shù)據(jù)透視表的指定項(xiàng)
求和項(xiàng):訂單金額 銷售人員
銷售途徑2 銷售途徑 畢春艷 高偉 何慶 林茂 蘇珊 楊光 張林波 總計(jì)
國際業(yè)務(wù) 國際業(yè)務(wù) 1597.2 3058.82 10779.6 18298.72 8794.74 8623.68 14445.3 65598.06
國內(nèi)業(yè)務(wù) 國內(nèi)市場 7840.91 1423 23681.12 30401.68 4235.4 16609.97 6597.34 90789.42
送貨上門 86232.2 47129.65 173558.39 143187.25 51456.1 124045.36 92711.21 718320.16
網(wǎng)絡(luò)銷售 19989.48 16664.78 61067.99 26466.4 7753.39 26794.78 9038.42 167775.24
郵購業(yè)務(wù) 1303.2 516 7157.21 7409.63 288 6426.3 240.4 23340.74
總計(jì) 116962.99 68792.25 276244.31 225763.68 72527.63 182500.09 123032.67 1065823.62
組合前的數(shù)據(jù)透視表
圖所示的示例文件“２８．７指定項(xiàng)組合．xls”可以從Excel Home網(wǎng)站下載。
如果用戶在圖所示的數(shù)據(jù)透視表中，希望將“國內(nèi)市場”、“送貨上門”、“網(wǎng)絡(luò)銷售”、“郵購業(yè)務(wù)”的銷售數(shù)據(jù)組合在一起，合稱為“國內(nèi)業(yè)務(wù)”，可參考如下步驟創(chuàng)建此項(xiàng)組合。
步驟１．在數(shù)據(jù)透視表中，鼠標(biāo)單擊“國內(nèi)市場”，按住鍵盤上的CTRL鍵，用鼠標(biāo)依次單擊“送貨上門”、“網(wǎng)絡(luò)銷售”、“郵購業(yè)務(wù)”標(biāo)題。
步驟２．單擊鼠標(biāo)右鍵，在彈出的快捷菜單中單擊“組及顯示明細(xì)數(shù)據(jù)”，鼠標(biāo)指向“組合”并單擊它；Excel創(chuàng)建了新的字段標(biāo)題，并自動(dòng)命名為“銷售途徑2”，并且將選中的項(xiàng)組合到新的“數(shù)據(jù)組1”的項(xiàng)中。
步驟３．選中標(biāo)為“數(shù)據(jù)組１”的單元格，輸入新的名稱“國內(nèi)業(yè)務(wù)”。
２８．７．２數(shù)字項(xiàng)組合
要將字段中的數(shù)字項(xiàng)組合，可以在分組對話框中進(jìn)行設(shè)置。例如，“銷售季度”字段組合，在數(shù)據(jù)透視表中單擊該字段的任意項(xiàng)，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中單擊“組及顯示明細(xì)數(shù)據(jù)”鼠標(biāo)指向“組合”并單擊它，出現(xiàn)“組合”對話框。
如果用戶希望將每２個(gè)季度創(chuàng)建為一組，可以在“起始于”文本框中輸入“１”，在“終止于”文本框中輸入“４”，在“步長”文本框中輸入“２”，單擊“確定”按鈕。
２８．７．３按日期或時(shí)間項(xiàng)組合
求和項(xiàng):訂單金額 銷售人員
年 訂單日期 畢春艷 高偉 何慶 林茂 蘇珊 楊光 張林波 總計(jì)
2003年 7月 996.62 7,327.52 8,323.85 1,863.40 1,614.88 584.00 20,710.27
8月 479.40 642.20 3,452.08 4,948.87 2,738.22 3,411.20 7,196.00 22,867.97
9月 1,206.60 1,420.00 2,317.20 4,193.12 3,866.40 3,260.00 16,263.32
10月 3,624.48 1,393.20 10,706.09 13,519.93 288.00 6,426.30 4,126.50 40,084.50
11月 9,087.48 3,901.08 3,304.78 6,898.66 1,586.40 7,834.90 568.80 33,182.10
12月 834.20 9,314.10 3,328.00 13,936.60 3,850.54 7,708.08 3,425.40 42,396.92
2004年 1月 11,025.34 716.72 6,365.44 15,955.81 2,122.92 12,234.35 9,664.39 58,084.97
2月 192.00 9,532.81 14,487.58 2,704.24 407.70 4,118.14 31,442.47
3月 3,891.00 2,520.40 15,324.58 10,645.14 756.00 5,243.77 6,209.70 44,590.59
4月 8,618.96 2,164.30 7,007.70 7,718.50 1,419.00 2,346.50 29,274.96
5月 5,053.66 4,500.27 13,359.05 9,977.74 3,061.90 9,355.96 4,417.90 49,726.48
6月 2,765.30 2,147.40 22,156.88 5,404.17 3,464.81 5,150.60 1,922.32 43,011.48
7月 3,569.58 7,365.40 1,900.76 4,795.69 493.00 8,666.67 671.34 27,462.44
8月 8,004.99 1,434.00 1,739.75 17,134.24 3,569.94 16,286.69 7,920.85 56,090.46
9月 8,689.36 4,176.40 12,533.50 9,301.19 1,418.71 7,441.55 1,057.80 44,618.51
10月 5,898.50 7,581.32 12,494.91 10,953.15 5,409.50 8,990.15 9,415.00 60,742.53
11月 1,890.50 484.25 13,965.81 5,177.05 4,016.35 7,513.90 5,452.75 38,500.61
12月 228.00 507.00 16,938.17 13,816.10 6,090.50 13,140.02 3,757.33 54,477.12
日期按原始項(xiàng)目排列的數(shù)據(jù)透視表
如圖所示的數(shù)據(jù)透視表顯示了按訂單日期統(tǒng)計(jì)的銷售人員的訂單金額。在實(shí)際工作中，因?yàn)槿掌谌匀话凑赵柬?xiàng)目按天排列，難以體現(xiàn)出分段時(shí)間的統(tǒng)計(jì)信息。在此種情況下，用戶可以對日期項(xiàng)進(jìn)行分組使表格變得更有意義，具體步驟如下。
步驟１．在數(shù)據(jù)透視表“訂單日期”字段內(nèi)單擊鼠標(biāo)右鍵，在彈出的快捷菜單中單擊“組及顯示明細(xì)數(shù)據(jù)”，鼠標(biāo)指向“組合”并單擊它。
步驟２．在出現(xiàn)的“分組”對話框中，保持起始和終止日期的默認(rèn)設(shè)置，再單擊“步長”列表框中的“年”，按CTRL鍵同時(shí)再選中列表框中的“月”。
步驟３．單擊“分組”對話框中的“確定”按鈕。
２８．７．４取消項(xiàng)目組合
如果不需要某個(gè)組合，可以選中這個(gè)組合單擊鼠標(biāo)右鍵，在彈出的快捷菜單中單擊“組及顯示明細(xì)數(shù)據(jù)”，鼠標(biāo)指向“取消組合”并單擊它，即可刪除組合，將字段恢復(fù)到組合前的狀態(tài)。
２８．７．５組合數(shù)據(jù)時(shí)遇到的問題
當(dāng)用戶試圖對一個(gè)日期或字段進(jìn)行分組時(shí)，可能會(huì)得到一個(gè)錯(cuò)誤信息警告，內(nèi)容為“選定區(qū)域不能分組”。
進(jìn)行分組字段的數(shù)據(jù)源中如果包含有空白的單元格或文本型的日期、數(shù)字，在組合時(shí)會(huì)提示用戶選定的區(qū)域不能分組。解決這個(gè)問題可以參見以下步驟。
步驟１．在空白單元格填充日期或數(shù)字（如有必要填充一個(gè)假設(shè)的日期或數(shù)字）。
步驟２．如果在日期或數(shù)字字段存在文本，移除它。
步驟３．如果數(shù)字被Excel視為了文本，將它改變?yōu)槌Ｒ?guī)類型。
２８．８在數(shù)據(jù)透視表中執(zhí)行計(jì)算
在默認(rèn)設(shè)置下，Excel對數(shù)據(jù)透視表數(shù)據(jù)區(qū)域的數(shù)字字段應(yīng)用求和函數(shù)，對非數(shù)字字段應(yīng)用計(jì)數(shù)函數(shù)。
Excel提供了多種匯總方式供用戶選擇，包括“求和”、“計(jì)數(shù)”、“平均值”、“最大值”、“最小值”、“乘積”等。要選擇匯總方式，可在數(shù)據(jù)透視表的數(shù)據(jù)區(qū)域中單擊右鍵，在彈出的快捷菜單中單擊“字段設(shè)置”。Excel會(huì)顯示出“數(shù)據(jù)透視表字段”對話框，選擇要采用的匯總方式，最后單擊“確定”按鈕。
２８．８．１以同一字段應(yīng)用多種匯總方式
用戶可以對一個(gè)數(shù)據(jù)字段同時(shí)應(yīng)用多種匯總方式。要為已經(jīng)位于數(shù)據(jù)透視表數(shù)據(jù)區(qū)域的字段添加第二種或更多的匯總方式，只需在“數(shù)據(jù)透視表列表”內(nèi)將該字段一次拖進(jìn)數(shù)據(jù)透視表的數(shù)據(jù)區(qū)中，利用“字段設(shè)置”對話框選擇想用的匯總方式即可。
２８．８．２自定義計(jì)算
如果“數(shù)據(jù)透視表字段”對話框內(nèi)的匯總方式仍然不能滿足需求，Excel還允許用戶自定義計(jì)算。利用此功能，可以計(jì)算數(shù)據(jù)透視表數(shù)據(jù)區(qū)域中每項(xiàng)占同行或同列數(shù)據(jù)總和的百分比，或顯示每個(gè)數(shù)值占總和的百分比。
應(yīng)用自定義計(jì)算的步驟為：
步驟１．在數(shù)據(jù)透視表的數(shù)據(jù)區(qū)域中單擊鼠標(biāo)右鍵，在彈出的快捷菜單中單擊“字段設(shè)置”，在“數(shù)據(jù)透視表字段”對話框內(nèi)單擊“選項(xiàng)”按鈕，出現(xiàn)擴(kuò)展對話框“數(shù)據(jù)顯示方式”。
步驟２．在“數(shù)據(jù)顯示方式”下拉列表中選擇想要執(zhí)行的計(jì)算，再從“基本字段”和“基本項(xiàng)”列表中選擇適當(dāng)?shù)倪x項(xiàng)。例如，在數(shù)據(jù)透視表中要顯示每名銷售人員與銷售人員“畢春艷”的“訂單金額”的差異，在“數(shù)據(jù)顯示方式”中選擇“差異”；基本字段選擇”銷售人員”；“基本項(xiàng)”中選擇“畢春艷”。
最后單擊“確定”按鈕完成。
自定義計(jì)算功能描述
選項(xiàng)
功能描述
普通 數(shù)據(jù)區(qū)域字段按普通方式而不是指定的函數(shù)匯總，此為默認(rèn)的計(jì)算
差異 數(shù)據(jù)區(qū)域字段與指定的基本字段和基本項(xiàng)的差值
百分比
數(shù)據(jù)區(qū)域顯示為基本字段和基本項(xiàng)的百分比
差異百分比 數(shù)據(jù)區(qū)域字段顯示為基本字段項(xiàng)的差異百分比
按某一字段匯總 數(shù)據(jù)區(qū)域字段顯示為基本字段項(xiàng)的匯總
占同行數(shù)據(jù)總和的百分比 數(shù)據(jù)區(qū)域字段顯示為每個(gè)數(shù)據(jù)項(xiàng)占該行所有項(xiàng)總和的百分比
占同列數(shù)據(jù)總和的百分比 數(shù)據(jù)區(qū)域字段顯示為每個(gè)數(shù)據(jù)項(xiàng)占該列所有項(xiàng)總和的百分比
占總和的百分比 數(shù)據(jù)區(qū)域了段顯示為每個(gè)數(shù)據(jù)項(xiàng)占該字段所有項(xiàng)總和的百分比
指數(shù) 使用公式：（（單元格的值）＊（總體匯總之和））／（（行匯總）＊（列匯總））
２８．８．３ 在數(shù)據(jù)透視表中使用計(jì)算字段和計(jì)算項(xiàng)
除了以上介紹的多種匯總方式以外，Excel還允許用戶向數(shù)據(jù)透視表中添加計(jì)算字段和計(jì)算項(xiàng)。計(jì)算字段是通過對表中現(xiàn)有的字段執(zhí)行計(jì)算后得到的新字段；計(jì)算項(xiàng)則是在已有的字段中插入新的項(xiàng)，是通過對該字段現(xiàn)有的其他項(xiàng)執(zhí)行計(jì)算后得到的。一旦創(chuàng)建了自定義的字段或項(xiàng)，Excel就允許在表格中使用它們，它們就像是在數(shù)據(jù)源中真實(shí)存在的一樣。
１．創(chuàng)建計(jì)算字段
圖中展示了一張已經(jīng)創(chuàng)建成功的數(shù)據(jù)透視表，下面下面通過添加計(jì)算字段，來寫成銷售人員提成的計(jì)算。
數(shù)據(jù)
銷售人員 求和項(xiàng):訂單金額 求和項(xiàng):銷售人員提成
畢春艷 116,962.99 1,754.44
高偉 68,792.25 1,031.88
何慶 276,244.31 4,143.66
林茂 225,763.68 3,386.46
蘇珊 72,527.63 1,087.91
楊光 182,500.09 2,737.50
張林波 123,032.67 1,845.49
總計(jì) 1,065,823.62 15,987.35
需要?jiǎng)?chuàng)建計(jì)算字段的數(shù)據(jù)透視表
步驟１．在數(shù)據(jù)透視表內(nèi)單擊任意單元格，單擊數(shù)據(jù)透視表工具欄的“數(shù)據(jù)透視表”，在彈出的快捷菜單中單擊“公式”，鼠標(biāo)指向“計(jì)算字段”并單擊它，激活“插入計(jì)算字段”對話框。
步驟２．在“插入計(jì)算字段”對話框的“名稱”框內(nèi)輸入“銷售人員提成”，將光標(biāo)定位到“公式”框中，清除原有的數(shù)據(jù)“＝０”
雙擊“字段”中的“訂單金額”，然后在“公式”框的“＝訂單金額”后輸入“＊０．０１５”（銷售人員的提成按１．５％）計(jì)算）。
步驟３．單擊“添加”按鈕，最后單擊“確定”按鈕關(guān)閉對話框。此時(shí)，數(shù)據(jù)透視表的數(shù)據(jù)區(qū)域中已經(jīng)創(chuàng)建了一個(gè)新的字段“銷售人員提成”。
２．添加計(jì)算項(xiàng)
如果要對上圖數(shù)據(jù)透視表創(chuàng)建“國際業(yè)務(wù)——國內(nèi)市場”的計(jì)算項(xiàng)，操作步驟如下。
步驟１．單擊數(shù)據(jù)透視表中“銷售途徑”字段標(biāo)題或分別單擊“銷售途徑”字段標(biāo)題下的“國際業(yè)務(wù)”和“國內(nèi)市場”，單擊數(shù)據(jù)透視表工具欄的“數(shù)據(jù)透視表”，在彈出的快捷菜單中單擊“公式”，鼠標(biāo)指向“計(jì)算項(xiàng)”并單擊它，激活“在銷售途徑中插入計(jì)算字段”對話框。
步驟２．在“在‘銷售途徑’中插入計(jì)算字段”對話框的“名稱”框內(nèi)輸入“國際業(yè)務(wù)—國內(nèi)市場”，將光標(biāo)定位到“公式”框中，清除原有的數(shù)據(jù)“＝０”，雙擊“項(xiàng)”中的“國際業(yè)務(wù)”，在“公式”框的“＝國際業(yè)務(wù)”后輸入“—”，再雙擊“項(xiàng)”中的“國內(nèi)市場”。
步驟３．單擊“添加”按鈕，最后單擊“確定”按鈕關(guān)閉對話框。此時(shí)，數(shù)據(jù)透視表行字段區(qū)域中已經(jīng)插入一個(gè)新的項(xiàng)目“國際業(yè)務(wù)——國內(nèi)市場”。
２８．９獲取數(shù)據(jù)透視表數(shù)據(jù)
數(shù)據(jù)透視表創(chuàng)建完成后，用戶要想獲取數(shù)據(jù)透視表中數(shù)據(jù)，可以使用GETPIVOTDATA函數(shù)。只需在數(shù)據(jù)透視表之外的單元格中輸入一個(gè)等號并單擊數(shù)據(jù)透視表中的一個(gè)單元格就可以看到一個(gè)替代單元格引用產(chǎn)生的透視數(shù)據(jù)公式。
GETPIVOTDATA函數(shù)有2個(gè)參數(shù)。
第１個(gè)參數(shù)是對數(shù)據(jù)透視表本身的引用，它可以是數(shù)據(jù)透視表中的任意單元格、分配給數(shù)據(jù)透視表的數(shù)據(jù)區(qū)域名或存儲(chǔ)在表格左上角內(nèi)的單元格中的標(biāo)志。
第２個(gè)參數(shù)則告知Excel所需數(shù)據(jù)的類型，它包含在雙引號，指定各字段名，這些字段名在表中的交叉點(diǎn)就是要獲取的數(shù)據(jù)。第2個(gè)參數(shù)中的各字段名之間要用空格鍵隔開。
注意：GETPIVOTDATA函數(shù)獲取的數(shù)據(jù)在數(shù)據(jù)透視表中必須是可見的，否則將返回錯(cuò)誤值“#REF！”。
例如，對于圖所示的數(shù)據(jù)透視表，要獲取銷售人員“畢春艷”在２００６年第三季度的銷售數(shù)據(jù)，可以輸入：
GETPIVOTDATA（數(shù)據(jù)透視表！A1，“畢春艷 2006 3“）
要獲取銷售人員“高偉”液晶電視的銷售數(shù)據(jù)，可以輸入：
GETPIVOTDATA（數(shù)據(jù)透視表！A1，“高偉 液晶電視”）
要獲取銷售人員“何慶”在2006年第二季度跑步機(jī)的銷售數(shù)據(jù)，可以輸入：
GETPIVOTDATA（數(shù)據(jù)透視表！A1，“何慶 2006 2 跑步機(jī)”）
２８．１０創(chuàng)建復(fù)合范圍的數(shù)據(jù)透視表
用戶可以使用來自同一工作簿的不同工作表或不同工作簿中數(shù)據(jù)，來創(chuàng)建數(shù)據(jù)透視表，前提是它們的結(jié)構(gòu)完全相同。在創(chuàng)建好的數(shù)據(jù)透視表中，每個(gè)源數(shù)據(jù)區(qū)域均顯示為頁軸的一項(xiàng)。通過頁軸上的下拉列表，用戶可以一覽無余地查看各個(gè)源數(shù)據(jù)區(qū)域以及對各數(shù)據(jù)區(qū)域合并計(jì)算后的匯總表格。
２８．１０．１創(chuàng)建單頁字段
要從工作表生成多重合并計(jì)算數(shù)據(jù)區(qū)域的數(shù)據(jù)透視表，可按照如下步驟進(jìn)行：
步驟１．單擊“數(shù)據(jù)”菜單，選擇“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”命令。
步驟２．“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А巢襟E之１”對話框顯示出來后，選擇“多重合并計(jì)算數(shù)據(jù)區(qū)域”選項(xiàng)，接著單擊“下一步”按鈕。
步驟３．“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А巢襟E之２ａ”對話框顯示出來后，選擇“多重單頁字段”選項(xiàng)（此項(xiàng)為默認(rèn)選項(xiàng)）。
步驟４．選擇參加數(shù)據(jù)透視表合并計(jì)算的第一個(gè)數(shù)據(jù)區(qū)域，接著單擊“添加”按鈕。本例中，第一個(gè)參加合并計(jì)算的數(shù)據(jù)區(qū)域?yàn)?#8216;１月份’！$A$1:$E$26。其他區(qū)域分別是'2月份'!$A$1:$E$17、‘3月份’！$A$1:$E$23。
注意“在指定數(shù)據(jù)區(qū)域進(jìn)行合并計(jì)算時(shí)，要包括行和列的標(biāo)題，但是不要包括匯總的行和列，數(shù)據(jù)透視表創(chuàng)建后會(huì)自動(dòng)計(jì)算匯總的行和列。
步驟５．對每個(gè)希望合并計(jì)算的區(qū)域重復(fù)步驟４。當(dāng)指定了所有的數(shù)據(jù)區(qū)域后，單擊“下一步按鈕選擇Excel顯示數(shù)據(jù)透視表的位置，再單擊”完成“按鈕。
步驟６．在默認(rèn)的情況下，Excel對數(shù)據(jù)源區(qū)域中的數(shù)值進(jìn)行和匯總。如果希望使用不同的匯總方式，雙擊”求和項(xiàng)：值”標(biāo)題，選擇要用的匯總方式，最后單擊“確定”按鈕完成。
圖２８－５９顯示了完成后的數(shù)據(jù)透視表?，F(xiàn)在的頁軸項(xiàng)為“全部”，這一頁顯示了所有月份費(fèi)用明細(xì)的匯總。選擇頁軸上的其他項(xiàng)，可單獨(dú)顯示各個(gè)月份的數(shù)據(jù)。
２８．１０．２創(chuàng)建自定義頁字段
在上例步驟３中，“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А巢襟E之２ａ”對話框顯示出來后，接受的是默認(rèn)選項(xiàng)“建單頁字段”，完成后的數(shù)據(jù)透視表頁軸會(huì)出現(xiàn)Excel默認(rèn)的“全部”、“項(xiàng)1”、“項(xiàng)2”、“項(xiàng)3”，如果用戶需要將頁軸中的各項(xiàng)改變?yōu)榕c待合并的各個(gè)工作表同名，則可以選擇“自定義頁字段”。添加各月份的數(shù)據(jù)區(qū)域后，在“字段”的文本框中分別輸入“1月份”、“2月份”、“3月份”即可。
２８．１０．３創(chuàng)建復(fù)合范圍的數(shù)據(jù)透視表的限制
在創(chuàng)建多重合并計(jì)算數(shù)據(jù)區(qū)域的數(shù)據(jù)透視表時(shí)，Excel會(huì)以各個(gè)待合并的子表的第一列作為合并的基準(zhǔn)，如果子表的非數(shù)據(jù)列有多個(gè)，Excel也只會(huì)選擇第一列。這一點(diǎn)與Excel的合并計(jì)算功能比較類似。
２８．１１創(chuàng)建數(shù)據(jù)透視圖
用戶可以通過“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?#8221;，在所需創(chuàng)建的報(bào)表類型中選擇“數(shù)據(jù)透視圖（及數(shù)據(jù)透視表）”來創(chuàng)建數(shù)據(jù)透視圖，
Excel將會(huì)在創(chuàng)建一張新的數(shù)據(jù)透視表的同時(shí)，創(chuàng)建一幅數(shù)據(jù)透視圖。此外，也可以先創(chuàng)建數(shù)據(jù)透視表，接著在數(shù)據(jù)透視表中選擇任意單元格，然后單擊“數(shù)據(jù)透視表”工具欄中的“圖表向?qū)?#8221;按鈕，Excel將在當(dāng)前數(shù)據(jù)透視表的基礎(chǔ)上創(chuàng)建一幅數(shù)據(jù)透視圖。
不論采用哪種方法，數(shù)據(jù)透視圖和數(shù)據(jù)透視表都鏈接在一起，其中之一的改變就會(huì)立即影響另一個(gè)。
