輸入身份證號自動顯示戶籍所在地,Excel身份證歸屬地識別

2020-12-15 傲來知趣

相信不少人力資源管理者都會頻繁的錄入人員身份信息，包括身份證號碼、歸屬地、出生日期和年齡等，在之前的文章裡我們講解了如何利用Excel自動提取身份證內的出生日期和計算年齡。今天給大家介紹Excel的又一實用功能，在Excel內實現輸入身份證號自動識別戶籍所在地功能。

實現該功能需要用到Excel格式的身份證歸屬地數據，將它單獨放在一個工作表中，我們給它命名為「資料庫」，沒有的話可以網上搜一下或者留言給我。

下面我們來看一下具體的操作步驟

第一步，如圖，我們創建一個名為「歸屬地查詢」的工作表（與「資料庫」工作表在同一個文件內），實現在「B3」一欄內輸入任意身份證號碼，自動在「B4」一欄內顯示相應的戶籍所在地。

第二步，直接在B4單元格輸入以下公式：「=IF(ISERROR(VLOOKUP(LEFT(B3,2),資料庫!A:B,2,)&VLOOKUP(LEFT(B3,4),資料庫!A:B,2,)&VLOOKUP(LEFT(B3,6),資料庫!A:B,2,)),"",IF(B3<>"",VLOOKUP(LEFT(B3,2),資料庫!A:B,2,)&VLOOKUP(LEFT(B3,4),資料庫!A:B,2,)&VLOOKUP(LEFT(B3,6),資料庫!A:B,2,),""))」

其中公式內的「資料庫!A:B」指的是「資料庫」工作表內的AB兩列的戶籍數據，「B3」指的是輸入身份證號的所在位置。這樣在B3單元格內輸入身份證號碼後就能在B4單元格內自動顯示相應的戶籍了。

第三步，上表適合單個身份證號的查詢，接下來我們將上面的公式運用到「人員名冊」工作表中，只需修改公式中身份證所在的單元格位置即可，在下面人員名冊表中我們的身份證號所在起始位置是C3，我們將上面公式中的所有「B3」全部改為"C3"，然後我們將改好的公式複製到「F3」單元格中在下拉複製即可。

如果再結合我們前一篇講過的出生日期和年齡的提取方法Excel根據身份證號提取出生日期並自動計算年齡，HR必備技能，出生日期和年齡也一同出來了！

相關焦點

  • Excel 2016如何通過身份證號識別戶籍歸屬地,其前六位數蘊含秘密
    中華人民共和國居民身份證是國家法定的證明公民個人身份的有效證件，18位身份證標準在國家質量技術監督局於1999年7月1日實施的GB11643-1999《公民身份號碼》中做了明確的規定，該標準規定了公民身份號碼的編碼對象、號碼的結構和表現形式，使每個編碼對象獲得一個唯一的、不變的法定號碼。公民身份號碼是特徵組合碼，由十七位數字本體碼和一位校驗碼組成。
  • excel輸入身份證號變成0 數字亂碼解決方法詳解
    excel輸入身份證號變成0 數字亂碼解決方法詳解時間：2017-03-22 08:29   來源：系統天堂   責任編輯：玲玲 川北在線核心提示：原標題：excel輸入身份證號變成0 數字亂碼解決方法詳解 excel輸入身份證號變成0怎麼辦呢?
  • excel輸入身份證號變成0怎辦 excel輸入數字亂碼問題解決方法
    excel輸入身份證號變成0怎麼辦呢?在使用excel時，很多童鞋都會碰到excel輸入數字亂碼的現象，特別是excel輸入身份證號變成0現象最為常見;下面小編就來教你怎麼辦。　　excel輸入身份證號變成0怎麼辦：　　究其原因，就是Excel目前最多支持15位數字，超過的數字全部默認為「0」，解決方法很簡單，將格式由「數值」改為「文本」。因為身份證號作為一串固定的數字並沒有運算需求，應該歸屬於規規矩矩的文本。
  • excel怎麼輸入身份證號
    Excel怎么正確的輸入身份證號在輸入身份證號時長會出現這樣的問題，因為數值大於15位，電腦自動轉化為科學計數法很不方便，因為我們要的就是18位的數字，那有沒有有沒有辦法可以保證可以正確的輸入身份證號號碼呢？
  • Excel輸入身份證號顯示錯誤怎麼辦?
    Excel輸入身份證號碼，會直接轉化為科學記數法，顯示成E+的形式，並且後三位直接變成了0.比如我們輸入身份證號碼370112199003079152，如下圖：回車確認之後顯示成了科學記數法的數字：3.70112E+17，再選中該單元格，身份證號碼的後三位已經變成「0」了，見下圖這個問題怎麼解決呢？
  • 辦公室文職工作基礎,電子表格excel中輸入身份證號你會幾種
    新從事財務人員和人力資源工作的朋友一開始可能都會遇到同樣一個問題，在電子表格Excel中輸入超過12位的數值後，電子表格本身系統會自動將其轉換為科學記數格式，輸入的數值超過15位的時候，16-18位數值會自動轉換為0，今天就說一下這個辦公室文職工作的基礎知識，希望對大家有所幫助。
  • Excel 2016如何通過身份證號驗證性別,兩種使用IF函數方法,試試
    關於中華人民共和國居民身份證，小編在百家號圖文《Excel 2016如何通過身份證號識別戶籍歸屬地，其前六位數蘊含秘密》中已介紹，不了解的小夥伴們可自行查閱。IF函數是指電子表格軟體中的條件函數，它按照指定的條件來判斷"真"(TRUE)、"假"(FALSE)，根據邏輯計算的真假值，從而返回相應的內容，可以使用IF函數對數值和公式進行條件檢測。
  • excel 根據身份證號碼自動提取出生年月、性別、年齡的方法實例
    下面以實例的方式講解如何根據身份證號碼自動提取出生年月、性別、年齡。首先，在excel中輸入以下表格，實例如下面所示。　　（身份證號碼的輸入方法見IT部落窩《excel如何輸入身份證號碼》文章介紹，網址是: http://www.ittribalwo.com/article/266.html）　　 A       
  • Excel輸入身份證號自動生成性別,詳解
    朋友們好，今天和大家分享Excel利用身份證號自動生成性別的函數，人員信息表是辦公人員經常接觸的表格，那麼自動生成性別，我們就節省了一部分時間，看下圖：用到的函數及效果大家都知道，身份證號裡面藏有玄機，其中倒數第二位可以判斷性別，奇數為男，偶數為女，朋友們可以看看自己的身份證號，我們只需要把倒數第二位提取出來，判斷是奇數還是偶數就可以了
  • 用條件格式對雙胞胎身份證號查重,excel錯的很明顯,難道是bug嗎
    最近又有一個小夥伴問到關於身份證號碼查重的問題，他發現一個比較奇怪的問題，兩個雙胞胎只有身份證號碼的最後一位不一樣，當使用條件格式對重複值進行突出顯示顏色的時候，這兩個身份證號被判定為重複(如下圖，魯班與劉備的身份證號碼，只有最後一位不一樣)，excel錯的這麼明顯難道是bug嗎？
  • 你的身份證號碼 就是納稅人識別號
    22日晚，國家稅務總局網站公布了三條與自然人納稅人識別號、個稅自行納稅申報、個人所得稅扣繳申報管理有關的公告和辦法。稅務總局明確，自2019年1月1日起，作為中國公民且持有有效居民身份證的，身份證號碼就是其納稅人識別號。在《關於自然人納稅人識別號有關事項的公告》中，稅務總局明確：自2019年1月1日起，自然人納稅人識別號，是自然人納稅人辦理各類涉稅事項的唯一代碼標識。
  • 如何在Excel中輸入18位身份證號?
    在公司、學校，經常會遇到需要在Excel錄入身份證號的場景，但錄入完畢會發現身份證號成了科學統計法，再次點擊發現最後三位成了「0」,這個如何解決呢？規規矩矩的輸入18位數字因為身份證號作為一串固定的數字並沒有運算需求，應該歸屬於規規矩矩的文本。方法一：先在表格中輸入英文半角的「』」號（冒號後面那個鍵），其作用就是將該表格項的屬性改為「文本」。
  • 用Excel快速提取身份證號的信息
    01身份證號中都隱藏了哪些信息？我們現在的身份證號有18位構成，每1位都有其特殊的含義：前6位表示地區碼：第1、2位表示：戶籍所在省份的代碼；第3、4位表示：戶籍所在地區(城市)的代碼；實現原理及如何用Excel驗證身份證號的正確性。
  • 你知道你的身份證號藏了多少小秘密嗎？
    身份證號我們經常都在用，但估計很多人都沒能準確說出其數字後面代表的真正含義。下面通過excel在人事工作中對身份證號的信息提取來說明其數字後面真正的含義。1、excel從身份證號提取籍貫。身份證號碼前6位表示的是籍貫地區，比如身份證前6位為「450521」，前面兩位「45」表示的是省，自治區或直轄市，中間兩位「05」表示的是所在地市，最後兩位「21」表示的是所在縣區，所以通過excel技巧，我們可以從身份證號提取出籍貫信息，如圖所示。公式：VLOOKUP(LEFT(B3,6),全國各省份戶籍行政區劃代碼表！
  • 身份證號還能這麼玩?利用Excel把身份證號玩出花
    【PConline 技巧】身份證號中蘊含著很多信息，比方說大家都知道的生日，便是其中一個最經典的案例。其實除此之外，還有一些數據也是日常經常會用到的。將這些信息快速提取出來，不但能讓工作更加高效，還能大大減少輸入出錯的機率。把身份證號碼玩出花的Excel小技巧1.
  • Excel文檔表格身份證號格式怎麼寫
    小編教你怎麼操作，Excel表格不僅方便統計數據，還能簡歷各種檔案，幫助人事、行政、運營等辦公人員大大提高工作效率，但是在統計人身份證號碼時如何在excel表格中將身份證的18位數字顯示出來。第1步：首先右鍵一個excel單元格格，選擇【設置單元格格式】。
  • Excel輸入身份證號後變成E+亂碼怎麼辦
    我們在使用EXCEL表格的時候經常會輸入身份證號碼或者其他的長數字，可是在輸入身份證號碼後不知道為什麼數字就變成了如下圖一樣的亂碼。我們稱之為E+。這是為什麼呢？要怎麼解決呢？為什麼輸入數字後會變成這樣的E+亂碼呢，那是因為我們輸入的數字過大，EXCEL就把這個數字自動換成科學計數方式了。避免出現這樣的科學計數發也非常簡單，今天就給大家說說最簡單可行的兩種方法。
  • 全國戶籍查詢系統身份證戶籍地址查詢
    2013/9/4 10:00:57   來源：搜房網     　　1.身份證號碼戶籍查詢系統姓名免費查詢的身份證號碼查詢歸屬地數據為本站所有
  • excel新手教程,教你如何設置excel表格中輸入身份證錯誤時提示
    hello大家好，今天繼續跟大家分享excel實用小技巧，在工作中有些時候需要別人輸入自己的身份證號碼，不過因為都是手動輸入，難免會出現身份證位數輸錯的情況，每個人的身份證號碼都是18位，如果輸入的時候因為某些原因少輸一位數，如何讓excel提示警告減小出錯率呢？
  • 分享下excel身份證號碼格式顯示方法,方便小妙招
    分享下excel身份證號碼格式顯示方法讓希望的種子生根發下，頂起一片天。獨白：一個簡單的操作，一份真誠的分享，現在分享下excel身份證號碼格式顯示方法的操作過程和技巧分享給大家，過程簡單看圖就會做，教程是自己原創的，其他分享平臺估計也能看到。