데이터 모델(Data Model)은 데이터 분석에서 필수적인 기능입니다. 데이터 모델을 사용하면 테이블 같은 데이터를 엑셀의 메모리에 불러온 뒤, 공통 열을 기준으로 데이터를 연결하도록 지정할 수 있습니다. 각 테이블 간의 관계를 '모델'이라는 단어로 설명하기 때문에 '데이터 모델'이라고 부릅니다. 엑셀은 이러한 데이터 모델을 만드는 다양한 방법을 제공하는데, 이 글에서는 그중 가장 유용한 3가지 방법을 자세히 살펴보겠습니다.
엑셀에서 데이터 모델을 만드는 3가지 방법
이번 글에서는 엑셀에서 데이터 모델을 생성하는 세 가지 방법을 다룹니다. 첫 번째는 관계(Relationships) 도구를 사용하는 방법, 두 번째는 파워 쿼리(Power Query)를 활용하는 방법, 마지막으로 파워 피벗(Power Pivot) 도구를 이용하는 방법입니다. 아래 샘플 데이터셋을 예시로 각 방법을 하나씩 설명드리겠습니다.
1. 관계(Relationships) 도구 활용하기
첫 번째 방법은 엑셀 리본 메뉴의 관계(Relationships) 도구를 사용하는 것입니다. 이 도구를 이용하면 동일한 데이터를 담고 있는 공통 열을 기준으로 두 테이블을 연결할 수 있습니다.
단계별 진행 방법:
- 먼저 데이터셋 안의 임의의 셀 하나를 선택합니다.
- 리본 메뉴에서 삽입 탭으로 이동합니다.
- 삽입 탭에서 테이블 명령을 클릭합니다.
- 테이블 만들기 대화상자가 나타나면 전체 데이터 범위가 테이블 데이터로 선택되어 있는지 확인합니다.
- 그다음 확인을 누릅니다.
- 이제 테이블 디자인 탭으로 이동합니다.
- 새로 만든 테이블에 이름을 지정합니다.
- 이렇게 하면 이후 단계에서 해당 테이블을 쉽게 구분할 수 있습니다.
- 마지막으로 나머지 데이터셋에도 동일한 과정을 반복해 모두 테이블로 변환합니다.
- 그다음 리본 메뉴에서 데이터 탭으로 이동합니다.
- 데이터 도구 그룹에서 관계 도구를 선택합니다.
- 그러면 새 창이 열립니다.
- 관계 관리 창에서 새로 만들기를 클릭합니다.
- 그러면 관계 만들기 대화상자가 화면에 나타납니다.
- 대화상자에서 먼저 분석하려는 테이블을 선택합니다. 이번 예시에서는 Sales(매출) 테이블입니다.
- 두 테이블에 공통으로 존재하는 열을 열(외래)로 선택합니다. 이 열에는 중복 값이 포함될 수 있습니다. 여기서는 ID 열입니다.
- 다음으로 조회 기준이 될 테이블을 관련 테이블로 지정합니다. 앞서 선택한 테이블과 연관된 값을 찾아오는 역할을 하는 테이블입니다. 이번 예시에서는 Executives(임원) 테이블입니다.
- 그런 다음 공통 열을 관련 열(기본)로 선택합니다. 이 열에는 반드시 중복되지 않은 고유 값만 있어야 합니다. 여기서도 ID 열입니다.
- 마지막으로 확인을 클릭합니다.
- 그러면 생성된 관계를 보여주는 창이 나타납니다. 확인을 눌러 관계 설정을 완료합니다.
- 이후 삽입 탭으로 이동하여 피벗 테이블을 클릭합니다.
- 드롭다운 목록에서 외부 데이터 원본 사용을 선택합니다.
- 외부 데이터 원본의 피벗 테이블 창에서 연결 선택을 클릭합니다.
- 기존 연결 창에서 먼저 테이블 항목으로 이동합니다. 앞서 관계 도구로 연결했던 두 개의 테이블이 목록에 표시되어 있는 것을 확인할 수 있습니다.
- 통합 문서 데이터 모델의 테이블을 선택합니다.
- 마지막으로 열기를 클릭합니다.
- 외부 데이터 원본의 피벗 테이블 창으로 돌아와 새 워크시트를 선택합니다.
- 그런 다음 이 데이터를 데이터 모델에 추가 옵션에 체크합니다.
- 마지막으로 확인을 누릅니다.
- 결과적으로 데이터 모델을 기반으로 한 피벗 테이블이 생성된 것을 확인할 수 있습니다.
- 여기서 두 테이블을 서로 연관 지어 분석할 수 있습니다. 예를 들어 Executives 테이블에서 임원 이름을 선택한 뒤 Sales 테이블에서 해당 인물의 매출 실적을 조회하는 식입니다.
- 두 테이블이 데이터 모델로 연결되어 있기 때문에 가능한 것입니다.
2. 파워 쿼리(Power Query) 활용하기
두 번째 방법은 엑셀의 파워 쿼리(Power Query)를 사용하는 것입니다. 파워 쿼리를 이용하면 두 개 이상의 테이블을 연결해 데이터 모델을 손쉽게 구축할 수 있습니다.
단계별 진행 방법:
- 먼저 데이터셋 내 임의의 셀을 선택합니다.
- 리본 메뉴에서 삽입 탭을 클릭합니다.
- 삽입 탭에서 테이블 명령을 선택합니다.
- 테이블 만들기 대화상자에서 전체 데이터 범위가 테이블 데이터로 지정되었는지 확인합니다.
- 그다음 확인을 누릅니다.
- 세 번째로 리본 메뉴에서 테이블 디자인 탭을 선택합니다.
- 새로 생성한 테이블에 이름을 부여합니다.
- 이렇게 해두면 이후 단계에서 테이블을 식별하기 훨씬 수월합니다.
- 마지막으로 남은 데이터셋에도 같은 절차를 반복해 테이블을 만듭니다.
- 이제 리본 메뉴에서 데이터 탭을 선택합니다.
- 테이블/범위에서를 클릭합니다.
- 그러면 파워 쿼리 창이 열립니다.
- 파워 쿼리 편집기에서 먼저 홈 탭을 선택합니다.
- 그런 다음 닫기 및 로드 옵션을 클릭합니다.
- 드롭다운 목록에서 닫기 및 로드 대상...을 선택합니다.
- 그러면 데이터 가져오기 창이 화면에 나타납니다.
- 데이터 가져오기 대화상자에서 연결만 만들기 옵션을 선택합니다.
- 그리고 이 데이터를 데이터 모델에 추가 항목에 체크합니다.
- 마지막으로 확인을 클릭합니다. 나머지 테이블에도 동일한 과정을 반복합니다.
- 이후 리본 메뉴에서 데이터 탭으로 이동합니다.
- 데이터 도구 그룹을 찾습니다.
- 데이터 모델 관리 도구를 선택합니다. 그러면 새 창이 열립니다.
- 새 창에서 홈 옵션을 선택합니다.
- 그다음 보기 탭으로 이동합니다.
- 마지막으로 다이어그램 보기를 선택합니다.
- 그러면 데이터가 다이어그램 형태로 표시됩니다. 다이어그램 보기에서는 각 테이블이 그림으로 보여집니다.
- 이제 두 테이블의 공통 열을 드래그해 서로 연결합니다. 이번 예시에서는 ID 열입니다.
- 연결선을 보면 한쪽에는 1, 반대쪽에는 별표(*) 표시가 있는 것을 확인할 수 있습니다. 이는 두 테이블이 일대다(One to Many) 관계임을 의미합니다.
- 1은 Executive ID 열에 중복 값이 없다는 뜻이며, 반면 Sales 테이블의 ID 열에는 중복 값이 있다는 의미입니다.
- 이후 삽입 탭으로 이동합니다.
- 피벗 테이블을 선택합니다.
- 드롭다운 목록에서 데이터 모델에서를 클릭합니다.
- 화면에 나타난 대화상자에서 먼저 새 워크시트를 선택합니다.
- 그다음 확인을 누릅니다.
- 결과적으로 데이터 모델을 활용해 피벗 테이블이 생성됩니다.
- 여기서 두 테이블을 연관 지어 분석할 수 있습니다. 예를 들어 Executives 테이블에서 임원 이름을 선택한 후 Sales 테이블에서 그들의 매출 실적을 확인할 수 있습니다.
- 데이터 모델이 두 테이블을 연결해 주기 때문에 가능한 일입니다.
3. 파워 피벗(Power Pivot) 활용하기
세 번째 방법은 파워 피벗(Power Pivot) 도구를 사용하는 것입니다. 파워 피벗을 이용하면 두 테이블을 공통 열로 연결해 데이터 모델을 만들 수 있습니다.
단계별 진행 방법:
- 먼저 데이터셋에서 임의의 셀 하나를 선택합니다.
- 리본 메뉴에서 삽입 탭을 클릭합니다.
- 삽입 탭에서 테이블 명령을 선택합니다.
- 테이블 만들기 대화상자에서 전체 데이터 범위를 테이블 데이터로 지정합니다.
- 그다음 확인을 클릭합니다.
- 세 번째 단계로 리본 메뉴의 테이블 디자인 탭을 선택합니다.
- 방금 만든 테이블에 이름을 지정합니다.
- 이후 단계에서 테이블을 찾는 데 도움이 됩니다.
- 마지막으로 남은 데이터셋에도 같은 방식으로 테이블을 생성합니다.
- 이제 파워 피벗 탭으로 이동합니다.
- 데이터 모델에 추가를 선택합니다.
- 파워 피벗 창이 열리면 먼저 홈 탭으로 이동합니다.
- 그런 다음 보기 옵션에서 다이어그램 보기를 선택합니다.
- 두 테이블 다이어그램에서 공통 열끼리 연결합니다. 이번 예시에서 공통 열은 ID입니다.
- 두 테이블은 일대다(One to Many) 관계로 연결됩니다.
- 이후 리본 메뉴에서 홈 탭으로 이동합니다.
- 피벗 테이블을 선택합니다.
- 피벗 테이블 만들기 대화상자에서 먼저 새 워크시트를 선택합니다.
- 그다음 확인을 클릭합니다.
- 그러면 두 테이블이 함께 담긴 피벗 테이블이 생성됩니다.
- 데이터 모델이 두 테이블을 연결해 주며, 덕분에 한 테이블의 값을 조회해 다른 테이블의 값과 연관 지어 표시할 수 있습니다.
마무리
이번 글에서는 엑셀에서 데이터 모델을 만드는 세 가지 방법을 꼼꼼하게 살펴보았습니다. 관계 도구, 파워 쿼리, 파워 피벗 중 상황에 맞는 방법을 선택하면 여러 테이블의 데이터를 하나로 연결해 더욱 체계적이고 세련된 방식으로 분석하고 시각화할 수 있습니다.
함께 읽으면 좋은 글
- 엑셀에서 데이터 모델에서 테이블 제거하는 방법 (2가지 빠른 팁)
- [해결됨!] 엑셀 데이터 모델 관계가 작동하지 않을 때 (6가지 해결책)
- 엑셀에서 데이터 모델 관리하는 방법 (쉬운 단계별 가이드)
- 엑셀에서 피벗 테이블에서 데이터 모델 제거하기 (간단한 방법)