百變數(shù)據(jù)透視表

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