매달 출고 데이터를 거래처별, 품목별로 집계해 보고서를 만들 때 수식을 일일이 작성하면 시간이 많이 걸리고 수정도 번거롭다. 기준이 하나만 바뀌어도 수식을 다시 손봐야 하기 때문이다.
피벗테이블은 끌어다 놓는 것만으로 같은 집계를 만든다. 원본 표의 열을 행, 열, 값 영역에 배치하면 합계와 개수가 자동으로 계산된다. 이 글에서는 만드는 순서와 날짜 그룹화, 슬라이서까지 정리한다.
1. 만들기 전 원본 표 점검
피벗테이블은 원본이 깔끔할수록 오류가 적다. 머리글은 한 줄이어야 하고, 빈 행과 병합 셀이 없어야 한다. 원본을 표로 변환해 두면 데이터가 늘어나도 범위가 자동으로 확장된다. 표 안의 아무 셀을 선택하고 Ctrl+T를 누르면 된다.
| 점검 항목 | 문제가 생기면 |
|---|---|
| 빈 행이나 빈 열 | 범위가 중간에 끊겨 일부 데이터가 빠진다 |
| 병합 셀 | 필드가 제대로 인식되지 않는다 |
| 비어 있거나 중복된 머리글 | 피벗테이블을 만들 수 없거나 필드 이름이 꼬인다 |
| 텍스트로 저장된 숫자 | 합계 대신 개수로 집계된다 |
2. 필드 배치로 집계표 만들기
원본 표를 선택하고 삽입 탭에서 피벗 테이블을 고른 뒤 새 워크시트에 만든다. 오른쪽 필드 목록에서 거래처를 행으로, 품목을 열로, 출고수량을 값으로 끌어다 놓으면 거래처별, 품목별 합계표가 완성된다. 값 영역에 숫자 필드를 넣으면 합계가, 텍스트 필드를 넣으면 개수가 기본으로 집계된다.
| 영역 | 역할 | 예 |
|---|---|---|
| 행 | 세로 기준 | 거래처 |
| 열 | 가로 기준 | 품목 |
| 값 | 집계 대상 | 출고수량 합계 |
| 필터 | 전체에 거는 조건 | 월 |
3. 날짜 그룹화와 슬라이서
출고일을 행에 넣고 우클릭해서 그룹을 선택하면 월, 분기, 연 단위로 묶을 수 있다. 최신 엑셀은 날짜를 넣으면 월 단위로 자동 그룹화되는 경우도 있다. 슬라이서는 피벗테이블 분석 탭에서 삽입하며, 거래처나 월을 버튼으로 눌러 보고서를 즉시 걸러 볼 수 있다.
4. 보고서 완성 단계
집계가 끝나면 보고서로 다듬는다. 숫자에는 천 단위 구분 쉼표를 넣고, 값 기준으로 정렬하며, 총합계 행의 표시 여부를 정한다. 피벗테이블 디자인 탭에서 스타일을 고르면 머리글과 합계 행이 구분되어 읽기 쉬워진다.
| 다듬기 항목 | 방법 |
|---|---|
| 숫자 서식 | 값 필드 설정에서 표시 형식 지정 |
| 정렬 | 행 레이블의 목록 단추에서 값 기준 정렬 |
| 총합계 | 디자인 탭의 총합계 설정 |
| 빈 셀 표시 | 피벗 옵션에서 빈 셀에 0 표시 |
5. 자주 겪는 문제와 해결
피벗테이블은 한 번 만들어 두고 매달 재사용하는 경우가 많다. 그래서 데이터가 늘거나 형식이 바뀌었을 때 생기는 문제를 알아 두면 시간을 아낄 수 있다. 가장 흔한 원인은 원본 범위가 고정되어 있는 경우다.
| 증상 | 원인 | 해결 |
|---|---|---|
| 새로 넣은 데이터가 반영되지 않는다 | 원본 범위가 고정되어 있음 | 원본을 표로 변환하거나 피벗테이블 분석 탭에서 데이터 원본을 변경한다 |
| 날짜가 월로 묶이지 않는다 | 날짜가 텍스트로 저장됨 | 날짜 형식으로 변환한 뒤 다시 그룹화한다 |
| 같은 거래처가 두 줄로 나온다 | 이름에 공백이나 표기 차이가 있음 | TRIM으로 공백을 정리하고 표기를 통일한다 |
| 열 너비가 새로 고침마다 바뀐다 | 자동 맞춤 옵션이 켜져 있음 | 피벗 옵션에서 새로 고침 시 열 너비 자동 맞춤을 끈다 |
핵심정리
- 원본은 머리글 한 줄, 빈 행과 병합 셀 없이 정리하고 표로 변환한다.
- 행, 열, 값 영역에 필드를 배치해 집계표를 만든다.
- 날짜는 그룹화로 월별 집계가 가능하고 슬라이서로 즉시 걸러 볼 수 있다.
- 원본이 바뀌면 새로 고침을 눌러야 반영된다.
다음 글에서는 조건부 서식으로 재고 부족과 납기 지연을 자동으로 표시하는 방법을 정리한다. 막히는 부분이 있다면 댓글로 남겨 주면 예제로 다루겠다.
이 글의 표와 수식 예제에 쓰인 데이터는 설명을 위해 만든 가상 예시입니다.
