รวมมูลค่าตามปีใน 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:คือ “sum_range” ที่มีค่าที่เราต้องการหาผลรวม
• $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 สามารถช่วยคุณได้ ไวยากรณ์ทั่วไปคือ:

• date_range: ช่วงวันที่ที่จะทดสอบสำหรับปีที่ระบุ
• year: ปีที่คุณต้องการรวมมูลค่าตาม;
• sum_range: ช่วงของค่าที่จะรวม

โปรดคัดลอกหรือป้อนสูตรต่อไปนี้ลงในเซลล์ว่าง จากนั้นลากสูตรไปยังเซลล์อื่นเพื่อให้ได้ผลลัพธ์:

คำอธิบายของสูตรนี้:

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

• ปี($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: ในนิพจน์นี้ TRUE และ 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

ฟังก์ชันสัมพัทธ์ที่ใช้:

บทความเพิ่มเติม: