수천, 수만 줄에 달하는 방대한 엑셀 데이터를 마주했을 때, 직장인들은 흔히 두려움을 느낍니다. “부서별 월간 매출액 합계를 구해오라”는 지시를 받으면 COUNTIF, SUMIF 같은 복잡한 함수를 어떻게 조합해야 할지 머리가 아파옵니다. 만약 함수를 전혀 몰라도 마우스 드래그 몇 번만으로 방대한 데이터를 요약하고 분석할 수 있는 마법 같은 기능이 있다면 믿으시겠습니까?
엑셀의 꽃이자 데이터 분석의 끝판왕이라고 불리는 기능, 바로 ‘피벗 테이블(Pivot Table)’입니다. 실무 엑셀은 VLOOKUP으로 시작해서 피벗 테이블로 끝난다는 말이 있을 정도로 그 위력은 대단합니다. 오늘은 엑셀 초보자도 단 5분 만에 데이터 분석 전문가처럼 훌륭한 요약 보고서를 만들어 낼 수 있는 피벗 테이블의 기초 활용법을 완벽하게 가이드해 드립니다.
1. 피벗 테이블(Pivot Table)이란 무엇인가?
‘피벗(Pivot)’은 농구나 스포츠에서 한 발을 축으로 두고 방향을 이리저리 회전하는 동작을 의미합니다. 엑셀의 피벗 테이블 역시 거대한 원본 데이터를 그대로 둔 상태에서, 사용자가 원하는 기준(축)에 따라 행과 열을 자유자재로 돌려가며 데이터를 요약하고 새로운 표를 만들어내는 훌륭한 기능입니다.
어려운 수식을 외워서 타이핑할 필요 없이, 마우스로 항목(필드)을 집어서 끌어다 놓기만 하면 엑셀이 알아서 그룹을 지어주고, 합계를 내고, 평균을 계산해 줍니다. 데이터의 흐름과 트렌드를 직관적으로 파악하는 데 이보다 더 빠르고 강력한 도구는 없습니다.
2. 피벗 테이블 만들기 실전 기초
수백 명의 직원 성명, 소속 부서, 직급, 그리고 월별 영업 실적이 적혀 있는 원본 데이터(표)가 있다고 가정해 보겠습니다.
🚨 가장 중요한 1단계: 원본 데이터 정리하기
피벗 테이블을 만들기 전, 원본 데이터가 ‘표’의 형태를 완벽하게 갖추고 있어야 오류가 나지 않습니다.
- 모든 열의 맨 첫 번째 칸에는 반드시 ‘머리글(제목)’이 있어야 합니다.
- 데이터 중간에 완전히 비어있는 행(줄)이나 열(칸)이 있으면 안 됩니다.
- 셀 병합이 되어 있다면 모두 풀어주는 것이 좋습니다.
2단계: 피벗 테이블 삽입하기
- 정리가 끝난 원본 데이터 표 안의 아무 셀이나 마우스로 클릭합니다.
- 엑셀 상단 메뉴의 [삽입] 탭으로 이동하여 가장 좌측에 있는 [피벗 테이블] 아이콘을 클릭합니다.
- ‘표 또는 범위에서 피벗 테이블 만들기’ 대화 상자가 나타나면, 엑셀이 알아서 원본 데이터의 전체 범위를 지정해 줍니다.
- 피벗 테이블을 배치할 위치로 ‘새 워크시트’를 선택하고 확인을 누릅니다.
3. 필드 레이아웃(행, 열, 값) 배치하기
새로운 시트가 열리면 왼쪽에는 텅 빈 피벗 테이블 영역이, 오른쪽에는 ‘피벗 테이블 필드’라는 작업 창이 나타납니다. 여기서부터가 진짜 분석의 시작입니다.
오른쪽 필드 창 위쪽에는 원본 데이터의 머리글(성명, 부서, 직급, 실적 등)이 체크박스 형태로 나열되어 있고, 그 아래에는 ‘필터, 열, 행, 값’이라는 4개의 네모난 영역이 있습니다. 마우스로 머리글을 끌어서 이 네모 영역에 던져 넣으면 됩니다.
- 행 영역: 표의 세로(세로축) 기준을 정합니다. ‘부서’ 필드를 마우스로 끌어서 ‘행’ 영역에 놓아보세요. 왼쪽에 영업1팀, 영업2팀 등 부서 이름이 세로로 중복 없이 깔끔하게 정리됩니다.
- 값 영역: 계산을 수행할 데이터를 넣습니다. ‘영업 실적’ 필드를 끌어서 ‘값’ 영역에 놓습니다. 엑셀이 순식간에 각 부서별 영업 실적의 ‘합계’를 계산하여 표를 완성합니다.
- 열 영역: 표의 가로(가로축) 기준을 추가로 나눕니다. ‘직급’ 필드를 ‘열’ 영역에 놓으면, 부서별 실적이 다시 대리, 과장, 부장 등 직급별로 쪼개져서 훨씬 입체적인 분석표가 만들어집니다.
값 필드 설정 변경 (합계 대신 평균 구하기)
값 영역에 들어간 데이터는 기본적으로 숫자를 다 더하는 ‘합계’로 표시됩니다. 만약 부서별 실적의 평균이나 인원수를 구하고 싶다면, ‘값’ 영역에 들어간 ‘합계: 영업 실적’을 마우스 좌클릭한 후 ‘값 필드 설정’으로 들어갑니다. 여기서 계산 함수를 ‘평균’이나 ‘개수’로 클릭 한 번에 바꿀 수 있습니다.
4. 슬라이서(Slicer) 기능으로 대시보드 만들기
피벗 테이블을 더 세련되게 다루는 꿀팁은 ‘슬라이서’ 기능을 활용하는 것입니다. 피벗 테이블을 클릭한 상태에서 상단의 [피벗 테이블 분석] 탭으로 들어가 [슬라이서 삽입]을 누릅니다.
‘월(Month)’ 필드를 선택하면 리모컨 같은 예쁜 버튼 창이 생깁니다. 이제 보고서를 보는 사람이 ‘1월’ 버튼을 누르면 1월 데이터만, ‘2월’ 버튼을 누르면 2월 데이터만 실시간으로 피벗 테이블에 반영되어 보여집니다. 함수 하나 쓰지 않고도 대기업의 전문적인 데이터 대시보드 부럽지 않은 보고서가 완성된 것입니다.
5. 결론 및 요약
피벗 테이블은 엑셀이 제공하는 기능 중 가장 높은 생산성을 자랑합니다. 원본 데이터를 절대 훼손하지 않으면서도, 마우스 드래그 앤 드롭 몇 번으로 방대한 데이터를 요리조리 분해하고 합치며 인사이트를 얻어낼 수 있습니다. 오늘 알려드린 행, 열, 값 영역의 배치 원리만 이해하시고, 본인이 가진 데이터를 이리저리 끌어다 놓으며 연습해 보세요. 더 이상 복잡한 함수와 싸울 필요 없이, 여러분의 데이터 요약 및 분석 능력이 전문가 수준으로 단숨에 업그레이드될 것입니다.