商務(wù)數(shù)據(jù)分析基礎(chǔ) 課件 第四章 數(shù)據(jù)處理

第四章數(shù)據(jù)處理目錄1、數(shù)據(jù)清洗2、數(shù)據(jù)加工3、數(shù)據(jù)修整1數(shù)據(jù)清洗1.1數(shù)據(jù)一致性處理1.2缺失數(shù)據(jù)的處理1.3刪除重復(fù)數(shù)據(jù)1.1數(shù)據(jù)一致性處理通過(guò)統(tǒng)計(jì)調(diào)查收集上來(lái)的數(shù)據(jù),經(jīng)常會(huì)出現(xiàn)同一字段的數(shù)據(jù)格式不一致的問(wèn)題,如圖4-1所示。這會(huì)對(duì)后續(xù)的數(shù)據(jù)分析造成影響,所以必須對(duì)數(shù)據(jù)格式進(jìn)行處理。圖4-1源數(shù)據(jù)下面就以圖4-1為例,將“身高”字段中的數(shù)據(jù)去掉字符“cm”。(1)選中C列,如圖4-2所示。(2)選擇“查找和選擇”→“替換”命令,如圖43所示。圖

4-2

選中

C

列4-3

選擇

“

替換”

命令(3) 在“查找和替換”對(duì)話框中選擇“替換”選項(xiàng)卡,在“查找內(nèi)容”文本框中輸入“cm”,設(shè)置“替換為”為空。單擊“全部替換”按鈕完成替換,如圖4-4所示。替換后的結(jié)果如圖4-5所示。圖

4-4

輸入查找內(nèi)容圖

4-5

替換后的結(jié)果1.2缺失數(shù)據(jù)的處理樣本清單中,如果單元格為空,則認(rèn)為存在缺失數(shù)據(jù)。缺失數(shù)據(jù)通常用以下兩種方法替換:(1)用樣本均值或眾數(shù)代替缺失值。(2)將缺失值數(shù)據(jù)刪除。首先要發(fā)現(xiàn)缺失值。一般用定位條件來(lái)查找缺失數(shù)據(jù)的單元格。下面演示將“年齡”字段中的空值替換為“20”。1)選中年齡所在的E列,選擇“查找和選擇”→“定位條件”命令,如圖4-6所示。圖4-6選擇“定位條件”命令

2)在“定位條件”對(duì)話框中,選中空值單選按鈕,如圖4-7所示。3)單擊“確定”按鈕,E列所有的空白單元格呈選中狀態(tài),如圖4-8所示。

圖

4-7

選中

“

空值”

單選按鈕圖

4-8

選中所有空值

(4) 輸入替代值“20”,按<Ctrl+Enter>組合鍵確認(rèn),結(jié)果如圖4-9所示。圖4-9結(jié)果呈現(xiàn)1.3刪除重復(fù)記錄在遇到Excel表格里有很多重復(fù)項(xiàng)的時(shí)候,如何刪除相同內(nèi)容,只保留一個(gè)呢?刪除重復(fù)項(xiàng)的操作極其簡(jiǎn)單,只需單擊數(shù)據(jù)表的任意位置,再單擊“數(shù)據(jù)”選項(xiàng)卡中的“刪除重復(fù)值”按鈕即可,如圖4-10所示。圖4-10刪除重復(fù)值2數(shù)據(jù)加工2.1

數(shù)據(jù)轉(zhuǎn)置2.2

字段分列2.3

字段匹配2.4

數(shù)據(jù)抽取2.4

數(shù)據(jù)計(jì)算2.1數(shù)據(jù)轉(zhuǎn)換在Excel應(yīng)用過(guò)程中,有時(shí)需要把行數(shù)據(jù)轉(zhuǎn)置成列數(shù)據(jù),或者把列數(shù)據(jù)轉(zhuǎn)置成行數(shù)據(jù),因?yàn)橐粭l一條粘貼過(guò)于煩瑣,這里就需要用到Excel中的數(shù)據(jù)轉(zhuǎn)置功能。操作方法是:先復(fù)制好橫行數(shù)據(jù),然后單擊“開(kāi)始”選項(xiàng)卡→“剪貼板”組中的“粘貼”下拉按鈕,在下拉列表中單擊“轉(zhuǎn)置”按鈕即可,如圖4-11所示。圖4-11數(shù)據(jù)轉(zhuǎn)換2.2

字段分列字段分列就是將一個(gè)字段分為多個(gè)字段。操作如下:(1)將A列中的日期分成年、月、日。首先將這組日期選中,如圖4-12所示。(2)選擇“數(shù)據(jù)”選項(xiàng)卡,單擊“分列”按鈕,如圖4-13所示。圖4-12源數(shù)據(jù)圖4-13單擊“分列”按鈕(3) 在文本分列向?qū)Ы缑?選擇“分隔符號(hào)”單選按鈕,單擊“下一步”按鈕,如圖4-14所示。圖4-14文本分列向?qū)Р襟E1(4) 在“分隔符號(hào)”區(qū)中勾選“其他”復(fù)選框,在其右側(cè)框內(nèi)輸入日期之間的小橫杠“”,單擊“下一步”按鈕,如圖4-15所示。圖4-15文本分列向?qū)Р襟E2(5) 進(jìn)入圖416所示界面,選擇分列的格式,這里默認(rèn)即可,單擊“完成”按鈕,如圖4-16所示。(6) 分列的結(jié)果如圖4-17所示。我們看到分成了三列,分別是年、月、日。圖

4-16

文本分列向?qū)Р襟E

3圖

4-17

結(jié)果呈現(xiàn)2.3

字段匹配字段匹配就是運(yùn)用VLOOKUP函數(shù)將原數(shù)據(jù)清單中沒(méi)有但其他數(shù)據(jù)清單中有的字段匹配過(guò)來(lái)。(1)以圖4-18中的數(shù)據(jù)為例,我們需要在A:B列中找到與C列對(duì)應(yīng)的B列的數(shù)據(jù);打開(kāi)Excel后,單擊D1單元格,如圖4-18所示。圖418源數(shù)據(jù)

圖4-18源數(shù)據(jù)(2) 單擊“公式”選項(xiàng)卡→“函數(shù)庫(kù)”組→“插入函數(shù)”按鈕,彈出“插入函數(shù)”對(duì)話框,在“搜索函數(shù)”框中輸入vlookup,如圖4-19所示。圖4-19“插入函數(shù)”對(duì)話框(3) 搜索到VLOOKUP函數(shù)之后會(huì)出來(lái)四個(gè)文本框需要我們填列。下面一一介紹這四個(gè)文本框中數(shù)據(jù)分別代表的含義:第一個(gè)文本框中數(shù)據(jù)表示的是我們?cè)谑裁磾?shù)據(jù)范圍內(nèi)取的我們需要的數(shù)據(jù),此表格中需要在A:B兩列中取得C列所對(duì)應(yīng)的B列的數(shù)據(jù),所以范圍就是A:B列。第二個(gè)文本框中數(shù)據(jù)表示的是需要取在什么條件下的數(shù)據(jù),在此表格中我們需要找出C列的所對(duì)應(yīng)的B列數(shù)據(jù),所以選中C列。第三個(gè)文本框中數(shù)據(jù)表示的是我們需要取的是數(shù)據(jù)范圍的第幾列數(shù),因?yàn)槲覀冃枰臄?shù)據(jù)是A:B兩列中的B列數(shù)據(jù),也就是第二列數(shù)據(jù),所以填2;依此類推,如果是數(shù)據(jù)范圍內(nèi)的第三列數(shù),則填3。第四個(gè)文本框中數(shù)據(jù)表示的是如果是錯(cuò)誤的結(jié)果則顯示為0,如圖4-20所示。圖4-20設(shè)置函數(shù)參數(shù)(4) 將這四個(gè)文本框都填完之后,按<Enter>鍵就會(huì)顯示出C列在A:B列中所對(duì)應(yīng)的B列的數(shù)據(jù),如圖4-21所示。需要注意的是C列的格式要與A列格式一樣,否則,數(shù)據(jù)取不出來(lái)。圖4-21結(jié)果呈現(xiàn)2.4數(shù)據(jù)抽取數(shù)據(jù)抽取是指利用原數(shù)據(jù)清單中某些字段的部分信息得到一個(gè)新字段。常用的數(shù)據(jù)抽取函數(shù)有:LEFT()、RIGHT()、YEAR()、MONTH()、WEEKDAY()、DAY()等。WEEKDAY()抽取函數(shù)的語(yǔ)法格式為WEEKDAY(date,type),其中,date為日期;type表示返值是從1到7還是從0到6,以及從星期幾開(kāi)始計(jì)數(shù),如省略則返值為1到7,且從星期日起計(jì)。WEEKDAY()抽取函數(shù)的應(yīng)用如圖4-22所示。圖422WEEKDAY()抽取函數(shù)應(yīng)用2.5數(shù)據(jù)計(jì)算有時(shí)候,我們需要的數(shù)據(jù)并不存在于數(shù)據(jù)表中,而是通過(guò)對(duì)其他字段進(jìn)行數(shù)學(xué)計(jì)算或函數(shù)計(jì)算來(lái)獲取。(1)好評(píng)率可以通過(guò)公式“好評(píng)率=(好評(píng)單數(shù)/成交單數(shù))×100%”來(lái)計(jì)算,如圖4-23所示。(2)銷售天數(shù)可以通過(guò)公式“銷售天數(shù)=下架日期-上架日期”來(lái)計(jì)算,如圖4-24所示。圖

4-23好評(píng)率計(jì)算圖

4-24

銷售天數(shù)計(jì)算3數(shù)據(jù)修整3.1三項(xiàng)移動(dòng)平均法3.2四項(xiàng)移動(dòng)平均法3.3分析工具加載3.1三項(xiàng)移動(dòng)平均法案例:計(jì)算如圖4-25所示的表格中商品銷售額的三項(xiàng)移動(dòng)平均數(shù)。分析:在本書(shū)配套教學(xué)素材中打開(kāi)文件“商品銷售額.xlsx”,選擇單元格區(qū)域A1∶B13,單擊“插入”選項(xiàng)卡→在“圖表”組中單擊“散點(diǎn)圖”下拉按鈕繪制散點(diǎn)圖,如圖4-26所示。圖

4-25商品銷售額圖

4-26

繪制散點(diǎn)圖選項(xiàng)結(jié)果得到如圖4-27所示的散點(diǎn)圖,從圖4-27中可以直觀地看出,第三個(gè)點(diǎn)對(duì)應(yīng)的數(shù)據(jù)明顯偏小,而第六個(gè)點(diǎn)對(duì)應(yīng)的數(shù)據(jù)明顯偏大,這可能是由不確定因素造成的。在這種情況下,可以通過(guò)移動(dòng)平均法對(duì)數(shù)據(jù)進(jìn)行修整,盡量排除不確定因素對(duì)數(shù)據(jù)造成的影響。圖427原始數(shù)據(jù)的散點(diǎn)圖三項(xiàng)移動(dòng)平均法的計(jì)算思路如下:第一個(gè)三項(xiàng)移動(dòng)平均數(shù)=(4205+4632+4000)/3=4279作為2月的數(shù)據(jù);第二個(gè)三項(xiàng)移動(dòng)平均數(shù)=(4632+4000+4800)/3=4477.33作為3月的數(shù)據(jù);依此類推。下面用函數(shù)AVERAGE計(jì)算三項(xiàng)移動(dòng)平均數(shù)。(1)選中C3單元格,單擊“公式”選項(xiàng)卡,選擇“自動(dòng)求和”→“平均值”命令,如圖4-28所示。圖4-28選擇“平均值”命令(2)更改公式為“=AVERAGE(B2∶B4)”,如圖4-29所示。圖4-29更改計(jì)算公式(3)確認(rèn)C3單元格的計(jì)算后,拖動(dòng)C3單元格的填充柄至C12單元格,如圖4-30所示。(4)修整后的散點(diǎn)圖如圖4-31所示圖

4-30填充單元格圖

4-31

結(jié)果呈現(xiàn)四項(xiàng)移動(dòng)平均因無(wú)法居中對(duì)齊中間項(xiàng),因此需要進(jìn)行一次正位。案例:計(jì)算如圖4-32所示的表格中商品銷售額的四項(xiàng)移動(dòng)平均數(shù)。3.2四項(xiàng)移動(dòng)平均圖432源數(shù)據(jù)(2)確認(rèn)C3單元格的計(jì)算后,拖動(dòng)C3單元格的填充柄至C11單元格。(3)在D4單元格中使用公式“=AVERAGE(C3∶C4)”,如圖4-34所示。圖

4

34

使用公式(4)確認(rèn)D4單元格的計(jì)算后,拖動(dòng)D4單元格的填充柄至D11單元格。(5)修整后的散點(diǎn)圖如圖4-35所示。圖4-35修整后的散點(diǎn)圖分析工具庫(kù)的加載方法如下:(1)選擇“文件”→“選項(xiàng)”命令,如圖4-36所示。(2)打開(kāi)“Excel選項(xiàng)”對(duì)話框,選擇“加載項(xiàng)”,在