שגיאת #REF! באקסל: למה היא קורית ואיך מתקנים

#REF! פירושה שנוסחה מפנה לתא שלא נמצא שם. הסיבה הרגילה היא שורה, עמודה או גיליון שנמחקו: כשעמודה C נמחקת, אקסל כותב מחדש את =B2*C2 כ-=B2*#REF!, והתוצאה היא #REF! מאז והלאה. הקישו Ctrl+Z (או Cmd+Z ב-Mac) מיד אחרי המחיקה כדי לקבל בחזרה את העמודה ואת הנוסחה.

נוסחאותאיפוס

D2fx

=B2*#REF!

	A	B	C	D
1	Product	Price	Qty	Total
2	Apple	1.2	10	#REF!
3	Pear	1.5	20	#REF!
4	Plum	0.8	15	#REF!
5	Bread	2.4	5	#REF!

#REF! הנוסחה מפנה לתא שלא קיים.

עמודת הכמות נמחקה והוקלדה מחדש, אבל הנוסחה עדיין אומרת #REF!: אקסל אף פעם לא מתקן הפניה אחרי שהיא נעלמה. לחצו על D2, החליפו את #REF! ב-C2 והקישו Enter. כל העמודה מתעדכנת, ו-D2 מציג 12.

איך #REF! נכנסת לנוסחה

אקסל כותב #REF! בתוך נוסחה בכל פעם שתא שהנוסחה השתמשה בו נעלם:

מחיקה של תאים בתוך טווח בטוחה: =SUM(B2:D2) הופכת ל-=SUM(B2:C2) כשעמודה C נמחקת. מחיקה של התא הראשון או האחרון בטווח רק מקצרת אותו. לכן =SUM(B2:D2) בטוחה יותר מ-=B2+C2+D2, שהופכת ל-=B2+#REF!+C2.

למה VLOOKUP מחזירה #REF!

הארגומנט השלישי של VLOOKUP סופר עמודות בתוך טווח הטבלה. אם הוא גדול ממספר העמודות בטווח, התוצאה היא #REF!.

נוסחאותאיפוס

F2fx

=VLOOKUP(E2,A2:C6,4,FALSE)

	A	B	C	D	E	F
1	Product	Price	Stock		Look for	Stock
2	Apple	1.2	40		Pear	#REF!
3	Pear	1.5	25
4	Plum	0.8	60
5	Bread	2.4	12
6	Milk	1.1	30

#REF! הנוסחה מפנה לתא שלא קיים.

ב-A2:C6 יש שלוש עמודות, ולכן 4 לא קיימת. שנו את ה-4 ל-3 ו-F2 מציג 25. זה קורה בעיקר אחרי מחיקה של עמודה מטבלת החיפוש: הטווח מצטמצם, ומספר העמודה הקבוע לא. XLOOKUP או INDEX עם MATCH נמנעות מזה כי הן מציינות את עמודת ההחזרה ישירות, כמו ב-=XLOOKUP(E2,A2:A6,C2:C6). ב-VLOOKUP יש את שאר הארגומנטים שלה.

#REF! עם INDEX ו-OFFSET

INDEX מחזירה #REF! כשמספר השורה או העמודה מחוץ לטווח שלה, ו-OFFSET כשהיא זזה מעל שורה 1 או לפני עמודה A.

נוסחאותאיפוס

B2fx

=INDEX(A2:A6,6)

	A	B	C
1	Score	Result	What it asks for
2	88	#REF!	6th value of 5
3	72	95	3rd value of 5
4	95	#REF!	2 rows above A2
5	64	81	4 rows below A2
6	81

#REF! הנוסחה מפנה לתא שלא קיים.

ב-A2:A6 יש חמישה ציונים, ולכן INDEX(A2:A6,6) היא #REF! בעוד ש-INDEX(A2:A6,3) מחזירה 95. שורה 0 לא קיימת, ולכן OFFSET(A2,-2,0) היא #REF!, ו-OFFSET(A2,4,0) נוחתת על A6: 81. כשהמיקום מגיע מנוסחה אחרת (MATCH, COUNT), בדקו קודם את הנוסחה ההיא. עוד בעמוד על INDEX.

גם INDIRECT נותנת #REF! כשהטקסט שלה אינו כתובת חוקית (=INDIRECT("ZZZ1"), כי העמודה האחרונה היא XFD) או מצביע לתוך חוברת עבודה סגורה.

#REF! בהעתקת נוסחה

הפניה יחסית זזה יחד עם הנוסחה. העתיקו אותה מספיק למעלה או הצידה וההפניה נופלת מהגיליון:

C3:  =B2*2        (one row up, one column back)
copy C3 to B2:  =A1*2
copy C3 to A2:  =#REF!*2    (there is no column before A)

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

מציאה והסרה של כל #REF! בחוברת עבודה

1. הקישו Ctrl+F (או Cmd+F ב-Mac), הקלידו #REF!, פתחו את אפשרויות, הגדירו את חפש ב ל-נוסחאות, ולחצו על חפש הכל. הרשימה מציגה כל נוסחה עם הפניה שבורה.
2. כדי לתקן הרבה בבת אחת, השתמשו ב-Ctrl+H (ב-Mac: Control+H): חפשו #REF! והחליפו בהפניה הנכונה, אבל רק כשכל התאמה צריכה לקבל את אותו תא.
3. בדקו את נוסחאות > מנהל השמות: שם שבעמודת מפנה אל שלו מופיע #REF! שובר כל נוסחה שמשתמשת בו.
4. אם הנתונים שנמחקו אבדו והנוסחה כבר לא נחוצה, בחרו את התאים והחליפו את הנוסחאות בערכים שלהן (העתקה, ואז בית > הדבק > ערכים). ערכי שגיאה נשארים שגיאות, אז מחקו את התאים האלה אחר כך.

תיקון חיפוש שמחזיר #REF!

נוסחאותאיפוס

F2fx

	A	B	C	D	E	F
1	Product	Price	Stock		Look for	Stock
2	Apple	1.2	40		Plum
3	Pear	1.5	25
4	Plum	0.8	60
5	Bread	2.4	12
6	Milk	1.1	30

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

תורכם: =VLOOKUP(E2,A2:C6,4,FALSE) החזירה #REF!. כתבו ב-F2 חיפוש עובד שמחזיר את המלאי של המוצר שב-E2.

בדיקההצגת התשובה

כל חיפוש שמחזיר כאן 60 והולך אחרי הנתונים עובר: ה-VLOOKUP עם עמודה 3, =XLOOKUP(E2,A2:A6,C2:C6) או =INDEX(C2:C6,MATCH(E2,A2:A6,0)).