월별 합계 – Excel 및 Google 스프레드시트
이 튜토리얼에서는 SUMIFS 함수를 사용하여 Excel 및 Google 스프레드시트에서 특정 월에 해당하는 데이터를 합산하는 방법을 보여드립니다.
월별 합계
먼저 특정 월과 연도에 해당하는 날짜에 해당하는 데이터를 합산하는 방법을 보여 드리겠습니다.
SUMIFS 함수를 DATE, YEAR, MONTH, EOMONTH 함수와 함께 사용하여 각 월의 판매량을 합산할 수 있습니다.
=SUMIFS(C3:C9,B3:B9,">="&DATE(YEAR(E3),MONTH(E3),1),B3:B9,"<="&EOMONTH(E3,0))
위의 수식을 작성하려면 먼저 각 월의 날짜 범위를 정의합니다. DATE 함수를 사용하여 매월 1일을 정의합니다.(예: 2021년 5월 1일) 수식에 날짜를 ‘하드코딩(데이터를 코드 내부에 직접 입력)’하면 됩니다:
">="&DATE(2021,5,1)또는 위의 예와 같이 셀 E3 내에서 날짜를 참조하여 날짜의 연도와 월을 추출하고 날짜를 1로 설정하여 기준을 유연하게 만들 수 있습니다:
">="&DATE(YEAR(E3),MONTH(E3),1)월의 마지막 날을 정의하려면 EOMONTH 함수를 사용할 수 있습니다:
"<="&EOMONTH(E3,0)이러한 모든 기준을 종합하면 다음과 같은 SUMIFS 수식을 작성할 수 있습니다:
=SUMIFS(C3:C9,B3:B9,">="&DATE(YEAR(E3),MONTH(E3),1),B3:B9,"<="&EOMONTH(E3,0))셀 참조 잠금
수식을 더 쉽게 읽을 수 있도록 잠긴 셀 참조 없이 수식을 표시했습니다:
=SUMIFS(C3:C9,B3:B9,">="&DATE(YEAR(E3),MONTH(E3),1),B3:B9,"<="&EOMONTH(E3,0))하지만 이러한 수식은 파일의 다른 곳에 복사하여 붙여넣으면 제대로 작동하지 않습니다. 대신 다음과 같이 잠긴 셀 참조를 사용해야 합니다:
=SUMIFS($C$3:$C$9,$B$3:$B$9,">="&DATE(YEAR(E3),MONTH(E3),1),$B$3:$B$9,"<="&EOMONTH(e3,0))자세한 내용은 셀 참조 잠그기 문서를 참조하세요.
월 값 서식 지정
이 예제에서는 E 열에 월을 나열했습니다. 이 월 값은 실제로 사용자 지정 숫자 서식을 사용하여 날짜를 생략하도록 서식이 지정된 형식입니다.
사용자 지정 데이터 형식은 2021년 5월을 표시하기 위해 yyyy”년” m”월”로 작성합니다.
여러 해에 걸친 월별 합계
위의 예에서는 특정 월과 연도에 속하는 날짜의 데이터를 합산했습니다. 대신 SUMPRODUCT 함수를 사용하여 모든 연도의 한 달 이내에 해당하는 날짜가 포함된 데이터를 합산할 수 있습니다.
=SUMPRODUCT($C$3:$C$8,--(MONTH($B$3:$B$8)=MONTH(E3)))
이 예에서는 SUMPRODUCT 함수를 사용하여 복잡한 “sum if” 계산을 수행합니다. 수식을 살펴보겠습니다.
이것이 최종 수식입니다:
=SUMPRODUCT(C3:C8,--(MONTH(B3:B8)=MONTH(E3)))먼저 SUMPRODUCT 함수는 각 판매 날짜의 판매 수를 나열한 다음 각 판매 날짜의 월을 지정된 월과 비교하여 월이 일치하면 TRUE를 반환하고, 일치하지 않으면 FALSE를 반환합니다:
=SUMPRODUCT({30;42;51;28;17;34},--({TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}))다음으로 이중 대시(–)는 TRUE 및 FALSE 값을 1과 0으로 변환합니다:
=SUMPRODUCT({30;42;51;28;17;34},{1;0;1;0;1;0})그런 다음 SUMPRODUCT 함수는 배열의 각 항목 쌍을 곱합니다:
=SUMPRODUCT({30;0;51;0;17;0})마지막으로 배열의 숫자가 합산됩니다:
=98합계 함수에서 부울 문과 “–” 명령을 사용하는 방법에 대한 자세한 내용은 여기에서 확인할 수 있습니다
Google 스프레드시트에서 월별 if 합계
이 수식은 Excel에서와 마찬가지로 Google 스프레드시트에서도 똑같이 작동합니다.
