엑셀 피벗테이블 활용법

분기 실적 보고를 앞두고 몇 천 줄짜리 판매 데이터를 부서별, 월별, 담당자별로 몇 번씩 다르게 잘라서 봐야 했던 적이 있습니다. SUMIFS를 조합해서 표를 짜다가 조건이 하나만 바뀌어도 수식을 전부 다시 손봐야 하는 상황이 반복됐는데요. 그때 팀 선배가 "그거 피벗으로 하면 5분이면 끝나"라고 알려준 뒤로, 정리 방식 자체가 바뀌었습니다. 오늘은 대량의 데이터를 요약할 때 실무에서 가장 강력한 도구로 꼽히는 피벗테이블을 정리해보겠습니다.

피벗테이블이 뭔가

피벗테이블은 원본 데이터를 건드리지 않은 채로, 원하는 기준에 따라 자동으로 요약하고 집계해주는 기능입니다. 앞서 다룬 SUMIF나 COUNTIF가 "조건 하나를 미리 정해서 수식을 짜는" 방식이었다면, 피벗테이블은 "기준을 마우스로 끌어다 놓기만 하면 알아서 요약해주는" 방식입니다.

예를 들어 부서별 합계가 필요하면 부서를 끌어다 놓고, 담당자별로 다시 보고 싶으면 담당자를 끌어다 놓으면 됩니다. 수식을 새로 짤 필요가 없습니다.

피벗테이블 만드는 순서

1단계: 원본 데이터를 표 형태로 정리한다

피벗테이블을 쓰려면 원본 데이터가 정돈된 형태여야 합니다. 열마다 항목명(부서, 날짜, 담당자, 금액 등)이 한 줄로 명확히 있어야 하고, 중간에 빈 행이나 병합된 셀이 있으면 안 됩니다. 이전 글에서 다룬 데이터 정리 과정이 피벗테이블의 전제 조건이라고 보시면 됩니다.

2단계: 삽입 메뉴에서 피벗테이블을 선택한다

원본 데이터 범위를 선택한 상태에서 [삽입] 탭 → [피벗 테이블]을 클릭합니다. 새 워크시트에 만들지, 같은 시트에 만들지 선택하는 창이 뜨는데, 원본과 헷갈리지 않도록 보통 새 워크시트를 추천합니다.

3단계: 필드를 끌어다 배치한다

피벗테이블을 만들면 오른쪽에 필드 목록이 나타나고, 아래에 네 개의 영역이 있습니다.

• 행: 세로로 나열할 기준 (예: 부서명)
• 열: 가로로 나열할 기준 (예: 월)
• 값: 실제로 집계할 숫자 (예: 매출 금액)
• 필터: 특정 조건으로 화면 전체를 걸러낼 기준 (예: 연도)

부서별 매출 합계가 필요하다면 부서명을 '행'에, 매출 금액을 '값'에 끌어다 놓기만 하면 됩니다. 이렇게 몇 초 만에 SUMIF로 한참 걸려 짜야 했던 표가 완성됩니다.

실무 예시: 부서별, 월별 매출 요약표 만들기

원본 데이터에 부서명, 매출월, 매출금액 열이 있다고 가정해봅시다.

1. 부서명을 '행' 영역에 끌어다 놓습니다.
2. 매출월을 '열' 영역에 끌어다 놓습니다.
3. 매출금액을 '값' 영역에 끌어다 놓습니다.

이렇게 하면 세로축에는 부서, 가로축에는 월이 나열되고, 교차하는 칸마다 해당 부서의 해당 월 매출 합계가 자동으로 채워진 표가 완성됩니다. SUMIFS로 이 표를 만들려면 부서 수 곱하기 월 수만큼 수식을 일일이 넣어야 하지만, 피벗테이블은 필드 세 개를 끌어다 놓는 것으로 끝납니다.

집계 방식 바꾸기

'값' 영역에 넣은 필드는 기본적으로 합계로 계산되지만, 오른쪽 클릭 후 [값 필드 설정]에 들어가면 평균, 개수, 최댓값, 최솟값 등으로 바꿀 수 있습니다. 예를 들어 부서별 매출 건수가 궁금하다면 합계 대신 개수로 바꾸면 됩니다.

실무에서 자주 쓰는 응용 기능

그룹 설정으로 날짜를 월/분기 단위로 묶기

날짜 데이터를 '행'이나 '열'에 넣으면 일 단위로 전부 나열되어 표가 너무 길어질 때가 있습니다. 이럴 때 날짜 필드 위에서 오른쪽 클릭 → [그룹]을 선택하면 월, 분기, 연도 단위로 자동으로 묶어줍니다. 실적 보고서를 만들 때 정말 자주 쓰는 기능입니다.

슬라이서로 클릭 몇 번에 조건 바꾸기

'필터' 영역 대신 슬라이서를 넣으면, 드롭다운 대신 버튼 형태로 부서나 연도를 클릭 한 번에 바꿔가며 볼 수 있습니다. [피벗테이블 분석] 탭 → [슬라이서 삽입]에서 추가할 수 있고, 보고서를 여러 사람과 같이 볼 때 훨씬 직관적입니다.

원본 데이터가 바뀌면 새로 고침이 필요하다

피벗테이블은 원본 데이터를 자동으로 계속 따라가지 않습니다. 원본에 데이터를 추가하거나 수정했다면, 피벗테이블 위에서 오른쪽 클릭 후 [새로 고침]을 눌러줘야 반영됩니다. 여기서 신입들이 자주 하는 실수가 원본을 업데이트해놓고 피벗테이블은 예전 데이터 그대로 보고서에 넣어버리는 경우입니다. 보고서를 최종 제출하기 전에는 반드시 새로 고침부터 확인하는 습관을 들이시길 추천드립니다.

자주 하는 실수 정리

• 원본 데이터에 빈 행이나 병합된 셀이 있어서 집계가 이상하게 되는 경우: 피벗테이블을 만들기 전에 원본 데이터부터 정리하는 게 먼저입니다.
• 새로 고침을 안 해서 예전 데이터로 보고하는 경우: 원본이 바뀌면 항상 새로 고침을 눌러야 합니다.
• 필드를 잘못된 영역에 넣어서 원하는 표가 안 나오는 경우: 세로로 볼 기준은 행, 가로로 볼 기준은 열, 집계할 숫자는 값이라는 구조를 먼저 머릿속에 그려놓고 시작하면 헷갈리지 않습니다.
• 값 영역에 텍스트 데이터를 넣어서 합계 대신 개수만 나오는 경우: 값 영역은 원래 숫자를 집계하는 자리라서, 텍스트를 넣으면 자동으로 개수 세기로 바뀝니다. 의도한 결과가 아니라면 필드를 다시 확인해봐야 합니다.

마치며

피벗테이블은 처음 마주하면 필드를 어디에 끌어다 놓아야 할지 낯설게 느껴지지만, 몇 번 직접 원본 데이터로 연습해보면 왜 실무자들이 "데이터 집계의 종결자"라고 부르는지 체감하실 수 있을 겁니다. SUMIF, COUNTIF로 수식을 짜다가 조건이 자꾸 바뀌어서 번거로우셨다면, 그 데이터를 피벗테이블에 그대로 넣어보시길 추천드립니다. 다음 편에서는 이렇게 집계한 데이터를 한눈에 보여주는 보고용 차트 만드는 방법을 다뤄보겠습니다.