엑셀 데이터 모델과 파워 피벗 기초

평평한 시트 하나로 관리하면 판매 행마다 제품 이름과 분류를 반복해서 적고, 그 복사본들을 계속 똑같이 맞춰 줘야 해요. 데이터 모델(Data Model)은 방식이 달라요. 작고 깔끔한 표 여러 개를 그대로 두고, 표끼리 어떻게 연결되는지만 엑셀에 알려 주죠. 그러면 피벗 테이블(PivotTable) 하나가 한 표에서는 분류를, 다른 표에서는 매출을 가져올 수 있어요. VLOOKUP이나 XLOOKUP으로 미리 이어 붙일 필요가 없어요.

팩트 테이블과 조회 테이블

업무 데이터는 대부분 자연스럽게 두 종류의 표로 나뉘어요.

• 팩트 테이블(fact table)은 사건을 기록해요. 판매, 주문, 결제 한 건당 한 행이에요. 날마다 늘어나고, 더할 숫자(수량, 매출, 원가)를 담고 있어요.
• 조회 테이블(lookup table)은 대상을 설명해요. 디멘션 테이블이라고도 하고, 제품, 고객, 지역 하나당 한 행이에요. 거의 바뀌지 않고, 묶어서 볼 기준이 되는 이름표(이름, 분류)를 담고 있어요.

두 표는 둘 다 가지고 있는 열인 키로 연결돼요. 여기서는 판매마다 ProductID가 있고, Products 표에는 ProductID마다 정확히 한 행이 있어요.

fx

	A	B	C	D	E	F	G	H
1	ProductID	Units	Revenue	Cost		ProductID	Name	Category
2	P1	12	480	300		P1	Widget A	Hardware
3	P2	8	320	200		P2	Widget B	Hardware
4	P1	5	200	130		P3	Cable Tie	Accessories
5	P3	10	900	550

판매마다 제품 정보를 되풀이하는 평평한 시트와 비교해 보세요.

네 행일 때는 별문제가 없어 보여요. 하지만 4만 행이 되면 분류 이름 하나를 바꾸는 데 셀 수천 개를 고쳐야 하고, 오타 하나가 새 분류를 만들어 버려요. 표를 나눠 두면 Products에서 한 번만 바꾸면 돼요.

이런 관계를 일대다(one-to-many) 관계라고 해요. 제품 하나는 여러 판매 행에 나오지만, 판매 행 하나는 정확히 한 제품만 가리켜요. "일" 쪽(Products)의 키는 중복이 없어야 하고, "다" 쪽(Sales)의 키는 반복돼도 괜찮아요.

범위마다 이름 있는 표로 만들기

데이터 모델은 엑셀 표를 기준으로 동작하니 표부터 만들어요.

1. 판매 데이터를 표로 만드세요

   판매 데이터 안의 아무 셀이나 클릭하고 Ctrl+T를 누르세요. 머리글 포함이 체크되어 있는지 확인하고 확인을 클릭하세요.

2. 표에 알아보기 쉬운 이름을 붙이세요

   테이블 디자인 탭의 속성 그룹에서 표 이름 상자의 기본 이름(표1)을 Sales로 바꾸고 Enter를 누르세요.

3. 조회 테이블도 똑같이 하세요

   제품 목록도 같은 방법으로 표로 만들고 이름을 Products로 정하세요. 이름이 중요해요. 피벗 테이블 필드 목록과 앞으로 쓸 모든 DAX 수식에 Sales[Revenue], Products[Category]처럼 표 이름이 들어가거든요.

두 표에 관계 만들기

1. 관계 대화 상자를 여세요

   데이터› then 데이터 도구› then 관계를 선택하세요. 관계 관리(Manage Relationships) 대화 상자가 열려요. 아직 연결된 표가 없어서 비어 있어요. (통합 문서에 표가 두 개 이상 있어야 이 단추를 쓸 수 있어요.)

2. 관계를 만드세요

   새로 만들기를 클릭하세요. 관계 만들기 대화 상자에서 이렇게 고르세요.

   • 테이블: Sales, 열(외래): ProductID — "다" 쪽이에요.
   • 관련 테이블: Products, 관련 열(기본): ProductID — 값이 중복되지 않는 "일" 쪽이에요.

   확인을 클릭한 다음 닫기를 클릭하세요.

관계를 만들면 두 표가 데이터 모델에도 함께 추가돼요. 워크시트 자체는 아무것도 바뀌지 않아요. 관계는 셀이 아니라 통합 문서의 모델 안에 저장되거든요.

두 표로 피벗 테이블 만들기

1. 데이터 모델 피벗 테이블을 삽입하세요

   삽입 탭에서 피벗 테이블 단추의 화살표를 클릭하고 데이터 모델에서를 선택하세요. 새 워크시트를 고르고 확인을 클릭하세요.

   이전 버전에서는 피벗 테이블을 클릭하면 피벗 테이블 만들기 대화 상자가 바로 열려요. 거기서 이 통합 문서의 데이터 모델 사용을 고르세요.

2. 필드 목록에서 두 표를 찾으세요

   피벗 테이블 필드 창에 모델 안의 표가 이름별로 나와요. 하나만 보이면 창 위쪽의 모두 탭을 클릭하세요.

3. 두 표에서 필드를 끌어오세요

   Products를 펼치고 Category를 행 영역으로 끌어다 놓으세요. Sales를 펼치고 Revenue를 값 영역으로 끌어다 놓으세요. 피벗 테이블에 Accessories 900, Hardware 1,000, 총합계 1,900이 나와요.

분류 이름은 Products에서, 숫자는 Sales에서 왔어요. 관계가 판매마다 ProductID를 Products로 넘겨 분류를 찾아 주기 때문에 조회 수식이 필요 없어요.

측정값과 계산된 열의 차이

Revenue를 값 영역으로 끌어다 놓으면 엑셀이 합계 : Revenue라는 자동 합계를 만들어요. 단순 합계보다 복잡한 계산은 데이터 모델의 수식 언어인 DAX(Data Analysis Expressions)로 직접 써요. DAX 계산은 두 가지가 있어요.

핵심은 컨텍스트예요. 측정값은 수식이 하나뿐이지만, 피벗 테이블의 셀마다 그 셀에 해당하는 행만 가지고 계산해요. Hardware 행에서 SUM(Sales[Revenue])는 Hardware 판매만 더하고(480 + 320 + 200 = 1,000), 총합계에서는 네 건을 모두 더해요.

그래서 비율은 측정값으로 계산해야 해요. 판매 건별 이익률을 계산된 열로 만들었다고 해 볼게요. 값은 37.5%, 37.5%, 35%, 38.9%예요. 피벗 테이블은 이걸 더해서 의미 없는 148.9%를 보여 주고, 평균을 내도 37.2%예요. 실제 이익률은 전체 이익을 전체 매출로 나눈 720 / 1,900 = 37.9%이고, 모든 수준에서 이렇게 계산해 주는 건 측정값뿐이에요.

첫 DAX 측정값 만들기

측정값은 Power Pivot 탭에서 만들어요. 탭이 보이지 않으면 파일 > 옵션 > 추가 기능으로 가서 관리 상자에서 COM 추가 기능을 고르고 이동을 클릭한 다음, Microsoft Power Pivot for Excel을 체크하고 확인을 클릭하세요. 파워 피벗(Power Pivot) 창 자체를 열려면 Power Pivot› then 데이터 모델› then 관리를 클릭하세요.

1. 측정값 대화 상자를 여세요

   Power Pivot› then 계산› then 측정값› then 새 측정값을 선택하세요. 피벗 테이블 필드 창에서 Sales 표 이름을 마우스 오른쪽 단추로 클릭하고 측정값 추가를 골라도 돼요.

2. 측정값 이름과 수식을 입력하세요

   테이블 이름은 Sales, 측정값 이름은 Total Revenue로 정하세요. 수식 상자의 등호 뒤에 이렇게 입력하세요.

   =SUM(Sales[Revenue])
   

   수식 확인을 클릭해 오류가 없는지 확인하고, 서식 옵션에서 숫자 형식을 고른 다음 확인을 클릭하세요.

3. 매출 총이익 측정값을 추가하세요

   두 번째 측정값을 Gross Margin이라는 이름으로 만드세요.

   =SUM(Sales[Revenue]) - SUM(Sales[Cost])
   

4. DIVIDE로 이익률을 추가하세요

   세 번째 측정값 Margin %를 만드세요. 앞의 두 측정값을 대괄호 안에 이름으로 넣어 참조해요. 서식은 백분율로 정하세요.

   =DIVIDE([Gross Margin], [Total Revenue])
   

   DIVIDE는 / 연산자와 같지만, 분모가 0이면 오류 대신 빈 값을 돌려줘요. 그래서 데이터가 없는 분류가 있어도 보고서가 오류로 뒤덮이지 않아요.

5. 피벗 테이블에서 측정값을 쓰세요

   새 측정값은 필드 목록의 Sales 표 아래에 fx 아이콘과 함께 나타나요. 값 영역에서 합계 : Revenue를 빼고 Total Revenue, Gross Margin, Margin %를 끌어다 놓으세요.

   fx

   	A	B	C	D
   1	행 레이블	Total Revenue	Gross Margin	Margin %
   2	Accessories	900	350	38.9%
   3	Hardware	1,000	370	37%
   4	총합계	1,900	720	37.9%

파워 피벗 창에서는 측정값이 Total Revenue := SUM(Sales[Revenue])처럼 쓰인 모습을 자주 보게 돼요. 이름, :=, 수식 순서로 쓴 같은 측정값이에요. 창의 데이터 보기에서 표 아래쪽 계산 영역에 이 형태로 직접 입력할 수도 있어요.

측정값에 필터를 걸려면 CALCULATE로 감싸세요. 아래 측정값은 어느 셀에 두든 Hardware 매출(1,000)을 돌려줘요. 이 필터가 피벗 테이블 행의 Category를 덮어쓰기 때문이에요.

=CALCULATE([Total Revenue], Products[Category] = "Hardware")

자주 하는 실수

• 관계가 없는 표의 필드를 함께 쓰기. 피벗 테이블의 모든 행에 같은 합계(분류마다 1,900)가 반복되고, 필드 목록에 표 사이에 관계가 필요할 수 있다는 메시지가 나타나요. 관계를 만든 다음 새로 고치세요.
• 조회 테이블에 중복된 키. Products에 P1이 두 번 있으면 엑셀이 일대다 관계를 만들 수 없어요. 데이터› then 데이터 도구› then 중복된 항목 제거로 키가 한 번씩만 나오게 정리하세요.
• 미묘하게 다른 키. P1과 P1 (뒤에 공백), 또는 한 표에서는 숫자이고 다른 표에서는 텍스트인 101은 서로 맞지 않아요. 키를 찾지 못한 판매 행은 피벗 테이블에서 (비어 있음) 아래로 묶여요. 두 표의 키 열을 모두 정리하세요.
• 비율을 계산된 열로 만들기. 앞에서 본 것처럼 백분율을 피벗 테이블에서 더하면 엉뚱한 값이 나와요. 비율은 DIVIDE([Gross Margin], [Total Revenue]) 같은 측정값으로 계산하세요.
• 측정값 이름을 열 이름과 똑같이 짓기. Revenue라는 측정값은 Revenue 열과 이름이 겹쳐요. Total Revenue처럼 다른 이름을 쓰세요.

연습

연습

판매 표와 제품 표 연결하기

1. 아래 시트의 Sales 데이터(ProductID, Units, Revenue, Cost)와 Products 데이터(ProductID, Name, Category)를 새 통합 문서에 입력하세요.
2. 각 범위를 표로 만들고 이름을 Sales, Products로 정하세요.
3. 데이터 > 관계로 Sales[ProductID]와 Products[ProductID]를 연결하세요.
4. 데이터 모델에서 피벗 테이블을 삽입하고 Category를 행 영역에 넣으세요.
5. Total Revenue, Gross Margin, Margin % 측정값을 만들어 값 영역에 넣으세요. Hardware 1,000 / 370 / 37%, Accessories 900 / 350 / 38.9%가 나오는지 확인하세요.
6. 행 영역의 Category를 Name으로 바꿔 보세요. Widget A는 판매 두 건이 합쳐져서 매출 680, 이익 250이 나와야 해요.

그다음 내 데이터로도 해 보세요. 평소 쓰는 평평한 시트를 팩트 테이블 하나와 조회 테이블 한두 개로 나누고, 모든 표를 함께 쓰는 피벗 테이블을 만들어 보세요.

fx

	A	B	C	D	E	F	G	H
1	ProductID	Units	Revenue	Cost		ProductID	Name	Category
2	P1	12	480	300		P1	Widget A	Hardware
3	P2	8	320	200		P2	Widget B	Hardware
4	P1	5	200	130		P3	Cable Tie	Accessories
5	P3	10	900	550

실력 점검

• 내 표 중에서 어느 것이 팩트 테이블이고 어느 것이 조회 테이블인지 구분할 수 있나요?
• 조회 수식을 쓰지 않고 공유하는 ID 열로 두 표에 관계를 만들 수 있나요?
• 이익률을 계산된 열이 아니라 측정값으로 만들어야 하는 이유를 설명할 수 있나요?
• SUM으로 측정값을 하나 만들고, 그 측정값을 참조하는 DIVIDE 측정값을 쓸 수 있나요?