엑셀에서 월별 데이터를 분석하고 집계표를 만드는 작업은 업무 효율성을 크게 좌우합니다. 하나의 월별 집계표를 기반으로 다른 월의 집계표를 신속하게 생성하는 방법을 통해 반복 작업 시간을 대폭 절약할 수 있습니다. SUMPRODUCT 함수와 서식 코드, 이름 정의 기능을 활용하면 복잡한 데이터 처리 작업도 자동화가 가능합니다. 이러한 방법론은 매월 반복되는 보고서 작성이나 매출 분석 업무에서 특히 유용하며, 엑셀 사용자의 생산성 향상에 크게 기여합니다.
엑셀 이름 정의로 데이터 범위 설정하기
엑셀에서 효율적인 월별 집계표를 만들기 위해서는 먼저 데이터 범위에 이름을 정의하는 작업이 필수입니다. 연습 시트에서 Ctrl+A를 눌러 전체 데이터 범위를 선택한 후, 수식 메뉴에서 정의된 이름 옵션을 활용합니다. 마이크로소프트 공식 문서에서도 강조하듯이, 선택영역에서 만들기 기능을 통해 첫 행에만 체크 표시를 하면 각 열마다 열 제목으로 이름이 자동으로 생성됩니다. 이렇게 정의된 이름들은 워크북 내의 모든 시트에서 공통으로 사용할 수 있어 함수 작성 시 가독성을 높이고 오류를 줄이는 효과를 얻을 수 있습니다. 특히 날짜, 담당자, 제품, 금액 등의 컬럼명이 이름으로 정의되면 복잡한 셀 참조 대신 직관적인 함수 작성이 가능합니다.
사용자 지정 서식으로 월별 제목 자동화
월별 집계표의 제목을 자동으로 표시하기 위해서는 사용자 지정 서식 기능을 활용해야 합니다. B2 셀을 선택하고 우클릭하여 셀 서식을 열어보겠습니다. 표시 형식 탭에서 사용자 지정을 선택한 후 형식 코드에 #월 집계표를 입력하면 숫자 10을 입력했을 때 10월 집계표로 자동 표시됩니다.
- 사용자 지정 서식 코드 #월 집계표를 통한 제목 자동화
- 숫자 입력만으로 월별 제목 변경 가능
- 복사된 시트에서도 동일한 서식 유지
- 시각적 일관성 확보를 통한 전문적인 보고서 작성
데이터 영역 조건부 서식 설정
집계표의 가독성을 높이기 위해 데이터 영역인 C5부터 I11까지의 셀 범위에 조건부 서식을 적용합니다. 엑셀 문제해결 가이드를 참고하면, 사용자 지정 서식에서 #,##0;;[빨강]#,##0 코드를 입력하여 값이 0인 경우 빨간색으로 표시되도록 설정할 수 있습니다. 이러한 시각적 구분을 통해 데이터가 없는 항목을 즉시 파악할 수 있으며, 분석 시 누락된 부분을 쉽게 식별할 수 있습니다.
| 서식 코드 | 표시 결과 | 활용 목적 |
|---|---|---|
| #,##0 | 천 단위 구분 표시 | 숫자 가독성 향상 |
| [빨강]#,##0 | 빨간색 숫자 표시 | 0값 시각적 구분 |
| #월 집계표 | 10월 집계표 형태 | 제목 자동화 |
| ;;[빨강]#,##0 | 0일 때만 빨간색 | 조건부 색상 적용 |
SUMPRODUCT 함수 활용한 다중 조건 집계
월별 집계의 핵심은 SUMPRODUCT 함수를 활용한 다중 조건 집계입니다. C5 셀에 =SUMPRODUCT((MONTH(날짜)=$B$2)*(담당=$B5)*(제품=C$4)*금액) 공식을 입력하면 세 가지 조건을 동시에 만족하는 데이터만 합계됩니다. 이 함수는 날짜 열에서 해당 월에 해당하는 데이터, 담당자가 일치하는 데이터, 제품이 일치하는 데이터를 모두 찾아 금액을 합산합니다. Microsoft 365 Excel의 고급 기능 중 하나인 이 함수는 복잡한 조건부 집계를 단일 수식으로 처리할 수 있어 매우 효율적입니다.
SUMPRODUCT 함수의 장점은 배열 수식처럼 작동하면서도 Ctrl+Shift+Enter를 누르지 않아도 된다는 점입니다. 각 조건은 TRUE(1) 또는 FALSE(0) 값을 반환하며, 모든 조건이 참일 때만 1이 되어 해당 금액이 합계에 포함됩니다. 이러한 방식으로 복잡한 데이터베이스에서도 정확한 집계 결과를 얻을 수 있습니다.
채우기 핸들로 수식 자동 복사
작성한 SUMPRODUCT 함수를 다른 셀로 확장하기 위해 채우기 핸들을 활용합니다. C5 셀의 우하단 모서리에 마우스를 올리면 나타나는 십자 모양 핸들을 H5까지 드래그하여 제품별 수식을 복사합니다. 엑셀의 상대 참조와 절대 참조 기능 덕분에 $B$2와 같은 절대 참조는 고정되고, C$4와 $B5 같은 혼합 참조는 적절히 변경되어 각 셀에 맞는 조건으로 수식이 자동 조정됩니다. 이후 다시 채우기 핸들을 이용해 10행까지 드래그하면 모든 담당자별 데이터가 자동으로 계산됩니다.
채우기 작업이 완료되면 C11 셀에 =SUM(C5:C10) 공식을 입력하여 세로 합계를 계산하고, I5 셀에 =SUM(C5:H5) 공식을 입력하여 가로 합계를 계산합니다. 이렇게 작성된 합계 공식들도 채우기 핸들을 통해 나머지 영역으로 확장하여 완전한 집계표를 완성할 수 있습니다.
차트 삽입과 시트 복사로 월별 보고서 완성
데이터 시각화를 위해 B4부터 H10 범위를 선택하고 삽입 메뉴에서 세로 막대형 차트를 추가합니다. Office 지원 센터에서 제공하는 차트 가이드라인에 따라 2차원 세로 막대형의 첫 번째 옵션을 선택하면 가독성 높은 차트가 생성됩니다. 완성된 10월 시트를 우클릭하여 이동 복사 메뉴를 선택하고, 복사본 만들기에 체크한 후 새로운 시트를 생성합니다. 복사된 시트의 이름을 11월로 변경하고 B2 셀에 11을 입력하면 이름 정의 기능과 SUMPRODUCT 함수 덕분에 모든 데이터와 차트가 자동으로 11월 데이터로 업데이트됩니다. 동일한 방식으로 12월 시트도 생성하여 3개월간의 월별 집계표를 신속하게 완성할 수 있습니다.
