IFERROR + XLOOKUP: إنشاء صيغ Excel المقاومة للخطأ

تبدو #N/A المنتشرة عبر التقرير معطلة، لذا فإن رد الفعل هو تغليف كل شيء في IFERROR والمضي قدمًا. في بعض الأحيان هذا صحيح. في كثير من الأحيان، يتم دفن مشكلة حقيقية - بحث معطل، أو قسمة على صفر، أو خطأ مطبعي في المفتاح - تحت خلية فارغة مرتبة.

يغطي هذا الدليل الأدوات الثلاثة للتعامل مع أخطاء الصيغة، والفرق بينها، والأنماط التي تخفي أخطاء متوقع بينما تترك أخطاء غير متوقع مرئية.

إجابة سريعة

استخدم وسيطة XLOOKUP if_not_found أو إيفنا عند توقع وجود تطابق مفقود. استخدم IFERROR فقط عندما يجب أن يشترك كل خطأ محتمل من التعبير الملتف في نفس الإجراء الاحتياطي. بالنسبة للمقامات الصفرية والحالات المعروفة الأخرى، اختبر الحالة مباشرة باستخدام IF؛ فهو يوثق السبب ويترك الأخطاء غير ذات الصلة مرئية.

الوضع	النمط الموصى به	لماذا
قد لا يكون للبحث أي تطابق	XLOOKUP(...,"Not found")	يعالج فقط الخطأ المتوقع
قد يفوتك VLOOKUP/INDEX-MATCH	IFNA(formula,"Not found")	يبقي #REF! و#VALUE! مرئيين
قد يكون المقام صفرًا	IF(B2=0,"",A2/B2)	اختبارات الحالة الحقيقية
أي فشل يعني حقًا نفس الشيء	IFERROR(formula,fallback)	الصيد واسع النطاق المناسب

الأدوات الثلاثة

اكتشف IFERROR نوع الخطأ كل:

=IFERROR(A2/B2, 0)

إذا كان القسم ينتج #DIV/0!، #VALUE!، #REF! — أي شيء — تحصل على 0. هذا الاتساع هو أيضًا خطره: #REF! من عمود محذوف يستحق الاهتمام، وليس 0 صامتًا.

يلتقط إيفنا #N/A فقط:

=IFNA(VLOOKUP(A2, Prices!A:B, 2, FALSE), "not listed")

هذا هو ما تريده دائمًا تقريبًا في عمليات البحث: "لم يتم العثور على القيمة" هو شرط متوقع، في حين أن #VALUE! أو #REF! من نفس الصيغة لا يزال يشير إلى خطأ حقيقي.

XLOOKUP if_not_found المدمج يجعل الجزء الاحتياطي من البحث نفسه:

=XLOOKUP(A2, Prices!A:A, Prices!B:B, "not listed")

أنظف من التغليف، ويتعامل فقط مع الحالة التي لم يتم العثور عليها - ولا تزال هناك أخطاء أخرى تظهر.

القاعدة الأساسية: التعامل مع المتوقع، والكشف عن غير المتوقع

اطرح سؤالاً واحدًا لكل صيغة: ما هو الخطأ الطبيعي هنا؟

• بحث يمكن أن يخطئ → يعالج #N/A (البديل الاحتياطي لـ IFNA أو XLOOKUP).
• نسبة يمكن أن يكون مقامها صفرًا → اختبر المقام بشكل صريح:

=IF(B2=0, "", A2/B2)

اختبار الحالة أفضل من اكتشاف الخطأ: يوثق IF(B2=0,…) لماذا أن الإجراء الاحتياطي موجود، في حين أن IFERROR حول نفس القسم سيبتلع أيضًا #VALUE! الناتج عن النص في العمود B.

• كل شيء آخر → دعه يخطئ. #REF! المرئي يكلفك دقيقة واحدة؛ واحد غير مرئي يكلفك تقريرا خاطئا.

الأخطاء الكلاسيكية

بطانية IFERROR على عمود كامل. تقوم بإرسال تقرير يحتوي على 40 صفرًا، ثلاثة منها أصفار حقيقية و37 منها عبارة عن ورقة تمت إعادة تسميتها ولم يلاحظها أحد.

رياضيات التغذية IFERROR(..., ""). سلسلة فارغة في عمود رقمي تتحول إلى SUMs بشكل خاطئ وتنتج #VALUE! في الحساب - الخطأ الذي قمت بإخفائه يعود بعد عمودين، بعيدًا عن سببه.

اكتشاف الأخطاء التي تشير إلى بيانات قذرة. إذا حدث خطأ في VALUE(A2) لأن العمود يمزج النص والأرقام، يكون الإصلاح هو تنظيف العمود، ولا يتم اكتشاف الأعراض.

تدقيق ورقة مليئة بالأخطاء المخفية

هل تريد وراثة مصنف حيث يتم تغليف كل صيغة في IFERROR؟ حركتان:

1. قم بإحصاء ما تم القبض عليه. في عمود مساعد، كرر الصيغة الداخلية بدون الغلاف وقم بحساب الأخطاء مع إدخال =SUM(--ISERROR(...)) فوق النطاق.
2. يمكن لـ اطلب من أحد المساعدين تدقيقها. الذكاء الاصطناعي لـ Excel فحص صيغ الورقة، وسرد الخلايا التي تمنع الأخطاء حاليًا ونوع كل خطأ، والتمييز بين "فشل البحث، وتم التعامل معه بشكل صحيح" من "المرجع المقطوع، المخفي بصمت" - ثم إصلاح الخلايا التي توافق عليها، مع نسخة احتياطية تلقائية قبل أي تغيير.

يعتبر هذا النوع من تدقيق الصيغة مملًا وسريعًا بالنسبة للأداة التي تقرأ المصنف برمجيًا. إذا كنت تفضل وصف الهدف في جملة بدلاً من إنشاء أعمدة مساعدة، جرب الوظيفة الإضافية مجانًا - وللتعرف على الطريق باللغة الإنجليزية البسيطة لكتابة هذه الصيغ في المقام الأول، راجع إنشاء صيغة الذكاء الاصطناعي.

أنماط صيغة أكثر موثوقية

إرجاع الحالة بدلاً من إرجاع الصفر بصمت:

=IF(B2=0, "CHECK DENOMINATOR", A2/B2)

احتفظ بنتائج البحث المفقودة مختلفة عن النتيجة الفارغة:

=XLOOKUP(A2, Prices!A:A, Prices!B:B, NA())

التحقق من صحة المفتاح قبل البحث عنه:

=IF(TRIM(A2)="", "MISSING KEY", XLOOKUP(TRIM(A2), Prices!A:A, Prices!B:B, "NOT LISTED"))

هذه الحالات المرئية أسهل في العد والتصفية والتحقيق من السلاسل الفارغة. إذا كان العرض التقديمي النظيف مطلوبًا، فاحتفظ بصيغة التشخيص في عمود مساعد وقدم نتيجة منفصلة تواجه المستخدم.

المراجع الرسمية

• مايكروسوفت: وظيفة IFNA
• مايكروسوفت: وظيفة XLOOKUP

الأسئلة الشائعة

ما الفرق بين IFERROR وIFNA؟

IFERROR يلتقط كل أنواع الأخطاء؛ يلتقط IFNA #N/A فقط (الخطأ "لم يتم العثور عليه"). حول عمليات البحث، تفضل وسيطة if_not_found الخاصة بـ IFNA أو XLOOKUP بحيث تكون الأخطاء الهيكلية مثل #REF! البقاء مرئية.

هل يجب علي استخدام IFERROR في كل مكان؟

لا. تعامل فقط مع الأخطاء التي تتوقعها (عادةً أخطاء البحث والمقامات الصفرية) ودع الأخطاء غير المتوقعة تظهر. الخطأ المرئي هو المعلومات التشخيصية؛ التقرير المخفي هو تقرير غير صحيح في المستقبل.

كيف يمكنني العثور على كافة الخلايا التي بها أخطاء في Excel؟

اضغط على F5 → خاص → الصيغ → حدد الأخطاء فقط. يقوم Excel بتحديد كل خلية خطأ في الورقة. يمكن لمساعد الذكاء الاصطناعي أن يذهب إلى أبعد من ذلك ويصنفها حسب نوع الخطأ والسبب.