جمع القيم حسب السنة في Excel

افترض أن لديك نطاق بيانات يحتوي على قائمة تواريخ ومبالغ مبيعات، وتريد الآن حساب إجمالي المبلغ بناءً على سنوات معيَّنة، كما هو موضَّح في لقطة الشاشة أدناه. في Excel، يمكن لوظيفتي **SUMIFS** و**SUMPRODUCT** العاديتين مساعدتك في إنجاز هذه المهمة بسرعة وسهولة. ويعرض هذا البرنامج التعليمي كيفية استخدام هاتين الوظيفتين بالتفصيل.

• جمع القيم حسب السنة في Excel باستخدام وظيفتي SUMIFS وDATE
• جمع القيم حسب السنة في Excel باستخدام وظيفة SUMPRODUCT

جمع القيم حسب السنة في Excel باستخدام وظيفتي SUMIFS وDATE

لجمع القيمة الإجمالية استنادًا إلى سنة معيَّنة، يجب عليك تحديد أول وآخر تاريخ في تلك السنة باستخدام وظيفة DATE، ثم استخدام وظيفة SUMIFS لجمع جميع القيم ضمن تاريخ محدد، والصيغة العامة هي:

• sum_range: النطاق الذي تحتوي قيمه على القيم المراد جمعها؛
• date_range: النطاق الذي تحتوي قيمه على التواريخ التي سيتم اختبارها للسنة المعيَّنة؛
• year: السنة المعيَّنة التي تريد جمع القيم استنادًا إليها. ()السنة، 1،1 تشير إلى أول يوم في السنة، والسنة، 12،31 تشير إلى آخر يوم في السنة.)

لحل هذه المهمة في Excel، يُرجى نسخ الصيغة أدناه أو إدخالها في خلية فارغة، ثم سحب الصيغة لأسفل إلى خلايا أخرى للحصول على الناتج:

شرح هذه الصيغة:

=SUMIFS($C$2:$C$12,$B$2:$B$12,«>=»&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31))

• $C$2:$C$12: هو «نطاق_الجمع» الذي يحتوي على القيم التي نريد جمعها.
• $B$2:$B$12,«>=»&DATE(E2,1,1): تتحقَّق من التواريخ في النطاق B2:B12 التي تكون أكبر من أو تساوي 01/01/2020 – أي أول يوم في السنة المحددة في الخلية E2.
• $B$2:$B$12,«<=»&DATE(E2,12,31): تتحقَّق من التواريخ في النطاق B2:B12 التي تكون أقل من أو تساوي 31/12/2020 – أي آخر يوم في السنة المحددة في الخلية E2.
• SUMIFS($C$2:$C$12,$B$2:$B$12,«>=»&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31)): بعد استيفاء المعايير، سيتم العثور على القيم في النطاق C2:C12 وجمعها.

جمع القيم حسب السنة في Excel باستخدام وظيفة SUMPRODUCT

هنا، هناك صيغة بسيطة أخرى تم إنشاؤها بواسطة وظيفة SUMPRODUCT ويمكنها أيضًا مساعدتك، والصيغة العامة هي:

• date_range: النطاق الذي تحتوي قيمه على التواريخ التي سيتم اختبارها للسنة المعيَّنة؛
• year: السنة المعيَّنة التي تريد جمع القيم استنادًا إليها؛
• sum_rangeالنطاق الذي تحتوي قيمه على القيم التي تريد جمعها.

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

شرح هذه الصيغة:

=SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)

• YEAR($B$2:$B$12)=E2: تقوم دالة YEAR باستخراج رقم السنة من كل خلية تاريخ ضمن النطاق B2:B12، ثم يقارن هذا التعبير ما إذا كانت السنة في الخلية E2 مطابقة للسنوات في ذلك النطاق. وستحصل على مصفوفة كالتالي: {TRUE؛FALSE؛TRUE؛TRUE؛FALSE؛TRUE؛TRUE؛FALSE؛FALSE؛TRUE؛FALSE}.
• (YEAR($B$2:$B$12)=E2)*$C$2:$C$12={TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}*$C$2:$C$12: في هذا التعبير، تُحوَّل قيمتي TRUE وFALSE تلقائيًا إلى 1 و0 على التوالي، ثم تُضرب هذه المصفوفة بكل مبلغ في النطاق C2:C12، لتُنتج مصفوفة جديدة كالتالي: {347؛0؛398؛430؛0؛379؛412؛0؛0؛420؛0}.
• SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)= SUMPRODUCT({347;0;398;430;0;379;412;0;0;420;0}): تقوم دالة SUMPRODUCT أخيرًا بجمع جميع الأرقام في هذه المصفوفة وإرجاع الناتج وهو 2386.

الوظيفة ذات الصلة المستخدمة:

مقالات إضافية: