Excel 2016數(shù)據(jù)處理與分析應用教程 課件 第6、7章 數(shù)據(jù)管理、數(shù)據(jù)透視分析

第6章數(shù)據(jù)管理主要內(nèi)容6.1數(shù)據(jù)驗證6.2數(shù)據(jù)排序6.3數(shù)據(jù)篩選6.4分類匯總6.1數(shù)據(jù)驗證數(shù)據(jù)驗證的目的是限定單元格中輸入數(shù)據(jù)的類型和范圍，并且能夠在數(shù)據(jù)錄入操作過程中及時地給出提示信息。數(shù)據(jù)驗證的最常見用法之一是創(chuàng)建下拉列表。主要內(nèi)容：6.1.1數(shù)據(jù)驗證設置6.1.2限制數(shù)據(jù)錄入的下拉列表設置6.1.1數(shù)據(jù)驗證設置主要包括三部分內(nèi)容：1.驗證條件設置

設定單元格內(nèi)容的數(shù)據(jù)類型和范圍。2.輸入信息設置

用戶數(shù)據(jù)錄入過程中的信息提示。3.出錯警告設置

用戶錄入錯誤數(shù)據(jù)時，向用戶顯示的提示值息。6.1.1數(shù)據(jù)驗證設置【例6-1】對工作表中的“成績”所在列設置數(shù)據(jù)驗證，限定只能輸入范圍在0~100之間的整數(shù)；在數(shù)據(jù)錄入過程中，系統(tǒng)提示標題為“成績錄入”內(nèi)容為“有效范圍：0~100”的輸入信息；若用戶錄入錯誤，彈出標題為“成績錄入錯誤”內(nèi)容為“成績范圍：0~100”的出錯警告信息。1.驗證條件設置：0~100之間的整數(shù)2.輸入信息設置：標題為“成績錄入”內(nèi)容為“有效范圍：0~100”3.出錯警告設置：標題為“成績錄入錯誤”內(nèi)容為“成績范圍：0~100”1.驗證條件設置：0~100之間的整數(shù)2.輸入信息設置：標題為“成績錄入”內(nèi)容為“有效范圍：0~100”效果展示3.出錯警告設置：標題為“成績錄入錯誤”內(nèi)容為“成績范圍：0~100”效果展示6.1.2限制數(shù)據(jù)錄入的下拉列表設置通過下拉列表實現(xiàn)單元格內(nèi)容的選擇錄入，以限定錄入內(nèi)容的范圍。1.固定內(nèi)容的下拉列表

例如性別，內(nèi)容固定只有“男”和“女”兩個選擇。2.可變內(nèi)容的下拉列表

例如學院，根據(jù)不同學校的具體情況，錄入內(nèi)容會有所變化。1.固定內(nèi)容的下拉列表性別的下拉列表設置：

將“允許”下拉框中的數(shù)據(jù)類型

指定為“序列”；在“來源”文本框中輸入：

男,女

（注意必須是英文逗號）1.固定內(nèi)容的下拉列表性別的下拉列表設置：

將“允許”下拉框中的數(shù)據(jù)類型

指定為“序列”；在“來源”文本框中輸入：

男,女

（注意必須是英文逗號）效果展示2.可變內(nèi)容的下拉列表學院的下拉列表錄入效果：首先需要建立下拉列表中的數(shù)據(jù)（F1:F3區(qū)域）然后再進行下拉列表設置2.可變內(nèi)容的下拉列表學院的下拉列表設置

將“允許”下拉框中的數(shù)據(jù)類型

指定為“序列”；在“來源”文本框中設置下拉列表

數(shù)據(jù)區(qū)域（F1:F3）6.2數(shù)據(jù)排序主要內(nèi)容：6.2.1排序規(guī)則6.2.2單字段排序6.2.3多字段排序6.2.4自定義序列排序6.2.1排序規(guī)則①數(shù)值按照值大小排序，升序按從小到大排序，降序按從大到小排序。②英文按照字母順序排序，升序按A～Z排序，降序按Z～A排序。③漢字可以按照拼音字母的順序排序，升序按A～Z排序，降序按Z～A排序；

或者按照筆畫的順序排序。④文本中的數(shù)字<英文字母<漢字。⑤日期和時間按日期、時間的先后順序排序，升序按從前到后的順序排序，降序按從后向前的順序排序。⑥自定義序列在定義時所指定的順序是從小到大。⑦必須設置了單元格顏色或字體顏色后，才能按顏色進行排序。同樣，只有使用條件格式創(chuàng)建了圖標集，才能按圖標進行排序。6.2.2單字段排序操作步驟如下。①

選中要排序的字段，或者選中該字段的任意一個單元格。②通過執(zhí)行下列操作之一來完成排序：在“數(shù)據(jù)”選項卡的“排序和篩選”選項組中，若要按照升序排序，單擊“升序”按鈕；若要按照降序排序，單擊“降序”按鈕。在“開始”選項卡的“編輯”選項組中，單擊“排序和篩選”按鈕，在出現(xiàn)的選項中進行“升序”或“降序”的選擇。6.2.2單字段排序“班級”升序排序6.2.2單字段排序“總成績”降序排序6.2.3多字段排序操作步驟如下。①

選擇數(shù)據(jù)區(qū)域中任意一個單元格。②

在“數(shù)據(jù)”選項卡“排序和篩選”選項組中，單擊“排序”按鈕，打開“排序”對話框。③

按順序指定主要關鍵字、次要關鍵字。6.2.3多字段排序【例6-2】對學生成績數(shù)據(jù)按照班級升序，總成績降序排序。6.2.4自定義序列排序不按系統(tǒng)默認的排序規(guī)則，自己指定排列順序。例如：按照教師職稱級別（助教，講師，副教授，教授）排序按照成績等級（優(yōu)秀、良好、中等、及格、不及格）排序6.2.4自定義序列排序如何自己指定排列順序？——自定義序列6.2.4自定義序列排序按照自定義序列（助教，講師，副教授，教授）升序排序①

在工作表中選擇數(shù)據(jù)區(qū)域中的任意一個單元格。②

在“數(shù)據(jù)”選項卡“排序和篩選”選項組中，單擊“排序”按鈕，打開“排序”對話框。

③

指定“職稱”作為主要關鍵字，次序為“自定義序列”，并添加自定義序列：助教，講師，副教授，教授。6.2.4自定義序列排序按照自定義序列（助教，講師，副教授，教授）升序排序6.3數(shù)據(jù)篩選數(shù)據(jù)篩選就是在數(shù)據(jù)表中僅僅顯示滿足篩選條件的數(shù)據(jù)記錄，把不滿足篩選條件的數(shù)據(jù)記錄暫時隱藏起來。主要內(nèi)容：6.3.1自動篩選6.3.2高級篩選6.3.3刪除重復記錄6.3.1自動篩選創(chuàng)建自動篩選的步驟：①

選擇數(shù)據(jù)區(qū)域中的任意一個單元格。②

在“數(shù)據(jù)”選項卡“排序和篩選”選項組中，單擊“篩選”按鈕，

切換到自動篩選狀態(tài)。③

此時工作表第一行中的每個列標題旁都會出現(xiàn)三角下拉箭頭按鈕，通過單擊相應列的三角下拉箭頭按鈕，完成篩選條件的設置即可。6.3.1自動篩選【例6-3】利用自動篩選篩選出“財務01”班學生。6.3.1自動篩選【例6-3】在篩選出“財務01”班學生基礎上，繼續(xù)篩選出“高等數(shù)學”成績高于平均值的學生。6.3.1自動篩選【例6-3】在篩選出“財務01”班學生基礎上，繼續(xù)篩選出“高等數(shù)學”成績高于平均值的學生。6.3.2高級篩選1.創(chuàng)建高級篩選條件區(qū)域，然后才能實施高級篩選。①

在工作表的空白位置輸入要指定篩選條件的字段名稱。②

在字段名稱下方相應行中輸入該字段的篩選條件表達式。

不同行之間的篩選條件是“或”的關系；

同一行中不同列之間的篩選條件是“與”的關系。6.3.2高級篩選【例6-4】利用高級篩選功能，篩選出“高等數(shù)學”“英語”和“物理”三門課程的成績均在90分（含90）以上的學生。6.3.2高級篩選2.創(chuàng)建高級篩選。①選擇數(shù)據(jù)區(qū)域中的任意一個單元格。②在“數(shù)據(jù)”選項卡的“排序和篩選”選項組中，單擊“高級”篩選按鈕，打開“高級篩選”對話框。③“高級篩選”對話框的“列表區(qū)域”自動選擇了需要進行篩選的整個數(shù)據(jù)區(qū)域；在“條件區(qū)域”選擇已經(jīng)創(chuàng)建好的篩選條件區(qū)域。④默認情況下“在原有區(qū)域顯示篩選結果”，只隱藏不符合條件的記錄。6.3.2高級篩選【例6-4】利用高級篩選功能，篩選出“高等數(shù)學”“英語”和“物理”三門課程的成績均在90分（含90）以上的學生。篩選結果6.3.2高級篩選【例6-5】利用高級篩選功能，篩選出“高等數(shù)學”、“英語”和“物理”三門課程中有一門成績在90分（含90）以上的學生。6.3.2高級篩選【例6-5】利用高級篩選功能，篩選出“高等數(shù)學”、“英語”和“物理”三門課程中有一門成績在90分（含90）以上的學生。篩選結果6.3.3刪除重復記錄重復記錄是指所有字段值均相同，或者某幾個字段值相同的記錄。6.3.3刪除重復記錄1.利用“刪除重復項”刪除重復記錄①

選擇數(shù)據(jù)區(qū)域中的任意一個單元格，在“數(shù)據(jù)”選項卡的“數(shù)據(jù)工具”選項組中，單擊“刪除重復項”按鈕，打開“刪除重復項”對話框。②

在對話框中選擇重復數(shù)據(jù)所在的列。如果要求刪除所有字段內(nèi)容都完全相同的記錄，就要把所有列都勾選上。6.3.3刪除重復記錄1.利用“刪除重復項”刪除重復記錄6.3.3刪除重復記錄2、利用“高級篩選”刪除重復記錄①

選擇數(shù)據(jù)區(qū)域中的任意一個單元格。②

在“數(shù)據(jù)”選項卡的“排序和篩選”選項組中，單擊“高級”篩選按鈕，打開“高級篩選”對話框。③

篩選方式一般選擇“將篩選結果復制到其他位置”，以方便刪除重復記錄以后的處理操作；注意必須勾選“選擇不重復的記錄”。6.3.3刪除重復記錄2、利用“高級篩選”刪除重復記錄6.4分類匯總主要內(nèi)容：6.4.1創(chuàng)建分類匯總6.4.2分級顯示分類匯總6.4.3復制分類匯總結果6.4.4刪除分類匯總6.4.1創(chuàng)建分類匯總操作步驟如下。①

按分類字段列進行排序，使相同的記錄集中在一起。②

在“數(shù)據(jù)”選項卡的“分級顯示”選項組中，單擊“分類匯總”按鈕，

打開“分類匯總”對話框。③

在“分類匯總”對話框中，完成相應設置。④

若要進行多級分類匯總，重復①~③操作，但一定注意不要選中“替換當前分類匯總”復選框。6.4.1創(chuàng)建分類匯總【例6-6】對學生成績數(shù)據(jù)，要求按班級和性別分別匯總高等數(shù)學、英語和物理成績的平均值。分析：①

按照班級和性別排序②

按班級分類匯總③

按性別分類匯總，不勾選“替換當前分類匯總”6.4.1創(chuàng)建分類匯總【例6-6】對學生成績數(shù)據(jù)，要求按班級和性別分別匯總高等數(shù)學、英語和物理成績的平均值。分析：①

按照班級和性別排序6.4.1創(chuàng)建分類匯總【例6-6】對學生成績數(shù)據(jù)，要求按班級和性別分別匯總高等數(shù)學、英語和物理成績的平均值。分析：①

按照班級和性別排序②

按班級分類匯總6.4.1創(chuàng)建分類匯總【例6-6】對學生成績數(shù)據(jù)，要求按班級和性別分別匯總高等數(shù)學、英語和物理成績的平均值。分析：①

按照班級和性別排序②

按班級分類匯總③

按性別分類匯總，不勾選“替換當前分類匯總”6.4.1創(chuàng)建分類匯總【例6-6】分類匯總結果6.4.2分級顯示分類匯總1級顯示效果6.4.2分級顯示分類匯總2級顯示效果6.4.2分級顯示分類匯總3級顯示效果6.4.2分級顯示分類匯總4級顯示效果6.4.3復制分類匯總結果操作步驟如下①

顯示出要復制的匯總數(shù)據(jù)，掩藏其他數(shù)據(jù)，然后選取要復制的數(shù)據(jù)區(qū)域。②

在“開始”選項卡的“編輯”選項組中，單擊“查找和選擇”按鈕，選擇“定位條件”，打開“定位條件”對話框。③

在“定位條件”對話框中選擇“可見單元格”。④

此時執(zhí)行復制操作，將不會復制那些隱藏的明細數(shù)據(jù)。然后選定目標位置，執(zhí)行粘貼操作即可。【例6-7】將按班級匯總的高等數(shù)學、英語和物理成績的平均值復制到另一張工作表中。

復制結果6.4.3復制分類匯總結果6.4.4刪除分類匯總刪除分類匯總，使工作表還原成原始狀態(tài)：①

單擊包含分類匯總的任意一個單元格。②

在“數(shù)據(jù)”選項卡的“分級顯示”選項組中，單擊“分類匯總”按鈕。③

在“分類匯總”對話框中，單擊“全部刪除”按鈕。第7章數(shù)據(jù)透視分析主要內(nèi)容7.1數(shù)據(jù)透視表7.2數(shù)據(jù)透視圖7.1

數(shù)據(jù)透視表主要內(nèi)容：7.1.1創(chuàng)建數(shù)據(jù)透視表7.1.2編輯數(shù)據(jù)透視表7.1.3更新數(shù)據(jù)透視表7.1.4篩選數(shù)據(jù)透視表7.1.1創(chuàng)建數(shù)據(jù)透視表①

在工作表中選擇數(shù)據(jù)區(qū)域的任意一個單元格。②

在“插入”選項卡的“表格”選項組中，單擊“數(shù)據(jù)透視表”按鈕，打開“創(chuàng)建數(shù)據(jù)透視表”對話框。③

確定數(shù)據(jù)區(qū)域。一般系統(tǒng)會自動選定整個數(shù)據(jù)區(qū)域作為數(shù)據(jù)透視表的源數(shù)據(jù)。④

選擇放置數(shù)據(jù)透視表的位置，可以放置到新工作表或現(xiàn)有工作表中，默認選擇新工作表。⑤

單擊“確定”按鈕，在新工作表中創(chuàng)建數(shù)據(jù)透視表框架。⑥

在數(shù)據(jù)透視表框架中完成字段布局即可。7.1.1創(chuàng)建數(shù)據(jù)透視表關鍵問題——設計字段布局篩選器：用該區(qū)域中的字段來篩選整個報表，

對應分頁字段；行：將該區(qū)域中的字段顯示在左側的行，

對應行字段；列：將該區(qū)域中的字段顯示在頂部的列，

對應列字段；值：將該區(qū)域中的字段進行匯總分析，

對應數(shù)據(jù)項?！纠?-1】對成績數(shù)據(jù)進行數(shù)據(jù)透視分析，按學院分頁查看各個班級男女生成績的平均值。分析：篩選器：學院行：班級列：性別值：成績

平均值7.1.1創(chuàng)建數(shù)據(jù)透視表【例7-1】對成績數(shù)據(jù)進行數(shù)據(jù)透視分析，按學院分頁查看各個班級男女生成績的平均值。分析：篩選器：學院行：班級列：性別值：成績7.1.1創(chuàng)建數(shù)據(jù)透視表默認情況下對數(shù)值型字段總是進行“求和”運算，而對非數(shù)值型字段進行“計數(shù)”運算，因此默認生成的數(shù)據(jù)透視表對成績進行了求和。7.1.2編輯數(shù)據(jù)透視表創(chuàng)建完數(shù)據(jù)透視表后，可以：1.更改數(shù)據(jù)透視表的匯總方式2.在字段布局中添加或刪除字段3.刪除行總計或列總計4.改變數(shù)值顯示方式5.顯示明細數(shù)據(jù)6.數(shù)據(jù)透視表的清除和刪除7.1.2編輯數(shù)據(jù)透視表1.更改數(shù)據(jù)透視表的匯總方式①

在“數(shù)據(jù)透視表字段”窗格中單擊“值”區(qū)域中的字段，選擇“值字段設置”，打開“值字段設置”對話框。②

在值字段設置對話框中，選擇值匯總方式。7.1.2編輯數(shù)據(jù)透視表2.在字段布局中添加或刪除字段（1）添加字段右擊要添加的字段名稱，根據(jù)需要選擇“添加到報表篩選”“添加到列標簽”“添加到行標簽”或“添加到值”。或者用鼠標將字段逐個拖拽到相應區(qū)域中。（2）刪除字段清除勾選字段列表中需要刪除字段前的復選框?；蛘咴?個布局區(qū)域中，單擊字段名稱，然后選擇“刪除字段”命令。7.1.2編輯數(shù)據(jù)透視表3.刪除行總計或列總計在數(shù)據(jù)透視表中右擊要刪除的“總計”，然后選擇“刪除總計”命令?；蛘咴跀?shù)據(jù)透視表中右擊任意一個單元格，選擇“數(shù)據(jù)透視表選項”命令，在對話框的“匯總和篩選”選項卡中，去掉勾選“顯示行總計”和“顯示列總計”。7.1.2編輯數(shù)據(jù)透視表4.改變數(shù)值顯示方式默認情況下數(shù)據(jù)透視表都是按照“無計算”方式顯示數(shù)值項，為了更清晰地分析數(shù)據(jù)間的相關性，可以指定數(shù)據(jù)透視表為特殊的顯示方式，例如，以“差異”“百分比”“差異百分比”等方式顯示數(shù)據(jù)。7.1.2編輯數(shù)據(jù)透視表4.改變數(shù)值顯示方式以財務01班的平均值為基準，分析其他班級平均分的差異情況。①

在“數(shù)據(jù)透視表字段”窗格中單擊“值”區(qū)域中的字段，選擇“值字段設置”命令，打開“值字段設置”對話框。②

在“值顯示方式”選項卡中將值顯示方式設置為“差異”，然后指定差異的基準比較對象為“班級”“財務01”。7.1.2編輯數(shù)據(jù)透視表4.改變數(shù)值顯示方式以財務01班的平均值為基準，分析其他班級平均分的差異情況。

差異比較結果7.1.2編輯數(shù)據(jù)透視表5.顯示明細數(shù)據(jù)在數(shù)據(jù)透視表中右擊要查看明細數(shù)據(jù)的單元格，在彈出的快捷菜單中選擇“顯示詳細信息”命令。在數(shù)據(jù)透視表中雙擊要查看明細數(shù)據(jù)的單元格，系統(tǒng)會自動創(chuàng)建一個新工作表，顯示該匯總數(shù)據(jù)的明細數(shù)據(jù)。7.1.2編輯數(shù)據(jù)透視表6.數(shù)據(jù)透視表的清除和刪除

清除是指刪除所有篩選器、行、列、值的設置，但是數(shù)據(jù)透視表并沒有被刪除，

只是需要重新設計布局。①

單擊數(shù)據(jù)透視表的任意一個單元格。②

在數(shù)據(jù)透視表工具“分析”選項卡“操作”選項組中，單擊“清除”按鈕，然后選擇“全部清除”命令。

刪除不再使用的數(shù)據(jù)透視表，操作步驟如下：①

單擊數(shù)據(jù)透視表的任意一個單元格。②

在數(shù)據(jù)透視表工具“分析”選項卡“操作”選項組中，單擊“選擇”按鈕，然后選擇“整個數(shù)據(jù)透視表”命令。③

按【Delete】鍵刪除。7.1.3更新數(shù)據(jù)透視表更改數(shù)據(jù)透視表的源數(shù)據(jù)后，數(shù)據(jù)透視表中的匯總數(shù)據(jù)不會同步更新。所以需要更新數(shù)據(jù)透視表，使數(shù)據(jù)透視表重新對源數(shù)據(jù)進行匯總計算。操作步驟：①

單擊數(shù)據(jù)透視表的任意一個單元格。②

在數(shù)據(jù)透視表工具“分析”選項卡“數(shù)據(jù)”選項組中，單擊“刷新”按鈕，然后選擇“刷新”或“全部刷新”命令來重新匯總數(shù)據(jù)。7.1.4篩選數(shù)據(jù)透視表通過篩選可以在數(shù)據(jù)適視表中查看想要的數(shù)據(jù)，并隱藏不想顯示的數(shù)據(jù)。1．按標簽篩選2．按值篩選3．按選定內(nèi)容篩選4．刪除篩選7.1.4篩選數(shù)據(jù)透視表1．按標簽篩選在數(shù)據(jù)透表中、單擊列標簽或行標簽右側的下拉箭頭，選擇“標簽篩選”，然后根據(jù)實際需要設置篩選條件。7.1.4篩選數(shù)據(jù)透視表2．按值篩選在數(shù)據(jù)透表中、單擊列標簽或行標簽右側的下拉箭頭，在篩選下拉列表中選擇“值篩選”，然后根據(jù)實際需要設置篩選條件。7.1.4篩選數(shù)據(jù)透視表3．按選定內(nèi)容篩選在數(shù)據(jù)視表中，右擊相應行或列標題中的內(nèi)容，在快捷菜單中選擇“篩選”，然后選擇“僅保留所選項目”或“隱藏所選項目”命令。7.1.4篩選數(shù)據(jù)透視表4．刪除篩選要刪除數(shù)據(jù)透視表中的所有篩選，操作步驟如下：①

單擊數(shù)據(jù)透視表的任意一個單元格。②

在數(shù)據(jù)透視表工具“分析”選項卡“操作”選項組中，單擊“清除”按鈕，然后選擇“清除篩選”命令。7.2

數(shù)據(jù)透視圖主要內(nèi)容：7.2.1通過數(shù)據(jù)透視表創(chuàng)建數(shù)據(jù)透視圖7.2.2通過數(shù)據(jù)區(qū)域創(chuàng)建數(shù)據(jù)透視圖7.2.3刪除數(shù)據(jù)透視圖7.2.4設置數(shù)據(jù)透視圖7.2.1通過數(shù)據(jù)透視表創(chuàng)建數(shù)據(jù)透視圖如果已經(jīng)創(chuàng)建了數(shù)據(jù)透視表，可以利用數(shù)據(jù)透視表直接創(chuàng)建數(shù)據(jù)透視圖。①

單擊數(shù)據(jù)透視表中的任意一個單元格。②

在“插入”選項卡“圖表”選項組中，單擊“數(shù)據(jù)透視圖”按鈕，選擇“數(shù)據(jù)透視圖”命令，打開“插入圖表”對話框。③

在插入圖表對話框中根據(jù)實際需要選擇一種圖表類型，單擊“確定”按鈕即可得到數(shù)據(jù)透視圖。7.2.1通過數(shù)據(jù)透視表創(chuàng)建數(shù)據(jù)透視圖【例7-2】基于已有的數(shù)據(jù)透視表創(chuàng)建的數(shù)據(jù)透視圖。7.2.2通過數(shù)據(jù)區(qū)域創(chuàng)建數(shù)據(jù)透視圖操作步驟①

在工作表中選擇數(shù)據(jù)區(qū)域中的任意一個單元格。②

在“插入”選項卡的“圖表