يعني #REF! أن صيغة تشير إلى خلية غير موجودة. والسبب المعتاد صف أو عمود أو ورقة محذوفة: عند حذف العمود C، يعيد الاكسل كتابة =B2*C2 بالشكل =B2*#REF!، وتصبح النتيجة #REF! من حينها. اضغط Ctrl+Z (أو Cmd+Z على Mac) مباشرة بعد الحذف لاستعادة العمود والصيغة.
| 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! في الصيغة كلما اختفت خلية كانت الصيغة تستخدمها:
| ما فعلته | تصبح =B2*C2 في D2 |
|---|---|
| حذفت العمود C | =B2*#REF! |
| حذفت الصف 2 | تُحذف الصيغة مع صفها؛ والصيغ في الصفوف الأخرى التي كانت تشير إلى الصف 2 تحصل على #REF! |
| حذفت الورقة التي تشير إليها صيغة | =#REF!B2*2 (لصيغة مثل =Prices!B2*2) |
| قصصت خلية ولصقتها فوق خلية تستخدمها الصيغة | #REF! مكان المرجع الذي كُتب فوقه |
حذف خلايا داخل نطاق آمن: تصبح =SUM(B2:D2) بالشكل =SUM(B2:C2) عند حذف العمود C. وحذف الخلية الأولى أو الأخيرة من النطاق لا يفعل سوى تقليصه. لذا =SUM(B2:D2) أكثر أمانًا من =B2+C2+D2، التي تصبح =B2+#REF!+C2.
لماذا تعيد VLOOKUP الخطأ #REF!
يعدّ الوسيط الثالث في VLOOKUP الأعمدة داخل نطاق الجدول. وإذا كان أكبر من عدد أعمدة النطاق، تكون النتيجة #REF!.
| 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.
| 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! في المصنف وحذفها
- اضغط Ctrl+F (أو Cmd+F على Mac)، واكتب
#REF!، وافتح خيارات، واضبط البحث في على الصيغ، وانقر بحث عن الكل. تعرض القائمة كل صيغة فيها مرجع مكسور. - لإصلاح الكثير دفعة واحدة، استخدم Ctrl+H (أو Control+H على Mac): ابحث عن
#REF!واستبدله بالمرجع الصحيح، لكن فقط عندما يجب أن تحصل كل النتائج على الخلية نفسها. - افحص صيغ > إدارة الأسماء: الاسم الذي يعرض عمود يشير إلى فيه
#REF!يكسر كل صيغة تستخدمه. - إذا ضاعت البيانات المحذوفة ولم تعد الصيغة لازمة، فحدد الخلايا واستبدل الصيغ بقيمها (نسخ، ثم الصفحة الرئيسية > لصق > قيم). وقيم الأخطاء تبقى أخطاء، فاحذف تلك الخلايا بعد ذلك.
إصلاح بحث يعيد #REF!
| 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)).
الأسئلة الشائعة
ماذا يعني #REF! في الاكسل؟
تشير الصيغة إلى خلية غير موجودة. وفي الغالب حُذف صف أو عمود أو ورقة كانت الصيغة تستخدمها، فاستبدل الاكسل المرجع بالقيمة #REF!، فأصبحت =B2*C2 بالشكل =B2*#REF!. وتعيد VLOOKUP وINDEX أيضًا #REF! عندما يكون رقم العمود أو الصف أكبر من النطاق.
كيف أصلح #REF! بعد حذف عمود؟
اضغط Ctrl+Z (أو Cmd+Z على Mac) فورًا للتراجع عن الحذف. وإذا فات الأوان، انقر الصيغة واستبدل #REF! بالخلية التي يجب أن تستخدمها، ثم انسخ الصيغة إلى الأسفل من جديد.
لماذا تعيد VLOOKUP الخطأ #REF!؟
لأن رقم العمود أكبر من عدد أعمدة نطاق الجدول. تطلب =VLOOKUP(E2,A2:C6,4,FALSE) العمود الرابع من نطاق فيه 3 أعمدة. استخدم 3، أو وسّع النطاق إلى A2:D6.
كيف أجد كل أخطاء #REF! في المصنف؟
اضغط Ctrl+F (أو Cmd+F على Mac)، وابحث عن #REF!، واضبط البحث في على الصيغ وانقر بحث عن الكل. يعرض الاكسل كل صيغة تحتوي على مرجع مكسور. وافحص صيغ > إدارة الأسماء أيضًا: قد تشير الأسماء إلى #REF! بعد الحذف.
كيف أتجنب #REF! عند حذف صفوف أو أعمدة؟
أشر إلى نطاقات بدلًا من خلايا مفردة. تتقلص =SUM(B2:D2) إلى =SUM(B2:C2) عند حذف العمود C أو D، بينما تتحول =B2+C2+D2 إلى =B2+#REF!+C2. وعمليات البحث التي تسمّي عمود الإرجاع، مثل =XLOOKUP(E2,A2:A6,C2:C6)، تصمد أمام إدراج الأعمدة وحذف الأعمدة التي لا تستخدمها.
