엑셀 피벗테이블 값 합계와 비율 바꾸는 방법

얼마 전 매출표를 피벗테이블로 묶었는데, 매출 합계 대신 건수가 표시돼서 잠깐 멈칫했습니다. 엑셀 피벗테이블 값은 어떤 열을 넣느냐에 따라 계산 방식이 달라집니다. 합계가 필요한지, 주문 건수가 필요한지, 전체에서 차지하는 비율이 필요한지부터 정하면 설정할 곳도 금방 찾을 수 있습니다.

값 영역에 넣을 열부터 고르기

원본 표에 지역, 주문일, 매출액 열이 있다고 해볼게요. 피벗테이블 안의 셀을 클릭하면 필드 목록이 나타납니다. 지역은 ‘행’ 영역에, 매출액은 ‘값’ 영역에 끌어 놓으면 지역별 매출을 볼 수 있습니다. 필드 목록이 보이지 않는다면 피벗테이블 안을 다시 클릭해 보세요.

예를 들어 서울 매출이 12만 원과 8만 원, 부산 매출이 6만 원과 4만 원이라면 합계는 각각 20만 원과 10만 원입니다. 같은 자료라도 매출액을 ‘행’ 영역에 넣으면 금액별 항목이 나열됩니다. 숫자를 더하거나 세려면 해당 열이 ‘값’ 영역에 있는지 먼저 확인하는 게 좋습니다.

처음 만드는 표라면 원본 데이터의 첫 줄에 ‘매출액’처럼 알아보기 쉬운 열 제목을 넣고, 중간에 완전히 빈 행을 두지 않는 편이 편합니다. 주문이 계속 추가되는 자료는 원본 범위를 엑셀의 표로 만들어 두면 이후 데이터를 관리하기에도 수월합니다.

합계 대신 개수나 평균이 나올 때

값 영역의 ‘매출액의 합계’ 옆 화살표를 눌러 ‘값 필드 설정’을 열어보세요. ‘값 요약 기준’에서 합계, 개수, 평균 등을 고를 수 있습니다. 앞의 사례에서 합계를 고르면 서울 20만 원, 부산 10만 원이고, 개수를 고르면 두 지역 모두 2건입니다. 평균은 서울 10만 원, 부산 5만 원이 됩니다. 보고 싶은 숫자에 맞춰 계산 기준을 선택하면 됩니다.

분명 금액 열인데 ‘매출액의 개수’가 먼저 나온다면 원본 열을 확인해야 합니다. Microsoft의 엑셀 안내에 따르면 숫자 데이터는 보통 합계로, 텍스트로 인식된 데이터는 개수로 표시됩니다. 금액 앞에 문자나 공백이 섞였거나 숫자가 텍스트로 저장돼 있으면 예상과 다른 결과가 나올 수 있습니다. 원본 값을 실제 숫자로 고친 뒤 피벗테이블을 새로 고쳐 보세요.

여기서 개수는 그 필드에서 비어 있지 않은 값의 수를 셉니다. 주문 행 자체를 세고 싶다면 값이 빠질 수 있는 매출액보다 모든 행에 들어 있는 주문번호를 값 영역에 넣는 편이 정확합니다. 빈칸이 있는 자료에서는 두 개수가 서로 다를 수 있거든요.

금액과 전체 비율을 나란히 보기

지역별 매출이 전체 매출에서 차지하는 몫도 궁금할 때가 있습니다. 매출액 필드를 값 영역에 한 번 더 끌어 놓고, 두 번째 필드의 ‘값 필드 설정’을 여세요. 첫 번째는 합계로 두고 두 번째는 ‘값 표시 형식’에서 ‘총합계 비율’을 선택하면 금액과 비율을 함께 볼 수 있습니다.

서울 20만 원, 부산 10만 원이라면 총매출 30만 원을 기준으로 서울은 약 66.7%, 부산은 약 33.3%입니다. ‘총합계 비율’은 표 전체 합계를 기준으로 하지만, ‘행 합계 비율’과 ‘열 합계 비율’은 기준이 다릅니다. 월별 열까지 넣었다면 어떤 합계를 기준으로 비교하려는지 확인하고 골라야 숫자를 잘못 읽지 않습니다.

표시되는 숫자가 200000처럼 붙어 있어 읽기 어렵다면 값 필드 설정의 ‘숫자 서식’에서 천 단위 구분 기호를 지정할 수 있습니다. 소수 자릿수도 그곳에서 맞추면 같은 값 필드에 형식이 적용됩니다. 계산을 바꾸는 ‘값 표시 형식’과 숫자의 겉모양을 바꾸는 ‘숫자 서식’은 역할이 다릅니다.

원본을 고쳤는데 값이 그대로라면

원본 표의 숫자를 수정하거나 새 주문을 추가해도 피벗테이블 값이 바로 달라지지 않을 수 있습니다. 이때는 피벗테이블 안에서 마우스 오른쪽 버튼을 눌러 ‘새로 고침’을 선택하세요. 피벗테이블이 여러 개라면 리본 메뉴의 ‘모두 새로 고침’이 편합니다.

새 행을 넣었는데도 값이 빠져 있다면 피벗테이블의 원본 범위에 그 행이 포함됐는지도 확인해야 합니다. 새로 고침은 이미 지정된 원본을 다시 읽는 기능이라, 원본 범위 밖에 추가한 행까지 자동으로 포함하는 것은 아닙니다. 매달 주문이 늘어나는 파일이라면 처음부터 엑셀 표를 원본으로 쓰는 방법이 실수를 줄여줍니다.

피벗테이블에서 숫자가 이상해 보일 때는 값 영역의 열, 계산 기준, 원본 데이터 형식, 새로 고침 상태 순서로 확인하는 습관이 꽤 유용합니다. 같은 매출 자료도 합계와 건수, 비율을 나란히 놓으면 훨씬 읽기 쉬운 보고서가 됩니다.