החלת אימות נתונים על תאים

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

הורד את הדוגמאות שלנו

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

הורד דוגמאות אימות נתונים של Excel

הוספת אימות נתונים לתא או לטווח

הערה: שלושת השלבים הראשונים בסעיף זה רלוונטיים להוספת כל סוג של אימות נתונים. שלבים 4-8 מיועדים במיוחד ליצירת רשימה נפתחת.

  1. בחר תא אחד או יותר שאת חוקיותם יש לוודא.

  2. בכרטיסיה נתונים, בקבוצה כלי נתונים, לחץ על אימות נתונים.

    האפשרות 'אימות נתונים' ממוקמת בכרטיסיה 'נתונים' בקבוצה 'כלי נתונים'
  3. בכרטיסיה הגדרות,‏ בתיבה אפשר, בחר רשימה.

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

    1. כדי להגביל תשובה לשתי אפשרויות (למשל, "האם יש לך ילדים?"), הקלד כן,לא.

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

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

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

      הזן שם בעל משמעות עבור הרשימה בתיבה 'שם'

    כעת, במקום להקליד את ערכי הרשימה בתיבה מקור של אימות הנתונים, הוסף את השם שזה עתה הגדרת ולפניו הקלד סימן שוויון (=).

    הזן סימן = לפני שם הטבלה

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

    הערה: מומלץ להציב את הרשימות בגליון עבודה נפרד (מוסתר אם יש צורך) כדי שאף אחד לא יוכל לערוך אותן.

  5. ודא שתיבת הסימון רשימה נפתחת בתוך תא נבחרה. אחרת, לא תוכל לראות את החץ של הרשימה הנפתחת ליד התא.

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

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

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

הערות: 

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

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

  • הסרת אימות נתונים – בחר את התא או התאים המכילים את האימות שברצונך למחוק ולאחר מכן עבור אל נתונים > אימות נתונים. בתיבת הדו-שיח 'אימות נתונים', הקש על לחצן נקה הכל ולאחר מכן הקש על אישור.

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

כדי לבצע פעולה זאת:

בצע שלבים אלה:

הגבלת הזנת הנתונים למספרים שלמים במסגרת מגבלות.

  1. פעל בהתאם לשלבים 1-3 בהוספת אימות נתונים לתא או לטווח לעיל.

  2. מתוך הרשימה אפשר, בחר מספר שלם.

  3. בתיבה נתונים, בחר את סוג ההגבלה הרצוי. לדוגמה, כדי להגדיר מגבלות עליונות ותחתונות, בחר בין.

  4. הזן את הערך המינימלי, המקסימלי או הספציפי שברצונך לאפשר את הזנתו.

    תיבת הדו-שיח 'קריטריוני אימות'

    ניתן גם להזין נוסחה המחזירה ערך של מספר.

    לדוגמה, נניח שאתה מאמת נתונים בתא F1. כדי לקבוע מגבלה מינימלית של ניכויים למספר ילדים כפול שתיים באותו תא, בחר גדול או שווה ל בתיבה נתונים והזן את הנוסחה ‎=2*F1 בתיבה ערך מינימלי.

הגבלת הזנת הנתונים למספר עשרוני במסגרת מגבלות.

  1. פעל בהתאם לשלבים 1-3 בהוספת אימות נתונים לתא או לטווח לעיל.

  2. בתיבה אפשר, בחר עשרוני.

  3. בתיבה נתונים, בחר את סוג ההגבלה הרצוי. לדוגמה, כדי להגדיר מגבלות עליונות ותחתונות, בחר בין.

  4. הזן את הערך המינימלי, המקסימלי או הספציפי שברצונך לאפשר את הזנתו.

    ניתן גם להזין נוסחה המחזירה ערך של מספר. לדוגמה, כדי לקבוע מגבלה מינימלית לעמלות ובונוסים בגובה 6% ממשכורת של איש מכירות בתא E1, בחר קטן או שווה ל בתיבה נתונים והזן את הנוסחה ‎=E1*6% בתיבה ערך מקסימלי.

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

הגבלת הזנת הנתונים לתאריך במסגרת טווח תאריכים.

  1. פעל בהתאם לשלבים 1-3 בהוספת אימות נתונים לתא או לטווח לעיל.

  2. בתיבה אפשר, בחר תאריך.

  3. בתיבה נתונים, בחר את סוג ההגבלה הרצוי. לדוגמה, כדי לאפשר תאריכים לאחר יום מסוים, בחר בגדול מ.

  4. הזן את תאריך ההתחלה, תאריך הסיום או התאריך הספציפי שברצונך לאפשר את הזנתו.

    ניתן גם להזין נוסחה המחזירה תאריך. לדוגמה, כדי לקבוע מסגרת זמן בין התאריך של היום לבין 3 ימים מהתאריך של היום, בחר בין בתיבה נתונים, הזן ‎=TODAY()‎ בתיבה תאריך התחלה והזן ‎=TODAY()+3 בתיבה תאריך סיום.

    הגדרות קריטריוני אימות להגבלת הזנת תאריכים למסגרת זמן מסוימת

הגבלת הזנת הנתונים לשעה בתוך מסגרת זמן.

  1. פעל בהתאם לשלבים 1-3 בהוספת אימות נתונים לתא או לטווח לעיל.

  2. בתיבה אפשר, בחר שעה.

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

  4. הזן את שעת ההתחלה, שעת הסיום או שעה ספציפית שיש לאפשר. אם ברצונך להזין שעות מסוימות, השתמש בתבנית השעה hh:mm.

    לדוגמה, נניח שהגדרת את תא E2 לשעת התחלה (08:00) ואת תא F2 לשעת סיום (17:00), וברצונך להגביל את מועדי הפגישות למסגרת זמן זו, בחר בין בתיבה נתונים, הזן ‎=E2 בתיבה שעת התחלה ולאחר מכן הזן ‎=F2 בתיבה שעת סיום.

    הגדרות אימות להגבלת הזנת שעות למסגרת זמן מסוימת

הגבלת הזנת הנתונים לטקסט באורך מוגדר.

  1. פעל בהתאם לשלבים 1-3 בהוספת אימות נתונים לתא או לטווח לעיל.

  2. בתיבה אפשר, בחר אורך טקסט.

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

  4. במקרה זה, אנו מעוניינים להגביל את ההזנה ל- 25 תווים. לכן, בחר קטן או שווה ל- בתיבה נתונים והזן 25 בתיבה מקסימום.

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

חישוב הנתונים המותרים בהתבסס על תוכן של תא אחר.

  1. פעל בהתאם לשלבים 1-3 בהוספת אימות נתונים לתא או לטווח לעיל. בתיבה אפשר, בחר את סוג הנתונים הרצוי.

  2. בתיבה נתונים, בחר את סוג ההגבלה הרצוי.

  3. בתיבה או בתיבות מתחת לתיבה נתונים, לחץ על התא שבו ברצונך להשתמש כדי לציין את הנתונים המותרים.

    לדוגמה, כדי להתיר ערכים עבור חשבון רק אם התוצאה לא תהיה גבוהה מהתקציב בתא E1, בחר אפשר >מספר שלם, נתונים, קטן או שווה ל, ומקסימום ‎>= =E1.

    הגדרות אימות לחישוב בהתבסס על תוכן תא אחר

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

כדי לוודא כי

הזן נוסחה זאת

התא המכיל מזהה מוצר (C2) מתחיל תמיד בקידומת הסטנדרטית ID ואורכו 10 תווים לפחות (גדול מ- 9).

‎=AND(LEFT(C2, 3) ="ID-",LEN(C2) > 9)‎

דוגמה 6: נוסחאות באימות נתונים

התא המכיל שם מוצר (D2) מכיל טקסט בלבד.

‎=ISTEXT(D2)‎

דוגמה 2: נוסחאות באימות נתונים

התא שמכיל תאריך יום הולדת (B6) חייב להיות גדול יותר ממספר השנים שהוגדר בתא B4.

‎=IF(B6<=(TODAY()-(365*B4)),TRUE,FALSE)‎

דוגמה של אימות נתונים כדי להגביל את הערך לגיל מינימלי

כל הנתונים בטווח התאים A2:A10 מכילים ערכים ייחודיים.

‎=COUNTIF($A$2:$A$10,A2)=1

דוגמה 4: נוסחאות באימות נתונים

הערה: עליך להזין תחילה את נוסחת אימות הנתונים עבור תא A2 ולאחר מכן להעתיק את A2 ל- A3:A10 כך שהארגומנט השני ב- COUNTIF יתאים לתא הנוכחי. כלומר, המקטע A2)=1 ישתנה ל- A3)=1 A4)=1 וכדומה

לקבלת מידע נוסף

הערך עבור כתובת דואר אלקטרוני בתא B4 מכיל את הסימן @.

‎=ISUMBER(FIND("@",B4)‎

דוגמה של אימות נתונים שמבטיח שכתובת דואר אלקטרוני תכיל את הסימן @

  • מדוע הפקודה 'אימות נתונים' אינה זמינה ברצועת הכלים? הפקודה עשויה להיות לא זמינה מהסיבות הבאות:

    • ייתכן שטבלת Microsoft Excel מקושרת לאתר SharePoint לא ניתן להוסיף אימות נתונים לטבלת Excel המקושרת לאתר SharePoint. כדי להוסיף אימות נתונים, יש לנתק את הקישור של טבלת Excel או להמיר אותה לטווח.

    • ייתכן שאתה מזין כעת נתונים הפקודה 'אימות נתונים' אינה זמינה בכרטיסיה נתונים כאשר אתה מזין נתונים בתא. כדי לסיים את הזנת הנתונים, הקש ENTER או ESC.

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

  • האם ניתן לשנות את גודל הגופן? לא, גודל הגופן קבוע. הדרך היחידה לשנות את גודל התצוגה היא להתאים את גודל התצוגה של המסך בפינה השמאלית התחתונה של חלון Excel. עם זאת, ניתן להשתמש בתיבה משולבת של ActiveX. ראה הוספת תיבת רשימה או תיבה משולבת לגליון עבודה.

  • האם קיימת אפשרות שאימות הנתונים ימלא אוטומטית או יבחר אוטומטית בזמן שאני מקליד? לא, אלא אם כן אתה משתמש בתיבה משולבת של ActiveX.

  • האם ניתן לבחור קטעים מרובים ברשימת אימות נתונים? לא, אלא אם אתה משתמש בתיבה משולבת של ActiveX או בתיבת רשימה.

  • האם אני יכול לבחור פריט ברשימת אימות נתונים ולהגדיר אותו לאכלס רשימה אחרת? כן! פעולה זו נקראת אימות נתונים תלוי. לקבלת מידע נוסף, ראה יצירת רשימות נפתחות תלויות.

  • כיצד ניתן להסיר את כל מידע אימות הנתונים מגליון עבודה? השתמש בתיבת הדו-שיח מעבר אל > מיוחד. בכרטיסיה בית > עריכה > חפש ובחר (או הקש F5 או Ctrl+G בלוח המקשים) ולאחר מכן מיוחד > אימות נתונים, בחר הכל (כדי לאתר את כל התאים הכוללים אימות נתונים) או זהים (כדי לאתר תאים שתואמים להגדרות מסוימות של אימות נתונים).

    תיבת הדו-שיח 'מעבר מיוחד'

    לאחר מכן, הפעל את תיבת הדו-שיח של אימות נתונים (הכרטיסיה 'נתונים' > 'אימות נתונים'), הקש על לחצן נקה הכל ולאחר מכן הקש על אישור.

  • האם ניתן לאלץ מישהו להזין ערך בתא(ים) עם אימות נתונים? לא, אבל ניתן להשתמש ב- VBA (‏Visual Basic for Applications) כדי לבדוק אם מישהו הזין ערך בתנאים מסוימים, למשל לפני שמירה או סגירה של חוברת העבודה. אם משתמש זה לא ביצע בחירה, באפשרותך לבטל את האירוע, ולא לאפשר לו להמשיך עד לביצוע בחירה.

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

    האפשרות 'עצב רק תאים המכילים'
  • כיצד אוכל לאמת כתובת דואר אלקטרוני? תוכל להשתמש בשיטה מותאם אישית > נוסחה ולבדוק אם הסימן @ קיים בערך. במקרה זה, הנוסחה היא ‎=ISNUMBER(FIND(“@”,D2))‎. הפונקציה FIND מחפשת את הסימן @, ואם הסימן נמצא, היא מחזירה את המיקום המספרי שלו במחרוזת הטקסט ומאפשרת את הערך. אם הסימן לא נמצא, הפונקציה FIND מחזירה שגיאה ומונעת את הערך.

האם יש לך שאלה בנושא פונקציה ספציפית?

פרסם שאלה בפורום הקהילה של Excel

עזור לנו לשפר את Excel

האם יש לך הצעות לשיפור הגירסה הבאה של Excel? אם כן, עיין בנושאים תחת User Voice עבור Excel.

למידע נוסף

מידע נוסף על אימות נתונים

וידאו: יצירה וניהול של רשימות נפתחות

הוספה או הסרה של פריטים מרשימה נפתחת

הסרת רשימה נפתחת

הרחב את הכישורים שלך
סייר בהדרכה
קבל תכונות חדשות לפני כולם
הצטרף למשתתפי Office Insider

האם מידע זה היה שימושי?

תודה על המשוב!

תודה על המשוב! נראה שכדאי לקשר אותך לאחד מנציגי התמיכה של Office.

×