10 שגיאות אפשריות בפונקציות Excel ופתרונותיהן

כך תוכל לגלות שגיאות אלה ב- Excel באופן אוטומטי

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

אלה 10 טעויות נפוצות בפונקציות Excel

להלן עשרת קודי השגיאה הנפוצים ביותר לבעיות ופתרונות אפשריים של Excel.

1. #NAME?

מתרחשות טעויות הקלדה - אם זה המקרה בנוסחה, הודעת השגיאה #שם? מופיעה במקום התוצאה. אין טעם להסתיר את השגיאה עם פונקציה כמו IFERROR. אתה צריך לתקן את זה. כל שעליך לעשות הוא לבחון היטב את השם שנתת לנוסחה. האם יש מכתב מעוות? שכחת אות או הזנת אותה פעמיים? לדוגמה, נכון = SUME (A3: A16) עד = SUM (A3: A16) ותקבל את התוצאה הנכונה.

השתמש באשף הנוסחאות

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

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

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

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

2. #NULL!

הודעת שגיאה זו יכולה להצביע על שני דברים:

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

במקרה הראשון, אתה מתכוון לטווח תאים רציף בנוסחה - למשל תאים B4 עד B12. אתה משתמש במעי הגס כמפעיל טווח. לדוגמה, אם ברצונך לחשב סך, הנוסחה הנכונה היא = SUM (B4: B12). אם אתה מתכוון לשני אזורים ללא חפיפה, הפסיק הוא המפעיל המתאים. אם אתה רוצה לחשב את הסכום מהאזורים B4 עד B12 ו- C9 עד C23, נוסחה זו נכונה: = SUM (B4: B12; C9: C23).

במקרה השני, אתה רוצה לעבוד עם טווחי תאים חופפים, אך שני הטווחים שצוינו אינם חופפים. לדוגמה, אתה מקבל את השגיאה עבור הנוסחה = CELL ("כתובת" (B4: B12 D4: D6)). אין חפיפה בין שני התחומים. אם תשנה את האזורים כך שהם חופפים - למשל as = CELL ("כתובת" (B4: B12 B5: D5)) - נקודת החיתוך של שני האזורים מוצגת כתוצאה מכך. במקרה זה זהו תא B5.

3. #רפרנס!

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

לדוגמה, יש לך גיליון אלקטרוני של Excel עם שורות 1, 2 ו -3 ועמודות A, B ו- C. הנוסחה היא = SUM (A2; B2; C2). כעת תמחק שורה 2. במקום התוצאה, לאחר מכן תראה את ערך השגיאה #REFER, מכיוון שאחד מערכי החישוב אינו קיים.

אם בטעות מחקת את החלק החסר לחישוב, תוכל לתקן את השגיאה שלך ישירות באמצעות הפקודה "בטל". אם אזור האזור נמחק בצדק, נוסח מחדש את הנוסחה. אם תזין = SUM (A2: C2), תוצג לך התוצאה הנכונה מכיוון ש- Excel פשוט מסיר את העמודה השנייה שנמחקה.

4. #ערך!

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

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

• אתה יכול להשתמש בפונקציות במקום באופרטורים מתמטיים. אז במקום = B4 + B5 + B6 הזן את הפונקציה = SUM (B4: B6).
• בדוק את התאים המדוברים עבור תווים מיוחדים. במידת הצורך, השתמש בפונקציה ISTTEXT בעמודה נפרדת לשם כך. זה מראה לך באיזה תא השגיאה נמצאת.
• אולי ערך השגיאה נובע מרווחים בתא שמופיעים בפונקציה. כדי לברר זאת, סמן את התאים המתאימים. לאחר מכן עבור אל "חפש ובחר" תחת "התחל" ולחץ על "החלף". הזן רווח עבור "חפש" ושום דבר עבור "החלף ב-". עכשיו אתה יכול להחליף את הרווחים הלא רצויים.
• תווים נסתרים יכולים גם להוביל לשגיאה נפוצה זו בפונקציות Excel. כדי לחסל אותם, עבור אל "התחל" ו"מיון וסנן "ב"סינון". מתחת לחץ המסנן, בטל את "בחר הכל" וסמן את "ריק תאים" ואת כל המיקומים שבהם אין כלום. אם תלחץ על אישור, Excel יראה לך את כל התאים הריקים כביכול המכילים סמלים מוסתרים. סמן אותו ולחץ על "הסר". מחק את המסנן שוב.

5. #####

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

6. # DIV / 0!

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

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

7. #NV!

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

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

8. #מספר

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

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

איך לעשות את זה:

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

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

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

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

9. הפניה מעגלית

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

איך פותרים את השגיאה:

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

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

10. E +

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

כך תוכל להציג את המספרים במלואם

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

1. סמנו את העמדות הרלוונטיות ברשימה שלכם.

2. הקש Ctrl ו- 1 בכדי שתוכל לעצב את התאים.

3. בתיבת הדו -שיח שנפתחת, בחר בכרטיסייה "מספרים" ובקטגוריה "מספר".

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

5. לאחר שתלחץ על אישור, Excel יראה לך את המספר בטבלה במלואה.

זיהוי אוטומטי של שגיאות אופייניות בפונקציות Excel

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

תחת "קובץ" עבור אל "אפשרויות".

בחר "נוסחאות".

סמן את התיבה שליד "אפשר בדיקת שגיאות רקע".

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

פונקציות אלה מזהות שגיאות בפונקציות Excel

באפשרותך להשתמש בפונקציות אלה כדי לבדוק במהירות שגיאות אופייניות בפונקציות Excel.

IFERROR

חלק מהנוסחאות אינן מלאות כפי שהדברים נראים. האם אתה רוצה למנוע הצגת שגיאות בפניך? פונקציית IFERROR מתאימה לכך. הוא מעריך את סוגי השגיאות #NV, #VALUE!, #REFERENCE!, #DIV / 0!, #NUMBER!, #NAME? או #NULL! הסוף. יש לו מבנה פשוט מכיוון שהוא מכיל רק את הערך ואת הערך_אם_טעות. הערך הראשון בודק את תקינות הפונקציה. התוכנית אמורה להציג את הערך האחרון בתוצאה אם יש שגיאה. הפונקציה שימושית מאוד כאשר אתה עורך טבלה במשך זמן רב.

דוגמה:

אתה מזין = IFERROR (B3 * C3; "הזן ערכים") או = IFERROR (B3 * C3; ""). במקרה הראשון תיזכר מה אתה עדיין צריך לעשות (אם יש שגיאה) עם הטקסט "הוסף ערכים". במקרה השני, התא שבדרך כלל היה מכיל את התוצאה פשוט נשאר ריק.

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

שגיאה

בקשר עם הפונקציה IFERROR, הפונקציה ISERROR יכולה גם להצביע על שגיאות קיימות. בדרך זו, הם אינם מעכבים חישובים נוספים ברשימה. לשם כך, הזן, לדוגמה: = IF (ISERROR (B3); "אנא בדוק"; B3 / 6). אם ניתן לבצע את החישוב, תקבל את התוצאה. אם Excel מזהה שגיאה בעת בדיקת B3, המילים "אנא בדוק" מופיעות בתא המתאים.

מסקנה: תקן שגיאות בפונקציות Excel במהירות ובקלות

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