엑셀의 꽃 피벗테이블 사용법(초보용 PIVOT)

엑셀의 기본 피벗에 대해 알아보고자 합니다.

엑셀의 많은 기능이 있지만 데이타를 분석하는 사람들에게 가장 활용도가 높은 기능은 피벗이라고 생각합니다.

몇 번의 클릭과 드래그 만으로도 내가 원하는 형태의 자료를 취할 수 있습니다.

피벗 조금만 알면 업무 시간을 확 줄일 수 있습니다.
(업무가 너무 빨리 끝나서 추가 업무를 받지 않도록 주의해야 합니다~)

제가 피벗으로 활용하는 주요 업무는 아래와 같습니다.

1) 데이타를 다운 받아 간단하게 리포트화
2) 특이사항 점검
3) 마스터 데이타 작성(하이라키-계층구조)
4) 시점별 데이타 분석
5) Raw Data 다시 만들기

업무마다 데이타마다 차이가 있지만 피벗을 이용해 유용하게 처리하는 작업들 입니다.

피벗이란 사전적 의미는 '중심축'으로 열과 행 축의 정보와 일치하는 데이타를 함축하여 보여 줍니다.

1. 피벗 실행하기

피벗은 표가 있어야 합니다.
원하는 표에서 범위를 선택 한 후 ▶ 삽입 탭 클릭 ▶ 피벗테이블 클릭 ▶ 새로운 창(피벗 테이블 만들기)에서 확인 을 눌러 주시면 됩니다.
(표 옆에 피벗 테이블을 만들고 싶으시면 피벗 테이블 만들기에서 - 피벗테이블 보고서를 넣을 위치 - 기존 워크시트를 선택하여 옆에 빈 셀을 눌러주시면 됩니다.)

피벗 실행하기

2. 피벗 테이블 셋팅

확인을 누르면 아래와 같은 '피벗 테이블' 기본 창이 생성되고 창을 누르게 되면 '피벗 테이블 필드'가 생성 됩니다.
원하는 값을 아래 '필터', '열', '행', '값'에 갔다 놓으면 오른쪽과 같이 데이타가 표시 됩니다.

피벗 테이블 세팅

3. 피벗 데이타 활용

피벗이 어렵게 느껴지는 분들은 먼저, 숫자의 값(판매수량, 매출실적 등)을 원하실 경우 최종적으로 얻고자 하는 필드를 '값' 영역에 넣고 필드를 바꿔가면서 다양한 형태의 리포트를 계속 만들다 보면 가장 적합한 리포트를 얻을 수 있습니다.
여기에서 피벗의 강점이 나타나는데, 값과 리포트 양식을 을 바로바로 확인 할 수 있기 때문에 시간이 많이 절약 됩니다.
저는 피벗을 통해서 보고서에 삽입 할 표의 양식 아이디어를 얻는 편입니다.
필드가 많을 경우 생각하는 것 보다 직접 해보는게 더 빠르게 결정 할 수 있습니다.

피벗 테이블 활용 예시

4. 테이블 형식 변경

위 그림의 (4)번의 경우 2개의 필드가 같은 라인에 있어서 보기 불편합니다.
이런 경우에 테이블 형식으로 바꿔 주면 보기 좋은 리포트로 변경 됩니다.
참고로, 디자인 탭에서 원하는 간단하게 원하는 레이아웃으로 변경 가능하오니 다른 버튼도 눌러보셨으면 합니다.

피벗테이블 양식 변경

5. 모든 항목 레이블 반복

모든 항목 레이블 반복이 항상 필요한 건 아닙니다. Raw Data를 다시 만들거나 필드 구분이 어려울 경우에 유용한 기능이라고 생각됩니다.
(저의 경우는 대부분 레이블 반복을 선택합니다. 데이타간 구분이 편하고, 다른 데이타와 취합 시에도 필요합니다.)

레이블 반복 1

*참고로 테이블에서 오른쪽 마우스를 눌러서도 5, 6번 작업이 가능합니다.

 대신에 가장 처음에 있는 필드(아래 그림에서는 A마트)를 선택 후 오른쪽 마우스 클릭하여야 테이블형식과 레이블 반복 효과가 적용이 됩니다. 하위 필드(아래 그림의 중간에 있는 고등어)를 선택하게 되면 선택 필드의 하위 단에만 양식 변경이 적용 됩니다.

레이블 반복 2

6. 피벗 테이블 팁

1) 1열에 필드값을 지정하고 표범위 셋팅 시 문자열(아래 오른쪽 그림 B~H)로 셋팅을 하게 되면 향후 raw data를 아래에 추가 시 피봇 테이블의 범위를 다시 지정해주지 않아도 되어 편리 합니다.

피벗 테이블 범위 선택 팁

2) 필드값에 이름이 없으면 아래와 같은 오류가 발생합니다. 동일한 필드명이 있을 경우 필드명 뒤에 자동으로 숫자가 넘버링 되며(이름1, 이름2, 이름3 등) 필드테이블 내에서는 동일 이름이 불가능 합니다.

피벗테이블 오류

3) raw data에 새로운 자료가 추가 되거나, 아래와 같이 테이블 형식이 약간 다른 경우 기존 필드 아래에 추가를 해준다면 손쉽게 기존 자료와 새로운 자료를 합칠 수 있다.
업무를 하다보면 다른 곳의 자료를 하나로 뭉쳐 비교해야 하는 경우가 많다. 이 때 'key값' 이라는 개념을 기억하고 있으면 서로 다른 자료를 연결하여 정확하게

피벗테이블 확장 팁

 

4) 피벗테이블을 여러 가지 만들어서 운영이 가능합니다. 

이때 처음 만든 피벗테이블을 복사한 경우와 표에서 다시 피벗테이블을 생성하는 경우에 '새로고침'을 하면 값이 달라 질 수 있습니다.

실제로 값이 다른 건 아닙니다.

처음 만든 피벗테이블에서 복사하여 사용하는 경우 아무 피벗이나 '새로고침'을 하면 테이블 모두에 값이 변경됩니다.

하지만 별도로 피벗테이블 만들기를 통해서 만든 피벗은 다른 피벗테이블의 '새로고침'이 작동되지 않아 예전 데이타를 가져오게 됩니다.

 

모든 업무가 그렇듯이 많이 해보면 스킬이 늘어 난다. 꼭 필요한 일이 아니어도 여러 영역으로 필드를 옮기면서 더 좋은 리포트를 찾다 보면 분명히 만족스러운 결과가 나올 것이다.

적게 일하고 많이 버세요~