الدالة IF، الصيغ المتداخلة وتجنب المخاطر

تتيح لك الدالة IF إمكانية إجراء مقارنة منطقية بين قيمة وما تتوقعه باختبار إرجاع نتيجة وشرط إذا كانت القيمة True أو False.

  • إذا كان (شيء ما True، فقم بشيء ما، وبخلاف ذلك قم بتنفيذ شيء آخر)

ولذا يمكن أن تكون لعبارة IF نتيجتان. النتيجة الأولى هي إذا كانت مقارنتك True، والثانية إذا كانت المقارنة False.

تُعد عبارات IF فعالة للغاية، وتشكل أساساً للعديد من نماذج جداول البيانات، لكنها أيضاً السبب الجذري للعديد من مشاكل جداول البيانات. وبشكل مثالي، يجب تطبيق عبارة IF على الحد الأدنى من الشروط، على سبيل المثال ذكر/أنثى، نعم/لا/ربما، على سبيل المثال لا الحصر، لكن في بعض الأحيان قد تحتاج إلى تقييم سيناريوهات أكثر تعقيداً تتطلب تداخل* أكثر من 3 دالات IF معاً.

* "التداخل" يشير إلى ممارسة جمع دالات متعددة معاً في صيغة واحدة.

استخدم الدالة IF، إحدى الدالات المنطقية، لإرجاع قيمة إذا كان الشرط صحيحاً وقيمة أخرى إذا كان الشرط خاطئاً.

بناء الجملة

‎IF(logical_test, value_if_true, [value_if_false])‎

على سبيل المثال:

  • =IF(A2>B2,"تجاوز الميزانية","موافق")

  • ‎=IF(A2=B2,B4-A4,»»)‎

اسم الوسيطة

الوصف

logical_test   

(مطلوبة)

القيمة التي تريد اختبارها.

value_if_true   

(مطلوبة)

القيمة التي تريد إرجاعها إذا كانت نتيجة logical_test تساوي TRUE.

value_if_false   

(اختيارية)

القيمة التي تريد إرجاعها إذا كانت نتيجة logical_test تساوي FALSE.

الملاحظات

في حين يتيح لك برنامج Excel تضمين ما يصل إلى 64 دالة IF مختلفة، فلا يُنصح القيام بذلك على الإطلاق. ما هو السبب؟

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

  • يمكن أن يصبح الاحتفاظ بعبارات IF المتعددة في غاية الصعوبة، لا سيما عند العودة في وقت لاحق ومحاولة معرفة ما كنت تحاول أنت أو شخص آخر تنفيذه.

إذا وجدت نفسك تستخدم عبارة IF التي تبدو وكأنها تزيد بدون أي نقاط نهاية في العرض، فقد حان الوقت للتوقف عن استخدام الماوس وإعادة التفكير في الاستراتيجية التي تستخدمها.

دعنا نتعرف على كيفية إنشاء عبارة IF المعقدة والمتداخلة بشكل صحيح باستخدام عبارات IF متعددة، ومتى يجب أن تعرف أن الوقت قد حان لاستخدام أداة أخرى في Excel.

الأمثلة

فيما يلي مثال لعبارة IF متداخلة وقياسية نسبياً لتحويل نقاط اختبار الطالب إلى ما يعادلها من درجات بالأحرف.

عبارة IF متداخلة مركبة، الصيغة في E2 هي =IF(B2>97,"A+",IF(B2>93,"A",IF(B2>89,"A-",IF(B2>87,"B+",IF(B2>83,"B",IF(B2>79,"B-",IF(B2>77,"C+",IF(B2>73,"C",IF(B2>69,"C-",IF(B2>57,"D+",IF(B2>53,"D",IF(B2>49,"D-","F"))))))))))))
  • =IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))

    تتبع عبارة IF المتداخلة والمعقدة منطقاً مباشراً:

  1. إذا كانت "نقاط الاختبار" (في الخلية D2) أكبر من 89، فإن الطالب سيحصل على الدرجة A

  2. إذا كانت "نقاط الاختبار" أكبر من 79، فإن الطالب سيحصل على الدرجة B

  3. إذا كانت "نقاط الاختبار" أكبر من 69، فإن الطالب سيحصل على الدرجة C

  4. إذا كانت "نقاط الاختبار" أكبر من 59، فإن الطالب سيحصل على الدرجة D

  5. وبخلاف ذلك، سيحصل الطالب على الدرجة F

هذا المثال تحديدا آمن نسبيا لأنه من غير المرجح أن يتغير الارتباط بين درجات الاختبار ودرجات الحروف، لذلك لن يتطلب الكثير من الصيانة. ولكن إليك فكرة – ماذا لو كنت بحاجة إلى تقسيم الدرجات بين A+و A و A- (وهكذا)؟ الآن يجب إعادة كتابة العبارة IF الخاصة بالشروط الأربعة للحصول على 12 شرطا! إليك كيف ستبدو الصيغة الآن:

  • =IF(B2>97,"A+",IF(B2>93,"A",IF(B2>89,"A-",IF(B2>87,"B+",IF(B2>83,"B",IF(B2>79,"B-", IF(B2>77,"C+",IF(B2>73,"C",IF(B2>69,"C-",IF(B2>57,"D+",IF(B2>53,"D",IF(B2>49,"D-","F"))))))))))))

لا يزال دقيقا من الناحية الوظيفية وسيعمل كما هو متوقع، ولكنه يستغرق وقتا طويلا للكتابة وأطول لاختباره للتأكد من أنه يقوم بما تريده. هناك مشكلة أخرى معلنة وهي أنه كان عليك إدخال الدرجات ودرجات الحروف المكافئة يدويا. ما هي احتمالات حدوث خطأ كتابي عن طريق الخطأ؟ تخيل الآن محاولة القيام بذلك 64 مرة مع ظروف أكثر تعقيدا! بالتأكيد، هذا ممكن، ولكن هل تريد حقا اخضع نفسك لهذا النوع من الجهد والأخطاء المحتملة التي سيكون من الصعب جدا اكتشافها؟

تلميح: تتطلب كل دالة في Excel قوسي فتح وإغلاق (). سيحاول Excel مساعدتك على تصور ما يحدث عند تلوين أجزاء مختلفة من الصيغة عند تحريرها. على سبيل المثال، إذا كنت تريد تعديل الصيغة أعلاه، عند نقل المؤشر بعد كل قوس من أقواس النهاية ")"، فإن قوس الفتح المقابل سيتحول إلى اللون نفسه. قد يكون ذلك مفيداً في الصيغ المتداخلة المعقدة عندما تحاول معرفة إذا كان لديك ما يكفي من الأقواس المطابقة.

الأمثلة الإضافية

فيما يلي مثال شائع جداً لاحتساب "عمولة المبيعات" استناداً إلى مستويات تحقيق الأرباح.

الصيغة في الخلية D9 هي F(C9>15000,20%,IF(C9>12500,17.5%,IF(C9>10000,15%,IF(C9>7500,12.5%,IF(C9>5000,10%,0)))))
  • =IF(C9>15000,20%,IF(C9>12500,17.5%,IF(C9>10000,15%,IF(C9>7500,12.5%,IF(C9>5000,10%,0)))))

تشير هذه الصيغة إلى أنه إذا (IF)‏ كان (C9 أكبر من 15000، فإنه يتم إرجاع 20%، وإذا (IF)‏ كان (C9 أكبر من 12500، فإنه يتم إرجاع 17.5%، وهكذا...

على الرغم من أنها تشبه إلى حد كبير المثال السابق للدرجات، إلا أن هذه الصيغة تعد مثالا رائعا على مدى صعوبة الاحتفاظ بعبارات IF الكبيرة – ماذا يجب أن تفعل إذا قررت مؤسستك إضافة مستويات تعويض جديدة وربما حتى تغيير قيم الدولار أو النسبة المئوية الحالية؟ سيكون لديك الكثير من العمل على يديك!

تلميح: يمكنك إدراج فواصل الأسطر في شريط الصيغة لتسهيل قراءة الصيغ الطويلة. ما عليك سوى الضغط على ALT+ENTER قبل النص الذي تريد إجراء التفاف لسطر جديد له.

فيما يلي مثال لسيناريو العمولة مع منطق غير مرتب:

الصيغة في الخلية D9 هي غير مرتبة كما يلي =IF(C9>5000,10%,IF(C9>7500,12.5%,IF(C9>10000,15%,IF(C9>12500,17.5%,IF(C9>15000,20%,0)))))

هل يمكنك رؤية الخطأ؟ مقارنة ترتيب مقارنات الإيرادات بالمثال السابق. ما هي الطريقة التي تسير بها هذه الطريقة؟ هذا صحيح، إنه من الأسفل إلى الأعلى (من 5000 دولار إلى 15000 دولار)، وليس العكس. ولكن لماذا يجب أن يكون هذا الأمر كبيرا جدا؟ إنه أمر كبير لأن الصيغة لا يمكنها اجتياز التقييم الأول لأي قيمة يزيد حجمها عن 5000 دولار أمريكي. لن لنقل أنك حصلت على إيرادات تبلغ 12500 دولار أمريكي – سوف تعود العبارة IF بنسبة 10٪ لأنها أكبر من 5000 دولار أمريكي، وستتوقف عند هذا الحد. قد تكون هذه مشكلة كبيرة لأنه في الكثير من الحالات، لا يلاحظ أحد هذه الأنواع من الأخطاء حتى يكون لها تأثير سلبي. وبالتالي، مع العلم بأن هناك بعض المخاطر الخطيرة مع عبارات IF المتداخلة المعقدة، ما الذي يمكنك فعله؟ في معظم الحالات، يمكنك استخدام الدالة VLOOKUP بدلا من إنشاء صيغة معقدة باستخدام الدالة IF. باستخدام VLOOKUP، ستحتاج أولا إلى إنشاء جدول مرجعي:

الصيغة في الخلية D2 هي =VLOOKUP‏(C2,C5:D17,2,TRUE)
  • =VLOOKUP‏(C2,C5:D17,2,TRUE)

تشير هذه الصيغة إلى البحث عن القيمة في الخلية C2 ضمن نطاق C5:C17. إذا تم العثور على القيمة، فسيتم إرجاع القيمة المطابقة من الصف نفسه في العمود D.

الصيغة في الخلية C9 هي =VLOOKUP‏(B9,B2:C6,2,TRUE)
  • =VLOOKUP‏(B9,B2:C6,2,TRUE)

وبشكل مماثل، تبحث هذه الصيغة عن القيمة الموجودة في الخلية B9 ضمن نطاق B2:B22. إذا تم العثور على القيمة، فسيتم إرجاع القيمة المطابقة من الصف نفسه في العمود C.

ملاحظة: تستخدم دالاتا VLOOKUP الوسيطة TRUE في نهاية الصيغ، مما يعني أننا نريد منهما البحث عن مطابقة تقريبية. بمعني آخر، سوف تطابق القيم المحددة في جدول البحث، وكذلك أي قيم تقع بينهما. في هذه الحالة، يجب فرز جداول البحث بالترتيب التصاعدي، من الأصغر إلى الأكبر.

يتم تناول VLOOKUP بتفاصيل أكثر هنا ،ولكن هذا بالتأكيد أبسط بكثير من العبارة IF المتداخلة المعقدة ذات المستوى 12! كما توجد فوائد أخرى أقل وضوحاً أيضاً:

  • جداول مراجع VLOOKUP متاحة ويمكن للجميع الاطلاع عليها.

  • يمكن تحديث قيم الجدول بسهولة ولن تحتاج أبداً إلى تغيير الصيغة في حال تغيير الشروط.

  • إذا كنت لا تريد تمكين الأشخاص من الاطلاع على جدول المراجع أو التداخل معه، فما عليك سوى وضعه في ورقة عمل أخرى.

هل تعلم؟

توجد الآن دالة IFS التي يمكنها استبدال عبارات IF المتعددة المتداخلة باستخدام دالة واحدة. لذلك بدلاً من مثال الدرجات الأولية، الذي يحتوي على 4 دالات IF متداخلة:

  • =IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))

يمكن أن يكون ذلك أسهل بكثير باستخدام دالة IFS واحدة:

  • =IFS(D2>89,"A",D2>79,"B",D2>69,"C",D2>59,"D",TRUE,"F")

تُعد الدالة IFS رائعة لأنه لا داعي للقلق بشأن كل عبارات IF والأقواس.

ملاحظة: تتوفر هذه الميزة فقط إذا كان لديك اشتراك في Microsoft 365. إذا كنت مشتركاً في Microsoft 365، فتأكد من أن لديك الإصدار الأخير من Office.

ارتباط لتجربة أو شراء Microsoft 365

هل تحتاج إلى مزيد من المساعدة؟

يمكنك دائماً الاستفسار من أحد الخبراء في مجتمع Excel التقني أو الحصول على الدعم في مجتمع الإجابات.

مواضيع ذات صلة

فيديو: دالات IF المتقدمة
الدالة IFS (Microsoft 365 Excel 2016 واللاحقة)
ستحسب الدالة COUNTIF القيم استنادا إلى معيار واحد
ستحسب الدالة COUNTIFS القيم استنادا إلى معايير متعددة
تقوم الدالة SUMIF ب جمع القيم استنادا إلى معيار واحد
تقوم الدالة SUMIFS ب جمع القيم استنادا إلى معايير متعددة
الدالة
AND الدالة
OR الدالة VLOOKUP
نظرة عامة على الصيغ في Excel
كيفية تجنب الصيغ المعطلة
الكشف عن الأخطاء في الصيغ
الدالات المنطقية
Excel الدالات (أبجديا)
Excel الدالات (حسب الفئة)

هل تحتاج إلى مزيد من المساعدة؟

توسيع المهارات
استكشاف التدريب
الحصول على الميزات الجديدة أولاً
الانضمام إلى Microsoft Insider

هل كانت المعلومات مفيدة؟

ما مدى رضاك عن جودة الترجمة؟
ما الذي أثّر في تجربتك؟

نشكرك على ملاحظاتك!

×