분기별 합계 구하기

사용 함수 및 정의

이 함수 조합의 목적은 날짜 데이터를 기준으로 각 항목이 어느 분기에 속하는지 동적으로 파악하고, 특정 분기에 해당하는 값들의 총합을 구하는 것입니다. 예를 들어, 일별 또는 월별 매출 기록에서 1분기 매출액, 2분기 매출액 등을 손쉽게 집계할 수 있습니다.

이 함수 조합의 반환 유형은 숫자(합계 금액)입니다.

함수 조합 사용법

=SUMPRODUCT((ROUNDUP(MONTH(날짜범위) / 3, 0) = 분기조건) * 합산범위)

• 날짜범위: 날짜 데이터가 입력된 셀 범위를 지정합니다. (예: A2:A100)
• 분기조건: 합계를 구하고자 하는 분기를 숫자로 지정합니다 (1, 2, 3, 또는 4).
• 합산범위: 합산할 숫자 데이터가 입력된 셀 범위를 지정합니다. 이 범위는 날짜범위와 행의 수가 동일해야 합니다. (예: B2:B100)

함수 예제 및 설명

아래의 예제 데이터와 함께 함수를 실제로 어떻게 사용하는지, 그리고 계산은 어떤 단계로 이루어지는지 상세히 알아보겠습니다.

예제 데이터

다음과 같이 날짜별 금액이 기록된 데이터가 있다고 가정합니다.

사용 예시 (1분기 합계 구하기)

위 표의 데이터를 바탕으로 1분기(1월~3월)의 금액 합계를 구하고자 한다면, 결과를 표시할 셀(예: D2셀)에 다음과 같은 수식을 입력합니다.

=SUMPRODUCT((ROUNDUP(MONTH($A$2:$A$13) / 3, 0) = 1) * $B$2:$B$13)

계산 과정 단계별 설명:

위 수식을 통해 1분기 합계인 450이 어떻게 계산되는지 단계별로 살펴보겠습니다. 설명문 내에서는 절대 참조 문자를 제외하고 설명합니다.

1. MONTH(A2:A13): 먼저 A2:A13 범위에 있는 각 날짜에서 월(Month) 숫자만 추출합니다.

   • 내부 계산 결과 배열: {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12}

2. MONTH(A2:A13) / 3: 추출된 각 월 숫자를 3으로 나눕니다.

   • 내부 계산 결과 배열: {0.333..., 0.666..., 1, 1.333..., 1.666..., 2, 2.333..., 2.666..., 3, 3.333..., 3.666..., 4}

3. ROUNDUP(MONTH(A2:A13) / 3, 0): 이전 단계에서 3으로 나눈 값을 소수점 첫째 자리에서 올림하여 정수로 만듭니다. 이 값이 바로 각 날짜가 속하는 분기(1, 2, 3, 4)가 됩니다.

   • 내부 계산 결과 배열 (각 날짜의 분기): {1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4}

4. (ROUNDUP(MONTH(A2:A13) / 3, 0) = 1): 이렇게 계산된 각 날짜의 분기가 우리가 찾고자 하는 분기조건인 1(1분기)과 일치하는지 비교합니다. 일치하면 TRUE, 일치하지 않으면 FALSE 값을 가지는 논리 배열이 생성됩니다.

   • 내부 계산 결과 배열: {TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE}

5. (ROUNDUP(MONTH(A2:A13) / 3, 0) = 1) * B2:B13: 이전 단계에서 얻은 논리 배열(TRUE/FALSE)과 합산범위로 지정된 B2:B13(금액)의 각 요소를 서로 곱합니다. Excel에서 곱셈과 같은 산술 연산을 할 때 TRUE는 1로, FALSE는 0으로 취급됩니다.

   • 1분기에 해당하는 날짜의 경우: 1 * 해당 금액 (예: 1 * 100 = 100, 1 * 150 = 150, 1 * 200 = 200)
   • 그 외 분기에 해당하는 날짜의 경우: 0 * 해당 금액 (예: 0 * 250 = 0)
   • 내부 계산 결과 배열: {100, 150, 200, 0, 0, 0, 0, 0, 0, 0, 0, 0}

6. SUMPRODUCT(...): 마지막으로, SUMPRODUCT 함수는 이렇게 계산된 배열의 모든 요소들의 합계를 구합니다.

   • 최종 결과: 100 + 150 + 200 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 = 450

따라서 1분기의 총금액은 450으로 계산됩니다. 만약 2분기의 합계를 구하고 싶다면, 수식에서 분기조건 부분의 숫자만 1에서 2로 변경해주면 됩니다.

기타 고려 사항

• 배열 수식 입력 불필요: SUMPRODUCT 함수는 그 자체로 배열 연산을 지원하기 때문에, 구형 Excel 버전(Excel 2019 이전)을 사용하시더라도 Ctrl+Shift+Enter 키를 눌러 배열 수식으로 입력할 필요가 없습니다.
• 데이터 유효성 검사: 날짜범위로 지정된 셀들에 날짜 형식이 아닌 텍스트나 빈 셀이 포함되어 있거나, 합산범위에 숫자가 아닌 값이 포함되어 있다면 #VALUE! 와 같은 오류가 발생할 수 있습니다. 원본 데이터가 정확한 형식으로 입력되어 있는지 확인하는 것이 중요합니다.
• 분기 조건의 유동적 관리: 수식 내에 직접 1, 2와 같이 분기 숫자를 입력하는 대신, 특정 셀(예: E1셀)에 분기 숫자를 입력하고 수식에서는 이 셀을 참조하도록 변경하면(...=E1)*합산범위), E1셀의 값만 바꾸는 것으로도 원하는 분기의 합계를 유동적으로 쉽게 확인할 수 있어 매우 편리합니다.