엑셀에서 여러 테이블을 가져오면 테이블 간의 관계를 자동으로 감지하고 정의해 주기 때문에 일일이 수동으로 만들 필요가 없습니다. 하지만 가끔 데이터 모델 관계가 제대로 작동하지 않는 경우가 발생하는데, 이는 다양한 원인 때문일 수 있습니다. 이 글에서는 엑셀 데이터 모델 관계 오류를 해결할 수 있는 실질적인 방법들을 소개합니다.
데이터 모델 관계 개요
데이터 모델 관계(Data Model Relationship)란 서로 다른 두 개의 데이터 테이블을 연결하는 관계를 의미합니다. 테이블 간에 관계를 설정하면 데이터 분석 능력이 한층 강화되고, 여러 곳에 흩어진 데이터를 하나의 구조 안에서 체계적으로 추적할 수 있습니다. 이러한 관계는 사용자가 직접 만들 수도 있고, 여러 테이블을 가져올 때 엑셀이 자동으로 감지하도록 할 수도 있습니다. 또한 Power Pivot 추가 기능을 활용해서도 데이터 모델 관계를 만들 수 있습니다.
데이터 모델에는 두 가지 유형의 관계가 있습니다. 일대일(One-to-One) 관계와 일대다(One-to-Many) 관계입니다.
일대일(One-to-One) 관계는 하나의 항목과 특정 하나의 항목이 연결되는 관계입니다. 예를 들어 고객과 고객 할인율의 관계가 대표적입니다. 특정 고객에게는 특정 할인율이 적용되기 때문입니다.
일대다(One-to-Many) 관계는 하나의 항목이 여러 항목과 연결되는 관계입니다. 예를 들어 고객과 주문의 관계가 이에 해당합니다. 한 명의 고객이 한 번에 여러 건의 주문을 넣을 수 있기 때문입니다.
반면 다대다(Many-to-Many) 관계는 데이터 모델에서 지원되지 않습니다. 예를 들어 고객과 상품의 관계를 생각해 보면, 한 고객이 여러 상품을 구매할 수 있고 동시에 하나의 상품을 여러 고객이 구매할 수 있습니다. 이런 유형의 관계는 데이터 모델에서 직접 표현할 수 없습니다.
두 테이블 사이에는 여러 유형의 관계가 존재할 수 있지만, 엑셀에서 관계를 생성하려면 한 테이블에서 다른 테이블로 이어지는 단일 경로가 반드시 있어야 합니다. 즉, 한 번에 하나의 관계만 활성화되며, 필요에 따라 비활성(inactive) 관계를 선택하여 사용할 수 있습니다.
엑셀 데이터 모델 관계 오류 해결 방법 6가지
엑셀 데이터 모델 관계가 작동하지 않는 문제를 해결하기 위해 6가지 검증된 방법을 준비했습니다. 각 솔루션은 상황에 따라 효과적으로 적용할 수 있으니, 아래 방법들을 차례로 시도해 보고 자신에게 맞는 해결책을 찾아보세요. 데이터 모델 관계는 수동으로 만드는 방법과 엑셀의 자동 감지 기능을 활용하는 방법 두 가지가 있는데, 각각의 과정에서 서로 다른 문제가 발생할 수 있습니다.
방법 1: 테이블 간 관계 수동으로 만들기
기존 피벗 테이블(Pivot Table)의 값 영역에 필드를 끌어다 놓으면 관계가 필요하다는 알림 메시지가 나타날 수 있습니다. 관계는 기본적으로 기본 키(primary key) 열과 이름이 같은 외래 키(foreign key)를 기준으로 형성됩니다. 열 이름만으로 관계를 판단할 수 없다면 테이블 간의 관계를 수동으로 만들어야 합니다. 참고로 기본 키는 테이블 내 행(row)을 고유하게 식별하는 키이고, 외래 키는 다른 테이블의 고유한 열을 참조하는 키를 말합니다. 따라서 먼저 기본 키 열과 이름이 같은 외래 키가 있는지 확인하고, 없다면 '관계 없음' 메시지가 뜨게 되므로 이 경우 직접 관계를 생성해야 합니다.
방법 2: 지원되지 않는 데이터 형식 수정하기
'관계를 찾을 수 없음' 메시지가 나타나거나 데이터 모델 관계가 작동하지 않는 또 다른 원인은 데이터 형식이 호환되지 않기 때문일 수 있습니다. 피벗 테이블에 사용된 테이블들의 데이터 형식이 서로 다르면 관계가 생성되지 않습니다. 이 경우 관계 만들기 대화 상자를 열어 피벗 테이블의 활성 테이블들 사이에 관계를 직접 설정하거나, 각 테이블의 데이터 형식을 동일하게 변환하여 관계가 잘 형성되도록 조치해야 합니다.
방법 3: 테이블 간 유효한 관계가 있는지 확인하기
피벗 테이블에 추가한 테이블들이 서로 논리적인 관계가 전혀 없는 경우도 있습니다. 예를 들어 부분적으로만 연결된 두 테이블을 삽입했는데 실제로 유효한 관계가 존재하지 않는다면, 엑셀의 자동 감지 기능도 올바른 결과를 반환하지 못합니다. 이런 상황에서는 서로 논리적인 연관성이 있는 테이블만 삽입해야 하며, 그렇지 않으면 데이터 모델 관계가 작동하지 않는 상태가 계속됩니다. 물론 이 경우에도 피벗 테이블에서 결과를 도출할 수는 있지만, 필드들이 의미 있는 결론을 제공하지 못한다는 점에 유의하세요.
방법 4: 브리지 테이블(Bridge Table) 만들기
데이터 모델 관계 문제를 해결하는 또 다른 방법은 브리지 테이블을 활용하는 것입니다. 브리지 테이블은 작동하지 않는 테이블과 사용하려는 테이블 사이를 연결해 주는 역할을 하며, 팩트 테이블과 문제가 되는 테이블 사이의 간격을 더 낮은 세부 수준(grain)에서 메워주는 테이블이라고 정의할 수 있습니다.
예를 들어 고객(Customer)과 상품(Product)이라는 두 개체가 있다고 가정해 보겠습니다. 이 둘을 연결하는 관계를 만들려면 매핑 과정에서 고객과 상품을 각각 별도의 테이블에 배치해야 합니다. 이 관계는 다대다(Many-to-Many) 관계이므로 두 테이블의 외래 키만으로는 표현할 수 없으며, 반드시 자체 테이블이 필요합니다. 바로 이때 브리지 테이블이 사용됩니다. 브리지 테이블은 서로 연결되는 두 테이블의 기본 키를 결합한 고유한 기본 키를 가집니다. 즉, 상품 테이블의 기본 키가 ProductID이고 고객 테이블의 기본 키가 CustomerID라면, 브리지 테이블의 기본 키는 ProductID와 CustomerID의 조합이 됩니다.
방법 5: 피벗 테이블 값 영역에 필드 추가하기
자동 관계 감지 기능은 측정값(measure)에만 적용됩니다. 피벗 테이블의 행 및 열 레이블에 사용하는 계산 필드에는 적용되지 않습니다. 따라서 여러 테이블을 삽입하여 피벗 테이블을 만들 때 서로 관련 없는 테이블이 포함될 수 있는데, 자동 관계 감지기를 통해 관계를 얻으려면 반드시 하나의 필드를 값(Value) 영역에 배치해야 합니다. 그렇지 않으면 엑셀 데이터 모델 관계가 작동하지 않는 상태가 나타납니다.
방법 6: 자동 감지로 잘못된 관계 찾아 수정하기
자동 감지 기능으로 관계를 생성하면, 감지 알고리즘이 테이블 값을 기반으로 가능한 모든 관계를 만든 뒤 확률에 따라 순위를 매기고, 가장 가능성 높은 관계를 최종적으로 생성합니다. 그런데 테이블에 키로 사용할 수 있는 열이 여러 개 포함되어 있다면, 일부 관계의 순위가 다른 것보다 낮게 매겨져 의도와 다른 잘못된 관계가 만들어지는 경우가 많습니다. 따라서 자동 감지 결과가 요구 사항에 맞지 않는 관계를 만들었다면 해당 관계를 삭제하고 수동으로 다시 생성하는 것이 좋습니다.
마무리
지금까지 엑셀 데이터 모델 관계가 작동하지 않는 문제를 해결하는 6가지 효과적인 방법을 살펴보았습니다. 모두 실제로 적용하기 쉬운 방법들이니, 데이터 모델 관계를 만들다가 예상과 다른 결과를 마주했다면 위 내용을 하나씩 점검해 보세요. 더 궁금한 점이 있다면 댓글로 남겨주세요.
함께 보면 좋은 글
- 엑셀에서 데이터 모델로 데이터 가져오는 방법 (2가지 쉬운 방법)
- 엑셀에서 데이터 모델 활용하기 (3가지 예제)
- 엑셀 데이터 모델에서 테이블 제거하는 방법 (2가지 빠른 팁)