מאמר זה יעזור לך להבין את כל הסיבות לשגיאות #SPILL, כמו גם את הפתרונות לתיקון אותן ב-Excel 365.
#לשפוך! הוא סוג חדש של שגיאת Excel המתרחשת בעיקר כאשר נוסחה שמייצרת תוצאות חישוב מרובות מנסה להציג את התפוקות שלה בטווח דליפה אך הטווח הזה כבר מכיל כמה נתונים אחרים.
נתוני החסימה יכולים להיות כל דבר, כולל ערך טקסט, תאים ממוזגים, תו רווח רגיל, או אפילו כאשר אין מספיק מקום להחזיר את התוצאות. הפתרון פשוט, או נקה את הטווח של כל נתוני חסימה או בחר מערך ריק של תאים שאינם מכילים כל סוג של נתונים בו.
שגיאת שפיכה מתרחשת בדרך כלל בעת חישוב נוסחאות מערך דינמי, מכיוון שנוסחת המערך הדינמי היא זו שמוציאה תוצאות למספר תאים או למערך. בואו נסתכל לעומק ונבין מה גורם לשגיאה זו ב-Excel וכיצד לפתור אותה.
מה גורם לשגיאת שפיכה?
מאז השקת מערכים דינמיים בשנת 2018, נוסחאות Excel יכולות להתמודד עם מספר ערכים בו-זמנית ולהחזיר תוצאות ביותר מתא אחד. מערכים דינמיים הם מערכים הניתנים לשינוי גודל המאפשרים לנוסחאות להחזיר תוצאות מרובות לטווח של תאים בגליון העבודה על סמך נוסחה שהוזנה בתא בודד.
כאשר נוסחת מערך דינמית מחזירה תוצאות מרובות, תוצאות אלו נשפכות אוטומטית לתאים השכנים. התנהגות זו נקראת 'שפך' באקסל. וטווח התאים שאליו התוצאות נשפכות נקרא 'טווח שפיכה'. טווח הדליפה יתרחב או יתכווץ באופן אוטומטי בהתבסס על ערכי המקור.
אם נוסחה מנסה למלא טווח דליפה עם תוצאות מרובות אך נחסמת על ידי משהו בטווח הזה, אזי מתרחשת שגיאת #SPILL.
ל- Excel יש כעת 9 פונקציות המשתמשות בפונקציונליות של מערך דינמי כדי לפתור בעיות, אלה כוללות:
- סדר פעולות
- לְסַנֵן
- לְשַׁרבֵּב
- סוג
- מיין לפי
- RANDARRAY
- ייחודי
- XLOOKUP
- XMATCH
נוסחאות מערך דינמיות זמינות רק ב'Excel 365' והיא אינה נתמכת כרגע על ידי אף אחת מתוכנות ה-Excel הלא מקוונות (כלומר, Microsoft Excel 2016, 2019).
שגיאות שפיכה לא נגרמות רק על ידי חסימת נתונים, ישנן מספר סיבות לכך שאתה עלול לקבל שגיאת #שפיכה. תן לנו לחקור את המצבים השונים שבהם אתה עלול להיתקל ב-#SPILL! שגיאה וכיצד לתקן אותן.
טווח שפיכה אינו ריק
אחת הסיבות העיקריות לשגיאות הדליפה היא שטווח הדליפה אינו ריק. לדוגמה, אם אתה מנסה להציג 10 תוצאות, אבל אם יש נתונים כלשהם באחד מהתאים באזור הדליפה, הנוסחה מחזירה #SPILL! שְׁגִיאָה.
דוגמה 1:
בדוגמה למטה, הכנסנו את הפונקציה TRANSPOSE בתא C2 כדי להמיר את הטווח האנכי של התאים (B2:B5) לטווח אופקי (C2:F2). במקום להעביר את העמודה לשורה, Excel מראה לנו את ה-#SPILL! שְׁגִיאָה.
וכאשר תלחץ על תא הנוסחה, תראה גבול כחול מקווקו המציין את אזור/טווח הדליפה (C2:F2) הדרוש להצגת התוצאות כפי שמוצג להלן. כמו כן, תבחינו בשלט אזהרה צהוב ועליו סימן קריאה.
כדי להבין את הסיבה מאחורי השגיאה, לחץ על סמל האזהרה שליד השגיאה וראה את ההודעה בשורה הראשונה מסומנת באפור. כפי שאתה יכול לראות, כתוב 'טווח השפך אינו ריק' כאן.
הבעיה כאן היא שלתאים בטווח הדליפה D2 ו-E2 יש תווי טקסט (לא ריקים), ומכאן, השגיאה.
פִּתָרוֹן:
הפתרון פשוט, או נקה את הנתונים (או הזז או מחק) שנמצאים בטווח הדליפה או העבר את הנוסחה למקום אחר שבו אין חסימה.
ברגע שתמחק או תזיז את החסימה, אקסל יאכלס אוטומטית את התאים עם תוצאות הנוסחה. כאן, כאשר אנו מנקים את הטקסט ב-D2 ו-E2, הנוסחה מעבירה את העמודה לשורה כמתוכנן.
דוגמה 2:
בדוגמה למטה, למרות שטווח הדליפה נראה ריק, הנוסחה עדיין מציגה את ה- Spill! שְׁגִיאָה. זה בגלל שהשפך אינו ריק למעשה, יש לו אופי חלל בלתי נראה באחד התאים.
קשה לאתר תווי רווח או כל דמות בלתי נראית אחרת שמתחבאת במה שנראה כתאים ריקים. כדי למצוא תאים כאלה עם נתונים לא רצויים, לחץ על ה-Error floatie (סימן אזהרה) ובחר 'Select Obstructing Cells' מהתפריט וזה יעביר אותך לתא שמכיל את הנתונים החוסמים.
כפי שאתה יכול לראות, בצילום המסך שלהלן, לתא E2 יש שני תווי רווח. כשתנקה את הנתונים האלה, תקבל את הפלט המתאים.
לפעמים, התו הבלתי נראה יכול להיות טקסט המעוצב באותו צבע גופן כמו צבע המילוי של התא או ערך תא בעיצוב מותאם אישית עם קוד המספר ;;;. כאשר תעצב ערך תא בהתאמה אישית עם ;;;, הוא יסתיר כל דבר בתא זה, ללא קשר לצבע הגופן או לצבע התא.
טווח שפיכה מכיל תאים ממוזגים
לפעמים, ה-#SPILL! שגיאה מתרחשת כאשר טווח הדליפה מכיל את התאים הממוזגים. נוסחת מערך דינמי לא עובדת עם תאים ממוזגים. כדי לתקן זאת, כל שעליך לעשות הוא לבטל מיזוג תאים בטווח הדליפה או להעביר את הנוסחה לטווח אחר שאין בו תאים ממוזגים.
בדוגמה שלמטה, למרות שטווח הדליפה ריק (C2:CC8), הנוסחה מחזירה את שגיאת הנשיפה. זה בגלל שהתאים C4 ו-C5 ממוזגים.
כדי לוודא שתאים ממוזגים הם הסיבה שאתה מקבל את השגיאה, לחץ עלסימן אזהרה ואמת את הסיבה - 'טווח נשפך התמזג תא'.
פִּתָרוֹן:
כדי לבטל את מיזוג התאים, בחר את התאים הממוזגים, ולאחר מכן בכרטיסייה 'בית', לחץ על הלחצן 'מיזוג ומרכז' ובחר 'בטל מיזוג תאים'.
אם אתה מתקשה לאתר את התאים הממוזגים בגיליון האלקטרוני הגדול שלך, לחץ על האפשרות 'בחר תאים חוסמים' מתפריט סימן האזהרה כדי לדלג לתאים הממוזגים.
טווח שפיכה בטבלה
נוסחאות מערך שנשפכו אינן נתמכות בטבלאות Excel. יש להזין נוסחת מערך דינמי רק בתא בודד בודד. אם תזין נוסחת מערך שנשפכה בטבלה או כאשר אזור הדליפה נופל לטבלה, תקבל את השגיאה "שפך". כאשר זה קורה, נסה להמיר את הטבלה לטווח נורמלי או הזז את הנוסחה מחוץ לטבלה.
לדוגמה, כאשר נזין את נוסחת הטווח הנשפך הבאה בטבלת Excel, נקבל שגיאת Spill בכל תא בטבלה, לא רק בתא הנוסחה. הסיבה לכך היא ש-Excel מעתיק אוטומטית כל נוסחה שהוזנה בטבלה לכל תא בעמודה של הטבלה.
כמו כן, תקבל שגיאת שפיכה כאשר נוסחה תנסה לשפוך תוצאות בטבלה. בצילום המסך שלהלן, אזור הדליפה נופל בתוך הטבלה הקיימת, כך שאנו מקבלים שגיאת שפיכה.
כדי לאשר את הסיבה מאחורי שגיאה זו, לחץ על סימן האזהרה וראה את סיבת השגיאה - 'טווח שפיכה בטבלה'
פִּתָרוֹן:
כדי לתקן את השגיאה, תצטרך להחזיר את טבלת האקסל לטווח. כדי לעשות זאת, לחץ באמצעות לחצן העכבר הימני בכל מקום בטבלה, לחץ על 'טבלה' ולאחר מכן בחר באפשרות 'המר לטווח'. לחלופין, אתה יכול ללחוץ באמצעות לחצן העכבר השמאלי בכל מקום בתוך הטבלה, ואז לעבור ללשונית 'עיצוב טבלה' ולבחור באפשרות 'המר לטווח'.
טווח שפיכה אינו ידוע
אם Excel לא הצליח לקבוע את גודל המערך שנשפך, זה יפעיל את שגיאת השפך. לפעמים, הנוסחה מאפשרת למערך דינמי לשנות את הגודל בין כל מעברי חישוב. אם גודל המערך הדינמי ממשיך להשתנות במהלך חישובים ואינו מתאזן, זה יגרום ל-#SPILL! שְׁגִיאָה.
סוג זה של שגיאת שפיכה מופעל בדרך כלל בעת שימוש בפונקציות נדיפות כגון RAND, RANDARRAY, RANDBETWEEN, OFFSET ופונקציות INDIRECT.
לדוגמה, כאשר אנו משתמשים בנוסחה שלהלן בתא B3, אנו מקבלים את שגיאת המשחק:
=SEQUENCE(RANDBETWEEN(1, 500))בדוגמה, הפונקציה RANDBETWEEN מחזירה מספר שלם אקראי בין המספרים 1 ל-500, והפלט שלה משתנה ללא הרף. והפונקציה SEQUENCE לא יודעת כמה ערכים לייצר במערך שפיכה. לפיכך, השגיאה #SPILL.
אתה יכול גם לאשר את סיבת השגיאה על ידי לחיצה על סימן האזהרה - 'טווח שפיכה אינו ידוע'.
פִּתָרוֹן:
כדי לתקן את השגיאה עבור נוסחה זו, הבחירה היחידה שלך היא להשתמש בנוסחה אחרת לחישוב שלך.
טווח שפיכה גדול מדי
לפעמים אתה עשוי להפעיל נוסחה המפלטת טווח נשפך גדול מכדי שגליון העבודה יוכל לטפל בו, והיא עשויה להתרחב מעבר לקצוות גליון העבודה. כשזה יקרה אתה עלול לקבל #SPILL! שְׁגִיאָה. כדי לפתור בעיה זו, אתה יכול לנסות להפנות לטווח מסוים או לתא אחד במקום לעמודות שלמות או להשתמש בתו '@' כדי לאפשר חיתוך מרומז
בדוגמה שלמטה, אנו מנסים לחשב 20% ממספרי המכירות בעמודה A ולהחזיר את התוצאות בעמודה B, אך במקום זאת, אנו מקבלים שגיאת Spill.
הנוסחה ב-B3 מחשבת 20% מהערך ב-A3, ואז 20% מהערך ב-A4, וכן הלאה. זה מייצר למעלה ממיליון תוצאות (1,048,576) ושופך את כולן בעמודה B החל בתא B3, אבל זה יגיע לסוף גליון העבודה. אין מספיק מקום כדי להציג את כל הפלטים, כתוצאה מכך, אנו מקבלים שגיאת #SPILL.
כפי שאתה יכול לראות, הסיבה לשגיאה זו היא ש- 'טווח השפיכה גדול מדי'.
פתרונות:
כדי לפתור בעיה זו, נסה לשנות את העמודה כולה עם טווח רלוונטי או הפניה של תא בודד, או הוסף את האופרטור @ כדי לבצע חיתוך מרומז.
תיקון 1: אתה יכול לנסות להפנות טווחים במקום עמודות שלמות. כאן, אנו משנים את כל הטווח A:A עם A3:A11 בנוסחה, והנוסחה תאכלס אוטומטית את הטווח בתוצאות.
תיקון 2: החלף את העמודה כולה רק בהפניה לתא באותה שורה (A3), ולאחר מכן העתק את הנוסחה לאורך הטווח באמצעות ידית המילוי.
תיקון 3: אתה יכול גם לנסות להוסיף את האופרטור @ לפני ההפניה כדי לבצע חיתוך מרומז. זה יציג את הפלט בתא הנוסחה בלבד.
לאחר מכן, העתק את הנוסחה מתא B3 לשאר הטווח.
הערה: כאשר אתה עורך נוסחה שנשפכה, אתה יכול לערוך רק את התא הראשון באזור/טווח הדליפה. אתה יכול לראות את הנוסחה בתאים אחרים של טווח הדליפה, אבל הם יהיו באפור ולא ניתן לעדכן.
נגמר הזיכרון
אם תפעיל נוסחת מערך שנשפכה שגורמת ל-Excel להיגמר בזיכרון, היא עלולה להפעיל את השגיאה #SPILL. בנסיבות אלה, נסה להפנות למערך או טווח קטן יותר.
לא מזוהה / סתירה
אתה יכול גם לקבל שגיאת משחק גם כאשר Excel לא מזהה או לא יכול ליישב את הסיבה לשגיאה. במקרים כאלה, בדוק שוב את הנוסחה שלך וודא שכל הפרמטרים של הפונקציות נכונים.
עכשיו, אתה יודע את כל הסיבות והפתרונות ל-#SPILL! שגיאות ב- Excel 365.