شاهد الفيديو (دقيقتان):
رأى كل فريق دعم فني (Help Desk) هذه الصيغة: دالة IF داخل IF داخل IF، وفي نهايتها صف من الأقواس المغلقة. تعمل الصيغة جيدًا إلى أن يضيف أحدهم شرطًا جديدًا وينسى قوسًا واحدًا، فيرد Excel برسالة "There’s a problem with this formula."
=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F"))))
يوفر Excel الحديث ثلاث أدوات أنظف لهذه المهمة. إليك متى تستخدم كل واحدة منها، مع الأمثلة نفسها الموجودة في الفيديو.
حمّل ملف التدريب: excel-nested-ifs-sample.xlsx (أرفق الملف عند النشر). يحتوي الملف على ورقة لكل مثال، والصيغ مكتوبة فيها مسبقًا.
1. IFS: قائمة اختبارات مرتبة
استخدم IFS عندما تكون لديك نطاقات تفحصها من الأعلى إلى الأسفل، مثل تحديد التقدير من الدرجة.
| A | B | C | |
|---|---|---|---|
| 1 | الاسم | الدرجة | التقدير |
| 2 | Abd Almouhimen Shamout | 94 | A |
| 3 | Abd Shamout | 83 | B |
| 4 | Mouhimen Dev | 71 | C |
| 5 | Abd A. Shamout | 58 | F |
=IFS(B2>=90,"A", B2>=80,"B", B2>=70,"C", TRUE,"F")
تكتب اختبارًا ثم نتيجته، وتكرر ذلك. يعيد Excel نتيجة أول اختبار تكون قيمته TRUE، لذلك فالترتيب مهم: ضع الشريحة الأعلى أولًا.
الزوج الأخير TRUE,"F" هو الخيار الشامل الذي يلتقط كل ما تبقى. القيمة TRUE صحيحة دائمًا، لذلك ينتهي إليها كل ما يفشل في الاختبارات الأخرى. إذا حذفته، فستعيد الدرجة 58 الخطأ #N/A، لأن IFS تعيد #N/A عندما لا يتحقق أي شرط. وتقترح وثائق Microsoft نفسها حيلة TRUE هذه.
نصيحة: يمكنك تقسيم الصيغة الطويلة على عدة أسطر في شريط الصيغة بالضغط على Alt + Enter. هذا يجعل قراءة IFS وSWITCH أسهل بكثير.
2. SWITCH: تطابقات تامة مع قيمة افتراضية
استخدم SWITCH عندما يمكن أن تحتوي خلية واحدة على قائمة معروفة من القيم، مثل رمز القسم.
=SWITCH(B2,
"IT", "Information Technology",
"HR", "Human Resources",
"FIN", "Finance",
"Unknown")
الوسيط الأول هو القيمة المراد فحصها. بعده تأتي أزواج من القيمة والنتيجة. أما الوسيط الأخير الذي لا زوج له فهو القيمة الافتراضية: إذا كان الرمز MKT، تعرض الخلية "Unknown" بدلًا من #N/A. ومن دون قيمة افتراضية، تعيد SWITCH الخطأ #N/A عندما لا يوجد تطابق.
الدالة SWITCH هنا أقصر من IFS، لأنك لا تكرر B2= في كل اختبار.
الجزء الصعب: SWITCH(TRUE, …) للشروط المعقدة
الدالة SWITCH العادية تقارن القيم التامة فقط. فالصيغة SWITCH(B2, ">=90", ...) لا تعني "90 أو أكثر"، بل تبحث عن النص ">=90".
الحيلة هي أن تضع TRUE بوصفها القيمة المراد فحصها. عندها يمكن أن تكون كل "قيمة" شرطًا كاملًا، وتعيد SWITCH نتيجة أول شرط تكون قيمته TRUE:
=SWITCH(TRUE,
AND(B2>=90, C2>=80), "Honors",
B2>=60, "Pass",
"Retake")
| الاسم | الدرجة | نسبة الحضور % | النتيجة |
|---|---|---|---|
| Abd Almouhimen Shamout | 94 | 96 | Honors |
| Abd Shamout | 92 | 70 | Pass (الحضور أقل من المطلوب لنيل Honors) |
| Mouhimen Dev | 71 | 88 | Pass |
| Abd A. Shamout | 58 | 90 | Retake |
يمكنك أن تجمع النطاقات وAND وOR وNOT في قائمة واحدة سهلة القراءة، وتبقى لديك قيمة افتراضية واضحة في النهاية. تستطيع IFS فعل الشيء نفسه، لكن كثيرين يفضلون SWITCH(TRUE) لأن القيمة الافتراضية مدمجة فيها.
3. XLOOKUP مع جدول شرائح
عندما تتغير الشرائح كثيرًا (نسب المكافآت، ومستويات العمولة، وأسعار الشحن)، لا تكتبها مباشرة داخل أي صيغة. ضعها في جدول صغير:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | الاسم | المبيعات | المكافأة | من | نسبة المكافأة % | |
| 2 | Abd Almouhimen Shamout | 12,400 | 5% | 0 | 0% | |
| 3 | Abd Shamout | 3,200 | 0% | 5,000 | 3% | |
| 4 | Mouhimen Dev | 21,500 | 8% | 10,000 | 5% | |
| 5 | Abd A. Shamout | 5,000 | 3% | 20,000 | 8% |
=XLOOKUP(B2, $E$2:$E$5, $F$2:$F$5, , -1)
B2: قيمة المبيعات المراد البحث عنها.$E$2:$E$5: عمود "من" في جدول الشرائح.$F$2:$F$5: المكافأة المطلوب إرجاعها.- الوسيط الرابع الفارغ يتجاوز خيار "إذا لم يتم العثور على القيمة".
- القيمة
-1هي وضع المطابقة (Match mode): تطابق تام، أو القيمة الأصغر التالية. القيمة 12,400 غير موجودة في الجدول، لذلك تأخذ XLOOKUP القيمة 10,000 وتعيد 5%.
هناك قاعدتان: يجب أن يبدأ جدول الشرائح من أدنى قيمة ممكنة (0 هنا)، وعلامات $ تثبّت الجدول عندما تسحب الصيغة إلى الأسفل. والآن، عندما تتغير مكافأة شريحة 10,000 إلى 6%، تعدّل خلية واحدة في الجدول فتتحدث كل المكافآت.
أي إصدار من Excel تحتاج؟
| الدالة | تعمل في |
|---|---|
| IFS, SWITCH | Excel 2019 و2021 و2024 وMicrosoft 365 وExcel للويب |
| XLOOKUP | Excel 2021 و2024 وMicrosoft 365 وExcel للويب (لا تعمل في Excel 2019 أو 2016) |
في الإصدارات الأقدم، ما زالت دوال IF المتداخلة تعمل، ويمكن للدالتين INDEX + MATCH (أو VLOOKUP مع TRUE) تنفيذ البحث في الشرائح. إذا شاركت ملفًا مع شخص يستخدم Excel 2016، فسيرى الخطأ #NAME? بدلًا من هذه الدوال.
قائمة التحقق
- هل تفحص نطاقات بالترتيب؟ استخدم IFS، وضع الشريحة الأعلى أولًا، واختم بـ
TRUE, "default". - هل لديك خلية واحدة بقائمة قيم معروفة؟ استخدم SWITCH واجعل القيمة الافتراضية الوسيط الأخير.
- هل لديك شروط مختلطة (نطاقات مع AND/OR)؟ استخدم SWITCH(TRUE, …).
- هل لديك شرائح تتغير؟ ضعها في جدول واستخدم XLOOKUP مع وضع المطابقة
-1. - تأكد أن كل من يفتح الملف لديه Excel 2019 أو أحدث (لـ IFS وSWITCH) أو 2021 أو أحدث (لـ XLOOKUP).
شاهد الفيديو:
Take Away Support: حلول تقنية تصلك في أقل من دقيقتين. بقلم عبد المهيمن شموط، mouhimen.dev.
المصادر
- دعم Microsoft، الدالة IFS: https://support.microsoft.com/en-us/excel/functions/ifs-function
- دعم Microsoft، الدالة SWITCH: https://support.microsoft.com/en-us/excel/switch-function
- دعم Microsoft، الدالة XLOOKUP: https://support.microsoft.com/en-us/excel/functions/xlookup-function
- دعم Microsoft، الدالة IF (الصيغ المتداخلة وتجنب الأخطاء الشائعة): https://support.microsoft.com/en-us/office/if-function-nested-formulas-and-avoiding-pitfalls-0b22ff44-f149-44ba-aeb5-4ef99da241c8
هذا المقال ترجمة عربية للمقال الأصلي باللغة الإنجليزية للكاتب عبد المهيمن شموط على mouhimen.dev.
