有时候在工作中我们需要校验身份证号码是不是正确的,如果只做一次,那么用Excel公式来校验即可;如果需要经常校验,那么我建议用PowerQuery来校验。
校验其实很简单:
如果身份证号码不足18位,那么标记出来(也可以通过身份证校验公式自动补齐18位,在这篇文章中我没涉及这个主题);如果身份证号码是18位的,那么需要通过校验公式去校验它是不是乱编的。
我们要做的只是把身份证校验公式搬到PowerQuery中而已。
下面以我随便写的两个虚拟身份证作为例子来演示如何用PowerQuery校验身份证。
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicmbw5CO1cjMyUDO2IGNzEDN2UTZ5EDZklTMlRWMjVDMmJGMw8CX0JXZ252bj91Ztl2Lc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
第一步:引入来自Excel区域的数据源。数据是我随手编的示例数据。
= Excel.CurrentWorkbook(){[Name="表1"]}[Content]
编辑搜图
请点击输入图片描述
第二步:标记不满18位的号码。
= Table.AddColumn(源, "标记不足18位的身份证号", each
if
Text.Length([号码])<>18
then
"Renew"
else
[号码])
第三步:计算18位号码的余数。
= Table.AddColumn(标记不足18位的身份证号, "计算余数", each
if
[标记不足18位的身份证号]="Renew"
then
"Renew"
else
Number.Mod((Number.FromText(Text.At([号码],0))*7
+Number.FromText(Text.At([号码],1))*9
+Number.FromText(Text.At([号码],2))*10
+Number.FromText(Text.At([号码],3))*5
+Number.FromText(Text.At([号码],4))*8
+Number.FromText(Text.At([号码],5))*4
+Number.FromText(Text.At([号码],6))*2
+Number.FromText(Text.At([号码],7))*1
+Number.FromText(Text.At([号码],8))*6
+Number.FromText(Text.At([号码],9))*3
+Number.FromText(Text.At([号码],10))*7
+Number.FromText(Text.At([号码],11))*9
+Number.FromText(Text.At([号码],12))*10
+Number.FromText(Text.At([号码],13))*5
+Number.FromText(Text.At([号码],14))*8
+Number.FromText(Text.At([号码],15))*4
+Number.FromText(Text.At([号码],16))*2),11))
第四步:计算校验码。
= Table.AddColumn(计算余数, "校验码", each
if
[计算余数]="Renew"
then
"Renew"
else
if [计算余数]=0 then 1 else
if [计算余数]=1 then 0 else
if [计算余数]=2 then "X" else
if [计算余数]=3 then 9 else
if [计算余数]=4 then 8 else
if [计算余数]=5 then 7 else
if [计算余数]=6 then 6 else
if [计算余数]=7 then 5 else
if [计算余数]=8 then 4 else
if [计算余数]=9 then 3 else 2)
第五步:更改校验码的格式,便于下一步校验。
= Table.TransformColumnTypes(校验码,{{"校验码", type text}})
第六步:进行校验。把通过校验的标记为"Y",表示号码是OK的。如果不OK则标记为需要更新。
= Table.AddColumn(更改的类型1, "比对校验码", each
if[校验码]="Renew" then "Renew" else
if Text.Upper(Text.At([号码],17))=[校验码] then "Y" else "Renew")
至此,比对完成,将结果加载至Excel的sheet。今后有新的号码要校验时,只需要覆盖原始文件,然后点击【数据】选项卡上的【刷新】按钮即可。
加载:
刷新:
全部代码: