엑셀 피벗테이블 요약 빠르게 만드는 방법

얼마 전 월별 차량 판매 자료를 받아 보니 거래 내역은 열 줄뿐인데도 어느 달에 어떤 차종이 많이 팔렸는지 바로 보이지 않았습니다. 행이 수백 줄로 늘어나면 눈으로 합계를 맞추는 일은 더 어려워지죠. 엑셀 피벗테이블 요약은 원본을 그대로 둔 채 월별 매출, 차종별 판매 건수처럼 보고 싶은 숫자를 묶어 보여줍니다. 처음부터 복잡한 기능을 익힐 필요는 없습니다. 표의 모양을 잡고, 필드를 놓고, 계산 기준을 확인하는 순서면 충분합니다.

원본 표부터 피벗테이블에 맞게 만들기

피벗테이블이 잘 작동하려면 첫 행에 열 이름이 하나씩 있어야 합니다. 차량 판매 자료라면 ‘판매일’, ‘차종’, ‘지점’, ‘주문번호’, ‘판매금액’처럼 적습니다. 그 아래는 한 행에 거래 한 건만 넣으세요. 같은 주문을 두 행으로 나누거나 중간에 ‘1월 합계’ 같은 행을 끼워 넣으면 건수와 금액이 부풀 수 있습니다.

숫자는 숫자로, 날짜는 엑셀이 인식하는 날짜로 입력하는 것도 중요합니다. 판매금액 칸에 ‘3,000만원’이라는 글자를 직접 넣기보다 30000000을 입력하고 셀 표시 형식으로 단위를 표현하는 편이 안전합니다. 열 제목에 빈칸이나 중복 이름이 없는지, 표 안에 병합된 셀이 없는지도 확인하세요.

자료 범위 안의 셀을 선택한 뒤 Ctrl+T를 눌러 엑셀 표로 바꾸면 새 거래가 아래에 추가될 때 원본 범위를 관리하기 편합니다. Microsoft의 엑셀 안내에서도 표를 원본으로 사용하면 새 데이터가 새로 고침 때 피벗테이블에 포함된다고 설명합니다. 자료가 계속 쌓이는 업무라면 처음부터 표로 만들어 두는 게 시간을 아낍니다.

행과 값에 필드를 놓아 첫 요약 만들기

표 안의 셀을 클릭하고 ‘삽입’에서 ‘피벗 테이블’을 선택합니다. 새 워크시트에 배치하면 원본과 결과를 나란히 관리하기 쉽습니다. 오른쪽에 나타나는 필드 목록에서 ‘차종’을 행 영역으로, ‘판매금액’을 값 영역으로 끌어 놓으세요. 차종별 매출 합계가 바로 나옵니다. ‘판매일’을 열 영역에 놓으면 날짜별 비교도 가능합니다. 월 단위 보고서라면 날짜 셀에서 그룹 기능을 이용해 월로 묶되, 여러 해의 자료가 섞여 있다면 연도도 함께 구분해야 합니다.

예를 들어 1월 SUV 매출이 9,000만 원, 세단이 5,000만 원이고 2월에는 각각 6,400만 원과 7,800만 원이라면, 행을 차종으로 놓은 결과는 SUV 1억 5,400만 원, 세단 1억 2,800만 원으로 보입니다. 여기에 월을 열로 추가하면 같은 총액이 어느 달에 발생했는지 한눈에 비교할 수 있죠. 피벗테이블의 장점은 수식을 새로 쓰지 않고도 행과 열의 배치를 바꿔 질문을 바꿀 수 있다는 점입니다.

합계와 건수, 평균을 목적에 맞게 고르기

값 영역의 숫자가 기대와 다르면 먼저 열 제목을 보세요. ‘판매금액의 합계’가 아니라 ‘판매금액의 개수’로 표시될 수 있습니다. 값 영역의 필드 메뉴에서 ‘값 필드 설정’을 열고 ‘값 요약 기준’을 합계로 바꾸면 됩니다. 다만 판매금액 열에 문자로 저장된 금액이 섞여 있다면 원본의 데이터 형식부터 고쳐야 정확한 합계가 나옵니다.

판매 건수를 보려면 주문번호를 값 영역에 넣고 ‘개수’를 선택할 수 있습니다. 이때 개수는 주문번호가 비어 있지 않은 행의 수입니다. 같은 주문번호가 두 번 들어간 경우 두 건으로 세므로, 고유 주문 수가 필요한 보고서라면 중복 여부를 먼저 확인해야 합니다. 평균 판매금액은 판매금액을 값 영역에 한 번 더 넣고 요약 기준을 ‘평균’으로 지정하면 합계와 함께 볼 수 있습니다.

전체 매출에서 차종이 차지하는 비율도 유용합니다. 판매금액을 값 영역에 한 번 더 놓고 ‘값 표시 형식’을 ‘총합계 비율’로 바꾸면 됩니다. 앞선 예시에서는 SUV의 비중이 약 54.6%입니다. 합계와 비율을 나란히 두면 규모가 큰 차종과 매출 집중도를 동시에 읽을 수 있습니다.

숫자가 달라졌다면 새로 고침과 원본 범위 확인하기

원본 표의 금액을 수정하거나 새 거래를 추가해도 피벗테이블에 즉시 반영되지 않을 수 있습니다. 피벗테이블 안에서 마우스 오른쪽 버튼을 눌러 ‘새로 고침’을 실행하세요. 보고서가 여러 개라면 ‘모두 새로 고침’을 사용할 수 있습니다. 새로 고쳤는데도 추가한 행이 빠진다면 피벗테이블의 원본 범위가 어디까지 지정됐는지 확인해야 합니다. 일반 셀 범위를 원본으로 만들었다면 새 행이 범위 밖에 있을 수 있습니다.

총합계만 맞는다고 끝내지 말고 원본과 한 번 대조해 보는 게 좋습니다. 기간 필터가 특정 달에만 걸려 있거나, 빈 주문번호 때문에 건수가 적게 보이는 일도 흔합니다. 자동차 판매 보고서라면 매출 합계와 판매 대수를 함께 놓고 평균 판매금액까지 보는 편이 현장 상황을 읽는 데 더 유용합니다. 피벗테이블은 숫자를 빠르게 모아 주지만, 어떤 숫자를 세고 있는지 판단하는 일은 여전히 사람의 몫입니다.