滕文惠 閆媛媛 姚曉芳

摘 ?要：高質(zhì)量的數(shù)據(jù)是數(shù)據(jù)分析和應(yīng)用的重要保證，“臟數(shù)據(jù)”會(huì)導(dǎo)致不可靠輸出，這種輸出導(dǎo)致的結(jié)果有可能難以彌補(bǔ)，因此數(shù)據(jù)清洗尤為重要。該文重點(diǎn)闡述了數(shù)據(jù)清洗思路及利用Excel軟件進(jìn)行數(shù)據(jù)清洗的具體方法，為教育和科研工作者提供一些參考。

關(guān)鍵詞：Excel ?數(shù)據(jù)清洗 ?方法

中圖分類號(hào)：G250.2 ? 文獻(xiàn)標(biāo)識(shí)碼：A ? ? ? ? ? 文章編號(hào)：1672-3791（2019）05（c）-0028-02

隨著計(jì)算機(jī)及通信技術(shù)的迅猛發(fā)展，如今已經(jīng)進(jìn)入了全新的數(shù)字化及智能化時(shí)代。各種信息系統(tǒng)的廣泛應(yīng)用積累了大量的原始數(shù)據(jù)，分析這些數(shù)據(jù)的內(nèi)在規(guī)律，預(yù)測相關(guān)業(yè)務(wù)量的發(fā)展趨勢，已經(jīng)成為各行各業(yè)的迫切需求，因此掌握數(shù)據(jù)分析技術(shù)顯得尤為重要。而數(shù)據(jù)清洗又是整個(gè)數(shù)據(jù)分析過程中不可缺少的一個(gè)環(huán)節(jié)，其結(jié)果質(zhì)量直接關(guān)系到模型效果和最終結(jié)論。在實(shí)際操作中，數(shù)據(jù)清洗通常會(huì)占據(jù)分析過程的50%～80%的時(shí)間。

1 ?數(shù)據(jù)清洗的定義

數(shù)據(jù)清洗，英文名為Data Cleaning或Data Scrubbing，是檢測和去除數(shù)據(jù)集中的噪聲數(shù)據(jù)和無關(guān)數(shù)據(jù)，處理遺漏數(shù)據(jù)，去除空白數(shù)據(jù)域和知識(shí)背景下的白噪聲。也就是將重復(fù)、多余的數(shù)據(jù)篩選清除，將缺失的數(shù)據(jù)補(bǔ)充完整，將錯(cuò)誤的數(shù)據(jù)糾正或者刪除，最后整理成為標(biāo)準(zhǔn)的、干凈的、連續(xù)的數(shù)據(jù)，提供給數(shù)據(jù)分析、數(shù)據(jù)挖掘等使用。

2 ?常用的數(shù)據(jù)清洗方式

實(shí)際工作中常用的數(shù)據(jù)清洗方式有人工清洗、計(jì)算機(jī)清洗及人工和計(jì)算機(jī)結(jié)合清洗。對(duì)于非標(biāo)準(zhǔn)數(shù)據(jù)或數(shù)據(jù)量較小的數(shù)據(jù)源，可使用人工清理的方式，即借助統(tǒng)計(jì)調(diào)查實(shí)際工作經(jīng)驗(yàn)，通過人工檢查的原始方法來發(fā)現(xiàn)異常值，但如果數(shù)據(jù)量較大、變量較多的情況，使用這種方法費(fèi)時(shí)費(fèi)力，效率相對(duì)較低。計(jì)算機(jī)清洗則是通過SPSS、SAS、Excel和Python等軟件，設(shè)置相應(yīng)的函數(shù)及校驗(yàn)規(guī)則，對(duì)缺失值、取值范圍，以及數(shù)據(jù)格式、數(shù)據(jù)類型、拼寫錯(cuò)誤等問題進(jìn)行識(shí)別和處理，大數(shù)據(jù)時(shí)代的數(shù)據(jù)清理更適合采用該種方式。然而計(jì)算機(jī)程序難免出現(xiàn)編程錯(cuò)誤或bug，因此還可以采用人工和計(jì)算機(jī)相結(jié)合清洗的方式，首先通過計(jì)算機(jī)檢測出可疑數(shù)據(jù)，統(tǒng)計(jì)調(diào)查人員再根據(jù)其業(yè)務(wù)知識(shí)和經(jīng)驗(yàn)進(jìn)行人工判斷。數(shù)據(jù)清洗后還需進(jìn)行質(zhì)量審核及評(píng)估，在審核評(píng)估中不斷發(fā)現(xiàn)問題、解決問題，確保調(diào)查數(shù)據(jù)的完整性和唯一合法性。該文則主要探討基于Excel的計(jì)算機(jī)清理方法。

3 ?數(shù)據(jù)清洗的方案

數(shù)據(jù)清洗是發(fā)現(xiàn)并糾正數(shù)據(jù)文件中可識(shí)別的錯(cuò)誤的最后一道程序，包括檢查數(shù)據(jù)完整性、唯一性、合法性等。數(shù)據(jù)的完整性，重點(diǎn)檢查數(shù)據(jù)是否有缺失值，例如人的屬性中缺少性別、籍貫、年齡等。數(shù)據(jù)的唯一性，重點(diǎn)檢查否有重復(fù)樣本，例如不同來源的數(shù)據(jù)出現(xiàn)重復(fù)的情況。數(shù)據(jù)的合法性，則檢查數(shù)據(jù)是否有異常值，例如獲取的數(shù)據(jù)與常識(shí)不符，年齡大于150歲。

3.1 缺失值的處理思路

缺失值指的是數(shù)據(jù)原本是必須存在的，但實(shí)際上該變量沒有數(shù)據(jù)，它與空值不同。主要的處理方法有其他信息補(bǔ)全法，例如使用省份證號(hào)推算性別、籍貫、出生日期、年齡等。這種方法比較復(fù)雜，一般用在補(bǔ)全一些不可缺失的信息時(shí)使用。前后數(shù)據(jù)補(bǔ)全法，例如時(shí)間序列數(shù)據(jù)部分缺失時(shí)，可以使用前后數(shù)據(jù)的均值或整個(gè)時(shí)間序列的均值進(jìn)行補(bǔ)全。當(dāng)缺失數(shù)據(jù)較多時(shí)，可以使用平滑處理進(jìn)行補(bǔ)全。剔除法，該辦法在實(shí)在無法補(bǔ)全的情況下使用，剔除不等于刪除，可以暫時(shí)不用這些數(shù)據(jù)，在以后的分析中可能還會(huì)使用。

3.2 重復(fù)值的處理思路

數(shù)據(jù)中屬性值相同的記錄被認(rèn)為是重復(fù)記錄，通過判斷記錄間的屬性值是否相等來檢測記錄是否相等，相等的記錄合并為一條記錄，即合并或者清除。主要方法有按主鍵去重及按規(guī)則去重。

3.3 異常值的處理思路

異常值是指樣本中的個(gè)別值，其數(shù)值明顯偏離所屬樣本的其余觀測值。用統(tǒng)計(jì)分析的方法識(shí)別可能的錯(cuò)誤值或異常值，如偏差分析、識(shí)別不遵守分布或回歸方程的值，也可以用簡單規(guī)則庫（常識(shí)性規(guī)則、業(yè)務(wù)特定規(guī)則等）檢查數(shù)據(jù)值，或使用不同屬性間的約束、外部的數(shù)據(jù)來檢測和清理數(shù)據(jù)。主要方法有設(shè)定強(qiáng)制合法規(guī)則、設(shè)定字段類型合法規(guī)則及設(shè)定字段內(nèi)容合法規(guī)則。

4 ?Excel數(shù)據(jù)清理的方法

現(xiàn)將數(shù)據(jù)清理過程中出現(xiàn)的常見問題，如重復(fù)項(xiàng)的處理、缺失值的處理、字段分列、去除非打印字符及空格、數(shù)據(jù)的離散化、數(shù)據(jù)的有效性等，分別探討在Excel中處理方法。

4.1 重復(fù)項(xiàng)的處理

方法是直接刪除重復(fù)數(shù)據(jù)，保留一條記錄即可。在Excel中選擇數(shù)據(jù)標(biāo)簽，單擊“刪除重復(fù)項(xiàng)”按鈕。打開“刪除重復(fù)項(xiàng)”對(duì)話框，默認(rèn)情況下所有字段都相等認(rèn)為是重復(fù)項(xiàng)，單擊“確定”刪除即可。還有一種情況是部分字段相同就認(rèn)為是重復(fù)項(xiàng)，這種情況可以現(xiàn)將重復(fù)項(xiàng)挑選出來，再根據(jù)統(tǒng)計(jì)調(diào)查人員的業(yè)務(wù)經(jīng)驗(yàn)有選擇性地刪除。

4.2 缺失值處理

直接剔除法，如果該字段分析價(jià)值不大且缺失比例較大，可以采取直接剔除法。前后數(shù)據(jù)補(bǔ)全法，即用前后數(shù)據(jù)的平均值來補(bǔ)全，主要應(yīng)用于時(shí)間序列數(shù)據(jù)。選定缺失值字段，單擊開始標(biāo)簽里的查找選擇下拉菜單的定位條件命令。打開“定位條件”對(duì)話框，選擇“空值”選項(xiàng)，定位出所有空缺單元格，輸入平均值公式，單擊Ctrl+Shift+Enter鍵確認(rèn)。用其他表格信息補(bǔ)全，可以利用表格相同字段，通過“Vlookup”函數(shù)完成精確查找并補(bǔ)全。

4.3 數(shù)據(jù)分列

主要用于將一個(gè)字段分裂為兩個(gè)字段，首先在需要分列字段后插入一個(gè)空列，然后選擇數(shù)據(jù)標(biāo)簽的“分列”按鈕，打開“文本分列向?qū)А睂?duì)話框，進(jìn)入文本分列向?qū)?，選擇該列數(shù)據(jù)的分隔符號(hào)進(jìn)行分列操作。

4.4 去除非打印字符及空格

通過網(wǎng)絡(luò)獲取的數(shù)據(jù)有時(shí)會(huì)回車符，空格等影響數(shù)據(jù)分析的字符，可以分別利用Clean（），Trim（）函數(shù)去除。

4.5 數(shù)據(jù)的離散化

對(duì)于字符數(shù)據(jù)，可以通過數(shù)據(jù)透視表觀察數(shù)據(jù)類別的基本情況，然后通過篩選進(jìn)行類別的重新組合，也可以將字段重新設(shè)置成為是否標(biāo)志。對(duì)于數(shù)值數(shù)據(jù)，先設(shè)置離散的區(qū)間及每個(gè)區(qū)間對(duì)應(yīng)的字段值，然后利用Vlookup的近似查找完成。

4.6 數(shù)據(jù)的有效性

數(shù)據(jù)的有效性是對(duì)單元格或單元格區(qū)域輸入的數(shù)據(jù)從內(nèi)容到數(shù)量上的限制。在Excel中選擇數(shù)據(jù)標(biāo)簽里的“數(shù)據(jù)驗(yàn)證”按鈕，打開“數(shù)據(jù)驗(yàn)證”對(duì)話框，可以依靠系統(tǒng)檢查數(shù)據(jù)的正確有效性，然后通過“圈釋無效數(shù)據(jù)”功能可以對(duì)已錄入的數(shù)據(jù)中不符合條件的數(shù)據(jù)做圈釋標(biāo)示。

5 ?結(jié)語

數(shù)據(jù)分析技術(shù)廣泛應(yīng)用于醫(yī)療、能源、零售、汽車、金融等諸多領(lǐng)域，通過分析有價(jià)值的數(shù)據(jù)，提供決策和建議。但臟數(shù)據(jù)的普遍存在，導(dǎo)致數(shù)據(jù)分析過程中可利用的數(shù)據(jù)有限，因此，數(shù)據(jù)清洗顯得尤為重要，對(duì)數(shù)據(jù)清洗方法的研究也將更加深入。

參考文獻(xiàn)

[1] 羅強(qiáng)，何利力，王曉菲.數(shù)據(jù)倉庫中數(shù)據(jù)清洗技術(shù)分析[J].電腦編程技巧與維護(hù)，2015（2）：61，76.

[2] 羅艷霞，王庭熙，駱紹曄.數(shù)據(jù)預(yù)處理在圖書借閱中的應(yīng)用[J].莆田學(xué)院學(xué)報(bào)，2016（2）：50-53.