엑셀 피벗테이블 핵심 개념

  141_초급 강좌 – 피벗 테이블 기본(1)

저의 원노트에 기록되어있는 모든 컴퓨터 자료들을 블로그로 옮기고 있지만 그 콘텐츠는 사실 다른 여러 책들을 참고 하고 있습니다. 온라인으로 엑셀 책을 검색하면 수십 개의 책들이 나오고 목차는 대부분 볼 수 있죠. 거기서 외국 책 한국 책들의 목차를 참고하고 내가 판단하기에 필요하다는 내용만을 기억하여 미리 순서를 만들어 놓고 거기에 따라 진행하고 있습니다. 제가 왜 이런 설명을 드리냐 하면 대부분의 책들에서 피벗 테이블은 상당히 뒷부분에 배치 되어 있다는 것입니다. 이게 예상외의 결과 였어요. 피벗 테이블만 제대로 알면 여러가지 귀찮은 테크닉이 필요가 없어 집니다. 업무 중에 매우 자주 사용하는 기능인데 책 뒤에 배치 되어 있으면 앞부분만 공부한 사람들은 활용도 못하잖아요. 그런데 피벗 테이블은 사용하기는 정말 쉽지만 알아야 하는 개념들을 포괄하고 있어서 고 난이도로 여기고 있다고 여겨 집니다.

그러나 피벗 테이블은 어렵지 않고 정말 유용합니다. 피벗 테이블 하나만 제대로 알아도 그동안 제가 포스팅 했던 여러가지 필터, 부분합, 자동 합계 등 많은 기능들이 필요가 없어 집니다. 피벗 테이블을 바로 만들기 전에 이번 포스팅에서는 피벗 테이블이 할 수 있는 것들과 구성, 그리고 개념을 확실히 집고 가겠습니다.

141연습파일.xlsx

1. 피벗 테이블을 사용 하는 근본적 이유

   피벗 테이블의 필드, 행, 값들을 알기 전에 왜 피벗 테이블을 쓰는가 알 필요가 있습니다. 피벗 테이블은 엑셀 97년도 버전부터 제대로 원하기 시작했으며 2000년도 버전 부터는 피벗 차트까지 지원하기 시작 했습니다. 피벗은 영어로 PIVOT, 즉 "돌리다 라는 의미를 갖고 있습니다. 어떻게 보면 간단하고 정확한 표현인 것 같은데요 이전에 제가 소개해 드렸든 행 열 전환과 혼동 하시면 안됩니다. 여기서 "돌리다"의 의미는 내가 가지고 있는 데이터를 어떤 식으로든 조합해서 다른 결과를 빨리 보여 준다는 의미가 강합니다. 데이터베이스 프로그램에서 지원하던 기능을 엑셀에서 매우 사용하기 쉽게 지원하는 기능입니다.

    

   좌측 표는 제품이 생산 될 때마다 코드, 크기, 생산 수량을 기록한 것이고 오른쪽 표는 지역별로 제품 생산 코드를 요약한 것 입니다.

   

   위의 표를 보시면 좌측의 지역당 제품의 생산을 순서대로 기록한 것 입니다. 이렇게 데이터를 입력하는 것은 데이터베이스의 관점에서 보면 아무런 문제가 되질 않습니다. 그런데 회사 전략을 세우는 과정에서 지역별 제품의 생산을 한눈에 보고 싶다면 왼쪽의 데이터는 매우 어렵습니다. 우리는 오른쪽과 같은 데이터를 필요로 합니다. 또 다른 한가지 예를 생각 해 봅시다.

    

   왼쪽 표는 역시 지역별로 제품 생산을 순서대로 기록한 것이고 오른쪽 표는 생산 용량을 요약한 것 입니다.

   

    

   만약 제품당 생산한 용량을 알고 싶다면 우리는 오른쪽의 표를 또 만들어야 합니다. 기존의 데이터는 우리가 미리 가지고 있던 것들입니다. 지금은 원본 데이터의 필드가 5가지 밖에 안되지만 실제 제품인 경우 원본이 가지는 필드는 판매수량, 생산 월, 생산 시간, 원가 등 어마어마한 데이터를 가질 수가 있습니다. 그런데 데이터를 입력하는 사람의 입장에서는 좌측의 표의 입력 형태가 가장 빠르고 합리적 입니다. 실수를 줄이고 또 데이터 베이스의 관점에서도 가장 올바른 형태입니다. 그렇지만 어떤 형태의 요약 정보가 언제 어떤 형태로 필요할지는 아무도 모릅니다. 원본데이터를 가지고 있다면 얻을 수 있지만 시간이 많이 걸립니다. 이런 작업을 쉽게 하기 위해 등장한 것이 피벗 테이블 입니다.

    

   우리가 원하는 정보는 데이터의 원본이 아니라 가공된 데이터 (PROCESSED DATA) 입니다. 피벗 테이블은 데이터의 원본만 있으면 사용자가 쉽게 원본 데이터를 가공하여 원하는 데이터를 얻을 수 있게 하는 아주 강력한 기능 입니다.

   데이터의 원본의 필드 (머리글)의 종류가 1개만 늘어나도 조합될 수 있는 가공된 데이터의 종류는 기하 급수적으로 늘어납니다. 모든 가공된 데이터를 저장할 필요가 없이 간단히 원본 데이터 만으로 원하는 형태의 가공된 요약된 정보를 보여주는 것이 피벗 테이블 입니다.

   데이터베이스 관점에서 보면 데이터는 우측 테이블 보다 항상 좌측 테이블 형태로 입력되는 것이 맞습니다. 원본 데이터를 가지고 전문 데이터 베이스 프로그램에서는 피벗 테이블 보다 훨씬 많은 가공 방법을 제공합니다. 피벗 테이블은 데이터 베이스의 명령어 중의 하나인 하나의 쿼리라고 할 수 있고 이걸 데이터베이스 프로그램 에서는 크로스 탭 쿼리 (CROSS TAB QUERY)라고 합니다.

    

2. 피벗 테이블이 할 수 있는 것들

   피벗 테이블의 개념은 이렇게 간단한 것입니다. 그러나 이 간단한 개념을 가지고 피벗 테이블이 무엇을 할 수 있는지 아는 것이 중요 하겠지요. 알아야 써 먹으니까요. 앞으로의 피벗 테이블의 기초 포스팅을 진행하며 자세히 살펴볼 예정이지만 피벗 테이블이 멀 할 수 있는지 소개하고 얼마나 강력한 기억인지 느끼고 가겠습니다.

    

   데이터의 그룹화

   피벗 테이블은 예전 부분합에서 소개 했던 그룹화 기능을 기본적으로 가지고 있고 사용하기도 무척 편리합니다. 언제든지 원하는데로 그룹화 하여 요약된 부분만 볼 수 있습니다. 지금 경기도와 경상도의 그룹을 닫은 상태 입니다.

   

    

    

   정렬과 필터

   피벗 테이블은 정렬과 필터를 따로 활성화 시켜주지 않아도 기본적으로 정렬, 필터 기능을 가지고 있습니다.

   

    

    

   슬라이서 필터링

   내가 제일 좋아하는 슬라이서 버튼을 통한 필터링이 엑셀 2010부터 지원 됩니다. 2013부터는 피벗 테이블이 아니어도 슬라이서 기능을 사용 할 수 있습니다.

   

    

    

   요약 행

   지금 보여드린 예제의 요약 행은 수량이지만 개수, 평균, 최대값, 최소 값등 원하는 대로 요약행을 언제든지 조정할 수 있습니다.

   

    

    

   데이터의 피벗

   피벗 테이블의 주 기능입니다. 원하는 대로 데이터를 마음껏 돌려가며 가공 할 수 있습니다.

   

    

   이 외에도 피벗 테이블이 할 수 있는 것들은 피벗 테이블 중급 편에서 따로 진행 하겠습니다.

   피벗 테이블을 당장 만드는 것 보다 피벗 테이블의 개념을 이해하는 것이 중요합니다. 피벗 테이블이 멀 하는지 알면 다들 잘 아시는 검색을 통해서 방법은 언제나 얻을 수 있습니다. 멀 할 수 있는지 모르면 검색도 못합니다. 피벗 테이블은 이제 엑셀의 핵심 기능으로 자리 잡았을 만큼 많은 편리함을 제공합니다. 다음 시간부터 는 본격적으로 피벗 테이블을 만들며 기능들을 소개 하겠습니다.