כיצד לתקן #REF! שגיאה

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

הדוגמה הבאה משתמשת בנוסחה ‎=SUM(B2,C2,D2)‎ בעמודה E.

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

אם תמחק את עמודה B, C או D, הדבר יגרום #REF! שגיאת ‎#REF!‎. במקרה זה נמחק את עמודה C (2007 Sales), והנוסחה תקרא כעת =SUM(B2,#REF!,C2). בעת שימוש בהפניות מפורשות לתאים באופן זה (כאשר אתה מפנה לכל תא בנפרד, מופרד באמצעות פסיק) וממחק שורה או עמודה שאליהן מתבצעת הפניה, ל- Excel אין אפשרות לפתור אותה, ולכן היא מחזירה את #REF! שגיאת ‎#REF!‎. זוהי הסיבה העיקרית לכך שלא מומלץ להשתמש בהפניות מפורשות לתאים בפונקציות.

דוגמה של #REF! נגרמת כתוצאה ממחיקת עמודה.

פתרון

אם מחקת בטעות שורות או עמודות, באפשרותך לבחור באופן מיידי בלחצן בטל בסרגל הכלים לגישה מהירה (או להקיש CTRL+Z) כדי לשחזר אותן.
שנה את הנוסחה לשימוש בהפניה לטווח במקום לתאים בודדים, למשל ‎=SUM(B2:D2)‎. כעת תוכל למחוק כל עמודה בטווח הסכום, ו- Excel יתאים באופן אוטומטי את הנוסחה. ניתן גם להשתמש ב- ‎=SUM(B2:B5)‎ עבור סכום של שורות.

בדוגמה הבאה, =VLOOKUP(A8,A2:D5,5,FALSE) יחזיר #REF! מכיוון שהוא מחפש ערך להחזרה מעמודה 5, אך טווח ההפניה הוא A:D, שהוא 4 עמודות בלבד.

דוגמה של נוסחת VLOOKUP עם טווח שגוי. הנוסחה היא =VLOOKU(A8,A2:D5,5,FALSE). אין עמודה חמישית בטווח VLOOKUP, כך ש- 5 גורם #REF! שגיאת ‎#REF!‎.

פתרון

התאם את הטווח כך שהוא יהיה גדול יותר או הקטן את ערך בדיקת המידע של העמודות כך שיתאים לטווח ההפניה. ‎=VLOOKUP(A8,A2:E5,5,FALSE)‎ יהיה טווח הפניה חוקי, וכך גם ‎=VLOOKUP(A8,A2:D5,4,FALSE)‎.

בדוגמה זו, הנוסחה =INDEX(B2:E5,5,5) מחזירה ערך #REF! מכיוון שטווח ה- INDEX הוא 4 שורות על 4 עמודות, אך הנוסחה מבקשת להחזיר את התוכן בשורה ה- 5 ובעמודה ה- 5.

דוגמה של נוסחת INDEX עם הפניה לטווח לא חוקי. הנוסחה היא ‎=INDEX(B2:E5,5,5)‎, אך הטווח הוא רק 4 שורות על 4 עמודות.

פתרון

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

בדוגמה הבאה, פונקציית INDIRECT מנסה להפנות לחוברת עבודה שנסגרה וגורמת לשגיאה #REF! שגיאת ‎#REF!‎.

דוגמה של #REF! נגרמת על-ידי הפניה ל- INDIRECT לחוברת עבודה סגורה.

פתרון

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

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

הפניות מחושבות חוברות עבודה מקושרות אינן נתמכות.

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

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

הערה: OLE היא טכנולוגיה שניתן להשתמש בה כדי לשתף מידע בין תוכניות.

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

הערה: חילופי מידע דינאמיים (DDE)הוא פרוטוקול מבוסס להחלפת נתונים בין תוכניות המבוססות על Microsoft Windows.

בעיות מאקרו

אם מאקרו מזין פונקציה בגליון העבודה המפנה לתא שמעל לפונקציה, והתא המכיל את הפונקציה נמצא בשורה 1, הפונקציה תחזיר #REF! מאחר שאין תאים מעל שורה 1. בדוק את הפונקציה כדי לראות אם ארגומנט מפנה לתא או לטווח תאים שאינו חוקי. פעולה זו עשויה לדרוש עריכת המאקרו ב- Visual Basic עורך (VBE) כדי לקחת בחשבון מצב זה.