בדיקת תקינות של תעודת זהות באקסל

[yossiz]

@y0527643446 כתב בבדיקת תקינות של תעודת זהות באקסל:

אני רוצה את הולידציה על עמודה שלמה
של הזנת תעודת זהות ושלא תופיע תשובה אלא פשוט תקפוץ הודעת שגיאה
כמו ב-Data Validation

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

@y0527643446 כתב בבדיקת תקינות של תעודת זהות באקסל:

אבל זה מוזר כי ב-Data Validation אפשר לשים נוסחאות כמו במאקרו

נוסחת אקסל זה לא קוד. אי אפשר להכניס לולידציה קוד VBA, אאל״ט

[y0527643446]

@yossiz
ראיתי ל-Data Validation יש מגבלה של 255 תווים
ולכן לא הצלחתי,
יש מישהי\ו שיצא להם להתגבר על המגבלה הזאת?

[OdedDvir]

@y0527643446 אפשר להתמודד עם המגבלה הזו, בתנאי שתדאגו מראש לקיים את התנאים הבאים:

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

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

=MOD(MID(A1,1,1)+MID("0246813579",MID(A1,2,1)+1,1)+MID(A1,3,1)+MID("0246813579",MID(A1,4,1)+1,1)+MID(A1,5,1)+MID("0246813579",MID(A1,6,1)+1,1)+MID(A1,7,1)+MID("0246813579",MID(A1,8,1)+1,1)+MID(A1,9,1),10)=0

• הערה: הנוסחא לעיל היא להגדרה של אימות נתונים על תא A1, אח"כ ניתן לגרור לתאים אחרים.
  מצ"ב צילום מסך לדוגמא:
  
  קובץ להורדה: בדיקת תקינות תעודת זהות.xlsx

[y0527643446]

@OdedDvir
תודה על התשובה המפורטת והברורה.
השדה שלי באמת מסוג טקסט
אבל בגלל שמשתמש מכניס את הת"ז
אני לא יכולה להגביל אותו להכניס כולל 0
בהתחלה ..
ז"א שאני לא אוכל לקצר את הנוסחה?

[dovid]

@OdedDvir מה רע בפתרון של @yossiz ?

[OdedDvir]

@dovid כתב בבדיקת תקינות של תעודת זהות באקסל:

@OdedDvir מה רע בפתרון של @yossiz ?

שום דבר לא רע, מסתמא כך הייתי עושה.
פשוט איתגרה אותי השאלה האם ניתן להתמודד עם המגבלה של 255 תוים...

[dovid]

אז @y0527643446, לתשומת לבך, @yossiz הציע לך פתרון טוב (עמודה נסתרת עם הנוסחה בכל התאים לצד התאים הפוטנציאליים).
אני סבור שלא הקדשת מספיק בשביל להבין את ההודעות, כי מהתשובות שלך להצעה נראה שהחמצת אותה.

[OdedDvir]

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

=LET(N,REPT(0,9-LEN(A1))&A1,LET(P,"0246813579",MOD(MID(N,1,1)+MID(P,MID(N,2,1)+1,1)+MID(N,3,1)+MID(P,MID(N,4,1)+1,1)+MID(N,5,1)+MID(P,MID(N,6,1)+1,1)+MID(N,7,1)+MID(P,MID(N,8,1)+1,1)+MID(N,9,1),10)=0))

מצורף קובץ משופר: בדיקת תקינות תעודת זהות.xlsx

[אף אחד 3]

@OdedDvir אתה בטוח שזה הקובץ הנכון ? אני לא רואה שם שום דבר מיוחד

[OdedDvir]

@אף-אחד-3 נסה להכניס בטור A מספר ת.ז. לא תקין

[אף אחד 3]

@OdedDvir אה אוקיי פשוט אני ממש בור באקסל אז ישר חיפשתי נוסחה
איפה באמת התנאי נמצא ?

[y0527643446]

@OdedDvir
תודה על הרצון והידע
אני פשוט ניסיתי גם להכניס בעמודה A ולא עשה לי שגיאה בת"ז לא תרינה וראיתי שאני מנסה לערוך את הולידציה יש לי כזו הודעת שגיאה:

אולי הקובץ לא עלה טוב?
שוב תודה על הכל..

[OdedDvir]

@y0527643446 @אף-אחד-3 התנאי מוגדר באימות נתונים.
כדי להציג אותו, יש לבחור את התא A1 ומהתפריט "אימות נתונים":

הנוסחא תופיע בחלון שיפתח:

[y0527643446]

@OdedDvir
לא הצלחתי להשתמש בנוסחה כי אני מקבלת את ההודעת שגיאה שצירפתי בניסיון לשמור את הנוסחה.

[OdedDvir]

@y0527643446 יתכן וגירסת האקסל שלכם אינה תומכת ב- LET.
דרישת המינימום היא גירסת אקסל 365 או 2021.

[y0527643446]

@OdedDvir
נכון באמת ראיתי שהאקסל שלי בגירסה ישנה יותר
חבל..
בכל אופן ממש תודה

[y0527643446]

@dovid @yossiz
ניסיתי לעשות כמו התשובה שלך והבעיה ש-Data Validatin
לא עובד על שדה מחושב.
ואפשר לעקוף את זה רק ע"י מאקרו.
אולי פעם הצלחת לעשות -Data Validatin על שדה מחושב?

[Y.Excel.Access]

@OdedDvir כתב בבדיקת תקינות של תעודת זהות באקסל:

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

באמת חידוש!
וכבר ניסיתי ליצור את זה פעם... (https://tchumim.com/topic/13565/איך-ליצור-משתנה-גלובלי-ב-google-sheets/5)
לצערי אין את זה בSHEETS. (לפחות הם כן הוסיפו את זה מאקסל ואולי יש למה לחכות... אבל זה לא הנושא של הפוסט כרגע, אולי אפתח חדש).

[צדיק תמים]

@Y-Excel-Access כתב בבדיקת תקינות של תעודת זהות באקסל:

וכבר ניסיתי ליצור את זה פעם... (https://tchumim.com/topic/13565/איך-ליצור-משתנה-גלובלי-ב-google-sheets/5)

ובמה נתקעת? הרי @dovid הביא לך פתרון מצוין - PropertiesService
עם מערכים וכדו' ניתן להתמודד בקלות ע"י JSON.stringify&JSON.parse (לפני השמירה ממירים לסטרינג ע"י stringify, ובשליפה מפרסרים חזרה ע"י parse)

[Y.Excel.Access]

@צדיק-תמים באקסל זה היה מצוין,
אבל בשיטס ראיתי שלעבוד עם UDF זה פחות מעשי.
בכל פונקציית UDF את ה LOADING שגורם לחכות עד שזה כבר לא רווחי. וזה די משמעותי כי בכל פתיחה של המשתמש זה משהה מחדש, וגם זה משהה את הפונקציות המפנות אליה ובפרט אם יש עוד פונקצית UDF שמפנה אליה שאז זמן ההשהייה מוכפל...
ספציפית אני זוכר גם שיש לאחת השיטות שם מגבלה של כמות תווים לא גדולה במשתנה, כך שלשרשר מערך במשתנה אחד זה לא תמיד יהיה אפשרי.

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