엑셀에서 데이터 모델 관리하는 방법: 5단계 완벽 가이드

엑셀에서 데이터 모델(Data Model)을 관리하는 방법을 찾고 계신가요? 잘 오셨습니다. 이 글에서는 엑셀 데이터 모델을 만들고 관리하는 전 과정을 단계별로 자세히 안내해 드립니다. 실습 파일을 내려받아 직접 따라 하면서 익힐 수 있도록 구성했으니, 끝까지 함께해 주세요.

엑셀의 데이터 모델이란?

엑셀 데이터 모델은 두 개 이상의 테이블이 공통 데이터 필드를 통해 서로 연결된 특수한 형태의 데이터 구조입니다. 여러 시트나 여러 소스에 흩어져 있는 데이터를 하나의 통합 테이블처럼 다룰 수 있게 해주는 것이 핵심입니다.

예를 들어 제품(Product), 영업사원(SalesRep), 판매(Sales) 세 개의 테이블이 있다고 가정해 봅시다. 엑셀 데이터 모델을 활용하면 이 세 테이블 사이에 관계(Relationship)를 설정하여 마치 하나의 거대한 테이블인 것처럼 데이터를 조회하고 분석할 수 있습니다.

왜 엑셀에서 데이터 모델을 만들어야 할까요?

리포트 작성에 필요한 데이터가 항상 한 테이블 안에만 들어 있지는 않습니다. 오히려 대부분의 경우 여러 테이블과 여러 시트에 나눠져 있는데, 이럴 때 바로 데이터 모델이 빛을 발합니다.

앞선 예시에서 판매 리포트에는 제품 가격과 영업사원의 담당 지역 정보가 필요할 수 있습니다. 하지만 이 정보는 각각 Product, SalesRep 테이블에 따로 저장되어 있습니다. 데이터 모델로 세 테이블 간의 관계를 맺어두면, Sales 테이블만으로도 제품명·가격·지역 등 연관된 모든 데이터를 손쉽게 불러와 분석할 수 있습니다.

엑셀에서 데이터 모델 관리하는 5단계

이제 데이터 모델을 실제로 만들고 관리하는 5가지 단계를 살펴보겠습니다. 이 글은 Microsoft Excel 365 버전 기준으로 작성되었으며, 다른 버전에서도 동일하게 적용할 수 있습니다.

1단계: 데이터셋 준비하기

데이터 모델을 만들기 위해서는 먼저 호환 가능한 형태의 데이터셋을 준비해야 합니다.

• 먼저 첫 번째 시트에 병합된 셀에 큰 글씨체로 제품 가격표 제목을 입력하고, 그 아래 필요한 헤더 필드를 작성합니다.
• 제목 아래 제품(Product) 열과 가격(Price) 열을 만듭니다.
• 제품 열에는 제품 이름을, 가격 열에는 해당 제품의 가격을 순서대로 입력합니다.
• 그러면 첫 번째 데이터 범위는 아래와 같은 모습이 됩니다.

• 다음으로 SalesRep(영업사원) 열과 Region(지역) 열로 구성된 영업사원 정보 데이터셋을 새 시트에 만듭니다.

• 마지막으로 세 번째 시트에 월별 판매 기록 데이터셋을 구성합니다. 여기서는 Date(날짜), SalesRep, Product, Unit(수량) 열을 사용해 판매 내역을 기록했습니다.

이렇게 총 3개의 데이터 범위를 하나의 통합 문서 안에 서로 다른 시트에 만들었습니다.

2단계: 표(Table) 삽입하기

두 번째 단계에서는 일반 데이터 범위를 표로 변환하고 이름을 지정합니다.

• 먼저 셀 B4를 선택합니다. 데이터 범위(B4:C10) 안의 아무 셀이나 선택해도 됩니다.
• 상단의 삽입 탭으로 이동합니다.
• 테이블 그룹에서 표 옵션을 선택합니다. 단축키 Ctrl + T를 눌러도 같은 결과를 얻을 수 있습니다.

• 표 만들기 입력창이 열리면,
• '머리글 포함' 항목에 체크되어 있는지 확인하고,
• 확인을 클릭합니다.

• 표가 생성되면 테이블 디자인 탭으로 이동합니다.
• 테이블 스타일 옵션 그룹에서 필터 단추의 체크를 해제합니다.

• 이제 속성 그룹의 테이블 이름 상자를 선택하고 이름을 수정합니다. 여기서는 Product라고 지정했습니다.

• 첫 번째 표가 아래와 같이 완성되었습니다.

• 같은 방법으로 나머지 두 데이터셋도 각각 SalesRep, Sales라는 이름의 표로 변환합니다.

3단계: 데이터 모델에 추가하기

이 단계에서는 만든 표들을 데이터 모델에 추가합니다.

• 먼저 표 안의 아무 셀이나 선택합니다. 여기서는 셀 B4를 선택했습니다.
• Power Pivot 탭으로 이동합니다.
• 테이블 그룹에서 데이터 모델에 추가를 선택합니다.

• 잠시 후 Excel용 Power Pivot 창이 열리고,
• 추가된 표가 표 이름과 동일한 탭에 나타나는 것을 확인할 수 있습니다.

• 같은 방법으로 나머지 두 개의 표도 Power Pivot에 추가합니다.

4단계: 데이터 모델 관리하기

이제 본격적으로 데이터 모델을 관리하는 단계입니다.

• 먼저 보기 그룹에서 다이어그램 보기(Diagram View)를 선택합니다.

• Product 테이블의 Product 필드를 클릭한 상태로 마우스를 끌어 Sales 테이블의 Product 필드에 놓습니다.

• 이렇게 하면 두 테이블 사이에 일대다(One-to-Many) 관계가 생성됩니다.

• 동일한 방식으로 SalesRep 테이블과 Sales 테이블 사이에도 관계를 설정합니다.

• 이후 보기 그룹에서 데이터 보기(Data View)를 선택하면 이전 화면으로 돌아갈 수 있습니다.

• 이제 Sales 테이블의 Unit 열 오른쪽에 새 열 Sales Amount(판매액)를 추가합니다. 이 열에는 '단가 × 판매 수량'으로 계산된 판매 금액이 들어갑니다.

• Sales Amount 열의 첫 번째 셀을 선택한 뒤, 수식 입력줄에 아래 수식을 입력합니다.

=RELATED('Product'[Price])*Sales[Unit]

• Enter 키를 누르면 RELATED 함수가 설정해 둔 관계를 통해 Product 테이블의 가격을 불러와 판매액을 자동 계산합니다.

• 이번에는 아래 이미지에 표시된 위치(측정값 영역)의 셀을 선택하고 다음 수식을 붙여넣습니다.

Total Amount:=SUM(Sales[Amount])

• Enter 키를 누르면 총 판매액을 집계하는 측정값(Measure)이 완성됩니다.

5단계: 피벗 테이블(PivotTable) 만들기

마지막 단계입니다. 이렇게 구축한 데이터 모델을 활용해 실제 리포트를 만들어 보겠습니다.

• Power Pivot 창의 PivotTable 그룹을 클릭합니다.

• 피벗 테이블 만들기 입력창이 열리면 '기존 워크시트'를 선택합니다.
• 위치(Location)로 Sales 워크시트의 셀 G4를 지정합니다.
• 확인을 클릭합니다.

• 활성 워크시트에 피벗 테이블 영역이 즉시 나타납니다.

• 피벗 테이블 영역을 클릭하면 피벗 테이블 필드 작업창이 열립니다.
• 아래 그림처럼 Product 필드를 행 영역으로, fx Total Amount 필드를 값 영역으로 끌어다 놓습니다.

• 이렇게 하면 제품별 판매액 리포트가 완성됩니다.

• 다시 피벗 테이블 필드 작업창으로 돌아가,
• SalesRep 테이블의 Region 필드를 행 영역으로 드래그합니다.

• 이제 지역별 · 제품별 판매액 리포트가 완성됩니다. 피벗 테이블 하단에서 총합계(Grand Total)도 함께 확인할 수 있습니다.

같은 방식으로 원하는 요소를 더 추가할 수 있습니다. 예를 들어 영업사원별 판매액 리포트도 손쉽게 만들 수 있습니다.

마무리

이 글에서는 엑셀에서 데이터 모델을 관리하는 방법을 쉽고 간결하게 정리했습니다. 데이터셋 준비부터 표 변환, Power Pivot에 추가, 관계 설정, 그리고 피벗 테이블 리포트 생성까지 전 과정을 다뤘으니 실무에 바로 적용해 보세요. 궁금한 점이나 제안 사항이 있다면 댓글로 알려주세요.

함께 보면 좋은 글

• 엑셀 데이터 모델에서 표 제거하는 방법 (2가지 빠른 방법)
• 엑셀 피벗 테이블에서 데이터 모델 제거하기 (쉬운 단계별 가이드)