엑셀 피벗테이블을 쓰면 매출 자료 수천 행에서 “지점별로 월별 합계 좀 뽑아줘”라는 요청도 1분이면 끝납니다. 함수로 하나하나 계산하면 반나절이 걸리지만, 피벗테이블을 쓰면 순식간입니다.
이름이 어려워 보여서 안 쓰는 분이 많은데, 실제로는 마우스로 항목을 끌어다 놓는 게 전부입니다. 수식을 한 줄도 쓰지 않습니다.
엑셀 피벗테이블이 하는 일
한 문장으로 말하면 같은 항목끼리 묶어서 계산해 주는 도구입니다.
거래 내역이 3천 줄 있다고 하면, 그 안에는 같은 지점이 수백 번 나오고 같은 상품도 여러 번 나옵니다. 피벗테이블은 이걸 자동으로 묶어서 지점별 합계, 상품별 개수 같은 걸 만들어 줍니다.
만들기 전에 확인할 것
원본 데이터가 아래 조건을 만족해야 제대로 작동합니다.
- 첫 행이 제목 행이고, 각 열에 이름이 있을 것
- 중간에 빈 행이나 빈 열이 없을 것
- 병합된 셀이 없을 것
- 한 열에는 한 종류의 데이터만 있을 것
병합된 셀이 가장 큰 걸림돌입니다. 보고서 형식으로 예쁘게 꾸며진 표는 대개 피벗테이블에 쓸 수 없습니다. 병합을 풀고 빈칸을 채워야 합니다.
엑셀 피벗테이블 만드는 순서
데이터 안 아무 셀이나 클릭한 뒤, 삽입 탭에서 피벗 테이블을 누릅니다. 범위는 엑셀이 알아서 잡아주니 대개 그대로 확인을 누르면 됩니다.
새 시트가 열리고 오른쪽에 필드 목록이 나타납니다. 여기서 네 개의 영역에 항목을 끌어다 놓습니다.
- 행 — 세로로 나열할 항목 (예: 지점명)
- 열 — 가로로 나열할 항목 (예: 월)
- 값 — 계산할 숫자 (예: 매출액)
- 필터 — 조건을 걸어 걸러낼 항목
지점을 행에, 월을 열에, 매출액을 값에 넣으면 지점별 월별 매출표가 즉시 완성됩니다.
값 계산 방식 바꾸기
값 영역에 숫자를 넣으면 기본은 합계입니다. 평균이나 개수를 보고 싶다면 값 항목을 클릭해서 값 필드 설정을 열고 원하는 계산 방식을 고르면 됩니다.
가끔 숫자를 넣었는데 합계가 아니라 개수로 나오는 경우가 있습니다. 이건 해당 열에 텍스트가 섞여 있다는 신호입니다. 원본 데이터에 “-“나 “해당없음” 같은 글자가 들어 있는지 확인해 보세요.
엑셀 피벗테이블 실무 팁
원본이 바뀌면 새로 고침을 해야 합니다. 피벗테이블은 만든 시점의 데이터를 기준으로 계산합니다. 원본에 행을 추가했다면 피벗테이블에서 마우스 오른쪽 클릭 후 새로 고침을 눌러야 반영됩니다. 이걸 잊고 옛날 숫자로 보고하는 사고가 은근히 자주 납니다.
원본을 표로 지정해 두면 편합니다. 데이터 범위를 선택하고 Ctrl+T를 눌러 표로 만든 뒤 피벗테이블을 만들면, 행이 늘어나도 범위가 자동으로 확장됩니다.
원본 데이터에 중복이 있으면 집계가 어긋납니다. 미리 중복값을 정리해 두시면 좋습니다. 피벗테이블은 한 번 익히면 계속 쓰게 되는 기능입니다. 함수를 더 배우기 전에 이것부터 익히시는 걸 권합니다. 투자 대비 효과가 가장 큽니다.
엑셀 피벗테이블, 한 단계 더 나아가기
날짜를 월별, 분기별로 묶기
날짜 데이터를 행에 넣으면 하루하루가 전부 나열되어 표가 끝없이 길어집니다. 이럴 때 그룹 기능을 씁니다.
날짜 항목 아무 곳이나 마우스 오른쪽 클릭하고 그룹을 선택하면 월, 분기, 연도 단위로 묶을 수 있습니다. 여러 개를 동시에 선택하면 연도 아래 분기, 분기 아래 월 형태로 계층이 만들어집니다.
그룹 옵션이 회색으로 비활성화되어 있다면 날짜 열에 텍스트가 섞여 있다는 신호입니다. 원본에서 빈칸이나 “미정” 같은 글자가 있는지 확인해 보세요.
비율로 보기
금액 합계뿐 아니라 전체에서 차지하는 비중을 보고 싶을 때가 있습니다.
값 필드 설정에서 값 표시 형식 탭을 열면 됩니다. 여기서 “총합계 비율”을 고르면 각 항목이 몇 퍼센트인지 표시됩니다. 상위 항목 비율, 전년 대비 증감률 같은 것도 여기서 설정합니다.
같은 값을 두 번 넣어서 하나는 금액, 하나는 비율로 표시하면 보고서가 한결 보기 좋아집니다.
슬라이서로 필터 만들기
피벗테이블 안을 클릭하고 삽입 탭에서 슬라이서를 추가하면 버튼 형태의 필터가 생깁니다.
드롭다운 필터보다 훨씬 직관적이라, 보고서를 남에게 보여줄 때 유용합니다. 클릭 한 번으로 지점별, 기간별 화면을 전환할 수 있으니까요.
엑셀 피벗테이블에서 자주 겪는 문제
숫자인데 합계가 안 나오고 개수로 나옴
해당 열에 텍스트가 섞여 있다는 뜻입니다. 빈칸이 아니라 공백 한 칸이 들어 있거나, “-“나 “해당없음” 같은 글자가 있는 경우입니다.
원본으로 돌아가 해당 열을 정렬해 보면 이상한 값이 위나 아래로 몰려서 금방 찾을 수 있습니다.
새로 추가한 데이터가 안 나옴
새로 고침을 했는데도 안 나온다면 범위가 확장되지 않은 것입니다. 피벗테이블 분석 탭에서 데이터 원본 변경을 눌러 범위를 다시 지정하세요.
이런 일을 막으려면 원본을 표로 만들어 두면 됩니다. 표는 행이 추가될 때 범위가 자동으로 늘어납니다.
파일이 무거워짐
피벗테이블은 원본 데이터의 복사본을 파일 안에 저장합니다. 데이터가 크면 파일 용량이 두 배가 되는 셈이죠.
피벗테이블 옵션의 데이터 탭에서 원본 데이터를 파일에 저장 옵션을 끄면 용량이 줄어듭니다. 다만 이 경우 파일을 열 때마다 새로 고침을 해야 합니다.
엑셀 피벗테이블은 처음 몇 번만 익숙해지면 이후로는 매번 손으로 계산하던 작업을 몇 초 만에 끝낼 수 있게 해줍니다. 더 다양한 옵션이 궁금하다면 마이크로소프트 엑셀 공식 지원 센터에서 확인할 수 있습니다.