엑셀 피벗 테이블 만들기 따라 하기

피벗 테이블이 무엇인지 알았다면 이제 직접 만들어 볼 차례입니다. 메뉴는 몇 개 되지 않지만 원본 준비와 필드 배치를 제대로 이해하지 못하면 원하는 모양이 잘 나오지 않습니다.

이 글에서는 거래처·품목별 판매 목록을 예시로 원본 준비, 피벗 테이블 삽입, 필드 배치, 값 요약 기준 변경, 날짜 그룹과 새로 고침까지 순서대로 따라 합니다.

원본 데이터 준비하기

피벗 테이블은 한 행에 한 건, 한 열에 한 종류의 정보가 들어간 목록에서 가장 잘 동작합니다. 만들기 전에 다음을 확인합니다.

• 머리글은 1행에 하나씩: 모든 열에 제목이 있어야 하고, 두 줄로 병합된 제목은 피합니다.
• 값을 열 제목으로 펼치지 않기: 1월·2월·3월을 각각 열로 둔 표 대신 "월" 열 하나를 만들고 행을 나눠 적습니다.
• 빈 행·빈 열·소계 행 없애기: 빈 행에서 범위가 끊기고, 소계 행은 한 건의 데이터로 계산되어 합계가 부풀려집니다.
• 한 열에는 같은 형식만: 날짜 열에 텍스트가 섞이면 월별로 묶는 그룹 기능이 동작하지 않을 수 있습니다.

피벗 테이블 삽입하기

1. 원본 목록 안의 아무 셀이나 클릭합니다.
2. 삽입 탭 > 피벗 테이블을 누릅니다. 최신 버전에서 목록이 펼쳐지면 테이블/범위에서를 고릅니다.
3. 피벗 테이블 만들기 창에서 범위가 맞는지 확인하고, 위치는 새 워크시트로 둔 채 확인을 누릅니다.

예시처럼 원본을 표(Table1)로 만들어 두면 범위에 표 이름이 들어가고, 나중에 표에 행을 추가해도 새로 고침만으로 반영됩니다. 일반 범위로 만들었다면 행을 추가한 뒤 데이터 원본 변경으로 범위를 다시 잡아야 합니다.

필드를 행·열·값·필터에 배치하기

새 시트 오른쪽에 피벗 테이블 필드 창이 열립니다. 위쪽에는 원본의 머리글이 필드로 나열되고, 아래쪽에는 네 개의 영역이 있습니다.

영역	역할	예시
행	왼쪽에 세로로 나열할 기준	거래처
열	위쪽에 가로로 나열할 기준	품목
값	계산할 숫자	금액의 합계
필터	표 전체에 걸 조건	특정 품목만

1. Customer 필드를 행 영역으로 끌어 놓으면 거래처 이름이 중복 없이 나열됩니다.
2. Total price를 값 영역에 놓으면 거래처별 금액 합계와 총합계 730,000이 표시됩니다.
3. Quantity도 값에 추가하면 수량 합계가 옆 열에 붙습니다.

이번에는 Quantity를 값 영역 밖으로 끌어내 없애고 Item을 열 영역에 넣어 봅니다. 거래처×품목 교차표가 되고 행과 열 끝에 각각 총합계가 붙습니다. Item을 필터 영역으로 옮기면 표 위에 드롭다운이 생기며, Lollipops만 고르면 사탕을 산 두 거래처만 남고 총합계는 165,000이 됩니다. 여러 품목을 함께 보려면 드롭다운의 여러 항목 선택에 체크합니다.

값 요약 기준 바꾸기: 합계에서 개수·평균으로

값 영역에 숫자 필드를 넣으면 기본으로 합계가 계산됩니다. 주문 건수가 필요하다면 다음처럼 바꿉니다.

1. 값 영역의 합계 : Total price 오른쪽 화살표를 누르고 값 필드 설정을 고릅니다.
2. 값 요약 기준 탭에서 개수를 선택하고 확인을 누릅니다.

결과는 CandyLand 4, Snacks R Us 3, Sweet Tooth's 5로 바뀝니다. 같은 창에서 평균, 최대, 최소도 고를 수 있고, 값 칸을 오른쪽 클릭해 값 요약 기준에서 바로 바꿔도 됩니다.

날짜 그룹과 새로 고침

날짜 필드를 열이나 행에 넣었을 때 날짜가 하루 단위로 펼쳐진다면, 날짜 칸 하나를 오른쪽 클릭하고 그룹을 누른 뒤 단위에서 월을 고릅니다. 예시에서는 1월 160,000, 2월 305,000, 3월 150,000, 4월 115,000으로 묶입니다. 엑셀 2016 이후 버전은 날짜 필드를 넣는 순간 연·분기·월로 자동 그룹되기도 합니다.

원본을 고친 뒤에는 피벗 테이블 안을 오른쪽 클릭해 새로 고침을 누르거나 Alt+F5를 누릅니다. 새로 고치기 전까지는 예전 결과가 그대로 남아 있습니다.

구글 스프레드시트에서는

범위를 선택하고 삽입 메뉴 > 피벗 테이블을 누르면 오른쪽 편집기에서 행·열·값·필터를 추가 단추로 넣습니다. 값의 요약 기준은 SUM, COUNTA, AVERAGE 같은 함수 이름으로 고릅니다. 원본이 바뀌면 자동으로 다시 계산되므로 새로 고침이 필요 없습니다.

자주 묻는 질문

함께 보면 좋은 글: 엑셀 피벗 테이블이란 개념 쉽게 이해하기, 엑셀 표 기능 사용법 범위를 표로 바꾸기, 엑셀 SUMIF SUMIFS 조건부 합계 구하기