第2章數(shù)據(jù)的清洗及美化教師：Office助手Datacleaningandbeautification2.1數(shù)據(jù)的清洗2.2數(shù)據(jù)美化2.1數(shù)據(jù)清洗Datacleaning實例20批量刪除多余的行從系統(tǒng)中導(dǎo)出的數(shù)據(jù)經(jīng)常出現(xiàn)多余的行，包括多余的標(biāo)題行和空白行。多余的行影響數(shù)據(jù)統(tǒng)計和分析，需要將其刪除，刪除方法如下：1.刪除多余的標(biāo)題行從系統(tǒng)導(dǎo)出的員工登記表如圖所示，其中有3個標(biāo)題行，要求只保留第一個標(biāo)題行，其余多余的2個標(biāo)題行刪除，操作步驟如下：實例20批量刪除多余的行步驟1：在任意標(biāo)題行上右鍵單擊，彈出的快捷菜單中選擇“篩選”|“按所選單元格值篩選”命令，如左圖所示，篩選出所有的標(biāo)題行，如右圖所示。實例20批量刪除多余的行步驟2：選中第2個和第3個標(biāo)題行，在選中的標(biāo)題行上右鍵單擊，彈出的快捷菜單中選擇“刪除行”命令，如左圖所示，即可將多余的標(biāo)題行刪除，效果如右圖所示，此時標(biāo)題行處于篩選狀態(tài)。實例20批量刪除多余的行步驟3：單擊B列右側(cè)的按鈕，打開的列表框中選中“全選”復(fù)選框，如左圖所示，單擊“確定”按鈕，顯示全部數(shù)據(jù)，如右圖所示，此時每個字段名的右側(cè)仍有按鈕。實例20批量刪除多余的行步驟4：打開“數(shù)據(jù)”選項卡，單擊“篩選”按鈕，取消字段名右側(cè)的按鈕，得到規(guī)范數(shù)據(jù)表，如圖所示。實例20批量刪除多余的行2.刪除多余的空白行如果系統(tǒng)導(dǎo)出的數(shù)據(jù)包含多余的空白行，如圖所示，將數(shù)據(jù)區(qū)域間隔為多個區(qū)域，不利于數(shù)據(jù)的分析?，F(xiàn)要求刪除多余的空白行，將數(shù)據(jù)存放在連續(xù)的區(qū)域中。操作步驟如下：實例20批量刪除多余的行步驟1：選中任何一列，例如C列，打開“數(shù)據(jù)”選項卡，單擊“篩選”按鈕，如左圖所示。此時，該列處于篩選狀態(tài)。步驟2：單擊C1單元格右側(cè)的按鈕，打開的下拉列表框中，只選中“空白”復(fù)選框，如右圖所示，單擊“確定”按鈕，即可篩選出所有的空白行。實例20批量刪除多余的行步驟3：選中所有空白行并右鍵單擊，彈出的快捷菜單中選擇“刪除行”命令，如圖所示，刪除所有空白行。實例20批量刪除多余的行步驟4：單擊C1單元格右側(cè)的按鈕，打開的下拉列表框中選中“全選”復(fù)選框，如左圖所示，單擊“確定”按鈕，將全部數(shù)據(jù)進行顯示，如右圖所示。實例20批量刪除多余的行步驟5：按快捷鍵Ctrl＋Shift＋L或者單擊“數(shù)據(jù)”選項卡中的“篩選”按鈕，取消C1右側(cè)的按鈕，得到規(guī)范的數(shù)據(jù)表，如圖所示。實例21批量刪除重復(fù)記錄如果數(shù)據(jù)源中有重復(fù)的記錄，如左圖所示，可利用Excel提供的刪除重復(fù)項功能，將重復(fù)的記錄刪除。操作步驟如下：步驟1：選中A1:A10單元格區(qū)域，打開“數(shù)據(jù)”選項卡，單擊“刪除重復(fù)項”按鈕，如右圖所示。實例21批量刪除重復(fù)記錄步驟2：彈出的對話框中，若要刪除標(biāo)題行中的重復(fù)值，則選中“數(shù)據(jù)包含標(biāo)題”復(fù)選框，反之，不選，單擊“確定”按鈕，如左圖所示。步驟3：彈出的提示框中顯示刪除的數(shù)據(jù)，單擊“確定”按鈕，完成刪除，效果如右圖所示。實例22批量轉(zhuǎn)換無法正常統(tǒng)計的數(shù)字?jǐn)?shù)據(jù)源中有些數(shù)字在使用SUM函數(shù)進行匯總運算時，結(jié)果為0，如圖2-21所示，原因是這些數(shù)字是文本格式，所以在使用SUM函數(shù)匯總運算時結(jié)果為0。若要對這些數(shù)字進行匯總，首先要將文本格式的數(shù)字轉(zhuǎn)換為數(shù)值，轉(zhuǎn)換方法有2種。實例22批量轉(zhuǎn)換無法正常統(tǒng)計的數(shù)字方法1：利用“轉(zhuǎn)換為數(shù)字”命令轉(zhuǎn)換步驟1：選中數(shù)字所在的單元格區(qū)域，在單元格區(qū)域的左側(cè)出現(xiàn)一個黃色的提示符號，單擊此符號，打開的下拉列表中選擇“轉(zhuǎn)換為數(shù)字”命令，如左圖所示。步驟2：將文本格式的數(shù)字轉(zhuǎn)換為數(shù)值后，匯總結(jié)果隨之發(fā)生變化，如右圖所示。實例22批量轉(zhuǎn)換無法正常統(tǒng)計的數(shù)字方法2：利用選擇性粘貼功能步驟1：在任意一個空白單元格上右鍵單擊，彈出的快捷菜單中選擇“復(fù)制”命令，然后選中數(shù)字區(qū)域并右鍵單擊，彈出快捷菜單中選擇“選擇性粘貼”命令，如圖所示。實例22批量轉(zhuǎn)換無法正常統(tǒng)計的數(shù)字步驟2：彈出的對話框中，依次選中“數(shù)值”和“加”單選按鈕，如左圖所示，單擊“確定”按鈕，將選中區(qū)域的文本格式數(shù)字轉(zhuǎn)換為數(shù)值，匯總結(jié)果隨之發(fā)生變化，如右圖所示。若將大量文本格式數(shù)字轉(zhuǎn)換為數(shù)值，優(yōu)先推薦使用方法2進行轉(zhuǎn)換。相比于方法1先行后列逐個轉(zhuǎn)換，方法2高效快捷瞬間完成轉(zhuǎn)換。實例23智能拆分合并單元格并填充數(shù)據(jù)在實際工作中，如果數(shù)據(jù)源中含有合并單元格，如圖所示，會導(dǎo)致排序、篩選、創(chuàng)建數(shù)據(jù)透視表等無法進行，給數(shù)據(jù)處理工作帶來一系列麻煩。解決此類問題的方法是先取消單元格的合并，填上相關(guān)數(shù)據(jù)后才能進行后續(xù)的數(shù)據(jù)處理。實例23智能拆分合并單元格并填充數(shù)據(jù)步驟1：選中合并單元格區(qū)域C2:C13，打開“開始”選項卡，單擊“合并后居中”按鈕，如左圖所示，取消單元格的合并。步驟2：按快捷鍵Ctrl＋G或F5鍵，彈出“定位”對話框，單擊“定位條件”按鈕，如右圖所示。實例23智能拆分合并單元格并填充數(shù)據(jù)步驟3：彈出的對話框中，選中“空值”復(fù)選框，如左圖所示，單擊“確定”按鈕，效果如右圖所示。實例23智能拆分合并單元格并填充數(shù)據(jù)步驟4：此時，當(dāng)前活動單元格為C3,在編輯欄中輸入公式“=C2”(即活動單元格上方的單元格)，然后按快捷鍵Ctrl＋Enter批量填充公式，填充效果如圖所示，完成拆分合并單元格并填充數(shù)據(jù)。實例23智能拆分合并單元格并填充數(shù)據(jù)步驟5：因為批量填充是通過公式完成的，為了避免后續(xù)的操作(如排序等)破壞填充結(jié)果，需要將公式結(jié)果轉(zhuǎn)為實際值。選中C2:C13單元格區(qū)域并右鍵單擊，彈出的快捷菜單中選擇“復(fù)制”命令，然后在選中的區(qū)域上再次右鍵單擊，彈出的快捷菜單中選擇粘貼選項中的“值”，如左圖所示。即可將選中區(qū)域中的公式清除，并將公式結(jié)果轉(zhuǎn)為實際值，效果如右圖所示。實例24批量轉(zhuǎn)換不規(guī)則的日期數(shù)據(jù)在Excel中日期的規(guī)范格式是2023/3/15或2023-3-15的形式，而在如圖所示中，日期格式不規(guī)范，需要將其轉(zhuǎn)換為規(guī)范格式，才能進行后續(xù)的數(shù)據(jù)處理，如使用函數(shù)進行計算或者創(chuàng)建數(shù)據(jù)透視表等。下面通過實例介紹將不規(guī)范日期批量轉(zhuǎn)換為規(guī)范日期的方法。實例24批量轉(zhuǎn)換不規(guī)則的日期數(shù)據(jù)步驟1：選中日期所在的列A列，打開“數(shù)據(jù)”選項卡，單擊“分列”按鈕，彈出的對話框中單擊“下一步”按鈕，如圖所示。實例24批量轉(zhuǎn)換不規(guī)則的日期數(shù)據(jù)步驟2：彈出的對話框中單擊“下一步”按鈕，進入文本分列向?qū)У?步，選中“日期”單選按鈕，如左圖所示，單擊“完成”按鈕，效果如右圖所示。實例24批量轉(zhuǎn)換不規(guī)則的日期數(shù)據(jù)步驟2：彈出的對話框中單擊“下一步”按鈕，進入文本分列向?qū)У?步，選中“日期”單選按鈕，如左圖所示，單擊“完成”按鈕，效果如右圖所示。在本例中，使用了分列功能將不規(guī)范日期批量轉(zhuǎn)換為規(guī)范日期，操作簡單快捷。對于大批量不規(guī)范日期的轉(zhuǎn)換，此功能更為便捷。實例25批量隱藏數(shù)據(jù)工作表中的某些信息如果不希望被他人看到，例如價格、工資、成績等，可將單元格中的這些信息隱藏起來，隱藏的信息同樣可以參與計算，不影響計算結(jié)果。如圖所示是成績統(tǒng)計表，現(xiàn)要求將“寫作”列的信息隱藏，隱藏方法如下：實例25批量隱藏數(shù)據(jù)步驟1：選中要隱藏信息的單元格區(qū)域D2:D12，在選中的單元格上右鍵單擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式...”命令，如圖所示，彈出“設(shè)置單元格格式”對話框。實例25批量隱藏數(shù)據(jù)步驟2：在“數(shù)字”選項卡的“分類”列表框中單擊“自定義”選項，“類型”由默認(rèn)字符“G/通用格式”改為“；；；”，如左圖所示，單擊“確定”按鈕，選中單元格的信息被隱藏，效果如右圖所示。實例26快速固定標(biāo)題行當(dāng)工作表中的記錄幾十條甚至是幾萬條時，窗口不能完全顯示，需要向下拖動滾動條才能瀏覽其他數(shù)據(jù)，如圖所示。在向下拖動滾動條瀏覽數(shù)據(jù)時，頂端的標(biāo)題行隨之滾動導(dǎo)致不可見，不利于對數(shù)據(jù)的理解。為了方便對數(shù)據(jù)理解，可讓標(biāo)題行自動置頂，使其在上下移動滾動條瀏覽數(shù)據(jù)時始終顯示在窗口的頂端。設(shè)置標(biāo)題行自動置頂?shù)姆椒ㄈ缦拢簩嵗?6快速固定標(biāo)題行步驟1：選中數(shù)據(jù)源中的任意一個單元格，按快捷鍵Ctrl＋T,彈出的對話框中選擇默認(rèn)設(shè)置，單擊“確定”按鈕，如左圖所所示。步驟2：此時，上下移動滾動條瀏覽數(shù)據(jù)時標(biāo)題行自動顯示在窗口的頂端始終可見，如右圖所示。實例26快速固定標(biāo)題行步驟3：若要取消標(biāo)題行自動置頂，打開“表格工具”中的“設(shè)計”選項卡，單擊“轉(zhuǎn)換為區(qū)域”按鈕，如圖所示，將其轉(zhuǎn)為普通的單元格區(qū)域即可。2.2數(shù)據(jù)美化Databeautification實例27利用快捷鍵隔行填充顏色隔行填充顏色不僅使表格美觀、易讀，而且醒目便于區(qū)分內(nèi)容，以免看串行。如在圖2-47所示的數(shù)據(jù)表格中設(shè)置隔行填充顏色，操作步驟如下：實例27利用快捷鍵隔行填充顏色步驟1：單擊數(shù)據(jù)區(qū)域中的任意一個單元格，然后按快捷鍵Ctrl＋T，彈出的對話框中，選擇默認(rèn)設(shè)置，單擊“確定”按鈕，如左圖所所示，即可瞬間自動隔行填充顏色，效果如右圖所示。實例27利用快捷鍵隔行填充顏色步驟2：若對填充的顏色或樣式不滿意，打開“表格工具”中的“設(shè)計”選項卡，在“表格樣式”組中可以選擇其他顏色或樣色，如圖所示，直到滿意為止。實例27利用快捷鍵隔行填充顏色步驟3：隔行填充顏色后，在每個字段名右側(cè)有一個下拉按鈕，這是因為將填充區(qū)域轉(zhuǎn)換為了表。若要取消字段名右側(cè)的下拉按鈕，打開“表格工具”中的“設(shè)計”選項卡，單擊“工具”組中的“轉(zhuǎn)換為區(qū)域”按鈕，如左圖所示，彈出的提示框中單擊“是”按鈕，將表轉(zhuǎn)換為普通的單元格區(qū)域，轉(zhuǎn)化后的效果如右圖所示。實例2快速輸入超長文本步驟1：利用自動更正功能將某一字母(例如b)設(shè)置為商品名稱“DellIns15CR-4528B15.6英寸筆記本電腦”，設(shè)置方法如圖所示。(注：輸入的字母或數(shù)字不能與工作表中的其他內(nèi)容重復(fù)，以免誤替換。)實例28自定義隔行填充顏色隔行填充顏色除了使用快捷鍵一鍵填充外，也可以自定義顏色進行填充，此填充方式使表格顏色更豐富更具有個性化。下面結(jié)合實例講解自定義隔行填充顏色方法，具體要求如下：①標(biāo)題行設(shè)置為深色背景②其余數(shù)據(jù)行用淺色隔行填充步驟1：選中標(biāo)題行所在的單元格區(qū)域A1:E1，打開“開始”選項卡，單擊“填充顏色”右側(cè)下拉按鈕，打開的下拉列表中單擊“其他顏色”，彈出的對話框中，拖動滑塊或輸入RGB值自定義標(biāo)題行填充顏色，如圖所示。實例28自定義隔行填充顏色步驟2：在第3行中選中A3:E3單元格區(qū)域，按照步驟1的設(shè)置方法，將其設(shè)置為如左圖所示的填充顏色，效果如右圖所示。實例28自定義隔行填充顏色步驟3：將第2行和第3行填充顏色復(fù)制給剩余數(shù)據(jù)行。選中A2:E3單元格區(qū)域，打開“開始”選項卡，單擊“格式刷”按鈕，如左圖所示。步驟4：鼠標(biāo)指針指向A4單元格，按住鼠標(biāo)左鍵拖動至E13單元格，如圖所示，批量復(fù)制填充格式，得到隔行填充效果，如右圖所示。實例29設(shè)置邊框默認(rèn)情況下，工作表無邊框，工作表中的網(wǎng)格線是為了方便輸入、編輯而預(yù)設(shè)的，打印時網(wǎng)格線并不顯示。為了使工作表美觀和易讀，可通過設(shè)置工作表的邊框改變其視覺效果，使數(shù)據(jù)的顯示更加清晰直觀。例如，在圖2-59中，將左圖表格設(shè)置為如右圖所示的邊框，操作方法如下：實例29設(shè)置邊框步驟3：選中A2:A13單元格區(qū)域，單擊“框線”下拉按鈕，打開的下拉列表中單擊“右框線”，如左圖所示，為選中區(qū)域添加右框線。若要更改邊框線的顏色，打開“框線”下拉列表，從“線條顏色”中選擇需要的顏色即可，如右圖所示。實例29設(shè)置邊框方法2：使用筆繪制框線步驟1：選中A2:E13單元格區(qū)域，打開“開始”選項卡，單擊“框線”下拉按鈕，打開的下拉列表中單擊“無框線”，刪除原有的框線。步驟2：單擊“框線”下拉按鈕，打開的下拉列表中從“線型”中選擇一種實線，如圖2-65所示。此時光標(biāo)變成筆形，在標(biāo)題行的下方按住鼠標(biāo)左鍵進行拖動即可繪制框線。按照相同的方法分別在最后一行的下方、首列的右側(cè)按住鼠標(biāo)左鍵拖動繪制框線。步驟3：框線繪制結(jié)束后，單擊“開始”選項卡中的“框線”按鈕，取消筆形即可。實例30繪制斜線表頭斜線表頭為了說明行、列、數(shù)據(jù)區(qū)域的含義。在Excel工作表中，斜線表頭分為2種，一是單斜線表頭，二是多斜線表頭，如圖所示。繪制斜線表頭有多種方法，本例主要介紹利用插入直線和文本框的方法繪制多斜線表頭，該方法同樣適用于單斜線表頭的繪制。實例30繪制斜線表頭例如，為左圖所示的表格繪制多斜線表頭，繪制后的效果如右圖所示，操作步驟如下：步驟1：設(shè)置表頭的行高和列寬，將第1行的行高設(shè)置為50，A列的寬度設(shè)置為23。實例30繪制斜線表頭步驟2：選中A1單元格，打開“插入”選項卡，單擊“形狀”按鈕，選擇“直線”，如左圖所示。步驟3：在A1單元格中按住鼠標(biāo)左鍵進行拖動，