엑셀 피벗 테이블 시크릿 꿀팁 5가지

피벗 테이블, 많이들 사용하시지만 보통은 '행/열/값' 배치 정도의 기본 기능만 쓰는 경우가 많습니다. 하지만 그 안에는 원본 데이터 바로 확인하기, 진짜 유니크한 개수 세기, 자동 증가율 계산, 시트 자동 분할, 보조 열 없는 그룹화까지, 실무에서 시간을 크게 아껴주는 기능들이 숨어 있습니다.

이번 시간에는 이 5가지 꿀팁을 순서대로 소개합니다. 아는 사람만 아는 기능이지만, 알고 나면 데이터 분석과 보고서 작업이 훨씬 빨라집니다.

이번 영상에서 습득할 수 있는 주요 내용

1. 더블 클릭으로 원본 데이터 바로 보기

SOURCE 시트 데이터를 이용해서 월별/지역별 피벗 테이블을 만들었다고 가정해 봅니다. 이 중 유독 눈에 띄게 높은 숫자 하나가 있다면, 원본 데이터로 가서 지역과 월을 각각 필터로 걸어 확인하는 게 보통의 방법입니다. 번거롭죠. 하지만 이렇게 하지 않아도 됩니다.

궁금한 값이 들어있는 셀을 더블 클릭만 하면, 그 값을 이루고 있는 원본 데이터들이 새 시트에 즉시 만들어집니다. 예를 들어 '광주' 지역 '6월' 값을 더블 클릭하면, 해당 조건에 맞는 데이터만 모아 놓은 시트가 바로 생성됩니다. 알고 나면 별거 아니지만, 실무 데이터 분석에서 꽤 유용한 기능입니다.

2. '고유 개수'로 진짜 유니크한 숫자 세기

지역별 거래처 수를 알고 싶어서 '지역'을 행 영역에, '거래처'를 값 영역에 배치하면 피벗 테이블이 만들어집니다. 그런데 특정 지역의 셀을 더블 클릭해서 내역을 확인해 보면, 표시된 숫자만큼 거래처가 있는 게 아니라 실제로는 그보다 훨씬 적은 경우가 많습니다. 같은 거래처가 여러 번 결제했기 때문에 일반적인 '개수' 세기로는 결제 횟수가 나오는 것입니다.

우리가 알고 싶은 건 "결제 횟수"가 아니라 "거래처가 몇 곳이냐"입니다. 방법은 이렇습니다. 피벗 테이블을 만들 때 [삽입] > [피벗 테이블] 대화상자에서 흔히 지나치는 [데이터 모델에 이 데이터 추가] 옵션을 체크하는 것이 핵심입니다.

'지역'을 행 영역에, '거래처'를 값 영역에 두 번 드래그합니다. 그중 두 번째 '거래처'를 우클릭 > [값 필드 설정]에서 계산 유형을 [고유 개수]로 바꾸면, 왼쪽은 거래건수, 오른쪽은 진짜 유니크한 거래처 수로 비교해 볼 수 있습니다. 데이터 모델에 추가하지 않으면 이 옵션 자체가 보이지 않으니, 피벗 테이블을 만드는 첫 단계에서 꼭 기억해 두시기 바랍니다.

3. 수식 없이 월별 증가율 자동 계산하기

월별 성장률, 즉 전월 대비 몇 % 늘었는지를 원본 데이터에서 수식으로 직접 계산하려면 번거롭습니다. 이것도 피벗 테이블 안에서 해결됩니다.

'거래일자'를 행 영역에, '매출액'을 값 영역에 놓은 뒤, '매출액'을 값 영역에 한 번 더 추가해 똑같은 열을 두 개 만듭니다. 복제된 열의 숫자를 우클릭 > [값 표시 방식] > '[기준값]에 대한 비율의 차이'를 선택하고, 기준 필드는 '개월', 기본 항목은 '(이전)'으로 설정합니다. 이렇게 하면 전월 대비 증감률이 자동으로 계산되고, 열 제목을 '전월 대비'처럼 알아보기 쉽게 바꿔주면 됩니다.

원본 데이터가 추가되더라도 [피벗 테이블 분석] 탭 > [새로 고침]만 누르면 증가율도 함께 자동 업데이트됩니다.

VBA를 사용하지 않고 피벗 테이블을 지역별 시트로 나누는 방법과, 원하는 항목별로 그룹화하는 방법은 '풀버전' 영상에서 이어가겠습니다.

4. 〔풀버전 전용〕카테고리별로 시트 자동 분할하기

피벗 테이블을 만들어 배포했는데 "지역별로 따로 나눠달라"는 요청을 받는 경우가 있습니다. 매번 복사해서 필터를 걸고 또 복사하는 수작업 대신, 이 작업도 자동으로 처리할 수 있습니다. 분리할 대상(예: 팀, 지역 등)이 10개든 100개든 문제없으며, VBA 코딩 없이도 가능합니다.

5. 〔풀버전 전용〕보조 열 없이 피벗 테이블 안에서 바로 그룹화하기

피벗 테이블 데이터를 받았는데 카테고리가 너무 잘게 쪼개져 있어서 상위 범주로 그룹핑해서 보고 싶을 때가 있습니다. 원본 데이터를 건드리거나 보조 열을 새로 만들지 않고도, 피벗 테이블 안에서 바로 처리할 수 있습니다. 트렌드 파악이나 보고서 작성 시 유용합니다.

멤버십 회원 전용 강의 (풀 버전)

더블 클릭·고유 개수·증가율 계산까지는 누구나 바로 따라 할 수 있지만, 실무에서 자주 만나는 "시트를 지역별로 나눠 달라", "카테고리를 큰 범주로 합쳐 달라" 등과 같은 요청은 별도의 방법을 알아야 빠르게 처리할 수 있습니다.

풀 버전 강의에서는 ① VBA 없이 피벗 테이블을 기준 필드별로 시트를 자동 분할하는 법, ② 보조 열 없이 피벗 테이블 안에서 바로 그룹화하고 이름을 정리하는 법을 이어서 소개합니다.

MVP TIP — 피벗 테이블 200% 활용 전략

1. 더블 클릭을 습관화하세요

피벗 테이블의 특정 값이 궁금할 때는 필터를 걸지 말고 그 셀을 더블 클릭해 보세요. 원본 데이터가 새 시트로 즉시 추출됩니다.

2. 피벗 테이블을 만들 때 '데이터 모델' 옵션을 기억하세요

[삽입] > [피벗 테이블] 대화상자에서 [데이터 모델에 이 데이터 추가]를 체크해야 '고유 개수' 옵션을 쓸 수 있습니다. 나중에 추가할 수 없으니 처음부터 체크해 두는 습관이 중요합니다.

3. 값을 두 번 넣는 걸 두려워하지 마세요

같은 필드를 값 영역에 두 번 넣고 [값 표시 방식]을 다르게 지정하는 방식은 증가율 계산 외에도 다양한 비교 분석에 활용할 수 있는 기본기입니다.

자주 묻는 질문 (FAQ)

마치며

피벗 테이블은 이미 다들 쓰고 있는 기능이지만, 오늘 소개해 드린 5가지 팁만 알아도 활용 폭이 훨씬 넓어집니다. 더블 클릭 한 번으로 원본 데이터를 확인하고, 데이터 모델로 진짜 유니크한 개수를 세고, 값 표시 방식만으로 증가율을 자동 계산하는 것만으로도 매일 반복하던 수작업을 크게 줄일 수 있습니다.

여기에 시트 자동 분할과 보조 열 없는 그룹화까지 더하면, 보고서 작업 시간을 줄여줍니다. 지금 바로 예제 파일을 열어, 궁금한 셀 하나를 더블 클릭해 보는 것부터 시작해 보세요.