كيفية استخدام دالة SUMIF في برنامج Excel مع معايير متعددة: 3 أمثلة عملية

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

قم بإعداد جدول مبيعات ثابت

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

SUMIF و SUMIFS: ما التغييرات؟

صيغة الشرط الواحد هي =SUMIF(range, criteria, [sum_range]): . النطاق هو العمود الذي يتحقق منه برنامج Excel، والمعيار هو القيمة أو القاعدة المراد مطابقتها، و sum_range الاختياري يحتوي على الأرقام المراد جمعها. على سبيل المثال، التحقق من أسماء الوكلاء في العمود A وجمع المبيعات من العمود E يحافظ على الشرط والقيم في نطاقين منفصلين.

يستخدم برنامج Excel دالة SUMIFS لمطابقة عدة شروط =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). لاحظ أن دالة SUMIFS تضع نطاق المجموع أولاً، بينما تضعه دالة SUMIF ثالثاً. تدعم Microsoft ما يصل إلى 127 زوجًا من النطاقات والمعايير في دالة SUMIFS. يجب أن تكون النطاقات المتطابقة بنفس الحجم والشكل.

مثال 1: جمع مبيعات وكيل واحد باستخدام دالة SUMIF

لحساب إجمالي مبيعات تشارلز عبر جميع المنتجات والأشهر والمناطق، أدخل ما يلي:

=SUMIF(A2:A7,"Charles",E2:E7)

يتحقق برنامج Excel A2:A7من وجود اسم "Charles"، ثم يجمع القيم في الصفوف المطابقة E2:E7. والنتيجة هي 1250 دولارًا (500 دولار + 750 دولارًا). يمكنك استبدال الاسم بين علامتي الاقتباس بمرجع خلية، مثل =SUMIF(A2:A7,G2,E2:E7)"Charles"، إذا كانت الخلية G2 تحتوي على اسم "Charles". يسهل مرجع الخلية إعادة استخدام النتيجة مع وكيل آخر.

مثال 2: أضف مبيعات أليكس في يناير باستخدام دالة SUMIFS

هنا، يجب أن تستوفي عملية البيع الشرطين التاليين: أن يكون اسم الوكيل أليكس وأن يكون الشهر يناير. استخدم دالة SUMIFS لأن كل زوج من المعايير يجب أن يتطابق:

=SUMIFS(E2:E7,A2:A7,"Alex",C2:C7,"Jan")

الوسيط الأول هو نطاق المبيعات. يتحقق الزوج التالي من عمود "الوكيل" لأليكس؛ ويتحقق الزوج الأخير من عمود "الشهر" ليناير. يتم تضمين الصف الذي يحقق كلا الشرطين فقط، لذا فإن الإجابة هي 420 دولارًا . لجعل المعايير قابلة للتعديل، ضع أليكس في الخلية G2 ويناير في الخلية H2، ثم استخدم =SUMIFS(E2:E7,A2:A7,G2,C2:C7,H2).

مثال 3: أضف مبيعات أحد الوكيلين

"تشارلز أو إليوت" هو شرط "أو" . أحد الخيارات المباشرة هو جمع نتيجتي دالة SUMIF:

=SUMIF(A2:A7,"Charles",E2:E7)+SUMIF(A2:A7,"Elliot",E2:E7)

تُرجع دالة SUMIF الأولى 1250 دولارًا لتشارلز، بينما تُرجع الثانية 1100 دولارًا لإليوت. مجموعهما معًا هو 2350 دولارًا . هذه الطريقة سهلة القراءة وفعّالة عند التعامل مع قائمة أسماء قصيرة. إذا وضعت الأسماء في الخليتين G2 وH2، فاستخدم =SUMIF(A2:A7,G2,E2:E7)+SUMIF(A2:A7,H2,E2:E7)دالة SUMIF لتغيير القائمة دون الحاجة إلى إعادة كتابة الصيغة. في حال تداخل المعايير، قد يؤدي جمع نتائج منفصلة إلى احتساب الصف نفسه أكثر من مرة؛ وهذان الاسمان مختلفان ولا يتداخلان.

أخطاء شائعة في استخدام دالتي SUMIF و SUMIFS

• عكس ترتيب الوسائط: تبدأ دالة SUMIF بنطاق المعايير؛ بينما تبدأ دالة SUMIFS بنطاق المجموع. تحقق من ترتيب الدوال قبل نسخ الصيغة.
• استخدام دالة SUMIF لشرط AND: بالنسبة للعامل أليكس وشهر يناير معًا، استخدم دالة SUMIFS. نتائج دالة SUMIF المنفصلة تجمع البدائل (OR)، وليس الشروط المتزامنة.
• عدم محاذاة النطاقات:E2:E7 في دالة SUMIFS، استخدم نطاقات ذات أبعاد متطابقة ، مثل A2:A7و و C2:C7. قد يؤدي بدء أو إنهاء نطاق ما في صف مختلف إلى ظهور خطأ أو إجمالي غير صحيح.
• ترك معايير النص بدون علامات اقتباس: اكتب النص بين "Charles"علامتي اقتباس، أو أشر إلى خلية تحتوي على هذا النص. بالنسبة لقاعدة المقارنة، قم بتضمينها كنص، مثل ">500":

إذا أعاد برنامج Excel القيمة صفر، فتأكد من تطابق المعايير تمامًا مع قيم ورقة العمل، وأن خلايا المبيعات تحتوي على أرقام وليس نصوصًا شبيهة بالأرقام. لمزيد من التفاصيل، راجع مرجع دالة SUMIF من Microsoft ، ومرجع دالة SUMIFS ، ودليل جمع القيم بشروط متعددة . في حالة حدوث خطأ، راجع خطوات استكشاف أخطاء SUMIF/SUMIFS وإصلاحها#VALUE! من Microsoft .

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