يعد برنامج مايكروسوفت إكسل من أهم البرامج المستخدمة في الشركات والمؤسسات المختلفة. حيث يقوم هذا البرنامج بتنظيم البيانات وإنشاء الرسوم البيانية وجدولة المهام وتنظيم بيانات العملاء والمحاسبة المالية وغيرها الكثير. كما أننا قد نضطر لاستخراج البيانات أو إنشاء التقارير والإحصائيات فنستخدم العديد من الدوال الشرطية في إكسل. من أهم هذه الدوال دالة إذا الشرطية IF، ودالة العد الشرطي COUNTIF، والجمع الشرطي SUMIF، والمعدل الشرطي AVERAGEIF.
إذا الشرطية IF أهم الدوال الشرطية في إكسل
تعد دالة إذا الشرطية IF أحد أهم الدوال الشرطية في إكسل حيث يكون لدينا شرط منطقي إما أن يتحقق أو لا. كما يتم بناء جملة دالة إذا الشرطية IF بالشكل التالي:
([value_if_false],[value_if_true],logical_test)IF=
حيث logical_test الشرط المنطقي أي الشرط المراد تحقيقه فرضاً أن يكون عمر الموظف أقل من 30 سنة.
value_if_true تعني القيمة التي ستظهر في حال تحقق الشرط، بينما value_if_false تعني القيمة التي ستظهر في حال لم يتحقق الشرط. القيمة ممكن أن تكون معادلة، أو خلية أو رقماً، أو نصاً يوضع بين علامتي اقتباس.
أمثلة
- if(A2>20,”Large”,”small”)= إظهار كلمة Large في حال كانت القيمة في الخلية A2 أكبر من 20 و small إذا كانت أقل.
- if(C2=”Full Time”,D2*15%+D2,D2*10%+D2)= في حال كانت قيمة الخلية C2 هي دوام كامل وهي طبعاً تمثل دوام موظف، فيتم زيادة 15% لمرتبه الممثل في الخلية D2. في حال عدم تحقق الشرط سيتم زيادة 10% أي دوامه جزئي.
مثال عن استخدام دالة إذا الشرطية IF
إذا الشرطية المتعددة (IF المتعددة)
يكون لدينا شرطين أو أكثر وفي هذه الحالة يكون الشرطين متداخلين. على سبيل المثال افحـص الخانـة A1 فـي حـال كانـت أكبـر مـن 30 ضـع Large، أمـا فـي حـال لـم يكـن كذلـك افتـح شـرط جديـد وتحقـق مـن جديـد، إذا كانـت الخليـة ذاتهـا أكبـر مـن 20 ضـع Medium وإذا لـم يتحقـق كل مـا سـبق ضـع Small.
أمثلة
- حسـاب الراتـب الجديـد إذا كانـت الزيـادة هـي 15 بالمئـة لمـن يعمـل بـدوام كامـل (Full Time) و10 بالمئـة لمـن يعمـل بـدوام جزئـي (Short Time) و5 بالمئـة لبقيـة الموظفيـن ويتم التعبير عنها بالشكل التالي:
if(C2=”Full Time”,D2*15%+D2, if( C2=”Short Time”,D2*10%+D2,D2*5%+D2))=
تعدد الشروط مع OR و AND
AND وهـي معادلـة تحقـق جميـع الشـروط، أي فـي حـال أردنـا أن تتحقـق مجموعـة مـن الشـروط نسـتخدم AND وسـوف يعطينـا True فـي حـال تحققهـا جميعاً وFalse فـي حـال عدم تحقق أي شرط منهـا علـى الأقل. أمــا OR فهــي معادلــة تحقــق شــرط واحــد علــى الأقل مــن بين عدد من الشروط، ويعطــي True عند تحقــق واحــد منهــا علــى الأقل، وFalse فــي حــال عدم تحقــق أي شــيء.
أمثلة
- معرفة الأشخاص الذين تجاوزت علاماتهم 50 في الامتحانات الأولية والنهائية ويتم التعبير عنها:
AND(B2>50,C2>50)= حيث جدول B يمثل العلامات الأولية و C يمثل العلامات النهائية. حيث سيتم إظهار النتيجة True في حال تحقق الشرطين والنتيجة False في حال عدم تحقق أحدهما أو كليهما.
الجمع الشرطي SUMIF أحد الدوال الشرطية في إكسل
يعد من عائلة الدالة SUM دالة الجمع من دوال إكسل للعمليات الحسابية. إلا أن هذه الدالة لا تجمع إلا في حال تحقق شرط معين. الوسيط الأول يمثل نطاق الشرط أي المجال الذي سنطبق عليه الشرط وفي حال تحققه سنجمع قيم الخانات. أما الوسيط الثاني يمثل الشرط الذي يجب أن يتحقق حتى نقوم بالجمع. بالنسبة للوسيط الثالث فهو نطاق الجمع أي نطاق الخانات الحاوية على القيم المراد جمعها في حال تحقق الشرط.
أمثلة
- SUMIF(B2:B8,”FullTime”,C2:C8)= تعني حساب مجموع رواتب الموظفين الذين يعملون براتب كامل.
- SUMIF(C2:C8,”>1500″)= حساب مجموع رواتب الموظفين الذين تتجاوز مرتباتهم 1500.
يمكننا التخلي عن الوسيط الثالث من معادلة SUMIF إذا كان النطاق المشروط هو ذاته نطاق الجمع.
الجمع بشروط متعددة SUMIFS أحد الدوال الشرطية في إكسل
أحد دوال برنامج مايكروسوفت إكسل الحسابية والشرطية الهامة. حيث أنه يتم الجمع في حال كان لدينا أكثر من شرط. تكتب بالشكل:
SUMIFS(sum_range1, criteria_range1,criteria1,criteria-range2, criteria2,….)=
وسطاء هذه الدالة أكثر من وسطاء دالة SUMIF حيث يمثل الوسيط الأول نطاق الجمع وهو الخانات التي سنقوم بجمعها في حال تحقق الشرط. الوسيط الثاني يمثل نطاق الخانات المشروط الأول الذي سوف نضع له شرط حتى يقوم بعد القيمة المقابلة. الوسيط الثالث يمثل المعيار الأول المراد تحقيقه في نطاق الخانات الأول. كما أننا نكرر هذه الوسطاء حتى انتهاء الشروط التي نريد تطبيقها.
أمثلة
- SUMIFS(D2:D8, B2:B8,”Male”,C2:C8, “Short Tim”)= تعني حساب مجموع رواتب الموظفين الذكور الذين يعملون بدوام جزئي. حيث D سيمثل الرواتب وB تمثل الجنس وC تمثل نوع الدوام.
- SUMIFS(C2:C8, A2:A8,”Laptop”,B2:B8, “HP”)= تعني حساب كمية اللابتوبات المباعة من نوع إتش بي HP حيث C تمثل عدد اللابتوبات وA نوع الجهاز وB تمثل نوع اللابتوب.
العد الشرطي COUNTIF أحد الدوال الشرطية في إكسل
دوال الجمع الشرطي SUMIF والعد الشرطي COUNTIF في إكسل
يتم بناء جملة دالة العد الشرطي COUNTIF بكتابة إشارة = ثم كلمة COUNTIF ثم الوسطاء داخل قوسين أول وسيط يمثل المجال الذي سنطبق عليه الدالة والوسيط الثاني يمثل المعيار الذي سنقوم بالعد عليه COUNTIF(range, criteria)=. على سبيل المثال لو كان لديك أسماء موظفين مع طبيعة عملهم وأردنا أن نعرف عدد الموظفين الذين دوامهم كاملاً نختار العمود الذي يحتوي على طبيعة العمل ثم نضع معيار العد بأن يكون دواماً كاملاً فيكون تمثيل دالة العد الشرطي COUNTIF بالشكل COUNTIF(B2:B8, “Full Time”)=
أمثلة
- COUNTIF(B2:B8, B2)= تعني أن يعد جميع القيم في المجال المحدد والتي تساوي قيمتها قيمة B2 حيث وضعنا بدل القيمة الخلية التي تحتوي عليها.
- COUNTIF(A2:A8, “>20”)= تعني هذه الدالة أن نعد جميع القيم التي أكبر من 20 في المجال المحدد.
- COUNTIF(B2:B8, “<> Free lance”)= تعني حساب عدد الموظفين جميعاً باستثناء من يعمل عمل حر.
يتوجب دوماً أن نضع إشارات المقارنة بين علامتي تنصيص.
العد بشروط متعددة COUNTIFS أحد الدوال الشرطية في إكسل
في حال كنا نريد تطبيق أكثر من شرط على قاعدة بيانات محددة هنا نحتاج COUNTIFS لأنها تضع أكثر من شرط. على سبيل المثال يتم بناء هذا الدالة بالشكل COUNTIFS(B2:B8, “Full Time”,C2:C8,”>1300″)= تطلب هذه الدالة عد جميع الموظفين الذين دوامهم كامل وفرضاً رواتبهم أكثر من 1300 طبعاً سيكون الجدول B يمثل الموظفين و C تمثل الرواتب.
فيكون بناء الدالة بالشكل COUNTIFS(Criteria_Range1, Criteria1, Criteria_Range2, Criteria2,…).
أمثلة
- COUNTIFS(C2:C8, “>300″,C2:C8,”<1500”)= تعني حساب عدد الموظفين فرضاً الذين تتراوح رواتبهم بين 300 و1500.
- COUNTIFS(B2:B8, “Full Time”,C2:C8,”<1300″,C2:C8″>300″)= تعني حساب عدد الموظفين الذين يعملون بدوام كامل ورواتبهم تتراوح بين 1300 و300.
حساب المعدل بشرط أو عدة شروط AVERAGEIFS ،AVERAGEIF
مثال عن استخدام دالة المعدل الشرطي AVERAGEIF في إكسل
الدالتان AVERAGEIF وAVERAGEIFS تشبهان الدالتين SUMIF وSUMIFS إلا أنهما تقومان بحساب المعدل أو المتوسط الحسابي الذي يعد من مقاييس النزعة المركزية. حيث AVERAGEIF دالة المعدل الشرطي تعني حساب المعدل بشرط واحد. AVERAGEIFS تعني حساب المعدل في حال تحقق أكثر من شرط. يوجد أيضاً ثلاث وسطاء لكل شرط مراد تحقيقه. الوسيط الأول يمثل نطاق الشرط، أما الوسيط الثاني يمثل المعيار الذي نريد تحقيقه والوسيط الثالث هو نطاق حساب المعدل. في حال وجود قيمة غير رقمية لا يتم تضمينها في حساب المعدل.
أمثلة
- AVERAGEIF(B2:B8,”FULL TIME”,C2:C8)= تعني حساب معدل رواتب الموظفين الذين يعملون بدوام كامل. حيث B تمثل نوع الدوام وC تعبر عن الرواتب هنا لابد من استخدام دالة المعدل الشرطي AVERAGEIF.
- AVERAGEIFS(B2:B8,B2:B8,”>70″,B2:B8,”<90″)= تعني حساب معدل الدرجات بين 70 و90 للطلاب.
مقالات مقترحة
هكذا نكون قد تعرفنا على أهم الدوال الشرطية في إكسل وكيفية كتابتها والتعامل معها حيث تكمن أهمية برنامج إكسل في تسهيل العمل وتوفير الوقت والجهد، ودوره الكبير والهام في العديد من القطاعات. كما أنه قد أصبح من الضروري أن تكون على علم واطلاع بكل الدوال الهامة في هذا البرنامج حتى تتمكن من القيام بمهامك بدقة والحصول على أفضل فرص للعمل.