כיצד להימנע מנוסחאות שגויות

כיצד להימנע מנוסחאות שגויות

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

תמונה של תיבת הדו-שיח 'בעיה בנוסחה זו' של Excel

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

התחל על-ידי בחירה באפשרות אישור או הקש ESC כדי לסגור את הודעת השגיאה.

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

תמונה של לחצן 'ביטול' בשורת הנוסחאות

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

הערה: אם אתה משתמש ב-Office באינטרנט, ייתכן שלא תראה את אותן שגיאות, או שהפתרונות לא יחולו.

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

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

תמונה של 'בית' > 'עיצוב' > 'התאם אוטומטית לרוחב עמודה'

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

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

תיבת הדו-שיח ’הפניות מנותקות’ ב- Excel

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

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

תמונה האפשרות הקישור של שאל כדי לעדכן את האפשרות 'בקש עדכון קישורים אוטומטיים'

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

אם הנוסחה אינה מציגה את הערך, בצע את הפעולות הבאות:

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

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

  • אם השלב שלעיל עדיין אינו פותר את הבעיה, ייתכן שהתא מעוצב כטקסט. תוכל לחץ באמצעות לחצן העכבר הימני על התא, לבחור עיצוב תאים > כללי (או Ctrl + 1) ולאחר מכן להקיש על F2 > Enter כדי לשנות את התבנית.

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

    תמונה של 'נתונים' > תיבת הדו-שיח 'טקסט לעמודות'

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

  1. בחר את הכרטיסיה קובץ , בחר אפשרויותולאחר מכן בחר את הקטגוריה נוסחאות .

  2. במקטע אפשרויות חישוב, תחת חישוב חוברת עבודה, ודא שהאפשרות אוטומטי נבחרה.

    תמונה של אפשרויות 'חישוב אוטומטי וידני'

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

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

לקבלת מידע נוסף אודות הפניות מעגליות, ראה הסרה או התרה של הפניה מעגלית.

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

אם תקליד משהו כמו SUM(A1:A10)‎, ‏Excel יציג את מחרוזת הטקסט SUM(A1:A10)‎ ולא תוצאה של נוסחה. לחלופין, אם תקליד 11/2, Excel יציג תאריך, כגון 2-נוב או 11/02/2009, במקום להפריד בין 11 ל-2.

כדי להימנע מתוצאות בלתי צפויות אלה, התחל פונקציות תמיד בסימן שוויון. לדוגמה, הקלד: =SUM (A1: A10) ו- = 11/2.

בעת שימוש בפונקציה בנוסחה, כדי שהנוסחה תפעל כראוי, עבור כל תו סוגריים פותח נדרש תו סוגריים סוגר. ודא שכל הסוגריים הם חלק מזוג תואם. לדוגמה, הנוסחה = IF (b5<0), "לא חוקי", B5 * 1.05) לא תפעל מאחר שקיימים שני סוגריים סוגריים, אך רק סוגריים פותחים אחד. הנוסחה הנכונה תיראה כך: ‎=IF(B5<0,"Not valid",B5*1.05)‎.

הפונקציות של Excel כוללות ארגומנטים - ערכים שעליך לספק כדי שהפונקציה תפעל. רק מספר פונקציות (כגון PI או TODAY) אינן משתמשות בארגומנטים. בדוק את תחביר הנוסחה שמופיע כאשר אתה מתחיל להקליד את הפונקציה כדי לוודא שהפונקציה כוללת את כל הארגומנטים הדרושים.

לדוגמה, הפונקציה UPPER מקבלת מחרוזת טקסט או הפניה לתא אחת בלבד בתור ארגומנט: ‎=UPPER("hello")‎ או ‎=UPPER(C2)‎.

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

צילום מסך של סרגל הכלים ’הפניה לפונקציה’

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

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

אל תזין מספרים שעוצבו באמצעות סימני דולר ($) או מפרידים עשרוניים (,) בנוסחאות מכיוון שסימני דולר מציינים הפניות מוחלטות ופסיקים הם מפרידי ארגומנטים. במקום להזין $1,000, הזן 1000 בנוסחה.

אם אתה משתמש במספרים מעוצבים בארגומנטים, תקבל תוצאות חישוב בלתי צפויות, אך ייתכן שתראה גם את השגיאה #NUM! . לדוגמה, אם תזין את הנוסחה = ABS (-2,134) כדי למצוא את הערך המוחלט של-2134, Excel יציג את ה#NUM! שגיאה, מאחר שהפונקציה ABS מקבלת ארגומנט אחד בלבד, והיא רואה את ה-2 ו-134 כארגומנטים נפרדים.

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

ייתכן שהנוסחה לא תחזיר את התוצאות הצפויות אם לא ניתן להשתמש בסוג הנתונים של התא עבור חישובים. לדוגמה, אם אתה מזין נוסחה פשוטה, כגון ‎=2+3, בתא שמעוצב כטקסט, ל- Excel אין אפשרות לחשב את הנתונים שהזנת. כל מה שתראה בתא הוא ‎=2+3. כדי לתקן זאת, שנה את סוג הנתונים של התא מטקסט לכללי באופן הבא:

  1. בחר את התא.

  2. בחר בית ובחר את החץ כדי להרחיב את הקבוצה ' תבנית מספר ' או ' תבנית מספר ' (או הקש Ctrl + 1). לאחר מכן בחר כללי.

  3. הקש על F2 כדי להיכנס למצב עריכה בתא ולאחר מכן הקש על Enter כדי לקבל את הנוסחה.

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

מאוד נפוץ להשתמש ב- x כאופרטור ההכפלה בנוסחה, אך Excel יכול לקבל רק את תו הכוכבית (*) עבור פעולות כפל. אם אתה משתמש בקבועים בנוסחה, Excel מציג הודעת שגיאה, ויכול לתקן את הנוסחה עבורך על-ידי החלפת ה- x בתו הכוכבית (*).

תיבת הודעה המציגה בקשה להחלפת x בתו * לביצוע פעולת כפל

עם זאת, אם אתה משתמש בהפניות לתאים, Excel יחזיר #NAME? ‎#VALUE!‎.

‎#NAME?‎ שגיאה בעת שימוש ב-x עם הפניות לתאים במקום * עבור כפל

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

לדוגמה, הנוסחה ‎="Today is " & TEXT(TODAY(),"dddd, mmmm dd")‎ משלבת את הטקסט "Today is" עם התוצאות של הפונקציות TEXT ו- TODAY, ומחזירה משהו כמו Today is Monday, May 30.

בנוסחה, "Today is" כולל רווח לפני המירכאות המסתיימות כדי לספק את השטח הריק הרצוי בין המילים "Today is" ו-"יום שני, מאי 30". ללא מרכאות מסביב לטקסט, הנוסחה עשויה להראות את השגיאה #NAME?.

בתוך כל נוסחה ניתן לשלב (או לקונן) 64 רמות של פונקציות לכל היותר.

לדוגמה, הנוסחה = IF (SQRT (PI ()) <2, "פחות משתיים!", "יותר משתיים!") כולל 3 רמות של פונקציות; הפונקציה PI מקוננת בתוך הפונקציה SQRT, אשר בתורו מקוננת בתוך הפונקציה IF.

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

לדוגמה, כדי להחזיר את הערך מתא D3 בגליון עבודה בשם נתונים רבעוניים בחוברת העבודה שלך, הקלד: = ' נתוני רבעון '! D3. ללא המירכאות סביב שם הגיליון, הנוסחה מציגה את השגיאה #NAME?.

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

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

לדוגמה, כדי להפנות לתאים A1 עד ה-A8 בגליון המכירות בחוברת העבודה של פעולות Q2 הפתוחה ב-Excel, הקלד: = [Q2 Operations.xlsx] Sales! A1: A8. ללא הסוגריים המרובעים, הנוסחה מציגה את השגיאה #REF!.

אם חוברת העבודה אינה פתוחה ב- Excel, הקלד את הנתיב המלא לקובץ.

לדוגמה, ‎=ROWS('C:\My Documents\[Q2 Operations.xlsx]Sales'!A1:A8)‎.

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

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

חלוקת תא בתא אחר המכיל אפס (0) או שאינו מכיל ערך לתוצאה של שגיאת #DIV/0!.

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

‎=IF(B1,A1/B1,0)‎

המציין כי אם(B1 קיים, אזי חלק את A1 ב- B1, אחרת החזר 0).

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

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

  • אם נוסחה מפנה לתאים שנמחקו או הוחלפו בנתונים אחרים, ואם היא מחזירה שגיאת #REF!, בחר את התא עם ה#REF! ‎#VALUE!‎. בשורת הנוסחאות, בחר #REF! ולמחוק אותו. לאחר מכן הזן את הטווח עבור הנוסחה שוב.

  • אם חסר שם מוגדר, ונוסחה המפנה לשם זה מחזירה שגיאת #NAME?, הגדר שם חדש המפנה לטווח הרצוי, או שנה את הנוסחה כך שתתייחס ישירות לטווח התאים (לדוגמה, A2: D8).

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

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

    לדוגמה, אם הנוסחה היא ‎=[Book1.xlsx]Sheet1'!A1, וחוברת העבודה Book1.xlsx אינה קיימת עוד, הערכים שמתבצעת אליהם הפניה בחוברת עבודה זו נותרים זמינים. עם זאת, אם תערוך ותשמור נוסחה שמפנה לחוברת עבודה זו, Excel יציג את תיבת הדו-שיח עדכון ערכים ויבקש ממך להזין שם קובץ. בחר ביטולולאחר מכן ודא שנתונים אלה אינם אובדים על-ידי החלפת הנוסחאות המפנות לחוברת העבודה החסרה עם תוצאות הנוסחה.

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

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

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

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

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

    תמונת רצועת הכלים של Excel

    קיצור מקשים: הקש CTRL+C.

  3. בחר את התא הימני העליון באזור הדבקה.

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

  4. בכרטיסיה בית , בקבוצה לוח , בחר הדבק תמונת לחצן ולאחר מכן בחר הדבק ערכים, או הקש Alt > E > S > v > Enter עבור Windows, או Option > Command > v > v > enter ב-Mac.

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

  1. בחר את הנוסחה שברצונך להעריך.

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

    הקבוצה 'ביקורת נוסחאות' בכרטיסיה 'נוסחאות'

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

    תיבת הדו-שיח 'הערכת נוסחה'

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

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

  5. המשך בפעולה זו עד להערכת כל חלקי הנוסחה.

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

    הערות: 

    • לא תתבצע הערכה של חלקים מסוימים של הפונקציות IF ו- CHOOSE, והשגיאה ‎#N/A עשויה להופיע בתיבה הערכה.

    • הפניות ריקות מוצגות כערכי אפס (0) בתיבה הערכה.

    • פונקציות מסוימות מחושבות מחדש בכל פעם שגליון העבודה משתנה. פונקציות אלה, הכוללות את הפונקציות RAND,‏ AREAS,‏ INDEX,‏ OFFSET,‏ CELL,‏ INDIRECT,‏ ROWS,‏ COLUMNS,‏ NOW,‏ TODAY ו- RANDBETWEEN, עלולות לגרום לתיבת הדו-שיח הערכת נוסחה להציג תוצאות שונות מהתוצאות בפועל בתא שבגליון העבודה.

זקוק לעזרה נוספת?

תוכל תמיד לשאול מומחה ב- Excel Tech Community או לקבל תמיכה בקהילת Answers.

למידע נוסף

מבט כולל על נוסחאות ב- Excel

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

פונקציות של Excel (בסדר אלפביתי)

פונקציות של Excel (לפי קטגוריה)

זקוק לעזרה נוספת?

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

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

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

×