지금까지 이 사이트에서 다룬 피벗 테이블은 대부분 단일 데이터 테이블에서 생성한 것이었습니다. 하지만 데이터 모델(Data Model) 기능은 피벗 테이블에 새로운 가능성을 열어줍니다. 데이터 모델을 활용하면 여러 개의 테이블을 하나의 피벗 테이블에서 동시에 사용할 수 있으며, 테이블 간 연결을 위해 하나 이상의 '테이블 관계'를 설정해야 합니다.
데이터 모델은 Excel 2013에서 처음 도입된 기능으로, 별도의 외부 프로그램 없이 통합 문서 내 여러 테이블을 마치 하나의 데이터베이스처럼 활용할 수 있다는 점이 큰 장점입니다. 이 글에서는 Excel에서 피벗 테이블 데이터 모델을 만드는 6단계 과정을 자세히 안내해 드립니다.
Excel에서 피벗 테이블 데이터 모델 만들기: 단계별 방법
아래 그림은 하나의 통합 문서에 있는 세 개의 테이블 일부를 보여줍니다. 각 워크시트는 같은 통합 문서에 속해 있으며, 편의상 별도 창으로 나타낸 것입니다. 시트 이름은 Orders(주문), Customers(고객), Regions(지역)이며, 정리를 위해 세 개의 테이블 역시 같은 이름으로 지정했습니다.
- Orders 테이블: 제품 주문 정보가 담겨 있습니다.
- Customers 테이블: 회사 고객에 대한 정보가 담겨 있습니다.
- Regions 테이블: 지역 관련 정보가 담겨 있습니다.
테이블을 자세히 살펴보면 Orders와 Customers 테이블에는 CustomerID 열이 공통으로 존재하고, Customers와 Regions 테이블에는 State 열이 공통으로 존재합니다. 바로 이 공통 열들을 사용하여 테이블 간 관계를 형성하게 됩니다.
일대다(One-to-Many) 관계란?
테이블 간의 관계는 '일대다' 구조입니다. Orders 테이블의 각 행에는 Customers 테이블에 정확히 하나씩 대응되는 행이 있으며, 이 매칭은 CustomerID 열로 결정됩니다. 마찬가지로 Customers 테이블의 각 행에는 Regions 테이블에 정확히 하나씩 대응되는 행이 있으며, 이는 State 열로 결정됩니다.
💡 참고: 데이터 모델로 만든 피벗 테이블은 단일 데이터 테이블로 만든 피벗 테이블과 비교할 때 몇 가지 제약이 있습니다. 가장 대표적인 것은 그룹화(Group) 기능을 사용할 수 없다는 점이며, 그 밖에도 계산 필드나 계산 항목을 만들 수 없습니다.
이번 예제의 목표는 주(State), 지역(Region), 연도(Year)별 매출을 요약하는 것입니다. 매출과 날짜 정보는 Order 테이블에, 주 정보는 Customers 테이블에, 지역명은 Regions 테이블에 각각 들어 있기 때문에 세 테이블을 모두 활용해야 목표한 피벗 테이블을 만들 수 있습니다.
아래는 실제로 사용한 단계별 진행 과정입니다.
1단계: 피벗 테이블 삽입하기
먼저 데이터 집합에서 피벗 테이블을 삽입합니다. Orders 테이블 안의 임의의 셀을 선택한 후, 삽입 ➪ 피벗 테이블 ➪ 테이블/범위에서를 선택하면 피벗 테이블 만들기 대화 상자가 나타납니다.
- 첫째, Orders 시트의 셀 범위 C5를 선택했습니다.
- 둘째, 삽입 탭 ➪ 피벗 테이블 ➪ 테이블/범위에서를 선택했습니다.
2단계: 데이터 모델에 데이터 추가하기
분석할 데이터와 피벗 테이블 보고서를 배치할 위치를 지정하는 두 가지 옵션은 기본값 그대로 둡니다. 그리고 '이 데이터를 데이터 모델에 추가' 확인란을 체크한 후 확인을 클릭합니다.
- 대화 상자가 나타나면,
- '이 데이터를 데이터 모델에 추가'를 선택하고,
- 확인을 누릅니다.
- 새로 만들어진 워크시트 오른쪽의 피벗 테이블 필드 작업 창을 보면 이전과 조금 다른 것을 알 수 있습니다. 이번에는 데이터 모델을 사용하도록 선택했기 때문입니다.
- 작업 창에는 활성 탭과 모두 탭 두 개가 있습니다. 활성 탭에는 Orders 테이블만 표시되고, 모두 탭에는 통합 문서의 모든 테이블이 표시됩니다.
- 모두 탭의 테이블을 활성 탭으로 옮길 수 있습니다. 예를 들어 Customers 테이블을 활성 탭에 넣으려면 모두 탭을 클릭한 후 Customers 테이블을 마우스 오른쪽 버튼으로 클릭하고 활성 탭에 표시를 선택합니다. Regions 테이블도 같은 방법으로 처리합니다.
- 이 작업은 선택 사항입니다. 나중에 모두 탭에서 필드를 직접 선택하게 됩니다.
- 다음 그림은 피벗 테이블 필드 작업 창의 활성 탭입니다. Customers와 Regions 테이블이 확장되어 열 머리글(필드 이름)이 보이며, 작업 창 레이아웃도 변경했습니다. 레이아웃을 변경하려면 도구 컨트롤을 클릭한 후 드롭다운 메뉴에서 필드 섹션 및 영역 섹션 나란히 표시를 선택하면 됩니다.
3단계: 관계 관리 메뉴 열기
이제 테이블 간의 관계를 설정할 차례입니다. 피벗 테이블 분석 ➪ 계산 ➪ 관계를 선택하면 관계 관리 대화 상자가 나타납니다.
- 새 피벗 테이블 내부 아무 곳이나 클릭한 후,
- 피벗 테이블 분석 탭에서 관계를 선택합니다.
- 그러면 또 다른 대화 상자가 나타나는데,
- 여기서 새로 만들기를 클릭합니다.
4단계: 테이블 관계 만들기
첫 번째 관계를 만들어 보겠습니다. 테이블 드롭다운에서 Orders를 선택하고, 열(외래)에서 CustomerID를 선택합니다. 그다음 관련 테이블에서 Customers를, 관련 열(기본)에서 CustomerID를 선택합니다.
- 새 대화 상자가 열리면 다음과 같이 설정합니다:
- 테이블: 데이터 모델 테이블 - Orders
- 관련 테이블: 워크시트 테이블 - Customers
- 열(외래): CustomerID
- 관련 열(기본): CustomerID
- 확인을 클릭하면 관계 관리 대화 상자로 돌아갑니다.
- 이후 새로 만들기를 다시 클릭하여 Customers 테이블과 Regions 테이블 사이의 관계를 아래와 같이 만듭니다:
- 테이블: 데이터 모델 테이블 - Customers
- 관련 테이블: 워크시트 테이블 - Regions
- 열(외래): State
- 관련 열(기본): State
- 그런 다음 확인을 누릅니다.
- 이제 관계 관리 대화 상자에 두 개의 관계가 표시됩니다.
💡 참고: 미리 테이블 관계를 설정하지 않았더라도 걱정하지 않아도 됩니다. 서로 다른 데이터 테이블의 필드를 피벗 테이블에 추가하려고 하면 Excel이 관계 설정을 유도하는 안내 메시지를 표시해 줍니다.
5단계: 피벗 테이블 필드를 영역에 배치하기
테이블 관계 설정을 마쳤으니, 이제 피벗 테이블 필드 작업 창에서 필드 이름을 적절한 영역으로 끌어다 놓기만 하면 됩니다:
- 첫째, Total(합계) 필드를 값 영역으로 끕니다.
- 둘째, Year(연도) 필드를 열 영역으로 옮깁니다.
- 셋째, Region(지역) 필드를 행 영역으로 끕니다.
- 넷째, StateName(주 이름) 필드를 행 영역으로 옮깁니다.
- 그러면 아래 그림처럼 피벗 테이블의 일부가 완성됩니다.
6단계: 슬라이서 추가하기
마지막 단계에서는 피벗 테이블을 더욱 유연하게 필터링할 수 있도록 두 개의 슬라이서(MailList와 Product)를 추가했습니다. 이를 통해 메일링 리스트에 등록된 고객별, 제품별로 피벗 테이블을 손쉽게 필터링할 수 있습니다.
- 먼저 피벗 테이블 내부 아무 곳이나 클릭합니다.
- 그다음 피벗 테이블 분석 탭에서 슬라이서 삽입을 선택합니다.
- 이후 모두 탭에서 다음 항목을 선택합니다:
- Customers: MailList
- Orders: Product
- 마지막으로 확인을 누릅니다.
- 모든 작업을 마치면 최종 피벗 테이블은 아래 그림과 같은 모습이 됩니다.
💡 팁: 피벗 테이블을 수식으로 변환할 수도 있습니다. 피벗 테이블의 임의의 셀을 선택한 후 피벗 테이블 분석 ➪ OLAP 도구 ➪ 수식으로 변환을 선택하세요. 그러면 피벗 테이블이 수식을 사용하는 셀들로 대체됩니다. 이때 생성되는 수식은 CUBEMEMBER와 CUBEVALUE 함수로 구성됩니다. 새 데이터 범위는 더 이상 피벗 테이블이 아니지만, 원본 데이터가 변경되면 수식도 함께 업데이트됩니다.
마무리
지금까지 Excel에서 피벗 테이블 데이터 모델을 만드는 6단계 과정을 살펴보았습니다. 여러 테이블을 하나의 피벗 테이블로 통합하면 단일 테이블만으로는 불가능했던 심층적인 데이터 분석이 가능해집니다. 궁금한 점이 있다면 언제든지 아래 댓글로 남겨주세요. 즐거운 엑셀 되세요! :)
함께 읽으면 좋은 글
- Excel 피벗 테이블에서 0 값 표시하기: 2가지 프로 팁
- 피벗 테이블 역변환 – 요약 데이터 피벗 해제(Unpivot) 방법
- Excel에서 피벗 테이블 보고서 만드는 방법
- 엑셀 초보를 위한 피벗 테이블 단계별 튜토리얼 | PDF 다운로드
- Excel에서 타임라인을 만들어 피벗 테이블 필터링하기!