網友問到:如何利用 Excel 來設計驗證「統一編號」的公式?

參考下圖,其規則如下:

(1) 取出統一編號的每一個位元數字。

(2) 由最高位至最低依序乘以:1, 2, 1, 2, 1, 2, 4, 1

(3) 取出第(2)式相乘後的個位數。

(4) 取出第(2)式相乘後的十位數。

(5) 將個位數和十位數相加。

(6) 將第(5)式相加後的結果除以10,結果如果整除,則該數為正確統一編號,否則為錯誤編號。

Excel-統一編號驗證(INT,MOD,MID)

 

【公式設計與解析】

儲存格B7:{=IF(MOD(SUM(INT(MID($A1,ROW(1:8),1)*{1;2;1;2;1;2;4;1}/10)
+MOD(MID($A1,ROW(1:8),1)*{1;2;1;2;1;2;4;1},10)),10),"錯誤編號","正確編號")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{ }」。

(1) MID($A1,ROW(1:8),1)

在陣列公式,利用 MID 函數依序取出儲存格A1裡第1個至第8個字元。

本例得到:4,5,1,1,3,9,1,6

(2) MID($A1,ROW(1:8),1)*{1;2;1;2;1;2;4;1}

將第(1)式取出的8個字元依序乘以:1, 2, 1, 2, 1, 2, 4, 1。

注意陣列的寫法:{1;2;1;2;1;2;4;1},數字以「;」分隔。

(3) INT(第(2)式/10)

在陣列公式中,將第(2)式傳回的8個數字分別除以10,利用 INT 函數取出「商數」。(即為十位數),本例得到:0,1,0,0,0,1,0,0

(4) MOD(第(2)式,10)

在陣列公式中,將第(2)式傳回的8個數字分別除以10,利用 MOD 函數取出「餘數」。(即為個位數),本例得到:4,0,1,2,3,8,4,6

(5) SUM(第(3)式,第(4)式)

在陣列公式中,第(3)式和第(4)式相加,本例得到:4,1,1,2,3,9,4,6加總後為30。

(6) IF(MOD(第(5)式,10),"錯誤編號","正確編號")

利用 MOD 函數將第(5)式傳回值除以10取餘數,本例結果為0。

最後傳回:正確編號

比較特別的是,如果倒數第2位如果是7,並且十位數和個位數相加再除以10,不管餘數為0或是1,結果都是正確的。

Excel-統一編號驗證(INT,MOD,MID)

學不完.教不停.用不盡文章列表

arrow
arrow

    vincent 發表在 痞客邦 留言(0) 人氣()