Excel에서 연도별 값 합계

데이터 범위에 날짜와 판매 금액 목록이 포함되어 있다고 가정하고, 아래 스크린샷과 같이 주어진 연도를 기준으로 총 금액을 얻고 싶습니다. Excel에서는 일반적인 SUMIFS 및 SUMPRODUCT 함수가 이 작업을 빠르고 쉽게 해결할 수 있습니다. 이 튜토리얼에서는 이 두 함수를 사용하는 방법을 자세히 설명합니다.

• SUMIFS 및 DATE 함수를 사용하여 Excel에서 연도별 값 합계
• SUMPRODUCT 함수를 사용하여 Excel에서 연도별 값 합계

SUMIFS 및 DATE 함수를 사용하여 Excel에서 연도별 값 합계

주어진 연도를 기준으로 총 값을 합산하려면 DATE 함수를 사용하여 연도의 첫 번째 및 마지막 날짜를 지정한 다음 SUMIFS 함수를 사용하여 지정된 날짜 내의 모든 값을 합산해야 합니다. 일반적인 구문은 다음과 같습니다:

• sum_range: 합산할 값의 범위;
• date_range: 특정 연도를 테스트할 날짜의 범위;
• year: 값을 합산할 특정 연도. (year,1,1 은 연도의 첫 번째 날을 나타내고, year,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 범위의 값이 찾아져 합산됩니다.

SUMPRODUCT 함수를 사용하여 Excel에서 연도별 값 합계

여기, 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: 이 표현식에서 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으로 반환합니다.

사용된 관련 함수:

더 많은 기사: