Page 1 of 1
การตรวจสอบเลขประจำตัวประชาชน 13 หลัก
Posted: Mon Oct 05, 2015 9:52 am
by ฟงว่อง
เรียน ท่านสมาชิกทุกท่าน
ด้วยจำเป็นต้องขอความอนุเคราะห์ท่านสมาชิกเพื่อโปรดผูกสูตรการตรวจสอบเลขประจำตัวประชาชน 13 หลัก ซึ่งพิมพ์ในประกาศรายชื่อ
ผู้มีสิทธิสอบแข่งขันนั้นถูกต้อง หรือไม่ (เพื่อประมวลว่าในสดมภ์ไฟล์ข้อมูลแถบสีส้มนั้น เลขประจำตัวประชาชนทั้งครบ 13 หลัก และถูกต้องตามข้อเท็จจริงหรือไม่) อนึ่ง ท่านสามารถกำหนดหรือแทรกตารางเพื่อให้สูตรประมวลผลได้หรือเพื่อแสดงให้ทราบว่าเลขประจำตัวประชาชนที่กรอกลงไปนั้นไม่ถูกต้อง
เพื่อแก้ไข ก่อนประกาศรายชื่อผู้มีสิทธิ์สอบแข่งขัน และขอบพระคุณในความอนุเคราะห์มา ณ โอกาสนี้
ขอแสดงความนับถือ
Re: การตรวจสอบเลขประจำตัวประชาชน 13 หลัก
Posted: Mon Oct 05, 2015 11:14 am
by DhitiBank
สามารถตรวจสอบได้ว่าครบ 13 หลักหรือไม่ด้วยฟังก์ชัน LEN ครับ เช่น ที่ F5 คีย์
=IF(LEN(B5)<>13,"ไม่ครบ 13","")
แต่หากต้องการตรวจด้วยว่า เลขบัตรประชาชนที่พิมพ์ลงไปครบ 13 ตัวแล้ว มีอยู่ในผู้สมัครสอบหรือไม่ (เช่น พิมพ์เลขบัตรประชาชนครบ 13 หลัก แต่ตัวเลขบางตำแหน่งผิดจากนาย ก กลายเป็นนาย ข แต่นาย ข ไม่ได้สมัครสอบ) จะต้องมีฐานข้อมูลรายชื่อผู้สมัครสอบ ที่มีเลขบัตรประชาชนระบุไว้ด้วย แล้วเขียนสูตรให้ตรวจสอบกับฐานข้อมูลนี้ครับ
อย่างไรก็ตาม หากต้องการตรวจแค่ว่า คีย์ครบ 13 หลักหรือไม่ ใช้แค่สูตรด้านบนก็ได้ครับ
Re: การตรวจสอบเลขประจำตัวประชาชน 13 หลัก
Posted: Mon Oct 05, 2015 11:28 am
by ฟงว่อง
แล้วกรณีกรอกเลขประจำตัวประชาชนครบ 13 หลักแล้ว แต่ว่ากรอกครบทั้ง 13 หลักนั้นถูกหรือไม่ ตามหลักวิชาการหรือไม่ มีวิธีการผูกสูตรตรวจสอบหรือไม่ครับ
Re: การตรวจสอบเลขประจำตัวประชาชน 13 หลัก
Posted: Mon Oct 05, 2015 11:43 am
by DhitiBank
คุณฟงว่องหมายถึงว่า เลข 13 หลักนั้น ตรงกับเลขประจำตัวของผู้สมัครจริงๆ หรือเปล่าใช่ไหมครับ?
หากใช่ คุณฟงว่องก็ต้องทำฐานข้อมูลของผู้สมัครสอบเอาไว้อีกชีทหนึ่ง เช่น
Code: Select all
A B C
1 ลำดับ ชื่อ รหัสประจำตัวประชาชน
2 1 นาย ก 1234567890123
3 2 นาย ข 3210987654321
4 3 นาง ง 8475623908123
แล้วค่อยเขียนสูตรเพื่อตรวจว่า รหัสที่คีย์มีอยู่ในฐานข้อมูลหรือไม่ครับ
แต่เกรงว่าหากมีผู้สมัครสอบจำนวนมาก การมาไล่สร้างฐานข้อมูลแบบนี้อาจทำให้เกิดการผิดพลาดเพิ่มได้ (เพราะคีย์เลขในฐานข้อมูลผิด) แต่หากมีฐานข้อมูลที่สามารถคัดลอกมาได้อยู่แล้ว ผมว่าตรวจกับฐานข้อมูลเลยจะแน่นอนสุดครับ ซึ่งทำให้ไม่จำเป็นต้องตรวจว่าคีย์ครบ 13 หลักหรือไม่ แค่ตรวจว่า มี/ไม่มี ในฐานข้อมูลก็พอครับ
แต่หากผมเข้าใจผิด รบกวนคุณฟงว่องบอกวิธีที่ใช้อยู่หน่อยครับ ว่าปกติแล้วทำการตรวจอย่างไร
Re: การตรวจสอบเลขประจำตัวประชาชน 13 หลัก
Posted: Mon Oct 05, 2015 10:15 pm
by 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
Re: การตรวจสอบเลขประจำตัวประชาชน 13 หลัก
Posted: Mon Oct 05, 2015 11:14 pm
by 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 --> คัดลอกลงล่างครับ
Re: การตรวจสอบเลขประจำตัวประชาชน 13 หลัก
Posted: Mon Oct 05, 2015 11:34 pm
by snasui
Re: การตรวจสอบเลขประจำตัวประชาชน 13 หลัก
Posted: Mon Oct 05, 2015 11:50 pm
by DhitiBank
ขอบคุณครับอาจารย์
พอเห็นสูตรอาจารย์ก็เลยเพิ่งจะมาคิดว่า Row(Indirect("A1:A12")) นี่มันฟุ่มเฟือยจริงๆ หากระบุตำแหน่งเซลล์แบบนี้ก็ไม่ต้องใช้ Indirect ก็ได้นี่นา แถมเป็น volatile อีก
Re: การตรวจสอบเลขประจำตัวประชาชน 13 หลัก
Posted: Tue Oct 06, 2015 9:59 am
by ฟงว่อง
ขอบคุณในความอนุเคราะห์ของทุกท่านมา ณ โอกาสนี้
ขอแสดงความนับถือ
黄蜂
Re: การตรวจสอบเลขประจำตัวประชาชน 13 หลัก
Posted: Tue Oct 06, 2015 11:41 am
by 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 ครับ หากเวอร์ชั่นใหม่กว่านี้สูตรจะสั้นลง
Re: การตรวจสอบเลขประจำตัวประชาชน 13 หลัก
Posted: Tue Oct 06, 2015 3:27 pm
by DhitiBank
ขออภัยครับ หากเอาสูตรยาวๆ ด้านบนไปใส่ในไฟล์ .xls จะใช้ไม่ได้เพราะเกินข้อจำกัดของไฟล์สกุลนี้
ปรับมาให้ใหม่ ตามไฟล์แนบครับ
ดูตัวอย่างเพิ่มเติมที่นี่ครับ