엑셀 피벗테이블 방법 처음부터 따라 하기

얼마 전 PC 부품 구매 내역을 엑셀로 받아 보니, 품목별로 얼마를 썼는지 보려면 같은 이름을 찾아가며 더해야 했습니다. 행이 몇 개 안 될 때는 괜찮지만, 월별 내역이 쌓이면 숫자를 놓치기 쉽죠. 이럴 때 쓰는 게 피벗테이블입니다. 원본은 그대로 두고 행과 열을 바꿔 가며 합계나 개수를 볼 수 있습니다.

원본 표부터 한 줄에 한 건씩 만들기

엑셀 피벗테이블 방법에서 가장 시간을 아끼는 부분은 메뉴를 외우는 일이 아닙니다. 원본을 반듯하게 만드는 일입니다. 첫 행에는 ‘월’, ‘품목’, ‘수량’, ‘금액’처럼 열 이름을 하나씩 적고, 그 아래에는 구매 한 건을 한 행에 넣습니다. ‘9월 CPU 2개 360000원’, ‘9월 SSD 3개 270000원’, ‘9월 RAM 2개 140000원’이라면 세 행으로 나누는 식입니다. 다음 달 구매 내역도 같은 열 아래에 이어 붙입니다.

중간에 빈 행이나 ‘9월 소계’ 같은 줄을 끼워 넣지 않는 편이 좋습니다. 제목 셀을 병합하거나 같은 열에 숫자와 ‘미정’ 같은 문자를 섞어 넣으면 필드가 제대로 잡히지 않거나 합계가 어긋날 수 있습니다. 금액 열에는 360000처럼 숫자만 입력하고, 원화 표시는 셀 서식으로 처리하세요. 마이크로소프트의 피벗테이블 안내도 열마다 머리글을 하나씩 두고 데이터 형식을 일정하게 유지하도록 설명합니다.

구매 내역이 계속 늘어난다면 원본 안의 셀을 선택하고 Ctrl+T를 눌러 엑셀 표로 만들어 두면 편합니다. ‘머리글 포함’을 확인한 뒤 만들면 됩니다. 표를 원본으로 쓴 피벗테이블은 나중에 아래쪽에 행을 추가했을 때 새로 고침으로 그 행까지 포함할 수 있습니다.

피벗테이블을 만들고 필드 네 칸 채우기

원본 표 안의 아무 셀이나 클릭한 다음 상단의 ‘삽입’에서 ‘피벗 테이블’을 선택합니다. 뜨는 창에서 표 또는 범위가 원본 전체를 가리키는지 확인하세요. 처음에는 ‘새 워크시트’를 고르는 게 편합니다. 원본 옆에 결과를 놓으면 나중에 행이 늘면서 다른 내용과 겹칠 수 있기 때문입니다. 확인을 누르면 빈 피벗테이블과 필드 목록이 나타납니다.

필드 목록 아래에는 필터, 열, 행, 값 영역이 있습니다. 앞서 만든 구매 표라면 ‘품목’을 행으로, ‘월’을 열로, ‘금액’을 값으로 끌어 놓습니다. 그러면 CPU·SSD·RAM이 세로로, 9월·10월이 가로로 놓이고 교차하는 칸에 금액 합계가 표시됩니다. ‘수량’을 값에 한 번 더 넣으면 같은 화면에서 구매 개수도 볼 수 있죠. 필드 이름을 체크하기만 하면 엑셀이 영역을 자동으로 고르기도 하니, 원하는 배치와 다르면 직접 끌어 옮기면 됩니다.

예를 들어 10월에 CPU 180000원, SSD 120000원, RAM 70000원을 더 샀다면 9월 합계는 770000원, 10월 합계는 370000원입니다. 전체 합계는 1140000원이 나와야 합니다. 처음 만든 뒤에는 이렇게 손으로 계산할 수 있는 몇 줄을 대조해 보는 게 좋습니다. 숫자가 맞으면 필드를 옮겨 ‘월별 품목 지출’과 ‘품목별 전체 지출’을 몇 번의 드래그로 바꿔 볼 수 있습니다.

합계 대신 개수가 나오면 원본을 확인하기

금액을 값 영역에 넣었는데 ‘금액의 개수’가 나타나는 경우가 있습니다. 엑셀이 그 열을 숫자가 아닌 데이터로 해석했을 가능성이 큽니다. 금액 칸에 ‘18만원’, ‘미입력’처럼 문자가 들어갔는지, 붙여 넣은 숫자가 텍스트로 저장됐는지 먼저 확인하세요. 금액을 숫자로 바로잡고 피벗테이블을 새로 고친 다음 결과를 다시 봅니다.

집계 방식을 직접 바꾸려면 값 영역의 ‘금액의 개수’ 옆 메뉴에서 ‘값 필드 설정’을 열고 ‘값 요약 기준’을 ‘합계’로 선택하면 됩니다. 다만 원본에 문자가 남아 있다면 설정만 바꿔도 기대한 총액이 나오지 않을 수 있습니다. 이럴 때는 원본의 금액 몇 칸을 클릭해 숫자로 인식되는지 확인하는 게 빠릅니다. 금액의 합계와 건수는 의미도 다릅니다. 구매가 네 번인지, 총 얼마를 썼는지에 따라 집계 방식을 골라야 합니다.

새 행을 넣은 뒤 숫자가 그대로일 때

피벗테이블은 원본을 수정하는 순간 화면의 집계가 자동으로 바뀌는 표가 아닙니다. 원본에 새 구매 내역을 추가하거나 금액을 고쳤다면 피벗테이블 안의 셀을 마우스 오른쪽 단추로 클릭해 ‘새로 고침’을 선택합니다. 피벗테이블이 여러 개라면 ‘피벗 테이블 분석’의 새로 고침 메뉴에서 ‘모두 새로 고침’을 사용할 수 있습니다.

새로 고쳤는데도 추가한 행이 빠져 있다면 피벗테이블을 만들 때 선택한 원본 범위를 확인하세요. 일반 셀 범위를 원본으로 잡았다면 마지막 행 아래에 쓴 데이터가 범위 밖일 수 있습니다. 이 경우 원본 범위를 새 행까지 넓히거나, 앞으로 계속 기록할 파일이라면 원본을 엑셀 표로 만든 뒤 피벗테이블의 데이터 원본을 그 표로 지정하는 편이 덜 번거롭습니다.

저는 처음 피벗테이블을 만들 때 필드를 많이 올리지 않습니다. 행에 품목 하나, 값에 금액 하나만 넣고 원본의 합계와 맞는지 봅니다. 그다음 월을 열에 올리면 어느 단계에서 숫자가 달라졌는지 금방 찾을 수 있습니다. 부품 구매 내역처럼 계속 쌓이는 자료일수록 이 순서가 손으로 소계를 만들고 다시 고치는 시간보다 훨씬 적게 듭니다.