項目4 處理異常值

處理異常值07-2月-25項目背景與目標(biāo)對數(shù)據(jù)進(jìn)行分析，如果存在異常值，那么分析結(jié)果可能會不準(zhǔn)確，因此針對【消費(fèi)情況（原始）】工作表的原始數(shù)據(jù)，查看并刪除異常值將顯得非常重要。一般情況下，學(xué)生在食堂消費(fèi)不會過高，消費(fèi)時間也應(yīng)在處于食堂的正常營業(yè)時間內(nèi)，當(dāng)出現(xiàn)超過最大消費(fèi)金額或非營業(yè)時間的消費(fèi)記錄時，說明這部分?jǐn)?shù)據(jù)為異常數(shù)據(jù)。因此需要對【消費(fèi)情況（原始）】工作表中的“消費(fèi)金額（元）”“消費(fèi)類型”“消費(fèi)時間”列進(jìn)行異常值分析，并刪除存在的異常值。2022/9/191處理消費(fèi)時間異常目錄處理消費(fèi)金額異常2項目分析2022/9/19查找并處理“消費(fèi)金額（元）”列的異常值。查找并處理“消費(fèi)類型”列中不屬于消費(fèi)的數(shù)據(jù)。0102通過【排序】功能查看“消費(fèi)金額（元）”列異常的數(shù)據(jù)，并進(jìn)行刪除通過【數(shù)據(jù)透視表】功能顯示每種消費(fèi)類型的數(shù)據(jù)，刪除“消費(fèi)類型”列中不屬于消費(fèi)類型的數(shù)據(jù)新建一個名為“消費(fèi)行為分析表-處理異常值”的工作簿。將【消費(fèi)行為分析表-獲取文本數(shù)據(jù)】工作簿中的【消費(fèi)情況（原始）】工作表復(fù)制至【消費(fèi)行為分析表-處理異常值】工作簿的【Sheet1】工作表中。將【Sheet1】重命名為“消費(fèi)記錄（清洗完數(shù)據(jù)）”。處理消費(fèi)金額異常2022/9/19打開【排序】對話框：選中E列；在【數(shù)據(jù)】選項卡的【排序和篩選】命令組中，單擊【排序】圖標(biāo)；彈出【排序提醒】對話框，單擊【排序】按鈕后會彈出【排序】對話框。查看消費(fèi)金額最大的數(shù)據(jù)在【消費(fèi)記錄（清洗完數(shù)據(jù)）】工作表中，根據(jù)消費(fèi)金額的大小進(jìn)行降序排列。2022/9/19查看消費(fèi)金額最大的數(shù)據(jù)設(shè)置主要關(guān)鍵字：在【排序】對話框的【主要關(guān)鍵字】欄的第一個下拉框中單擊按鈕；在下拉列表中選擇【消費(fèi)金額（元）】選項；排序依據(jù)下拉列表中選擇【單元格值】選項；次序為【降序】選項。2022/9/19查看消費(fèi)金額最大的數(shù)據(jù)單擊【確定】按鈕，即可根據(jù)消費(fèi)金額的大小進(jìn)行降序排列，得到的效果如圖。2022/9/19查看消費(fèi)金額最大的數(shù)據(jù)刪除消費(fèi)金額異常的數(shù)據(jù)：假設(shè)消費(fèi)金額大于或等于300元為異常消費(fèi)金額，可以通過刪除的方式處理異常值。選中【消費(fèi)金額（元）】異常值的行并進(jìn)行右鍵單擊；在彈出的快捷菜單中選擇【刪除】命令，即可刪除消費(fèi)金額異常值，刪除后效果如圖：2022/9/19打開【創(chuàng)建數(shù)據(jù)透視表】對話框：單擊數(shù)據(jù)區(qū)域內(nèi)任意一個單元格；在【插入】選項卡的【表格】命令組中，單擊【數(shù)據(jù)透視表】圖標(biāo)；在彈出【創(chuàng)建數(shù)據(jù)透視表】對話框中，默認(rèn)選擇的數(shù)據(jù)為整個數(shù)據(jù)區(qū)域，放置數(shù)據(jù)透視表的位置默認(rèn)為新工作表，也可以指定將其放置在現(xiàn)有工作表中。查看每個消費(fèi)類型的計數(shù)在【消費(fèi)記錄（清洗完數(shù)據(jù)）】工作表中手動創(chuàng)建【消費(fèi)類型】的數(shù)據(jù)透視表，查看【消費(fèi)類型】列每個唯一值的計數(shù)。2022/9/19查看每個消費(fèi)類型的計數(shù)創(chuàng)建空白數(shù)據(jù)透視表：單擊【確定】按鈕，Excel將創(chuàng)建一個空白數(shù)據(jù)透視表，并顯示【數(shù)據(jù)透視表字段】窗格。2022/9/19查看每個消費(fèi)類型的計數(shù)添加字段：將【消費(fèi)類型】拖曳至【行】區(qū)域和【值】區(qū)域。2022/9/19查看每個消費(fèi)類型的計數(shù)設(shè)置數(shù)據(jù)透視表樣式：打開數(shù)據(jù)透視表格式的下拉列表：在【設(shè)計】選項卡的【數(shù)據(jù)透視表樣式】命令組中，單擊

按鈕；選擇樣式：在下拉列表中選擇其中一種樣式，即可更改數(shù)據(jù)透視表的樣式，此處選擇【中等色】列表的【淺橙色，數(shù)據(jù)透視表樣式中等深淺3】選項。2022/9/191處理消費(fèi)時間異常目錄處理消費(fèi)金額異常2項目分析2022/9/19在【消費(fèi)記錄（清洗完數(shù)據(jù)）】工作表中，所有的營業(yè)地點(diǎn)的營業(yè)時間為5:00~24:00，因此0:00~5:00之內(nèi)的所有消費(fèi)記錄屬于異常情況，需要對0:00~5:00之內(nèi)的消費(fèi)數(shù)據(jù)進(jìn)行刪除。0102采用TEXT函數(shù)、HOUR函數(shù)、WEEKDAY函數(shù)提取出“消費(fèi)時間”列的日期、時間和星期通過【篩選】功能處理消費(fèi)時間異常的數(shù)據(jù)TEXT函數(shù)可通過格式代碼向數(shù)字應(yīng)用格式，進(jìn)而更改數(shù)字的顯示方式。

TEXT(value,format_text)提取消費(fèi)時間中的日期、小時、星期TEXT函數(shù)2022/9/19參數(shù)參數(shù)解釋value必需。表示要應(yīng)用格式的數(shù)字，可以是數(shù)值、計算結(jié)果為數(shù)字值的公式，或?qū)Π瑪?shù)字值的單元格的引用format_text必需。表示文本字符串的數(shù)字格式，為“單元格格式”對話框中“數(shù)字”選項卡上“分類”框中的文本形式的數(shù)字格式插入“日期”列：右鍵單擊E列；在彈出的快捷菜單中選擇【插入】命令，即可插入新的一列；在新的一列的第一個單元格輸入“日期”。提取消費(fèi)時間中的日期、小時、星期使用TEXT函數(shù)提取消費(fèi)時間的日期：2022/9/19提取消費(fèi)時間中的日期、小時、星期輸入公式：選擇單元格E2，輸入“=TEXT(D2,"yyyy/m/d")”。確定公式：按下【Enter】鍵，即可用TEXT函數(shù)提取日期。2022/9/19提取消費(fèi)時間中的日期、小時、星期填充公式：將鼠標(biāo)指針移至單元格E2的右下角，當(dāng)指針變?yōu)楹谏哟值摹?”時雙擊，即可提取剩余消費(fèi)時間的日期。2022/9/19HOUR函數(shù)可以返回時間值的小時數(shù)，即一個介于0到23之間的整數(shù)。

HOUR(serial_number)提取消費(fèi)時間中的日期、小時、星期HOUR函數(shù)2022/9/19參數(shù)參數(shù)解釋serial_number必需。表示要查找小時的時間值。時間有多種輸入方式：帶引號的文本字符串、十進(jìn)制數(shù)或其他公式或函數(shù)的結(jié)果插入“時長（小時）”列：右鍵單擊F列，在彈出的快捷菜單中選擇【插入】命令，在新的一列第一個單元格輸入“時長（小時）”。輸入公式：選擇單元格F2，輸入“=HOUR(D2)”。按下【Enter】鍵，即可用HOUR函數(shù)提取時長。之所以出現(xiàn)圖中的#號，因為F列的寬度不夠，需要手動調(diào)整列寬。提取消費(fèi)時間中的日期、小時、星期使用HOUR函數(shù)提取消費(fèi)時間的小時數(shù)：2022/9/19設(shè)置F列的單元格格式：單元格F2之所以出現(xiàn)圖中的格式，是因為插入的新列會沿用前一列的單元格格式，所以需要修改F列的單元格格式；在【開始】選項卡的【數(shù)字】命令組中，單擊

按鈕，在彈出的快捷菜單中選擇【常規(guī)】命令。提取消費(fèi)時間中的日期、小時、星期2022/9/19提取消費(fèi)時間中的日期、小時、星期填充公式：將鼠標(biāo)指針移動至F2單元格的右下角，當(dāng)指針變?yōu)楹谏哟值摹?”時雙擊，即可提取剩余消費(fèi)時間的小時數(shù)。2022/9/19WEEKDAY函數(shù)可以返回某日期的星期數(shù)，在默認(rèn)情況下，它的值為1（星期天）到7（星期六）之間的一個整數(shù)。

WEEKDAY(serial_number,return_type)提取消費(fèi)時間中的日期、小時、星期WEEKDAY函數(shù)2022/9/19參數(shù)參數(shù)解釋serial_number必需。表示要查找的日期，可以是指定的日期或引用含有日期的單元格。日期有多種輸入方式：帶引號的文本串、系列數(shù)或其他公式或函數(shù)的結(jié)果return_type可選。表示星期的開始日和計算方式。return_type代表星期的表示方式：當(dāng)Sunday（星期日）為1、Saturday（星期六）為7時，該參數(shù)為1或省略；當(dāng)Monday（星期一）為1、Sunday（星期日）為7時，該參數(shù)為2（這種情況符合中國人的習(xí)慣）；當(dāng)Monday（星期一）為0、Sunday（星期日）為6時，該參數(shù)為3插入“星期”列：右鍵單擊G列，在彈出的快捷菜單中選擇【插入】命令，在第一個單元格輸入“星期”。輸入公式：選擇單元格F2，輸入“=WEEKDAY(E2)”；按下【Enter】鍵，即可提取星期。填充公式。將鼠標(biāo)指針移動至G2單元格的右下角，當(dāng)指針變?yōu)楹谏哟值摹?”時雙擊，即可提取剩余消費(fèi)時間的星期。提取消費(fèi)時間中的日期、小時、星期使用WEEKDAY函數(shù)提取消費(fèi)時間的星期：打開【自定義自動篩選方式】對話框：單擊“時長（小時）”列旁的倒三角符號；在彈出的快捷菜單中依次選擇【數(shù)字篩選】命令→【自定義篩選】命令。刪除消費(fèi)記錄異常的數(shù)據(jù)通過【篩選】功能，篩選出0:00~5:00之內(nèi)的所有消費(fèi)記錄，并其進(jìn)行刪除：2022/9/19自定義篩選方式：在第一個條件設(shè)置中，單擊第一個

符號，在下拉列表中選擇【大于或等于】，在旁邊的文本框中輸入“0”；選擇【與】單選按鈕，并在第二個條件設(shè)置中，單擊第一個

符號，在下拉列表中選擇【小于】，在旁邊的文本框中輸入“5”。刪除消費(fèi)記錄異常的數(shù)據(jù)2022/9/19單擊【確定】按鈕，即可篩選出消費(fèi)時間異常的數(shù)據(jù)。刪除異常值：單擊單元格A3035，按【Ctrl+Shift+→】組合鍵選中第3035行數(shù)據(jù)，再按【Ctrl+Shift+↓】組合鍵選中第3035行之后的所有行的數(shù)據(jù)，即選中了篩選出的所有“時長（小時）”列中的異常數(shù)據(jù)；右鍵單擊選中區(qū)域，在彈出的快捷菜單中單擊【刪除行】命令。若彈出【MicrosoftExcel】提示對話框，則單擊【確定】按鈕即可。刪除消費(fèi)記錄異常的數(shù)據(jù)2022/9/19顯示篩選后結(jié)果：單擊“時長（小時）”列旁的倒三