엑셀 피벗테이블, 실무자를 위한 핵심 기능 총정리
엑셀 피벗테이블 기초 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [기초 입문] 엑셀 피벗테이블, 실무자를 위한 기초 총정리 특강예제파일
라이브 강의 전체영상도 함께 확인해보세요!
위캔두 멤버십 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.
함수 vs 피벗테이블: 실무에서 피벗테이블이 중요한 이유
엑셀에서 함수는 대부분의 직장인에게 매우 중요한 기능으로 여겨집니다. 그러나 실무에서 작성하는 대부분의 보고서는 주로 간단한 집계를 필요로 하는데, 이런 경우 함수보다는 피벗테이블을 사용하는 것이 더 효율적인데요. 간단한 예제와 함께 그 이유를 확인해보겠습니다.
오빠두Tip : 피벗테이블의 기능을 100% 활용하려면, 올바른 구조로 데이터를 관리하는 것이 무엇보다 중요합니다. 올바른 데이터 관리 규칙에 대한 자세한 설명은 아래 기초 영상 강의를 참고하세요!
- 함수로 집계해보기 : 예제파일에서 [간단예제_함수] 시트로 이동한 후, 작성된 데이터에서 각 제조사별 금액의 매출 합계를 집계해보겠습니다. I1셀에 "제조사"를 작성한 후, I2셀에 아래와 같이 UNIQUE 함수를 입력해서 제조사의 고유값 목록을 구합니다.
=UNIQUE(E2:E25)
UNIQUE 함수로 제조사의 고유 목록을 생성합니다. 오빠두Tip : 엑셀 2019 이전 버전 사용자는 [데이터] 탭 - [중복된 항목 제거] 기능으로 제조사의 고유값 목록을 구할 수 있습니다. 자세한 방법은 영상 강의를 참고하세요! - 이제 각 제조사의 매출 합계를 계산합니다. J1셀에 "매출 합계"를 작성한 후, 아래와 같이 SUMIF 함수를 작성해서 각 제조사별 매출 합계를 계산합니다. 마지막으로 계산된 범위를 선택한 후, [홈] 탭 - [쉼표] 버튼을 클릭하거나 단축키 Ctrl + Shift + 1 을 눌러 숫자에 천단위 구분 기호를 추가하면 보고서가 완성됩니다.
=SUMIF(E1:E25,I2#,G1:G25)
각 제조사의 매출 합계를 SUMIF 함수로 집계합니다. 오빠두Tip : 엑셀2021부터 새롭게 추가된 분산범위와 해시(#)기호를 활용하는 방법은 아래 기초 영상강의를 참고하세요!
- 함수로 작성한 보고서의 한계 : 이와 같이 일회성으로 보고서를 작성하는 경우에는, 함수로 편리하게 보고서를 작성할 수 있습니다. 하지만 작성한 보고서의 구조를 변경하거나, 합계 대신 평균을 집계해야 할 경우에는 어떻게 해야 할까요? 예를 들어, 지금은 "제조사의 매출 합계"를 구했지만, "제조사의 남/녀 성별 매출 합계"로 보고서의 구조를 변경해야 한다면 상당한 작업이 필요할 수 있습니다.
함수로 작성한 보고서는 구조를 변경하기 매우 어렵습니다. - 또한 매출 합계를 평균으로 바꿔야 한다면, 보고서에 사용한 함수를 모두 수정해야 하는 것 뿐만 아니라 수식이 하나라도 잘못되면 전체 결과값에 영향을 줄 수 있어 상당한 주의가 필요합니다.
또한 집계 방식을 변경하려면 함수를 하나씩 수정해야 하는 번거로움이 있습니다. - 이럴 때 피벗테이블을 사용하면, 보고서의 구조를 손쉽게 변경하고 다양한 집계 방식으로 편리하게 데이터를 분석할 수 있습니다.
피벗테이블을 사용하면 보고서 구조 및 집계 방식을 편리하게 변경할 수 있습니다.
피벗테이블, 십자가(┿)만 기억하세요!
피벗테이블의 사용법은 아주 간단합니다. 피벗테이블의 왼쪽 상단을 기준으로 십자가(┿)를 그린 후, 각각의 행/열/값 영역으로 보고 싶은 필드를 이동하면 피벗테이블이 완성됩니다.
오빠두Tip : 피벗테이블 십자가 규칙에 대한 자세한 설명은 아래 5분 기초 영상 강의를 참고하세요!
만약, 아직 피벗테이블을 사용하는 것이 익숙하지 않을 경우, 엑셀이 데이터를 자동으로 분석해주는 '추천 피벗테이블'을 사용해도 편리합니다. 피벗테이블로 분석할 원본 데이터 범위를 선택한 후, [삽입] 탭 - [추천 피벗테이블] 버튼을 클릭하면, 아래 그림과 같이 권장 피벗테이블 목록이 표시되고 [새 시트] 또는 [기존 시트]를 선택해서 피벗테이블을 손쉽게 만들 수 있습니다.
추천 피벗테이블을 사용하면 초보자도 편리하게 피벗테이블을 사용할 수 있습니다. 왕초보를 위한 피벗테이블 기본 사용법
- 원클릭 합계/평균/개수 계산법 : 분석할 필드를 피벗테이블 위에 모두 배치하였으면, 이제 피벗테이블의 기본 기능을 사용하여 편리하게 데이터를 분석할 수 있습니다. 예제파일에서 [피벗테이블_활용] 시트로 이동한 후, 아래 그림과 같이 [행: 제조사], [열: 결제방식], [값: 금액]을 추가하여 피벗테이블을 추가합니다. 피벗테이블을 추가한 후, 값 영역의 숫자에는 천 단위 구분기호를 추가하여 보고서를 완성합니다.
그림과 같이 데이터 분석을 위한 기본 피벗테이블을 만듭니다. - 피벗테이블의 값 영역을 우클릭한 후, [값 요약 기준]을 선택하면 실무에서 자주 사용하는 '합계', '평균', '개수', '최대값', '최소값' 으로 집계할 수 있습니다. 또는 [기타 옵션]을 선택한 후, '숫자 개수' 또는 '표준 편차' 등 다양한 방식으로 집계하는 것도 가능합니다.
피벗테이블에서 값을 우클릭 - 값 요약기준에서 집계 방식을 변경할 수 있습니다. 오빠두Tip : 피벗테이블의 기본 집계 방식은 '합계'이며, 문자 데이터일 경우 '개수'로 집계합니다. 만약 합계로 집계한 필드를 '평균'으로 모두 바꿔야 할 경우, 아래 1분 영상 강의에서 소개해드린 방법을 사용해보세요!
- 합계+점유율 동시 표시하기 : 피벗테이블에서 합계와 점유율을 동시에 표시하는 방법은 아주 간단합니다. 값 영역에 동일한 항목을 여러개 추가한 후, 각 표시형식을 변경하면 됩니다. 이전에 만든 피벗테이블에서, 아래 그림과 같이 값 영역으로 [금액] 필드를 하나 더 추가하면 동일한 값으로 합계가 두 번 집계됩니다.
값 영역에 동일한 필드를 여러개 추가할 수 있습니다. - 방금 추가한 금액 필드를 우클릭한 후, [값 표시 형식]을 선택하고 '총 합계 비율' 또는 '열 합계 비율', '행 합계 비율'로 적절히 변경하면 합계와 점유율을 동시에 표시할 수 있습니다.
피벗테이블에서 값을 우클릭 - 값 표시형식으로 비율을 선택할 수 있습니다. - 피벗테이블의 머리글을 선택한 후, 수식 입력줄을 선택하거나 F2 키를 눌러서 머리글의 값을 '점유율'로 변경하면 합계 + 점유율을 동시에 표시하는 피벗테이블이 완성됩니다.
피벗테이블에서 머리글을 선택한 후, F2키를 누르거나 수식입력줄에서 머리글을 변경합니다. - 특정 항목 필터링 결과 표시하기 : 피벗테이블의 [필터 영역]을 활용하면 특정 항목의 필터링 결과를 출력할 수 있습니다. 방금 만든 피벗테이블에서 필터 영역으로 '지역' 필드를 추가하면 피벗테이블 위로 '지역 필드'가 추가됩니다.
피벗테이블의 필터 영역으로 지역 필드를 추가합니다. - 필터의 화살표 버튼을 클릭한 후, 원하는 지역을 선택하면 피벗테이블 보고서가 업데이트 됩니다. 만약 여러 지역을 동시에 선택할 경우, 필터 옵션에서 '여러 항목 선택'을 활성화합니다.
필터를 확장한 후, '여러 항목 선택'을 체크하면 여러 항목으로 필터를 적용할 수 있습니다. 오빠두Tip : 피벗테이블의 필터 영역은 '텍스트 필터'로만 동작합니다. 따라서 'xx 보다 큰 값'과 같은 조건으로 필터를 적용할 수 없는 점을 주의하세요!
보고서 가독성을 높이는 피벗테이블 기본 설정
피벗테이블로 보고서를 작성할 때, 몇 가지 간단한 설정을 변경하면 보고서의 가독성을 크게 향상시킬 수 있습니다. 이번 포스트에서는 핵심 내용만 정리하였습니다. 자세한 내용은 영상 강의를 참고해주세요!
- 셀 서식 활용하기 : "셀 서식"은 엑셀로 가독성 좋은 보고서를 작성하려면 반드시 알아야 할 핵심 규칙입니다. 실무에서는 "#,##0" 과 "세미콜론(;)" 2가지를 기억하면 대부분의 상황에 활용할 수 있습니다.
· #,##0 : 회계점(천단위 구분기호)를 포함한 정수를 표시
· 세미콜론(;) : 양수;음수;0;텍스트 서식을 구분 - 예를 들어, 아래 그림과 같은 피벗테이블에서 셀 서식으로 "#,##0,"을 적용하면 천단위를 절사한 값으로 더욱 깔끔한 보고서를 만들 수 있습니다.
셀 서식을 사용하면 보고서의 가독성을 크게 높일 수 있습니다. 오빠두Tip : 직장인이 꼭 알아야 할 셀 서식 핵심 규칙은 아래 강의에서 꼼꼼히 정리했습니다.
- 우선 순위로 정렬하기 : 피벗테이블로 작성한 보고서는 기본적으로 '행 레이블'의 오름차순으로 정렬됩니다.
- 따라서, 보고서의 가독성을 높이기 위해, "보고서를 받는 사람의 입장"에서 우선순위에 따라 정렬하는 것이 중요합니다. 정렬을 변경하려면 필터 버튼을 클릭한 후, [기타 정렬 옵션] → [정렬 옵션]으로 이동해서 정렬 순서를 변경합니다.
피벗테이블의 레이블 필터 - 기타 정렬 옵션에서 정렬 기준을 변경할 수 있습니다. - 보고서 레이아웃 변경하기 : 피벗테이블의 기본 레이아웃은 '압축형'입니다. 따라서, 아래 그림과 같이 '행 영역'으로 여러 개의 필드를 추가하면, 모든 필드가 한 줄로 작성되어 가독성이 떨어지는 문제가 있습니다.
피벗테이블의 기본 레이아웃은 압축형으로 모든 필드가 한 열에 세로로 길게 표시됩니다. - 그럴 경우, 피벗테이블 선택 → [디자인] 탭 → [보고서 레이아웃] → [테이블 형식]으로 변경하면 가독성을 크게 향상시킬 수 있습니다.
피벗테이블을 선택한 후, [디자인] - [보고서 레이아웃]에서 테이블 형식으로 변경합니다. 오빠두Tip : 피벗테이블 보고서 레이아웃 설정에 대한 더욱 자세한 설명은 아래 5분 기초 영상 강의를 참고하세요!
피벗테이블 활용도를 2배 올려주는 슬라이서 사용법
실무에서 피벗테이블이 사랑받는 이유 중 하나는, 보고서의 구조와 집계 방식을 손쉽게 변경하여 데이터 분석에 용이하기 때문입니다. 이와 더불어, 피벗테이블을 '슬라이서'와 함께 사용하면 실시간으로 데이터를 분석할 수 있어 2배 이상으로 활용할 수 있습니다.
- 슬라이서 추가하기 : 피벗테이블을 선택한 후, [피벗테이블 분석] → [슬라이서 삽입] 버튼을 클릭합니다. [슬라이서 삽입] 대화상자가 실행되면, 슬라이서로 추가할 필드를 선택한 후 [확인] 버튼을 클릭해서 슬라이서를 추가합니다. 이번 예제에서는 "등급, 성별, 제조사" 슬라이서를 추가했습니다.
피벗테이블 선택 - [피벗테이블 분석] - [슬라이서 삽입]에서 슬라이서를 추가합니다. - 슬라이서의 크기와 위치를 적절히 변경한 후, 버튼을 클릭하면 피벗테이블이 실시간으로 필터링됩니다.
- 슬라이서 꾸미기 : 슬라이서 설정에서 버튼의 개수와 보고서의 스타일을 변경할 수 있습니다. 슬라이서를 선택한 후, [단추] 그룹 → [열] 에서 한 줄에 들어가는 버튼의 개수를 변경합니다.
슬라이서를 추가한 후, [슬라이서] 탭 - [열] 에서 한 줄에 들어갈 버튼 개수를 변경합니다. - 오빠두엑셀 홈페이지에서 제공하는 엑셀 스타일 시트를 활용하면 아래와 같이 슬라이서 디자인을 편리하게 꾸밀 수 있습니다. 스타일 시트 사용방법은 아래 영상 강의를 참고하세요.👇
스타일 시트를 사용하면 슬라이서 디자인을 편리하게 적용할 수 있습니다. - 여러 보고서를 동시에 필터링하기 : 슬라이서로 여러개의 피벗테이블 보고서를 필터링 할 수도 있습니다. 슬라이서를 우클릭 → [보고서 연결]을 선택하면, 현재 슬라이서와 연결된 피벗테이블과 동일한 원본데이터로 만들어진 피벗테이블 목록이 표시됩니다. [보고서 연결]에서 슬라이서와 연결할 피벗테이블을 모두 선택한 후 [확인] 버튼을 클릭하면, 여러개의 피벗테이블이 동시에 필터링됩니다.
슬라이서를 우클릭 - 보고서 연결로 여러 피벗테이블을 동시에 필터링할 수 있습니다. 오빠두Tip : 보고서 연결은 반드시 "원본데이터가 동일한 피벗테이블"에만 적용된다는 점을 주의하세요!
업무시간을 줄여주는 피벗테이블 실전 사용법
실무에서 꼭 필요한 피벗테이블의 기초 사용법을 모두 알아봤습니다! 이제 본격적으로 업무시간을 크게 줄이는데 도움이 되는 피벗테이블 실전 사용법을 하나씩 알아보겠습니다.
- 누적 데이터 관리: 표기능 : 현업에서는 대부분의 데이터가 매일 누적됩니다. 따라서, 피벗테이블의 원본 데이터를 일반 범위(예: A1:C100)로 설정하면, 새로운 데이터가 추가될 때마다 범위를 변경해야 합니다. 이런 번거로움을 줄기 위해 범위를 표로 변경하면, 새로운 데이터를 자동으로 인식하는 피벗테이블을 만들 수 있습니다. 예제파일에서 [피벗테이블_활용] 시트로 이동한 후, 원본데이터에서 임의의 셀을 선택하고 Ctrl + A 를 눌러 모든 데이터를 선택합니다. 이후 [삽입] 탭 - [표] 버튼을 클릭하거나 Ctrl + T 를 동시에 눌러 표 만들기 대화상자를 실행합니다.
범위 선택 - [삽입] - [표]를 선택하거나 단축키 Ctrl + T 를 눌러 범위를 표로 변환합니다. - '머리글 포함'을 체크한 후, [확인] 버튼을 클릭하면 범위가 표로 변환됩니다. 표를 선택하고 [테이블 디자인] 탭을 선택한 후, 왼쪽에서 표 이름을 변경합니다. 이번에는 "매출현황"으로 변경하겠습니다.
표 선택 - [테이블 디자인] - [표 이름]에서 표의 이름을 변경합니다. - 표를 선택한 후, [삽입] → [피벗테이블]을 선택하면 방금 변경한 '매출현황' 표를 인식하여 피벗테이블을 만들 수 있습니다. '새 워크시트'를 선택한 후, [확인] 버튼을 클릭해서 새 시트에 피벗테이블을 생성합니다.
표를 선택한 후, 새 워크시트에 피벗테이블을 추가합니다. - 다시 [피벗테이블_활용] 시트로 이동한 후, 아래에 새로운 데이터를 추가하면 표가 확장됩니다.
표에 새로운 데이터를 추가하면 표가 자동으로 확장됩니다. - 데이터를 추가한 후, 피벗테이블 시트로 돌아와서 피벗테이블을 우클릭 → [새로고침]을 선택하면 새로운 데이터를 포함하여 피벗테이블이 갱신됩니다.
표를 참조하여 추가한 피벗테이블을 우클릭 - [새고로침]을 클릭하면 새 데이터가 피벗테이블에 반영됩니다. - 시트별로 한 번에 나누기 : 피벗테이블을 활용하면 각 항목별 피벗테이블 보고서를 여러개의 시트로 나누어 편리하게 작성할 수 있습니다. 이번 예제에서는 "지역별 피벗테이블 보고서" 각 제조사마다 시트로 나누어 보고서를 작성해보겠습니다. 아래 그림과 같이 [행: 지역], [값: 금액]으로 피벗테이블을 만든 후, 필터 영역으로 제조사 필드를 이동합니다.
피벗테이블의 필터 영역으로 제조사 필드를 추가합니다. - 피벗테이블을 선택한 후, [피벗테이블 분석] 탭 → [옵션] → [보고서 필터 페이지 표시]를 선택하면 필터 영역에 추가된 필드의 각 항목으로 시트를 나누어 보고서를 만들 수 있습니다.
피벗테이블 분석 - 옵션 - 보고서 필터 페이지 표시를 클릭합니다. - [보고서 필터 페이지 표시] 대화상자가 실행되면, '제조사'를 선택한 후 [확인] 버튼을 클릭합니다. 그러면 각 시트별로 나뉘어진 제조사별 피벗테이블 보고서가 한 번에 완성됩니다.
'제조사'를 선택 후 [확인]을 클릭하면 각 제조사별 피벗테이블이 여러 시트로 나뉘어 완성됩니다. - 구간/그룹별 분석하기 : 피벗테이블의 그룹 기능을 사용하면 숫자 및 날짜로 작성된 데이터를 각 구간으로 나누어 편리하게 분석할 수 있습니다. 아래 그림과 같이 [행: 나이], [값: 금액]으로 피벗테이블을 생성합니다.
나이별 매출 현황을 분석하는 피벗테이블을 추가합니다. - 피벗테이블에서 나이 필드를 우클릭 → [그룹]을 선택합니다. [그룹화] 대화상자가 실행되면 시작, 끝, 단위의 값을 적절하게 변경합니다.
피벗테이블에서 [나이] 행을 우클릭 - [그룹]을 선택하면 그룹화 대화상자가 실행됩니다. - 마지막으로 [확인] 버튼을 클릭하면 나이대별로 그룹화된 피벗테이블 보고서가 완성됩니다.
그룹화에서 각 값을 적절히 작성 후, [확인] 버튼을 클릭하면 연령대별 피벗테이블 보고서가 완성됩니다. 오빠두Tip : 이 외에도 그룹기능을 사용하면 날짜별 주 단위 데이터 집계를 편리하게 할 수 있습니다. 피벗테이블 주 단위 집계는 아래 1분 영상 강의로 정리했습니다.
- 함수로 집계해보기 : 예제파일에서 [간단예제_함수] 시트로 이동한 후, 작성된 데이터에서 각 제조사별 금액의 매출 합계를 집계해보겠습니다. I1셀에 "제조사"를 작성한 후, I2셀에 아래와 같이 UNIQUE 함수를 입력해서 제조사의 고유값 목록을 구합니다.
