נוסחאות Excel אינן פועלות: סיבות אפשריות וכיצד לתקן זאת!

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

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

אך מכיוון שאנו חיים בעולם העוקב אחר עקרון פארטו, אם אתה בודק כמה בעיות נפוצות, סביר להניח שהוא יפתור 80% (או אולי אפילו 90% או 95% מהבעיות שבהן נוסחאות אינן פועלות ב- Excel).

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

אז בואו נתחיל!

תחביר לא תקין של הפונקציה

הרשו לי להתחיל להצביע על המובן מאליו.

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

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

לדוגמה, הפונקציה VLOOKUP לוקחת שלושה ארגומנטים חובה וארגומנט אופציונלי אחד.

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

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

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

= VLOOKUP (A2, A2: C6,2, FALSE)

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

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

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

להלן דוגמה שבה יש לי את נתוני הציונים עבור חלק מהתלמידים ואני רוצה לחלץ ציונים בבחינה 1 לתלמידים בטבלה מימין.

כשאני משתמש בנוסחת VLOOKUP למטה זה נותן לי שגיאה בכמה שמות.

= VLOOKUP (E2, $ A $ 2: $ C $ 6,2)

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

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

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

קרא גם: זיהוי שגיאות באמצעות איתור באגים בנוסחאות של Excel

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

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

לדוגמה, בדוגמה שלהלן, אם אני מנסה להשתמש ב- VLOOKUP כדי להביא את הציון עבור מארק, זה נותן לי את השגיאה #לא רלוונטית (השגיאה לא זמינה).

למרות שאני יכול לראות שהנוסחה נכונה והשם 'סימן' ברור שיש את הרשימה, מה שאני לא יכול לראות הוא שיש תא נגרר בתא שיש לו את השם (בתא D2).

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

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

תוכל לעשות זאת באמצעות אחת מהשיטות הבאות:

1. נקה את התא והסר את כל הרווחים המובילים/נגררים לפני השימוש בו בנוסחאות
2. השתמש בפונקציית TRIM בתוך הנוסחה כדי לוודא שכל התייחסות/רווח/כפול רווחים מתעלמים.

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

= VLOOKUP (TRIM (D2), $ A $ 2: $ B $ 6,2,0)

למרות שלקחתי את הדוגמה VLOOKUP, זוהי גם בעיה נפוצה בעבודה עם פונקציות TEXT.

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

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

שימוש בחישוב ידני במקום אוטומטי

הגדרה זו יכולה לשגע אותך (אם אתה לא יודע שזה מה שגורם לכל הבעיות).

ל- Excel שני מצבי חישוב - אוֹטוֹמָטִי ו מדריך ל.

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

זו ההגדרה שכולנו רגילים אליה.

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

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

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

אם תפעיל חישוב ידני, Excel לא יחשב אלא אם תאלץ זאת.

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

כל שעליך לעשות במקרה זה הוא להחזיר את החישוב לאוטומטי או לאלץ חישוב מחדש על ידי לחיצה על מקש F9.

להלן השלבים לשינוי החישוב בין ידני לאוטומטי:

1. לחץ על הכרטיסייה נוסחה
2. לחץ על אפשרויות חישוב
3. בחר אוטומטי

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

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

מחיקת שורות/טור/תאים המובילים ל- #REF! שְׁגִיאָה

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

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

ולפעמים… זה לא יכול.

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

תן לי להראות לך למה אני מתכוון באמצעות דוגמה.

להלן השתמשתי בנוסחת SUM להוספת התאים A2: A6.

עכשיו, אם אני מוחק כל אחד מהתאים/השורות האלה, נוסחת SUM תחזיר #REF! שְׁגִיאָה. זה קורה מכיוון שכאשר מחקתי את השורה, הנוסחה לא יודעת למה להתייחס כעת.

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

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

מיקום שגוי של סוגריים (BODMAS)

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

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

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

לדוגמה, נניח שיש לך את הנוסחה הבאה:

=5+10*50

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

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

=(5+10)*50

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

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

מַפעִיל	תיאור	סדר עדיפות
: (המעי הגס)	טווח	1
(שטח יחיד)	הִצטַלְבוּת	2
, (פסיק)	הִתאַחֲדוּת	3
-	שלילה (כמו ב- -1)	4
%	אֲחוּזִים	5
^	אקספונטינציה	6
* ו /	כפל וחילוק	7
+ ו- -	חיבור וחיסור	8
&	הסתייגות	9
= =	השוואה	10

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

שימוש לא נכון בהפניות לתאים מוחלטים/יחסית

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

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

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

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

תן לי להראות לך דוגמא.

להלן יש לי מערך נתונים שבו אני רוצה להביא את הציון בבחינה 1 עבור השמות בעמודה E (מקרה שימוש פשוט ב- VLOOKUP)

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

= VLOOKUP (E2, A2: B6,2,0)

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

זה קורה מכיוון שלא נעלתי את ארגומנט מערך הטבלאות - זהו A2: B6 בתא F2, בעוד שזה היה צריך להיות $ A $ 2: $ B $ 6

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

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

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

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

לדוגמה, אם אני רוצה להתייחס לתא A1 בגיליון 2, ההפניה תהיה = גיליון 2! A1 (כאשר יש סימן קריאה אחרי שם הגיליון)

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

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

= '[דוגמה לחוברת עבודה.קסלסקס] גיליון לדוגמה'! $ A $ 1

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

= 'C: \ Users \ sumit \ Desktop \ [דוגמה לחוברת עבודה. Xlsx] גיליון דוגמה'! $ A $ 1

במקרה שתשנה את שם חוברת העבודה או דף העבודה שאליו מתייחסת הנוסחה, זה יתן לך #REF! שְׁגִיאָה.

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

הפניות חוזר

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

להלן דוגמה פשוטה, שבה אני משתמש בנוסחת SUM בתא A4 תוך שימוש בה בחישוב עצמו.

= SUM (A1: A4)

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

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

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

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

תאים מעוצבים כטקסט

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

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

זה לא מכריח אותו לחשב ולתת את התוצאה.

ויש לזה תיקון קל.

1. שנה את הפורמט ל'כללי 'מ'טקסט' (הוא נמצא בכרטיסיה בית בקבוצה מספרים)
2. עבור לתא המכיל את הנוסחה, היכנס למצב העריכה (השתמש ב- F2 או לחץ פעמיים על התא) ולחץ על Enter

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

אם יש כפירה, אתה יכול פשוט להסיר אותו.

טקסט הופך אוטומטית לתאריכים

ל- Excel יש את ההרגל הרע הזה של המרה שנראית כמו תאריך לתאריך של ממש. לדוגמה, אם תזין 1/1, Excel ימיר אותו ל- 01-Jan של השנה הנוכחית.

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

ומכיוון ש- Excel מאחסן את ערכי התאריך והשעה כמספרים, ברגע שאתה מזין 1/1, הוא הופך אותו למספר המייצג את ה -1 בינואר של השנה הנוכחית. במקרה שלי, כשאני עושה זאת, הוא ממיר אותו למספר 43831 (עבור 01-01-2020).

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

כיצד לתקן זאת?

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

להלן השלבים לשינוי הפורמט לטקסט כך שלא ימיר טקסט אוטומטית לתאריכים:

1. בחר את התאים/הטווח שבו ברצונך לשנות את הפורמט
2. לחץ על הכרטיסייה דף הבית
3. בקבוצה מספר, לחץ על התפריט הנפתח עיצוב
4. לחץ על טקסט

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

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

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

שורות/עמודות נסתרות יכולות לתת תוצאות בלתי צפויות

זה לא מקרה של נוסחה שנותנת לך את התוצאה הלא נכונה אלא של שימוש בנוסחה הלא נכונה.

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

בתא C12, השתמשתי בפונקציית SUM כדי לקבל את ערך המכירה הכולל עבור כל הרשומות הנתונות.

בינתיים הכל טוב!

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

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

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

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

פונקציית SUM אינה יכולה להסביר את הנתונים המסוננים ולתת לך את התוצאה עבור כל התאים (מוסתרים או גלויים). אם אתה רק רוצה לקבל את הסכום/הספירה/הממוצע של התאים הגלויים, השתמש בפונקציות SUBTOTAL או AGGREGATE.

טייק אוויי מפתח - להבין את השימוש הנכון והמגבלות של פונקציה ב- Excel.

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

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

מקווה שמצאת הדרכה שימושית!