1、首先開啟exce表格,在左邊紅色方框中輸入需要篩選出生年月日的身份證號碼,然後在右邊邊框中準
Excel表格如何篩選身份證中的出生年月呢?下面來一起看看吧!
方法
開啟Excel應用程序。
1、首先判斷身份證號碼是15位還是18位,使用len(身份號碼)判斷長度,然後使用if進行判斷
新建一個工作簿。
1、開啟EXCEl檔案,選擇身份證號所在列,點擊右鍵 ,選擇複製2、然後在“數據”選單下選擇“分
爲了便於演示,輸入了一下數據。
在Excel表格中輸入身份證號碼,可以透過MID函數自動生成出生年月日。方法步驟如下:1、開啟需
輸入以下公式“=DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2))”,然後回車即可。
1、首先開啟excel,我們看下面的表格是員工的姓名和身份證號碼。2、在excel提取性別的單元
結果如圖所示
1、以excel2010版本爲例,如圖所示有下列的身份證號碼,其中身份證格式是文字格式,否則後面的幾
擴展閱讀,以下內容您可能還感興趣。
exce表格中怎樣在身份證中篩選出生年月日
1、首先開啟exce表格,在左邊紅色方框中輸入來需要篩選出生年月日的身份證號碼,然後在右邊*邊框中準備輸入需要篩選出的生年月日,如下圖所示:
2、在對應的單元格輸入“=TEXT(MID(B3,7,8),"0000-00-00")”,其中MID函數作用爲從源指定的字元串中截取出指定數量字元,TEXT函數作用爲將制定的字元轉化爲自己想要的格式文字,如下圖zd:
3、然後按下回車鍵之後就可以看到想要的格式出生年月,如下圖:
4、然後將公式填充剩下的單元格,就得到了全部的出生年月,如下圖:
EXCEL中如何從身份證號碼中篩選出年月日(出生日期)
材料/工具:Excel2010
1、打zd開事先準備好數據工作表Excel2010表格檔案。如圖所示
2、選定需要提取出生年月日的單專元格,在編輯欄輸入公式。示例圖中,選定C2單元格,欄輸入以下公式:=DATE(MID(B2,7,4),MID(B2,11,2),MID(B2,13,2))
3、向下拖動填充序列,即可實現從多個身份證號碼提屬取身份證號碼。
4、提取效果如圖所示。
在excel表格中如何篩選身份證號碼裏出生日期那幾個數字
1、首復先判斷身份證號碼是15位還是制18位,使用len(身份號碼)判斷百長度,然後使用if進行判斷
2、將出生年月日取出、連接,使用mid或者度left或者right取出出生日期
3、使用datedif篩選,判知斷出生年月日與現在相差道多少年
MID(A1,7,IF(LEN(A1)=18,8,6)
在EXCEL中怎麼從身份證號中批量提取出生年月
1、開啟EXCEl檔案,選擇身份證號所在百列,點擊右鍵 ,選擇複製
2、然後在“數據”選單下選擇“分列”
3、在“分列”對話框中選擇“固定列寬”,點擊下一步
4、單擊數據預覽的下方的標尺,把身份證號,分成3段,中間一段爲出生年月,點擊下一步
5、單擊圖示中度標出來的1、3,分別選擇“不匯入此列(跳過)”
6、選擇中間一列,數據格式選擇“日期”格內式
7、然後點擊“目標區域”後的小方框
8、選擇所要填寫出生日期的區域,按Enter回車鍵
9、在彈出來的文容本分列嚮導中點擊完成
10、介面中選擇的出生日期區域便會被自動填寫
在EXCEL表格中 如何從身份證號碼中提取出生年月
一、分析身份證號碼
其實,身份證號碼與一個人的性別、出生年月、籍貫等資訊是緊密相連的,無論是15位還是18位的身份證號碼,其中都儲存了相關的個人資訊。
15位身份證號碼:第7、8位爲出生年份(兩位數),第9、10位爲出生月份,第11、12位代表出生日期,第15位代表性別,奇數爲男,偶數爲女。
18位身份證號碼:第7、8、9、10位爲出生年份(四位數),第11、第12位爲出生月份,第13、14位代表出生日期,第17位代表性別,奇數爲男,偶數爲女。
例如,某學生的身份證號碼(15位)是320521198908070242,那麼表示1989年8月7日出生,性別爲女。如果能想辦法從這些身份證號碼中將上述個人資訊提取出來,不僅快速簡便,而且不容易出錯,覈對時也只需要對身份證號碼進行檢查,肯定可以大大提高工作效率。
二、轉換身份證號碼格式
我們先將學生的身份證號完整地輸入到EXCEL表格中,這7a64e58685e5aeb931333337626131時默認爲“數字”格式(單元格內顯示的是科學記數法的格式),需要更改一下數字格式。選中該列中的所有身份證號後,右擊鼠標,選擇“設定單元格格式”。在彈出對話框中“數字”標籤內的“分類”設爲“文字”,然後點擊確定。
三、提取個人資訊
這裏,我們需要使用IF、LEN、MOD、MID、DATE等函數從身份證號碼中提取個人資訊。假設學生的身份證號碼資訊已輸入完畢(C列),出生年月資訊填寫在D列,性別資訊填寫在B列。
1. 提取出生年月資訊
如果上交報表時需要填寫出生年月日,我們需要關心身份證號碼的相應部位即可,即顯示爲“19890807”這樣的資訊。在D2單元格中輸入公式
=IF(LEN(C2)=15,"19"&MID(C2,7,6),MID(C2,7,8)),其中:
文字函數MID返回文字字元串中從指定位置開始的特定數目的字元,該數目由用戶指定。語法:MID(text,start_num,num_chars)即MID(文字,開始字元,所取字元數),
Text 是包含要提取字元的文字字元串。
Start_num 是文字中要提取的第一個字元的位置。文字中第一個字元的start_num 爲 1,以此類推。
Num_chars 指定希望 MID 從文字中返回字元的個數。
字元個數計算函數LEN返回文字字元串中的字元數。語法:LEN(text)
Text 是要查找其長度的文字。空格將作爲字元進行計數。
LEN(C2)=15:檢查C2單元格中字元串的字元數目,本例的含義是檢查身份證號碼的長度是否是15位。
MID(C2,7,4):從C2單元格中字元串的第7位開始提取6位數字,本例中表示提取15位身份證號碼的第7、8、9、10、11、12位數字。
"19"&:表示如果身份證號碼是15位,在年份前加“19”。
MID(C2,7,8):從C2單元格中字元串的第7位開始提取8位數字,本例中表示提取18位身份證號碼的第7、8、9、10、11、12、13、14位數字。
IF(LEN(C2)=15,MID(C2,7,6),MID(C2,7,8)):IF是一個邏輯判斷函數,表示如果C2單元格是15位,則提取第7位開始的6位數字並在年份前加“19”,如果不是15位則提取自第7位開始的8位數字。
如果需要顯示爲“1989-08-07”這樣的格式,可以把上面的公式稍加修改即可:
=IF(LEN(C2)=15,"19"&MID(C2,7,2)&"-"&MID(C2,9,2)&"-"&MID(C2,11,2),MID(C2,7,4)&"-"&MID(C2,11,2)&"-"&MID(C2,13,2))。
解釋:如果身份證號碼是15位,則從第7位開始取兩位數字,並在這兩位數字前加“19”,後面加“-”,從第9位開始取兩位數字,並在這兩位數字後面加“-”,從第11位開始取兩位數字。否則,從第7位開始取四位數字,並在這四位數字後面加“-”,從第11位開始取兩位數字,並在這兩位數字後面加“-”,從第13位開始取兩位數字。
也可以使用DATE格式,並在“單元格格式→日期”中進行設定。
如果學生的身份證號全部都是18位的新一代身份證,可以以把上面的公式進行簡化:
=MID(C2,7,4)&"-"&MID(C2,11,2)&"-"&MID(C2,13,2)
2. 提取性別資訊
由於報表中學生不是按照男、女固定的順序進行編排,如果一個一個手工輸入的話,既麻煩又容易出錯。
例如性別資訊統一在B列填寫,可以在B2單元格中輸入公式:
=IF(MOD(IF (LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"男","女")
其中:
LEN(C2)=15:檢查身份證號碼的長度是否是15位。
MID(C2,15,1):如果身份證號碼的長度是15位,那麼提取第15位的數字。
MID(C2,17,1):如果身份證號碼的長度不是15位,即18位身份證號碼,那麼應該提取第17位的數字。
MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2):用於得到給出數字除以指定數字後的餘數,本例表示對提出來的數值除以2以後所得到的餘數。
IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"男","女"):如果除以2以後的餘數是1,那麼B2單元格顯示爲“男”,否則顯示爲“女”。
回車確認後,即可在B2單元格顯示正確的性別資訊,接下來就是選中填充柄直接拖曳。現在這份報表無論是提取資訊或是覈對,都方便多了。
提取性別也可以使用下面這個公式(無論是15位還是18位):
=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女")
如果學生的身份證號全部都是18位的新一代身份證,可以使用下面的公式:
=IF(MID(C2,17,1)/2=TRUNC(MID(C2,17,1)/2),"女","男")
數學函數TRUNC 將數字的小數部分截去,返回整數。
語法:TRUNC(number,num_digits)
Number 需要截尾取整的數字。
Num_digits 用於指定取整精度的數字。Num_digits 的默認值爲 0。
說明:函數 TRUNC 和函數 INT 類似,都返回整數。函數 TRUNC 直接去除數字的小數部分,而函數 INT 則是依照給定數的小數部分的值,將其四捨五入到最接近的整數。函數 INT 和函數 TRUNC 在處理負數時有所不同。
如果身份證號的輸入已是15或18位,還可以用公式:
=IF(MOD(LEFT(RIGHT(C2,(LEN(C2)=18)+1)),2),"男","女")
RIGHT 根據所指定的字元數返回文字字元串中最後一個或多個字元。
語法:RIGHT(text,num_chars)
Text 是包含要提取字元的文字字元串。
Num_chars 指定希望 RIGHT 提取的字元數。
LEFT 基於所指定的字元數返回文字字元串中的第一個或前幾個字元。
語法:LEFT(text,num_chars)
Text 是包含要提取字元的文字字元串。
Num_chars 指定要由 LEFT 所提取的字元數。