Excel 2016數(shù)據(jù)處理與分析應(yīng)用教程 課件 第6章 數(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ù)驗證設(shè)置6.1.2限制數(shù)據(jù)錄入的下拉列表設(shè)置6.1.1數(shù)據(jù)驗證設(shè)置主要包括三部分內(nèi)容：1.驗證條件設(shè)置

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

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

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

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

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

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

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

男,女

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

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

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

男,女

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

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

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

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

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

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

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

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

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

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

③

指定“職稱”作為主要關(guān)鍵字，次序為“自定義序列”，并添加自定義序列：助教，講師，副教授，教授。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刪除重復(fù)記錄6.3.1自動篩選創(chuàng)建自動篩選的步驟：①

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

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

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

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

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

在字段名稱下方相應(yīng)行中輸入該字段的篩選條件表達(dá)式。

不同行之間的篩選條件是“或”的關(guān)系；

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

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

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

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

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

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

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

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

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

在“分類匯總”對話框中，完成相應(yīng)設(shè)置。④

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

按照班級和性別排序②

按班級分類匯總③

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

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

按照班級和性別排序②

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

按照班級和性別排序②

按班級分類匯總③

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

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

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