EXCEL:从身份证号到地域信息
- 2026-03-22 15:35:42
EXCEL:从身份证号到地域信息整理数据时，不少人会卡在“身份证提取生日”“地域编码拆分省市区县”两大难题上。其实我的《地域.xlsx》表格中，早已备好适配的极简公式——既能快速拆解身份证出生日期，又能凭地域编码精准匹配省、市、区县，今天一次性讲透，新手也能直接照搬使用。 提取身份证出生日期的核心，是抓住18位身份证的编码规律，我表格里的公式无需死记参数，上手就能用。 拆解逻辑：18位身份证第7-14位为“YYYYMMDD”格式的出生日期（如“19900101”），MID(B2,7,8)函数提取这8个字符，再通过TEXT函数转换为“年/月/日”的清晰格式。 实际效果：若B2单元格为身份证“110101199001011234”，公式直接输出“1990/01/01”，下拉填充3秒就能搞定数百行数据。 ① 先设文本格式：右键选中身份证列→“设置单元格格式”→“文本”，避免前导“0”丢失（如“010101...”误变为“10101...”）。 ② 加容错防报错：将公式改为 =IFERROR(TEXT(MID(B2,7,8),"yyyy/mm/dd"),"身份证号错误")，无效身份证号会显示“身份证号错误”，杜绝满屏#VALUE!报错。 先明确核心规律：标准6位地域编码遵循“前2位省、中间2位市、后2位区县”的分层规则，举例如下： 编码“110101”：前2位“11”对应北京市（省），中间2位“01”对应市辖区（市），后2位“01”对应东城区（区县）； 编码“310104”：前2位“31”对应上海市（省），中间2位“01”对应市辖区（市），后2位“04”对应徐汇区（区县）。 假设表格A列为6位地域编码，需在B列填省份、C列填城市、D列填区县，直接套用《地域.xlsx》适配公式： 1. 匹配省份（B列）： =IFERROR(XLOOKUP(LEFT(A2,2)&"*",编码表!A:A,编码表!B:B,"未匹配"),"") 逻辑：提取A列编码前2位（省级编码），在“编码表”A列模糊匹配（“*”兼容不同长度编码），返回对应B列省份名称。效果：A2为“110101”，B2自动显示“北京市”。 2. 匹配城市（C列）： =IFERROR(XLOOKUP(LEFT(A2,4)&"*",编码表!A:A,编码表!C:C,"未匹配"),"") 逻辑：提取A列编码前4位（省市联合编码），匹配“编码表”后返回C列城市名称。效果：A2为“110101”，C2自动显示“市辖区”。 3. 匹配区县（D列）： =IFERROR(XLOOKUP(A2,编码表!A:A,编码表!D:D,"未匹配"),"") 逻辑：用完整6位编码精准匹配“编码表”，返回D列区县名称。效果：A2为“110101”，D2自动显示“东城区”。 上述公式能生效，全依赖“编码表”这个核心数据库。我的《地域.xlsx》中，专门有一张命名为“编码表”的工作表，存储“地域编码-省-市-区县”的对应关系，核心结构如下：
① 表格名称要对应：公式中的“编码表！A:A”需与实际工作表名称一致，若表格名为“地域编码库”，需将公式中的“编码表”替换为“地域编码库”，确保引用正确。 ② 禁止删除空行：编码表需保持“编码-省-市-区县”的对应结构，切勿随意删行，否则会导致公式匹配出错。 若表格同时包含身份证号和地域编码，组合使用上述公式，可一次性完成“生日+省+市+区县”全维度拆分： 3. 依次套用城市、区县公式，下拉填充整列，瞬间完成全表数据整理，无需来回切换表格查询。 《地域.xlsx》中的公式，核心亮点的是摒弃复杂逻辑，直击数据拆分的核心规律：身份证拆生日盯准第7-14位，用短公式快速提取；地域编码拆分依托“前2省、中2市、后2区县”规则，用XLOOKUP精准匹配。 新手无需记忆复杂参数，复制公式后微调单元格引用即可使用，效率比手动整理提升10倍。下次遇到同类数据整理需求，直接套用公式，告别熬夜翻编码表、逐行拆数据的麻烦。 后台回复01赠送行政编码大全
一、身份证拆生日：表格短公式，复制即用
1. 核心公式（直接复制）
=TEXT(MID(B2,7,8),"yyyy/mm/dd")
2. 新手避坑技巧
二、地域编码拆省市区：表格匹配逻辑，精准拆分
3个公式，拆分省、市、区县（直接复制）
三、核心前提：“编码表”的正确用法
地域编码（A列） | 省份（B列） | 城市（C列） | 区县（D列） |
|---|---|---|---|
110000 | 北京市 | 北京市 | - |
110100 | 北京市 | 市辖区 | - |
110101 | 北京市 | 市辖区 | 东城区 |
310000 | 上海市 | 上海市 | - |
310104 | 上海市 | 市辖区 | 徐汇区 |
使用编码表的2个关键注意点
四、实战应用：一键搞定身份证+地域信息拆分
拆生日（B列为身份证）：=TEXT(MID(B2,7,8),"yyyy/mm/dd") 拆省份（A列为地域编码）：=IFERROR(XLOOKUP(LEFT(A2,2)&"*",编码表!A:A,编码表!B:B,"未匹配"),"")
五、总结：公式的核心优势——精准适配场景
本文来自网友投稿或网络内容，如有侵犯您的权益请联系我们删除，联系邮箱：wyl860211@qq.com 。
