엑셀 피벗테이블로 매출표를 빠르게 집계하는 방법

얼마 전 지점별 매출 파일을 받았는데, 행이 600개쯤 되니 필터를 걸어 합계를 하나씩 계산하는 게 더 오래 걸렸습니다. 지점 이름도 여러 번 반복되고 날짜도 섞여 있었거든요. 이럴 때 엑셀 피벗테이블을 쓰면 원본을 건드리지 않고 지점별 합계나 담당자별 건수를 빠르게 볼 수 있습니다. 처음엔 화면 오른쪽에 뜨는 필드 목록이 낯설지만, 네 칸의 역할만 알면 생각보다 단순합니다.

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

피벗테이블은 한 행을 거래 한 건으로 읽습니다. 예를 들어 열 제목이 ‘날짜, 지점, 담당자, 상품, 매출액’이라면 그 아래에는 거래가 한 줄씩 들어가야 합니다. 중간에 ‘강남점 소계’ 같은 행을 넣거나 제목을 두 줄로 만들면 집계할 때 걸림돌이 됩니다. 병합된 셀도 풀어 두는 편이 안전합니다.

특히 열 제목은 한 줄에 하나씩, 비어 있지 않게 적어야 합니다. 표 한가운데 빈 행이나 빈 열이 있으면 엑셀이 데이터 범위를 좁게 잡을 수 있습니다. 날짜 열에는 날짜만, 매출액 열에는 숫자만 넣으세요. ‘12만 원’처럼 문자가 섞인 금액은 합계 대신 개수로 처리될 수 있습니다.

원본이 매주 늘어나는 파일이라면 데이터 안의 셀을 선택하고 ‘삽입 → 표’로 먼저 바꿔 두는 게 편합니다. 새 거래를 표 아래에 추가한 뒤 피벗테이블을 새로 고치면 추가된 행도 집계 대상에 들어갑니다. 일반 셀 범위를 원본으로 지정했다면 새 행이 범위 밖에 놓일 수 있으니 따로 확인해야 합니다.

행과 값 두 칸으로 첫 집계 만들기

원본 표 안의 셀 하나를 클릭한 뒤 ‘삽입 → 피벗 테이블’을 선택합니다. 범위가 맞는지 확인하고 위치는 ‘새 워크시트’를 고르면 원본과 결과가 섞이지 않아 보기 좋습니다. 화면 오른쪽의 피벗테이블 필드 목록에는 열 제목이 그대로 나타납니다.

• ‘지점’을 ‘행’ 영역으로 끌면 지점 이름이 세로로 나열됩니다.
• ‘매출액’을 ‘값’ 영역으로 끌면 지점별 매출 합계가 표시됩니다.
• ‘담당자’를 ‘열’ 영역에 놓으면 각 지점의 매출이 담당자별로 나뉩니다.
• ‘상품’을 ‘필터’ 영역에 놓으면 특정 상품만 골라 볼 수 있습니다.

가령 강남점 거래 3건이 12만 원, 15만 원, 9만 원이고 홍대점 거래 2건이 8만 원, 11만 원이라면 결과에는 강남점 36만 원, 홍대점 19만 원이 표시됩니다. 여기서 지점을 행에서 빼고 담당자를 넣으면 같은 원본으로 담당자별 매출을 볼 수 있습니다. 표를 다시 만들 필요 없이 필드의 위치만 바꾸는 방식입니다.

합계 대신 개수가 나오거나 금액이 이상할 때

매출액을 값에 넣었는데 ‘매출액 개수’가 보이면 먼저 원본 열을 확인합니다. 숫자처럼 보이는 셀이 실제로는 문자일 수 있고, 빈칸이나 문자 값이 섞였을 수도 있습니다. 원본 값을 고친 뒤 피벗테이블 안에서 마우스 오른쪽 버튼을 눌러 ‘새로 고침’을 선택하세요.

숫자 데이터가 맞는데도 원하는 계산이 아니라면 값 영역의 ‘매출액’ 옆 메뉴에서 ‘값 필드 설정’을 열어 ‘합계’를 선택하면 됩니다. 거래 건수를 보고 싶을 때는 반대로 ‘개수’를 고르면 되고요. 합계와 건수를 함께 보고 싶다면 매출액 필드를 값 영역에 두 번 넣고, 각각 다른 계산 방식으로 지정할 수도 있습니다.

‘개수’는 빈칸이 아닌 셀을 세는 방식이라 실제 거래 행 수와 다를 수 있습니다. 취소 거래나 금액 누락 행까지 포함해야 한다면 거래번호처럼 모든 행에 값이 들어 있는 열을 개수 기준으로 쓰는 게 더 정확합니다. 숫자가 맞지 않을 때는 피벗테이블의 특정 합계 셀을 두 번 클릭해 그 합계에 포함된 원본 행을 새 시트에서 확인할 수도 있습니다.

새 데이터를 넣은 뒤 결과까지 갱신하기

원본의 금액을 수정하거나 거래를 추가해도 피벗테이블 숫자가 바로 바뀌지는 않습니다. 피벗테이블 안의 셀을 선택하고 ‘새로 고침’을 실행해야 합니다. Microsoft 엑셀 안내에서도 피벗테이블은 원본 데이터의 스냅숏을 사용한다고 설명합니다. 그래서 원본 수정과 결과 갱신을 한 묶음의 작업으로 생각하면 덜 헷갈립니다.

새로 고쳐도 추가한 거래가 보이지 않는다면 피벗테이블의 ‘데이터 원본 변경’에서 범위를 확인하세요. 처음에 원본을 엑셀 표로 만들어 두었다면 새 행이 포함되기 쉬워 이런 점검이 줄어듭니다. 보고서를 보내기 전에는 원본의 전체 매출과 피벗테이블의 총합계도 한 번 비교해 두면 좋습니다.

저는 반복해서 받는 매출 파일일수록 원본을 표로 만들고 피벗테이블을 별도 시트에 둡니다. 다음 주 파일을 다룰 때도 행을 추가하고 새로 고침만 하면 같은 기준으로 숫자를 다시 볼 수 있어서, 수작업으로 합계를 옮겨 적을 때보다 훨씬 마음이 편합니다.