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

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

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

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

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

האם אתה משתמש במפרידי הרשימה הנכונים בנוסחה?

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

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

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

האם אתה רואה שגיאת סולמית (#) בנוסחאות של Excel?

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

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

• תיקון #NUM! שגיאה
• תיקון שגיאת‎‏ ‏‎‎#VALUE!‎‎‎‎‎‎‎‏ שגיאה
• תיקון שגיאת ‎#N/A
• תיקון שגיאת‎‏ ‏‎‎#DIV/0!‎‎‎‎‎‎‎‏ שגיאה
• תיקון שגיאת ‎#REF!‎‎‎‎‎‎ שגיאה
• תיקון שגיאת‎‎‏ ‏‎‎#NAME?‎‎‎‎‎‎‎‏
• תיקון שגיאת ‎#NULL!‎ שגיאה

תיקון קישורים מנותקים בנוסחאות ב- Excel

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

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

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

הנוסחה מציגה תחביר במקום ערך ב- Excel

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

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

  עצה

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

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

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

  

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

כאשר נוסחה אינה מחושבת, תצטרך לבדוק אם חישוב אוטומטי זמין ב- 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),"Not valid",B5*1.05) לא תפעל מכיוון שקיימים שני תווי סוגריים סוגרים, אלא רק תו סוגריים פותח אחד. הנוסחה הנכונה תיראה כך: =IF(B5<0,"Not valid",B5*1.05).

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

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

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

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

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

טיפול במספרים לא מעוצבים בנוסחאות ב- 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 בתו הכוכבית (*).

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

האם חסרים תווי מרכאות סביב טקסט בנוסחאות?

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

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

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

האם ישנן יותר מ- 64 פונקציות בנוסחה?

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

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

האם שמות גליונות תחומים בגרשיים?

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

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

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

תיקון נתיבים של חוברות עבודה חיצוניות בנוסחאות Excel

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

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

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

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

האם אתה מחלק ערכים מספריים באפס?

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

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

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

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

האם הנוסחה מפנה לנתונים שנמחקו?

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

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

• אם הנוסחה מפנה לתאים שנמחקו או הוחלפו בנתונים אחרים, ואם היא מחזירה שגיאת #REF!, בחר את התא עם שגיאת #REF! שגיאת ‎#REF!‎. בשורת הנוסחאות, בחר #REF! ומחק אותה. לאחר מכן הזן שוב את הטווח עבור הנוסחה.
• אם חסר שם מוגדר, ונוסחה שמפנה לשם זה מחזירה את השגיאה ‎#NAME?‎, הגדר שם חדש שמפנה לטווח הרצוי, או שנה את הנוסחה כך שתפנה ישירות לטווח התאים (לדוגמה, A2:D8).
• אם חסר גיליון עבודה, ונוסחה שמפנה אליו מחזירה את השגיאה ‎#REF!‎, למרבה הצער, אין דרך לפתור את הבעיה – לא ניתן לשחזר גליון עבודה שנמחק.
• אם חסרה חוברת עבודה, נוסחה שמפנה אליה נשארת כמות שהיא עד לעדכון הנוסחה.
  לדוגמה, אם הנוסחה היא ‎=[Book1.xlsx]Sheet1'!A1, וחוברת העבודה Book1.xlsx אינה קיימת עוד, הערכים שמתבצעת אליהם הפניה בחוברת עבודה זו נותרים זמינים. עם זאת, אם תערוך ותשמור נוסחה שמפנה לחוברת עבודה זו, Excel יציג את תיבת הדו-שיח עדכון ערכים ויבקש ממך להזין שם קובץ. בחר 'ביטול' ולאחר מכן ודא שנתונים אלה לא יאבדו על-ידי החלפת הנוסחאות שמפנות לחוברת העבודה החסרה בתוצאות הנוסחאות.

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

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

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

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

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

2. בכרטיסיה בית , בקבוצה לוח , בחר העתק .
   
   קיצור מקשים: הקש CTRL+C.

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

   עצה

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

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

אם יש לך נוסחה מקוננת, הערך אותה שלב אחרי שלב

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

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

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

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

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

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

   הערה

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

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

תמיד תוכל לשאול מומחה בקהילה הטכנולוגית של Excel או לקבל תמיכה בקהילות.

למידע נוסף

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

עזרה של Excel & למידה