במדריך זה נראה לכם כיצד ניתן לבדוק תקינות של מספרי תעודות זהות במהירות הבזק על ידי שימוש בנוסחת אקסל בודדה וכן על ידי פונקציית מותאמת אישית ב- VBA.

היתרון בשיטה המוצעת במדריך זה הוא שניתן להריץ את הנוסחה על מספר תעודת זהות אחד או על 500 מספרים בקלות באמצעות שימוש בנוסחה אוניברסלית. אין צורך באף כלי חיצוני. רק להעתיק ולהדביק. בנוסף – הנוסחה מסוגלת להתמודד עם מספרי תעודות זהות שאינם בעלי אורך סטנדרטי.

הבהרה: השימוש בפתרון המוצע בהמשך המדריך הוא על אחריותכם האישית בלבד. אין אנו נושאים באחריות על כל טעות, בעיה, אי דיוק שעלולים לנבוע כתוצאה ממדריך זה. 

הנוסחה לבדיקת תקינות תעודה הזהות:

כל שעליכם לעשות כדי לבדוק מספר תעודת זהות הוא להעתיק את הנוסחה “הקצרה” הבאה:

=MOD(MID(REPT(0,9-LEN(A2))&A2,1,1)+MID(“0246813579”,MID(REPT(0,9-LEN(A2))&A2,2,1)+1,1)+MID(REPT(0,9-LEN(A2))&A2,3,1)+MID(“0246813579”,MID(REPT(0,9-LEN(A2))&A2,4,1)+1,1)+MID(REPT(0,9-LEN(A2))&A2,5,1)+MID(“0246813579”,MID(REPT(0,9-LEN(A2))&A2,6,1)+1,1)+MID(REPT(0,9-LEN(A2))&A2,7,1)+MID(“0246813579”,MID(REPT(0,9-LEN(A2))&A2,8,1)+1,1)+MID(REPT(0,9-LEN(A2))&A2,9,1),10)=0

הדביקו אותה בקובץ האקסל בסמוך למספר תעודת הזהות שאתם מבקשים לבדוק. כעת, כל שנותר לעשות הוא לשנות את שם התא מ- A2 לתא שבו נמצאת תעודת הזהות. הקלידו CTRL+H על מנת לפתוח את חלון החיפוש והחלפה. בחלון זה, הקלידו ב”חיפוש” את הטקסט A2, והקלידו ב”החלפה” את שם התא שבו נמצא מספר תעודת הזהות שלכם.

קיבלתם TRUE – מזל טוב, תעודת הזהות תקינה. קיבלתם FALSE? כדאי שתבדקו שוב את המספר.

שימו לב – הנוסחה הנ”ל יודעת להתמודד גם עם מספרי תעודת זהות בעלי פחות מ- 9 ספרות, זאת על ידי הוספת אפסים בתחילת המספר.

הסבר על האלגוריתם שעומד מאחורי הנוסחה

לפני שנסביר מה הנוסחה עושה, בואו נבין את ההיגיון שמאחורי האלגוריתם של בדיקת תעודות הזהות בישראל.

לצורך ההדגמה, נשתמש במספר תעודת הזהות הבא: 924593221 (הבהרה – מדובר במספר פיקטיבי שהמצאנו לצורך ההדגמה. כדאי להתעכב על הפרט הזה, כדי להבין שעל אף שמדובר במספר תקין לחלוטין, הוא לא בהכרח שייך לאדם מסוים. האלגוריתם בסך הכל בודק שמדובר במספר תקני. קחו זאת בחשבון בבדיקה שלכם).

לאחר שהבהרנו זאת, בואו ניגש למשימה.

שלב 1 – נכתוב את כל הספרות – כל ספרה בתא נפרד. במידה ואין לנו 9 ספרות, נוסיף בתחילת המספר אפסים שישלימו ל- 9 ספרות.

שלב 2 – מתחת לכל ספרה במיקום אי זוגי (1,3,5,7,9) נרשום את הספרה 1. מתחת לכל ספרה במיקום זוגי (2,4,6,8) נרשום את הספרה 2 – אלו הן המשקולות שלנו.

שלב 3 – נבצע הכפלה פשוטה בין הספרה לבין המשקולת שלה (1 או 2, בהתאם למיקום)

שלב 4 – עבור כל תוצאה דו-ספרתית שהתקבלה בשלב 3, נכתוב את סכום הספרות (למשל 13 מורכב מ- 1 ו- 3, ולכן 1+3 = 4).

עבור תוצאת חד ספרתית, נשאיר את אותו מספר שהתקבל מקודם.

שלב 5 – נסכום את כל המספרים שקיבלנו בשלב 4

שלב 6 – נחלק את התוצאה ב- 10. אין שארית? סימן שהמספר תקין. יש שארית? המספר לא תקין!

ספרת הביקורת (הספרה האחרונה במספר תעודת הזהות) בעצם באה לוודא שהמספר תקין. אם הספרה אינה משלימה את התוצאה למספר שאינו מתחלק ב- 10 – סימן שיש בעיה עם המספר. 

כך זה נראה באקסל:

שימו לב – אנו מזמינים אתכם ללחוץ כאן על מנת להוריד את קובץ האקסל שעמו ביצענו את החישוב של אלגוריתם בדיקת תעודות הזהות שבדוגמא לעיל. כך תוכלו לבדוק את מספרי תעודות הזהות שלכם כדי להיווכח שהאלגוריתם עובד. תוכלו לראות בקובץ גם את הפתרון באמצעות נוסחה, וכן פתרון באמצעות מאקרו, כפי שיוצג בהמשך.

אז מה בעצם הנוסחה עושה?

על מנת “לפשט” את הנוסחה, נציג את הנוסחה ללא פונקציות REPT ו- LEN. תפקיד פונקציית REPT הוא להשלים את מספר ת.ז ל- 9 ספרות, במידה ונקלט מספר קצר יותר (כפי שקורה עם מספרי ת.ז ישנים). LEN פשוט מסייעת לנו להבין את אורך מספר תעודת הזהות המקורי.

הבה נתבונן בנוסחה “המקוצרת”:

=MOD(MID(A2,1,1)+MID(“0246813579”,MID(A2,2,1)+1,1)+MID(A2,3,1) +MID(“0246813579”,MID(A2,4,1)+1,1)+MID(A2,5,1)+MID(“0246813579”,MID(A2,6,1)+1,1)+MID(A2,7,1)+

MID(“0246813579”,MID(A2,8,1)+1,1)+MID(A2,9,1),10)=0

אם ננסה לתמצת את הנוסחה, ניתן להגיד שהנוסחה מבצעת סכימה של מכפלות הספרות בתעודת הזהות בהתאם לאלגוריתם – ספרות במיקום אי זוגי מוכפלות ב- 1, ואילו ספרות במיקום זוגי מוכפלות ב- 2 ובמידה וסכום שהתקבל עבור ספרה מסוימת הינו דו-ספרתי – 2 הספרות יחוברו לצורך קבלת ספרה אחת (שימו לב – הנוסחה אינה מבצעת למעשה שום מכפלה, אלא יודעת להחזיר באופן חכם את תוצאת ההכפלה של כל ספרה ב- 2 או 1, בהתאם למיקום הספרה). אם הסכום שהתקבל מתחלק ב- 10 ללא שארית – נקבל TRUE, כלומר מספר ת.ז תקין.

פשוט, לא? 🙂

שימוש בנוסחה בתוך קוד VBA

במידה ואתם מעוניינים להשתמש בנוסחה כחלק מקוד מאקרו שאתם כותבים, צירפנו עבורכם קוד VBA עבור פונקצייה מותאמת אישית בשם checkID שפועלת בדיוק כמו הנוסחה שהסברנו מקודם. להלן קטע הקוד של הפונקציה:

Function checkID(ID As String)
Dim ID_9 As String
ID_9 = WorksheetFunction.Rept(0, 9 – Len(ID)) & ID
If (CInt(Mid(ID_9, 1, 1)) + CInt(Mid(“0246813579”, Mid(ID_9, 2, 1) + 1, 1)) + CInt(Mid(ID_9, 3, 1) + CInt(Mid(“0246813579”, Mid(ID_9, 4, 1) + 1, 1))) _
+ CInt(Mid(ID_9, 5, 1)) + CInt(Mid(“0246813579”, Mid(ID_9, 6, 1) + 1, 1)) + CInt(Mid(ID_9, 7, 1) + CInt(Mid(“0246813579”, Mid(ID_9, 8, 1) + 1, 1))) _
+ CInt(Mid(ID_9, 9, 1))) Mod 10 = 0 Then
checkID = True
Else
checkID = False
End If
End Function

(הקוד נראה קצת מקוטע באתר. אל חשש. כאשר תדביקו אותו בעורך המאקרואים, הוא יוצג בצורה תקינה).

לנוחיותכם, צירפנו דוגמא לשימוש בקטע הקוד בקובץ ההדגמה של בדיקת תעודות הזהות. לחצו כאן להורדתו. אל תשכחו ללחוץ ENABLE CONTENT בעת פתיחת הקובץ על מנת שהמאקרו יעבוד!

קרדיטים והמלצות לקריאה נוספת

1. ברצוננו לציין שהנוסחה הכתובה במדריך זה מתבססת על נוסחה שהתפרסמה לראשונה בדיון בקישור המצורף. הנוסחה הנ”ל מוצגת כאן באדיבותם של אפרים קרויזר ומיכאל (מיקי) אבידן (מחבר מאמרי ויקי, מנחה דיון בפורומים מרובים ב- TechNet ו-Answers של ‘מיקרוסופט’ ומנהל פורום ‘אופיס’ ב’תפוז’. Microsoft® Excel MVP – Most Valuable Professional 2009-2018).
2. תודה לבלוג COMPFINDINGS על הרעיון לעשות שימוש בפונקציות REPT ו- LEN לצורך הפיכת הנוסחה שמישה גם עבור מספרי תעודת זהות בעלי פחות מ- 9 ספרות.
3. תודה לבלוג “חדר המידע” של משה “הלמו” הלוי על ההסבר על האלגוריתם לבדיקת תעודת זהות.
4. תודה מיוחדת לגיא דיאמנט על סיוע וייעוץ בכתיבת המדריך.
5. למתעניינים – מצורף מדריך כיצד לכתוב את אלגוריתם בדיקת תעודות הזהות בשפות JavaScript, PHP, ASP ו- C#
6. כמו כן – להלן מדריך נוסף שמסביר כיצד לכתוב את אלגוריתם בדיקת תעודות הזהות בשפות פסקל, רובי ופרל

זהו, עכשיו אתם מומחים לזיהוי תעודות זהות תקניות. נשמח לשמוע את דעתכם על המדריך וכמובן שתשתפו אותו עם חבריכם! 🙂