Excel의 데이터 모델은 무엇입니까?
Excel의 데이터 모델은 두 개 이상의 테이블이 공통 또는 더 많은 데이터 시리즈를 통해 서로 관계를 맺고 데이터 모델 테이블과 다양한 다른 시트 또는 소스의 데이터가 결합하여 고유 한 모든 테이블의 데이터에 액세스 할 수있는 테이블.
설명
- 공통 열을 기반으로 관계를 생성하여 여러 테이블의 데이터를 통합 할 수 있습니다.
- 데이터 모델은 투명하게 사용되어 Excel의 피벗 테이블과 Excel의 피벗 차트에서 사용할 수있는 테이블 형식 데이터를 제공합니다. 테이블을 통합하여 Excel에서 피벗 테이블, Power Pivot 및 Power View를 사용하여 광범위한 분석을 수행 할 수 있습니다.
- 데이터 모델을 사용하면 데이터를 Excel의 메모리로로드 할 수 있습니다.
- 우리가 직접 볼 수없는 메모리에 저장됩니다. 그런 다음 Excel에 공통 열을 사용하여 데이터를 서로 연결하도록 지시 할 수 있습니다. 데이터 모델의 '모델'부분은 모든 테이블이 서로 관련되는 방식을 나타냅니다.
- 데이터 모델은 정보가 여러 테이블에있는 경우에도 필요한 모든 정보에 액세스 할 수 있습니다. 데이터 모델이 생성 된 후 Excel은 메모리에서 데이터를 사용할 수 있습니다. 메모리에있는 데이터를 사용하여 데이터에 여러 가지 방법으로 액세스 할 수 있습니다.
예
이 데이터 모델 Excel 템플릿은 여기에서 다운로드 할 수 있습니다. – 데이터 모델 Excel 템플릿예 1
영업 사원과 관련된 세 개의 데이터 세트가있는 경우 : 첫 번째는 매출 정보를 포함하고 두 번째는 영업 사원의 수입을 포함하고 세 번째는 영업 사원의 비용을 포함합니다.
이 세 가지 데이터 세트를 연결하고 이들과 관계를 만들기 위해 다음 단계에 따라 데이터 모델을 만듭니다.
- 데이터 세트를 Table 객체로 변환합니다.
일반 데이터 세트와는 관계를 만들 수 없습니다. 데이터 모델은 Excel 테이블 개체에서만 작동합니다. 이것을하기 위해:
- 1 단계 – 데이터 세트 내부를 클릭 한 다음 '삽입'탭을 클릭 한 다음 '테이블'그룹에서 '테이블'을 클릭합니다.
- 2 단계 – '내 테이블에 헤더가 있음'옵션을 선택 또는 선택 취소하고 확인을 클릭합니다.
- 3 단계 – 새 테이블을 선택한 상태에서 '도구'그룹의 '테이블 이름'에 테이블 이름을 입력합니다.
- 4 단계 – 이제 첫 번째 데이터 세트가 'Table'개체로 변환 된 것을 볼 수 있습니다. 다른 두 데이터 세트에 대해 이러한 단계를 반복하면 다음과 같이 'Table'개체로도 변환되는 것을 볼 수 있습니다.
데이터 모델에 '테이블'개체 추가 : 연결 또는 관계를 통해.
연결을 통해
- 하나의 테이블을 선택하고 '데이터'탭을 클릭 한 다음 '연결'을 클릭하십시오.
- 결과 대화 상자에 '추가'아이콘이 있습니다. '추가'드롭 다운을 확장하고 '데이터 모델에 추가'를 클릭합니다.
- 결과 대화 상자에서 '테이블'을 클릭 한 다음 테이블 중 하나를 선택하고 '열기'를 클릭합니다.
이렇게하면 하나의 테이블이있는 통합 문서 데이터 모델이 생성되고 다음과 같은 대화 상자가 나타납니다.
따라서 다른 두 테이블에 대해서도이 단계를 반복하면 이제 데이터 모델에 세 테이블이 모두 포함됩니다.
이제 통합 문서 연결에 세 테이블이 모두 나타나는 것을 볼 수 있습니다.
관계를 통해
관계 생성 : 두 데이터 세트가 모두 테이블 객체이면 둘 사이의 관계를 생성 할 수 있습니다. 이것을하기 위해:
- '데이터'탭을 클릭 한 다음 '관계'를 클릭하십시오.
- 현재 연결이 없기 때문에 빈 대화 상자가 표시됩니다.
- '새로 만들기'를 클릭하면 다른 대화 상자가 나타납니다.
- '테이블'및 '관련 테이블'드롭 다운을 확장합니다. '관계 만들기'대화 상자가 나타나 관계에 사용할 테이블과 열을 선택합니다. 'Tables'확장에서 어떤 방식 으로든 분석하고자하는 데이터 세트를 선택하고 'Related Table'에서 조회 값이있는 데이터 세트를 선택합니다.
- Excel의 조회 테이블은 일대 다 관계의 경우 더 작은 테이블이며 공통 열에 반복되는 값이 없습니다. 'Column (Foreign)'확장시 기본 테이블에서 공통 열을 선택하고 'Related Column (Primary)'에서 관련 테이블에서 공통 열을 선택합니다.
- 이 네 가지 설정을 모두 선택한 상태에서 '확인'을 클릭합니다. '확인'을 클릭하면 다음과 같은 대화 상자가 나타납니다.
이 단계를 반복하여 다른 두 테이블 인 Revenue Table with Expenses 테이블을 연결하면 다음과 같이 데이터 모델에서도 관련됩니다.
Excel은 이제 공통 열인 영업 사원 ID (이 경우)를 기반으로 데이터 모델의 데이터를 결합하여이면에서 관계를 만듭니다.
예제 # 2
이제 위의 예에서 Table 개체를 평가하거나 분석하는 피벗 테이블을 만들고 싶다고 가정 해 보겠습니다.
- '삽입'-> '피벗 테이블'을 클릭하십시오.
- 결과 대화 상자에서 '외부 데이터 소스 사용'옵션을 클릭 한 다음 '연결 선택'을 클릭합니다.
- 결과 대화 상자에서 '테이블'을 클릭하고 세 개의 테이블이 포함 된 통합 문서 데이터 모델을 선택하고 '열기'를 클릭합니다.
- 위치에서 '새 워크 시트'옵션을 선택하고 '확인'을 클릭합니다.
- 피벗 테이블 필드 창에 테이블 개체가 표시됩니다.
- 이제 필요에 따라 테이블 개체를 분석하기 위해 피벗 테이블을 적절히 변경할 수 있습니다.
예를 들어,이 경우 특정 영업 사원의 총 수익 또는 수익을 찾으려면 다음과 같이 피벗 테이블이 생성됩니다.
이것은 많은 수의 관측치를 포함하는 모델 / 테이블의 경우 큰 도움이됩니다.
따라서 피벗 테이블은 Excel 메모리에서 데이터 모델 (연결을 선택하여 선택)을 즉시 사용하여 테이블 간의 관계를 표시하는 것을 볼 수 있습니다.
기억해야 할 사항
- 데이터 모델을 사용하면 여러 테이블의 데이터를 한 번에 분석 할 수 있습니다.
- 데이터 모델과의 관계를 생성하면 단일 테이블 내에서 모든 열을 가져올 필요가 없으므로 VLOOKUP, SUMIF, INDEX 함수 및 MATCH 수식을 사용할 필요가 없습니다.
- 데이터 세트를 외부 소스에서 Excel로 가져 오면 모델이 암시 적으로 생성됩니다.
- 기본 및 외래 키 관계가있는 관련 테이블을 가져 오면 테이블 관계가 자동으로 생성 될 수 있습니다.
- 관계를 만드는 동안 테이블에서 연결하는 열은 동일한 데이터 유형을 가져야합니다.
- 데이터 모델로 생성 된 피벗 테이블을 사용하여 슬라이서를 추가하고 원하는 필드에서 피벗 테이블을 분할 할 수 있습니다.
- LOOKUP () 함수에 비해 데이터 모델의 장점은 훨씬 적은 메모리가 필요하다는 것입니다.
- Excel 2013은 일대일 또는 일대 다 관계 만 지원합니다. 즉, 연결하려는 열에 테이블 중 하나에 중복 값이 없어야합니다.