엑셀 피벗테이블 만들기 처음부터 따라 하는 방법
얼마 전 PC 부품 구매 내역을 엑셀로 받아 월별 지출을 보려는데, 같은 부품 이름이 여러 줄에 흩어져 있었다. 필터로 한 항목씩 골라 더하다 보니 숫자를 빠뜨리기 쉬웠다. 이런 자료는 피벗테이블로 묶는 편이 빠르다. 원본을 고치지 않고도 월별, 부품별 합계를 바꿔 가며 볼 수 있다.
원본 표부터 한 줄씩 다듬기
엑셀 피벗테이블 만들기에서 시간을 가장 많이 잡아먹는 건 버튼 위치보다 원본 데이터다. 첫 행에는 ‘구매월’, ‘부품’, ‘금액’처럼 각 열의 이름을 하나씩 넣는다. 그 아래에는 구매 건당 한 행을 쓴다. 예를 들어 ‘1월·CPU·320000’, ‘1월·SSD·90000’, ‘2월·CPU·350000’처럼 적으면 된다. 월별 소계나 전체 합계를 원본 중간에 끼워 넣으면 나중에 금액이 이중으로 더해질 수 있다.
열 제목이 비어 있거나 병합돼 있으면 먼저 풀어야 한다. 자료 가운데 완전히 빈 행이나 빈 열도 없애는 게 좋다. 금액 열에는 숫자만 넣고, ‘32만 원’ 같은 문자는 숫자로 바꾼다. 화면에 원 단위를 표시하고 싶다면 값 자체에 글자를 붙이지 말고 셀 표시 형식을 쓰면 된다. 날짜 열도 실제 날짜와 ‘2월 구매’ 같은 문장을 섞지 않는 편이 안전하다.
원본 범위 안의 셀을 하나 선택하고 Ctrl+T를 누르면 엑셀 표로 바꿀 수 있다. ‘머리글 포함’을 확인한 뒤 표를 만들어 두면 다음 달 구매 내역을 맨 아래에 추가하기 편하다. 마이크로소프트 안내에서도 엑셀 표를 피벗테이블의 원본으로 권장한다. 새 행을 표에 넣은 뒤 피벗테이블을 새로 고치면 확장된 데이터가 반영되기 때문이다.
삽입 메뉴에서 피벗테이블 만들기
표 안의 셀을 클릭한 상태에서 상단의 ‘삽입’ 탭으로 가서 ‘피벗 테이블’을 누른다. 대화 상자에 방금 만든 표 이름이나 데이터 범위가 잡혔는지 먼저 확인한다. 범위가 일부만 선택됐다면 여기서 바로 고친다. 결과를 놓을 위치는 ‘새 워크시트’를 고르면 원본과 섞이지 않아 확인하기 쉽다. ‘확인’을 누르면 빈 피벗테이블과 필드 목록이 나타난다.
필드 목록 아래에는 보통 필터, 열, 행, 값 네 영역이 있다. 이름만 보면 어렵지만 역할은 단순하다. 행은 세로로 나열할 항목, 열은 가로로 나눌 항목, 값은 계산할 숫자다. 필터는 특정 조건만 보고 싶을 때 쓴다. 체크박스를 누르면 엑셀이 필드를 자동 배치하기도 하는데, 원하는 모양이 아니면 필드 이름을 직접 끌어 옮기면 된다.
행과 열을 바꿔 실제로 필요한 숫자 보기
부품별 월간 지출을 보려면 ‘부품’을 행에, ‘구매월’을 열에, ‘금액’을 값에 넣는다. 그러면 CPU, SSD 같은 항목이 세로로 서고 1월, 2월이 가로로 놓인다. 가령 1월에 CPU 320000원, SSD 90000원, 메모리 80000원을 샀고 2월에 각각 350000원, 110000원, 90000원을 썼다면 CPU 합계는 670000원, 전체 합계는 1040000원으로 보여야 한다. 원본에서 직접 더한 금액과 피벗테이블의 총합계를 한 번 대조하면 입력 실수도 잡힌다.
같은 자료에서 월별 지출 흐름이 더 궁금하면 ‘구매월’을 행으로 옮기고 ‘부품’을 열로 옮긴다. 원본을 다시 만들 필요는 없다. 부품별 거래 건수를 세고 싶다면 거래마다 빠짐없이 채운 ‘부품’ 필드를 값 영역에 넣고 계산 방식을 ‘개수’로 설정한다. 한 건을 한 행에 적었다면 이 값이 거래 건수다. 거래번호처럼 고유한 열이 있다면 그 열의 개수를 세는 편이 더 명확하다.
합계가 이상하거나 새 자료가 안 보일 때
금액을 넣었는데 ‘합계’ 대신 ‘개수’가 나타나면 금액 열에 문자로 저장된 숫자나 문자가 섞였는지 확인한다. 값 영역의 ‘금액의 개수’ 옆 메뉴에서 ‘값 필드 설정’을 열어 ‘합계’로 바꿀 수 있지만, 원본에 문자가 남아 있으면 기대한 합계가 나오지 않을 수 있다. 금액 열을 숫자로 고친 다음 피벗테이블을 새로 고치는 순서가 확실하다.
새로 입력한 행이 보이지 않는다면 피벗테이블 안에서 마우스 오른쪽 버튼을 눌러 ‘새로 고침’을 선택한다. 그래도 빠져 있다면 원본이 엑셀 표인지, 새 행이 표 범위 안에 들어갔는지 확인한다. 일반 셀 범위를 원본으로 지정했다면 행을 추가해도 원본 범위가 저절로 늘어나지 않을 수 있다. 이 경우 ‘피벗 테이블 분석’에서 데이터 원본을 변경해 새 범위까지 포함해야 한다.
나는 피벗테이블을 만든 뒤 숫자가 그럴듯하게 보인다고 바로 쓰지 않는다. 원본의 전체 합계와 피벗테이블 총합계를 먼저 맞춰 보고, 다음에 행과 열을 바꿔 필요한 관점으로 본다. PC 부품처럼 항목이 계속 늘어나는 내역일수록 이 확인 한 번이 나중에 잘못된 지출 판단을 막아준다.
