Excel의 내부 데이터 모델 - 더미

피벗 테이블과 Excel 데이터 연결을 조합하여 Power Pivot 추가 기능없이 내부 데이터 모델과 직접 상호 작용할 수 있습니다. Microsoft Office (가정용 또는 소규모 비즈니스 버전)를 사용하는 경우와 같이 Power Pivot 추가 기능과 함께 제공되지 않는 Excel 버전을 사용하는 경우 유용합니다. 모든 Excel 2013 및 2016 통합 문서에는 내부 데이터 모델이 제공됩니다.

여기서 볼 수있는 거래 테이블이 있고 다른 워크 시트에 직원에 대한 정보가 들어있는 직원 테이블이 있다고 가정 해보십시오.

이 표에는 종업원 번호 별 거래가 나와 있습니다.

이 표는 직원에 대한 정보를 제공합니다: 이름, 성 및 직책.

직책 별 매출을 보여주는 분석을 작성해야합니다. 판매 및 직위가 두 개의 별도 표에 있다는 사실을 감안할 때 일반적으로 어려울 것입니다. 그러나 내부 데이터 모델을 사용하면 다음과 같은 간단한 단계를 수행 할 수 있습니다.

1. 거래 데이터 테이블 내부를 클릭하고 리본에서 삽입 ➪ 피벗 테이블을 선택하여 새 피벗 테이블을 시작합니다.
2. 피벗 테이블 만들기 대화 상자에서 데이터 모델에이 데이터 추가 옵션을 선택합니다.

   

   Transactions 테이블에서 새 피벗 테이블을 만들 때이 데이터를 데이터 모델에 추가를 선택해야합니다.

3. Employees 데이터 테이블을 클릭하고 새 피벗 테이블을 시작합니다.

   다시 그림과 같이 데이터 모델에이 데이터 추가 옵션을 선택하십시오.

   피벗 테이블 만들기 대화 상자는 명명 된 범위를 참조합니다. 즉, 각 테이블에 특정 이름이 지정되었습니다. 내부 데이터 모델에 데이터를 추가 할 때는 데이터 테이블의 이름을 지정하는 것이 가장 좋습니다. 이렇게하면 내부 데이터 모델에서 테이블을 쉽게 인식 할 수 있습니다. 테이블의 이름을 지정하지 않으면 내부 데이터 모델에 Range1, Range2 등으로 표시됩니다.

   

   Employees 테이블에서 새 피벗 테이블을 만들고이 데이터를 데이터 모델에 추가를 선택합니다.
4. 데이터 테이블에 이름을 지정하려면 테이블의 모든 데이터를 강조 표시 한 다음 리본에서 수식 → 이름 정의 명령을 선택하기 만하면됩니다. 대화 상자에서 테이블의 이름을 입력하십시오. 다른 모든 테이블에 대해 반복하십시오.
5. 두 테이블이 내부 데이터 모델에 추가 된 후 피벗 테이블 필드 목록을 열고 ALL 선택기를 선택합니다. 이 단계는 필드 목록에 두 범위를 모두 표시합니다.

   

   피벗 테이블 필드 목록에서 모두를 선택하여 내부 데이터 모델의 두 테이블을 확인합니다.
6. 피봇 테이블을 평소대로 빌드하십시오.이 경우 Job_Title은 Row 영역에 배치되고 Sales_Amount는 Values ​​영역으로 이동합니다.

   여기에서 알 수 있듯이 Excel은 내부 데이터 모델의 두 테이블을 사용 중임을 즉시 인식하고 두 테이블 간의 관계를 생성하라는 메시지를 표시합니다. Excel에서 테이블 간의 관계를 자동으로 검색하거나 만들기 단추를 클릭 할 수 있습니다. Excel에서 잘못 이해하지 못하도록 항상 관계를 직접 작성하십시오.

   

   Excel에서 메시지가 표시되면 두 테이블 간의 관계를 만들도록 선택합니다.
7. 만들기 버튼을 클릭하십시오.

   Excel이 여기에 표시된 관계 만들기 대화 상자를 엽니 다. 여기서 관계를 정의하는 테이블과 필드를 선택합니다. Transactions 테이블에 Sales_Rep 필드가 있음을 알 수 있습니다. Employee_Number 필드를 통해 Employees 테이블과 관련됩니다.

표 및 열 드롭 다운 목록을 사용하여 적절한 관계를 구축하십시오.

관계를 작성한 후에는 두 테이블의 데이터를 효과적으로 사용하여 필요한 분석을 작성하는 단일 피벗 테이블을 갖게됩니다. 다음 그림은 Excel 내부 데이터 모델을 사용하여 직무별로 판매를 표시한다는 목표를 달성했음을 보여줍니다.

직책 별 판매량을 보여주는 목표를 달성했습니다.

오른쪽 아래 드롭 다운의 이름이 관련 열 (기본)이라는 것을 알 수 있습니다. 기본이라는 용어는 내부 데이터 모델이 연결된 테이블의이 필드를 기본 키로 사용한다는 것을 의미합니다.

기본 키는 고유하지 않은 고유 값 (중복 또는 공백 없음) 만 포함하는 필드입니다. 집계 오류 및 중복을 방지하기 위해 데이터 모델에서 기본 키 필드가 필요합니다. 생성하는 모든 관계에는 기본 키로 지정된 필드가 있어야합니다.

Employees 테이블은 Employee_Number 필드에 공백이나 Null 값이없는 모든 고유 값을 가져야합니다. Excel이 여러 테이블을 조인 할 때 데이터 무결성을 보장 할 수있는 유일한 방법입니다.

내부 데이터 모델에 테이블을 할당 한 후에는 테이블 간의 관계를 조정해야 할 수도 있습니다. 내부 데이터 모델에서 관계를 변경하려면 리본에서 데이터 탭을 클릭하고 관계 명령을 선택합니다. 관계 관리 대화 상자가 열립니다.

관계 관리 대화 상자를 사용하여 내부 데이터 모델의 관계를 변경할 수 있습니다.

다음 명령을 찾을 수 있습니다.

• New: 내부 데이터 모델의 두 테이블 사이에 새로운 관계를 만듭니다.
• 편집: 선택한 관계를 변경합니다.
• 활성화: 내부 데이터 모델에서 데이터를 집계하고 분석 할 때 Excel과의 관계를 고려하도록 선택한 관계를 적용합니다.
• 비활성화: 내부 데이터 모델에서 데이터를 집계하고 분석 할 때 Excel과의 관계를 무시하도록 선택한 관계를 해제합니다.
• 삭제: 선택한 관계를 제거하십시오.