엑셀 피벗테이블 수정방법 막힐 때 이렇게 바꾸세요

얼마 전 월별 지출표에 새 항목을 추가했는데, 피벗테이블 합계는 그대로여서 숫자를 한참 들여다봤습니다. 원본 데이터와 피벗테이블은 같은 화면에 있어도 움직이는 방식이 다르더군요. 엑셀 피벗테이블 수정방법은 무엇을 바꾸려는지부터 구분하면 훨씬 쉽습니다. 숫자를 최신 상태로 만들지, 집계 기준을 바꿀지, 새 행까지 포함할지에 따라 누를 곳이 다릅니다.

숫자가 예전 그대로라면 새로 고침부터

원본 시트에서 금액을 고쳤는데 피벗테이블에는 이전 금액이 보인다면, 피벗테이블 안의 셀을 마우스 오른쪽 버튼으로 누르고 ‘새로 고침’을 선택합니다. 또는 피벗테이블을 클릭한 뒤 ‘피벗 테이블 분석’ 탭에서 새로 고침을 누르면 됩니다. Microsoft 안내에서도 원본 데이터를 바꾼 뒤 피벗테이블을 새로 고치는 절차를 설명합니다.

예를 들어 3월 배송비를 12,000원에서 21,000원으로 바로잡았다면, 원본 셀 수정 후 새로 고침만으로 합계가 반영됩니다. 같은 파일에 피벗테이블이 여러 개라면 ‘모두 새로 고침’을 쓰는 편이 편합니다. 단, 새로 고침은 이미 지정된 원본 범위를 다시 읽는 작업입니다. 범위 밖에 새로 붙인 행까지 자동으로 잡아 주는지는 따로 확인해야 합니다.

• 기존 행의 금액만 수정했다면 새로 고침
• 새 행을 추가했다면 원본 범위 확인 후 새로 고침
• 필드 이름이 안 보인다면 원본의 열 제목과 새로 고침 상태 확인

행과 열을 바꾸려면 필드 목록을 열기

피벗테이블이 ‘월별 매출’로 보이는데 ‘담당자별 매출’로 바꾸고 싶을 때는 표 안의 숫자를 직접 고치지 않습니다. 피벗테이블을 클릭하면 오른쪽에 필드 목록이 나타납니다. 보이지 않으면 ‘피벗 테이블 분석’ 탭의 ‘필드 목록’을 누릅니다. 화면 구성에 따라 탭 이름이나 위치는 조금 다를 수 있습니다.

필드 목록 아래에는 보통 필터, 열, 행, 값 영역이 있습니다. ‘월’이 행에 있고 ‘매출액’이 값에 있다면, 월을 빼고 ‘담당자’를 행으로 끌어오면 담당자별 매출표가 됩니다. ‘월’을 열에 놓으면 담당자별 행과 월별 열을 동시에 볼 수도 있습니다. 기존 보고서를 여러 곳에 쓰고 있다면 시트를 복제한 뒤 배치를 바꾸면 비교하기 편합니다.

필드가 너무 많이 보일 때는 체크 표시를 해제하거나 해당 필드를 영역 밖으로 끌어 제거합니다. 여기서 빠지는 것은 피벗테이블의 표시 항목이지 원본 시트의 열이 아닙니다. 반대로 원본에 새 열을 만들었는데 목록에 나타나지 않으면 먼저 새로 고침을 해 보세요. 그래도 없으면 피벗테이블이 참조하는 원본 범위에 그 열이 포함됐는지 확인합니다.

합계가 개수로 나올 때는 값 필드 설정

금액을 넣었는데 합계 대신 ‘개수’가 나오는 경우가 있습니다. 이때는 값 영역에 들어간 필드 이름 옆의 메뉴에서 ‘값 필드 설정’을 열고 ‘값 요약 기준’을 합계로 바꿉니다. 평균, 최댓값, 개수도 여기서 선택합니다. 예를 들어 주문 40건의 총매출은 합계, 주문당 평균 금액은 평균이 맞습니다. 무엇을 구하려는지에 따라 같은 금액 열도 결과가 달라집니다.

합계로 바꿨는데 값이 이상하면 원본 금액 열을 먼저 봐야 합니다. 숫자처럼 보여도 일부 셀이 문자로 저장됐거나 ‘미입력’ 같은 글자가 섞이면 원하는 계산이 나오지 않을 수 있습니다. 원본의 자료형을 맞춘 뒤 다시 새로 고침합니다. 값에 쉼표나 원화 표시를 붙이고 싶다면 숫자를 직접 입력해 덮어쓰지 말고 값 필드 설정의 ‘표시 형식’에서 숫자 형식을 지정하는 편이 안정적입니다.

새 행이 빠졌다면 원본 범위를 바꾸기

원본이 2행부터 120행까지였는데 121행부터 새 주문 15건을 붙였다면, 기존 피벗테이블은 여전히 120행까지만 읽고 있을 수 있습니다. 피벗테이블을 클릭하고 ‘피벗 테이블 분석’ 탭에서 ‘데이터 원본 변경’을 선택해 범위의 마지막 행을 확인합니다. 새 행까지 포함되도록 범위를 지정한 다음 새로 고침하면 됩니다. 수정 전후의 주문 건수나 총액을 대조하면 빠진 행이 있는지 바로 드러납니다.

매달 행이 늘어나는 파일이라면 원본 범위를 엑셀 ‘표’로 만들어 두는 방법이 편합니다. 원본 데이터의 셀 하나를 선택하고 Ctrl+T를 누른 뒤 머리글 포함 여부를 확인합니다. 피벗테이블의 데이터 원본을 그 표로 지정하면, 표에 추가한 행은 새로 고침할 때 함께 반영됩니다. 기존 피벗테이블의 원본이 일반 셀 범위라면 표를 만들기만 해서는 연결 대상이 바뀌지 않으므로 ‘데이터 원본 변경’까지 확인해야 합니다.

제가 자주 쓰는 순서는 간단합니다. 원본 값을 고쳤으면 새로 고침, 보고 싶은 기준이 달라졌으면 필드 목록, 집계 방식이 틀렸으면 값 필드 설정, 새 행이 빠졌으면 데이터 원본 변경입니다. 피벗테이블이 말을 안 듣는 것처럼 보여도 어느 부분이 오래된 상태인지 찾으면 대부분 몇 번의 클릭으로 바로잡을 수 있습니다.