Excel作為數據分析最常用的工具之一,在實際的操作中也有很多的技巧。如果要對數據分析處理的結果準確率要100%,必須要有良好的數據源,所以,在數據的處理和分析中,對數據的清洗是必不可少的,是數據分析和處理的第一步。
一、數據清洗:提取類。
函數:LEFT、Right、Mid、FIND。
目的:按照指定的要求提取指定的值。
方法:
在指定的目標單元格中依次輸入:=LEFT(C3,6)、=MID(C3,7,8)、=RIGHT(C3,4)。
解讀:
除了用Left、Mid和Right提取固定位置的值之外,還可以藉助Find函數提取不固定位置的值。
目的:分別提取混合內容「姓名&工號」中的「姓名」和「工號」。
方法:
在目標單元格中輸入公式:=LEFT(B3,FIND("-",B3)-1)、=MID(B3,FIND("-",B3)+1,100)。
解讀:
1、公式中的-1和+1是修正值,在實際的應用中要靈活對待。
2、公式:=MID(B3,FIND("-",B3)+1,100)中的第3個參數「100」是自定義值,只要明顯的大於要提取欄位的長度即可。
二、數據清洗:清除類。
函數:Trim。
目的:清除「員工姓名&工號」中多餘的空格。
方法:
在目標單元格中輸入公式:=TRIM(B3)。
解讀:
1、為了更清晰的進行對比,用Len函數對清除前後的字符串長度進行了測量。
2、如果字符串中間有多個空格,則只保留一個,其餘的全部會清除。
三、數據清洗:替換類。
1、REPLACE函數。
功能:將指定字符串中的部分字符串用新的字符串進行替換。
語法結構:=Replace(源字符串,開始位置,字符長度,替換字符串)。
目的:將「員工姓名&工號」中的「-」替換為「*」。
方法:
在目標單元格中輸入公式:=REPLACE(B3,FIND("-",B3),1,"*")。
2、Substitute函數。
功能:將指定字符串中指定的字符用心的字符進行替換。
語法結構:=Substitute(源字符串,被替換字符串,替換字符串,[替換序號])。
解讀:
參數「替換序號」可省略,意思就是值如果「源字符串」中有兩個或多個「被替換字符串」,通過參數「替換序號」來指定具體要替換第幾個「被替換字符串」。例如:字符串「我愛我的祖國」中有2個「我」,如果「替換序號」為1,則只替換第1個「我」;如果「替換序號」為2,則只替換第2個「我」。
目的:將「員工姓名&工號」中的「-」替換為「*」。
方法:
在目標單元格中輸入公式:=SUBSTITUTE(B3,"-","*")。
四、數據清洗:內容合併類。
1、Concat函數。
功能:連接列表或文本字符串區域。
語法結構:=Concat(字符串或單元格區域)。
目的:將同一員工的所有信息合併到「備註」列中。
方法:
在目標單元格中輸入公式:=CONCAT(B3:F3)。
2、Phonetic函數。
功能:合併出數字外的字符串或區域。
語法結構:=Phonetic(字符串或單元格區域)。
目的:將同一員工的所有信息合併到「備註」列中。
方法:
在目標單元格中輸入公式:=PHONETIC(B3:F3)。
解讀:
合併的內容中並沒有「月薪」是因為其功能決定的,Phonetic函數不能合併沒有拼音的字符。
3、Textjoin函數。
功能:使用分隔符連接列表字符串區域。
語法結構:=Textjoin(分隔符,是否保留空格,合併區域)。
目的:將同一員工的所有信息合併到「備註」列中。
方法:
在目標單元格區域中輸入公式:=TEXTJOIN("、",1,B3:F3)。
解讀:
上述的3個合併字符串函數,除了按列合併內容外,還可以按行合併。
最美尾巴:
工欲善其事,必先利其器,數據的處理和分析也是如此,要得到正確的結果,首先要對數據進行清洗,文中從四個方面出發,介紹了4類數據清洗技巧,包括字符提取、字符清除、字符替換以及字符連接。在實際的應用中具有很高的應用價值哦!