זיהוי שגיאות בנוסחאות

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

עליך להקפיד	מידע נוסף
להתחיל כל פונקציה בסימן שוויון (=)	אם השמטת את סימן השוויון, מה שתקליד עשוי להיות מוצג כטקסט או כתאריך. לדוגמה, אם תקליד SUM(A1:A10)‎‏,‏ Excel יציג את מחרוזת הטקסט SUM(A1:A10)‎ ולא יבצע את החישוב. אם תקליד 11/2,‏ Excel יציג את התאריך 11-פבר (בהנחה שתבנית התא היא כללי) במקום לחלק 11 ב- 2.
להתאים את כל תווי הסוגריים הפותחים והסוגרים	ודא שכל הסוגריים הם חלק מזוג תואם (פותח וסוגר). בעת שימוש בפונקציה בנוסחה, חשוב שכל תו סוגריים יהיה במיקום הנכון כדי שהפונקציה תפעל כראוי. לדוגמה, הנוסחה ‎‎=IF(B5<0),"Not valid",B5*1.05)‎‎ לא תפעל מכיוון שקיימים שני תווי סוגריים סוגרים ורק תו סוגריים פותח אחד, ויש לציין רק תו אחד מכל סוג. הנוסחה הנכונה נראית כך: ‎‎=IF(B5<0,"Not valid",B5*1.05)‎.
להשתמש בנקודתיים כדי לציין טווח	בעת הפניה לטווח תאים, השתמש בנקודתיים (:) כדי להפריד בין ההפניה לתא הראשון בטווח לבין ההפניה לתא האחרון בטווח. לדוגמה, =SUM(A1:A5), לא =SUM(A1 A5), אשר יחזיר #NULL! שגיאה.
להזין את כל הארגומנטים הדרושים	פונקציות מסוימות כוללות ארגומנטים נדרשים. כמו כן, הקפד שלא להזין יותר מדי ארגומנטים.
להזין את סוג הארגומנטים המתאים	פונקציות מסוימות, כגון SUM, דורשות ארגומנטים מספריים. פונקציות אחרות, כגון REPLACE, דורשות ערך טקסט בארגומנט אחד לפחות. אם אתה משתמש בסוג נתונים שגוי כארגומנט, ייתכן Excel תוצאות בלתי צפויות או להציג שגיאה.
לקנן עד 64 פונקציות לכל היותר	באפשרותך להזין או לקנן 64 רמות של פונקציות לכל היותר בתוך פונקציה.
לתחום שמות גליונות אחרים בסימני גרש	אם הנוסחה מפנה לערכים או לתאים בגליונות עבודה אחרים או בחוברות עבודה אחרות, והשם של חוברת העבודה או גליון העבודה מכיל רווחים או תוווים שאינם אלפביתיים, עליך לתחום את השם בסימני גרש ('), כגון ‎='נתונים רבעוניים'!D3 או ‎='123'!A1.
להוסיף סימן קריאה (!) אחרי שם גליון עבודה בעת הפניה אליו מתוך נוסחה	לדוגמה, כדי להחזיר את הערך מתא D3 בגליון עבודה בשם "נתונים רבעוניים" באותה חוברת עבודה, השתמש בנוסחה: ‎‎='נתונים רבעוניים'!D3.
לכלול את הנתיב לחוברות עבודה חיצוניות	ודא שכל הפניה חיצונית מכילה שם של חוברת עבודה ואת הנתיב לחוברת העבודה. הפניות לחוברות עבודה כוללות את שם חוברת העבודה, ויש לתחום אותן בסוגריים מרובעים ([שם_חוברת_עבודה.xlsx]). הן חייבות לכלול גם את שמות גליונות העבודה שבחוברת העבודה. אם חוברת העבודה שברצונך להפנות אליה אינה פתוחה ב- Excel, תוכל עדיין לכלול הפניה אליה בנוסחה. עליך לספק את הנתיב המלא לקובץ, כגון בדוגמה הבאה: ‎=ROWS('C:\My Documents\[תפעול ברבעון השני.xlsx]מכירות'!A1:A8)‎. נוסחה זו מחזירה את מספר השורות בטווח שכולל את התאים A1 עד A8 בחוברת העבודה האחרת (8). הערה: אם הנתיב המלא מכיל תווי רווח, כמו בדוגמה הקודמת, עליך לתחום את הנתיב בסימני גרש (בתחילת הנתיב ולאחר שם גליון העבודה, לפני סימן הקריאה).
להזין מספרים ללא עיצוב	אל תעצב מספרים כשאתה מזין אותם בנוסחאות. לדוגמה, אם הערך שברצונך להזין הוא ‎$1,000, הזן 1000 בנוסחה. אם תזין פסיק כחלק מהמספר, Excel יפרש את הפסיק כתו מפריד. אם ברצונך להציג מספרים כך שיציגו מפרידים של אלפים או מיליונים או סמלי מטבעות, עצב את התאים לאחר הזנת המספרים. לדוגמה, אם ברצונך להוסיף 3100 לערך בתא A3, ותזין את הנוסחה =SUM(3,100,A3), Excel יוסיף את המספרים 3 ו- 100 ולאחר מכן יוסיף סכום זה לערך מ- A3, במקום להוסיף 3100 ל- A3, אשר יהיה =SUM(3100,A3). לחלופין, אם אתה מזין את הנוסחה ‎=ABS(-2,134)‎‏‎‏‎, Excel מציג שגיאה משום שהפונקציה ABS מקבלת ארגומנט אחד בלבד: ‎=ABS(-2134)‎.

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

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

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

1. עבור Excel ב- Windows, עבור אל קובץ > אפשרויות > נוסחאות, או
   עבור Excel ב- Mac, בחר בתפריט Excel > העדפות > בדיקת שגיאות.

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

   

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

4. תחת כללי בדיקה של Excel, בחר או נקה את תיבות הסימון של הכללים הבאים:

   • תאים המכילים נוסחאות שלהן תוצאות שגויות: הנוסחה אינה משתמשת בתחביר, בארגומנטים או בסוגי הנתונים הצפויים. ערכי השגיאה כוללים את ‎#DIV/0!‎,‏ ‎#N/A,‏ ‎#NAME?‎,‏ ‎#NULL!‎,‏ ‎#NUM!‎,‏ ‎#REF!‎ ו- ‎#VALUE!‎. לכל אחד מערכים אלה יש סיבות שונות והוא נפתר בדרכים שונות.

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

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

     • הקלדת נתונים שאינם נוסחה בתא של עמודה מחושבת.

     • הקלד נוסחה בתא עמודה מחושבת ולאחר מכן השתמש ב - Ctrl +Zאו בחר בטל בסרגל הכלים לגישה מהירה.

     • הקלדת נוסחה חדשה בעמודה מחושבת שכבר מכילה חריגה אחת או יותר.

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

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

   • תאים המכילים שנים שמיוצגות כשתי ספרות: התא מכיל תאריך טקסט אשר עלול להתפרש בטעות כתאריך השייך למאה אחרת כאשר הוא מופיע בתוך נוסחאות. לדוגמה, התאריך בנוסחה ‎=YEAR("1/1/31")‎ עשוי להיות 1931 או 2031. השתמש בכלל זה כדי לחפש תאריכי טקסט רב-משמעיים.

   • מספרים המעוצבים כטקסט או המופיעים אחרי גרש: התא מכיל מספרים המאוחסנים כטקסט. מצב זה מתרחש בדרך כלל בעת ייבוא נתונים ממקורות אחרים. מספרים המאוחסנים כטקסט עלולים לגרום לתוצאות מיון בלתי צפויות, ולכן מומלץ להמיר אותן למספרים. לדוגמה, הנוסחה ‎'=SUM(A1:A10)‎ נחשבת לטקסט.

   • נוסחאות שאינן תואמות לנוסחאות אחרות באזור: הנוסחה אינה תואמת לתבנית של נוסחאות אחרות בקרבתה. במקרים רבים, נוסחאות הסמוכות לנוסחאות אחרות משתנות רק בהפניות שנמצאות בשימוש. בדוגמה הבאה של ארבע נוסחאות סמוכות, Excel מציג שגיאה ליד הנוסחה ‎=SUM(A10:C10)‎ בתא D4 מאחר שהנוסחאות הסמוכות אליה גדלות בשורה אחת, ונוסחה זו גדלה ב- 8 שורות – Excel מצפה לנוסחה ‎=SUM(A4:C4)‎.

     

     אם ההפניות המשמשות בנוסחה אינן תואמות לאלה בנוסחאות הסמוכות, Excel מציג שגיאה.

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

     לדוגמה, Excel מוסיף שגיאה לצד הנוסחה =SUM(D2:D4) בעת החלת כלל זה, מכיוון שהתאים D5, D6 ו- D7 סמוכים לתאים שאליהם מתבצעת ההפניה בנוסחה ולתא המכיל את הנוסחה (D8), ותאים אלה מכילים נתונים שיש להפנות אל הנוסחה.

     

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

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

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

     

   • הנתונים שהוזנו בטבלה אינם חוקיים: קיימת שגיאת אימות בטבלה. בדוק את הגדרת האימות של התא על-ידי מעבר לכרטיסיה נתונים > הקבוצה כלי נתונים > אימות נתונים.

1. בחר את גליון העבודה שבו ברצונך לחפש אחר שגיאות.

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

   אם תיבת הדו-שיח בדיקת שגיאות אינה מוצגת, בחר נוסחאות > ביקורת > שגיאות.

3. אם התעלמת בעבר משגיאות כלשהן, באפשרותך לבדוק שוב אם קיימות שגיאות אלה על-ידי ביצוע הפעולות הבאות: עבור אל קובץ >אפשרויות > נוסחאות. עבור Excel ב- Mac, בחר בתפריט Excel > העדפות > בדיקת שגיאות.

   במקטע בדיקת שגיאות, בחר איפוס התעלמות משגיאות >אישור.

   

   הערה: איפוס התעלמות משגיאות מאפס את כל השגיאות בכל הגליונות בחוברת העבודה הפעילה.

   עצה: מומלץ להעביר את תיבת הדו-שיח בדיקת שגיאות אל מתחת לשורת הנוסחאות.

   

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

5. בחר הבא.

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

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

   

אם נוסחה אינה יכולה להעריך תוצאה כראוי, Excel מציג ערך שגיאה, כגון #####, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, ו- #VALUE!. לכל סוג שגיאה יש סיבות שונות ופתרונות שונים.

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

נושא	תיאור
תיקון שגיאת ####	Excel מציג שגיאה זו כשעמודה אינה רחבה דיה כדי להציג את כל התווים בתא, או כשתא מכיל ערכי תאריך או שעה שליליים. לדוגמה, נוסחה המחסירה תאריך בעתיד מתאריך בעבר, כגון ‎=06/15/ 2008-07/ 01/2008, מביאה לערך תאריך שלילי. עצה: נסה לבצע התאמה אוטומטית של התא על-ידי לחיצה כפולה בין כותרות העמודות. אם ### מוצג מאחר של- Excel אין אפשרות להציג את כל התווים שהפעולה תתקן אותו.
תיקון שגיאת‎‏ ‏‎‎#DIV/0!‎‎‎‎‎‎‎‏ שגיאה	Excel מציג שגיאה זו כאשר מספר מחולק באפס (0) או בתא שאינו מכיל ערכים. עצה: הוסף מטפל בשגיאות, כמו בדוגמה הבאה: ‎=IF(C2,B2/C2,0)‎.
תיקון שגיאת ‎#N/A	Excel מציג שגיאה זו כשאין ערך זמין עבור פונקציה או נוסחה. אם אתה משתמש בפונקציה כגון VLOOKUP, האם קיימת התאמה לפריט שאתה מנסה לחפש בטווח בדיקת המידע? לעתים קרובות, לא קיימת התאמה. נסה להשתמש ב- IFERROR כדי להעלים את ‎#N/A. במקרה זה, עליך להשתמש בנוסחה הבאה: ‎=IFERROR(VLOOKUP(D2,$D$6:$E$8,2,TRUE),0)‎
תיקון שגיאת‎‎‏ ‏‎‎#NAME?‎‎‎‎‎‎‎‏ שגיאה	שגיאה זו מוצגת כש- Excel אינו מזהה טקסט בנוסחה. לדוגמה, שם טווח או שם של פונקציה עשויים להיות מאויתים באופן שגוי. הערה: אם אתה משתמש בפונקציה, ודא ששם הפונקציה מאוית כראוי. במקרה זה, השם SUM מאוית באופן שגוי. הסר את ה- e ו- Excel יתקן אותו.
תיקון שגיאת ‎#NULL!‎ שגיאה	Excel מציג שגיאה זו בעת ציון חיתוך של שני אזורים שאינם מצטלבים. אופרטור החיתוך הוא תו רווח המפריד בין הפניות בנוסחה. הערה: ודא שהטווחים מופרדים כראוי - האזורים C2:C3 ו- E4:E6 אינם מצטלבים, ולכן הזנת הנוסחה =SUM(C2:C3 E4:E6) מחזירה את ערך #NULL! שגיאת ‎#REF!‎. הצבת פסיק בין טווחי C ו- E מתקן אותה =SUM(C2:C3,E4:E6)
תיקון #NUM! שגיאה	Excel מציג שגיאה זו כאשר נוסחה או פונקציה מכילה ערכים מספריים שאינם חוקיים. האם אתה משתמש בפונקציה דרגתית, כגון IRR או RATE? אם כן, #NUM! קרוב לוודאי שהסיבה לכך היא שהפונקציה אינה יכולה למצוא תוצאה. עיין בנושא העזרה לקבלת שלבי פתרון.
תיקון שגיאת ‎#REF!‎‎‎‎‎‎ שגיאה	Excel מציג שגיאה זו כאשר הפניה לתא אינה חוקית. לדוגמה, ייתכן שמחיקה תאים שנוסחאות אחרות הפנו אליהם, או שהדבקת תאים שהעברת מעל התאים שנוסחאות אחרות הפנו אליהם. האם מחקת בטעות שורה או עמודה? מחקנו את עמודה B בנוסחה ‎=SUM(A2,B2,C2)‎ וגרמנו לשגיאה. השתמש באפשרות בטל (Ctrl+Z) כדי לבטל את המחיקה, בנה מחדש את הנוסחה או השתמש בהפניה לטווח רציף, כגון ‎=SUM(A2:C2)‎, אשר היה מתעדכן באופן אוטומטי בעת מחיקת העמודה B.
תיקון שגיאת‎‏ ‏‎‎#VALUE!‎‎‎‎‎‎‎‏ שגיאה	Excel עשוי להציג שגיאה זו אם הנוסחה כוללת תאים שמכילים סוגי נתונים שונים. האם אתה משתמש באופרטורים מתמטיים (+, -, *, /, ^) עם סוגי נתונים שונים? אם כן, נסה להשתמש בפונקציה במקום זאת. במקרה זה, הפונקציה ‎=SUM(F2:F5)‎ פותרת את הבעיה.

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

ניתן להזיז או לעגן סרגל כלים זה, בדומה לסרגלי כלים אחרים. לדוגמה, ניתן לעגן אותו בתחתית החלון. סרגל הכלים עוקב אחר מאפייני התא הבאים: 1) חוברת עבודה, 2) גיליון, 3) שם (אם התא מכיל טווח מתאים בעל שם), 4) כתובת תא, 5) ערך ו- 6) נוסחה.

הוספת תאים לחלון הצפייה

1. בחר את התאים שבהם ברצונך לצפות.

   כדי לבחור את כל התאים בגליון עבודה עם נוסחאות, עבור אל בית > עריכה> בחר חפש & בחר (או השתמש ב- Ctrl+G, או Control+G ב- Mac)> עבור אל נוסחאות >מיוחדות.

   

2. עבור אל נוסחאות >ביקורת נוסחאות > חלון צפייה.

3. בחר הוסף צפייה.

   

4. ודא שבחרת את כל התאים שבהם ברצונך לצפות ובחר הוסף.

   

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

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

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

הסרת תאים מחלון הצפייה

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

2. בחר את התאים שברצונך להסיר.

   כדי לבחור תאים מרובים, הקש CTRL ולאחר מכן בחר את התאים.

3. בחר מחק צפייה.

   

לעתים, הבנת האופן שבו נוסחה מקוננת מחשבת את התוצאה הסופית היא קשה, מכיוון שיש כמה חישובי ביניים ובדיקות לוגיות. עם זאת, באמצעות תיבת הדו-שיח הערכת נוסחה, באפשרותך להציג את החלקים השונים של נוסחה מקוננת כאשר הם מוערכים לפי הסדר שבו מחושבת הנוסחה. לדוגמה, קל יותר להבין את הנוסחה =IF(AVERAGE(D2:D5)>50,SUM(E2:E5),0) כאשר תוכל לראות את תוצאות הביניים הבאות:

בתיבת הדו-שיח 'הערכת נוסחה'	תיאור
‎=IF(AVERAGE(D2:D5)>50,SUM(E2:E5),0)‎	בתחילה, מוצגת הנוסחה המקוננת. הפונקציה AVERAGE והפונקציה SUM מקוננות בתוך הפונקציה IF. טווח התאים D2:D5 מכיל את הערכים 55, 35, 45 ו- 25, ולכן תוצאת הפונקציה AVERAGE(D2:D5)‎ היא 40.
‎=IF(40>50,SUM(E2:E5),0)‎	טווח התאים D2:D5 מכיל את הערכים 55, 35, 45 ו- 25, ולכן תוצאת הפונקציה AVERAGE(D2:D5)‎ היא 40.
‎=IF(False,SUM(E2:E5),0)‎	מכיוון ש- 40 אינו גדול מ- 50, הביטוי בארגומנט הראשון של הפונקציה IF (הארגומנט logical_test) הוא בעל ערך False. הפונקציה IF מחזירה את הערך של הארגומנט השלישי (הארגומנט value_if_false). הפונקציה SUM אינה מוערכת משום שהיא הארגומנט השני בפונקציה IF (הארגומנט value_if_true), והיא מוחזרת רק כאשר הביטוי הוא בעל ערך True.

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

2. עבור אל נוסחאות >ביקורת נוסחאות >הערך נוסחה.

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

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

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

4. המשך לבחור באפשרות הערך עד להערכת כל חלק של הנוסחה.

5. כדי לראות שוב את ההערכה, בחר הפעל מחדש.

6. כדי לסיים את ההערכה, בחר סגור.