엑셀 피벗테이블, 실무자를 위한 핵심 기능 총정리

실습 가이드

라이브 강의 전체영상도 함께 확인해보세요!

위캔두 멤버십 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브 강의 풀영상을 모두 확인하실 수 있습니다.

함수 vs 피벗테이블: 실무에서 피벗테이블이 중요한 이유

엑셀의 함수는 직장인이 갖춰야 할 핵심 기능으로 자주 언급됩니다. 그러나 실무에서 작성하는 대부분의 보고서는 단순한 합계·평균과 같은 집계 작업이 중심이기 때문에, 이런 경우에는 함수보다 피벗테이블을 사용하는 편이 훨씬 효율적입니다. 간단한 예제로 그 이유를 하나씩 살펴보겠습니다.

1. 함수로 집계해보기 : 예제파일에서 [간단예제_함수] 시트로 이동한 후, 작성된 데이터에서 각 제조사별 매출 합계를 함수로 집계해보겠습니다. I1셀에 "제조사"를 입력한 다음, I2셀에 아래와 같이 UNIQUE 함수를 작성해 제조사의 고유값 목록을 구합니다.
   =UNIQUE(E2:E25)

   

   오빠두Tip : 엑셀 2019 이전 버전 사용자는 [데이터] 탭 - [중복된 항목 제거] 기능을 활용해 제조사의 고유값 목록을 구할 수 있습니다. 자세한 방법은 영상 강의를 참고하세요.
2. 이어서 각 제조사의 매출 합계를 계산합니다. J1셀에 "매출 합계"를 입력한 후, 아래와 같이 SUMIF 함수를 작성해 제조사별 매출 합계를 구합니다. 마지막으로 결과 범위를 선택한 뒤 [홈] 탭 - [쉼표] 버튼을 클릭하거나 단축키 Ctrl + Shift + 1 을 눌러 천 단위 구분 기호를 적용하면 보고서가 완성됩니다.
   =SUMIF(E1:E25,I2#,G1:G25)

   

   오빠두Tip : 엑셀 2021부터 새롭게 도입된 분산 범위와 해시(#) 기호 활용법은 아래 기초 영상 강의에서 자세히 다룹니다.
   
3. 함수로 작성한 보고서의 한계 : 이처럼 일회성으로 보고서를 만들 때는 함수만으로도 충분히 편리하게 작업할 수 있습니다. 다만 작성한 보고서의 구조를 바꾸거나 합계 대신 평균으로 집계 방식을 변경해야 한다면 상황이 달라집니다. 예를 들어 지금은 "제조사별 매출 합계"를 구했지만, "제조사의 남/녀 성별 매출 합계"처럼 보고서의 구조 자체를 바꿔야 한다면 작성에 적지 않은 추가 작업이 필요합니다.

   

4. 매출 합계를 평균으로 바꿔야 하는 경우에도, 보고서에 사용된 함수를 모두 수정해야 할 뿐만 아니라 수식 한 줄만 잘못 입력해도 전체 결과에 영향을 주기 때문에 세심한 주의가 필요합니다.

   

5. 이런 상황에서 피벗테이블을 사용하면, 보고서 구조를 손쉽게 변경하고 다양한 집계 방식으로 데이터를 빠르게 분석할 수 있어 작업 효율이 크게 올라갑니다.

   

피벗테이블, 십자가(┿)만 기억하세요!

피벗테이블의 사용법은 매우 간단합니다. 피벗테이블 필드 목록의 왼쪽 상단을 기준으로 십자가(┿) 모양을 떠올린 뒤, 분석하고 싶은 필드를 행·열·값 영역으로 끌어다 놓기만 하면 보고서가 완성됩니다.

만약 피벗테이블 사용이 아직 익숙하지 않다면, 엑셀이 데이터 구조를 분석해 자동으로 제안해주는 '추천 피벗테이블' 기능을 활용하는 것도 좋은 방법입니다. 분석할 원본 데이터 범위를 선택한 후 [삽입] 탭 - [추천 피벗테이블] 버튼을 클릭하면, 아래 그림과 같이 권장 피벗테이블 목록이 표시되며 [새 시트] 또는 [기존 시트]를 선택해 손쉽게 피벗테이블을 만들 수 있습니다.

왕초보를 위한 피벗테이블 기본 사용법

1. 원클릭 합계/평균/개수 계산법 : 분석할 필드를 피벗테이블 위에 모두 배치했다면, 이제 기본 기능만으로도 데이터를 다양한 관점에서 빠르게 분석할 수 있습니다. 예제파일에서 [피벗테이블_활용] 시트로 이동한 후, 아래 그림과 같이 [행: 제조사], [열: 결제방식], [값: 금액]을 배치해 피벗테이블을 생성합니다. 이후 값 영역의 숫자에 천 단위 구분 기호를 적용해 보고서를 마무리합니다.

   

2. 피벗테이블의 값 영역을 우클릭한 후 [값 요약 기준]을 선택하면, 실무에서 자주 사용하는 '합계', '평균', '개수', '최댓값', '최솟값'으로 손쉽게 집계 방식을 전환할 수 있습니다. 또는 [기타 옵션]을 선택하면 '숫자 개수', '표준 편차' 등 더욱 다양한 통계 방식으로도 집계가 가능합니다.

   

   오빠두Tip : 피벗테이블의 기본 집계 방식은 숫자 데이터의 경우 '합계', 문자 데이터의 경우 '개수'로 설정됩니다. 합계로 집계된 모든 필드를 한 번에 평균으로 변경해야 한다면, 아래 1분 영상 강의에서 소개한 방법을 참고해 보세요.
   
3. 합계+점유율 동시 표시하기 : 피벗테이블에서 합계와 점유율을 동시에 보여주는 방법은 매우 간단합니다. 값 영역에 동일한 항목을 한 번 더 추가한 뒤 표시 형식만 바꿔주면 됩니다. 앞서 만든 피벗테이블에서 아래 그림처럼 값 영역으로 [금액] 필드를 한 번 더 추가하면, 같은 값으로 합계가 두 번 집계됩니다.

   

4. 새로 추가한 금액 필드를 우클릭한 후 [값 표시 형식]을 선택하고 '총 합계 비율' 또는 '열 합계 비율', '행 합계 비율' 가운데 보고서에 적합한 항목으로 변경하면, 합계와 점유율을 한 보고서에 함께 표시할 수 있습니다.

   

5. 마지막으로 피벗테이블의 머리글을 선택한 뒤, 수식 입력줄을 클릭하거나 F2 키를 눌러 머리글의 값을 '점유율'로 변경하면, 합계와 점유율이 함께 표시되는 피벗테이블 보고서가 완성됩니다.

   

6. 특정 항목 필터링 결과 표시하기 : 피벗테이블의 [필터 영역]을 활용하면 특정 항목만 필터링해 보고서를 출력할 수 있습니다. 앞서 완성한 피벗테이블에서 필터 영역으로 '지역' 필드를 추가하면, 피벗테이블 위쪽에 '지역 필터'가 새로 표시됩니다.

   

7. 필터의 화살표 버튼을 클릭한 뒤 원하는 지역을 선택하면, 피벗테이블 보고서가 즉시 갱신됩니다. 여러 지역을 동시에 선택해야 한다면, 필터 옵션에서 '여러 항목 선택'을 활성화한 후 원하는 항목을 다중 선택합니다.

   

   오빠두Tip : 피벗테이블의 필터 영역은 '텍스트 필터'로만 동작합니다. 따라서 'xx 보다 큰 값'과 같은 숫자 조건으로는 필터를 적용할 수 없으니 이 점을 주의해 사용하세요.

보고서 가독성을 높이는 피벗테이블 기본 설정

피벗테이블로 보고서를 작성할 때, 몇 가지 간단한 설정만 변경해도 보고서의 가독성을 크게 향상시킬 수 있습니다. 이번 포스트에서는 핵심 내용만 간추려 정리했으며, 자세한 설명은 영상 강의를 참고해 주세요.

1. 셀 서식 활용하기 : "셀 서식"은 가독성 좋은 엑셀 보고서를 만들기 위해 반드시 알아두어야 할 핵심 규칙입니다. 실무에서는 "#,##0"과 세미콜론(;) 두 가지 서식만 익혀도 대부분의 상황에서 충분히 활용할 수 있습니다.
   · #,##0 : 천 단위 구분 기호를 포함한 정수 표시
   · 세미콜론(;) : 양수;음수;0;텍스트 순서로 서식을 구분
2. 예를 들어, 아래 그림과 같은 피벗테이블에서 셀 서식으로 "#,##0,"을 적용하면 천 단위를 절사한 형태로 더욱 깔끔하게 보고서를 정리할 수 있습니다.

   

   오빠두Tip : 직장인이 꼭 익혀두어야 할 셀 서식 핵심 규칙은 아래 강의에서 자세히 정리했으니 함께 참고하세요.
   
3. 우선 순위로 정렬하기 : 피벗테이블로 작성한 보고서는 기본적으로 '행 레이블'을 기준으로 오름차순 정렬됩니다.
4. 따라서 보고서 가독성을 높이기 위해서는 "보고서를 받는 사람의 입장"에서 중요한 항목이 위로 오도록 우선순위에 따라 정렬하는 것이 좋습니다. 정렬을 변경하려면 행 레이블의 필터 버튼을 클릭한 후, [기타 정렬 옵션] → [정렬 옵션]에서 원하는 기준을 선택해 정렬 순서를 조정합니다.

   

5. 보고서 레이아웃 변경하기 : 피벗테이블의 기본 레이아웃은 '압축형'으로 설정되어 있습니다. 이 상태에서 아래 그림처럼 행 영역에 여러 필드를 추가하면, 모든 필드가 한 열에 겹쳐 표시되어 가독성이 떨어지는 문제가 발생합니다.

   

6. 이런 경우 피벗테이블을 선택한 후 [디자인] 탭 → [보고서 레이아웃] → [테이블 형식]으로 변경하면, 각 필드가 별도 열로 구분되어 가독성이 한층 좋아집니다.
   
   오빠두Tip : 피벗테이블 보고서 레이아웃 설정에 대한 자세한 설명은 아래 5분 기초 영상 강의에서 함께 다룹니다.
   

피벗테이블 활용도를 2배 올려주는 슬라이서 사용법

실무에서 피벗테이블이 사랑받는 이유 중 하나는 보고서 구조와 집계 방식을 손쉽게 바꿔 데이터를 자유롭게 분석할 수 있다는 점입니다. 여기에 더해, 피벗테이블을 '슬라이서'와 함께 사용하면 클릭 한 번으로 데이터를 실시간으로 필터링할 수 있어 활용도가 2배 이상으로 올라갑니다.

1. 슬라이서 추가하기 : 피벗테이블을 선택한 후 [피벗테이블 분석] → [슬라이서 삽입] 버튼을 클릭합니다. [슬라이서 삽입] 대화상자가 나타나면, 슬라이서로 추가할 필드를 선택한 뒤 [확인] 버튼을 클릭해 슬라이서를 생성합니다. 이번 예제에서는 "등급, 성별, 제조사" 세 가지 필드를 슬라이서로 추가했습니다.
   
2. 슬라이서의 크기와 위치를 적절히 조정한 다음 버튼을 클릭하면, 피벗테이블이 실시간으로 필터링되어 결과가 즉시 갱신됩니다.
3. 슬라이서 꾸미기 : 슬라이서 설정에서는 버튼의 개수와 보고서 스타일을 자유롭게 변경할 수 있습니다. 슬라이서를 선택한 뒤 [단추] 그룹 → [열] 항목에서 한 줄에 표시되는 버튼 개수를 조정합니다.
   
4. 오빠두엑셀 홈페이지에서 제공하는 엑셀 스타일 시트를 활용하면, 아래와 같이 슬라이서 디자인을 보다 깔끔하게 꾸밀 수 있습니다. 스타일 시트 사용 방법은 아래 영상 강의에서 단계별로 정리했으니 참고하세요.
   

   

5. 여러 보고서를 동시에 필터링하기 : 슬라이서를 활용하면 한 번의 조작으로 여러 피벗테이블 보고서를 동시에 필터링할 수도 있습니다. 슬라이서를 우클릭한 뒤 [보고서 연결]을 선택하면, 현재 슬라이서와 동일한 원본 데이터로 만들어진 피벗테이블 목록이 표시됩니다. [보고서 연결] 창에서 슬라이서와 연결할 피벗테이블을 모두 체크한 뒤 [확인] 버튼을 클릭하면, 선택된 모든 피벗테이블이 한 번에 필터링됩니다.

   

   오빠두Tip : 보고서 연결은 "원본 데이터가 동일한 피벗테이블"에만 적용된다는 점을 반드시 기억하세요. 서로 다른 데이터 원본을 사용하는 피벗테이블끼리는 슬라이서로 함께 필터링할 수 없습니다.

업무시간을 줄여주는 피벗테이블 실전 사용법

지금까지 실무에서 꼭 알아야 할 피벗테이블 기초 사용법을 살펴봤습니다. 이제부터는 본격적으로 업무시간을 단축하는 데 도움이 되는 피벗테이블 실전 활용법을 하나씩 정리해 보겠습니다.

1. 누적 데이터 관리: 표 기능 : 실무에서는 대부분의 데이터가 매일 누적되어 늘어납니다. 이때 피벗테이블의 원본 데이터를 단순한 셀 범위(예: A1:C100)로 지정해 두면, 새로운 데이터가 추가될 때마다 매번 범위를 다시 설정해야 하는 번거로움이 생깁니다. 이런 문제를 해결하기 위해 원본 범위를 '표(Table)'로 변환하면, 새로 추가되는 데이터를 자동으로 인식하는 피벗테이블을 만들 수 있습니다. 예제파일에서 [피벗테이블_활용] 시트로 이동한 후, 원본 데이터에서 임의의 셀을 선택하고 Ctrl + A 를 눌러 전체 데이터를 선택합니다. 이후 [삽입] 탭 - [표] 버튼을 클릭하거나 Ctrl + T 를 눌러 표 만들기 대화상자를 실행합니다.
   
2. '머리글 포함' 옵션을 체크한 후 [확인] 버튼을 클릭하면, 선택한 범위가 표로 변환됩니다. 변환된 표를 선택하고 [테이블 디자인] 탭에서 왼쪽의 [표 이름] 항목을 변경합니다. 이번 예제에서는 표 이름을 "매출현황"으로 지정합니다.
   
3. 표를 선택한 상태에서 [삽입] → [피벗테이블]을 선택하면, 방금 이름을 지정한 '매출현황' 표를 데이터 원본으로 인식해 피벗테이블을 만들 수 있습니다. '새 워크시트'를 선택한 뒤 [확인] 버튼을 클릭해 새 시트에 피벗테이블을 생성합니다.

   

4. 다시 [피벗테이블_활용] 시트로 돌아간 다음, 기존 데이터 아래쪽에 새로운 행을 입력하면 표가 자동으로 확장되어 새 데이터를 함께 인식합니다.

   

5. 데이터 추가가 끝나면 피벗테이블 시트로 돌아와, 피벗테이블을 우클릭한 뒤 [새로 고침]을 선택해 새로 추가된 데이터를 포함한 결과로 갱신합니다.
   
6. 시트별로 한 번에 나누기 : 피벗테이블을 활용하면 보고서를 항목별로 여러 시트에 자동 분리하여 작성할 수도 있습니다. 이번 예제에서는 "지역별 피벗테이블 보고서"를 각 제조사 단위 시트로 나누어 만들어 보겠습니다. 아래 그림과 같이 [행: 지역], [값: 금액]으로 피벗테이블을 만든 뒤, 필터 영역으로 '제조사' 필드를 이동합니다.

   

7. 피벗테이블을 선택한 후 [피벗테이블 분석] 탭 → [옵션] → [보고서 필터 페이지 표시]를 선택하면, 필터 영역에 추가한 필드의 각 항목별로 시트가 자동 생성되어 보고서가 분리됩니다.

   

8. [보고서 필터 페이지 표시] 대화상자가 실행되면 '제조사'를 선택한 후 [확인] 버튼을 클릭합니다. 그러면 각 제조사별로 시트가 분리된 피벗테이블 보고서가 한 번에 완성됩니다.
   
9. 구간/그룹별 분석하기 : 피벗테이블의 그룹 기능을 활용하면, 숫자나 날짜로 입력된 데이터를 일정 구간 단위로 묶어 분석할 수 있습니다. 아래 그림과 같이 [행: 나이], [값: 금액]으로 피벗테이블을 생성합니다.

   

10. 생성된 피벗테이블에서 나이 필드를 우클릭한 뒤 [그룹]을 선택합니다. [그룹화] 대화상자가 실행되면 시작값, 끝값, 단위 항목을 보고서 목적에 맞게 적절히 변경합니다.
    
11. 마지막으로 [확인] 버튼을 클릭하면 나이대별로 그룹화된 피벗테이블 보고서가 완성됩니다.
    
    오빠두Tip : 그룹 기능은 숫자뿐만 아니라 날짜 데이터에서도 유용하게 사용할 수 있습니다. 피벗테이블의 주 단위 집계 방법은 아래 1분 영상 강의에서 짧고 빠르게 정리했습니다.