（中職）Office辦公軟件應(yīng)用案例教程-excel第8課 創(chuàng)建數(shù)據(jù)透視表ppt教學(xué)課課件

1、YCF正版可修改PPT（中職）Office辦公軟件應(yīng)用案例教程-excel第8課 創(chuàng)建數(shù)據(jù)透視表ppt教學(xué)課課件創(chuàng)建數(shù)據(jù)透視表 合并計(jì)算和透視表辦公軟件實(shí)訓(xùn)教程Excel 2016X學(xué)習(xí)目標(biāo) 能夠?qū)Σ煌ぷ鞅碇械臄?shù)據(jù)進(jìn)行合并計(jì)算，對(duì)數(shù)據(jù)量較大的數(shù)據(jù)通過(guò)數(shù)據(jù)透視表查看、匯總、排序，使用切片器篩選，并能美化透視表，建立透視圖。辦公軟件實(shí)訓(xùn)教程Excel 2016辦公軟件實(shí)訓(xùn)教程Excel 2016 人們對(duì)Excel數(shù)據(jù)透視表的強(qiáng)大功能贊嘆不已，卓卓我還不會(huì)呢?？粗鴶?shù)據(jù)量很大的網(wǎng)店銷(xiāo)售數(shù)據(jù)和多個(gè)部門(mén)提供的需要匯總的大量數(shù)據(jù)，卓卓我一籌莫展。好在有師傅的幫助，卓卓學(xué)習(xí)了合并計(jì)算，建立數(shù)據(jù)透視表，在透視

2、表中查看數(shù)據(jù)、匯總數(shù)據(jù)和排序數(shù)據(jù)。問(wèn)題解決了，卓卓當(dāng)然很高興，卓卓現(xiàn)在首先要完成的就是將銷(xiāo)售部、人事部、工藝部提供的數(shù)據(jù)進(jìn)行合并計(jì)算，我們來(lái)看看他是怎么做的吧。圖2-8-1 樣表辦公軟件實(shí)訓(xùn)教程Excel 2016將多個(gè)表中的數(shù)據(jù)合并計(jì)算；根據(jù)數(shù)據(jù)建立透視表，編輯透視表，操作透視表，完成數(shù)據(jù)的分析、匯總和排序，完成切片器篩選數(shù)據(jù)。辦公軟件實(shí)訓(xùn)教程Excel 2016辦公軟件實(shí)訓(xùn)教程Excel 2016圖2-7-2 排序按鈕及排序?qū)υ捒驅(qū)τ诜胖迷诓煌ぷ鞅碇?，有相同結(jié)構(gòu)的表格，我們需要將相關(guān)數(shù)據(jù)合并到一起，以便對(duì)所有信息有一個(gè)直觀的了解。要達(dá)成此目的可以使用數(shù)據(jù)透視表，除此之外 Excel還為我

3、們提供一種簡(jiǎn)單快捷有效的工具，合并計(jì)算。一.合并計(jì)算1.操作步驟新建表新建一張表，需要將人事部、工藝部、銷(xiāo)售部的費(fèi)用明細(xì)匯總到這張表中，即存放合并計(jì)算的結(jié)果。選中左上角的單元格，表示合并計(jì)算的結(jié)果從這個(gè)單元格開(kāi)始放置。單擊“數(shù)據(jù)”選項(xiàng)卡下“合并計(jì)算”按鈕，如圖2-8-2所示。彈出合并計(jì)算對(duì)話框，“函數(shù)”設(shè)置“求和”。 “引用位置”設(shè)置先單擊 按鈕，切換到“人事部”表，選擇要合并的數(shù)據(jù)，如圖2-8-3所示。再單擊 按鈕，回到合并計(jì)算對(duì)話框，完成人事部數(shù)據(jù)引用。單擊“添加”按鈕，將數(shù)據(jù)添加到引用位置中。按同樣方法添加工藝部和銷(xiāo)售部的數(shù)據(jù)。如圖2-8-4所示。圖2-7-3 次要關(guān)鍵字圖2-8-3 合

4、并計(jì)算對(duì)話框圖2-8-4 添加引用位置辦公軟件實(shí)訓(xùn)教程Excel 2016 圖2-8-5 合并計(jì)算結(jié)果一.合并計(jì)算添加數(shù)據(jù)完成后，勾選“標(biāo)簽位置”下面的兩個(gè)選項(xiàng)，“首行”和“最左列”。單擊“確定”。查看合并的結(jié)果，三張表中的數(shù)據(jù)匯總到一張表中如圖2-8-5所示。2. 讓合并結(jié)果動(dòng)態(tài)更新。若用戶希望合并后的結(jié)果能夠隨著數(shù)據(jù)源的變化而自動(dòng)更新時(shí)，可以在“合并計(jì)算”對(duì)話框中勾選“創(chuàng)建指向源數(shù)據(jù)的鏈接”復(fù)選框，這樣合并結(jié)果就可以動(dòng)態(tài)更新了。3. “合并計(jì)算”對(duì)話框中，如果不勾選 “首行”和“最左列”，合并計(jì)算的結(jié)果是沒(méi)有首行和最左列的。在本例中添加了最左列，求和時(shí)是按照最左列的字段進(jìn)行分類(lèi)匯總計(jì)算的，

5、若其他兩表中字段的順序、內(nèi)容不一致，會(huì)將不同的行或列的數(shù)據(jù)根據(jù)字段進(jìn)行分類(lèi)匯總。“合并計(jì)算”命令的默認(rèn)計(jì)算方式為求和?？梢栽凇昂喜⒂?jì)算”對(duì)話框的函數(shù)列表中更改?！昂喜⒂?jì)算”命令的計(jì)算方式包含求和、計(jì)數(shù)、平均值、最大值、最小值。小貼士：對(duì)不同表格的數(shù)據(jù)進(jìn)行合并計(jì)算時(shí)，表格中要有相同的字段名。 對(duì)不同表格的數(shù)據(jù)進(jìn)行合并計(jì)算時(shí)，表格中要有相同的字段名。小貼士：二.數(shù)據(jù)透視表數(shù)據(jù)透視表可以將表格中的數(shù)據(jù)整合到一張表中，是一種可以快速匯總大量數(shù)據(jù)的交互式表格。使用數(shù)據(jù)透視表可以深入分析數(shù)據(jù)、以多種方式查詢數(shù)據(jù)、匯總數(shù)據(jù)。1.創(chuàng)建數(shù)據(jù)透視表單擊“插入”選項(xiàng)卡下的“數(shù)據(jù)透視表”按鈕，如圖2-8-6所示。選擇

6、要分析的數(shù)據(jù)表區(qū)域。選擇現(xiàn)有工作表或新工作表放置透視表，單擊“確定”。生成數(shù)據(jù)透視表框架，如圖2-8-7所示。在數(shù)據(jù)透視表字段中，選擇需要的字段，如銷(xiāo)售日期、貨品名稱(chēng)、銷(xiāo)售數(shù)量。系統(tǒng)會(huì)默認(rèn)放置在列或行標(biāo)簽上。也可自己拖動(dòng)字段到不同的框內(nèi)。效果如圖2-8-8所示。辦公軟件實(shí)訓(xùn)教程Excel 2016圖2-8-6 數(shù)據(jù)透視表對(duì)話框 圖2-8-7 數(shù)據(jù)透視表框架圖2-8-8 數(shù)據(jù)透視表辦公軟件實(shí)訓(xùn)教程Excel 2016圖2-8-9 銷(xiāo)售日期由“行”變?yōu)椤傲小毙Ч麍D2-8-10 數(shù)據(jù)透視表的樣式2.編輯透視表系統(tǒng)生成透視表后，用戶可以通過(guò)設(shè)置透視表的布局、樣式、選項(xiàng)等標(biāo)簽，美化編輯創(chuàng)建的透視表。重組

7、數(shù)據(jù)透視表數(shù)據(jù)透視表是顯示數(shù)據(jù)信息的視圖，創(chuàng)建數(shù)據(jù)透視表后不能直接修改透視表所顯示的數(shù)據(jù)項(xiàng)。但表中的字段可以在字段欄或區(qū)域欄互換。如將銷(xiāo)售日期由“行”變?yōu)椤傲小保Ч鐖D2-8-9所示。也可以根據(jù)需要隨時(shí)向透視表增加或刪除字段，在數(shù)據(jù)透視表字段上操作即可。美化數(shù)據(jù)透視表。可以通過(guò)布局、樣式、選項(xiàng)、格式等工具美化透視表。設(shè)置樣式的方法是單擊“數(shù)據(jù)透視表工具”下的“設(shè)計(jì)”按鈕，彈出數(shù)據(jù)透視表的樣式，可以選擇其中的一種樣式，然后再設(shè)置“數(shù)據(jù)透視表樣式選項(xiàng)”中的行和列，如圖2-8-10所示。辦公軟件實(shí)訓(xùn)教程Excel 2016圖2-8-11 切片器對(duì)話框 圖2-8-12 創(chuàng)建切片器的顯示效果3.編輯透

8、視表切片器Excel切片器可以快速實(shí)現(xiàn)數(shù)據(jù)篩選，用戶可在切片器的數(shù)據(jù)上查詢當(dāng)前數(shù)據(jù)狀態(tài)。方法：?jiǎn)螕簟皵?shù)據(jù)透視表工具”中“分析”下面的“插入切片器”命令，在“插入切片器”對(duì)話框中選擇字段，如貨品名稱(chēng)和銷(xiāo)售數(shù)量，如圖2-8-11所示，單擊“確定”。貨品名稱(chēng)和銷(xiāo)售數(shù)量?jī)蓚€(gè)切片器就生成了，效果如圖2-8-12所示。當(dāng)用戶選擇貨品名稱(chēng)切片器上的貨物名稱(chēng)，如顯示器時(shí)，數(shù)據(jù)透視表和銷(xiāo)售數(shù)量切片器上則顯示出顯示器的相關(guān)數(shù)據(jù)信息。查詢方便，數(shù)據(jù)清晰、一目了然。按“Delete”鍵可刪除切片器。更改計(jì)算方式數(shù)據(jù)透視表默認(rèn)匯總方式是“求和”，它的計(jì)算類(lèi)型還有計(jì)數(shù)、平均值、最大值、最小值等。更改匯總方式有兩種方法：一

9、種是在“數(shù)據(jù)透視表字段”對(duì)話框中，在“值字段”下拉列表中，選擇“值字段設(shè)置”按鈕，更改計(jì)算類(lèi)型。第二種方法是在透視表中鼠標(biāo)右擊，彈出對(duì)話框，單擊“值字段設(shè)置”按鈕更改。排序數(shù)據(jù)透視表還可以對(duì)所選列或行排序，如：在透視表中按貨品銷(xiāo)售金額排序，方法如下：將貨品名稱(chēng)字段拖動(dòng)到行標(biāo)簽，銷(xiāo)售金額字段拖動(dòng)2次到值區(qū)域，如圖2-8-13所示。圖2-8-13 字段設(shè)置辦公軟件實(shí)訓(xùn)教程Excel 2016圖2-8-11 切片器對(duì)話框 圖2-8-12 創(chuàng)建切片器的顯示效果3.編輯透視表排序數(shù)據(jù)透視表還可以對(duì)所選列或行排序，如：在透視表中按貨品銷(xiāo)售金額排序，方法如下：將貨品名稱(chēng)字段拖動(dòng)到行標(biāo)簽，銷(xiāo)售金額字段拖動(dòng)2次

10、到值區(qū)域，如圖2-8-13所示。在銷(xiāo)售金額2字段下拉列表中選擇“值字段設(shè)置”。在 “值字段設(shè)置”對(duì)話框中設(shè)置值顯示方式為“降序排列”，基本字段為“商品名稱(chēng)”，如圖2-8-14所示。單擊“確定”，商品按銷(xiāo)售金額降序排序，效果如圖2-8-15所示。辦公軟件實(shí)訓(xùn)教程Excel 2016圖2-7-16 “1”按鈕下總計(jì)項(xiàng)三.創(chuàng)建數(shù)據(jù)透視圖操作步驟如下：1.單擊“數(shù)據(jù)透視表工具”選項(xiàng)卡“分析”下的“數(shù)據(jù)透視圖”按鈕，在彈出的“插入圖表”對(duì)話框中單擊“折線圖”，再選擇“帶數(shù)據(jù)標(biāo)記的折線圖”，如圖2-8-16所示。2.單擊“確定”，效果如圖2-8-17所示。圖2-8-17 數(shù)據(jù)透視圖效果辦公軟件實(shí)訓(xùn)教程Ex

11、cel 2016圖2-8-18 網(wǎng)店銷(xiāo)售明細(xì)表樣表圖2-8-3 合并計(jì)算對(duì)話框1. 將費(fèi)用明細(xì)表中，人事部、工藝部、銷(xiāo)售部三個(gè)部門(mén)的數(shù)據(jù)進(jìn)行合并計(jì)算，并使合并后的數(shù)據(jù)動(dòng)態(tài)更新。方法如下：打開(kāi)“費(fèi)用明細(xì)表.xlsx”，另存為“費(fèi)用明細(xì)表效果.xlsx”。在效果表中新建工作表Sheet4。選中Sheet4左上角的A1單元格。單擊“數(shù)據(jù)”選項(xiàng)卡下“數(shù)據(jù)工具”組的“合并計(jì)算”按鈕。彈出合并計(jì)算對(duì)話框，“函數(shù)”設(shè)置“求和”。 單擊 按鈕，設(shè)置“引用位置”。切換到人事部表，選擇 A1：B6單元格區(qū)域，單擊 按鈕回到合并計(jì)算對(duì)話框，完成人事部的引用位置，如圖2-8-3所示。單擊“添加”按鈕，添加引用位置。接

12、下來(lái)，按上述操作步驟，完成工藝部和銷(xiāo)售部的引用位置添加，最終效果如圖2-8-4所示。添加引用位置完成后，勾選“標(biāo)簽位置”下面的兩個(gè)選項(xiàng)，“首行”和“最左列”。勾選“創(chuàng)建指向源數(shù)據(jù)的鏈接”復(fù)選框，這樣合并結(jié)果就可以動(dòng)態(tài)更新了。單擊“確定”。查看合并結(jié)果，三張表中的數(shù)據(jù)匯總到一張表中，如圖2-8-5所示。保存文件“費(fèi)用明細(xì)表效果.xlsx”，存盤(pán)退出。2. 為網(wǎng)店銷(xiāo)售明細(xì)表中的數(shù)據(jù)創(chuàng)建數(shù)據(jù)透視表。網(wǎng)店銷(xiāo)售明細(xì)表的數(shù)據(jù)樣表如圖2-8-18所示。新建工作簿，將 “網(wǎng)店銷(xiāo)售明細(xì)表.xlsx”的內(nèi)容復(fù)制下來(lái)，另存為“網(wǎng)店銷(xiāo)售明細(xì)表效果.xlsx”，在效果表中進(jìn)行操作。 單擊“插入”選項(xiàng)卡下的“數(shù)據(jù)透視表”

13、按鈕。在“數(shù)據(jù)透視表”對(duì)話框中，選擇數(shù)據(jù)表區(qū)域；選擇放置數(shù)據(jù)透視表的位置為“新工作表”。 圖2-8-4 添加引用位置圖2-8-5 合并計(jì)算結(jié)果辦公軟件實(shí)訓(xùn)教程Excel 2016圖2-7-21 第5題效果圖5.按照自定義序列 “銷(xiāo)售部、研發(fā)部、文秘部”進(jìn)行排序。選擇數(shù)據(jù)任一單元格。單擊【數(shù)據(jù)】選項(xiàng)卡【排序和篩選】組的“排序”按鈕，彈出“排序”對(duì)話框。在主要關(guān)鍵字中選擇“部門(mén)”，在“次序”中選擇“自定義序列”，如圖2-7-5所示。在“自定義序列”對(duì)話框的“輸入序列”中輸入“銷(xiāo)售部、研發(fā)部、文秘部”，如圖2-7-6所示，單擊“添加”按鈕，單擊“確定”。回到“排序”對(duì)話框，單擊“確定”。效果如圖2-

14、7-21所示。6.復(fù)制Sheet1工作表中的內(nèi)容至Sheet3，在Sheet3中篩選“男”職工記錄。選擇任一數(shù)據(jù)單元格，單擊【數(shù)據(jù)】選項(xiàng)卡【排序和篩選】組的“篩選”按鈕。在“性別”篩選按鈕下選擇“男”。篩選出男員工數(shù)據(jù)記錄。效果如圖2-7-22所示。7. 在Sheet3中，篩選“銷(xiāo)售業(yè)績(jī)=85”記錄。單擊【數(shù)據(jù)】選項(xiàng)卡【排序和篩選】組的“清除”按鈕。清除當(dāng)前的篩選條件，讓數(shù)據(jù)全部顯示。在工作表中單擊“銷(xiāo)售業(yè)績(jī)”的篩選按鈕，選擇“數(shù)字篩選”下的“大于或等于”選項(xiàng)，如圖2-7-23所示。在彈出的對(duì)話框中輸入85，單擊“確定”，最終效果如圖2-7-24所示。圖2-7-22 第6題效果圖圖2-7-23

15、 數(shù)字篩選條件 圖2-6-13 “COUNTIF”參數(shù)設(shè)置8.在Sheet3中，利用高級(jí)篩選，篩選“銷(xiāo)售業(yè)績(jī)85”且“表達(dá)能力83”的人員。在工作表下方第19行，復(fù)制列標(biāo)題行第1行。設(shè)置條件：“銷(xiāo)售業(yè)績(jī)85”、“表達(dá)能力83”。如圖2-7-8所示。單擊【數(shù)據(jù)】選項(xiàng)卡【排序和篩選】組的“高級(jí)”按鈕。彈出“高級(jí)篩選”對(duì)話框如圖2-7-9所示。在“列表區(qū)域”文本框中自動(dòng)選擇了數(shù)據(jù)區(qū)域。單擊“條件區(qū)域”右側(cè)的折疊按鈕，在工作表中選擇滿足篩選條件的單元格區(qū)域，即E19:F20。單擊“確定”，篩選出符合條件的一條記錄，效果如圖2-7-10所示。9. 在Sheet3中，利用高級(jí)篩選，篩選“銷(xiāo)售業(yè)績(jī)85”或“

16、寫(xiě)作能力85”的人員，將Sheet3重命名為“篩選”。單擊【數(shù)據(jù)】選項(xiàng)卡【排序和篩選】組的“清除”按鈕。先清除當(dāng)前的篩選條件，讓數(shù)據(jù)全部顯示。更改篩選的條件，在單元格區(qū)域E20和G21中輸入條件，如圖2-7-11所示。即“銷(xiāo)售業(yè)績(jī)85”和“寫(xiě)作能力85”。注意：兩個(gè)條件不在同一行。單擊【數(shù)據(jù)】選項(xiàng)卡【排序和篩選】組的“高級(jí)”按鈕。彈出“高級(jí)篩選”對(duì)話框。設(shè)置“方式”為“將篩選結(jié)果復(fù)制到其他位置”， “列表區(qū)域”文本框中默認(rèn)填充所有數(shù)據(jù)區(qū)域， “條件區(qū)域”則選擇單元格區(qū)域E19:G21，“復(fù)制到”選擇單元格A24。如圖2-7-12所示。單擊“確定”，篩選出符合條件的記錄，從A24開(kāi)始放置。效果如

17、圖2-7-13所示。將工作表重命名為“篩選”。辦公軟件實(shí)訓(xùn)教程Excel 2016圖2-7-9 “與條件高級(jí)篩選”對(duì)話框圖2-7-10“與”篩選的數(shù)據(jù)圖2-7-11 “或”條件設(shè)置圖2-7-12 “或條件篩選”對(duì)話框圖2-7-13 “或”篩選出的數(shù)據(jù)辦公軟件實(shí)訓(xùn)教程Excel 201610.復(fù)制Sheet1工作表至Sheet4，在Sheet4中按部門(mén)匯總平均分。查看“總計(jì)”項(xiàng)和“各部門(mén)分類(lèi)匯總”項(xiàng)。將Sheet4重命名為“匯總”。首先對(duì)“部門(mén)”列數(shù)據(jù)進(jìn)行排序。選擇數(shù)據(jù)清單里的任一數(shù)據(jù)單元格。單擊【數(shù)據(jù)】選項(xiàng)卡【分級(jí)顯示】組的“分類(lèi)匯總”按鈕。彈出“分類(lèi)匯總”對(duì)話框，設(shè)置“分類(lèi)字段”為部門(mén)，“匯

18、總方式”為平均值，“匯總項(xiàng)”為平均分，如圖2-7-14所示。最后單擊“確定”按鈕，效果如圖2-7-15所示。查看“總計(jì)”項(xiàng)和“各部門(mén)分類(lèi)匯總”項(xiàng)按部門(mén)匯總后，在圖的左上角有 字樣，單擊“1”按鈕，顯示總計(jì)項(xiàng)，如圖2-7-16所示。單擊“2”按鈕，顯示二級(jí)匯總項(xiàng)數(shù)據(jù)，如圖2-7-17所示。將工作表更名為“匯總”。11.將文件另存為“員工考評(píng)表效果.xlsx”到自己姓名文件夾下。圖2-7-14 分類(lèi)匯總對(duì)話框圖2-7-15 按部門(mén)分類(lèi)匯總平均分圖2-7-16 “1”按鈕下總計(jì)項(xiàng) 圖2-7-17 “2”按鈕下各部門(mén)分類(lèi)匯總”辦公軟件實(shí)訓(xùn)教程Excel 2016圖2-7-26 練技能第4題效果1.新建工作簿，將“員工考評(píng)表.xlsx” 內(nèi)容復(fù)制到工作表中，保存文件名為“練一練效果.xlsx”。 將 “練一練效果.xlsx”中Sheet1的內(nèi)容復(fù)制到Sheet2、Sheet3 、Sheet4中。在Sheet2進(jìn)行如下操作。2.對(duì)數(shù)據(jù)按“總分”降序排列。3.先