142 엑셀 피벗테이블 만들기

142_엑셀 피벗테이블 만들기 (2)

이번 포스팅부터 먼저 개념을 이해 하는것이 정말 중요 합니다. 이 개념을 이해 하기만 하면 엑셀에서 가장 파워풀한 기능으로 활용 될 수 있습니다. 이것을 잘 활용하면 데이터의 가공뿐 아니라 피벗 차트, 분석을 통해 엑셀의 주된 목적인 데이터의 분석과 시각화를 가장 잘 이용하기 위한 툴 입니다. 사실 이 부분만 따로 때서 중급으로 강의를 진행 할 정도로 분량이 많지만 기본만 알아도 대부분의 핵심 기능들을 사용하는데 부담 없을 거라 믿으며 강의를 시작 하겠습니다.

이번 포스팅은 엑셀 리본 삽입 탭의 피벗 테이블에 관한 내용 입니다.

142연습파일.xlsx

142연습파일_완성본.xlsx

1. 피벗 테이블 조건과 만들기

   피벗테이블은 데이터만 있다면 항상 만들 수는 있지만 가장 효율적이고 손이 덜 가는 테이블을 만들 때는 제가 항상 강조하는 형식으로 데이터가 저장되어 있다면 정말 편리하고 바로 이용하실 수 있습니다. 데이터를 올바르게 저장하는 것이 서식 보다 엑셀에서는 몇배나 중요하다고 계속 강조 드리고 있습니다. 엑셀은 워드가 아니기 때문입니다.

   첫째, 하나의 열에 하나의 필드 원칙을 지킵니다.

   둘째, 하나의 표 안에는 절대 빈 행과 빈 열이 없게 합니다.

   셋째, 만약 표의 제목 행을 넣고 싶다면 테이블과 제목 행 사이에 빈 행을 추가 합니다.

   넷째, 표와 표 사이에는 항상 최소 1개 이상의 빈 열을 삽입합니다.

   만약 데이터가 이와 같은 형태로 저장되어 있다면 피벗 테이블은 이미 완성된 거라고 보시면 됩니다.

    

   데이터를 데이터베이스 표로 만들 필요도 없습니다. 단순히 표 안에 커서를 위치하시고 삽입 >> 피벗 테이블만 클릭 해 주시면 됩니다.

   

    

    

   엑셀은 빈 열과 빈 행을 통하여 자동으로 피벗 테이블에 포함할 데이터를 잡습니다. 바로 이 때문에 하나의 표에 빈 행과 빈 열을 빼라고 강조한 것이며 제목 행이 있다면 빈 행을 삽입하라고 한 것 입니다. 머리글 행, 즉 필드는 내 표의 첫 번째 행을 기준으로 자동으로 피벗테이블에 추가 합니다. 피벗 테이블을 만드는 장소는 새 워크시트를 추천 합니다.

   

    

    

   피벗 테이블이 생성된 화면 입니다. 여기서 붉은색 1,2,3 지역을 주목하시면 됩니다.

   

    

   피벗 테이블이 생성 되면 3가지 영역이 추가 됩니다.

   1번은 피벗 테이블을 사용할 때만 활성화 되는 리본 명령어의 변화 입니다. 분석 탭과 디자인 탭이 추가 생성 되었습니다.

   2번은 피벗 테이블로 데이터를 가공하기 위한 피벗 테이블 필드 창 입니다.

   3번은 피벗 테이블을 새로운 시트에 만들었기 때문에 생성된 피벗 테이블 전용 시트 입니다. 물론 이름 변경과 색 지정이 가능 합니다.

    

2. 피벗 테이블 필드 창

   분석 탭과 디자인 탭 보다 확실히 이해 하셔야 하는 것이 피벗 테이블의 데이터를 돌리기 위한, 즉 가공하기 위한 피벗 테이블 필드 창입니다. 이것만 알고 있으면 피벗 테이블에 자신감이 붙고 피벗 테이블을 필요 이상으로 사용하게 되는 부작용도 낳을 수 있습니다.

    

   엑셀 2013버전부터 피벗 테이블 필드 창은 화면 우측에 고정된 상태로 생성 되지만 언제든지 마우스 드래그를 통하여 옮길 수 있습니다.

   

    

    

   화면 구석에 가져 가면 다시 원래의 상태로 고정(dock) 됩니다.

   

    

   피벗 테이블 필드 창에서 데이터 표를 구성하기 위한 최소한의 조건, 열, 행, 값을 마음 데로 조절할 수 있습니다.

   

    

   지금부터 피벗테이블의 사용법을 알려 드리니 위의 사진을 잘 만들지 못하겠으면 그냥 패스 하세요.

    

3. 피벗 테이블 열, 행, 값 이해하기

   피벗 테이블의 열, 행, 값에 머가 들어갈지 고민부터 하면 머리가 아파 집니다. 내가 무슨 데이터가 필요한지 일단 찍어놓고 피벗으로 돌리다 보면 원하는 데이터가 나오지 가장 먼저 하실 작업은 내가 무슨 값들을 원하는지 정하는 것입니다. 머리글을 바탕으로 무슨 데이터를 원하는지 정하기만 했다면 요것 조것 돌려 보기만 하면 됩니다.

    

   테이블의 첫번째 행을 기중으로 생성된 필드 입니다. 내가 얻고 싶은 데이터는 제품 코드, 크기, 생산 수량에 관한 것 입니다.

   

    

    

   제품 코드의 종류만을 알고 싶다면 행 이나 열로 제품 코드만 넣으시면 됩니다.

   

    

   여기서 아무것도 아닌 것 같지만 한번 생각해 봅시다. 만약 원래의 데이터에서 피벗 테이블을 쓰지 않고 이렇게 제품의 종류를 중복 값이 없게 나열 하려면? 여러 방법이 있지만 절대 단일 함수로는 저렇게 중복된 값을 제외하고 제품 코드의 종류만 뽑을 수 없습니다. 필터 기능을 이용하면 어떤 값들이 있는 줄 알 수는 있지만 이렇게 따로 행으로 뽑아주지는 않습니다.

    

   피벗 테이블의 행 열에서 필드가 위치한 순서는 중요합니다. 크기가 제품 코드 위에 위치하고 있으므로 크기 별로 제품코드가 자동 그룹화 되어 행에 배치 됩니다.

   

    

    

   만약 제품 코드가 크기 위에 위치하면 그룹화 기준이 변경됩니다. 위치 조절은 마우스 좌 클릭 드래그 입니다.

   

    

   제가 처음에 보여준 표를 만들려면 여기서 크기만 열로 드래그를 해서 이동 하시면 됩니다.

   

    

    

   행에 제품 코드, 열에 크기, 값에 생산 수량이 정확히 배치하였습니다.

   

    

   지금 이 표를 피벗 테이블을 쓰지 않고 바로 원본 데이터에서 만들려면요?

   당장 생각나는 함수만 VLOOKUP, COUNTIF, SUMIF 함수이네요. 그리고 필요에 따라 더 많은 함수가 들어가야할 지도 모릅니다. 그리고 만약 함수로 이 표를 만든다고 해도 거기서부터 다른 표를 가공할 수가 없습니다. 다른 표를 원한다면 또 함수를 적용해야 합니다. 피벗 테이블은 이런 수고를 없애 줍니다.

    

   지난 포스팅과 이번 포스팅 만으로도 피벗 테이블의 유용 함은 이미 아셨을거라 믿습니다. 피벗테이블은 엑셀의 데이터 분석을 위한 가장 핵심적인 기능입니다. 원본 데이터를 가지고 얼마든지 원하는 데이터를 몇번의 마우스 클릭만으로 얻게 하고 함수에 대한 부담감을 없앨 수 있습니다. 다음 포스팅부터 더 많은 피벗 테이블의 기능에 대해 알아 보겠습니다.