كيفية استخدام دالة IF الشرطية في Microsoft Excel


Huda AlMashta

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

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

if-excel.thumb.png.da929facb69c9d1381686

الصيغة الأساسية لدالة IF هي:

IF(logical_test; [value_if_ture]; [value_if_false])
  • logical_test هو الشرط، كمقارنة قيمتين أو خليتين فيما إذا كانت إحدى القيم أكبر من الأخرى، وهذا الشرط مطلوب تحديده في الصيغة.
  • value_if_true هي القيمة التي يتم إرجاعها إذا تحقق الشرط، وتحديدها في الصيغة مطلوب.
  • value_if_false هي القيمة التي يتم إرجاعا إذا لم يتحقق الشرط، وتحديدها في الصيغة اختياري.

سنقوم بشرح بعض الأمثلة لتوضيح مفهوم هذه الدالة.

مثال 1: في هذا المثال سنشرح أبسط صيغ استخدام دالة IF. في الجدول التالي مجموعة من القيم في عمودين، A وB. المطلوب هو مقارنة القيم في العمودين، وإذا كانت القيم في العمود A أكبر من القيمة في العمود B يتم إرجاع القيمة "نعم" في عمود "النتيجة"، وإلا يتم إرجاع القيم "كلا" في عمود "النتيجة".

568c3ecd7ffd4_1-1-_IF.thumb.png.d9ae6d52

سنكتب الصيغة في الخلية الأولى من عمود النتيجة، C2:

568c3ece34254_2-_IF.thumb.png.126a180a8b

أي أنّ الشرط logical_test هو A2>B2، النتيجة إذا تحقق الشرط value_if_true هي "نعم"، والنتيجة إذا لم يتحقق الشرط value_if_false هي "كلا".

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

568c3ed1eea90_3-_.thumb.png.38ecf3d0c700

إذا كانت هناك نصوص ضمن الصيغة يجب أن توضع بين علامتي اقتباس كما فعلنا مع الكلمتين "نعم" و "كلا" لأننا نريد إظهارها بصيغة نصوص في النتيجة.

مثال 2: في هذا المثال مجموعة من السلع الصيفية والشتوية، والمطلوب هو إرجاع نتيجة الخصم "%50" إذا كانت السلعة صيفية والقيمة "0" إذا كانت السلعة شتوية. سنقوم بكتابة الصيغة في الخلية C2:

568c3ed406224_4-2-_if.thumb.png.a5744fd5

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

سنقوم بنسخ الصيغة إلى باقي الخلايا باستخدام التعبئة التلقائية:

568c3ecfbabc0_5-2-_if.thumb.png.70bcaa42

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

وستكون الصيغة بالشكل التالي:

568c3eebb02ab_6-2-_TRIM.thumb.png.481b91

وعند نسخ الصيغة إلى باقي الخلايا ستظهر النتيجة الصحيحة.

568c3eeca704b_7-2-_if__.thumb.png.6e9b3e

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

دوال IF المتداخلة

ويُقصد بها دالة IF داخل دالة IF أخرى، وهذه الصيغة تتيح لك اختبار العديد من المعايير وزيادة عدد النتائج المحتملة. وسنوضح طريقة كتابة الصيغة بالمثالين التاليين:

مثال 1: في الجدول أدناه مجموعة درجات لمجموعة من الطلاب، والمطلوب هو إرجاع النتيجة "ممتاز" إذا كانت الدرجة أكبر أو تساوي 90، النتيجة "جيد جدا" إذا كانت الدرجة أكبر أو 80-90، النتيجة "جيد" إذا كانت النتيجة أكبر أو تساوي 70-80، النتيجة "متوسط" إذا كانت النتيجة أكبر أو تساوي 60-70، النتيجة "مقبول" إذا كانت النتيجة أكبر أو تساوي 50-60، أو النتيجة "راسب" إذا لم يتحقق الشرط السابق:

568c3eed937e2_8-_if__-_1.thumb.png.fa6f9

سنحدد الخلية C2 وسنكتب الصيغة في شريط الصيغة:

568c3eeece017_9-_IF_-__1.thumb.png.38862

ستعمل الصيغة كالتالي: سيتم أولا تقييم الشرط الأول وهو إذا كانت قيمة الخلية B2 أكبر أو تساوي 90، فإذا تحقق الشرط سيتم إرجاع النتيجة "ممتاز" وتتوقف. وإذا لم يتحقق ستنتقل إلى الشرط الثاني، وهو إذا كانت القيمة أكبر أو تساوي 80. لكن القيمة أكبر أو تساوي 80 تشتمل على القيم من 80-100، والقيمة من 90-100 يجب أن تكون نتيجتها "ممتاز" وليس "جيد جدا" كيف سيتم الأمر؟ لن يتم احتساب القيم من 90-100 لأن الشرط الأول هو غير متحقق من الأصل، أي أنّ القيم التي يتم تقييمها في الشرط الثاني هي بالفعل أقل من 90.

إذا تحقق الشرط الثاني سيتم إرجاع القيمة "جيد جدا" ثم تتوقف الصيغة، وإذا لم يتحقق سيتم الانتقال إلى الشرط الثالث، وهكذا.

سنقوم بنسخ الصيغة إلى بقية الخلايا باستخدام التعبئة التلقائية:

568c3ef1b7869_10-_IF_-__1.thumb.png.b41a

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

568c3ef67ab42_11-_If___2.thumb.png.fed11

سنحدد الخلية D2 لإظهار النتيجة فيها، وسنكتب الصيغة في شريط الصيغة كالتالي:

568c3ef909107_12-_if_-__2.thumb.png.64c8

يتم أولا إيجاد مقدار الخصم بضرب السعر بالنسبة 75% إذا كانت سنة الإنتاج (في العمود  B) 2011، بالنسبة 50% إذا كانت سنة الإنتاج 2012، وبالنسبة 25% إذا كانت سنة الإنتاج 2013. بعدها يتم طرح مقدار الخصم من السعر الأصلي في عمود "السعر".

لم نستخدم قيم سنوات الإنتاج ونسب الخصم بشكل مباشر في الصيغة وإنما قمنا باستخدام الخلايا التي تحتويها كمرجع مطلق absolute reference (أي استخدمنا $G$2 بدلا من 2011، $H$2 بدلا من 75% وهكذا بالنسبة لبقية القيم). والسبب هو ليكون بإمكاننا تغيير هذه القيم لاحقا دون الحاجة إلى إعادة كتابة الصيغة.

لاستخدام الخلية كمرجع مطلق اضغط F4 بعد أن تقوم بتحديد الخلية (أو كتابة اسمها) عند كتابتك للصيغة.

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

سنقوم بنسخ الصيغة إلى بقية الخلايا باستخدام التعبئة التلقائية:

568c3efba4405_13-_if_-__2.thumb.png.4863

ملاحظة: بالإمكان كتابة دالات داخلية حتى 64 دالة في الصيغة الواحدة.

استخدام دالة IF مع المعاملات AND أو OR

تُستخدم الدالة IF مع الدالة AND لإظهار نتيجة معينة إذا صحت جميع المعطيات. وتُستخدم مع الدالة OR لعرض نتيجة معينة إذا صح جزء واحد على الأقل من المعطيات.

تكون البنية العامة لدالة AND كالتالي:

AND(argument1; argument2;…)

والبنية العامة للدالة OR كالتالي:

OR(argument1; argument2;…)

يسمى argument الوسيطة (وأفضل تسميته المُعطى).

سنستخدم المثال التالي لتوضيح طريقة كتابة الصيغة:

568c3efe99302_14-_if__And-.thumb.png.fe3

في هذا المثال لدينا ثلاثة اختبارات لعدد من الطلاب، والمطلوب هو إرجاع القيمة "نعم" في عمود "جميع التقييمات ممتازة" إذا كانت الدرجات في جميع الاختبارات للطالب الواحد أكبر أو تساوي 90. سنحدد الخلية E2 لعرض النتيجة للطالب الأول وسنكتب الصيغة في شريط الصيغة:

568c3f02431bd_15-_if__and-_.thumb.png.5e

ستعمل الصيغة كالتالي: إذا كانت درجة الاختبار الأول ودرجة الاختبار الثاني ودرجة الاختبار الثالث أكبر أو تساوي 90 فسيتم إرجاع القيمة "نعم" في عمود "جميع التقييمات ممتازة". أما إذا كانت إحدى الدرجات أقل من 90 فسيتم إرجاع القيمة "كلا"، حتى ولو كانت بقية الدرجات أكبر أو تساوي 90.

سنقوم بنسخ الصيغة إلى بقية الخلايا باستخدام التعبئة التلقائية:

568c3f07280cb_16-_if__and-_.thumb.png.30

أما عمل الدالة OR فهو مشابه لعمل الدالة AND، باستثناء أنها تقوم بإرجاع القيمة "نعم" إذا صح معطى واحد على الأقل من المعطيات، والقيمة "كلا" إذا لم يصح أي من المعطيات.

تُكتب الصيغة التي تتضمن دالة IF والمعامل OR كالتالي:

568c3f0922c5b_17-_if__or-_.thumb.png.e99

بعد كتابة الصيغة سنقوم بنسخها إلى بقية الخلايا بالتعبئة التلقائية:

568c3f0a668a7_18-_if__or-_.thumb.png.706

استخدام الدالتين COUNTIFS وSUMIFS

COUNTIFS

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

البنية العامة للدالة COUNTIFS هي كالتالي:

COUNTIFS([criteria_range1]; criteria1; [criteria_range2]; criteria2];…)
  • criteria_range1 هو نطاق الخلايا التي سيتم تطبيق المعيار الأول عليها، وهو مطلوب في الصيغة.
  • criteria1 هو المعيار الذي سيطبق على نطاق المعايير الأول لتحديد الخلايا التي سيتم حساب عددها فيما لو تحقق، وهو مطلوب في الصيغة أيضا.

أما نطاق المعايير الثاني والمعيار الثاني فهي اختيارية في الصيغة.

ملاحظة: جميع النطاقات يجب أن تكون بعدد الصفوف والأعمدة نفسه لنطاق المعايير الأول، لكن لا يُشترط أن تكون متجاورة. وكذلك بإمكانك استخدام أحرف البدل wildcard characters مثل النجمة (*) علامة الاستفهام في إنشاء المعايير.

مثال: في الجدول أدناه مجموعة من المشاريع التي تبدأ وتنتهي في أوقات محددة. المطلوب هو حساب عدد المشاريع التي تبدأ بعد التاريخ 1/2/2015 وتنتهي قبل التاريخ 1/7/2015. لقد قمنا بكتابة المعايير في جدول منفصل لاستخدمها كمرجع مطلق:

568c3f0b918ab_19-_countifs-_.thumb.png.a

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

سنقوم بتحديد نطاق المعايير الأول (criteria_range1 (B2:B9، ثم سنحدد المعيار الأول (criteria1) وسنضغط F4 لاستخدامه كمرجع مطلق. بعدها سنحدد نطاق المعايير الثاني C2:C9، ثم المعيار الثاني كمرجع مطلق، وبذلك تصبح الصيغة لهذا المثال كالتالي:

568c3f0e01733_20-_countifs-__.thumb.png.

سيتم بتقييم نطاق الخلايا الأول B2:B9 وحساب عدد التواريخ التي تحقق المعيار >1/2/2015، هناك سبعة في هذا المثال؛ جميع المشاريع ماعدا المشروع 7. بعدها سيقوم بفحص تلك التواريخ السبعة وحساب عدد التواريخ التي تحقق المعيار <1/7/2015 وهناك ثلاثة في هذا المثال، المشروع 1، المشروع 3، والمشروع 5. وبالتي ستكون عدد التواريخ التي تحقق المعيارين 3:

568c3f145e84e_21-_countifs-__.thumb.png.

SUMIFS

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

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
  • sum_range هو نطاق الخلايا المطلوب جمع القيم التي تحقق المعايير فيه.
  • criteria_range1 هو نطاق الخلايا الذي سيتم تطبيق المعيار الأول عليه، وهو مطلوب في الصيغة.
  • criteria1 هو المعيار الذي سيطبق على نطاق المعايير الأول لتحديد الخلايا التي سيتم جمعها وهو مطلوب في الصيغة أيضا.

أما نطاق المعايير الثاني criteria_range2 والمعيار الثاني criteria1 فهي اختيارية في الصيغة.

مثال: في الجدول أدناه مجموعة من الطلبات والمبيعات المصنفة حسب مندوب المبيعات والمنطقة. المطلوب هو إيجاد مجموع المبيعات حسب المنطقة والتي يحقق فيها مندوب المبيعات عدد طلبات أكبر أو يساوي 20.

568c3f15883b1_22-_sumifs-.thumb.png.5561

سنقوم أولا بتحديد نطاق المجموع sum_range، وهو نطاق الخلايا D2:D8 وسنستخدمه كمرجع مطلق. ثم سنحدد نطاق المعايير الأول criteria_range1، وهو نطاق الخلايا B2:B8 وسنستخدمه كمرجع مطلق أيضا. أما المعيار الأول فهو "الشمال"، وسنستخدمه كمرجع نسبي relative reference. وأخيرا سنحدد نطاق المعايير الثاني، وهو نطاق الخلايا C2:C8 كمرجع مطلق ثم المعيار الثاني >=20 كمرجع نسبي:

568c3f18ecb64_23-_sumifs-_.thumb.png.d85

سيتم أولا تحديد مندوبي المبيعات الذي يحققون المعيار "الشمال"، وهما "زيد" و"سامي" في هذا المثال. ومن بين مندوبي المبيعات هؤلاء سيتم تحديد أيهما حقق عدد طلبات أكبر أو يساوي 20، وكلاهما قد حقق هذا العدد في هذا المثال. وبالتالي سيتم جمع المبيعات لكليهما.

نفس الخطوات تطبق على المعايير "الجنوب"، الشرق"، والغرب" عند نسخ الصيغة باستخدام التعبئة التلقائية:

568c3f1e5adf7_24-_sumif-_.thumb.png.19b0

لاحظ أن نتيجة الشرق تركت فارغة، وذلك لأن المعيار الثاني (عدد الطلبات أكبر أو يساوي 20) لا يتحقق.

ملاحظة: إنّ معايير [الشمال]، [الجنوب]، [الشرق]، [الغرب] هي في الحقيقة [=الشمال]، [=الجنوب]، [=الشرق]، [=الغرب]. لكننا لم نقم بكتابتها بالصيغة الأخيرة لأنّ معيار [=] هو المعيار الافتراضي في البرنامج، وسيتم اعتماده ما لم يتم تحديد غيره من المعايير.

استخدام الدالتين AVERAGEIFS وIFERROR

AVERAGEIFS

تُستخدم هذه الدالة لإيجاد المتوسط لمجموعة من الخلايا التي تحقق عدد من المعايير، والبنية العامة لها هي:

AVERAGEIFS(average_range; criteria_range1; criteria1; [criteria_range2; criteria2]…)
  • average_range هو نطاق الخلايا المطلوب إيجاد متوسط قيمها. ويتم تجاهل الخلايا الفارغة أو القيم النصية. وهو مطلوب في الصيغة.
  • criteria_range1 هو نطاق المعايير الأول الذي يتم تطبيق المعيار الأول عليه، وهو مطلوب في الصيغة.
  • criteria1 المعيار الذي يتم على أساسه تقييم نطاق المعايير الأول، وهو مطلوب في الصيغة أيضا.

أما نطاق المعايير الثاني criteria_range2 والمعيار الثاني criteria1 فهي اختيارية في الصيغة.

مثال: سنستخدم المثال السابق لتطبيق دالة AVERAGEIFS. لكن باختلاف المطلوب، وهو إيجاد متوسط المبيعات حسب المنطقة والتي يحقق فيها مندوب المبيعات عدد طلبات أكبر أو يساوي 20.

568c3f219eebe_25-_averageifs-.thumb.png.

سنقوم أولا بتحديد نطاق المتوسط average_range، وهو نطاق الخلايا التي نريد تطبيق دالة ANERAGEIFS عليها. أي النطاق D2:D8 وسنستخدمه كمرجع مطلق. ثم سنحدد نطاق المعايير الأول criteria_range1، وهو نطاق الخلايا B2:B8 وسنستخدمه كمرجع مطلق أيضا. أما المعيار الأول فهو "الشمال"، وسنستخدمه كمرجع نسبي relative reference. وأخيرا سنحدد نطاق المعايير الثاني، وهو نطاق الخلايا C2:C8 كمرجع مطلق ثم المعيار الثاني >=20 كمرجع نسبي:

568c3f2704db3_26-_averageifs-_.thumb.png

سيتم أولا تحديد مندوبي المبيعات الذي يحققون المعيار "الشمال"، وهما "زيد" و"سامي" في هذا المثال. ومن بين مندوبي المبيعات هؤلاء سيتم تحديد أيهما حقق عدد طلبات أكبر أو يساوي 20، وكلاهما قد حقق هذا العدد في هذا المثال. وبالتالي سيتم إيجاد متوسط المبيعات لكليهما.

نفس الخطوات تطبق على المعايير "الجنوب"، الشرق"، والغرب" عند نسخ الصيغة باستخدام التعبئة التلقائية:

568c3f281986f_27-_averageifs-_.thumb.png

لكن كما نلاحظ وجود خطأ القسمة على صفر (DIV/0#!) في الخلية H4، والسبب هو أنّ كلا المعيارين لم يتحققا. فمن منطقة الشرق يوجد أحمد فقط، وعدد الطلبات الذي حققه أصغر من 20. لذلك حصل هذا الخطأ. إذ تقوم دالة AVERAGEIFS بإرجاع القيمة صفر إذا لم تتحقق جميع المعايير. ولإصلاح هذا الخطأ (وغيرها من الأخطاء التي يمكن أن تحصل) سنستخدم الدالة IFERROR.

IFERROR

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

البنية العامة لهذه الدالة هي:

IFERROR(value, value_if_error)
  • value هي القيمة التي يتم فحصها بحثا عن الخطأ.
  • value_if_error هي القيمة التي يتم إرجاعها عند وجود خطأ في الصيغة.

سنقوم بإدخال هذه الدالة مع دالة AVERAGEIFS وبالصيغة التالية:

568c3f2b6bc28_28-_iferror-.thumb.png.b9e

في هذا المثال تمثل دالة AVERAGEIFS قيمة value التي يتم فحصها بحثا عن الخطأ. و"لا يوجد متوسط" هي قيمة value_if_error التي يتم إرجاعها عند عدم وجود قيم لإيجاد المتوسط. سنستخدم التعبئة التلقائية لنسخ الصيغة إلى بقية الخلايا:

568c3f2e8c940_29-_iferror-.thumb.png.8f5





تفاعل الأعضاء


جزاك الله خير ..

عندي سؤال اذا سمحتم 

اريد دالة تقوم بحساب الزيادة في رقم معين في كولم 

يعني مثلا  عندي الرقم هو 100 اكثر من 100 يتم حسابة في كولم لوحدة و القيمة المحتسبة تضرب في 2 .. يعني مثلا العامل انتج 150 متر والمطلوب منه 100متر تعتبر ال 50متر اضافي لة يضرب في ريالين ..

ولكم فائق الشكر والتقدير 

شارك هذا التعليق


رابط هذا التعليق
شارك على الشبكات الإجتماعية


يجب أن تكون عضوًا لدينا لتتمكّن من التعليق

انشاء حساب جديد

يستغرق التسجيل بضع ثوان فقط


سجّل حسابًا جديدًا

تسجيل الدخول

تملك حسابا مسجّلا بالفعل؟


سجّل دخولك الآن