내용
Excel에서 피벗 테이블을 작성할 때 초기 범위를 설정하고 피벗 테이블을 삽입할 위치를 선택하라는 첫 번째 대화 상자에서 아래에 눈에 띄지 않지만 매우 중요한 확인란이 있습니다. 이 데이터를 데이터 모델에 추가 (이 데이터를 추가 데이터 모델로) 그리고 조금 더 높은 스위치 이 책의 데이터 모델 사용 (이 통합 문서의 데이터 모델 사용):
불행히도 오랫동안 피벗 테이블에 익숙하고 작업에서 성공적으로 사용하는 많은 사용자가 때때로 이러한 옵션의 의미를 제대로 이해하지 못하고 사용하지 않습니다. 그리고 헛된. 결국 데이터 모델에 대한 피벗 테이블을 생성하면 기존 Excel 피벗 테이블에 비해 몇 가지 매우 중요한 이점이 있습니다.
그러나 이러한 "빵"을 가까이서 고려하기 전에 먼저 이 데이터 모델이 실제로 무엇인지 이해합시다.
데이터 모델이란
데이터 모델 (MD 또는 DM = 데이터 모델로 약칭) 테이블 형식 데이터를 저장할 수 있는 Excel 파일 내부의 특수 영역입니다. 원하는 경우 서로 연결된 하나 이상의 테이블입니다. 실제로 이것은 Excel 통합 문서에 포함된 작은 데이터베이스(OLAP 큐브)입니다. Excel 자체의 시트에 일반(또는 스마트) 테이블 형태의 데이터를 저장하는 기존의 데이터 모델과 비교할 때 데이터 모델에는 다음과 같은 몇 가지 중요한 이점이 있습니다.
- 테이블은 최대 2억 라인, 그리고 Excel 시트는 1만 개 조금 넘게 들어갈 수 있습니다.
- 거대한 크기에도 불구하고 이러한 테이블의 처리(필터링, 정렬, 계산, 요약 작성 등)가 수행됩니다. 매우 빠름 Excel 자체보다 훨씬 빠릅니다.
- 모델의 데이터로 다음을 사용하여 추가(원하는 경우 매우 복잡한) 계산을 수행할 수 있습니다. 내장 DAX 언어.
- 데이터 모델에 로드된 모든 정보는 매우 강하게 압축 특별한 내장 아카이버를 사용하고 원본 Excel 파일의 크기를 적당히 늘립니다.
모델은 Microsoft Excel에 내장된 특수 추가 기능에 의해 관리 및 계산됩니다. 파워피벗내가 이미 쓴 것에 대해. 활성화하려면 탭에서 개발자 클릭 COM 추가 기능 (개발자 — COM 추가 기능) 해당 확인란을 선택합니다.
탭이라면 개발자 (개발자)리본에서 볼 수 없지만 통해 켤 수 있습니다. 파일 – 옵션 – 리본 설정 (파일 — 옵션 — 리본 사용자 정의). 위의 COM 추가 기능 목록에 표시된 창에 Power Pivot이 없으면 사용 중인 Microsoft Office 버전에 포함되어 있지 않은 것입니다 🙁
나타나는 Power Pivot 탭에는 큰 연한 녹색 버튼이 있습니다. (관리하다), 클릭하면 Excel 상단에 Power Pivot 창이 열리고 현재 책의 데이터 모델 내용이 표시됩니다.
그 과정에서 중요한 참고 사항: Excel 통합 문서에는 데이터 모델이 하나만 포함될 수 있습니다.
데이터 모델에 테이블 로드
모델에 데이터를 로드하려면 먼저 테이블을 동적 "스마트" 키보드 단축키로 바꿉니다. Ctrl 키+T 탭에서 친숙한 이름을 지정하십시오. 건설자 (디자인). 이것은 필수 단계입니다.
그런 다음 세 가지 방법 중 하나를 사용하여 선택할 수 있습니다.
- 버튼을 눌러 모델에 추가 (데이터 모델에 추가) 탭 파워피벗 탭 홈 (집).
- 팀 선택 삽입 – 피벗 테이블 (삽입 - 피벗 테이블) 체크박스를 켜고 이 데이터를 데이터 모델에 추가 (이 데이터를 데이터 모델에 추가). 이 경우 Model에 로드된 데이터에 따라 피벗 테이블도 즉시 구축됩니다.
- 고급 탭에서 Data (데이트) 버튼을 클릭하십시오. 테이블/레인지에서 (표/범위에서)Power Query 편집기에 테이블을 로드합니다. 이 경로는 가장 길지만 원하는 경우 여기에서 추가 데이터 정리, 편집 및 파워 쿼리가 매우 강력한 모든 종류의 변환을 수행할 수 있습니다.
그런 다음 명령에 의해 결합된 데이터가 모델에 업로드됩니다. 홈 — 닫기 및 로드 — 닫기 및 로드… (홈 — 닫기&로드 — 닫기&로드…). 열리는 창에서 옵션을 선택하십시오 연결만 만드세요 (연결만 생성) 그리고 가장 중요한 것은 체크 표시 이 데이터를 데이터 모델에 추가 (이 데이터를 데이터 모델에 추가).
데이터 모델의 요약을 작성합니다.
요약 데이터 모델을 구축하기 위해 다음 세 가지 접근 방식 중 하나를 사용할 수 있습니다.
- 버튼을 누르시오 요약표 (피벗 테이블) 파워 피벗 창에서
- Excel에서 명령 선택 삽입 – 피벗 테이블 그리고 모드로 전환 이 책의 데이터 모델 사용 (삽입 — 피벗 테이블 — 이 통합 문서의 데이터 모델 사용).
- 팀 선택 삽입 – 피벗 테이블 (삽입 - 피벗 테이블) 체크박스를 켜고 이 데이터를 데이터 모델에 추가 (이 데이터를 데이터 모델에 추가). 현재 "스마트" 테이블이 모델에 로드되고 전체 모델에 대한 요약 테이블이 작성됩니다.
데이터를 데이터 모델에 로드하고 이에 대한 요약을 작성하는 방법을 파악했으므로 이제 이를 통해 얻을 수 있는 이점과 이점을 살펴보겠습니다.
이점 1: 수식을 사용하지 않는 테이블 간의 관계
일반 요약은 한 소스 테이블의 데이터만 사용하여 작성할 수 있습니다. 예를 들어 판매, 가격 목록, 고객 디렉토리, 계약 등록 등 여러 가지가 있는 경우 먼저 VLOOKUP과 같은 기능을 사용하여 모든 테이블의 데이터를 하나로 수집해야 합니다. (조회), 인덱스 (인덱스), 더 많이 노출 (성냥), 썸슬림 (수미프) 등등. 이것은 길고 지루하며 많은 양의 데이터로 Excel을 "생각"으로 만듭니다.
데이터 모델 요약의 경우 모든 것이 훨씬 간단합니다. Power Pivot 창에서 한 번만 테이블 간의 관계를 설정하면 충분합니다. 이렇게 하려면 탭에서 파워피벗 버튼을 누르십시오 (관리하다) 그런 다음 나타나는 창에서 – 버튼 차트보기 (다이어그램 보기). 링크를 생성하려면 테이블 간에 공통(키) 열 이름(필드)을 끌어야 합니다.
그런 다음 데이터 모델에 대한 요약에서 관련 테이블의 모든 필드(행, 열, 필터, 값)를 요약 영역에 넣을 수 있습니다. 모든 것이 자동으로 연결되고 계산됩니다.
이점 2: 고유한 값 계산
일반 피벗 테이블은 합계, 평균, 개수, 최소값, 최대값 등의 여러 내장 계산 기능 중 하나를 선택할 수 있는 기회를 제공합니다. 데이터 모델 요약에서 매우 유용한 기능이 이 표준 목록에 추가되어 계산합니다. 고유한 수(반복되지 않는 값). 예를 들어, 이를 통해 각 도시에서 판매하는 고유한 상품(범위)의 수를 쉽게 계산할 수 있습니다.
필드를 마우스 오른쪽 버튼으로 클릭 – 명령 값 필드 옵션 그리고 탭에서 조작 왼쪽 메뉴에서 다른 요소의 수 (고유 수):
이점 3: 사용자 지정 DAX 수식
때로는 피벗 테이블에서 다양한 추가 계산을 수행해야 합니다. 일반 요약에서는 계산된 필드와 개체를 사용하여 이 작업을 수행하는 반면 데이터 모델 요약에서는 특수 DAX 언어(DAX = 데이터 분석 식)의 측정값을 사용합니다.
측정값을 생성하려면 탭에서 을 선택합니다. 파워피벗 명령 측정값 – 측정값 생성 (측정값 — 새 측정값) 또는 피벗 필드 목록에서 테이블을 마우스 오른쪽 버튼으로 클릭하고 측정값 추가 (측정 추가) 컨텍스트 메뉴에서:
열리는 창에서 다음을 설정합니다.
- 테이블 이름생성된 측정값이 저장될 위치입니다.
- 측정 이름 – 새 필드에 대해 이해하는 모든 이름.
- 상품 설명 – 선택 사항.
- 공식 – 가장 중요한 것은 여기에서 수동으로 입력하거나 버튼을 클릭하기 때문입니다. fx 목록에서 DAX 함수를 선택하면 측정값을 값 영역에 던질 때 결과를 계산해야 합니다.
- 창 하단에서 목록의 측정값에 대한 숫자 형식을 즉시 설정할 수 있습니다. 범주.
DAX 언어는 개별 값이 아니라 전체 열과 테이블로 작동하기 때문에 항상 이해하기 쉬운 것은 아닙니다. 그러나 많은 양의 데이터를 처리하는 능력의 힘을 과대평가하기 어렵기 때문에 그만한 가치가 있습니다.
이점 4: 사용자 정의 필드 계층
종종 표준 보고서를 작성할 때 동일한 필드 조합을 주어진 순서로 피벗 테이블에 던져야 합니다. 년-분기-월-일및 카테고리-제품및 국가-도시-고객 등. 데이터 모델 요약에서 이 문제는 직접 생성하여 쉽게 해결할 수 있습니다. 계층 — 사용자 정의 필드 세트.
파워 피벗 창에서 버튼을 사용하여 차트 모드로 전환 차트보기 탭 홈 (홈 — 다이어그램 보기), 선택 Ctrl 키 원하는 필드를 선택하고 마우스 오른쪽 버튼을 클릭합니다. 상황에 맞는 메뉴에는 명령이 포함됩니다. 계층 생성 (계층 구조 생성):
생성된 계층 구조는 이름을 바꾸고 마우스로 필수 필드로 끌어서 나중에 한 번의 이동으로 요약에 넣을 수 있습니다.
이점 5: 맞춤형 스텐실
이전 단락의 아이디어를 계속해서 데이터 모델 요약에서 각 필드에 대해 고유한 요소 집합을 만들 수도 있습니다. 예를 들어, 전체 도시 목록에서 귀하의 책임 영역에 있는 도시들만의 집합을 쉽게 만들 수 있습니다. 또는 귀하의 고객, 귀하의 상품 등만을 특별한 세트로 모으십시오.
이렇게 하려면 탭에서 피벗 테이블 분석 드롭다운 목록에서 필드, 항목 및 세트 해당 명령이 있습니다 (분석 — 필드, Itemps & Sets — 행/열 항목을 기반으로 세트 생성):
열리는 창에서 요소의 위치를 선택적으로 제거, 추가 또는 변경하고 결과 세트를 새 이름으로 저장할 수 있습니다.
생성된 모든 집합은 새 피벗 테이블의 행과 열 영역으로 자유롭게 끌 수 있는 별도의 폴더에 있는 피벗 테이블 필드 패널에 표시됩니다.
이점 6: 표와 열을 선택적으로 숨기기
이것은 작지만 어떤 경우에는 매우 즐거운 이점입니다. Power Pivot 창의 필드 이름이나 테이블 탭을 마우스 오른쪽 버튼으로 클릭하여 명령을 선택할 수 있습니다. 클라이언트 툴킷에서 숨기기 (클라이언트 도구에서 숨기기):
숨겨진 열이나 테이블은 피벗 테이블 필드 목록 창에서 사라집니다. 일부 보조 열(예: 계산 또는 관계 생성을 위한 키 값이 있는 열) 또는 전체 테이블을 사용자로부터 숨겨야 하는 경우 매우 편리합니다.
이점 7. 고급 드릴다운
일반 피벗 테이블의 값 영역에 있는 셀을 두 번 클릭하면 Excel은 이 셀 계산과 관련된 소스 데이터 조각의 복사본을 별도의 시트에 표시합니다. 이것은 공식적으로 드릴다운(Drill-down)이라고 불리는 매우 편리한 기능입니다(보통 "실패"라고 말합니다).
데이터 모델 요약에서 이 편리한 도구는 더 미묘하게 작동합니다. 관심 있는 결과가 있는 셀에 서 있으면 옆에 팝업되는 돋보기가 있는 아이콘을 클릭할 수 있습니다( 익스프레스 트렌드) 그런 다음 관련 테이블에서 관심 있는 필드를 선택합니다.
그 후 현재 값(모델 = 탐색기)이 필터 영역으로 이동하고 사무실에서 요약을 작성합니다.
물론 이러한 절차를 여러 번 반복하여 관심 있는 방향으로 데이터를 지속적으로 탐색할 수 있습니다.
이점 8: 피벗을 큐브 함수로 변환
데이터 모델에 대한 요약에서 셀을 선택한 다음 탭에서 선택하면 피벗 테이블 분석 명령 OLAP 도구 – 수식으로 변환 (분석 — OLAP 도구 — 공식으로 변환), 전체 요약이 자동으로 수식으로 변환됩니다. 이제 행-열 영역의 필드 값과 값 영역의 결과는 특수 큐브 함수인 CUBEVALUE 및 CUBEMEMBER를 사용하여 데이터 모델에서 검색됩니다.
기술적으로 이것은 이제 요약을 처리하는 것이 아니라 수식이 있는 여러 셀을 사용한다는 것을 의미합니다. 보고서의 요약 내에서 추가 계산을 수행하고 원하는 방식으로 정렬하는 등의 작업을 수행합니다.
동시에 소스 데이터와의 연결은 물론 유지되며 향후 소스가 변경되면 이러한 공식이 업데이트됩니다. 아름다움!
- 파워 피벗 및 파워 쿼리를 사용하여 피벗 테이블의 계획 사실 분석
- 여러 줄 머리글이 있는 피벗 테이블
- Power Pivot을 사용하여 Excel에서 데이터베이스 만들기