項(xiàng)目4 處理異常值

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

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

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

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

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

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

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

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