피벗 테이블 · 스프레드시트 데이터 분석
Pivot Table
피벗 테이블이란?
- 드래그로 만드는 교차 집계표
- 원본은 한 행에 한 건이어야 한다
- 질문을 바꿔 여러 각도로 보기 쉽다
피벗 테이블을 쓰기 시작하면 가장 먼저 바뀌는 건 보고서를 만드는 순서입니다. 보통은 "부서별로 뽑아 주세요", "이번엔 분기별로요" 같은 요청이 올 때마다 새 표를 하나씩 더 만듭니다. 피벗은 원본을 그대로 둔 채 보는 각도만 갈아 끼우는 방식이라, 요청 하나에 표 하나를 더 만들던 구조가 원본 하나에 여러 화면을 붙이는 구조로 바뀝니다. 결과적으로 담당자가 쓰는 시간이 표를 만드는 데서 숫자를 해석하는 데로 옮겨갑니다.
값 영역은 합계 외에도 개수·평균·최대·최소·비율 등으로 바꿀 수 있고, 날짜 필드는 우클릭 한 번으로 월·분기·연 단위로 묶입니다. 다만 '수강 인원 몇 명'처럼 중복을 뺀 고유 개수는 기본 피벗에서 나오지 않고, 생성 시 데이터 모델에 추가해야 집계 옵션이 생깁니다. 비슷한 일을 SUMIFS로도 하지만 쓰임이 다릅니다. 양식이 고정된 정기 보고서는 수식, 기준을 계속 바꿔 보는 탐색 작업은 피벗이 맞습니다. 여러 파일을 매달 합쳐야 하거나 행이 수십만 건을 넘어가면 그때는 파워쿼리나 BI 도구로 넘어갈 시점입니다.
현장에서 피벗 숫자가 틀리는 이유는 기능이 아니라 거의 원본입니다. 외부에서 받은 금액이 텍스트로 저장돼 있으면 그 행은 합계에서 조용히 빠지고, 원본에 행을 추가해도 범위를 표(Ctrl+T)로 잡지 않았으면 새 데이터가 반영되지 않습니다. 여기에 새로고침을 빠뜨리거나 필터가 걸린 화면을 그대로 캡처하면, 회의 자료의 총액이 재무 마감 수치와 수천만 원씩 어긋납니다. 더 곤란한 건 피벗은 오류 표시 없이 그럴듯한 표를 보여준다는 점이라, 보고 전에 총합을 원본 합계와 한 번 맞춰 보는 절차가 필요합니다.
1991년 로터스 임프루브(Lotus Improv)가 데이터를 여러 축으로 재구성하는 개념을 선보였고, 마이크로소프트가 1994년 엑셀 5.0에서 피벗 테이블이라는 이름으로 도입하며 대중화됐습니다. 이후 대부분의 스프레드시트 프로그램이 같은 기능을 제공합니다.
임직원 620명 규모의 자동차 부품 제조업 A사 이야기입니다. 인재개발팀 3명이 연간 교육비 4억 3천만 원을 집행하는데, 분기마다 경영회의용 부서별·과정별 집계표를 만드는 데 꼬박 나흘이 걸렸습니다. 수강 이력이 팀별 엑셀 12개 파일에 흩어져 있었기 때문입니다. (가상 예시)
- 인재개발팀 김 과장이 12개 파일을 한 시트로 합치면서 병합 셀과 팀별 소계 행 47개를 걷어내고, 일자·부서·과정명·수강자·금액이 한 건에 한 행으로 오도록 원본을 다시 깔았습니다.
- 범위를 표로 지정한 뒤 행에 부서, 열에 월, 값에 금액 합계를 올리자 3분 만에 첫 교차표가 나왔고, 옆자리 주임이 "이걸 나흘씩 만들었던 거냐"고 했습니다.
- 날짜를 분기로 그룹화하고 값을 인원당 평균으로 바꿔 보니, 생산본부 리더십 과정 단가가 사무직군보다 1인당 18만 원 높다는 사실이 그제야 눈에 들어왔습니다.
- 재무팀 이 차장이 "총액이 2,800만 원 안 맞는다"고 지적해 확인해 보니, 위탁기관에서 받은 정산표의 금액 일부가 텍스트로 저장돼 합계에서 빠져 있었습니다. 숫자로 변환하자 마감 수치와 맞았습니다.
- 분기 집계 작업이 나흘에서 반나절로 줄었고, 임원이 회의 중 "직급별로도 보자"고 하자 김 과장이 필드를 바꿔 3분 만에 화면에 띄웠습니다.