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

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

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

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

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

• نطاق_الجمع: النطاق الذي يحتوي على القيم المراد جمعها؛
• نطاق_التواريخ: النطاق الذي سيتم اختبار تواريخه للسنة المحددة؛
• السنة: السنة المحددة التي تريد جمع القيم بناءً عليها. (السنة, 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 التي تكون أقل من أو تساوي 12/31/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 يمكن أن تساعدك أيضًا، والصيغة العامة هي:

• نطاق_التواريخ: النطاق الذي سيتم اختبار تواريخه للسنة المحددة؛
• السنة: السنة المحددة التي تريد جمع القيم بناءً عليها؛
• نطاق_الجمع: النطاق الذي يحتوي على القيم المراد جمعها.

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

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

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

• YEAR($B$2:$B$12)=E2: تقوم دالة YEAR بإرجاع رقم السنة من كل خلية تاريخ في النطاق B2:B12. هذا التعبير يتحقق إذا كانت السنة في E2 تساوي السنة في النطاق B2:B12. وستحصل على مصفوفة مثل هذه: {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: في هذا التعبير، سيتم تحويل TRUEs وFALSEs إلى 1s و0s، وسيتم ضرب هذه المصفوفة في كل قيمة في النطاق 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.

الدوال ذات الصلة:

مقالات ذات صلة: