엑셀 시리즈 #24 자동화 - 피벗테이블 없이 월별 보고서 자동화하기

📊 엑셀 시리즈 #24 자동화 - 피벗테이블 없이 월별 보고서 자동화하기

안녕하세요, 7옥타브고양이입니다 😊

매달 반복되는 월별 매출 보고서, 매번 수작업으로 정리하시나요?
피벗테이블을 쓸 수도 있지만, 팀 내에서 피벗을 잘 모르는 사람도 많고, 자동 업데이트가 되지 않아 매달 새로 설정해야 하는 번거로움이 있죠.

그래서 오늘은 “피벗테이블 없이도 자동으로 월별 합계를 구하는 방법”을 소개합니다. 사무직 실무에서 보고 자동화의 핵심이 되는 기법입니다.

엑셀 시리즈 #24 자동화 - 피벗테이블 없이 월별 보고서 자동화하기

---

1️⃣ 실무에서 자주 겪는 문제

아래는 온라인 주문 데이터를 관리하는 시트입니다. 월별로 판매금액을 정리해야 하지만, 날짜별로 뒤섞여 있고 매달 데이터를 다시 복사해 피벗으로 돌리는 작업이 반복됩니다.

주문일자	상품명	수량	단가	판매금액
2025-01-03	무선마우스	5	20,000	=C2*D2
2025-01-12	키보드	3	35,000	=C3*D3
2025-02-01	노트북가방	2	45,000	=C4*D4
2025-02-15	충전기	4	25,000	=C5*D5
2025-03-10	USB허브	6	15,000	=C6*D6
2025-03-28	마우스패드	10	8,000	=C7*D7

이 데이터를 바탕으로 “1월, 2월, 3월별 총 매출액”을 자동으로 집계하려면 어떻게 해야 할까요?

---

2️⃣ 피벗 대신 함수 조합으로 자동 합계 만들기

핵심은 월별 조건에 맞는 합계를 자동으로 계산하는 것입니다. 이를 위해 SUMPRODUCT 또는 SUMIFS 함수를 사용하면 됩니다.

① 월 구분용 ‘월’ 열 추가:

=TEXT(A2,"yyyy-mm")

위 수식은 주문일자(예: 2025-01-03)를 “2025-01” 형태로 바꿔줍니다. 이 열이 있으면 이후에 월별 집계가 훨씬 간단해집니다.

매출데이터

② SUMIFS 함수로 월별 자동 집계:

이제 별도의 요약 시트를 만들어 아래처럼 구성합니다.

월	매출합계
2025-01
2025-02
2025-03

매출합계 셀(B2)에 아래 수식을 입력합니다 👇

=SUMIFS(매출데이터!E:E, 매출데이터!F:F, A2)​

이 수식은 ‘매출데이터’ 시트의 월(F열)이 A2(예: “2025-01”)과 같은 행의 판매금액(E열)을 모두 더합니다. 이제 새로운 주문이 추가되어도 자동으로 월별 합계가 업데이트됩니다!

월별 자동집계

---

3️⃣ 고급 응용: 자동 월 리스트 생성

수동으로 “1월, 2월, 3월”을 적는 대신, UNIQUE 함수로 자동으로 월 목록을 뽑아낼 수도 있습니다.

=UNIQUE(매출데이터!F2:F100)

이렇게 하면 데이터가 추가될 때마다 새로운 월이 자동으로 목록에 추가됩니다. 정렬까지 하고 싶다면 다음처럼 작성하세요 👇

=SORT(UNIQUE(매출데이터!F2:F100))

즉, “월별 요약표”도 완전 자동화가 가능합니다 ✅

---

4️⃣ 실무 응용 예제: 부서별 + 월별 자동 보고서

조금 더 실무적으로, 부서별로 월별 합계를 구해야 하는 상황을 예로 들어보겠습니다.

부서	주문일자	판매금액
영업1팀	2025-01-05	3,000,000
영업1팀	2025-02-07	4,500,000
영업2팀	2025-01-20	2,800,000
영업2팀	2025-03-11	6,000,000

이때 요약 시트의 부서별/월별 매출표를 자동화하려면 다음 수식을 사용할 수 있습니다.

=SUMIFS(매출데이터!$D:$D, 매출데이터!$A:$A, $A2, 매출데이터!$B:$B, B$1)​

위 식은 ‘부서(A열)’와 ‘월(B열)’ 조건을 동시에 충족하는 판매금액의 합계를 구합니다. 행(부서)과 열(월)을 모두 자동 참조하므로, 보고서 전체를 자동 갱신할 수 있습니다.

 

부서별 + 월별 자동 보고서 "매출데이터 시트"

부서별 + 월별 자동 보고서 요약

---

5️⃣ 사무직 자동화 포인트 💡

• 피벗테이블 없이도 자동 집계가 가능해 반복작업을 크게 줄일 수 있습니다.
• 새로운 데이터 추가 시 자동 업데이트되므로, 매달 새로 만들 필요가 없습니다.
• 함수 기반이라 공유 파일에서도 깨지지 않고 유지됩니다.
• 필터 + 조건부 서식을 함께 쓰면 월별 성과 시각화도 가능합니다.

💬 TIP: 파일 구조는 데이터 시트(원본)와 요약 시트(결과)를 분리하면 유지보수가 쉬워집니다.
또한 수식 범위를 테이블 형식(Ctrl+T)으로 만들어두면 새 행 추가 시 자동 확장됩니다.

---

6️⃣ 마무리 및 정리

오늘 배운 “피벗 없이 월별 보고서 자동화” 기법은 복잡한 피벗 설정 없이도 함수를 이용해 손쉽게 자동화할 수 있는 강력한 방법입니다.

이 방식은 영업 매출, 재고 소진, 출장비, 프로젝트 실적 등 모든 “월 단위 집계”가 필요한 사무직 보고서에서 사용할 수 있습니다.

매달 피벗 새로 만드는 시간을 줄이고, 데이터만 넣으면 자동으로 요약되는 엑셀 시트를 만들어 보세요! 보고서 품질도 향상되고, 시간은 절약됩니다 ⏰

다음 글은 엑셀 시리즈 #25 함수 - 고급 필터 기능으로 부서별 보고서 자동 생성하기 입니다. ^^

© 7옥타브고양이