數(shù)據(jù)透視表教程5

1、本課培訓(xùn)重點(diǎn)：在數(shù)據(jù)透視表中計(jì)算1、多匯總方式計(jì)算一-11種匯總方式2、自定義顯小方式 9種顯小方式3、添加計(jì)算字段和計(jì)算項(xiàng)4、利用透視表函數(shù)、SQL語句、VBA在透視表中進(jìn)行計(jì)算（這方面的內(nèi)容將在以后的學(xué)習(xí)中 會講）一：多種匯總方式計(jì)算-11種匯總方式如果要對數(shù)據(jù)區(qū)域中的同一字段同時(shí)使用多種匯總方式，只需要將字段列表中的字段多 次拖到數(shù)據(jù)區(qū)域，在多次使用某字段時(shí)，數(shù)據(jù)透視表會自動(dòng)在字段后面加上序列號。然后單 擊右鍵字段設(shè)置在“數(shù)據(jù)透視表字段”對話框的“匯總方式”下拉列表中選擇不同的匯 總方式。如圖1、數(shù)據(jù)透視表的11種匯總方式分別為：求和，計(jì)數(shù)，平均值，最大值，最小值，乘積，數(shù) 值計(jì)數(shù)，標(biāo)準(zhǔn)

2、偏差，總體標(biāo)準(zhǔn)偏差，方差，總體方差。A、求和：計(jì)算所有數(shù)值數(shù)據(jù)的累加和。B、計(jì)數(shù)：對所有單元格進(jìn)行計(jì)數(shù)，包括數(shù)值、文本和錯(cuò)誤的單元格(單元格內(nèi)有空格也會計(jì) 算在內(nèi))。與Excel函數(shù)二Counta()等同。C、平均值：求平均值。D、最大值：顯示最大值。E、最小值：顯示最小值。F、乘積：將所有單元格的數(shù)值相乘。如果數(shù)據(jù)集的單元格的值分別是3、4、5,則乘積為60。G、數(shù)值計(jì)數(shù)：只計(jì)算數(shù)值單元格的個(gè)數(shù)，與Excel函數(shù)二Count()等同。H、標(biāo)準(zhǔn)偏差和總體標(biāo)準(zhǔn)偏差：計(jì)算標(biāo)準(zhǔn)偏差。如果數(shù)據(jù)集包含全部成員，則要用“總體標(biāo)準(zhǔn) 偏差”。如果數(shù)據(jù)集包含成員的一些樣本，則使用“標(biāo)準(zhǔn)偏差”。I、方差和總體方差

3、：計(jì)算統(tǒng)計(jì)方差。如果數(shù)據(jù)集包含全部成員，則要使用“總體方差”。如 果數(shù)據(jù)集只是全體成員的抽樣，則使用“方差”估計(jì)方差?！坝?jì)數(shù)”和“數(shù)值計(jì)數(shù)”的區(qū)別在于，“計(jì)數(shù)”是只要不是空值的項(xiàng)就會進(jìn)一個(gè)空格也 會進(jìn)行計(jì)數(shù)的統(tǒng)計(jì)，而“數(shù)值計(jì)數(shù)”則是只對是數(shù)值型的項(xiàng)進(jìn)行計(jì)數(shù)統(tǒng)計(jì)。另外，數(shù)據(jù)透視 表的匯總方式默認(rèn)情況下是以求和方式匯總，但是如果數(shù)據(jù)源中有空白或是非數(shù)值類型的數(shù) 據(jù)則使用計(jì)數(shù)方式匯總。實(shí)例1：多種方式統(tǒng)計(jì)員工的生產(chǎn)數(shù)量統(tǒng)計(jì)不同員工的生產(chǎn)總量、平均產(chǎn)量、最大產(chǎn)量以及最小產(chǎn)量。操作方法：1、用數(shù)據(jù)源創(chuàng)建數(shù)據(jù)透視表，將“生產(chǎn)數(shù)量”字段拖到數(shù)據(jù)區(qū)域按“求和”匯總。即可 統(tǒng)計(jì)各個(gè)生產(chǎn)員工的“生產(chǎn)總量”。如圖2

4、、2、再次將“生產(chǎn)數(shù)量”字段拖動(dòng)到數(shù)據(jù)區(qū)域，選中“生產(chǎn)數(shù)量2”字段，單擊鼠標(biāo)右鍵 字段設(shè)置選擇匯總方式為“平均值”確定。即可統(tǒng)計(jì)各個(gè)生產(chǎn)員工“生產(chǎn)數(shù)量”的“平 均產(chǎn)量”。如圖3、3、用同樣的方法也可以對“最大產(chǎn)量”和“最小產(chǎn)量”進(jìn)行匯總。只需選擇不同的匯總 方式即可。二：多種自定義顯示方式9種顯示方式數(shù)據(jù)透視表的自定義顯示方式可用不同的方式顯示數(shù)據(jù)透視表數(shù)據(jù)區(qū)域中的字段項(xiàng)數(shù) 據(jù)。操作方法：單擊右鍵字段設(shè)置選項(xiàng)在“數(shù)據(jù)顯示方式”下拉框中選擇9種數(shù)據(jù)顯示方 式中的任意一種指定基本字段和基本項(xiàng)確定。如圖4、甄據(jù)透視表字段匯總方式底）:平是最乘直值值計(jì)均.M小積簡選項(xiàng);濾字段：工時(shí)數(shù)星名稱加）：|求和

5、項(xiàng):工時(shí)數(shù)星2數(shù)字皿L取消隱藏?cái)?shù)據(jù)顯示方式（A）：普通5分比差:尊百公比按某一手段匯總占同行數(shù)據(jù)總和的百分比占同列數(shù)據(jù)總和的百分比A數(shù)據(jù)透視表的9種自定義顯示方式分別為：普通、差異、百分比、差異百分比、按某一 字段匯總、占同行數(shù)據(jù)總和的百分比、占同列數(shù)據(jù)總和的百分比、占總和的百分比、指數(shù)。A、普通：數(shù)據(jù)區(qū)域字段按照默認(rèn)的匯總方式計(jì)算，默認(rèn)的匯總方式為求和或計(jì)數(shù)。B、差異：數(shù)據(jù)區(qū)域字段與指定基本字段和基本項(xiàng)的差值，一般用來與標(biāo)準(zhǔn)進(jìn)行對比時(shí)使用。C、百分比：數(shù)據(jù)區(qū)域顯示為基本字段和基本項(xiàng)的百分比，一般用來分析完成率和達(dá)成占比。D、差異百分比：數(shù)據(jù)區(qū)域字段顯示為與基本字段項(xiàng)的差異百分比。E、按某一字

6、段匯總：數(shù)據(jù)區(qū)域字段顯示為按某基本字段項(xiàng)的匯總。一般用于累計(jì)和排名等。F、占同行數(shù)據(jù)總和的百分比：數(shù)據(jù)區(qū)域字段顯示為每個(gè)數(shù)據(jù)項(xiàng)占該行所有項(xiàng)總和的百分比。G、占同列數(shù)據(jù)總和的百分比：數(shù)據(jù)區(qū)域字段顯示為每個(gè)數(shù)據(jù)項(xiàng)占該列所有項(xiàng)總和的百分比。H、占總和的百分比：數(shù)據(jù)區(qū)域字段顯示為每個(gè)數(shù)據(jù)項(xiàng)占該字段所有項(xiàng)總和的百分比。I、指數(shù)：公式：（單元格的值）*（總體匯總之和）/（行匯總）*（列匯總），主要反映數(shù)據(jù)區(qū) 域字段項(xiàng)的相對重要性。實(shí)例2：自定義顯示方式測定員工的工時(shí)完成情況在數(shù)據(jù)透視表里設(shè)定“定額工時(shí)”為測定基準(zhǔn)，計(jì)算員工工時(shí)跟定額工時(shí)的差異以及差 異率。操作方法：1、根據(jù)數(shù)據(jù)源創(chuàng)建數(shù)據(jù)透視表。將“工時(shí)數(shù)

7、量”三次拖到數(shù)據(jù)區(qū)域，即對“工時(shí)數(shù)量” 進(jìn)行三次求和計(jì)算。2、對數(shù)據(jù)進(jìn)行降序排序。右鍵單擊“員工姓名”字段字段設(shè)置在彈出的對話框中選 擇“高級”按“工時(shí)數(shù)量”進(jìn)行降序排序確定。如圖5、3、對“工時(shí)數(shù)量2”進(jìn)行差異量的測定分析。選中“工時(shí)數(shù)量2 ”單擊右鍵字段設(shè)置 在彈出的“數(shù)據(jù)透視表字段”對話框中單擊“選項(xiàng)”按鈕選擇“差異”（根據(jù)需要也可選 其它9種顯示方式）基本字段選擇“員工姓名”基本項(xiàng)選擇“定額工時(shí)”確定。如圖6、工號平均值10844K-個(gè)）數(shù)字或） 陲敦）數(shù)值計(jì)臀敷據(jù)語視表字段數(shù)據(jù) 耳第一小組員工姓缶IV求和項(xiàng)工盼數(shù)量 孝和項(xiàng):工町?dāng)?shù)是w9第一小組西第二小蛆亭湖亮劉度超李延悻李恒前李士律

8、11711711賀照璐安俞帆巍獎(jiǎng)陣方瞰名糖求甜項(xiàng)工時(shí)數(shù)星63648-156留93-10079H6芟本頂（Ji112151L005101099900215-11004、除了差異量的分析，如果還需要得到差異百分率的分析，其操作方式與上面基本相同， 只是在“選項(xiàng)”列表中，數(shù)據(jù)顯示方式選擇“百分比”即可。三、計(jì)算字段和計(jì)算項(xiàng)計(jì)算字段是通過對數(shù)據(jù)透視表中現(xiàn)有字段進(jìn)行計(jì)算后得到的新字段。計(jì)算項(xiàng)是通過對數(shù) 據(jù)透視表中現(xiàn)有字段的項(xiàng)進(jìn)行計(jì)算后得到的新項(xiàng)。添加了新的計(jì)算字段后，新字段會同時(shí)添 加在字段列表中，與原來的數(shù)據(jù)源字段同樣使用，具有等同的作用。計(jì)算字段和計(jì)算項(xiàng)可以 對數(shù)據(jù)透視表中現(xiàn)有的字段和字段項(xiàng)進(jìn)行引用

9、運(yùn)算，也可進(jìn)行簡單的函數(shù)運(yùn)算，但是不能引 用數(shù)據(jù)透視表以外的單元格和數(shù)據(jù)，也不能使用自定義名稱的引用。操作方法：光標(biāo)定位到需插入計(jì)算項(xiàng)或計(jì)算字段的位置單擊數(shù)據(jù)透視表工具欄的“數(shù) 據(jù)透視表”公式“計(jì)算項(xiàng)”或“計(jì)算字段”在彈出的對話框的“名稱”處輸入新名稱， 在“公式”處輸入新字段的數(shù)據(jù)運(yùn)算公式單擊“添加”確定，即可添加新的計(jì)算字段或 計(jì)算項(xiàng)。如圖7、8、插入計(jì)算字段鷺入新字 段名稱注意：A、如果光標(biāo)定位在列字段或數(shù)據(jù)區(qū)域時(shí)只能添加計(jì)算字段而無法添加計(jì)算項(xiàng)，只有光 標(biāo)定位在行字段或行字段下的項(xiàng)時(shí)才能添加計(jì)算項(xiàng)。B、透視表中如果有組合的項(xiàng)目，將不能添加計(jì)算項(xiàng)，會出現(xiàn)“由于某數(shù)據(jù)透視表字段 已組合，因

10、此不能向此報(bào)表中添加計(jì)算項(xiàng)”的警告提示框。需要先取消組合的項(xiàng)目， 然后再插入計(jì)算項(xiàng)，最后再對字段中的項(xiàng)目進(jìn)行分組。如圖9修改和刪除已添加的計(jì)算字段和計(jì)算項(xiàng)方法：A、修改計(jì)算字段：選中數(shù)據(jù)透視表中要修改的字段單擊數(shù)據(jù)透視表工具欄“數(shù)據(jù)透視 表”公式計(jì)算字段單擊名稱下拉按鈕選擇要修改的字段名稱在名稱框輸入新的名 稱在公式框輸入新的公式修改確定。即可對計(jì)算字段進(jìn)行修改。B、刪除計(jì)算字段：選中數(shù)據(jù)透視表中要?jiǎng)h除的字段單擊數(shù)據(jù)透視表工具欄“數(shù)據(jù)透視 表”公式計(jì)算字段單擊名稱下拉按鈕選擇要?jiǎng)h除的字段名稱刪除確定。即可對 計(jì)算字段進(jìn)行刪除。修改計(jì)算項(xiàng)和刪除計(jì)算項(xiàng)可參照修改計(jì)算字段的刪除計(jì)算字段的方法。如圖1

11、0、插乙字題耍J確定關(guān)閉插入計(jì)算字段名稱更）:公式W：零星名中數(shù)姓.簪=聘工次段 劇工工員名字點(diǎn)擊下技筋頭， 選中要修改或刷 除的名稱，最后 點(diǎn)擊修改或刪除實(shí)例3：添加字段進(jìn)行工時(shí)完成情況排序根據(jù)不同小組的員工的工時(shí)完成情況，進(jìn)行名次的排序。操作方法：1、根據(jù)數(shù)據(jù)源生成數(shù)據(jù)透視表，對“工時(shí)數(shù)量”字段進(jìn)行求和匯總。并將“員工姓名” 字段根據(jù)“工時(shí)數(shù)量”進(jìn)行降序排序。如圖11、2、利用添加計(jì)算字段方式實(shí)現(xiàn)名次的排序。選中數(shù)據(jù)區(qū)任意一個(gè)單元格單擊數(shù)據(jù)透視 表工具欄選擇“公式”計(jì)算字段在“插入計(jì)算字段”對話框。將需要添加的計(jì)算字段名 稱定義為“名次”，在“公式”中輸入“1”確定。即新增加了值為“1”的

12、“名次”的字 段。如圖12、3、用自定義顯示方式進(jìn)行名次排序。選擇“名次”字段單擊右鍵字段設(shè)置選項(xiàng)在 “數(shù)據(jù)顯示方式”列表中選擇“按某一字段匯總”基本字段選擇“員工姓名”確定。如圖13、注意:取消數(shù)據(jù)透視表字段源字段： 名法名稱：畫圈融隱藏和 1U.K 數(shù) 百直直 十 數(shù)皿雄粕積尚 計(jì)平是最乘數(shù)工號 T的數(shù)量數(shù)字在進(jìn)行名次的排序前一定要先進(jìn)行降序的排序。實(shí)例4 :用百分比方式顯示計(jì)算結(jié)果如果需要用百分比顯示方式來表達(dá)對數(shù)據(jù)的計(jì)算結(jié)果，通過自定義顯示方式可以計(jì)算數(shù) 據(jù)項(xiàng)在行或列數(shù)據(jù)中的百分比，也可計(jì)算數(shù)據(jù)項(xiàng)在所有數(shù)據(jù)總和中的百分比。操作方法：根據(jù)數(shù)據(jù)源生成數(shù)據(jù)透視表，選中數(shù)據(jù)區(qū)域的單元格右鍵單擊

13、字段設(shè)置在數(shù)據(jù)顯 示方式的下拉列表中選擇“占同行數(shù)據(jù)總和的百分比”確定。即可得到某一商品在不同地 區(qū)的銷售百分比的情況。如圖14、也可在數(shù)據(jù)顯示方式的下拉列表中選擇“占同列數(shù)據(jù)總和的百分比”或“占總和的百分比。實(shí)例5 :添加計(jì)算字段和計(jì)算項(xiàng)進(jìn)行分析需要同時(shí)顯示數(shù)據(jù)和所占費(fèi)用的百分比，并對兩年數(shù)據(jù)差異分析，就需要用到計(jì)算字段 和計(jì)算項(xiàng)。操作方法：1、添加計(jì)算字段：選中要插入計(jì)算字段的單元格單擊數(shù)據(jù)透視表工具欄“數(shù)據(jù)透視表” 公式計(jì)算字段在插入計(jì)算字段的名稱框中輸入“差異”在公式里輸入“二2007年-2006 年”，（也可以在字段列表內(nèi)單擊再單擊插入字段或雙擊字段名）添加確定。即完成計(jì) 算字段添加

14、。如圖15、插入計(jì)算字段名稱更）：差異欲式血）： 巨，ZW7年，-忌口如年，|數(shù)碼稱號蝙名際證目目要月日憑科科摘枝&字度或I7 O O,?6102孺定關(guān)閉2、添加計(jì)算項(xiàng)：選中要插入字段項(xiàng)的行字段單擊數(shù)據(jù)透視表工具欄中“數(shù)據(jù)透視表” 公式計(jì)算項(xiàng)在名稱框中輸入計(jì)算項(xiàng)的新名稱在字段選擇框中選擇要添加計(jì)算項(xiàng)的隸屬 字段在項(xiàng)中選擇計(jì)算的項(xiàng)添加確定。完成計(jì)算項(xiàng)添加。如圖16、注意：在添加了計(jì)算項(xiàng)后列總計(jì)的數(shù)據(jù)可能不準(zhǔn)確，解決辦法是另外再添加一個(gè)計(jì)算項(xiàng)進(jìn)行 總計(jì)的計(jì)算。如圖17、18四、常見問題分析1、為什么在透視表中使用match等函數(shù)卻得不到運(yùn)算結(jié)果?在數(shù)據(jù)透視表計(jì)算字段中可以使用簡單的函數(shù)運(yùn)算（如sum, if, text等），但數(shù)據(jù)透視表的 運(yùn)算是在透視表緩存中進(jìn)行的，因此不能使用函數(shù)對單元格進(jìn)行引用或自定義名稱等。也就 是說，可以使用不需要使用單元格引用或自定義名稱的工作表函數(shù)，而Match則需要使用單 元格引用。因此，不能在計(jì)算字段或計(jì)算項(xiàng)中使用。2、為什么添加了由“單價(jià)” * “數(shù)量”得到的“總金額”新字段后，其合計(jì)數(shù)計(jì)算不正 確？數(shù)據(jù)透視表總計(jì)結(jié)果總是先進(jìn)行求和再進(jìn)行其它運(yùn)算，因此，在計(jì)算總金額時(shí)，是將“單價(jià)” 和“數(shù)量”先分別求和，然后再進(jìn)行相乘，因此計(jì)算結(jié)果不正確。如圖19、3、為什么我的透視表不能進(jìn)行行總計(jì)？數(shù)據(jù)透視表的行總計(jì)或列總計(jì)僅能計(jì)算由一維表數(shù)據(jù)項(xiàng)形成的列字