اكسل

19 تلميحًا لصيغ IF المتداخلة

19 Tips Nested If Formulas

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



لكن غالبًا ما يؤدي أحد IF إلى آخر ، وبمجرد أن تجمع بين أكثر من زوج من IFs ، يمكن أن تبدأ الصيغ الخاصة بك في الظهور مثل Frankensteins الصغيرة :)

هل متداخلة IFs الشر؟ هل هم ضروريون في بعض الأحيان؟ ما هي البدائل؟





تابع القراءة لمعرفة إجابات هذه الأسئلة والمزيد ...

1. IF الأساسية

قبل أن نتحدث عن IF المتداخلة ، دعنا نراجع بسرعة بنية IF الأساسية:



 
= IF (test,[true],[false])

تُجري الدالة IF اختبارًا وتنفذ إجراءات مختلفة بناءً على ما إذا كانت النتيجة صحيحة أم خاطئة.

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

للتوضيح ، نستخدم هنا IF للتحقق من الدرجات وحساب 'النجاح' للحصول على درجات لا تقل عن 65:

دالة IF الأساسية - عودة

تحتوي الخلية D3 في المثال على هذه الصيغة:

 
= IF (C3>=65,'Pass')

والتي يمكن قراءتها على هذا النحو: إذا كانت النتيجة في C3 65 على الأقل ، فقم بإرجاع 'Pass'.

لاحظ مع ذلك أنه إذا كانت النتيجة أقل من 65 ، IF إرجاع FALSE ، لأننا لم نوفر قيمة إذا كانت false. لعرض 'فشل' للنتائج غير الناجحة ، يمكننا إضافة 'فشل' كوسيطة خاطئة مثل:

 
= IF (C3>=65,'Pass','Fail')

دالة IF الأساسية - مع القيمة المضافة للخطأ

فيديو: كيفية بناء الصيغ المنطقية .

2. ماذا يعني التعشيش

يعني التداخل ببساطة دمج الصيغ ، واحدة داخل الأخرى ، بحيث تتعامل إحدى الصيغ مع نتيجة أخرى. على سبيل المثال ، إليك صيغة تتداخل فيها الدالة TODAY داخل الدالة MONTH:

 
= MONTH ( TODAY ())

ترجع الدالة TODAY التاريخ الحالي داخل الدالة MONTH. تأخذ الدالة MONTH هذا التاريخ وتعيد الشهر الحالي. حتى الصيغ المعقدة بشكل معتدل تستخدم التداخل بشكل متكرر ، لذلك سترى التداخل في كل مكان في الصيغ الأكثر تعقيدًا.

3. متداخلة بسيطة IF

IF المتداخلة عبارة عن جملتين IF إضافيتين فقط في صيغة ، حيث تظهر عبارة IF داخل الأخرى.

للتوضيح ، قمت أدناه بتوسيع صيغة النجاح / الفشل الأصلية أعلاه للتعامل مع النتائج 'غير المكتملة' عن طريق إضافة دالة IF ، وتداخل IF داخل الأخرى:

متداخلة أساسية IF

 
= IF (C3='','Incomplete', IF (C3>=65,'Pass','Fail'))

يتم تشغيل IF الخارجي أولاً ويختبر لمعرفة ما إذا كانت C3 فارغة. إذا كان الأمر كذلك ، فإن IF الخارجي يُرجع 'غير مكتمل' ، ولا يعمل IF الداخلي أبدًا.

إذا كانت النتيجة ليس فارغًا ، تقوم الدالة IF الخارجية بإرجاع FALSE ، ويتم تشغيل الدالة IF الأصلية.

تعلم IFs المتداخلة مع تدريب فيديو واضح وموجز .

4. IF متداخلة للمقاييس

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

الحيلة هي تحديد الاتجاه (من الأعلى إلى الأدنى ، أو من الأدنى إلى الأعلى) ، ثم هيكلة الظروف وفقًا لذلك. على سبيل المثال ، لتعيين الدرجات بترتيب 'منخفض إلى مرتفع' ، يمكننا تمثيل الحل المطلوب في الجدول التالي. لاحظ أنه لا يوجد شرط لـ 'A' ، لأنه بمجرد أن نجتاز جميع الشروط الأخرى ، نعلم أن الدرجة يجب أن تكون أكبر من 95 ، وبالتالي يجب أن تكون 'A'.

نتيجة رتبة شرط
0-63 F <64
64-72 د <73
73-84 ج <85
85-94 ب <95
95-100 إلى

مع فهم الشروط بوضوح ، يمكننا إدخال عبارة IF الأولى:

كيفية إنشاء صيغة if في التفوق
 
= IF (C5<64,'F')

هذا يعتني بالحرف 'F'. الآن ، للتعامل مع 'D' ، نحتاج إلى إضافة شرط آخر:

 
= IF (C5<64,'F', IF (C5<73,'D'))

لاحظ أنني أسقطت ببساطة IF أخرى في IF الأول للنتيجة 'false'. لتمديد الصيغة للتعامل مع 'C' ، نكرر العملية:

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C')))

نستمر في هذا الطريق حتى نصل إلى الصف الأخير. ثم ، بدلاً من إضافة IF أخرى ، فقط أضف الدرجة النهائية للخطأ.

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C', IF (C5<95,'B','A'))))

فيما يلي صيغة IF المتداخلة النهائية قيد التنفيذ:

إكمال مثال IF المتداخلة لحساب التقديرات

فيديو: كيفية عمل IF متداخلة لتعيين التقديرات

5. IFs المتداخلة لها تدفق منطقي

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

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

التدفق المنطقي لـ IF متداخلة

6. استخدم تقييم لمشاهدة التدفق المنطقي

على نظام Windows ، يمكنك استخدام ملف ميزة التقييم لمشاهدة برنامج Excel وهو يحل الصيغ ، خطوة بخطوة. هذه طريقة رائعة 'لرؤية' التدفق المنطقي للصيغ الأكثر تعقيدًا ، ولاستكشاف الأخطاء وإصلاحها عندما لا تعمل الأشياء بالشكل الذي تتوقعه. تُظهر الشاشة أدناه نافذة التقييم مفتوحة وجاهزة للانطلاق. في كل مرة تنقر فيها على الزر 'تقييم' ، يتم حل 'الخطوة التالية' في الصيغة. يمكنك العثور على تقييم في علامة تبويب الصيغ في الشريط (Alt M ، V).

استخدام 'تقييم' للتنقل عبر IF متداخلة تقوم بتعيين التقديرات

لسوء الحظ ، لا يحتوي إصدار Mac من Excel على ميزة التقييم ، ولكن لا يزال بإمكانك استخدام خدعة F9 أدناه.

7. استخدم F9 لتحديد نتائج الفحص

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

باستخدام F9 ، تحقق من IF المتداخلة التي تقوم بتعيين التقديرات

استخدم Control + Z (Command + Z) على جهاز Mac للتراجع عن F9. يمكنك أيضًا الضغط على Esc للخروج من محرر الصيغة دون أي تغييرات.

فيديو: كيفية تصحيح أخطاء صيغة باستخدام F9

8. اعرف حدودك

يحتوي Excel على حدود على مدى عمق تداخل دالات IF. حتى Excel 2007 ، سمح Excel بما يصل إلى 7 مستويات من IFs المتداخلة. في Excel 2007+ ، يسمح Excel بما يصل إلى 64 مستوى.

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

9. تطابق الأقواس مثل المحترفين

أحد التحديات مع IFs المتداخلة هو مطابقة أو 'موازنة' الأقواس. عندما لا يتم مطابقة الأقواس بشكل صحيح ، فإن المعادلة الخاصة بك تنكسر. لحسن الحظ ، يوفر E xcel أداتين لمساعدتك على التأكد من أن الأقواس 'متوازنة' أثناء تحرير الصيغ.

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

أقواس الصيغة متطابقة الألوان ولكن يصعب رؤيتها

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

لسوء الحظ ، فإن الكتابة بالخط العريض هي ميزة Windows فقط. إذا كنت تستخدم Excel على جهاز Mac لتحرير الصيغ المعقدة ، فمن المنطقي أحيانًا نسخ الصيغة ولصقها في محرر نصوص جيد ( نص رانجلر مجاني وممتاز) للحصول على أدوات أفضل لمطابقة الأقواس. سيومض Text Wrangler عند مطابقة الأقواس ، ويمكنك استخدام Command + B لتحديد كل النص الموجود بين الأقواس. يمكنك لصق الصيغة مرة أخرى في Excel بعد تسوية الأمور.

10. استخدم نافذة تلميح الشاشة للتنقل والاختيار

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

انتقل إلى وسيطات الصيغة وحددها باستخدام تلميح الشاشة

يمكنك رؤيتي وأنا أستخدم نافذة تلميح الشاشة كثيرًا في هذا الفيديو: كيفية بناء IF متداخلة .

11. اهتم بالنصوص والأرقام

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

 
= IF (A1='100','Pass','Fail')

هي درجة الاختبار في A1 هل حقا نص وليس رقم؟ لا؟ ثم لا تستخدم علامات الاقتباس حول الرقم. وإلا ، فسيعيد الاختبار المنطقي FALSE حتى عندما تكون القيمة درجة النجاح ، لأن '100' ليست هي 100. إذا كانت درجة الاختبار رقمية ، فاستخدم ما يلي:

 
= IF (A1=100,'Pass','Fail')

12. أضف فواصل أسطر لتسهيل قراءة IFs المتداخلة

عندما تعمل باستخدام صيغة تحتوي على مستويات متعددة من IFs المتداخلة ، فقد يكون من الصعب إبقاء الأمور في نصابها الصحيح. نظرًا لأن Excel لا يهتم بـ 'المسافة البيضاء' في الصيغ (مثل المسافات الزائدة أو فواصل الأسطر) ، يمكنك تحسين قابلية قراءة ifs المتداخلة بشكل كبير عن طريق إضافة فواصل أسطر.

على سبيل المثال ، تُظهر الشاشة أدناه IF المتداخلة التي تحسب معدل العمولة بناءً على رقم المبيعات. هنا يمكنك رؤية بنية IF المتداخلة النموذجية ، والتي يصعب فك تشفيرها:

من الصعب قراءة IFs المتداخلة بدون فواصل أسطر

ومع ذلك ، إذا أضفت فواصل أسطر قبل كل 'قيمة إذا كانت خطأ' ، فإن منطق الصيغة ينتقل بشكل واضح. بالإضافة إلى ذلك ، من الأسهل تحرير الصيغة:

تجعل فواصل الأسطر قراءة IFs المتداخلة أسهل

يمكنك إضافة فواصل أسطر على Windows باستخدام Alt + Enter ، في نظام Mac ، استخدم Control + Option + Return.

فيديو: كيفية جعل قراءة IF المتداخلة أسهل .

13. تحديد IFs بـ AND و OR

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

على سبيل المثال ، في المشكلة أدناه ، نريد وضع علامة 'x' في العمود D لتمييز الصفوف حيث يكون اللون 'أحمر' ويكون الحجم 'صغيرًا'.

إذا كانت الدالة AND أبسط من حالتي IF متداخلتين

يمكننا كتابة الصيغة مع حالتي IF متداخلين مثل هذا:

 
= IF (B6='red', IF (C6='small','x',''),'')

ومع ذلك ، باستبدال الاختبار بوظيفة AND ، يمكننا تبسيط الصيغة:

 
= IF ( AND (B6='red',C6='small'),'x','')

بالطريقة نفسها ، يمكننا بسهولة توسيع هذه الصيغة باستخدام الدالة OR للتحقق من وجود اللون الأحمر أو الأزرق والصغير:

 
= IF ( AND ( OR (B4='red',B4='blue'),C4='small'),'x','')

كل هذا استطاع باستخدام IFs المتداخلة ، لكن الصيغة ستصبح أكثر تعقيدًا بسرعة.

فيديو: إذا كان هذا أو ذاك

14. استبدل IFs المتداخلة بـ VLOOKUP

عندما يقوم IF المتداخلة بتعيين القيم بناءً على إدخال واحد ، يمكن استبداله بسهولة بامتداد وظيفة VLOOKUP . على سبيل المثال ، يقوم IF المتداخلة بتعيين أرقام لخمسة ألوان مختلفة:

 
= IF (E3='red',100, IF (E3='blue',200, IF (E3='green',300, IF (E3='orange',400,500))))

يمكننا استبداله بسهولة بـ VLOOKUP (الأبسط بكثير):

 
= VLOOKUP (E3,B3:C7,2,0)

متداخل IF مقابل VLOOKUP

كمكافأة ، يحتفظ VLOOKUP بالقيم في ورقة العمل (حيث يمكن تغييرها بسهولة) بدلاً من تضمينها في الصيغة.

على الرغم من أن الصيغة أعلاه تستخدم المطابقة التامة ، إلا أنه يمكنك استخدامها بسهولة VLOOKUP للدرجات أيضا.

أنظر أيضا: 23 شيئًا يجب معرفته عن VLOOKUP

فيديو: كيفية استخدام VLOOKUP

فيديو: لماذا تعتبر VLOOKUP أفضل من IFs المتداخلة

15. اختر اختيار

يمكن أن توفر وظيفة CHOOSE حلاً أنيقًا عندما تحتاج إلى تعيين أرقام بسيطة ومتتالية (1،2،3 ، إلخ) لقيم عشوائية.

في المثال أدناه ، يتم استخدام CHOOSE لإنشاء اختصارات مخصصة في أيام الأسبوع:

متداخلة IF مقابل وظيفة CHOOSE

أَكِيدْ استطاع استخدم IF متداخلة طويلة ومعقدة للقيام بنفس الشيء ، ولكن من فضلك لا تفعل :)

16. استخدم IFS بدلاً من IFs المتداخلة

إذا كنت تستخدم Excel 2016 عبر Office 365 ، فهناك وظيفة جديدة يمكنك استخدامها بدلاً من IFs المتداخلة: وظيفة IFS. توفر وظيفة IFS هيكلًا خاصًا لتقييم الشروط المتعددة بدون التعشيش:

وظيفة IFS - شروط متعددة دون تداخل

تبدو الصيغة المستخدمة أعلاه كما يلي:

 
= IFS (D5<60,'F',D5<70,'D',D5<80,'C',D5<90,'B',D5>=90,'A')

لاحظ أن لدينا زوجًا واحدًا فقط من الأقواس!

ماذا يحدث عند فتح جدول بيانات يستخدم وظيفة IFS في إصدار قديم من Excel؟ في Excel 2013 و 2010 (وأعتقد أن Excel 2007 ، ولكن لا يمكنني الاختبار) ، سترى '_xlfn'. ملحق بـ IFS في الخلية. ستظل القيمة المحسوبة مسبقًا موجودة ، ولكن إذا تسبب شيء ما في إعادة حساب الصيغة ، فسترى الخطأ #NAME. مايكروسوفت لديها مزيد من المعلومات هنا .

17. ماكس خارج

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

تمنحك وظيفة MAX طريقة ذكية للقيام بذلك بدون وجود IF في أي مكان في الأفق:

 
= MAX (calculation,0)

ترجع هذه التقنية نتيجة الحساب إذا كانت موجبة ، وتعيد صفرًا بخلاف ذلك.

أنا أحب هذا البناء لأنه فقط في غاية البساطة . انظر هذه المقالة لكتابة كاملة .

18. اعتراض الأخطاء مع IFERROR

الاستخدام الكلاسيكي لـ IF هو اعتراض الأخطاء وتقديم نتيجة أخرى عند حدوث خطأ ، مثل هذا:

 
= IF ( ISERROR (formula),error_result,formula)

هذا قبيح ولا لزوم له ، لأن نفس الصيغة تدخل مرتين ، ويجب على Excel أن يحسب نفس النتيجة مرتين عندما لا يكون هناك خطأ.

في Excel 2007 ، تم تقديم وظيفة IFERROR ، والتي تتيح لك تعقب الأخطاء بشكل أكثر أناقة:

 
= IFERROR (formula,error_result)

الآن عندما تطرح الصيغة خطأ ، تقوم IFERROR ببساطة بإرجاع القيمة التي تقدمها.

19. استخدم المنطق المنطقي

يمكنك أيضًا تجنب IFs المتداخلة أحيانًا باستخدام ما يسمى بـ 'المنطق المنطقي'. تشير الكلمة المنطقية إلى قيم TRUE / FALSE. على الرغم من أن Excel يعرض الكلمتين TRUE و FALSE في الخلايا ، إلا أنه داخليًا ، يعامل Excel TRUE على أنه 1 ، و FALSE على أنه صفر. يمكنك استخدام هذه الحقيقة لكتابة صيغ ذكية وسريعة للغاية. على سبيل المثال ، في مثال VLOOKUP أعلاه ، لدينا صيغة IF متداخلة تبدو كالتالي:

 
= IF (E3='red',100, IF (E3='blue',200, IF (E3='green',300, IF (E3='orange',400,500))))

باستخدام المنطق المنطقي ، يمكنك إعادة كتابة الصيغة كما يلي:

 
=(E3='red')*100+(E3='blue')*200+(E3='green')*300+(E3='orange')*400+(E3='purple')*500

يقوم كل تعبير بإجراء اختبار ، ثم يضاعف نتيجة الاختبار بـ 'القيمة إذا كانت صحيحة'. نظرًا لأن الاختبارات ترجع إما TRUE أو FALSE (1 أو 0) ، فإن نتائج FALSE تلغي نفسها بشكل فعال خارج الصيغة.

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

فيديو: كيفية استخدام المنطق المنطقي في صيغ Excel

متى تحتاج إلى IF متداخلة؟

مع كل هذه الخيارات لتجنب IFs المتداخلة ، قد تتساءل متى يكون من المنطقي استخدام IF متداخلة؟

أعتقد أن IFs المتداخلة منطقية عندما تحتاج إلى التقييم عدة مدخلات مختلفة لاتخاذ قرار.

على سبيل المثال ، لنفترض أنك تريد حساب حالة فاتورة 'مدفوعة' و 'مفتوحة' و 'متأخرة' وما إلى ذلك. يتطلب هذا أن تنظر في عمر الفاتورة و اتزان رائع:

حساب حالة الفاتورة مع IF متداخلة

في هذه الحالة ، يعد IF المتداخل حلاً جيدًا تمامًا.

افكارك؟

وماذا عنك؟ هل أنت IF-ster؟ هل تتجنب IFs المتداخلة؟ هل متداخلة IFs الشر؟ شارك أفكارك أدناه.

تعلم صيغ Excel بسرعة مع تدريب فيديو موجز . المؤلف ديف برونز


^