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

الدالة 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 ر.س. لنفترض أن لديك 12000 ر.س من الإيرادات – فسترجع العبارة 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 بشكل أكثر تفصيلاً هنا، لكن ذلك بالتأكيد أبسط بكثير من المستويات 12 وعبارة IF المعقدة والمتداخلة! كما توجد فوائد أخرى أقل وضوحاً أيضاً:

  • جداول مراجع 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 والأقواس.

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

تجربة Office 365 أو الإصدار الأخير من Excel

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

ويمكنك دائماً الاستفسار من أحد الخبراء في مجتمع Excel‏ التقني،أو الحصول على الدعم من خلال إجابات المجتمع،أو اقتراح ميزة جديدة أو تحسين عن طريق ‏Excel User Voice‏.

انظر أيضاً

شاهد الفيديو الذي يوضح كيفية استخدام الدالة IF‏

الدالة IFS (‏Office 365 وExcel 2016 والإصدار الأحدث)

تحسب الدالة COUNTIF عدد القيم استناداً إلى معيار فردي

تحسب الدالة COUNTIFS عدد القيم استناداً إلى معايير متعددة

تجمع الدالة SUMIF القيم استناداً إلى معيار فردي

تجمع الدالة SUMIFS القيم استناداً إلى معايير متعددة

الدالة AND‏

‏الدالة OR‏

‏الدالة VLOOKUP‏

نظرة عامة حول الصيغ في Excel‏

كيفية تفادي الصيغ المقطوعة

استخدام تدقيق الأخطاء لكشف الأخطاء في الصيغ

الدالات المنطقية

دالات Excel (أبجدياً)

دالات Excel (حسب الفئة)

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

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

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

شكراً لك على الملاحظات! يبدو أنه من المفيد إيصالك بأحد وكلاء دعم Office لدينا.

×