辦公軟件應(yīng)用（Office 2016）課件：Excel 2016表格數(shù)據(jù)的排版、篩選

Excel2016表格數(shù)據(jù)的排版、篩選與匯總

任務(wù)一：排序年終考評成績表

任務(wù)一：排序年終考評成績表一般在查看數(shù)據(jù)的時候，往往需要將數(shù)據(jù)進(jìn)行一定的順序排列，便于我們對數(shù)據(jù)進(jìn)行查找與分析。例如，我們對員工年終考評時、學(xué)生成績等各進(jìn)行查看時，經(jīng)常需要將員工年終考評成績由高低順序排列，便于我們清楚查看；也可以根據(jù)序號，編號對數(shù)據(jù)進(jìn)行清晰查看。本例將以員工年終考評成績?yōu)槔榻B員工年終考評成績的方法及技巧。任務(wù)一：排序年終考評成績表教學(xué)目標(biāo)掌握數(shù)據(jù)高低排序方法重點和難點掌握數(shù)據(jù)篩選到新的工作表方法

資源同步文件素材文件：資源/素材文件/第七章/員工考評成績表.xlsx結(jié)果文件：資源/成品文件/第七章/員工考評成績表.xlsx任務(wù)一：排序年終考評成績表教學(xué)任務(wù)：一、按成績高低進(jìn)行排序二、利用自動篩選功能篩選成績表數(shù)據(jù)三、使用高級篩選數(shù)據(jù)任務(wù)一：排序年終考評成績表為了方便根據(jù)序號查看成績，需要使用排序功能對指定列中的數(shù)據(jù)按人員序號進(jìn)行排列。任務(wù)一：排序年終考評成績表（1）應(yīng)用“排序”按鈕排序數(shù)據(jù)使用“排序”選項對數(shù)據(jù)排序的具體操作如下。第一步：將文本插入點定位于“工號”列中的任意單元格；第二步：單擊“開始”選項卡“編輯”組中的“排序和篩選”按鈕；第三步：在彈出的下拉菜單中的選擇“升序”命令即可根據(jù)當(dāng)前列中的數(shù)據(jù)，按從高到低的順序進(jìn)行排序，如下圖所示任務(wù)一：排序年終考評成績表任務(wù)一：排序年終考評成績表（2）應(yīng)用表格篩選功能快速排序數(shù)據(jù)將單元格轉(zhuǎn)換為表格后，在表格中自動啟動篩選功能，利用列標(biāo)題拉下菜單中的“排序”命令可快速對表格進(jìn)行排序。具體操作方法如下。任務(wù)一：排序年終考評成績表①將單元格區(qū)域轉(zhuǎn)換為表格第一步：點擊圖表中任意有內(nèi)容表格；第二步：單擊“插入”選項卡“表格”按鈕；第三步：在打開的“創(chuàng)建表”對話框中設(shè)置表數(shù)據(jù)的來源為表格中的A1:J22單元區(qū)域；第四步：單擊“確定”按鈕將該單元格區(qū)域轉(zhuǎn)換為表格對象，如下圖所示。任務(wù)一：排序年終考評成績表任務(wù)一：排序年終考評成績表②設(shè)置排序方式第一步：單擊H1單元格右側(cè)的下拉按鈕；第二步：在彈出的下拉菜單中選擇“降序”命令，即可依據(jù)“平均分”列中的數(shù)據(jù)，按從高到低的順序?qū)Ρ砀駭?shù)據(jù)排序，如下圖所示。任務(wù)一：排序年終考評成績表任務(wù)一：排序年終考評成績表（3）根據(jù)多個關(guān)鍵字排序數(shù)據(jù)在對表格數(shù)據(jù)進(jìn)行排序時，有時進(jìn)行排序的字段中會在多個相同的數(shù)據(jù)，這時就需要讓數(shù)據(jù)相同的數(shù)據(jù)項按另一個字段的數(shù)據(jù)進(jìn)行排序。例如，本例中將根據(jù)平均成績的將序排列，有相同的則以總分排序具體操作如下。任務(wù)一：排序年終考評成績表第一步：選擇“排序和篩選”；第二步：執(zhí)行“自定義排序”命令，如下圖1所示；第三步：在“主要關(guān)鍵字”行中設(shè)置列為“平均分”、排序依據(jù)為“數(shù)值”、次序為“降序”；第四步：單擊“添加條件”按鈕；第五步：在“次要關(guān)鍵字”行中設(shè)置列為“總分”、排序依據(jù)為“數(shù)值”、次序為“降序”，如下圖2所示。任務(wù)一：排序年終考評成績表任務(wù)一：排序年終考評成績表任務(wù)一：排序年終考評成績表二、利用自動篩選功能篩選成績表數(shù)據(jù)（1）篩選指定類別的數(shù)據(jù)部分時候需要篩選指定類別的數(shù)據(jù)，如下表格需要篩選合格人數(shù)，排除不合格人數(shù)，具體操作方法如下。第一步：單擊J1單元格右側(cè)的下拉按鈕；第二步：在彈出的下拉菜單取消選中不需要顯示的數(shù)據(jù)值“不適合”選擇前的復(fù)選框；第三步：單擊“確定”按鈕即可，如下圖所示。任務(wù)一：排序年終考評成績表任務(wù)一：排序年終考評成績表（2）清除篩選對表格數(shù)據(jù)進(jìn)行篩選后，不滿住條件的數(shù)據(jù)仍然存在與表格中，只是被隱藏篩選的字段右側(cè)的下拉按鈕，如還需要可清除篩選，具體操作方法如下。第一步：單擊J1單元格右側(cè)的下拉按鈕；第二步：在下拉菜單中選擇“從***中清除篩選”命令即可，如下圖所示。任務(wù)一：排序年終考評成績表任務(wù)一：排序年終考評成績表（3）篩選指定范圍的數(shù)據(jù)在以數(shù)值類型的數(shù)據(jù)為篩選條件時，常常需要篩選出一定范圍的數(shù)據(jù)而非確切的多個數(shù)值。例如，本例中要從上一次的篩選結(jié)果中篩選出“科目1”成績在80分以上的數(shù)據(jù)，具體操作方法如下。任務(wù)一：排序年終考評成績表第一步：單擊C1單元格右側(cè)的下拉按鈕；第二步：在彈出的下拉菜單中選擇“數(shù)字篩選”命令；第三步：在彈出的下級菜單中選擇“大于”命令，如下圖所示。任務(wù)一：排序年終考評成績表任務(wù)一：排序年終考評成績表三、使用高級篩選數(shù)據(jù)在對表格中的數(shù)據(jù)進(jìn)行篩選時，為不影響原數(shù)據(jù)表中的顯示，常常需要將篩選結(jié)果放置到指定工作表或其他單元格區(qū)域，此時可以應(yīng)用高級篩選功能篩選數(shù)據(jù)。要使用高級篩選功能，首先需要在表格中任意空白單元格區(qū)域內(nèi)列舉篩選條件，然后應(yīng)用“高級篩選”命令實現(xiàn)篩選功能。任務(wù)一：排序年終考評成績表（1）將合格數(shù)據(jù)篩選到新工作表本例中需要將所有平均成績合格的數(shù)據(jù)篩選到新工作表中，具體操作方法如下。①新建、重命名工作表第一步:點擊加號新建一個工作表標(biāo)簽；第二步:雙擊“Sheet2”工作表標(biāo)簽，更改工作表名稱為“平均分合格”，如下圖所示。任務(wù)一：排序年終考評成績表任務(wù)一：排序年終考評成績表②制作條件區(qū)域并進(jìn)行篩選第一步：分別在“平均分合格”工作表中的A1輸入“平均分”，A2輸入“＞=60”文本，即列舉出篩選的條件“平均分大于等于60”；第二步：單擊“平均分”下拉按鈕，點擊“文本篩選”；第三步：選擇子菜單內(nèi)的“自定義篩選”；第四步：在彈出框內(nèi)選擇“大于或等于”“60”，如下圖所示。任務(wù)一：排序年終考評成績表任務(wù)一：排序年終考評成績表任務(wù)一：排序年終考評成績表③設(shè)置高級篩選列表區(qū)域第一步：打開“高級篩選”對話框；第二步：在“方式”欄中選中“將篩選結(jié)果復(fù)制到其他位置”單選按鈕；第三步：在“列表區(qū)域”參數(shù)框中引用“成績表”工作表中所有數(shù)據(jù)單元格區(qū)域（含列標(biāo)題），如下圖所示。任務(wù)一：排序年終考評成績表任務(wù)一：排序年終考評成績表④設(shè)置高級篩選條件區(qū)域第一步：在“條件區(qū)域”參數(shù)框中引用“平均分合格”工作室表中的A1:A2但語言各區(qū)域；第二步：在“復(fù)制到”參數(shù)框中引用“平均分合格”工作表中的A3單元格，如下圖所示。任務(wù)一：排序年終考評成績表任務(wù)一：排序年終考評成績表⑤完成并查看篩選結(jié)果單擊“高級篩選”對話框中的“確定”按鈕后，即可在“平均分合格”工作表中的到篩選的結(jié)果，該結(jié)果列表的位置從A3單元格開始，如下圖所示。任務(wù)一：排序年終考評成績表任務(wù)一：排序年終考評成績表（2）將所有科目均合格的數(shù)據(jù)篩選到新工作表本例中需要將所有科目均合格的數(shù)據(jù)篩選到新工作表中，具體操作方法如下。①制作條件區(qū)域并進(jìn)行篩選第一步：新建工作表并設(shè)置工作表名稱為“所有科目合格”；第二步：在A1:D2單元格區(qū)域中輸入如下圖所示的數(shù)據(jù)內(nèi)容作為條件區(qū)域；第三步：單擊“數(shù)據(jù)”選項卡“排序和篩選”組中的“高級”按鈕；第四步：打開“高級篩選”對話框，在“方式”欄中選中“將篩選結(jié)果復(fù)制到其他位置”單選按鈕；第五步：在“列表區(qū)域”參數(shù)框中引用“成績表”工作表中所有數(shù)據(jù)單元格區(qū)域（含列標(biāo)題），在“條件區(qū)域”參數(shù)框中引用“所有科目合格”工作表中的A1:D單元格區(qū)域，在“復(fù)制到”參數(shù)框中引用“所有科目合格”工作表中的A3單元格，如下圖所示。任務(wù)一：排序年終考評成績表任務(wù)一：排序年終考評成績表②完成篩選并查看結(jié)果單擊“高級篩選”對話框中的“確定”按鈕，完成篩選并查看篩選結(jié)果，如右圖所示。謝謝！Excel2016表格數(shù)據(jù)的排版、篩選與匯總

任務(wù)二：制作銷售情況分析表任務(wù)二：制作銷售情況分析表在日常辦公應(yīng)用中，常需要對大量的數(shù)據(jù)按不同的類別進(jìn)行匯總計算。例如，在銷售情況匯總過程中，需要對不同時間的銷售進(jìn)行匯總、對不同產(chǎn)品的銷售情況進(jìn)行匯總。本例將應(yīng)用Excel中的合并計算機和分類匯總功能對銷售情況分析表中的數(shù)據(jù)進(jìn)行分析。任務(wù)二：制作銷售情況分析表教學(xué)目標(biāo)掌握郵件合并功能的使用、中文簡繁轉(zhuǎn)換的方法重點和難點郵件合并功能的使用

資源同步文件素材文件：資源/素材文件/第七章/制作銷售情況分析表.xlsx結(jié)果文件：資源/成品文件/第七章/制作銷售情況分析表.xlsx任務(wù)二：制作銷售情況分析表教學(xué)任務(wù)：一、銷售金額的匯總二、應(yīng)用分類匯總功能匯總數(shù)據(jù)三、篩選數(shù)據(jù)任務(wù)二：制作銷售情況分析表有時候我們需要按某一個分類將數(shù)據(jù)結(jié)果進(jìn)行匯總計算，可以應(yīng)用Excel中的合并計算出每月總的銷售金額，并將該結(jié)果列舉到新工作表中，具體操作方法如下：任務(wù)二：制作銷售情況分析表（1）各月銷售金額匯總①制作匯總表并進(jìn)行計算第一步：新建工作表并設(shè)置工作表名稱為“各月銷售總額”；第二步：在A1單元格區(qū)域中輸入“日期”，B1單元格輸入“銷售金額”；第三步：選擇A2單元格；第四步：選擇“數(shù)據(jù)”選項卡“數(shù)據(jù)工具”組中的“合并計算”按鈕，如下圖所示。任務(wù)二：制作銷售情況分析表任務(wù)二：制作銷售情況分析表②設(shè)置合并計算參數(shù)第一步：經(jīng)上部操作彈出“合并計算”對話框，在“函數(shù)”下拉列表框中選擇“求和”選項；第二步：在“引用設(shè)置”參數(shù)框中引用“原始數(shù)據(jù)表”工作表中“日期”和“銷售金額”列中的數(shù)據(jù)，即D2:E20單元格區(qū)域；第三步：在“標(biāo)簽位置”欄中選中“最左列”復(fù)選框；第四步：單擊“添加”按鈕，如下圖所示。任務(wù)二：制作銷售情況分析表③合并計算并查看合并結(jié)果單擊“合并計算”對話框中的“確定”按鈕，即可在“各月銷售總額”工作表中計算出各月的銷售總額，如下圖所示。任務(wù)二：制作銷售情況分析表（2）按產(chǎn)品匯總銷售金額在素材文件“銷售情況分析表”中列舉了各產(chǎn)品各月的銷售金額，現(xiàn)需要計算出各產(chǎn)品總的銷售金額，并將該結(jié)果列舉到新工作表中，為了與“原始數(shù)據(jù)表”工作表中的數(shù)據(jù)同步，即若“原始數(shù)據(jù)表”工作表中的數(shù)據(jù)發(fā)生變化，該匯總結(jié)果也會隨之變化，此時可在進(jìn)行合并計算時選中“創(chuàng)建指向源數(shù)據(jù)的鏈接”復(fù)選框，具體操作方法如下。任務(wù)二：制作銷售情況分析表①制作匯總表并進(jìn)行合并計算第一步：新建工作表并設(shè)置工作表名稱為“各產(chǎn)品銷售金額匯總”；第二步：選擇A1單元格；第三步：單擊“數(shù)據(jù)”選項卡“數(shù)據(jù)工具”組中的“合并計算”按鈕一個圖標(biāo)，打開“合并計算”對話框，如下圖所示。任務(wù)二：制作銷售情況分析表任務(wù)二：制作銷售情況分析表②設(shè)置合并計算參數(shù)第一步：在“函數(shù)”下拉列表框中選擇“求和”選項；第二步：在“引用位置”參數(shù)框中引用“原始數(shù)據(jù)表”工作表中“產(chǎn)品”、“日期”和“銷售金額”列中的數(shù)據(jù)，即C2:E20單元格區(qū)域；第三步：在“標(biāo)簽位置”欄中選中“最左列”和“創(chuàng)建指向源數(shù)據(jù)的鏈接”復(fù)選框；第四步：單擊“添加”按鈕，如下圖所示。任務(wù)二：制作銷售情況分析表任務(wù)二：制作銷售情況分析表③合并計算并查看合并結(jié)果第一步：單擊“合并計算”對話框中的“確定”按鈕，第二步：在“各產(chǎn)品銷售總額”工作表中計算出產(chǎn)品的銷售總額；第三步：刪除結(jié)果中不需要的空列，并在頂部插入一行，添加列標(biāo)題行，最終效果如下圖所示。任務(wù)二：制作銷售情況分析表任務(wù)二：制作銷售情況分析表（3）關(guān)于合并計算的應(yīng)用應(yīng)用合并計算可以快速將同類別的數(shù)據(jù)進(jìn)行合并，進(jìn)行合并的數(shù)據(jù)可以在不同的工作表中，只要數(shù)據(jù)具有相同標(biāo)簽即可進(jìn)行合并。在進(jìn)行合并運算時，表明數(shù)據(jù)類別的數(shù)據(jù)稱為“標(biāo)簽”，它可以存在于進(jìn)行合并的數(shù)據(jù)的最左列，亦可存在于數(shù)據(jù)的第一行，例如本例中就是將“產(chǎn)品稱”作為合并的標(biāo)簽，對所有相同標(biāo)簽的數(shù)據(jù)進(jìn)行了求和的計算。在對數(shù)據(jù)進(jìn)行合并計算時，除使用“求和”方式外，在“合并計算”對話框中的“函數(shù)”下拉列表框中還可以選擇其他計算方式合并數(shù)據(jù)，如“平均值”、“最大值”和“乘積”等。任務(wù)二：制作銷售情況分析表二、應(yīng)用分類匯總功能匯總數(shù)據(jù)應(yīng)用合并計算可以快速對某一類數(shù)據(jù)進(jìn)行匯總計算，合并之后重在體現(xiàn)其計算結(jié)果，無法清晰地顯示出明細(xì)數(shù)據(jù)。為對不同類別的數(shù)據(jù)進(jìn)行匯總，同時能更清晰地查看匯總后的明細(xì)數(shù)據(jù)，可以使用分類匯總功能。在使用分類匯總前，需要將要進(jìn)行分類匯總的數(shù)據(jù)進(jìn)行排序，使類別相同的數(shù)據(jù)位置排列在一起，從而實現(xiàn)分類的功能，然后再使用分類匯總命令按相應(yīng)的分類進(jìn)行數(shù)據(jù)匯總。任務(wù)二：制作銷售情況分析表（1）按部門分類匯總銷售金額在本例中，為清晰地查看到各部門的銷售情況，可按“部門”字段進(jìn)行分類，并匯總銷售金額，具體操作方法如下。任務(wù)二：制作銷售情況分析表①制作匯總表并按部門排列數(shù)據(jù)第一步：復(fù)制“原始數(shù)據(jù)表”工作表并將其重命名為“各部門銷售表”;第二步：選擇“部門”列中的任意單元格；第三步：單擊“數(shù)據(jù)”選項卡“排序和篩選”組中的“升序”按鈕，按“部門”列中數(shù)據(jù)的升序進(jìn)行排序，如下圖所示。任務(wù)二：制作銷售情況分析表任務(wù)二：制作銷售情況分析表②執(zhí)行“分類匯總”命令單擊“數(shù)據(jù)”選項卡“分級顯示”組中的“分類匯總”按鈕，打開“分類匯總”對話框，如下圖所示任務(wù)二：制作銷售情況分析表③設(shè)置分類匯總參數(shù)第一步：在“分類字段”下拉列表框中選擇“部門”選項；第二步：在“匯總方式”下拉列表框中選擇“求和”選項；第三步：在“選定匯總項”列表框中選中“銷售金額”復(fù)選框；第四步：單擊“確定”按鈕，如下圖所示。任務(wù)二：制作銷售情況分析表任務(wù)二：制作銷售情況分析表④查看匯總結(jié)果完成分類匯總后在工作表中的數(shù)據(jù)將分級顯示，單擊左側(cè)的“一”按鈕可隱藏明細(xì)數(shù)據(jù)，單擊“+”按鈕可顯示出該分類中的明細(xì)數(shù)據(jù)，如下圖所示。任務(wù)二：制作銷售情況分析表（2）按姓名分類匯總平均銷售金額在本例中，為清晰地查看各員工的平均銷售金額，可按“姓名”字段進(jìn)行分類，并以平均值方式匯總銷售金額，具體操作方法如下。任務(wù)二：制作銷售情況分析表①制作匯總表并按姓名排列數(shù)據(jù)第一步：復(fù)制“原始數(shù)據(jù)表”工作表并將其重命名為“按姓名分類”；第二步：選擇“姓名”列中的任意單元格；第三步：單擊“數(shù)據(jù)”選項卡“排序和篩選”組中的“升序”按鈕，按“姓名”列中數(shù)據(jù)的升序進(jìn)行排序，如下圖所示。任務(wù)二：制作銷售情況分析表任務(wù)二：制作銷售情況分析表②執(zhí)行“分類匯總”命令單擊“數(shù)據(jù)”選項卡“分級顯示”組中的“分類匯總”按鈕，打開“分類總匯”對話框，如下圖所示。任務(wù)二：制作銷售情況分析表③設(shè)置分類匯總參數(shù)第一步：在“分類字段”下拉列表框中選擇“姓名”選項；第二步：在“匯總方式”下拉列表框中選擇“平均值”選項；第三步：在“選定匯總表”列表框中選中“銷售金額”復(fù)選框；第四步：單擊“確定”按鈕，如下圖所示。任務(wù)二：制作銷售情況分析表任務(wù)二：制作銷售情況分析表④查看匯總結(jié)果完成分類總匯后如下圖所示。任務(wù)二：制作銷售情況分析表（3）按商品和日期分類匯總銷售金額在本例中，為清晰地查看各種產(chǎn)品各月的銷售情況，可利用分類匯總功能按產(chǎn)品進(jìn)行分類匯總，并在該分類匯總的基礎(chǔ)上再根據(jù)日期分類匯總，即在同一表格區(qū)域中使用兩次分類匯總。為使用兩次分類匯總，在對表格中的數(shù)據(jù)進(jìn)行排序時，應(yīng)以第一次分類的字段為排序的主要關(guān)鍵字，以第二次分類的字段為排序的次要關(guān)鍵字，在排序之后進(jìn)行兩次分類匯總即可，具體操作方法如下。任務(wù)二：制作銷售情況分析表①制作匯總表并列數(shù)據(jù)第一步：復(fù)制“原始數(shù)據(jù)表”工作表并將其重命名為“按產(chǎn)品和日期分類”；第二步：選擇表格中的任意單元格；第三步：單擊“數(shù)據(jù)”選項卡“排序口篩選”組中的“排序”按鈕，如下圖所示。任務(wù)二：制作銷售情況分析表任務(wù)二：制作銷售情況分析表②設(shè)置排序關(guān)鍵字及選項第一步：打開“排序”對話框，單擊“添加條件”按鈕；第二步：設(shè)置“主要關(guān)鍵字”為“產(chǎn)品”，次要關(guān)鍵字為“日期”；第三步：單擊“選項”按鈕，如下圖所示。任務(wù)二：制作銷售情況分析表任務(wù)二：制作銷售情況分析表③執(zhí)行“分類匯總”命令第一步：打開“排序選項”對話框,選中“筆劃排序"單選按鈕；第二步：單擊“確定”按鈕，如下圖所示。任務(wù)二：制作銷售情況分析表④執(zhí)行“分類匯總”命令單擊“數(shù)據(jù)”選項卡“分級顯示”組中的“分類匯總”按鈕，如下圖所示。任務(wù)二：制作銷售情況分析表⑤設(shè)置分類匯總參數(shù)第一步：打開“分類匯總”對話框，在“分類字段”下拉列表框中選擇“產(chǎn)品”選項；第二步：在“匯總方式”下拉列表框中選擇“求和”選項;第三步：在“選定匯總項”列表框中選中“銷售金額”復(fù)選框；第四步：單擊“確定”按鈕，如下圖所示。任務(wù)二：制作銷售情況分析表任務(wù)二：制作銷售情況分析表⑥再次執(zhí)行“分類匯總”命令再次單擊“數(shù)據(jù)”選項卡“分級顯示”組中的“分類匯總”按鈕，如下圖所示。任務(wù)二：制作銷售情況分析表⑦設(shè)置分類匯總參數(shù)第一步：打開“分類匯總”對話框，在“分類字段”下拉列表框中選擇“日期”選項；第二步：在“匯總方式”下拉列表框中選擇“求和”選項；第三步：在“選定匯總項”列表框中選中“銷售金額”復(fù)選框;第四步：取消選中“替換當(dāng)前分類匯總”復(fù)選框;第