【HR必看】Excel中對×××号碼的處理技巧
×××号碼的處理是HR部門經常遇到的問題，我給幾個地方的企業教育訓練時發現，相當一部分人員基本還是手動對×××号碼進行處理，效率可想而知啦！下面，劉老師為大家深入講解Excel中，與×××号碼處理相關的方方面面。
一、錄入技巧
1、手動輸入問題與解決方法
問題：在Excel中輸入×××号碼時，發現輸入後變成了科學計數格式，如圖：
使用單引号" ' "強制轉成文本類型，發現後三位竟然全變成了0。
咋辦呢？
原因：Excel中輸入大于11位數字時，會自動以科學計數法顯示。Excel最大精确到15位，大于15位，後面的數字全轉換為0。
解決辦法：
1）手動輸入時，在×××号碼先輸入單引号，再輸入×××号，如上圖，将強制格式為文本。
2）先把單元格格式設定為"文本"，再輸入×××号碼。
2、×××号碼長度驗證
在輸入×××号碼時，由于數字太多，很容易多輸或漏輸。使用"資料驗證"可以限制輸入的必須是數字且必須是18位。
單擊"資料"頁籤"資料工具"分組--"資料驗證"。在彈出的對話框中的"驗證條件"裡選擇"自定義"，公式框中輸入"=LEN(--E2)=18"。
"Len"函數：用來統計字元串中字元的個數。
"--E2"：負負得正，對單元格數值沒有影響，但輸入"-"的目的，確定E2單元格必須為數值。
3、使用分列批量強制轉為文本
選擇×××号碼所在的單元格區域，單擊"資料"頁籤，選擇"資料工具"分組"分列"。在彈出的"文本分列"對話框中，單擊"下一步"，"下一步"，第3步的"列資料格式"中選擇"文本"，然後單擊"完成"。
4、批量導入
可以把來自文本檔案、網頁、Access、SQL等資料内容直接導入到Excel中進行處理。下面為從文本檔案導入為例講解導入步驟。
如圖，已有"人事名單"的文本檔案。
步驟1：單擊"資料"頁籤，在"擷取外部資料"分組中，選擇"自文本"，在"導入文本檔案"對話框中，找到要導入的文本檔案"人事名單"，再單擊"導入"。
步驟2：如果文本檔案中，包含标題，則選中"資料包含标題"選項。然後單擊"下一步"。
步驟3：分隔符号選擇"Tab鍵"，然後單擊"下一步"。
步驟4：對下列對話框中，對每列設定"列資料格式"，其中包含×××号碼的列設定為"文本"格式，單擊"完成"。
步驟5：在"導入資料"對話框中，選擇資料放置的起始位置後，單擊"确定"。
二、從×××号碼中提取生日和性别
1、提取生日
在D2單元格中輸入公式：
=TEXT(MID(C2,7,8),"0000-00-00")
利用填充快速複制公式：
選中D2單元格，滑鼠放在右下角，輕按兩下，快速填充。
2、提取性别
×××号碼的17位為性别，奇數為"男"，偶數為"女"。
=IF(MOD(MID(C2,17,1),2),"男","女")
Mid:從字元串指定位置提取指定個數的字元。
MOD：傳回兩數相除的餘數。
3、計算年齡
DATEDIF函數是Excel隐藏函數，可以傳回兩個日期之間的年\月\日間隔數。
DATEDIF(start_date,end_date,unit)
Start_date時間段内的起始日期。
End_date時間段内結束日期。
Unit 指定傳回數值類型。
Unit 傳回
注：結束日期必須大于起始日期
"Y" 傳回整年數。
"M" 傳回整月數。
"D" 傳回間隔天數。
"MD" start_date 與 end_date 日期中天數的差。忽略日期中的月和年。
"YM" start_date 與end_date 日期中月數的差。忽略日期中的年。
"YD" start_date 與 end_date 日期中天數的差。忽略日期中的年。
=DATEDIF(D2,TODAY(),"Y")
或
=YEAR(TODAY())-YEAR(D2)
更多精彩内容，請關注微信平台
| |
| ||
| 掃描左邊的二維碼，關注我們 | |||
| 劉道軍 微軟視窗講師 | 13年的IT職業教育經驗，10年的企業教育訓練及項目經驗。深得廣大企業及學員喜愛。 曾經授課企業： 武漢電信、山西省國稅局、東風本田、中國網通河北分公司、省教育廳、泉州電信、81398部隊、河南省國網、甘肅省國網、新疆國網、華中電網、甘肅省國稅、江西省國網、四川省國網、上海國網、浦發銀行、東風、神龍汽車、河南國稅、建設銀行、NEC、博世、德國拜耳……。 |