การตรวจสอบเลขประจำตัวประชาชน 13 หลัก

[ฟงว่อง]

เรียน ท่านสมาชิกทุกท่าน
ด้วยจำเป็นต้องขอความอนุเคราะห์ท่านสมาชิกเพื่อโปรดผูกสูตรการตรวจสอบเลขประจำตัวประชาชน 13 หลัก ซึ่งพิมพ์ในประกาศรายชื่อ
ผู้มีสิทธิสอบแข่งขันนั้นถูกต้อง หรือไม่ (เพื่อประมวลว่าในสดมภ์ไฟล์ข้อมูลแถบสีส้มนั้น เลขประจำตัวประชาชนทั้งครบ 13 หลัก และถูกต้องตามข้อเท็จจริงหรือไม่) อนึ่ง ท่านสามารถกำหนดหรือแทรกตารางเพื่อให้สูตรประมวลผลได้หรือเพื่อแสดงให้ทราบว่าเลขประจำตัวประชาชนที่กรอกลงไปนั้นไม่ถูกต้อง
เพื่อแก้ไข ก่อนประกาศรายชื่อผู้มีสิทธิ์สอบแข่งขัน และขอบพระคุณในความอนุเคราะห์มา ณ โอกาสนี้
ขอแสดงความนับถือ

[DhitiBank]

สามารถตรวจสอบได้ว่าครบ 13 หลักหรือไม่ด้วยฟังก์ชัน LEN ครับ เช่น ที่ F5 คีย์
=IF(LEN(B5)<>13,"ไม่ครบ 13","")

แต่หากต้องการตรวจด้วยว่า เลขบัตรประชาชนที่พิมพ์ลงไปครบ 13 ตัวแล้ว มีอยู่ในผู้สมัครสอบหรือไม่ (เช่น พิมพ์เลขบัตรประชาชนครบ 13 หลัก แต่ตัวเลขบางตำแหน่งผิดจากนาย ก กลายเป็นนาย ข แต่นาย ข ไม่ได้สมัครสอบ) จะต้องมีฐานข้อมูลรายชื่อผู้สมัครสอบ ที่มีเลขบัตรประชาชนระบุไว้ด้วย แล้วเขียนสูตรให้ตรวจสอบกับฐานข้อมูลนี้ครับ

อย่างไรก็ตาม หากต้องการตรวจแค่ว่า คีย์ครบ 13 หลักหรือไม่ ใช้แค่สูตรด้านบนก็ได้ครับ

[ฟงว่อง]

แล้วกรณีกรอกเลขประจำตัวประชาชนครบ 13 หลักแล้ว แต่ว่ากรอกครบทั้ง 13 หลักนั้นถูกหรือไม่ ตามหลักวิชาการหรือไม่ มีวิธีการผูกสูตรตรวจสอบหรือไม่ครับ

[DhitiBank]

คุณฟงว่องหมายถึงว่า เลข 13 หลักนั้น ตรงกับเลขประจำตัวของผู้สมัครจริงๆ หรือเปล่าใช่ไหมครับ?

หากใช่ คุณฟงว่องก็ต้องทำฐานข้อมูลของผู้สมัครสอบเอาไว้อีกชีทหนึ่ง เช่น

Code: Select all

     A       B                    C
1    ลำดับ    ชื่อ                  รหัสประจำตัวประชาชน
2    1       นาย ก                1234567890123
3    2       นาย ข                3210987654321
4    3       นาง ง                8475623908123

แล้วค่อยเขียนสูตรเพื่อตรวจว่า รหัสที่คีย์มีอยู่ในฐานข้อมูลหรือไม่ครับ

แต่เกรงว่าหากมีผู้สมัครสอบจำนวนมาก การมาไล่สร้างฐานข้อมูลแบบนี้อาจทำให้เกิดการผิดพลาดเพิ่มได้ (เพราะคีย์เลขในฐานข้อมูลผิด) แต่หากมีฐานข้อมูลที่สามารถคัดลอกมาได้อยู่แล้ว ผมว่าตรวจกับฐานข้อมูลเลยจะแน่นอนสุดครับ ซึ่งทำให้ไม่จำเป็นต้องตรวจว่าคีย์ครบ 13 หลักหรือไม่ แค่ตรวจว่า มี/ไม่มี ในฐานข้อมูลก็พอครับ

แต่หากผมเข้าใจผิด รบกวนคุณฟงว่องบอกวิธีที่ใช้อยู่หน่อยครับ ว่าปกติแล้วทำการตรวจอย่างไร

[ZEROV]

การป้อนข้อมูลเลขที่บัตรประจำตัวประชาชนทำได้หลายแบบ เช่น
"0000000000000"
"0 0000 00000 00 0"
"0-0000-00000-00-0"
ถ้าข้อมูลมาจากหลายแหล่งและรูปแบบไม่เหมือนกันทำให้ยากต่อการใช้งาน
ใช้ฟังชั่นนี้ตรวจสอบและปรับแก้ได้ครับ

Code: Select all

Function IDCheck(IDNum As Variant, Optional ByVal ResultType As Byte) As Variant
Dim Text As String
For i = 1 To Len(IDNum)
If InStr("0123456789", Mid(IDNum, i, 1)) > 0 Then Text = Text & Mid(IDNum, i, 1)
Next i
If Len(Text) <> 13 Then
IDCheck = False
Exit Function
End If
For i = 1 To 12
sum = sum + Mid(Text, i, 1) * (14 - i)
Next
N = 11 - (sum Mod 11)
IDCheck = Right(N, 1) = Right(Text, 1)
Select Case ResultType
Case 1: IDCheck = IIf(IDCheck, Val(Text), False)
Case 2: IDCheck = IIf(IDCheck, Format(Val(Text), "0 0000 00000 00 0"), False)
Case 3: IDCheck = IIf(IDCheck, Format(Val(Text), "0-0000-00000-00-0"), False)
 End Select
End Function

[DhitiBank]

อ๋อ ขอบคุณคุณ ZEROV มากครับ ผมไม่รู้มาก่อนว่าตำแหน่งที่ 13 นั่นคือ Check Digit เพิ่งมาอ่านจากที่นี่
Check Digit

หากเป็นการตรวจสอบแบบนี้และเวลาคีย์จะคีย์แค่ตัวเลขอย่างเดียว (หากคีย์หลายรูปแบบ ควรใช้ VBA ที่คุณ ZEROV แนะนำ) ลองใช้สูตรได้อีกแบบหนึ่งครับ เช่น ที่ F5 คีย์

=IF(ISERR(SUMPRODUCT(--MID(B5,ROW(INDIRECT("A1:A12")),1),{13;12;11;10;9;8;7;6;5;4;3;2})),"Invalid ID",IF(MOD(11-MOD(SUMPRODUCT(--MID(B5,ROW(INDIRECT("A1:A12")),1),{13;12;11;10;9;8;7;6;5;4;3;2}),11),10)&""<>RIGHT(B5,1),"Invalid ID",""))

Enter --> คัดลอกลงล่างครับ

[snasui]

ดูตัวอย่างเพิ่มเติมที่นี่ครับ http://www.snasui.com/viewtopic.php?t=6968

[DhitiBank]

ขอบคุณครับอาจารย์
พอเห็นสูตรอาจารย์ก็เลยเพิ่งจะมาคิดว่า Row(Indirect("A1:A12")) นี่มันฟุ่มเฟือยจริงๆ หากระบุตำแหน่งเซลล์แบบนี้ก็ไม่ต้องใช้ Indirect ก็ได้นี่นา แถมเป็น volatile อีก

[ฟงว่อง]

ขอบคุณในความอนุเคราะห์ของทุกท่านมา ณ โอกาสนี้
ขอแสดงความนับถือ
黄蜂

[DhitiBank]

แถมครับ

หากรหัสบัตรประชาชนไม่ได้คีย์แค่ตัวเลขอย่างเดียว แต่มี "-", "." หรือ เครื่องหมายอะไรก็แล้วแต่คั่นอยู่ อาจใช้สูตรนี้ได้ครับ สมมติว่าคีย์รหัส id ในเซลล์ A1

=IF(ISERR(SUMPRODUCT(LOOKUP(SMALL(IF(ISNUMBER(--MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)),ROW(INDIRECT("A1:A"&LEN(A1)))),ROW($A$1:$A$12)),ROW(INDIRECT("A1:A"&LEN(A1))),--MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)),{13;12;11;10;9;8;7;6;5;4;3;2})),"Invalid",
IF(MOD(11-MOD(SUMPRODUCT(LOOKUP(SMALL(IF(ISNUMBER(--MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)),ROW(INDIRECT("A1:A"&LEN(A1)))),ROW($A$1:$A$12)),ROW(INDIRECT("A1:A"&LEN(A1))),--MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)),{13;12;11;10;9;8;7;6;5;4;3;2}),11),10)<>LOOKUP(2,1/ISNUMBER(--MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)),--MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)),"Invalid",""))

กด Ctrl+Shift ค้างเอาไว้ แล้วกด Enter ครับ

ใช้ได้กับ excel 2003 ครับ หากเวอร์ชั่นใหม่กว่านี้สูตรจะสั้นลง

[DhitiBank]

ขออภัยครับ หากเอาสูตรยาวๆ ด้านบนไปใส่ในไฟล์ .xls จะใช้ไม่ได้เพราะเกินข้อจำกัดของไฟล์สกุลนี้

ปรับมาให้ใหม่ ตามไฟล์แนบครับ