ذكرنا سابقا في درس الصيغ والدوال في اكسل أنّه توجد أنواع مختلفة من الدوال، ومصنفة حسب المجالات المختلفة، منها الدوال الهندسية، الدوال المالية، الدوال المنطقية، دوال الوقت والتاريخ...إلخ.
سنتحدث في هذا الدرس بشكل مفصل عن دالة IF الشرطية، وهي إحدى الدوال المنطقية. تُستخدم هذه الدالة لإرجاع قيمة محدد إذا تحقق الشرط، وقيمة أخرى إذا لم يتحقق.
الصيغة الأساسية لدالة IF هي:
IF(logical_test; [value_if_ture]; [value_if_false])
- logical_test هو الشرط، كمقارنة قيمتين أو خليتين فيما إذا كانت إحدى القيم أكبر من الأخرى، وهذا الشرط مطلوب تحديده في الصيغة.
- value_if_true هي القيمة التي يتم إرجاعها إذا تحقق الشرط، وتحديدها في الصيغة مطلوب.
- value_if_false هي القيمة التي يتم إرجاعا إذا لم يتحقق الشرط، وتحديدها في الصيغة اختياري.
سنقوم بشرح بعض الأمثلة لتوضيح مفهوم هذه الدالة.
مثال 1: في هذا المثال سنشرح أبسط صيغ استخدام دالة IF. في الجدول التالي مجموعة من القيم في عمودين، A وB. المطلوب هو مقارنة القيم في العمودين، وإذا كانت القيم في العمود A أكبر من القيمة في العمود B يتم إرجاع القيمة "نعم" في عمود "النتيجة"، وإلا يتم إرجاع القيم "كلا" في عمود "النتيجة".
سنكتب الصيغة في الخلية الأولى من عمود النتيجة، C2:
أي أنّ الشرط logical_test هو A2>B2، النتيجة إذا تحقق الشرط value_if_true هي "نعم"، والنتيجة إذا لم يتحقق الشرط value_if_false هي "كلا".
بالطبع ستكون النتيجة "نعم" لأن 12 أكبر من 10. سنكرر هذه الصيغة على بقية الخلايا في عمود "النتيجة"، وسنستخدم زر التعبئة في حافة الخلية لتطبيق التعبئة التلقائية:
إذا كانت هناك نصوص ضمن الصيغة يجب أن توضع بين علامتي اقتباس كما فعلنا مع الكلمتين "نعم" و "كلا" لأننا نريد إظهارها بصيغة نصوص في النتيجة.
مثال 2: في هذا المثال مجموعة من السلع الصيفية والشتوية، والمطلوب هو إرجاع نتيجة الخصم "%50" إذا كانت السلعة صيفية والقيمة "0" إذا كانت السلعة شتوية. سنقوم بكتابة الصيغة في الخلية C2:
لاحظ أنه يمكن استخدام النصوص في صيغة الشرط أيضا، لكن يجب أن توضع بين علامتي اقتباس، كما فعلنا مع النص "صيفي".
سنقوم بنسخ الصيغة إلى باقي الخلايا باستخدام التعبئة التلقائية:
لاحظ أيضا أن نتيجة الخلية C5 خاطئة على الرغم من كتابة الصيغة بصورة صحيحة إذ يجب أن يكون الخصم 50% لأنّ السلعة صيفية. والسبب هو وجود مسافة بادئة إضافية قبل النص "صيفي" لذلك لم نحصل على النتيجة المتوقعة. ولحل هذه المشكلة سنستخدم الدالة TRIM التي تقوم بإزالة كافة المسافات الإضافية بين النصوص، باستثناء المسافات الفردية بين الكلمات.
وستكون الصيغة بالشكل التالي:
وعند نسخ الصيغة إلى باقي الخلايا ستظهر النتيجة الصحيحة.
ذكرنا أن تحديد قيمة value_if_false في الصيغة أمر اختياري، وإذا لم نقم بتحديدها سيتم إرجاع القيمة 0 في كلا الحالتين، إذا تحقق الشرط أو لم يتحقق.
دوال IF المتداخلة
ويُقصد بها دالة IF داخل دالة IF أخرى، وهذه الصيغة تتيح لك اختبار العديد من المعايير وزيادة عدد النتائج المحتملة. وسنوضح طريقة كتابة الصيغة بالمثالين التاليين:
مثال 1: في الجدول أدناه مجموعة درجات لمجموعة من الطلاب، والمطلوب هو إرجاع النتيجة "ممتاز" إذا كانت الدرجة أكبر أو تساوي 90، النتيجة "جيد جدا" إذا كانت الدرجة أكبر أو 80-90، النتيجة "جيد" إذا كانت النتيجة أكبر أو تساوي 70-80، النتيجة "متوسط" إذا كانت النتيجة أكبر أو تساوي 60-70، النتيجة "مقبول" إذا كانت النتيجة أكبر أو تساوي 50-60، أو النتيجة "راسب" إذا لم يتحقق الشرط السابق:
سنحدد الخلية C2 وسنكتب الصيغة في شريط الصيغة:
ستعمل الصيغة كالتالي: سيتم أولا تقييم الشرط الأول وهو إذا كانت قيمة الخلية B2 أكبر أو تساوي 90، فإذا تحقق الشرط سيتم إرجاع النتيجة "ممتاز" وتتوقف. وإذا لم يتحقق ستنتقل إلى الشرط الثاني، وهو إذا كانت القيمة أكبر أو تساوي 80. لكن القيمة أكبر أو تساوي 80 تشتمل على القيم من 80-100، والقيمة من 90-100 يجب أن تكون نتيجتها "ممتاز" وليس "جيد جدا" كيف سيتم الأمر؟ لن يتم احتساب القيم من 90-100 لأن الشرط الأول هو غير متحقق من الأصل، أي أنّ القيم التي يتم تقييمها في الشرط الثاني هي بالفعل أقل من 90.
إذا تحقق الشرط الثاني سيتم إرجاع القيمة "جيد جدا" ثم تتوقف الصيغة، وإذا لم يتحقق سيتم الانتقال إلى الشرط الثالث، وهكذا.
سنقوم بنسخ الصيغة إلى بقية الخلايا باستخدام التعبئة التلقائية:
مثال 2: في الجدول التالي مجموعة من المنتجات المطلوب إيجاد أسعارها بعد الخصم، لكن نسبة الخصم تختلف حسب سنة الإنتاج:
سنحدد الخلية D2 لإظهار النتيجة فيها، وسنكتب الصيغة في شريط الصيغة كالتالي:
يتم أولا إيجاد مقدار الخصم بضرب السعر بالنسبة 75% إذا كانت سنة الإنتاج (في العمود B) 2011، بالنسبة 50% إذا كانت سنة الإنتاج 2012، وبالنسبة 25% إذا كانت سنة الإنتاج 2013. بعدها يتم طرح مقدار الخصم من السعر الأصلي في عمود "السعر".
لم نستخدم قيم سنوات الإنتاج ونسب الخصم بشكل مباشر في الصيغة وإنما قمنا باستخدام الخلايا التي تحتويها كمرجع مطلق absolute reference (أي استخدمنا $G$2 بدلا من 2011، $H$2 بدلا من 75% وهكذا بالنسبة لبقية القيم). والسبب هو ليكون بإمكاننا تغيير هذه القيم لاحقا دون الحاجة إلى إعادة كتابة الصيغة.
لاستخدام الخلية كمرجع مطلق اضغط F4 بعد أن تقوم بتحديد الخلية (أو كتابة اسمها) عند كتابتك للصيغة.
وكما في المثال السابق ستعمل الصيغة كالتالي: ستقوم أولا بتقييم الشرط الأول، وهو إذا كانت سنة الإنتاج تساوي 2011، فإذا تحقق الشرط ستقوم بضرب السعر بالنسبة 75% وتتوقف ثم تطرح الناتج من السعر الأصلي. وإذا لم يتحقق الشرط ستنتقل إلى الشرط الثاني، وهكذا.
سنقوم بنسخ الصيغة إلى بقية الخلايا باستخدام التعبئة التلقائية:
ملاحظة: بالإمكان كتابة دالات داخلية حتى 64 دالة في الصيغة الواحدة.
استخدام دالة IF مع المعاملات AND أو OR
تُستخدم الدالة IF مع الدالة AND لإظهار نتيجة معينة إذا صحت جميع المعطيات. وتُستخدم مع الدالة OR لعرض نتيجة معينة إذا صح جزء واحد على الأقل من المعطيات.
تكون البنية العامة لدالة AND كالتالي:
AND(argument1; argument2;…)
والبنية العامة للدالة OR كالتالي:
OR(argument1; argument2;…)
يسمى argument الوسيطة (وأفضل تسميته المُعطى).
سنستخدم المثال التالي لتوضيح طريقة كتابة الصيغة:
في هذا المثال لدينا ثلاثة اختبارات لعدد من الطلاب، والمطلوب هو إرجاع القيمة "نعم" في عمود "جميع التقييمات ممتازة" إذا كانت الدرجات في جميع الاختبارات للطالب الواحد أكبر أو تساوي 90. سنحدد الخلية E2 لعرض النتيجة للطالب الأول وسنكتب الصيغة في شريط الصيغة:
ستعمل الصيغة كالتالي: إذا كانت درجة الاختبار الأول ودرجة الاختبار الثاني ودرجة الاختبار الثالث أكبر أو تساوي 90 فسيتم إرجاع القيمة "نعم" في عمود "جميع التقييمات ممتازة". أما إذا كانت إحدى الدرجات أقل من 90 فسيتم إرجاع القيمة "كلا"، حتى ولو كانت بقية الدرجات أكبر أو تساوي 90.
سنقوم بنسخ الصيغة إلى بقية الخلايا باستخدام التعبئة التلقائية:
أما عمل الدالة OR فهو مشابه لعمل الدالة AND، باستثناء أنها تقوم بإرجاع القيمة "نعم" إذا صح معطى واحد على الأقل من المعطيات، والقيمة "كلا" إذا لم يصح أي من المعطيات.
تُكتب الصيغة التي تتضمن دالة IF والمعامل OR كالتالي:
بعد كتابة الصيغة سنقوم بنسخها إلى بقية الخلايا بالتعبئة التلقائية:
استخدام الدالتين COUNTIFS وSUMIFS
COUNTIFS
تستخدم هذه الدالة لتطبيق مجموعة من المعايير على خلايا ضمن نطاق محدد، ثم إيجاد عدد المرات التي تتحقق فيها كافة المعايير.
البنية العامة للدالة COUNTIFS هي كالتالي:
COUNTIFS([criteria_range1]; criteria1; [criteria_range2]; criteria2];…)
- criteria_range1 هو نطاق الخلايا التي سيتم تطبيق المعيار الأول عليها، وهو مطلوب في الصيغة.
- criteria1 هو المعيار الذي سيطبق على نطاق المعايير الأول لتحديد الخلايا التي سيتم حساب عددها فيما لو تحقق، وهو مطلوب في الصيغة أيضا.
أما نطاق المعايير الثاني والمعيار الثاني فهي اختيارية في الصيغة.
ملاحظة: جميع النطاقات يجب أن تكون بعدد الصفوف والأعمدة نفسه لنطاق المعايير الأول، لكن لا يُشترط أن تكون متجاورة. وكذلك بإمكانك استخدام أحرف البدل wildcard characters مثل النجمة (*) علامة الاستفهام في إنشاء المعايير.
مثال: في الجدول أدناه مجموعة من المشاريع التي تبدأ وتنتهي في أوقات محددة. المطلوب هو حساب عدد المشاريع التي تبدأ بعد التاريخ 1/2/2015 وتنتهي قبل التاريخ 1/7/2015. لقد قمنا بكتابة المعايير في جدول منفصل لاستخدمها كمرجع مطلق:
لقد استخدمنا المعايير في الخليتين A12 وB12 كمراجع مطلقة ليصبح بإمكاننا تغيير هذه التواريخ لاحقا عند الحاجة دون أن نضطر إلى إعادة كتابة الصيغة من البداية.
سنقوم بتحديد نطاق المعايير الأول (criteria_range1 (B2:B9، ثم سنحدد المعيار الأول (criteria1) وسنضغط F4 لاستخدامه كمرجع مطلق. بعدها سنحدد نطاق المعايير الثاني C2:C9، ثم المعيار الثاني كمرجع مطلق، وبذلك تصبح الصيغة لهذا المثال كالتالي:
سيتم بتقييم نطاق الخلايا الأول B2:B9 وحساب عدد التواريخ التي تحقق المعيار >1/2/2015، هناك سبعة في هذا المثال؛ جميع المشاريع ماعدا المشروع 7. بعدها سيقوم بفحص تلك التواريخ السبعة وحساب عدد التواريخ التي تحقق المعيار <1/7/2015 وهناك ثلاثة في هذا المثال، المشروع 1، المشروع 3، والمشروع 5. وبالتي ستكون عدد التواريخ التي تحقق المعيارين 3:
SUMIFS
تستخدم هذه الدالة لجمع القيم في الخلايا التي تحقق عددا من المعايير ضمن نطاق من الخلايا، والبنية العامة لها هي كالتالي:
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
- sum_range هو نطاق الخلايا المطلوب جمع القيم التي تحقق المعايير فيه.
- criteria_range1 هو نطاق الخلايا الذي سيتم تطبيق المعيار الأول عليه، وهو مطلوب في الصيغة.
- criteria1 هو المعيار الذي سيطبق على نطاق المعايير الأول لتحديد الخلايا التي سيتم جمعها وهو مطلوب في الصيغة أيضا.
أما نطاق المعايير الثاني criteria_range2 والمعيار الثاني criteria1 فهي اختيارية في الصيغة.
مثال: في الجدول أدناه مجموعة من الطلبات والمبيعات المصنفة حسب مندوب المبيعات والمنطقة. المطلوب هو إيجاد مجموع المبيعات حسب المنطقة والتي يحقق فيها مندوب المبيعات عدد طلبات أكبر أو يساوي 20.
سنقوم أولا بتحديد نطاق المجموع sum_range، وهو نطاق الخلايا D2:D8 وسنستخدمه كمرجع مطلق. ثم سنحدد نطاق المعايير الأول criteria_range1، وهو نطاق الخلايا B2:B8 وسنستخدمه كمرجع مطلق أيضا. أما المعيار الأول فهو "الشمال"، وسنستخدمه كمرجع نسبي relative reference. وأخيرا سنحدد نطاق المعايير الثاني، وهو نطاق الخلايا C2:C8 كمرجع مطلق ثم المعيار الثاني >=20 كمرجع نسبي:
سيتم أولا تحديد مندوبي المبيعات الذي يحققون المعيار "الشمال"، وهما "زيد" و"سامي" في هذا المثال. ومن بين مندوبي المبيعات هؤلاء سيتم تحديد أيهما حقق عدد طلبات أكبر أو يساوي 20، وكلاهما قد حقق هذا العدد في هذا المثال. وبالتالي سيتم جمع المبيعات لكليهما.
نفس الخطوات تطبق على المعايير "الجنوب"، الشرق"، والغرب" عند نسخ الصيغة باستخدام التعبئة التلقائية:
لاحظ أن نتيجة الشرق تركت فارغة، وذلك لأن المعيار الثاني (عدد الطلبات أكبر أو يساوي 20) لا يتحقق.
ملاحظة: إنّ معايير [الشمال]، [الجنوب]، [الشرق]، [الغرب] هي في الحقيقة [=الشمال]، [=الجنوب]، [=الشرق]، [=الغرب]. لكننا لم نقم بكتابتها بالصيغة الأخيرة لأنّ معيار [=] هو المعيار الافتراضي في البرنامج، وسيتم اعتماده ما لم يتم تحديد غيره من المعايير.
استخدام الدالتين AVERAGEIFS وIFERROR
AVERAGEIFS
تُستخدم هذه الدالة لإيجاد المتوسط لمجموعة من الخلايا التي تحقق عدد من المعايير، والبنية العامة لها هي:
AVERAGEIFS(average_range; criteria_range1; criteria1; [criteria_range2; criteria2]…)
- average_range هو نطاق الخلايا المطلوب إيجاد متوسط قيمها. ويتم تجاهل الخلايا الفارغة أو القيم النصية. وهو مطلوب في الصيغة.
- criteria_range1 هو نطاق المعايير الأول الذي يتم تطبيق المعيار الأول عليه، وهو مطلوب في الصيغة.
- criteria1 المعيار الذي يتم على أساسه تقييم نطاق المعايير الأول، وهو مطلوب في الصيغة أيضا.
أما نطاق المعايير الثاني criteria_range2 والمعيار الثاني criteria1 فهي اختيارية في الصيغة.
مثال: سنستخدم المثال السابق لتطبيق دالة AVERAGEIFS. لكن باختلاف المطلوب، وهو إيجاد متوسط المبيعات حسب المنطقة والتي يحقق فيها مندوب المبيعات عدد طلبات أكبر أو يساوي 20.
سنقوم أولا بتحديد نطاق المتوسط average_range، وهو نطاق الخلايا التي نريد تطبيق دالة ANERAGEIFS عليها. أي النطاق D2:D8 وسنستخدمه كمرجع مطلق. ثم سنحدد نطاق المعايير الأول criteria_range1، وهو نطاق الخلايا B2:B8 وسنستخدمه كمرجع مطلق أيضا. أما المعيار الأول فهو "الشمال"، وسنستخدمه كمرجع نسبي relative reference. وأخيرا سنحدد نطاق المعايير الثاني، وهو نطاق الخلايا C2:C8 كمرجع مطلق ثم المعيار الثاني >=20 كمرجع نسبي:
سيتم أولا تحديد مندوبي المبيعات الذي يحققون المعيار "الشمال"، وهما "زيد" و"سامي" في هذا المثال. ومن بين مندوبي المبيعات هؤلاء سيتم تحديد أيهما حقق عدد طلبات أكبر أو يساوي 20، وكلاهما قد حقق هذا العدد في هذا المثال. وبالتالي سيتم إيجاد متوسط المبيعات لكليهما.
نفس الخطوات تطبق على المعايير "الجنوب"، الشرق"، والغرب" عند نسخ الصيغة باستخدام التعبئة التلقائية:
لكن كما نلاحظ وجود خطأ القسمة على صفر (DIV/0#!) في الخلية H4، والسبب هو أنّ كلا المعيارين لم يتحققا. فمن منطقة الشرق يوجد أحمد فقط، وعدد الطلبات الذي حققه أصغر من 20. لذلك حصل هذا الخطأ. إذ تقوم دالة AVERAGEIFS بإرجاع القيمة صفر إذا لم تتحقق جميع المعايير. ولإصلاح هذا الخطأ (وغيرها من الأخطاء التي يمكن أن تحصل) سنستخدم الدالة IFERROR.
IFERROR
تستخدم هذه الدالة لإرجاع قيمة نقوم بتحديدها إذا كان ناتج الصيغة خطأ، أو إرجاع نتيجة الصيغة إذا كانت صحيحة.
البنية العامة لهذه الدالة هي:
IFERROR(value, value_if_error)
- value هي القيمة التي يتم فحصها بحثا عن الخطأ.
- value_if_error هي القيمة التي يتم إرجاعها عند وجود خطأ في الصيغة.
سنقوم بإدخال هذه الدالة مع دالة AVERAGEIFS وبالصيغة التالية:
في هذا المثال تمثل دالة AVERAGEIFS قيمة value التي يتم فحصها بحثا عن الخطأ. و"لا يوجد متوسط" هي قيمة value_if_error التي يتم إرجاعها عند عدم وجود قيم لإيجاد المتوسط. سنستخدم التعبئة التلقائية لنسخ الصيغة إلى بقية الخلايا:
أفضل التعليقات
انضم إلى النقاش
يمكنك أن تنشر الآن وتسجل لاحقًا. إذا كان لديك حساب، فسجل الدخول الآن لتنشر باسم حسابك.