excel技巧之——怎麼從身份證號碼裡提取員工性別

=IF(D2="","",IF(LEN(D2)=15,IF(MOD(MID(D2,15,1),2)=1,"男","女"),IF(MOD(MID(D2,17,1),2)=1,"男","女"))) 把身份證 號碼放在 D2 就可以了 記得給分哦

現在我國的身份證號碼都是18位的，其中的第17位為公民的性別，在製作公司的員工個人資訊表中，需要輸入性別，難道要找到每個人核實性別後再輸入嗎？不用這麼麻煩的，統計每個人的身份證號碼即可自動得知其性別。但是如何設定呢？下面我來教大家吧！

身份證號碼包含了每個人的出生日期、性別等等資訊，並且最後一位數字可以判定身份證號碼的正確與否。以下步驟為詳細講解使用函式提取性別的方法。公式為“=MID(A2,17,1)”（A表示單元格，從第17個字元開始提取1個字元） 1、首先，將所需要提取出生

材料/工具

Excel

方法

如圖，這是需要根據身份證號碼判斷其性別的身份證號碼。

讓Excel根據身份證號自動填寫“性別”（男、女） 2007年10月20日 星期六 05:33 P.M. 每個單位一般都有“職工花名冊”，其中有“身份證號”和“性別”兩列資料，有沒有想過讓表格根據身份證號自動填寫性別呢? 方法如下： 在C4單元格中輸入“=IF(MOD(RIGHT(

MOD(MID(A2,17,1),2)，對mid函式提取出的那個字元，用mod函式將其除以2，看餘數為0還是1，MOD函式是返回兩數相除的餘數，此為mid函式的返回數除以2，餘數分別為0或者1，也就是整除或者不整除，數學中，偶數除2餘數為0，奇數除2餘數為1，mod和mid函式相配合，即可得知A2單元格的第17位是偶數還是奇數。

=IF(MOD(VALUE(MID(A1,17,1)),2)=1,"男","女") 在A1中輸入一串18位輸的身份證號 身份證號碼各位數字的含義如下： 1）前1、2位數字表示：所在省份的程式碼； （2）第3、4位數字表示：所在城市的程式碼； （3）第5、6位數字表示：所在區縣的程式碼； （4

根據上述判斷出的奇數或者是偶數，利用if函式即可返回我們希望得到的結果，即每一個身份證號碼對應的性別

Excel中提取18位身份證號性別公式：“=IF(MOD(MID(A1,17,1),2),"男","女")”。公式的具體用法如下： 1、開啟Excel，在一列中從上到下依次輸入需要提取性別的18位身份證號。把身份證都放在同一列是為了方便用公式提取性別。 2、在第一行身份證後輸

接下來單擊右鍵，選擇設定單元格格式。

15位和18位身份證號都適用 =IF(MOD(MID(A1,15,3),2)=1,"男","女") 或 =IF(MOD(MID(A1,15,3),2)=0,"女","男") 18位身份證號適用 =IF(MOD(MID(A1,15,1),2)=1,"男","女") 或 =IF(MOD(MID(A1,15,1),2)=0,"女",男")

點選數字-文字。完成

一、分析身份證號碼 其實，身份證號碼與一個人的性別、出生年月、籍貫等資訊是緊密相連的，無論是15位還是18位的身份證號碼，其中都儲存了相關的個人資訊。 15位身份證號碼：第7、8位為出生年份(兩位數)，第9、10位為出生月份，第11、12位代表出

擴充套件閱讀，以下內容您可能還感興趣。

在excel中，從身份證中提取出生年月日、性別、年齡

這個公式是18位身份證的

年齡：就是從身份證號中提百取出出生年份，用當前的年份去減

性別：判斷倒數第2位是奇數還是偶數

年月度日：從身份證中提取出相應的年月日（這個公式同時適用15位和回18位）

如果重寫這些公式

年齡：

=IF(LEN(A1)=18,DATEDIF(DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),NOW(),"y"),DATEDIF(DATE("19"&MID(A1,7,2),MID(A1,9,2),MID(A1,11,2)),NOW(),"y"))

性別：

=IF(LEN(A1)=18,IF(MOD(VALUE(MID(A1,17,1)),2)=1,"男答","女"),IF(MOD(VALUE(MID(A1,15,1)),2)=1,"男","女"))

年月日：

=IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),DATE("19"&MID(A1,7,2),MID(A1,9,2),MID(A1,11,2)))

EXCEL 公式 身份證轉換性別

讓Excel根據身份證號自動填寫“性別”（男、女）

2007年10月20日 星期六 05:33 P.M.

每個單位一般都有“職工花名冊”，其中有“身份證號”和“性別”兩列資料，有沒有想過讓表格根據身份證號自動填寫性別呢?

方法如下：

在C4單元格中輸入“=IF(MOD(RIGHT(D4),2)=0,"女","男")”，以下單元格複製即可。

說明：根據目前我國居民身份證的編號規則，男的末尾數是奇數，女的末尾數是偶數。Mod()是求餘數函式，RIGHT()是擷取右側字串函式，上述Right(D4)也可寫為Right(d4,1)，即擷取D4單元格中資料的最右側一位。

A1存放身份證號碼.

則在B1獲取性別的公式為：=IF(MOD(LEFT(RIGHT(A1,(LEN(A1)=18)+1)),2),"男","女")這個18、15位的通用。居民身份證號碼是從事人事管理方面的人員經常接觸到的一種特殊資料，原為15位，在21世紀以後都統一升級為18位，其編碼規則按排列順序從左至右依次為：

15位：6位數字地址碼，6位數字出生日期碼，3位數字順序碼。

18位：6位數字地址碼，8位數字出生日期碼，3位數字順序碼和1位數字校驗碼。

下面將向用戶介紹在WPS表格中如何進行身份證資料的錄入和利用身份證號碼進行相關操作，包括以下內容：

1.身份證資料的錄入；　　　2.身份證號碼位數的轉換；

3.從身份證號碼中提取生日；4.從身份證號碼中提取性別；

1.身份證號碼的輸入

居民身份證號碼分別存在15位和18位數字組合（除部分身份證號碼以X結尾），那麼身份證號碼的錄入也經常使使用者產生困惑。因此提供以下兩種方法供使用者進行資料錄入：

以文字方式錄入：首先，設定單元格格式為“文字”，然後進行身份證號碼的錄入。

前置單撇號進行錄入：在輸入身份證號碼時，先輸入單撇號“'”，再輸入身份證號即可，如：在單元格中輸入：'123456199912310123。

注意：以上兩種方法同樣可以用於如：帳號、產品編號等長數值的資料錄入。

已經錄入資料的修改：

如果使用者直接輸入身份證號碼，可能使用者看到的是以科學計數法來顯示的數值，因為在WPS表格中，整數數值僅能保留15位有效數字，而且當用戶輸入超過11位數值時7a686964616fe59b9ee7ad9431333332613662，系統自動以科學計數法來進行顯示。

因此對於這種情況，分別有兩種辦法來解決：

1.已經輸入的15位身份證：使用者需要重新在單元格格式中進行自定義設定：0，身份證可以完整顯示。

2.已經輸入的18位身份證：由於整數數值僅能保留15位有效數字，系統除了顯示成科學計數法以外，原來錄入的資料系統只能保留前15位有效數字，其他3位數字以0顯示，資料精度已經丟失。因此這種資料只能重新錄入。 　　2.15位與18位身份證的相互轉換：

進入21世紀後，*局新頒發的居民身份證都是18位。某些時候使用者如果需要將15位身份證和18位身份證位數進行轉換（或進行校驗），可以使用以下兩種方法：

15位轉換為18位：

如：A2單元格為某一15位身份證號碼，B2的轉換的陣列公式為：

{=REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUM(MID(REPLACE(A2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)}

利用這個公式的原理，使用者還可以對18位身份證進行校驗，只需要將公式中“REPLACE(A2,7,,19)”修改為“LEFT(A2,17)”即可。　　18位轉換為15位:

如：A2單元格為某一18位身份證號碼，B2的轉換公式為：

=REPLACE(LEFT(A2,17),7,2,"")

3.從身份證號碼中提取資訊

人事部門經常需要從身份證號碼中提取相關的人員資訊，如：出生日期、性別等資訊。下面分別對這兩種資訊的提取進行介紹：

提取出生日期:

如：A2單元格為某一身份證號碼（18位或15位），下面的兩個公式將可以提取出文字型日期：

=TEXT(MID(A2,7,IF(LEN(A2)=15,6,8)),"0-00-00")

=TEXT(MID(A2,7,6+(LEN(A2)=15)*2),"0-00-00")

如果使用者希望將提取出來的資料轉換為真正的日期，則需要對資料進行轉換，常用的方法是：

=--TEXT(MID(A2,7,6+(LEN(A2)=15)*2),"0-00-00")

該公式的最前面加上“兩個負號”，在WPS表格中通常稱為“減負運算”，目的是將文字型資料轉換為數值（由於日期型資料為特殊的數值，使用者只需要設定單元格格式為日期格式即可）。

提取人員性別:

在身份證號碼中，身份證順序碼的最後一位數字的奇偶性可以識別使用者的性別，下面的公式將通過MOD函式取得使用者的性別。

如：A2單元格為某一身份證號碼（18位或15位），提取性別的公式為：

=IF(MOD(MID(A1,IF(LEN(A1)=15,15,17),1),2),"男","女")

=IF(MOD(RIGHT(LEFT(A1,17)),2),"男","女")

小結：

1.身份證號碼的輸入技巧，該技巧對錄入帳號、產品編號等長數值的資料同樣適用。

　方法1：先設定單元格格式為“文字”格式，再進行輸入；

　方法2：在輸入時，先輸入單撇號，再輸入身份證號碼；

2.使用者可以通過函式公式對15位和18位身份證號碼進行轉換和校驗。

3.使用者可以通過函式公式從身份證號碼中提取人員出生日期、人員性別等資訊。

適用範圍:

適用於WPS Office 2005及以上版本。　 希望採納，謝謝~~~　　　　

Excel表格中把身份證號碼轉變成性別

Function SFZ(cell As String, Options As String) As String    '身份證提取(DQ-地區,SR-出生年月,XB-性別)

    Application.Volatile

    Dim temp As String

    Options = UCase(Options)

    If cell = "" Then SFZ = "": Exit Function

    If Len(cell) <> 15 And Len(cell) <> 18 Then SFZ = "": Exit Function

    If Options = "" And Options <> "DQ" And Options <> "SR" And Options <> "XB" Then SFZ = "": Exit Function

    If Options = "DQ" Then

        temp = WorksheetFunction.VLookup(Left(cell, 2), ThisWorkbook.Sheets("身份證資料").Range("A1:B5919"), 2, False)

        temp = temp & "--" & WorksheetFunction.VLookup(Left(cell, 6), ThisWorkbook.Sheets("身份證資料").Range("A1:B5919"), 2, False)

        SFZ = temp

    End If

    If Options = "SR" Then

        If Len(cell) = 15 And Mid(cell, 7, 1) = 0 Then SFZ = "20" & Mid(cell, 7, 2) & "-" & Mid(cell, 9, 2) & "-" & Mid(cell, 11, 2)

        If Len(cell) = 15 And Mid(cell, 7, 1) > 0 Then SFZ = "19" & Mid(cell, 7, 2) & "-" & Mid(cell, 9, 2) & "-" & Mid(cell, 11, 2)

        If Len(cell) = 18 Then SFZ = Mid(cell, 7, 4) & "-" & Mid(cell, 11, 2) & "-" & Mid(cell, 13, 2)

    End If

    If Options = "NL" Then

        If Len(cell) = 15 And Mid(cell, 7, 1) = 0 Then temp = "20" & Mid(cell, 7, 2) & "-" & Mid(cell, 9, 2) & "-" & Mid(cell, 11, 2)

        If Len(cell) = 15 And Mid(cell, 7, 1) > 0 Then temp = "19" & Mid(cell, 7, 2) & "-" & Mid(cell, 9, 2) & "-" & Mid(cell, 11, 2)

        If Len(cell) = 18 Then temp = Mid(cell, 7, 4) & "-" & Mid(cell, 11, 2) & "-" & Mid(cell, 13, 2)

        SFZ = Year(Now()) - Year(temp)

        If SFZ = 0 Then

            SFZ = Month(Now()) - Month(temp) & "個月"

        End If

    End If

    If Options = "XB" Then SFZ = VBA.IIf((Mid(cell, 15, 3) Mod 2), "男", "女")

End Function

自定義函式e69da5e887aae799bee5baa6e79fa5e9819331333431353262 呼叫 

就是 

=sfz(a1,"XB") 提取性別

=sfz(a1,"SR") 提取出生年月

=sfz(a1,"NL") 提取年齡

excel中根據給定人身份證號碼求性別的函式

具體解決步驟如下：

1.有一組資料，最後一列是真實性別。

2.在函式框中輸入“=IF(MOD(MID(B2,17,1),2)=1,"男","女")”，按回車，可以百自動判斷並顯示對應身份證所屬者是“男”或者“女”。

(MID(B2,17,1)表示提取度B2單元格中第17位位置的字元；

MOD(MID(B2,17,1),2)表示求B2單元格中第17位位置的字元與2的餘數；

整個公式表示判斷B2單元格中的第17位位置的字元與2相除是否等於專1，如果等於1，即17位位置的字元為奇數，則判斷並顯示為屬“男”，如果不等於1，即17位位置的字元為偶數，則判斷並顯示為“女”。

3.按下回車鍵，得到結果。

4.下拉資料

5.完成

在EXCEL表格中身份證號如何自動提取性別和出生年月 詳細

1、首先開啟excel，我們看下面的表格是員工的姓名和身份證號碼。

2、在excel提取性別的單元格內輸入zhidao函式“=IF(MOD(MID(C2,17,1),2)=0,"女","男")。其中“專C2”為所提取身份證號的單元格，“17”代表提取資訊起始位置為第屬17位，“1”代表提取資訊共計1位。

3、在輸入框輸入函式後，點選回車鍵，身份證中的性別資訊就提取出來了。

4、在excel提取年月日的單元格內輸入函式“=TEXT(MID(C2,7,8),"0000-00-00")”。其中“C2”為所提取身份證號的單元格，“7”代表提取資訊起始位置為第7位，“8”代表提取資訊共計8位。

5、在輸入框輸入函式後，點選回車鍵，身份證中的年月日資訊就提取出來了。

6、在excel提取年月的單元格內輸入函式“=TEXT(MID(C2,7,6),"0000-00")”。其中“C2”為所提取身份證號的單元格，“7”代表提取資訊起始位置為第7位，“6”代表提取資訊共計6位。