엑셀 피벗테이블 처음 쓰는 법 — 데이터 정리부터 슬라이서까지

엑셀 피벗테이블 완성 화면과 슬라이서 패널이 함께 보이는 대표 이미지

처음 회사에서 “이거 피벗테이블로 정리해서 보고해주세요”라는 말을 들으면 무슨 소리인지 막막하기 쉽습니다. 함수도 아니고 그냥 표도 아닌데, 클릭 몇 번으로 수백 줄짜리 데이터가 요약된다는 게 잘 믿기지 않았는데요.

그런데 원본 데이터 정리부터 슬라이서까지 순서대로 따라 해 보면, 엑셀 피벗테이블은 생각보다 어려운 기능이 아닙니다. 이 글에서는 사무직 초보자가 바로 따라 할 수 있도록 원본 데이터 정리 → 삽입 → 필드 배치 → 값 요약 → 새로고침 → 슬라이서까지, 실무에서 쓰는 순서 그대로 정리해보겠습니다.


■ 원본 데이터부터 정리해야 하는 이유

피벗테이블을 처음 배울 때 다들 삽입 버튼부터 찾으시는데요. 그런데 정작 가장 많은 오류가 나는 지점은 삽입 이후가 아니라 그 이전, 즉 원본 데이터 단계입니다. 이 순서를 건너뛰면 나중에 두 배로 고생하게 됩니다.

머리글은 반드시 1행에, 열마다 겹치지 않게 넣어야 합니다. 헤더 행에 빈 칸이 하나라도 있으면 엑셀이 필드명을 인식하지 못해 “필드 이름이 잘못됨” 같은 오류를 띄우는데요. 병합된 셀도 마찬가지로 문제가 됩니다 — 피벗테이블은 병합 셀을 처리하지 못하기 때문에, 특히 헤더 행에 병합이 남아 있으면 오류의 직접적인 원인이 됩니다.

데이터 중간에 빈 행이나 빈 열이 있는 것도 위험합니다. 엑셀이 그 지점에서 데이터가 끝난 것으로 오인해, 그 뒤에 있는 내용을 통째로 누락시킬 수 있기 때문입니다. 숫자를 넣어야 할 값 열에 빈 셀이 있으면 “합계”가 아니라 “개수”로 잘못 집계되는 부작용도 있어서, 가능하면 0이나 “해당없음”으로 채워두는 편이 안전합니다.

또 하나, 원본 데이터 안에 이미 “합계”나 “평균” 같은 요약 행을 넣어두는 경우가 있는데요. 이렇게 하면 피벗테이블이 그 요약 행조차 하나의 개별 기록으로 잘못 집계해버립니다. 원본에는 가공되지 않은 순수한 자료만 남겨두는 것이 원칙입니다.

피벗테이블용으로 정리된 원본 데이터 표 예시 - 머리글 1행, 병합 없음, 빈 행 없음

이렇게 정리한 표는 Ctrl+T로 정식 “표(Table)”로 변환해두는 것을 추천합니다. 표로 만들어두면 나중에 데이터가 추가돼도 피벗테이블의 원본 범위가 자동으로 늘어나기 때문인데요. 이 부분은 뒤에서 새로고침을 다룰 때 다시 이야기하겠습니다.


■ 피벗테이블 삽입하기, 리본 메뉴 세 번이면 끝

원본 데이터를 정리했다면 이제 진짜 삽입할 차례입니다. 방법은 생각보다 간단합니다.

데이터 범위 안의 아무 셀이나 클릭한 뒤, 리본 메뉴의 [삽입] 탭 → [피벗 테이블]을 누르면 됩니다. 셀 하나만 선택해도 엑셀이 인접한 데이터 범위를 자동으로 알아서 인식해주는데요. 이 부분이 처음에는 신기하게 느껴지실 것입니다.

대화상자가 뜨면 두 가지만 확인하시면 됩니다. 첫째는 분석할 데이터 범위가 맞는지, 둘째는 피벗테이블을 새 워크시트에 만들지 기존 워크시트에 만들지입니다. 처음 다루신다면 새 워크시트를 선택하시길 권해 드립니다 — 작업 공간이 넓어서 필드를 배치하고 결과를 확인하기가 한결 편하기 때문입니다.

확인을 누르면 빈 피벗테이블 틀과 함께 화면 오른쪽에 “피벗 테이블 필드” 작업창이 나타납니다. 어떻게 배치해야 할지 감이 안 잡히신다면, [삽입] 탭의 [추천 피벗 테이블]을 먼저 눌러보시는 것도 방법입니다. 엑셀이 데이터에 맞는 레이아웃 몇 가지를 자동으로 제안해주기 때문에, 시작점을 잡기에는 충분히 쓸모가 있습니다.


■ 행·열·값·필터, 필드는 어디에 놓아야 할까

피벗테이블 필드 작업창을 처음 보면 상자가 네 개나 있어서 당황하실 수 있는데요. 그런데 역할만 알면 배치는 어렵지 않습니다.

  • 행(Rows) 영역: 거래처명, 제품명, 담당자명처럼 텍스트/범주형 데이터를 놓습니다. 여기에 넣은 항목들이 피벗테이블의 세로축으로 나열됩니다.
  • 열(Columns) 영역: 행과 교차하는 두 번째 기준을 놓는 자리입니다. 흔히 월별 날짜나 또 다른 범주를 배치해 교차표 형태를 만듭니다.
  • 값(Values) 영역: 실제로 집계할 숫자 데이터를 놓습니다. 기본값은 “합계”이고, 숫자가 아닌 필드를 넣으면 자동으로 “개수”로 바뀝니다.
  • 필터(Filters) 영역: 전체에 적용할 조회 조건을 놓는 자리로, 위쪽에 드롭다운으로 표시됩니다.

필드는 마우스로 끌어다 언제든 다른 영역으로 옮길 수 있습니다. 원본 데이터를 건드리지 않고도 관점만 바꿔가며 여러 요약표를 만들 수 있다는 것, 이것이 이 기능의 진짜 매력입니다.

피벗 테이블 필드 작업창에서 행·열·값·필터 영역에 필드를 드래그로 배치하는 화면

값 영역의 집계 방식도 바꿀 수 있습니다. 값 필드명을 마우스 오른쪽 버튼으로 클릭해 [값 요약 기준]을 선택하면 합계·개수·평균·최댓값·최솟값 등을 바로 고를 수 있는데요. 더 세부적인 옵션까지 조정하려면 같은 메뉴에서 [값 필드 설정]을 열면 됩니다.

여기서 하나 꼭 짚고 넘어가야 할 함정이 있습니다. 이 집계의 “평균”은 어디까지나 단순 산술 평균이라, 수량(비중)을 반영하지 않는다는 점입니다. 예를 들어 500원짜리 상품 100개, 1,000원짜리 상품 200개를 팔았다고 해보겠습니다.

단가 필드를 그냥 “평균”으로 잡으면 (500+1,000)÷2, 즉 750원으로 계산되어버리는데요. 실제 판매금액 기준으로 계산한 가중평균은 833원이니, 이 둘은 완전히 다른 숫자입니다. 수량을 고려한 평균이 필요하다면 “계산 필드” 기능으로 “금액 합계 ÷ 수량 합계” 수식을 직접 만들어야 한다는 것, 잊지 않으셨으면 합니다.


■ 원본이 바뀌면? 새로고침을 잊지 마세요

피벗테이블을 만들고 나서 흔히 하는 착각이 하나 있습니다. 원본 데이터를 고치면 표도 알아서 바뀔 거라는 생각인데요. 결론부터 말씀드리면 — 그렇지 않습니다.

원본을 수정하거나 새 행을 추가해도 이미 만들어진 표에는 자동으로 반영되지 않습니다. 반드시 손으로 새로고침을 눌러줘야 하는데요. 위에서 마우스 오른쪽 버튼을 클릭해 [새로 고침]을 누르거나, 단축키 Alt+F5를 쓰면 됩니다.

그런데 새로고침만 눌렀는데도 새로 추가한 행이 반영되지 않는 경우가 있습니다. 처음 만들 때 지정한 원본 범위가 고정되어 있으면, 그 범위 밖에 추가된 행은 새로고침을 해도 잡히지 않기 때문입니다. 이럴 때는 우클릭해 [피벗 테이블 옵션] → [데이터 원본 변경]에서 범위를 다시 지정해줘야 합니다.

이 번거로움을 근본적으로 없애는 방법이 바로 앞서 말씀드린 “표(Table)”입니다. 원본 데이터를 Ctrl+T로 표로 만들어두면 새 행이 추가될 때 표 범위 자체가 자동으로 늘어나기 때문에, 새로고침 한 번만 눌러도 새 데이터까지 반영됩니다. 매번 범위를 다시 지정하는 수고를 덜고 싶으시다면, 처음부터 표로 만들어두시길 권해 드립니다.


■ 슬라이서로 클릭 한 번에 필터링하기

어느 정도 익숙해지셨다면, 마지막으로 슬라이서를 소개해 드리고 싶습니다. 슬라이서는 버튼 클릭만으로 빠르게 필터링할 수 있게 해주는 시각적 도구인데요. 기존의 드롭다운 필터보다 직관적이고, 지금 어떤 조건으로 걸러져 있는지 한눈에 보인다는 점이 확실히 다릅니다.

삽입 방법도 어렵지 않습니다. 안의 셀을 클릭한 상태에서 [삽입] 탭 → [슬라이서]를 누르고, 필터링에 쓰고 싶은 필드(담당자, 지역, 카테고리 등)에 체크하면 됩니다. 확인을 누르면 해당 필드의 항목들이 버튼 형태로 나열된 패널이 만들어집니다.

슬라이서 패널에서 항목 버튼을 클릭해 피벗테이블을 필터링하는 화면

버튼을 클릭하면 결과가 즉시 그 조건으로 필터링됩니다. 여러 항목을 한꺼번에 고르고 싶으면 Ctrl을 누른 채 클릭하시면 되고, 필터를 지우고 싶으면 우측 상단의 “필터 지우기” 아이콘을 누르면 됩니다.

같은 원본 데이터를 쓰는 표가 여러 개라면, 슬라이서를 우클릭해 [보고서 연결]에서 연결할 대상들을 선택할 수도 있습니다. 슬라이서 하나로 여러 표를 한꺼번에 제어할 수 있다는 뜻인데요. 그리고 날짜 필드가 있다면 일반 슬라이서 대신 “시간 표시 막대”를 넣어보시는 것도 추천합니다. 연/분기/월/일 단위로 기간을 드래그해서 필터링할 수 있어, 날짜 범위를 다룰 때는 이쪽이 더 편합니다.


여기까지 따라오셨다면, 피벗테이블이 처음 생각했던 것만큼 복잡한 기능은 아니라는 걸 느끼셨을 것 같습니다. 사실 어려운 건 이 기능 자체가 아니라, 그 앞단인 원본 데이터를 깔끔하게 정리하는 습관이었습니다.

처음에는 삽입 버튼부터 눌러 보고 오류를 만나기 쉽습니다. 순서를 바꿔 데이터 정리부터 시작하면, 그다음부터는 오히려 수월합니다. 다음에 수백 줄짜리 데이터를 앞에 두고 막막해지신다면, 함수를 하나하나 짜기 전에 이 기능부터 한번 열어보시길 권해 드립니다.


■ 함께 하면 좋은 글

거래처가 많아질 때 엑셀 거래처 목록이 엉키는 이유와 정리법피벗테이블을 쓰기 전, 원본 데이터가 왜 자꾸 엉키는지 그 원인과 정리 방법을 다룬 글입니다. 이번 글의 “원본 데이터 정리” 단계와 이어서 읽으면 좋습니다.
회사에서 바로 쓰는 엑셀 데이터 유효성 검사 방법 6가지날짜 칸에 텍스트가 섞이거나 숫자 칸에 단위가 같이 들어가는 문제를 미리 막는 방법을 소개합니다. 데이터를 잘못 인식하는 사고를 예방하는 데 도움이 됩니다.
엑셀 숫자 앞 0 표시하는 방법 4가지, 사라지는 원인부터 해결까지사원번호나 우편번호처럼 0으로 시작하는 값이 엑셀에서 사라지는 현상을 다룬 글입니다. 원본 데이터의 형식을 통일해야 집계 오류를 막을 수 있다는 점과 함께 참고하시면 좋습니다.

위로 스크롤