《Excel數(shù)據(jù)分析基礎(chǔ)與實(shí)戰(zhàn)》第5章 數(shù)據(jù)透視表和透視圖

數(shù)據(jù)透視表和透視圖21-12月-231編輯數(shù)據(jù)透視表目錄創(chuàng)建數(shù)據(jù)透視表2操作數(shù)據(jù)透視表中的數(shù)據(jù)3創(chuàng)建數(shù)據(jù)透視圖4數(shù)據(jù)透視表能全面、靈活地對(duì)數(shù)據(jù)進(jìn)行分析、匯總，通過轉(zhuǎn)換行或列，可得到多種分析結(jié)果，還可以顯示不同的頁(yè)面來(lái)篩選數(shù)據(jù)。為了方便對(duì)某餐飲店的訂單信息進(jìn)行后續(xù)分析，需要根據(jù)已有數(shù)據(jù)表在Excel中創(chuàng)建一個(gè)數(shù)據(jù)透視表。利用Excel自動(dòng)創(chuàng)建數(shù)據(jù)透視表，具體操作步驟如下。自動(dòng)創(chuàng)建數(shù)據(jù)透視表打開【推薦的數(shù)據(jù)透視表】對(duì)話框。打開【訂單信息.xlsx】工作簿，單擊數(shù)據(jù)區(qū)域內(nèi)任一單元格，在【插入】選項(xiàng)卡的【表格】命令組中，單擊【推薦的數(shù)據(jù)透視表】圖標(biāo)，如左圖所示，彈出【推薦的數(shù)據(jù)透視表】對(duì)話框，如右圖所示。自動(dòng)創(chuàng)建數(shù)據(jù)透視表在【推薦的數(shù)據(jù)透視表】對(duì)話框中顯示了一些縮略圖，其展示了可以選擇的數(shù)據(jù)透視表。（1）選擇數(shù)據(jù)透視表在【推薦的數(shù)據(jù)透視表】對(duì)話框左邊的縮略圖中選擇其中一個(gè)，此處選擇第一個(gè)，單擊【確定】按鈕，Excel將在一個(gè)新的工作表中創(chuàng)建數(shù)據(jù)透視表，如圖所示。自動(dòng)創(chuàng)建數(shù)據(jù)透視表如果推薦的數(shù)據(jù)透視表都不適合，那么可以手動(dòng)創(chuàng)建數(shù)據(jù)透視表，具體操作步驟如下。（1）打開【創(chuàng)建數(shù)據(jù)透視表】對(duì)話框打開【訂單信息】工作表，單擊數(shù)據(jù)區(qū)域內(nèi)任一單元格，在【插入】選項(xiàng)卡的【表格】命令組中，單擊【數(shù)據(jù)透視表】圖標(biāo)，彈出【創(chuàng)建數(shù)據(jù)透視表】對(duì)話框，如圖所示。其中，選擇的數(shù)據(jù)為整個(gè)數(shù)據(jù)區(qū)域，而放置數(shù)據(jù)透視表的位置默認(rèn)為新工作表，但是用戶可以指定放置在現(xiàn)有工作表中。手動(dòng)創(chuàng)建數(shù)據(jù)透視表（2）確定創(chuàng)建空白數(shù)據(jù)透視表單擊上圖中所示的【確定】按鈕，Excel將創(chuàng)建一個(gè)空白數(shù)據(jù)透視表，并顯示【數(shù)據(jù)透視表字段】窗格，如圖所示。手動(dòng)創(chuàng)建數(shù)據(jù)透視表（3）添加字段將“結(jié)算時(shí)間”字段拖曳至【篩選】區(qū)域，“店鋪所在地”和“店鋪名”字段分別拖曳至【行】區(qū)域，“消費(fèi)金額”字段拖曳至【值】區(qū)域，如左圖所示，創(chuàng)建的數(shù)據(jù)透視表如右圖所示。手動(dòng)創(chuàng)建數(shù)據(jù)透視表1編輯數(shù)據(jù)透視表目錄創(chuàng)建數(shù)據(jù)透視表2操作數(shù)據(jù)透視表中的數(shù)據(jù)3創(chuàng)建數(shù)據(jù)透視圖4對(duì)于制作好的數(shù)據(jù)透視表，有時(shí)還需要對(duì)數(shù)據(jù)透視表進(jìn)行編輯，使得數(shù)據(jù)透視表的展示更符合分析要求，更加美觀。為了更好地展示和分析餐飲店的訂單信息數(shù)據(jù)，需要在Excel中對(duì)所創(chuàng)建的數(shù)據(jù)透視表進(jìn)行編輯。對(duì)數(shù)據(jù)透視表顯示的內(nèi)容進(jìn)行修改，具體操作步驟如下。修改數(shù)據(jù)透視表（1）行字段改為列字段打開【數(shù)據(jù)透視表.xlsx】工作簿，在【數(shù)據(jù)透視表字段】窗格中，將“店鋪所在地”字段由【行】區(qū)域拖曳到【列】區(qū)域，如左圖所示。此時(shí)數(shù)據(jù)透視表如右圖所示。修改數(shù)據(jù)透視表（2）列字段改為行字段將“店鋪所在地”字段由【列】區(qū)域拖曳到【行】區(qū)域，結(jié)果如圖所示。對(duì)比手動(dòng)創(chuàng)建數(shù)據(jù)透視表步驟（3）中的右圖與本圖可知，同一區(qū)域的字段順序不同，數(shù)據(jù)透視表的結(jié)果也會(huì)有所不同。修改數(shù)據(jù)透視表對(duì)創(chuàng)建好的數(shù)據(jù)透視表，對(duì)其名稱進(jìn)行更改，具體操作步驟如下。（1）打開【數(shù)據(jù)透視表選項(xiàng)】對(duì)話框?qū)π薷臄?shù)據(jù)透視表小節(jié)修改后的數(shù)據(jù)透視表，單擊數(shù)據(jù)區(qū)域內(nèi)任一單元格，在【分析】選項(xiàng)卡的【數(shù)據(jù)透視表】命令組中，單擊【選項(xiàng)】圖標(biāo)，彈出【數(shù)據(jù)透視表選項(xiàng)】對(duì)話框，如圖所示。重命名數(shù)據(jù)透視表（2）輸入數(shù)據(jù)透視表的新名稱在上圖中所示的【數(shù)據(jù)透視表名稱】文本框中輸入“訂單信息”，如圖所示。（3）確定設(shè)置單擊圖中所示的【確定】按鈕，即可完成對(duì)數(shù)據(jù)透視表名稱的更改。重命名數(shù)據(jù)透視表改變數(shù)據(jù)透視表的布局包括設(shè)置分類匯總、設(shè)置總計(jì)、設(shè)置報(bào)表布局和空行等。現(xiàn)將數(shù)據(jù)透視表的布局改為表格形式。打開【數(shù)據(jù)透視表.xlsx】工作簿，單擊數(shù)據(jù)區(qū)域內(nèi)任一單元格，在【設(shè)計(jì)】選項(xiàng)卡的【布局】命令組中，單擊【報(bào)表布局】圖標(biāo)，如左圖所示，選擇【以表格形式顯示】命令。該數(shù)據(jù)透視表即以表格形式顯示，如右圖所示。改變數(shù)據(jù)透視表的布局在工作表中插入數(shù)據(jù)透視表后，還可以對(duì)數(shù)據(jù)透視表的格式進(jìn)行設(shè)置，使數(shù)據(jù)透視表更加美觀。用戶可以使用系統(tǒng)自帶的樣式來(lái)設(shè)置數(shù)據(jù)透視表的格式，具體操作步驟如下。（1）打開數(shù)據(jù)透視表格式的下拉列表打開【數(shù)據(jù)透視表.xlsx】工作簿，在【設(shè)計(jì)】選項(xiàng)卡的【數(shù)據(jù)透視表樣式】命令組中，單擊按鈕，彈出的下拉列表如圖所示。設(shè)置數(shù)據(jù)透視表樣式1.自動(dòng)套用樣式（2）選擇樣式在彈出的下拉列表中選擇其中一種樣式，即可更改數(shù)據(jù)透視表的樣式，此處選擇“淺橙色，數(shù)據(jù)透視表樣式淺色17”，結(jié)果如圖所示。設(shè)置數(shù)據(jù)透視表樣式如果系統(tǒng)自帶的數(shù)據(jù)透視表樣式不能滿足需要，那么用戶還可以自定義數(shù)據(jù)透視表樣式，具體操作步驟如下。（1）打開【新建數(shù)據(jù)透視表樣式】對(duì)話框在【設(shè)計(jì)】選項(xiàng)卡的【表格樣式】命令組中，單擊按鈕，在彈出的下拉列表中選擇【新建數(shù)據(jù)透視表樣式】，彈出【新建數(shù)據(jù)透視表樣式】對(duì)話框，如圖所示。設(shè)置數(shù)據(jù)透視表樣式2.自定義數(shù)據(jù)透視表樣式（2）輸入新樣式名稱和選擇表元素在上圖中所示的【名稱】對(duì)話框中輸入樣式的名稱，此處輸入“新建樣式1”，在【表元素】下拉框中選擇【整個(gè)表】選項(xiàng)，如圖所示。設(shè)置數(shù)據(jù)透視表樣式（3）打開【設(shè)置單元格格式】對(duì)話框單擊上圖中所示的【格式】按鈕，彈出【設(shè)置單元格格式】對(duì)話框，如圖所示。設(shè)置數(shù)據(jù)透視表樣式（4）設(shè)置邊框樣式切換到【邊框】選項(xiàng)卡，在【樣式】列表框中選擇“無(wú)”下面的虛線樣式，在【顏色】下拉框中設(shè)置邊框的顏色為“藍(lán)色”，在【預(yù)設(shè)】中選擇【外邊框】，如圖所示。（5）確定設(shè)置單擊圖中所示的【確定】按鈕，返回【新建表樣式】對(duì)話框，再單擊【確定】按鈕，回到工作表中。設(shè)置數(shù)據(jù)透視表樣式（6）打開數(shù)據(jù)透視表格式的下拉列表。在【設(shè)計(jì)】選項(xiàng)卡的【數(shù)據(jù)透視表樣式】命令組中，單擊按鈕，在彈出的下拉列表出現(xiàn)了一個(gè)自定義樣式，如圖所示。設(shè)置數(shù)據(jù)透視表樣式（7）選擇新建樣式1選擇上圖中所示的“新建樣式1”，結(jié)果如圖所示。設(shè)置數(shù)據(jù)透視表樣式1編輯數(shù)據(jù)透視表目錄創(chuàng)建數(shù)據(jù)透視表2操作數(shù)據(jù)透視表中的數(shù)據(jù)3創(chuàng)建數(shù)據(jù)透視圖4操作數(shù)據(jù)透視表中的數(shù)據(jù)可以對(duì)數(shù)據(jù)源數(shù)據(jù)的更新得到及時(shí)反饋，對(duì)數(shù)據(jù)分析更方便高效，使得數(shù)據(jù)在數(shù)據(jù)透視表中的展示更加清晰。為了更清晰的展示和分析餐飲店的訂單信息數(shù)據(jù)，需要在Excel中對(duì)手動(dòng)創(chuàng)建數(shù)據(jù)透視表小節(jié)中創(chuàng)建的數(shù)據(jù)透視表進(jìn)行更改。數(shù)據(jù)透視表的數(shù)據(jù)來(lái)源于數(shù)據(jù)源，不能在透視表中直接修改。當(dāng)原數(shù)據(jù)表中的數(shù)據(jù)被修改之后，數(shù)據(jù)透視表不會(huì)自動(dòng)進(jìn)行更新，必須執(zhí)行更新操作才能刷新數(shù)據(jù)透視表，具體操作步驟如下。刷新數(shù)據(jù)透視表打開【訂單信息.xlsx】工作簿，切換至【Sheet2】工作表，右鍵單擊數(shù)據(jù)透視表的任一單元格，在彈出的菜單中選擇【刷新】命令，如圖所示。刷新數(shù)據(jù)透視表或在【分析】選項(xiàng)卡的【數(shù)據(jù)】命令組中，單擊【刷新】圖標(biāo)，對(duì)數(shù)據(jù)透視表進(jìn)行更新，如圖所示。刷新數(shù)據(jù)透視表在創(chuàng)建完數(shù)據(jù)透視表后，用戶還可以對(duì)數(shù)據(jù)透視表的字段進(jìn)行相應(yīng)的設(shè)置。除了直接拖動(dòng)字段到區(qū)域中，向數(shù)據(jù)透視表添加字段的方式還有以下兩種方法。（1）打開【訂單信息.xlsx】工作簿切換到【Sheet2】工作表，在【選擇要添加到報(bào)表的字段】區(qū)域中，勾選【是否結(jié)算（0.未結(jié)算.1.已結(jié)算）】復(fù)選框，如圖所示設(shè)置數(shù)據(jù)透視表的字段1.添加字段根據(jù)字段的特點(diǎn)，該字段被添加到【值】區(qū)域，所得的數(shù)據(jù)透視表如圖所示。需要注意的是，所選字段將被添加到默認(rèn)區(qū)域：非數(shù)字字段添加到【行】區(qū)域，日期和時(shí)間層次結(jié)構(gòu)添加到【列】區(qū)域，數(shù)值字段添加到【值】區(qū)域。設(shè)置數(shù)據(jù)透視表的字段（2）右鍵單擊“是否結(jié)算（0.未結(jié)算.1.已結(jié)算）”在彈出的快捷菜單中選擇【添加到數(shù)值】命令，如圖所示，該字段即被添加到【值】區(qū)域處。設(shè)置數(shù)據(jù)透視表的字段將數(shù)據(jù)透視表中的字段進(jìn)行重命名，如將值字段中的“求和項(xiàng):是否結(jié)算（0.未結(jié)算.1.已結(jié)算）”改為“訂單數(shù)”，具體操作步驟如下。（1）打開【值字段設(shè)置】對(duì)話框在【值】區(qū)域中單擊“求和項(xiàng):是否結(jié)算（0.未結(jié)算.1.已結(jié)算）”，在彈出的快捷菜單中選擇【值字段設(shè)置】命令，彈出【值字段設(shè)置】對(duì)話框，如圖所示。設(shè)置數(shù)據(jù)透視表的字段2.重命名字段（2）輸入“訂單數(shù)”在上圖中所示的【自定義名稱】文本框中輸入“訂單數(shù)”，如圖所示。設(shè)置數(shù)據(jù)透視表的字段（3）確定設(shè)置單擊上圖中所示的【確定】按鈕，結(jié)果如圖所示。設(shè)置數(shù)據(jù)透視表的字段在數(shù)據(jù)透視表中刪除“訂單數(shù)”字段有如下兩種方法。（1）通過快捷菜單刪除“訂單數(shù)”字段在【值】區(qū)域中右鍵單擊“訂單數(shù)”字段，在彈出的快捷菜單中選擇【刪除字段】命令，如左圖所示，結(jié)果如右圖所示。設(shè)置數(shù)據(jù)透視表的字段3.刪除字段（2）通過拖動(dòng)法刪除“訂單數(shù)”字段在【值】區(qū)域中單擊“訂單數(shù)”字段，按住鼠標(biāo)將它拖曳到【數(shù)據(jù)透視表字段】窗格外，釋放鼠標(biāo)進(jìn)行刪除，如圖所示。設(shè)置數(shù)據(jù)透視表的字段在數(shù)據(jù)透視表中，數(shù)據(jù)的匯總方式默認(rèn)為求和，但還有計(jì)數(shù)、平均值、最大值、最小值等。將數(shù)據(jù)透視表的匯總方式改為最大值，具體操作步驟如下。（1）打開【值字段設(shè)置】對(duì)話框在【值】區(qū)域中單擊【求和項(xiàng):消費(fèi)金額】旁邊的倒三角按鈕，選擇【值字段設(shè)置】選項(xiàng)，彈出【值字段設(shè)置】對(duì)話框，如圖所示。改變數(shù)據(jù)透視表的匯總方式（2）選擇計(jì)算類型并確定設(shè)置在上圖中所示的【值字段匯總方式】列表框中選擇【最大值】選項(xiàng)，單擊【確定】按鈕，結(jié)果如圖所示。改變數(shù)據(jù)透視表的匯總方式在數(shù)據(jù)透視表中，還可以對(duì)數(shù)據(jù)進(jìn)行篩選操作，數(shù)據(jù)篩選方式有使用切片器篩選數(shù)據(jù)透視表和使用日程表篩選數(shù)據(jù)透視表，合理篩選數(shù)據(jù)可增強(qiáng)數(shù)據(jù)的可視性，便于對(duì)數(shù)據(jù)進(jìn)行研究。切片器是一個(gè)交互式的控件，它提供了一種可視性極強(qiáng)的篩選方式來(lái)篩選數(shù)據(jù)透視表中的數(shù)據(jù)。通過切片器篩選數(shù)據(jù)透視表，具體操作步驟如下。篩選數(shù)據(jù)1.使用切片器篩選數(shù)據(jù)透視表（1）打開【插入切片器】對(duì)話框打開【訂單信息.xlsx】工作簿，切換到【Sheet2】工作表，單擊數(shù)據(jù)區(qū)域內(nèi)任一單元格，在【分析】選項(xiàng)卡的【篩選】命令組中，單擊【插入切片器】圖標(biāo)，彈出【插入切片器】對(duì)話框，如圖所示。篩選數(shù)據(jù)（2）插入切片器勾選上圖中所示的【店鋪所在地】復(fù)選框，單擊【確定】按鈕，此時(shí)即可插入了【店鋪所在地】切片器，如圖所示。篩選數(shù)據(jù)（3）篩選數(shù)據(jù)在【店鋪所在地】切片器中單擊【廣州】選項(xiàng)，則在數(shù)據(jù)透視表中只會(huì)顯示廣州的消費(fèi)金額，如圖所示。篩選數(shù)據(jù)只有當(dāng)數(shù)據(jù)透視表中包含日期格式的字段時(shí)，才能使用日程表。通過日程表篩選數(shù)據(jù)透視表，具體操作步驟如下。（1）打開【插入日程表】對(duì)話框單擊數(shù)據(jù)區(qū)域內(nèi)任一單元格，在【分析】選項(xiàng)卡的【篩選】命令組中，單擊【插入日程表】圖標(biāo)，彈出【插入日程表】對(duì)話框，如圖所示。篩選數(shù)據(jù)2.使用日程表篩選數(shù)據(jù)透視表（2）插入【結(jié)算時(shí)間】日程表單擊上圖中所示的【結(jié)算時(shí)間】復(fù)選框，此時(shí)即可插入了【結(jié)算時(shí)間】日程表，如圖所示。篩選數(shù)據(jù)（3）篩選出廣州8月的消費(fèi)金額單擊上圖中所示的【8月】選項(xiàng)，則在數(shù)據(jù)透視表中只會(huì)顯示8月份的消費(fèi)金額，如圖所示。篩選數(shù)據(jù)1編輯數(shù)據(jù)透視表目錄創(chuàng)建數(shù)據(jù)透視表2操作數(shù)據(jù)透視表中的數(shù)據(jù)3創(chuàng)建數(shù)據(jù)透視圖4數(shù)據(jù)透視圖可以實(shí)現(xiàn)更加形象化地體現(xiàn)數(shù)據(jù)的情況。數(shù)據(jù)透視圖可以根據(jù)數(shù)據(jù)區(qū)域創(chuàng)建，也可以根據(jù)已經(jīng)創(chuàng)建好的數(shù)據(jù)透視表創(chuàng)建。在Excel中分別利用這兩種方式建立數(shù)據(jù)透視圖，展示每個(gè)地區(qū)的每個(gè)店的用戶消費(fèi)情況。根據(jù)數(shù)據(jù)區(qū)域創(chuàng)建數(shù)據(jù)透視圖，具體操作步驟如下。通過數(shù)據(jù)區(qū)域創(chuàng)建數(shù)據(jù)透視圖（1）打開【創(chuàng)建數(shù)據(jù)透視圖】對(duì)話框打開【訂單信息.xlsx】工作簿，切換到【訂單信息】工作表，單擊數(shù)據(jù)區(qū)域內(nèi)任一單元格，在【插入】選項(xiàng)卡的【圖表】命令組中，單擊【數(shù)據(jù)透視圖】圖標(biāo)，彈出【創(chuàng)建數(shù)據(jù)透視圖】對(duì)話框，如圖所示。通過數(shù)據(jù)區(qū)域創(chuàng)建數(shù)據(jù)透視圖（2）創(chuàng)建一個(gè)空白數(shù)據(jù)透視圖單擊上圖中所示的【確定】按鈕，Excel將創(chuàng)建一個(gè)空白數(shù)據(jù)透視圖，并顯示【數(shù)據(jù)透視圖字段】窗格，如圖所示。通過數(shù)據(jù)區(qū)域創(chuàng)建數(shù)據(jù)透視圖（3）設(shè)置字段將“結(jié)算時(shí)間”字段拖曳至【篩選】區(qū)域，“店鋪所在地”和“店鋪名”字段分別拖曳至【軸(類別)】區(qū)域，【消費(fèi)金額】字段拖曳至【值】區(qū)域，如圖所示。通過數(shù)據(jù)區(qū)域創(chuàng)建數(shù)據(jù)透視圖（4）修改透視圖標(biāo)題為“不同店鋪消費(fèi)金額”，結(jié)果如圖所示。通過