有時候在工作中我們需要校驗身份證号碼是不是正确的，如果隻做一次，那麼用Excel公式來校驗即可；如果需要經常校驗，那麼我建議用PowerQuery來校驗。
校驗其實很簡單：
如果身份證号碼不足18位，那麼标記出來(也可以通過身份證校驗公式自動補齊18位，在這篇文章中我沒涉及這個主題)；如果身份證号碼是18位的，那麼需要通過校驗公式去校驗它是不是亂編的。
我們要做的隻是把身份證校驗公式搬到PowerQuery中而已。
下面以我随便寫的兩個虛拟身份證作為例子來示範如何用PowerQuery校驗身份證。
第一步：引入來自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。今後有新的号碼要校驗時，隻需要覆寫原始檔案，然後點選【資料】頁籤上的【重新整理】按鈕即可。
加載：
重新整理：
全部代碼：