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

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

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

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

اكتشف 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 فحص صيغ الورقة، وسرد الخلايا التي تمنع الأخطاء حاليًا ونوع كل خطأ، والتمييز بين "فشل البحث، وتم التعامل معه بشكل صحيح" من "المرجع المقطوع، المخفي بصمت" - ثم إصلاح الخلايا التي توافق عليها، مع نسخة احتياطية تلقائية قبل أي تغيير.

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

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

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

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

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

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

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

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