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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  1. בחר את התא.

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

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

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

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

תיבת הודעה המציגה בקשה להחלפת x בתו * לביצוע פעולת כפל
הודעת שגיאה על שימוש ב- x עם קבועים במקום ב- * לצורך הכפלה

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

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

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

לדוגמה, הנוסחה ‎="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 רמות של פונקציות לכל היותר.

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

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

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

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

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

לדוגמה, כדי להפנות לתאים A1 עד A8 בגיליון Sales של חוברת העבודה Q2 Operations הפתוחה ב- 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!‎ ‎. בשורת הנוסחאות, בחר את הכיתוב ‎#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. אם חלק הנוסחה המסומן בקו תחתון הוא הפניה לנוסחה אחרת, לחץ על צעד לתוך כדי להציג את הנוסחה האחרת בתיבה הערכה. לחץ על צעד אל מחוץ כדי לחזור לתא ולנוסחה הקודמים.

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

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

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

    הערות: 

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

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

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

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

ניתן לשאול תמיד מומחה ב- Excel Tech Community, לקבל תמיכה בקהילת Answers או להציע תכונה חדשה או שיפור ב- Excel User Voice.

למידע נוסף

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

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

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

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

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

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

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

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

×