Excel電商數(shù)據(jù)分析與應(yīng)用 課件 4.數(shù)據(jù)的清洗方法

數(shù)據(jù)的清洗方法4.1數(shù)據(jù)規(guī)整&4.2數(shù)據(jù)合并與分組&4.3數(shù)據(jù)變形1數(shù)據(jù)類型2CONTENTS缺失值與異常值處理3縱向合并4橫向合并5數(shù)據(jù)分組6數(shù)據(jù)透視7數(shù)據(jù)逆透視數(shù)據(jù)類型PARTONE數(shù)據(jù)類型

數(shù)據(jù)類型是數(shù)據(jù)一致性檢查的基本項，在嚴謹?shù)臄?shù)據(jù)清洗過程中每個列（字段）都需要指定數(shù)據(jù)類型，否則在后續(xù)的分析過程中有可能因為數(shù)據(jù)類型不符合算法要求導(dǎo)致出錯。在Excel的許多場景應(yīng)用中無須指定數(shù)據(jù)的類型，但在PowerQuery中必須指定數(shù)據(jù)類型。在PowerQuery編輯器【轉(zhuǎn)換】選項卡中修改數(shù)據(jù)類型，也可以全選數(shù)據(jù)，單擊【檢測數(shù)據(jù)類型】選項即可。數(shù)據(jù)類型數(shù)據(jù)類型示例邏輯true

false整數(shù)0

1

-1

小數(shù)0.31.3-1.3時間#time(09,15,00)文本"hello"二進制#binary("AQID")常見的數(shù)據(jù)類型如表：缺失值與異常值處理PARTTWO缺失值與異常值處理缺失值和異常值的處理有兩種方法，第一種是刪除法，刪除缺失或異常數(shù)據(jù)的記錄，使用此方法的前提條件是刪除記錄不會對數(shù)據(jù)分析的結(jié)果產(chǎn)生影響。第二種是插補法，當刪除整條記錄對分析結(jié)果有嚴重影響時，根據(jù)實際情況可用0插補也可以用均值插補。

在PowerQuery編輯器【開始】選項卡中使用【替換值】功能替換缺失值或異常值。如圖：縱向合并PARTTHREE縱向合并縱向合并在數(shù)據(jù)庫中也稱為追加查詢，常用于多張相同結(jié)構(gòu)字段的表格合并成一張表格的場景。

例：現(xiàn)有100家門店的銷售數(shù)據(jù)，分別在100個文件中，將100家門店的銷售數(shù)據(jù)合并成一張表。縱向合并①在Excel【數(shù)據(jù)】選項卡中選擇【獲取數(shù)據(jù)】，在【自文件】中選擇【從文件夾】選項，將數(shù)據(jù)導(dǎo)入PowerQuery編輯器?？v向合并②在彈出的“文件夾”對話框中，選擇文件夾路徑?？v向合并③Excel2019版本有組合功能可以一鍵組合成一個文件，Excel2010/2016版本可參考后續(xù)操作。單擊【組合】后會再彈出下拉菜單，選擇【合并和編輯】選項?？v向合并④針對部分版本沒有組合功能的操作，需要進入編輯器中編輯，每一個Binary都是一個二進制文件，數(shù)據(jù)以二進制文件形式被PowerQuery編輯器讀取。縱向合并⑤在【添加列】選項卡中，單擊【自定義列】選項。輸入“Csv.Document([Content],[Encoding=936])”將二進制文件轉(zhuǎn)換成表格。Csv.Document是將二進制文件轉(zhuǎn)換成Csv格式的表格。[Encoding=936]是指定中文編碼為GBK。縱向合并⑥展開Table，取消勾選【使用原始列名作為前綴】。縱向合并⑦發(fā)現(xiàn)展開后字段名稱在記錄中，需要在【開始】選項卡單擊【將第一行用作標題】選項，第一行的記錄會轉(zhuǎn)變成字段名（標題）?？v向合并⑧由于每一個文件都有字段名稱，因此需要將多余的名稱過濾掉，如圖4-14所示，對【日期】進行篩選，將“日期”過濾掉?？v向合并⑨在【開始】選項卡中單擊【關(guān)閉并上載】選項，將數(shù)據(jù)導(dǎo)入Excel，可以觀察合并后的數(shù)據(jù)結(jié)果。橫向合并PARTFOUR橫向合并橫向合并在數(shù)據(jù)庫中稱為合并查詢，合并查詢分為左外部、右外部、完全外部、內(nèi)部、左反和右反6種連接方式。

下圖為左外部連接示意圖和右外部連接示意圖左外部連接示意圖右外部連接示意圖橫向合并例：現(xiàn)有兩張表，分別是學(xué)生的基本信息表和學(xué)生的成績表，若分析班級不同性別的學(xué)生成績的差異，需要將兩張表進行合并。學(xué)生基本信息表姓名性別年齡張三男19李四男18陳紅女19孫玉女19學(xué)生成績表姓名學(xué)科成績張三語文82張三數(shù)學(xué)80張三英語79李四語文99李四數(shù)學(xué)77李四英語98陳紅語文98陳紅數(shù)學(xué)96陳紅英語83孫玉語文95孫玉數(shù)學(xué)94孫玉英語84橫向合并①：選中表格，在【數(shù)據(jù)】選項卡中單擊【自表格/區(qū)域】（某些版本是【從表格】的字樣）選項，分別將兩張表格導(dǎo)入PowerQuery編輯器。橫向合并②在PowerQuery編輯器的【開始】選項卡中，單擊【合并查詢】選項，在【合并】對話框中，表1和表2分別選中【姓名】字段，連接種類選擇【左外部】。橫向合并③合并后展開表，由于信息表中已經(jīng)有姓名字段，因此取消勾選【姓名】，并且取消勾選【使用原始列名作為前綴】。橫向合并下圖為完全外部連接返回左表和右表的所有數(shù)據(jù)、內(nèi)部連接僅返回左表和右表匹配的所有數(shù)據(jù)、左反連接返回僅左表有的數(shù)據(jù)、右反連接返回僅右表有的數(shù)據(jù)。完全外部連接示意圖內(nèi)部連接示意圖左反連接示意圖右反連接示意圖橫向合并例：某企業(yè)針對老消費者開展了贈品活動，現(xiàn)有已領(lǐng)取禮品的消費者名單如表4-3所示，需要排查未領(lǐng)禮品的人員名單，下表是所有消費者的名單。已領(lǐng)禮品消費者名單張三李四王五趙六所有消費者名單張三李四王五趙六錢七孫八橫向合并前面步驟與上個案例相同，不同的是在“合并”窗口，連接方式選擇【左反】。合并后，就可以找到?jīng)]有領(lǐng)取的名單，表中的空表可以直接刪除。最后在【開始】選項卡中單擊【關(guān)閉并上載】選項，將數(shù)據(jù)導(dǎo)入Excel，即可得到消費者名單。數(shù)據(jù)分組PARTFIVE數(shù)據(jù)分組數(shù)據(jù)分組是根據(jù)某個維度將數(shù)據(jù)以某種算術(shù)方法（求和、計數(shù)等）進行統(tǒng)計匯總。例4-4：現(xiàn)有某班級同學(xué)各個學(xué)科的成績（如下表），老師想統(tǒng)計班級各個學(xué)科的平均分。學(xué)生成績表姓名學(xué)科成績張三語文82張三數(shù)學(xué)80張三英語79李四語文99李四數(shù)學(xué)77李四英語98陳紅語文98陳紅數(shù)學(xué)96陳紅英語83孫玉語文95孫玉數(shù)學(xué)94孫玉英語84數(shù)據(jù)分組①先選中學(xué)習(xí)成績表，在【數(shù)據(jù)】選項卡中單擊【自表格/區(qū)域】（某些版本是【從表格】的字樣）選項，將表格導(dǎo)入PowerQuery編輯器。數(shù)據(jù)分組②選中【學(xué)科】列，在【開始】選項卡中單擊【分組依據(jù)】選項，對數(shù)據(jù)進行分組統(tǒng)計。在“分組依據(jù)”對話框中進行設(shè)置，【操作】選擇【平均值】，【柱】選擇【成績】。數(shù)據(jù)透視PARTSIX數(shù)據(jù)透視數(shù)據(jù)變形是將數(shù)據(jù)的結(jié)構(gòu)進行變換，特別是一維表轉(zhuǎn)為二維表，或者二維表轉(zhuǎn)為一維表，變換結(jié)構(gòu)便于后續(xù)的數(shù)據(jù)分析工作開展。數(shù)據(jù)透視是將某維度的行轉(zhuǎn)變成列，可實現(xiàn)數(shù)據(jù)的快速匯總和分類。例:將下列的學(xué)生成績表轉(zhuǎn)變成姓名和學(xué)科的二維表學(xué)生成績表姓名學(xué)科成績張三語文82張三數(shù)學(xué)80張三英語79李四語文99李四數(shù)學(xué)77李四英語98陳紅語文98陳紅數(shù)學(xué)96陳紅英語83孫玉語文95孫玉數(shù)學(xué)94孫玉英語84數(shù)據(jù)透視①選中表格，在【數(shù)據(jù)】選項卡中單擊【自表格/區(qū)域】選項，將兩張表格導(dǎo)入PowerQuery編輯器。數(shù)據(jù)透視②在PowerQuery編輯器中，選中【成績】列，在【轉(zhuǎn)換】選項卡中單擊【透視列】選項。數(shù)據(jù)透視③在【透視列】對話框中，設(shè)置【值列】為【成績】，單擊【確定】按