關於Excel中身份證號碼的8個技巧,你知道哪些?

在Excel表格中，經常需要輸入身份證號碼，很多小夥伴很容易遇到一些問題，可能有些小夥伴知道怎麼解決，但是只是知其然而不知其所以然。今天我們就來講講關於身份證的相關技巧，一起學習吧~

身份證號碼錄入

關於身份證的輸入，初學Excel的童鞋一定會遇到以下問題，看看下面這個例子：

身份證號碼錄入

從上圖中我們看到，末尾3位會變成0，這是因為在Excel當前單元格中輸入的數字位數如果超過15位(不含15位)時，系統將15位以後的數字全部顯示為「0」，並以科學計數法顯示。這樣是挺智能，但是失去了身份證原來的意義。我們怎麼做可以讓它顯示正常呢。

來看看下圖兩種解決方式：

單元格式設置成文本或單引號

以上兩種方式都能有效解決上述問題，一是設置單元格式為文本，二是輸入單引號（英文輸入法狀態下）。有童鞋可能有點小疑問，輸入西文單引號是否會影響文本的內容的計算，這裡要告訴大家是不影響的哈。大家可以用len函數計算下字符串的長度來驗證：

在C2單元格中輸入=LEN(B2),在C3單元格中輸入=LEN(B3),顯示結果均為18.結果是一樣的哈！

解決了身份證輸入的問題，有時我們可能還要提取身份證中的出生年月、性別和年齡等等，然後再將提取的信息進行轉化。

當然在提取和信息轉化之前，我們必須要了解下身份證號碼的結構及含義，見下圖：

身份證號碼組成圖（來源百度）

這樣我們就知道哪些信息分別對應的是性別和出生日期啦，那麼要截取出文本中對應的字符串，我們一般用文本函數，文本函數相對比較簡單，易學易會哈，今天我們要用到的文本截取函數是MID函數。

公式函數

MID函數

功能說明：MID 返回文本字符串中從指定位置開始的特定數目的字符，該數目由用戶指定。

使用格式：=MID(text,start_num,num_chars)，它包含三個參數

通俗解釋：=MID(截取字符串,截取起始字符位置,截取的字符串個數)

學習了這個函數後我們就可以截取出性別的字符串啦，性別是從C2單元格中的第17位開始截取1位，即在D2單元格中輸入公式：=MID(C2,17,1)

出生日期是從C2單元格中的第7位開始截取8位，在E2單元格中輸入公式：=MID(C2,7,8)

輸入好公式後，下拉填充公式即可。

mid函數

提取的這些數據與我們理解的性別和日期還有些差距，繼續往下看↓↓↓：

性別中數字為奇數時則代表男性，為偶數時則代表女性

那我們只需要簡單的判定即可。我們需要用的ISEVEN函數判定數字是否為偶數。

ISEVEN函數

功能說明：如果參數 number 為偶數，返回 TRUE，否則返回 FALSE。

使用格式：=ISEVEN(number)

通俗解釋：=ISEVEN(被判斷的數據)，參數只有1個哈

從功能說明可以看出它返回的結果是TRUE或者FALSE,那我們還要配合IF函數使用，來得到性別。這裡簡單講下IF函數的使用：

IF函數

功能說明：對邏輯值進行比較，得到一個判定結果對應的值

使用格式：=IF(logical_test,value_if_true,value_if_false)

通俗解釋：=IF(真或假,結果為真返回的值,,結果為真返回的值)

說完了性別，我們說下出生日期，出生日期相對簡單，只需要轉化下格式即可。

我們這裡要介紹的是Text函數，這個函數的功能非常強大，強大到你無法想像喔。按照小編的理解，這個函數都可以出個專題來講。來看看它的用法：

TEXT函數

功能說明：TEXT函數可通過格式代碼向數字應用格式，進而更改數字的顯示方式。如果要按更可讀的格式顯示數字，或者將數字與文本或符號組合，它將非常有用。

使用格式：=TEXT(value,format_text)

通俗解釋：=TEXT(將要轉化格式的內容,將要顯示的格式)

結合上面的介紹，我們來分別對D2和E2單元格的公式加工改造下吧，來看看下圖：

顯示格式轉換

分別在D2和E2單元格中輸入公式：

=IF(ISEVEN(MID(C2,17,1)),"女","男")

=TEXT(MID(C2,7,8),"0-00-00")

搞定啦，是不是很簡單，還有最後一個問題需要解決，那就是年齡啦，這裡給大家介紹另外兩個函數，一個是YEAR函數,另外一個是TODAY函數

分別看看用法：

YEAR函數

功能說明：返回對應於某個日期的年份。 Year 作為 1900 - 9999 之間的整數返回。

使用格式：=YEAR(日期)

通俗解釋：=YEAR(日期)，帶一個參數

TODAY函數

功能說明：獲取今天的日期(來源於電腦的日期)

使用格式：=TODAY()

假通俗解釋：=TODAY()，直接輸入，不需要帶參數.

學習了這兩個函數後，就可以知道怎麼得到年齡啦，在F2單元格中輸入公式=YEAR(TODAY())-YEAR(E2)，搞定！

來看看下圖操作：

年齡轉化

總結：

單元格輸入數字長度大於15時，後面的數字會變成0，需要在輸入前，將單元格格式設置成文本，或在輸入的數字前加上英文狀態下的單引號ISEVEN是判定偶數函數，與之對應的是ISODD函數，判定數據是否為奇數TEXT文本函數可用於各種文本格式轉化，需要重點掌握YEAR函數、TODAY函數是日期相關的函數，還有其他使用頻率高的日期函數，如：MONTH,DAY,DATE函數等等關於相關函數的學習，大家可以根據上面的例子，自己練習學習哈，強烈推薦大家參考Excel函數對應的幫助，寫的比較詳細。

今天的分享就到這裡，內容比較多，但大部分的函數都相對比較簡單，容易上手。相信小夥伴們，很快就會啦！感謝大家閱讀，大家如有任何問題，歡迎評論留言，小編會一一解答。

效率優先