الجمع وفق عدة شروط باستخدام SUMIFS

أنتم تعرفون بالفعل أن SUMIF تجمع القيم المطابقة لقاعدة واحدة، مثل جميع مبيعات المنطقة الشرقية. لكن الأسئلة الواقعية عادةً ما تتضمن أكثر من جانب: ما مبيعات المنطقة الشرقية في يناير؟ هذا يعني وجود شرطين في الوقت نفسه.

هنا تتألق SUMIFS. تشير S في نهايتها إلى قدرتها على جمع عدة معايير، ولا يُضاف الصف إلى الإجمالي إلا إذا اجتاز كل اختبار تحددونه.

في هذا الدرس ستتعلّمون ترتيب الوسائط، وكتابة أول مجموع متعدد المعايير، وتجنّب الأخطاء الشائعة التي يقع فيها كثيرون.

تعكس SUMIFS الترتيب الذي قد تتوقعونه من SUMIF. تأتي الأرقام التي تجمعونها أولًا، ثم يُحدَّد كل شرط في صورة زوج.

• sum_range — القيم المطلوب جمعها
• criteria_range1، criteria1 — الاختبار الأول
• criteria_range2، criteria2 — الاختبار الثاني

يمكنكم إضافة أزواج النطاق والمعيار حتى 127 شرطًا. اقرأوا النمط التالي بصوت عالٍ: اجمعوا هذا، حيث يساوي هذا ذاك، وحيث يساوي هذا ذاك.

تخيّلوا جدولًا يحتوي العمود A فيه على المنطقة، والعمود B على الشهر، والعمود C على المبلغ. تريدون حساب إجمالي مبيعات المنطقة الشرقية في يناير.

توجد المبالغ التي تجمعونها في C:C. يتحقق الشرط الأول من A:A بحثًا عن "الشرق"، ويتحقق الشرط الثاني من B:B بحثًا عن "يناير". ولا يُحتسب الصف إلا عند تحقق الشرطين معًا.

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

إذا كان نطاق الجمع هو C2:C100، بينما كان أحد نطاقات المعايير هو A2:A99، فستُرجع الصيغة الخطأ #VALUE! لأن الصفوف غير متطابقة.

أفضل عادة هي استخدام صفَي البداية والنهاية نفسيهما لجميع النطاقات، أو استخدام أعمدة كاملة مثل A:A باستمرار.

تعمل كتابة "الشرق" بين علامتي اقتباس مباشرةً، لكن التقرير المرن يتيح للمستخدم الاختيار. ضعوا المنطقة في الخلية F1 والشهر في F2، ثم أشيروا إلى هاتين الخليتين باعتبارهما المعيارين.

عند تغيير F1 أو F2، يُعاد حساب الإجمالي فورًا. لاحظوا أن مرجع الخلية العادي لا يحتاج إلى علامات اقتباس — إذ تُستخدم علامات الاقتباس للنص الحرفي المكتوب داخل الصيغة فقط.

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

• ">100" — أكبر من 100
• "<=50" — أصغر من 50 أو يساويه
• "<>0" — لا يساوي صفرًا

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

ماذا لو كان الحد موجودًا في خلية بدلًا من كتابته؟ لا يمكنكم كتابة ">F1" فحسب — فهذا يبحث عن النص الحرفي F1. بدلًا من ذلك، صِلوا عامل المقارنة بالخلية باستخدام الرمز &.

لذلك تنشئ ">"&F1 المعيار أكبر من القيمة الموجودة في F1 أيًا كانت. وتُعد حيلة الربط هذه ضرورية لإنشاء تقارير ديناميكية يتحكم فيها المستخدم.

تتوسع SUMIFS بسهولة. أضيفوا زوجًا آخر من النطاق والمعيار لكل قاعدة جديدة. لنفترض أن العمود D يحتوي على مندوب المبيعات. يمكنكم حساب إجمالي مبيعات المنطقة الشرقية في يناير التي أجرتها "Maria".

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

تدعم معايير النص أحرف البدل. تطابق العلامة النجمية * أي عدد من الأحرف، بينما تطابق علامة الاستفهام ? حرفًا واحدًا بالضبط.

• "North*" تطابق North وNortheast وNorthwest
• "*east*" تطابق أي نص يحتوي على east

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

يجدر بكم حفظ الفرق بينهما لأن ترتيب الوسائط معكوس:

• SUMIF: range, criteria, [sum_range] — يأتي النطاق المطلوب اختباره أولًا، بينما يكون sum_range اختياريًا ويأتي أخيرًا.
• SUMIFS: sum_range, criteria_range1, criteria1, ... — يأتي sum_range دائمًا أولًا.

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

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

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

يمكنكم الآن جمع الأرقام التي تستوفي عدة شروط في الوقت نفسه:

• SUMIFS(sum_range, range1, crit1, range2, crit2, ...) — يأتي sum_range أولًا.
• يجب أن تكون جميع النطاقات بالحجم نفسه، وإلا فستحصلون على الخطأ #VALUE!.
• استخدموا ">"&F1 للمقارنة بخلية، و* / ? لأحرف البدل.
• تستخدم الشروط منطق AND — يجب أن يجتاز الصف جميع الشروط.

التالي: عدّ الصفوف وفق عدة شروط باستخدام COUNTIFS.