目錄/Contents3.1數據清洗3.2數據的來源第三章數據的處理3.3數據的修整目錄/Contents3.1數據清洗3.2數據的來源13.1數據清洗數據清洗就是將格式錯誤的數據進行處理糾正，將錯誤的數據糾正或刪除，將缺失的數據補充完整，將重復多余的數據刪除。3.1.1數據一致性處理通過統計調查收集上來的數據，經常會出現同一字段的數據格式不一致的問題，如圖3-1所示。這會直接影響后續的數據分析，所以必須對數據的格式做出一致性處理。圖3-1數據格式不一致的資料3.1數據清洗數據清洗就是將格式錯誤的數據進行處理糾正，21.1數字通信系統的基本概念下面就以圖3-1所示的數據為例，將“身高”這個字段中的數據去掉字符“cm”。打開Excel文件“數據處理.xlsx”，找到“數據清洗”工作表。（1）把鼠標指針移到字母C上，當指針變成

時，單擊選擇C列，如圖3-2所示。（2）選擇“查找和選擇”|“替換”命令，如圖3-3所示。41.1數字通信系統的基本概念下面就以圖3-1所示的數據為3圖3-2選擇C列圖3-3選擇“替換”命令圖3-2選擇C列圖3-3選擇“替換”命令4（3）在“查找和替換”對話框的“查找內容”中輸入“cm”，設置“替換為”為空，單擊“全部替換”按鈕完成替換，如圖3-4所示。替換后的結果如圖3-5所示。圖3-4輸入查找內容和替換內容圖3-5替換后的結果（3）在“查找和替換”對話框的“查找內容”中輸入“cm”，設53.1.2缺失數據的處理數據清單中，單元格如果出現空值，就認為數據存在缺失。缺失數據的處理方法通常有以下3種：

用樣本均值（或眾數、中位數）代替缺失值；

將有缺失值的記錄刪除；

保留該記錄，在要用到該值做分析時，將其臨時刪除（最常用方法）。3.1.2缺失數據的處理6首先來解決如何發現缺失數據，僅靠眼睛來搜索缺失數據顯然是不現實的，一般我們用“定位條件”來查找缺失數據的單元格。下面演示將“年齡”字段中的空值均替換為“18”。（1）選擇“年齡”所在的E列。（2）選擇“查找和選擇”|“定位條件”命令，如圖3-6所示。（3）在“定位條件”對話框中，選中“空值”單選項，如圖3-7所示。圖3-6選擇“定位條件”命令

圖3-7選擇定位條件“空值”首先來解決如何發現缺失數據，僅靠眼睛來搜索缺失數據顯然是不現7（4）單擊“確定”按鈕后，E列所有的空白單元格呈選中狀態，如圖3-8所示。（5）輸入替代值“18”，按Ctrl+Enter組合鍵確認，結果如圖3-9所示。圖3-8查找到所有空白單元格圖3-9統一輸入新的數據（4）單擊“確定”按鈕后，E列所有的空白單元格呈選中狀態，如83.1.3刪除重復記錄刪除重復記錄的操作極其簡單，只需單擊數據表的任意位置，再單擊“數據”|“刪除重復項”按鈕即可，如圖3-10所示。圖3-10刪除重復項3.1.3刪除重復記錄圖3-10刪除重復項93.2數據加工數據加工的手段主要有數據轉置、字段分列、字段匹配、數據抽取、數據計算。3.2.1數據轉置操作的方法是：先復制好橫行數據，然后在粘貼時單擊“開始”|“剪貼板”組“粘貼”按鈕下面的三角箭頭，單擊“轉置”按鈕即可，如圖3-11所示。圖3-11轉置性粘貼3.2數據加工數據加工的手段主要有數據轉置、字段分列、字10 3.2.2字段分列（1）選擇“字段分列”工作表的A列數據，如圖3-12所示。（2）單擊“數據”|“分列”按鈕，如圖3-13所示。圖3-12選擇A列

圖3-13數據分列 3.2.2字段分列圖3-12選擇A列圖3-1311（3）要將字段“姓名”中的第一個字分列出來，所以選中“固定列寬”單選項，如圖3-14所示。圖3-14選中“固定列寬”單選項（3）要將字段“姓名”中的第一個字分列出來，所以選中“固定列12（4）單擊“下一步”按鈕，在刻度尺上單擊鼠標確定分列的位置，如圖3-15所示。（5）單擊“下一步”按鈕，確定目標區域的起點單元格D1，如圖3-16所示。圖3-15確定分列位置圖3-16確定目標區域（4）單擊“下一步”按鈕，在刻度尺上單擊鼠標確定分列的位置，13（6）單擊“完成”按鈕，分列的結果如圖3-17所示。圖3-17分列結果（6）單擊“完成”按鈕，分列的結果如圖3-17所示。圖3-1143.2.3字段匹配字段匹配就是將原數據清單中沒有但其他數據清單中有的字段匹配過來。例如，文件“數據處理.xlsx”中的“全校名單”工作表是某校2015級全體學生的基本信息（見圖3-18），“四級名單”工作表是2015級學生中報考了英語四級的學生名單（見圖3-19）。圖3-18“全校名單”工作表圖3-19“四級名單”工作表3.2.3字段匹配圖3-18“全校名單”工作表圖3-115（1）將“白有成”的身份證號碼匹配到單元格D2。（2）雙擊D2的填充柄完成填充，將所有人的身份證號碼都匹配過來，結果如圖3-21所示。圖3-20vlookup函數的參數設置圖3-21匹配結果（1）將“白有成”的身份證號碼匹配到單元格D2。圖3-20163.2.4數據抽取數據抽取是指利用原數據清單中某些字段的部分信息得到一個新字段。常用的數據抽取函數有left()、right()、mid()、year()、month()、day()、weekday()。圖3-22weekday函數應用3.2.4數據抽取圖3-22weekday函數應用173.2.5數據計算例1：文件“數據處理.xlsx”的“數據計算1”工作表中只有“銷量”和“單價”，沒有“銷售額”，可以通過公式“銷售額=單價×銷量”來計算銷售額，如圖3-23所示。圖3-23計算銷售額3.2.5數據計算圖3-23計算銷售額18例2：文件“數據處理.xlsx”的“數據計算2”工作表中只有“成交單數”和“好評單數”，可以通過公式“好評率= ”來計算好評率，如圖3-24所示。圖3-24計算好評率例2：文件“數據處理.xlsx”的“數據計算2”工作表中只有19例3：文件“數據處理.xlsx”的“數據計算3”工作表中，已知商品的“上架日期”和“下架日期”，可以通過公式“銷售天數=下架日期-上架日期”來計算商品的銷售天數，如圖3-25所示。圖3-25計算銷售天數例3：文件“數據處理.xlsx”的“數據計算3”工作表中，已20例4：文件“數據處理.xlsx”的“數據計算4”工作表中，已知商品的“上架日期”，要計算迄今為止的上架天數，可以用函數today來獲取當天的日期，用公式“=today()-B2”來計算上架天數，如圖3-26所示。圖3-26計算上架天數例4：文件“數據處理.xlsx”的“數據計算4”工作表中，已21例5：文件“數據處理.xlsx”的“數據計算3”工作表中，可以用公式“=D2/360”計算銷售年數，結果為3.6，如圖3-27所示。圖3-27計算銷售年數例5：文件“數據處理.xlsx”的“數據計算3”工作表中，可22這樣計算出來的年數通常是一個小數，如果希望得到整數，可以用int函數取整，即用公式“=int(D2/360)”，結果是3，如圖3-28所示。因為int函數的功能是返回不大于括號內參數的整數。圖3-28用int函數取整這樣計算出來的年數通常是一個小數，如果希望得到整數，可以用i23如果要進行四舍五入式取整，則要用函數round。當round(number，digits)函數的第二個參數為0時，就可以對第一個參數進行四舍五入式取整，所以在編輯欄將公式修改為“=round(D2/360,0)”即可，如圖3-29所示。圖3-29用round函數取整如果要進行四舍五入式取整，則要用函數round。當round24函數int(number)的功能是向下取整（數軸上離左邊最近的整數），如圖3-30所示。圖3-30int函數解釋函數int(number)的功能是向下取整（數軸上離左邊最近25所以，int(6.4)=int(6.7)=6，int(-6.4)=int(-6.7)=-7。函數round(number,digits)的功能是進行四舍五入運算，功能解釋如表3-1所示。number1263.472digits-2-1012四舍五入的位數十位個位取整保留1位小數保留2位小數結果1300126012631263.51263.47表3-1round函數解釋所以，int(6.4)=int(6.7)=6，int(-6.263.3數據的修整在一段較長的時間內，由于普通的、持續的、決定性等基本因素的作用，總體往往呈現逐漸向上或向下變動的趨勢，如圖3-31所示。圖3-31明顯的向上趨勢3.3數據的修整在一段較長的時間內，由于普通的、持續的、27在這樣的趨勢中，也不排除受一些偶然因素或不規則因素的影響，出現與整體趨勢相差很大的極端數據，如圖3-32中箭頭所對應的數據所示。移動平均法就是從時間數列的第一位數值開始，按一定項數求平均數，逐項移動，形成一個新的動態數列。常用的移動平均法有三項移動平均法和四項移動平均法。圖3-32局部的數據異動在這樣的趨勢中，也不排除受一些偶然因素或不規則因素的影響，出283.3.1三項移動平均法例：計算圖3-33所示的表格中，商品銷售額的三項移動平均數。分析：選擇單元格區域A1:B13，單擊“插入”|“散點圖”|“僅帶數據標記的散點圖”按鈕，如圖3-34所示。圖3-332004—2015年銷售額統計圖3-34插入散點圖3.3.1三項移動平均法圖3-332004—201529結果得到圖3-35所示的散點圖，從散點圖可以直觀地看出，第3個點明顯偏小，而第6個點明顯偏大，這可能是由不確定因素的影響造成的。在這種情況下，可以通過移動平均法對數據做修整，盡量排除不確定因素對數據造成的影響。圖3-35原始數據的散點圖結果得到圖3-35所示的散點圖，從散點圖可以直觀地看出，第330圖3-36選擇“平均值”命令圖3-37計算三項平均數圖3-36選擇“平均值”命令圖3-37計算三項31圖3-38計算三項移動平均數圖3-39修整后的數據散點圖圖3-38計算三項移動平均數圖3-39修整后的數據散323.3.2四項移動平均法例：計算圖3-40所示的表格中商品銷售額的四項移動平均數。圖3-40四項移動平均數的計算3.3.2四項移動平均法圖3-40四項移動平均數的計33下面在Excel中用函數average計算四項移動平均數。（1）打開文件“數據處理.xlsx”中的“四項移動平均”工作表，在C3中使用公式“=average(B2:B5)”計算第一個四項移動平均數，如圖3-41所示。（2）確認C3的計算后，將C3的填充柄填充到C11。圖3-41計算四項移動平均數下面在Excel中用函數average計算四項移動平均數。圖34（3）在D4中使用公式“=average(C3:C4)”，如圖3-42所示。（4）確認D4的計算后，將D4的填充柄填充到D11。注意：若采用奇數項移動平均，平均值對準居中原時間數列的項數，一次可得趨勢值。若采用偶數項移動平均，平均值未對準居中原時間數列的項數，需再通過一次移動平均進行正位。圖3-42計算四項移動平均正位數（3）在D4中使用公式“=average(C3:C4)”，如353.3.3分析工具庫的加載和應用在Excel中，還可以用分析工具庫完成移動平均數的計算。默認情況下，Excel并沒有安裝分析工具庫。下面介紹分析工具庫的安裝過程。（1）在Excel2010窗口中，選擇“文件”|“選項”命令。（2）在隨后打開的“Excel選項”對話框中，單擊左邊的“加載項”選項后，再單擊下方的“轉到”按鈕，如圖3-43所示。圖3-43加載項3.3.3分析工具庫的加載和應用圖3-43加載項36（3）在隨后打開的“加載宏”對話框中，選中“分析工具庫”復選項，單擊“確定”按鈕，如圖3-44所示。（4）加載成功后，會在”數據”選項卡中看到一個新的功能“數據分析”，如圖3-45所示。圖3-44加載“分析工具庫”圖3-45加載成功后的“數據”選項卡（3）在隨后打開的“加載宏”對話框中，選中“分析工具庫”復選37下面介紹利用“數據分析”功能計算三項移動平均數的方法。（1）打開文件“數據處理.xlsx”中的“三項移動平均”工作表，單擊“數據”|“數據分析”按鈕。（2）在隨后打開的“數據分析”對話框中選擇“移動平均”選項，如圖3-46所示。圖3-46選擇“移動平均”選項下面介紹利用“數據分析”功能計算三項移動平均數的方法。圖3-38（3）在“移動平均”對話框中設置各參數如圖3-47所示，最終結果如圖3-48所示。圖3-47三項“移動平均”的設置圖3-48最終結果（3）在“移動平均”對話框中設置各參數如圖3-47所示，最終39目錄/Contents3.1數據清洗3.2數據的來源第三章數據的處理3.3數據的修整目錄/Contents3.1數據清洗3.2數據的來源403.1數據清洗數據清洗就是將格式錯誤的數據進行處理糾正，將錯誤的數據糾正或刪除，將缺失的數據補充完整，將重復多余的數據刪除。3.1.1數據一致性處理通過統計調查收集上來的數據，經常會出現同一字段的數據格式不一致的問題，如圖3-1所示。這會直接影響后續的數據分析，所以必須對數據的格式做出一致性處理。圖3-1數據格式不一致的資料3.1數據清洗數據清洗就是將格式錯誤的數據進行處理糾正，411.1數字通信系統的基本概念下面就以圖3-1所示的數據為例，將“身高”這個字段中的數據去掉字符“cm”。打開Excel文件“數據處理.xlsx”，找到“數據清洗”工作表。（1）把鼠標指針移到字母C上，當指針變成

時，單擊選擇C列，如圖3-2所示。（2）選擇“查找和選擇”|“替換”命令，如圖3-3所示。41.1數字通信系統的基本概念下面就以圖3-1所示的數據為42圖3-2選擇C列圖3-3選擇“替換”命令圖3-2選擇C列圖3-3選擇“替換”命令43（3）在“查找和替換”對話框的“查找內容”中輸入“cm”，設置“替換為”為空，單擊“全部替換”按鈕完成替換，如圖3-4所示。替換后的結果如圖3-5所示。圖3-4輸入查找內容和替換內容圖3-5替換后的結果（3）在“查找和替換”對話框的“查找內容”中輸入“cm”，設443.1.2缺失數據的處理數據清單中，單元格如果出現空值，就認為數據存在缺失。缺失數據的處理方法通常有以下3種：

用樣本均值（或眾數、中位數）代替缺失值；

將有缺失值的記錄刪除；

保留該記錄，在要用到該值做分析時，將其臨時刪除（最常用方法）。3.1.2缺失數據的處理45首先來解決如何發現缺失數據，僅靠眼睛來搜索缺失數據顯然是不現實的，一般我們用“定位條件”來查找缺失數據的單元格。下面演示將“年齡”字段中的空值均替換為“18”。（1）選擇“年齡”所在的E列。（2）選擇“查找和選擇”|“定位條件”命令，如圖3-6所示。（3）在“定位條件”對話框中，選中“空值”單選項，如圖3-7所示。圖3-6選擇“定位條件”命令

圖3-7選擇定位條件“空值”首先來解決如何發現缺失數據，僅靠眼睛來搜索缺失數據顯然是不現46（4）單擊“確定”按鈕后，E列所有的空白單元格呈選中狀態，如圖3-8所示。（5）輸入替代值“18”，按Ctrl+Enter組合鍵確認，結果如圖3-9所示。圖3-8查找到所有空白單元格圖3-9統一輸入新的數據（4）單擊“確定”按鈕后，E列所有的空白單元格呈選中狀態，如473.1.3刪除重復記錄刪除重復記錄的操作極其簡單，只需單擊數據表的任意位置，再單擊“數據”|“刪除重復項”按鈕即可，如圖3-10所示。圖3-10刪除重復項3.1.3刪除重復記錄圖3-10刪除重復項483.2數據加工數據加工的手段主要有數據轉置、字段分列、字段匹配、數據抽取、數據計算。3.2.1數據轉置操作的方法是：先復制好橫行數據，然后在粘貼時單擊“開始”|“剪貼板”組“粘貼”按鈕下面的三角箭頭，單擊“轉置”按鈕即可，如圖3-11所示。圖3-11轉置性粘貼3.2數據加工數據加工的手段主要有數據轉置、字段分列、字49 3.2.2字段分列（1）選擇“字段分列”工作表的A列數據，如圖3-12所示。（2）單擊“數據”|“分列”按鈕，如圖3-13所示。圖3-12選擇A列

圖3-13數據分列 3.2.2字段分列圖3-12選擇A列圖3-1350（3）要將字段“姓名”中的第一個字分列出來，所以選中“固定列寬”單選項，如圖3-14所示。圖3-14選中“固定列寬”單選項（3）要將字段“姓名”中的第一個字分列出來，所以選中“固定列51（4）單擊“下一步”按鈕，在刻度尺上單擊鼠標確定分列的位置，如圖3-15所示。（5）單擊“下一步”按鈕，確定目標區域的起點單元格D1，如圖3-16所示。圖3-15確定分列位置圖3-16確定目標區域（4）單擊“下一步”按鈕，在刻度尺上單擊鼠標確定分列的位置，52（6）單擊“完成”按鈕，分列的結果如圖3-17所示。圖3-17分列結果（6）單擊“完成”按鈕，分列的結果如圖3-17所示。圖3-1533.2.3字段匹配字段匹配就是將原數據清單中沒有但其他數據清單中有的字段匹配過來。例如，文件“數據處理.xlsx”中的“全校名單”工作表是某校2015級全體學生的基本信息（見圖3-18），“四級名單”工作表是2015級學生中報考了英語四級的學生名單（見圖3-19）。圖3-18“全校名單”工作表圖3-19“四級名單”工作表3.2.3字段匹配圖3-18“全校名單”工作表圖3-154（1）將“白有成”的身份證號碼匹配到單元格D2。（2）雙擊D2的填充柄完成填充，將所有人的身份證號碼都匹配過來，結果如圖3-21所示。圖3-20vlookup函數的參數設置圖3-21匹配結果（1）將“白有成”的身份證號碼匹配到單元格D2。圖3-20553.2.4數據抽取數據抽取是指利用原數據清單中某些字段的部分信息得到一個新字段。常用的數據抽取函數有left()、right()、mid()、year()、month()、day()、weekday()。圖3-22weekday函數應用3.2.4數據抽取圖3-22weekday函數應用563.2.5數據計算例1：文件“數據處理.xlsx”的“數據計算1”工作表中只有“銷量”和“單價”，沒有“銷售額”，可以通過公式“銷售額=單價×銷量”來計算銷售額，如圖3-23所示。圖3-23計算銷售額3.2.5數據計算圖3-23計算銷售額57例2：文件“數據處理.xlsx”的“數據計算2”工作表中只有“成交單數”和“好評單數”，可以通過公式“好評率= ”來計算好評率，如圖3-24所示。圖3-24計算好評率例2：文件“數據處理.xlsx”的“數據計算2”工作表中只有58例3：文件“數據處理.xlsx”的“數據計算3”工作表中，已知商品的“上架日期”和“下架日期”，可以通過公式“銷售天數=下架日期-上架日期”來計算商品的銷售天數，如圖3-25所示。圖3-25計算銷售天數例3：文件“數據處理.xlsx”的“數據計算3”工作表中，已59例4：文件“數據處理.xlsx”的“數據計算4”工作表中，已知商品的“上架日期”，要計算迄今為止的上架天數，可以用函數today來獲取當天的日期，用公式“=today()-B2”來計算上架天數，如圖3-26所示。圖3-26計算上架天數例4：文件“數據處理.xlsx”的“數據計算4”工作表中，已60例5：文件“數據處理.xlsx”的“數據計算3”工作表中，可以用公式“=D2/360”計算銷售年數，結果為3.6，如圖3-27所示。圖3-27計算銷售年數例5：文件“數據處理.xlsx”的“數據計算3”工作表中，可61這樣計算出來的年數通常是一個小數，如果希望得到整數，可以用int函數取整，即用公式“=int(D2/360)”，結果是3，如圖3-28所示。因為int函數的功能是返回不大于括號內參數的整數。圖3-28用int函數取整這樣計算出來的年數通常是一個小數，如果希望得到整數，可以用i62如果要進行四舍五入式取整，則要用函數round。當round(number，digits)函數的第二個參數為0時，就可以對第一個參數進行四舍五入式取整，所以在編輯欄將公式修改為“=round(D2/360,0)”即可，如圖3-29所示。圖3-29用round函數取整如果要進行四舍五入式取整，則要用函數round。當round63函數int(number)的功能是向下取整（數軸上離左邊最近的整數），如圖3-30所示。圖3-30int函數解釋函數int(number)的功能是向下取整（數軸上離左邊最近64所以，int(6.4)=int(6.7)=6，int(-6.4)=int(-6.7)=-7。函數round(number,digits)的功能是進行四舍五入運算，功能解釋如表3-1所示。number1263.472digits-2-1012四舍五入的位數十位個位取整保留1位小數保留2位小數結果1300126012631263.51263.47表3-1round函數解釋所以，int(6.4)=int(6.7)=6，int(-6.653.3數據的修整在一段較長的時間內，由于普通的、持續的、決定性等基本因素的作用，總體往往呈現逐漸向上或向下變動的趨勢，如圖3-31所示。圖3-31明顯的向上趨勢3.3數據的修整在一段較長的時間內，由于普通的、持續的、66在這樣的趨勢中，也不排除受一些偶然因素或不規則因素的影響，出現與整體趨勢相差很大的極端數據，如圖3-32中箭頭所對應的數據所示。移動平均法就是從時間數列的第一位數值開始，按一定項數求平均數，逐項移動，形成一個新的動態數列。常用的移動平均法有三項移動平均法和四項移動平均法。圖3-32局部的數據異動在這樣的趨勢中，也不排除受一些偶然因素或不規則因素的影響，出673.3.1三項移動平均法例：計算圖3-33所示的表格中，商品銷售額的三項移動平均數。分析：選擇單元格區域A1:B13，單擊“插入”|“散點圖”|“僅帶數據標記的散點圖”按鈕，如圖3-34所示。圖3-332004—2015年銷售額統計圖3-34插入散點圖3.3.1三項移動平均法圖3-332004—201568結果得到圖3-35所示的散點圖，從散點圖可以直觀地看出，第3個點明顯偏小，而第6個點明顯偏大，這可能是由不確定因素的影響造成的。在這種情況下，可以通過移動平均法對數據做修整，盡量排除不確定因素對數據造成的影響。圖3-35原始數據的散點圖結果得到圖3-35所示的散點圖，從散點圖可以直觀地看出，第369圖3-36選擇“平均值”命令圖3-37計算三項平均數圖3-36選擇“平均值”命令圖3-37計算三項70圖3-38計算三項移動平均數圖3-39修整后的數據散點圖圖3-38計算三項移動平均數圖3-39修整后的數據散713.3.2四項移動平均法例：計算圖