엑셀 고급 · 대화형 레슨 12개 중 7번째
데이터 모델과 파워 피벗 기초
평평한 표 하나로 관리하면 판매 행마다 제품 이름을 반복해서 적어야 해요. 데이터 모델(Data Model)은 대신 작고 깔끔한 표 여러 개를 관계로 연결해요. 판매 팩트 테이블 하나와 조회 테이블 하나를 공유하는 열 하나로 잇는 식이에요.
화면 기준: Windows용 Microsoft 365 Excel. 연습은 어느 브라우저에서나 할 수 있어요.
글로 보는 튜토리얼엑셀 데이터 모델과 파워 피벗 기초실력 점검
먼저 해 보세요:
- 팩트 테이블(fact table, 거래 내역)과 조회 테이블(lookup table, 제품·지역)을 구분해요.
- 조회 수식 대신 공유 열로 두 표 사이에 관계(relationship)를 만들어요.
- 계산된 열(calculated column)과 측정값(measure)의 차이를 설명해요.
- SUM이나 DIVIDE를 사용해 간단한 DAX 측정값을 작성해요.
이미 다 아는 내용인가요? A8 — 가상 분석으로 건너뛰세요.
개념 익히기
이번 레슨에서 배울 내용
- 팩트 테이블 vs. 조회 테이블
- 팩트 테이블은 이벤트를 담아요. 판매 한 건당 한 행이에요. 조회 테이블은 설명을 담아요. 제품 하나당 한 행이에요. 팩트는 많고, 조회 항목은 적어요.
- 관계와 일대다
- 제품 하나는 여러 판매 행에 나타날 수 있지만, 각 판매 행은 정확히 한 제품만 가리켜요. 이게 일대다 관계이고, 공유하는 Product ID 열을 연결해서 만들어요.
- 측정값 vs. 계산된 열
- 계산된 열은 행마다 값을 하나씩 저장하고, 한 번만 계산해요. 측정값은 지금 보고 있는 범위의 컨텍스트 안에서 그때그때 계산해요. 그래서 같은 측정값이라도 피벗 테이블(PivotTable)의 셀마다 다른 숫자를 돌려줘요.
- 간단한 DAX:
SUM,DIVIDE Revenue := SUM(Sales[Revenue]);Margin % := DIVIDE([Gross Margin],[Revenue])— DIVIDE는 분모가 0일 때 오류 대신 빈 값을 안전하게 돌려줘요.- 피벗 테이블에서 데이터 모델 사용하기
- 표끼리 관계가 연결되면, 피벗 테이블 하나에서 한 표의 Region과 다른 표의 Revenue를 함께 가져올 수 있어요. 먼저 조회 수식으로 이어 붙일 필요가 없어요.
엑셀에서 찾는 위치
Power Pivot›관리 · 측정값›또는 데이터 › 관계
자동 저장 Book1.xlsx · 저장됨 검색–□✕
파일홈삽입페이지 레이아웃수식데이터검토보기자동화도움말Power Pivot
관리
데이터 모델
측정값
KPI
계산
데이터 모델에 추가
테이블
검색
관계
| A | B | C | D | E | F | G | ||
|---|---|---|---|---|---|---|---|---|
| 1 | 행 레이블 | Total Sales | Margin % | |||||
| 2 | 하드웨어 | 12,450 | 31.2% | |||||
| 3 | 액세서리 | 4,380 | 44.8% | |||||
| 4 | 전자 제품 | 9,920 | 27.5% | |||||
| 5 | 총합계 | 26,750 | 31.9% | |||||
| 6 | ||||||||
| 7 | ||||||||
| 8 |
피벗 테이블 필드✕
활성 | 모두
▾ Sales
Σ Total Sales
Σ Margin %
▸ Products
▸ Calendar
◀ ▶Sheet1+
준비접근성: 계속 진행 가능▦ ▤ ▥ — + 100%
옆으로 밀어 창 전체를 볼 수 있어요.
- 1Power Pivot 탭. 안 보이면 파일 › 옵션 › 추가 기능 › COM 추가 기능 › Microsoft Power Pivot for Excel.
- 2데이터 모델에 추가로 표를 모델에 넣어요.
- 3관리를 누르면 모델이 열리고, 다이어그램 뷰에서 일대다 관계를 그려요.
- 4측정값 › 새 측정값에서 =DIVIDE([Margin],[Total Sales]) 같은 DAX를 써요.
- 5측정값은 데이터 모델 피벗 테이블 필드 목록에 Σ로 표시돼요.
Microsoft 365용 Excel(Windows) 화면을 재현한 그림입니다. Mac, 웹용 Excel 또는 좁은 창에서는 리본 배치가 조금 다를 수 있습니다.
따라 만들기
Sales 팩트 테이블과 Products 조회 테이블이 Product ID로 연결돼 있어요. 관계를 만든 다음 두 측정값을 읽어 보세요.
혼자 응용하기
내 표에 관계 만들기
평소 쓰는 평평한 스프레드시트 하나를 팩트 테이블 하나와 조회 테이블 한두 개로 나누고, 공유 ID 열로 관계를 만든 다음, Revenue 측정값과 Gross Margin % 측정값을 작성해 보세요. 그리고 두 표의 필드를 한꺼번에 가져오는 피벗 테이블을 만들어 보세요.
이 레슨에서 틀린 부분을 찾았거나 제안이 있나요? 의견 보내기