엑셀 피벗 테이블 완벽 정리

이번에 설명드린 엑셀 함수는 '피벗'입니다. 상사가 갑자기 피벗으로 정리하라는 요청이 있을때, 식은땀 나고 그러시지는 않으셨어요? (제가 그랬었어요ㅎㅎ) 이번 참에 엑셀 피벗 기능을 완벽히 이해하셔서 누가 어떤 요청을 하더라도 자신있게 처리해보자구요! 그정도로 이해만하면 사용하기 쉬운 피벗 함수입니다.

 

우선 피벗의 정의부터 볼까요?

피벗이란?

정리가 안된 데이터 및 많은 양의 데이터를 사용자가 생각하는 바에 따라 원하는 기준으로 데이터를 재구성(재배치)하는 실무 엑셀 기능. 좀 더 쉽게 풀이하면 많은 데이터 중에서 내가 필요한 정보를 확인할 수 있도록 쉽게 재배치 할 수 있는 엑셀 기능이라고 이해하시면 됩니다.

 

이번에도 예제로 한번 설명드려 보겠습니다. 아래와 같이 정리가 필요한 데이터가 있다고 가정합니다.

이러한 데이터가 있는데 영업부 소속의 직원들에 대해서만 데이터를 정리하고 싶을 때도 있고, 혹은 부장 지급 중에서 나이는 30 이상이면서 근무연수는 15년 이상인 직원의 데이터만 필요한 경우, 이러한 다양한 니즈의 케이스가 있을텐데요. 바로 이럴 때 피벗 기능을 사용하는 겁니다.

 

사용하는 방법은 먼저 아래 그림처럼 원본 데이터 영역에 대하여 블록을 지정합니다. 마우스로해도 되고 키보드로 해도 됩니다. 참고로 키보드로 블록 지정하는 방법은 다음 엑셀 소개 글에서 단축키 설명드릴 때 함께 알려드리겠습니다. 그 다음에는 상단 메뉴에서 [삽입]으로 들어가셔서 첫번째 버튼인 [피벗테이블]을 선택합니다.

그 다음에는 "피벗 테이블 만들기" 팝업이 나타나게 될텐데요. 크게 두가지로 항목이 나뉘어 집니다. ① 분석할 데이터를 선택, ② 피벗 테이블을 넣을 위치 선택. 분석할 데이터 선택 영역에는 첫번째인 "표 또는 범위 선택" 항목이 기본 설정되어 있을 겁니다. 이유는 바로 앞에서 우리가 분석할 데이터에 대해 블록을 지정하였고, 그 지정 범위가 자동으로 설정되었기 때문입니다. 즉, 분석할 데이터는 SKIP 하시면 되는 겁니다.

그 다음에는 보고서를 넣을 위치 선택인데요. 첫번째 새워크시트는 열려져 있는 파일에서 새로운 시트를 생성하여 피벗 보고서를 넣는 방법이구요, 두번째 기존 워크시트는 현재 보여지는 엑셀 시트에 피벗 보고서를 넣는 방법입니다. 이 부분은 두 가지 기능 모두 직접 해보시면 이해가 빠르실 겁니다. 여기서는 새 워크시트로 해보겠습니다.

이제 새 워크시트가 생성되면서 좌측에는 피벗 테이블1이라는 네모 박스가 생성되었고, 우측에는 피벗 테이블 필드라는 설정영역이 보이게 됩니다.

이제부터는 우리가 원하는 행/열의 컬럼들을 설정하면 되는데요. 윗 부분인 보고서에 추가할 필드 선택에서 데이터들을 드래그하여 밑에 행/열 등 영역에 가져다 놓으시면 자동 설정됩니다. 다시 쉽게 원복도 되니 연습할겸 자유롭게 설정해보시면 이해하시기 편하실 겁니다. 그럼 이번에는 한번 부서별 직급별 팀원들의 정보를 정리해보겠습니다.

여기까지 잘 따라오셨으면 '피벗'기능은 80% 다 이해하신겁니다. 여기서 추가적으로 부장 직원들은 필터기능을 이용하여 제외해보겠습니다. 이를 위해서는 우측 설정 영역에서 직급 데이터를 위 필터영역으로 가져가면 좌측에 필터 드롭다운 박스가 생기고 여기서 부장 데이터만 체크를 해제하면 됩니다.

다시 앞단계로 넘어와서 이번에는 부서별로 합계가 중간에 포함되어 있는데 이 부분을 없애고 좀더 깔끔하게 정리해보겠습니다. 이를 위해서는 좌측 피벗 결과 보고서에서 우측 마우스를 누르면 메뉴 기능이 나오고 여기서 "피벗 테이블 옵션"을 선택합니다.

여기서 [표시]탭에 들어간 후, "클래식 피벗 테이블 레이아웃 표시"를 체크후 확인 버튼을 클릭합니다.

아래와 같이 나온 상태에서 요약 행에 다시 우측 마우스를 선택하고 '부서' 부분합을 선택합니다.

그럼 아래 그림처럽 부서별 합계 행이 제외된 결과물을 볼 수 있습니다.

이런식으로 자유자재로 데이터들을 원하는 형태로 재배치하며 보고서에 사용할 자료들을 만든다고 보시면 됩니다. 피벗 기능 관련해서 가장 기본적인 기능만 소개해드렸으며, 이 부분만 잘 이해하시고 몇 번 연습하시면 회사에서 필요로 하는 피벗 업무 왠만한 일들을 모두 해결하실 수 있을 수준이 됩니다.

 

연습해보시고 어떤 질문이던 댓글로 남겨주시면 추가로 설명해 드리겠습니다.