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

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

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

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

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

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

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

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

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

• 2 دولارات كندية: 12 دولارًا كنديًا: هو “sum_range” الذي يحتوي على القيم التي نريد جمعها.
• $ B $ 2: $ B $ 12، "> =" & DATE (E2,1,1،XNUMX،XNUMX): يتحقق من التواريخ الموجودة في النطاق B2: B12 الأكبر من 01/01/2020 أو التي تساويها - اليوم الأول من السنة في الخلية E2.
• $ B $ 2: $ B $ 12، "<=" & DATE (E2,12,31،XNUMX،XNUMX): يتحقق من التواريخ الموجودة في النطاق 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 ، وبناء الجملة العام هو:

• 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 تساوي السنة في النطاق 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 و FALSE على 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.

الوظيفة النسبية المستخدمة:

المزيد من المقالات: