几百人电话、身份证 Excel 核验完整方案来自豆包

昨天做了表格，今早发现有个电话号码少了一位数字，当时登记的时候没有仔细检查，后期完善也未发现。今早同事导入发现，还是自己没有想到运用合适的方法检查核验。  询问豆包得到了这个方案，好的，下次记得使用公式检查。表格那就要配套使用表格的检查方法，譬如农民耕地，要想作物长得好有丰收，就得精耕细作。

重要提醒：Excel 公式只能校验格式、位数、身份证校验码，不能联网核验是否真实存在；如需公安库实人核验，要走官方接口。 先把身份证、手机号两列设置为【文本格式】，否则长数字会变成科学计数法，校验全部失效！

假设：

• B 列：身份证号（B2 开始）
• C 列：手机号（C2 开始） D 列放身份证校验，E 列放手机号校验

一、身份证批量校验（18 位二代证）

D2 粘贴下面公式，下拉填充，自动标记位数错误、校验码错误：

=IF(LEN(TRIM(B2))<>18,"位数错误",IF(MID("10X98765432",MOD(SUMPRODUCT(MID(TRIM(B2),ROW($1:$17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11)+1,1)=UPPER(RIGHT(TRIM(B2),1)),"✅正确","❌校验码错误"))

• 结果含义：位数错误 / 校验码错误 / ✅正确
• 原理：身份证最后一位是校验码，公式用前 17 位重算校验码对比，能查出输错数字的情况

二、手机号批量校验（国内 11 位）

E2 公式，下拉填充：

=IF(LEN(TRIM(C2))<>11,"❌位数不对",IF(ISNUMBER(--TRIM(C2)),"✅正常","❌含非数字"))

作用：检查是不是 11 位、有没有空格 / 字母等乱字符。

三、查重：找出重复身份证 / 重复手机号

1）标记重复身份证（F2）

=IF(COUNTIF($B$2:$B$500,B2)>1,"⚠重复","")

$B$2:$B$500 改成你实际数据区域

2）标记重复手机号（G2）

=IF(COUNTIF($C$2:$C$500,C2)>1,"⚠重复","")

操作：填充完公式后，打开筛选（Ctrl+Shift+L），直接筛选所有带❌⚠的行，集中修正。

四、进阶清洗（常见脏数据）

1. 清除前后空格
   ：TRIM() 上面公式已经自带，去除首尾空格；中间多余空格可用替换功能删掉
2. 清理换行符
   ：CLEAN()，公式可套入
3. 去空行
   ：筛选空白单元格，检查漏填
4. 条件格式标红
   ：选中 D:G 结果列 → 开始 → 条件格式，包含 “❌/⚠” 自动标红，一眼定位错误

五、两种核验级别区分（非常关键）

1. 格式核验（Excel 本地，免费，就是上面这套公式）
   ✔ 检查位数、非法字符、身份证校验码、重复号码 ❗ 不能验证这个人是不是真的持有该身份证，伪造的 18 位合规假证，公式识别不出来
2. 真实身份核验（实人核验）
   需要对接公安官方核验接口，需要资质，一般政务 / 企业采购使用，不在 Excel 本地完成。

六、推荐操作顺序（几百人最快流程）

1. 复制原始表做备份（防止改错）
2. B、C 列设置单元格格式为【文本】
3. 粘贴 4 个校验公式（身份证有效性、手机号格式、身份证重复、手机号重复）下拉
4. 筛选所有错误行，导出错误清单，分发核对修正
5. 修正完，重新跑一遍校验，直到没有❌