여러 시트를 통합한 피벗테이블 생성하기 feat.데이터모델링
여러 시트에 존재하는 테이블들을 JOIN하여 하나의 피벗테이블에서 처리할 수 있다.
실습을 위해 활용할 xlsx 파일 내 시트 별 데이터 파일 다운로드
주문서 시트
| 주문ID | 날짜 | 고객ID | 상품ID | 수량 |
|---|---|---|---|---|
| O0001 | 20180105 | C03 | P004 | 79 |
| O0002 | 20180108 | C02 | P007 | 153 |
| O0003 | 20180108 | C04 | P001 | 74 |
| O0004 | 20180111 | C02 | P003 | 139 |
| O0005 | 20180316 | C04 | P005 | 52 |
| O0006 | 20180316 | C05 | P006 | 40 |
| O0007 | 20180318 | C03 | P003 | 38 |
| O0008 | 20180401 | C03 | P002 | 118 |
| O0009 | 20180415 | C02 | P002 | 185 |
| O0010 | 20180422 | C04 | P007 | 94 |
| O0011 | 20180501 | C02 | P007 | 162 |
| O0012 | 20180501 | C02 | P002 | 88 |
| O0013 | 20180604 | C04 | P008 | 171 |
| O0014 | 20180604 | C04 | P002 | 20 |
고객정보 시트
| 고객ID | 고객명 | 성별 | 등급 |
|---|---|---|---|
| C01 | 홍길동 | 남 | VIP |
| C02 | 김진호 | 남 | 일반 |
| C03 | 김진희 | 여 | VIP |
| C04 | 박재우 | 남 | 일반 |
| C05 | 이승민 | 여 | 일반 |
상품정보 시트
| 상품ID | 상품명 | 가격 | 재고량 |
|---|---|---|---|
| P001 | 가방 | 29900 | 104 |
| P002 | 신발 | 19900 | 121 |
| P003 | 키보드 | 9900 | 166 |
| P004 | 마우스 | 15000 | 313 |
| P005 | 이어폰 | 34000 | 454 |
| P006 | 공기청정기 | 88000 | 234 |
| P007 | 지갑 | 64000 | 254 |
| P008 | 청바지 | 51200 | 152 |
세 시트에서 중복되는 고객ID와 상품ID 필드를 JOIN을 통해 결합하면
| 주문ID | 날짜 | 수량 | 고객ID | 고객명 | 성별 | 등급 | 상품ID | 상품명 | 가격 | 재고량 |
|---|---|---|---|---|---|---|---|---|---|---|
| O0001 | 20180105 | 79 | C03 | 김진희 | 여 | VIP | P004 | 마우스 | 15000 | 313 |
이와 같은 하나의 테이블처럼 분석에 활용할 수 있다.
이를 위해, 엑셀에서 데이터베이스 개념을 적용한 데이터 모델링을 활용해야 한다.
먼저, 각각의 시트에 존재하는 데이터를 표(Ctrl+T) 형태로 만들고 표 이름을 각각 지정해준다.
표 도구 - 디자인 탭 (표 생성 시 자동으로 보임)
통합된 피벗테이블을 만드는 두 가지 방법이 있다.
-
하나의 표를 가지고 먼저 피벗테이블 생성한 뒤 다른 테이블과의 관계를 설정하는 방식
-
처음부터 데이터 모델 관리를 통해 모델링(관계 설정) 후 피벗테이블을 생성하는 방식
피벗테이블 생성 후 관계 설정하는 방식
먼저, 주문서 테이블로 피벗테이블을 생성한다. 이 때, 아래 쪽에 데이터 모델에 추가를 선택해야 한다.
피벗테이블 필드에 활성과 모두라는 것이 보이게 되고 모두를 클릭하면 이미 만들어 둔 테이블들이 보인다.
모델링없이 그냥 사용하면 잘못된 데이터가 입력되며 안내창이 뜬다.
피벗테이블 도구 - 분석 탭에 관계를 선택한다.
새로 만들기를 선택하여 관계를 설정해준다.
주문서의 고객ID(외래키)와 고객정보의 고객ID(주키 또는 기본키)
주문서의 상품ID(외래키)와 상품정보의 상품ID(주키 또는 기본키)
관계가 설정된 것을 확인할 수 있다.
데이터 모델링이 제대로 되었는지 확인하려면 데이터 탭 - 데이터 모델링 관리 창에서 다이어그램 뷰를 선택한다.
관계선에 마우스를 올려주면 연결된 필드명이 각각 보인다.
통합한 데이터 모델을 통해 고객별로 각각 주문한 상품명과 수량을 파악할 수 있다.
처음부터 데이터 모델링 후 피벗테이블을 생성하는 방식
생성해 둔 각각의 테이블의 아무 셀이나 선택 후 POWERPIVOT 탭 - 데이터 모델에 추가를 선택한다. ( 테이블이 3개면 3번을 수행한다.)
데이터 - 데이터모델링 화면 아래를 보면 데이터 모델에 추가한 테이블명이 보인다.
원하는 필드끼리 클릭 앤 드래그를 통해 관계를 설정할 수 있다.
현재 화면(데이터 모델링 관리) 창에서 피벗테이블 버튼을 클릭하면 통합된 피벗테이블을 생성할 수 있다.