VLOOKUP을 사용하여 한 테이블에서 다른 테이블로 열을 가져온 적이 있나요? 이제 Excel에 기본 제공 데이터 모델이 포함되어 있으므로 VLOOKUP을 더 이상 사용할 필요가 없습니다. 각 테이블의 일치하는 데이터를 기반으로 두 데이터 테이블 간의 관계를 만들 수 있습니다. 그리고 나서 Power View 시트를 만들 수 있고, 여러 원본에서 테이블을 가져온 경우에도 각 테이블의 필드를 사용하는 피벗 테이블 및 기타 보고서를 작성할 수 있습니다. 예를 들어 고객 판매 데이터가 있는 경우 판매 패턴을 연도별 및 월별로 분석하기 위해 시간 인텔리전스 데이터를 가져와 관계를 만들 수 있습니다.
통합 문서의 모든 테이블은 피벗 테이블 및 Power View 필드 목록에 나열되어 있습니다.
관계형 데이터베이스에서 관련 테이블을 가져오는 경우 Excel에서는 흔히 백그라운드에서 작성 중인 데이터 모델에 해당 관계를 만들 수 있습니다. 다른 모든 경우에는 관계를 수동으로 만들어야 합니다.
-
통합 문서에 여러 개의 테이블이 포함되어 있고 각 테이블에 다른 테이블의 열에 매핑될 수 있는 열이 있는지 확인합니다.
-
다음 중 하나를 선택합니다.데이터 서식을 테이블로 지정하거나 외부 데이터를 새 워크시트의 테이블로 가져오기합니다.
-
각 테이블에 의미 있는 이름을 지정합니다. 표 도구에서 디자인 > 표 이름을 클릭하고 이름을 입력합니다.
-
테이블 중 하나의 열이 중복되지 않고 고유한 데이터 값을 가지고 있는지 확인합니다. Excel에서는 하나의 열이 고유한 값을 포함하는 경우에만 관계를 만들 수 있습니다.
예를 들어 고객 판매와 시간 인텔리전스의 관계를 설정하려면 두 테이블 모두에 동일한 형식(예: 2012-01-01)의 날짜가 포함되어 있어야 하며 적어도 한 테이블(시간 인텔리전스)의 열에 각 날짜가 한번씩 나열되어 있어야 합니다.
-
데이터 > 관계를 클릭합니다.
통합 문서에 테이블이 하나밖에 없는 경우에는 관계가 회색으로 표시됩니다.
-
관계 관리 상자에서 새로 만들기를 클릭합니다.
-
관계 만들기 상자에서 테이블의 화살표를 클릭하고 목록에서 테이블을 선택합니다. 이 테이블은 일대다 관계의 "다" 쪽에 있어야 합니다. 고객 및 시간 인텔리전스 예제를 사용하는 경우 날짜에 관계없이 다수의 판매가 발생할 수 있으므로 고객 판매 테이블을 먼저 선택합니다.
-
열(외래)에서 관련 열(기본)과 연관된 데이터가 있는 열을 선택합니다. 예를 들어 두 테이블에 날짜 열이 있는 경우 날짜 열을 선택합니다.
-
관련 표에서는 방금 전에 테이블에서 선택한 테이블과 관련된 데이터 열이 하나 이상 있는 테이블을 선택합니다.
-
관련 열(기본)에서는 열에서 선택한 열의 값과 일치하는 고유의 값이 있는 열을 선택합니다.
-
확인을 클릭합니다.
Excel에서 테이블 간의 관계에 대한 자세한 정보
관계에 대한 참고 사항
-
여러 테이블에서 필드를 피벗 테이블 필드 목록으로 끌어 놓을 때 관계가 있는지 여부를 알 수 있습니다. 관계를 만들라는 메시지가 표시되지 않는 경우에는 Excel에 데이터 관계를 만드는 데 필요한 관계 정보가 이미 있는 것입니다.
-
관계 만들기는 VLOOKUP 사용과 유사합니다. 일치하는 데이터가 포함된 열이 Excel 테이블에서 다른 테이블의 행을 상호 참조할 수 있도록 해야 합니다. 시간 인텔리전스 예제에서 고객 테이블에는 시간 인텔리전스 테이블에도 있는 날짜 값이 필요합니다.
-
데이터 모델에서 테이블 관계는 일대일(예: 승객마다 탑승권이 하나씩 있음)이나 일대다(예: 비행기에 여러 승객이 있음)일 수 있지만 다대다일 수는 없습니다. 다대다 관계의 경우 “순환 종속성이 감지되었습니다.” 등의 순환 종속성 오류가 발생합니다. 이 오류는 다대다 관계의 두 테이블을 직접 연결하거나 간접적으로 연결(각 관계 안에서는 일대다가 성립되지만 전체를 봤을 때는 다대다 관계인 일련의 테이블 관계)한 경우 발생합니다. 관계에 대한 자세한 내용은 데이터 모델의 테이블 간 관계를 참조하세요.
-
두 열에 있는 데이터 형식은 서로 호환되어야 합니다. 자세한 내용은 Excel 데이터 모델의 데이터 형식을 참조하세요.
-
특히 어떤 열을 사용해야 할지 잘 모르는 경우 보다 직관적인 방식으로 관계를 만드는 다른 방법이 있습니다. Power Pivot의 다이어그램 뷰에서 관계 만들기를 참조하세요.
예제: 항공사 비행 데이터와 시간 인텔리전스 데이터의 관계 지정
마켓플레이스에서 무료 데이터를 사용하여 테이블 관계와 시간 인텔리전스에 Microsoft Azure 있습니다. 이러한 데이터 세트 중 일부는 매우 크고, 적절한 기간에 데이터 다운로드를 완료하기 위해 빠른 인터넷 연결이 필요합니다.
-
Microsoft Excel에서 Power Pivot 추가 기능을 시작하고 Power Pivot 창을 엽니다.
-
외부 데이터 가져오기 > 데이터 서비스 > Microsoft Azure Marketplace에서를 클릭합니다. 테이블 가져오기 마법사에서 Microsoft Azure Marketplace 홈 페이지가 열립니다.
-
Price(가격)에서 Free(무료)를 클릭합니다.
-
Category(범주)에서 Science & Statistics(과학 및 통계)를 클릭합니다.
-
DateStream을 찾아 Subscribe(구독)을 클릭합니다.
-
Microsoft 계정을 입력하고 로그인을 클릭합니다. 데이터 미리 보기가 창에 표시됩니다.
-
아래쪽으로 스크롤하여 Select Query(쿼리 선택)를 클릭합니다.
-
다음을 클릭합니다.
-
BasicCalendarUS를 선택한 다음 마침을 클릭하여 데이터를 가져옵니다. 고속 인터넷 연결을 사용할 경우 일 분 안에 가져오기가 완료됩니다. 가져오기를 마치면 73,414개 행이 전송되었다는 상태 보고서가 표시됩니다. 닫기를 클릭합니다.
-
외부 데이터 가져오기 > 데이터 서비스 > Microsoft Azure Marketplace에서를 클릭하여 두 번째 데이터 집합을 가져옵니다.
-
Type(형식)에서 Data(데이터)를 클릭합니다.
-
Price(가격)에서 Free(무료)를 클릭합니다.
-
US Air Carrier Flight Delays를 찾아 Select(선택)를 클릭합니다.
-
아래쪽으로 스크롤하여 Select Query(쿼리 선택)를 클릭합니다.
-
다음을 클릭합니다.
-
마침을 클릭하여 데이터를 가져옵니다. 고속 인터넷 연결을 사용할 경우 가져오기에 15분 정도 걸릴 수 있습니다. 가져오기를 마치면 2,427,284개 행이 전송되었다는 상태 보고서가 표시됩니다. 닫기를 클릭합니다. 이제 데이터 모델에 두 개의 테이블이 포함되어 있습니다. 두 테이블 간에 관계를 만들려면 각 테이블에 호환되는 열이 있어야 합니다.
-
BasicCalendarUS의 DateKey의 형식은 2012-01-01 오전 12:00:00입니다. On_Time_Performance 테이블에도 날짜/시간 열인 FlightDate가 있으며 해당 값도 2012-01-01 오전 12:00:00와 동일한 형식으로 지정되어 있습니다. 두 열에는 데이터 형식이 동일한 일치하는 데이터가 있으며 적어도 하나의 열(DateKey)에는 고유한 값만 있습니다. 이후 진행할 여러 단계에서 해당 열을 사용해 테이블 관계를 설정하겠습니다.
-
파워 피벗 창에서 피벗 테이블을 클릭하여 새 워크시트 또는 기존 워크시트에서 피벗 테이블을 만듭니다.
-
필드 목록에서 On_Time_Performance를 확장하고 ArrDelayMinutes를 클릭해 값 영역에 추가합니다. 피벗 테이블에 비행기가 지연되는 총 시간이 분 단위로 표시됩니다.
-
BasicCalendarUS를 확장하고 MonthInCalendar를 클릭해 행 영역에 추가합니다.
-
피벗 테이블에 월이 나열되는데 시간(분)의 총합이 월별로 동일하게 표시됩니다. 반복되는 동일한 값이 있다는 것은 관계가 필요하다는 의미입니다.
-
필드 목록의 "테이블 사이에 관계가 필요할 수 있습니다."에서 만들기를 클릭합니다.
-
관련 표에서 On_Time_Performance를, 관련 열(기본)에서 FlightDate를 선택합니다.
-
테이블에서 BasicCalendarUS를, 열(외래)에서 DateKey를 선택합니다. 확인을 클릭하여 관계를 만듭니다.
-
지연 시간(분)의 총합이 월마다 다르게 표시됨을 확인할 수 있습니다.
-
BasicCalendarUS에서 YearKey를 MonthInCalendar 위의 행 영역에 끌어다 놓습니다.
이제 도착 지연을 연도 및 월별 또는 달력의 기타 값으로 분류할 수 있습니다.
팁: 기본적으로 월은 사전순으로 나열됩니다. 파워 피벗 추가 기능을 사용하면 월을 시간순으로 나타내도록 정렬을 변경할 수 있습니다.
-
파워 피벗 창에 BasicCalendarUS 테이블이 열려 있어야 합니다.
-
홈 탭에서 열 기준 정렬을 클릭합니다.
-
정렬에서 MonthInCalendar를 선택합니다.
-
기준에서 MonthOfYear를 선택합니다.
피벗 테이블에서 각 월-연도 조합(October 2011, November 2011)이 특정 연도의 월 숫자(10, 11)를 기준으로 정렬됩니다. 이 시나리오에 필요한 모든 열을 DateStream 피드에서 제공하므로 정렬 순서를 쉽게 변경할 수 있습니다. 다른 시간 인텔리전스 테이블을 사용하는 경우 단계가 다를 수 있습니다.
"테이블 사이에 관계가 필요할 수 있습니다."
피벗 테이블에 필드를 추가할 때 피벗 테이블에서 선택한 필드에 테이블 관계가 필요하다는 메시지가 표시될 수 있습니다.
![관계가 필요하면 [만들기] 단추가 표시됩니다.](https://support.content.office.net/ko-kr/media/21daf83c-8ee5-483b-971c-7c960da8f1b4.png)
관계가 필요한 경우 Excel에서는 알림을 표시할 수는 있지만 사용할 테이블 및 열이나 테이블 관계가 가능한지 여부는 알 수 없습니다. 이때 다음 단계를 수행하면 필요한 답을 얻을 수 있습니다.
1단계: 관계에 지정할 테이블 결정
모델에 테이블이 몇 개 없다면 사용해야 하는 테이블을 즉각적으로 파악할 수 있습니다. 하지만 모델의 크기가 클 경우에는 약간의 도움이 필요할 수 있습니다. 한 가지 방법은 Power Pivot 추가 기능에서 다이어그램 뷰를 사용하는 것입니다. 다이어그램 뷰는 데이터 모델의 모든 테이블을 시각적으로 표현합니다. 다이어그램 뷰를 사용하면 어떤 테이블이 모델의 나머지 테이블과 분리되어 있는지 빠르게 파악할 수 있습니다.
참고: 피벗 테이블 또는 Power View 보고서에서 사용하기에 적절하지 않은 모호한 관계가 만들어질 수 있습니다. 모든 테이블이 모델의 다른 테이블과 어떤 식으로든 관련되어 있지만 서로 다른 테이블의 필드를 결합하려고 하면 "테이블 사이에 관계가 필요할 수 있습니다."라는 메시지가 나타난다고 가정하겠습니다. 이러한 메시지는 주로 다대다 관계로 실행할 때 많이 나타납니다. 일련의 테이블 관계를 따라 사용하려는 테이블에 연결하면 일대다 테이블 관계가 두 개 이상 생길 확률이 높습니다. 모든 상황에 적용할 수 있는 간단한 해결책은 없지만 계산된 열을 만들어 사용하려는 열을 한 테이블로 통합해 해결할 수 있습니다.
2단계: 테이블 간 경로를 만드는 데 사용할 수 있는 열 찾기
모델의 다른 테이블과 연결되지 않은 테이블을 식별한 후 해당 열을 검토하여 모델의 다른 테이블에 있는 열에 일치하는 값이 있는지 확인합니다.
예를 들어 지역별 제품 판매를 포함하는 모델이 있으며 각 지역의 판매와 인구 통계 추세에 상관 관계가 있는지 알아 보기 위해 나중에 인구 통계 데이터를 가져왔다고 가정하겠습니다. 다른 데이터 원본에서 인구 통계 데이터를 가져왔기 때문에 처음에는 인구 통계 테이블이 모델의 다른 테이블과 연결되어 있지 않습니다. 인구 통계 데이터를 모델의 다른 테이블들과 통합하려면 인구 통계 테이블 중 하나에서 이미 사용 중인 열에 해당하는 열을 찾아야 합니다. 인구 통계 데이터가 지역별로 구성되어 있으며 판매 데이터에서 판매가 발생한 지역을 지정하는 경우 도시, 우편 번호 또는 지역 등의 공통된 열을 찾으면 두 데이터 집합을 서로 연결하여 조회할 수 있습니다.
일치하는 값 외에도 관계를 만드는 데 필요한 추가 사항이 몇 가지 있습니다.
-
조회 열의 데이터 값은 고유해야 합니다. 즉, 열에 중복 값이 포함될 수 없습니다. 데이터 모델에서 Null 및 빈 문자열은 빈 칸에 해당하여 별도의 데이터 값으로 인정됩니다. 따라서 조회 열에는 여러 개의 Null이 있을 수 없습니다.
-
원본 열 및 조회 열의 데이터 형식은 호환되어야 합니다. 데이터 형식에 대한 자세한 내용은 데이터 모델의 데이터 형식을 참조하세요.
테이블 관계에 대한 자세한 내용은 데이터 모델의 테이블 간 관계를 참조하세요.
