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

false整數(shù)0

1

-1

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

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

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

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