Excel Power Pivot: 단계별 가이드

대용량 Excel 파일을 분석하다 보면 성능이 쉽게 느려집니다. 

Power Pivot은 다른 접근 방식을 제공합니다. 테이블을 연결하고 계산을 처리하면서도 성능 저하가 거의 없습니다. VLOOKUP() 체인과 보조 열로 씨름하는 대신, Excel에 내장된 구조화된 시스템으로 작업합니다.

이 가이드에서는 Power Pivot으로 데이터 모델을 설정하고, 테이블 관계를 생성하고, DAX 수식을 작성하며, 대화형 보고서를 만드는 방법을 학습합니다.

Power Pivot이란 무엇이며 왜 유용할까요?

Power Pivot은 Excel의 내장 데이터 모델링 엔진입니다. 더 큰 데이터 세트를 불러오고, 여러 테이블을 연결하며, 기존 워크시트에서 겪던 느려짐 없이 복잡한 계산을 실행할 수 있습니다.

Power Pivot의 차별점

Power Pivot은 데이터를 시트에 직접 저장하지 않고 Excel의 내부 데이터 모델에 모두 적재합니다. 

일반 워크시트는 약 백만 행까지 도달할 수 있지만 그 전에 이미 속도가 저하되는 경우가 많습니다. Power Pivot은 데이터를 압축해 별도로 관리함으로써 이 한계를 우회하여, 통합 문서 성능을 유지하면서 수천만 행까지도 다룰 수 있게 합니다.

VLOOKUP 체인 대신 관계형 구조

데이터를 모델에 넣으면 키를 사용해 테이블 간 관계를 만들 수 있으며, 이는 가벼운 데이터베이스와 유사합니다. 모든 것을 하나의 거대한 시트로 평탄화하고 중첩된 VLOOKUP() 함수로 테이블을 무리하게 결합할 필요가 없습니다. Power Pivot은 연결된 테이블을 나란히, 깔끔하고 신뢰성 있게 분석할 수 있게 해줍니다.

DAX로 더 강력한 계산

Power Pivot uses DAX (Data Analysis Expressions), a formula language explicitly built for analytical work. You can use this to create measures that go far beyond what a standard PivotTable can handle, from simple sums to time-based metrics, ratios, rolling windows, and other advanced calculations.

예시 시나리오

다음은 기업이 업무에서 Power Pivot을 활용하는 두 가지 예시입니다. 

• 매출 성과 추적: 주문 이력, 제품 테이블, 고객 속성을 결합한 뒤, 수기 병합 없이 전년 대비 매출 또는 고객 생애가치에 대한 DAX 측정값을 만듭니다.
• 운영 보고: 재고, 출하, 공급업체 데이터를 연결하고 동일한 모델에서 충족률, 리드 타임, 예측 오차 등을 계산합니다.

한마디로, Power Pivot은 Excel 안에서 데이터베이스 스타일의 경험을 제공합니다. 대용량 또는 다중 테이블 데이터 세트로 작업한다면, 복잡한 보고 워크플로를 빠르고 확장 가능한 모델로 전환할 수 있습니다.

Excel에서 Power Pivot 설정하기

이제 Excel에서 Power Pivot을 사용하는 방법을 살펴보겠습니다. 

Power Pivot 사용 설정

Power Pivot을 따로 다운로드할 필요는 없습니다. 이미 Excel에 포함되어 있습니다. 사용 설정 방법은 다음과 같습니다.

1. Excel 시트를 엽니다 
2. 리본에서 파일을 클릭합니다
3. 이어 옵션 > 추가 기능을 선택합니다 
4. 그런 다음 드롭다운에서 COM 추가 기능을 선택하고 이동을 클릭합니다
5. 팝업 창이 표시됩니다. 여기에서 Microsoft Power Pivot for Excel을 선택한 다음 확인을 클릭합니다

이제 리본에 Power Pivot 탭이 표시됩니다.

Excel에서 Power Pivot 추가 기능을 사용 설정합니다. 이미지: 작성자.

참고: Power Pivot은 Excel Professional Plus 또는 Microsoft 365에서만 작동합니다. 사용 설정 후에도 탭이 보이지 않는다면, 현재 컴퓨터의 Excel 버전에 포함되어 있지 않을 수 있습니다.

여러 소스에서 데이터 가져오기

이제 Excel 파일, CSV 파일, 심지어 SQL Server 데이터베이스 등 다양한 리소스에서 데이터를 가져올 수 있습니다. 

예시로, .xlsb 파일에 두 개의 데이터 세트가 있습니다.

1. sales.xlsb 

2. customer.xlsb 

이를 Power Pivot으로 가져오려면 다음과 같이 하세요.

1. Power Pivot 탭을 클릭하고 관리를 선택합니다. 새 창이 열립니다
2. 홈으로 가서 외부 데이터 가져오기를 클릭하고 기타 원본에서를 선택합니다
3. 아래로 스크롤하여 Excel 파일을 클릭합니다

다른 소스에서 데이터 가져오기. 이미지: 작성자.

4. 이제 팝업에서 찾아보기 를 클릭하고 customer.xlsb 파일을 선택합니다 

5. 첫 행을 열 머리글로 사용을 체크하고 다음을 클릭합니다

Excel 파일을 Power Pivot으로 가져옵니다. 이미지: 작성자.

다음 창에서 미리 보기 및 필터를 클릭해 가져오기 전에 데이터 모양을 확인합니다. 만족스러우면 확인을 누르면 모든 행이 성공적으로 전송되었음을 표시합니다. 그런 다음 닫기를 클릭합니다.

선택한 데이터 미리 보기. 이미지: 작성자. 

같은 과정을 sales.xlsb 파일에도 반복합니다. 그러면 화면 하단에 두 파일이 가져온 상태로 표시됩니다. 더블 클릭해 이름을 바꾸세요. 

두 파일 모두 가져옴. 이미지: 작성자.

관계 및 데이터 모델 구축

이제 데이터가 Power Pivot에 로드되었으니, 테이블이 어떻게 연결되는지 Excel이 이해할 수 있도록 연결할 차례입니다. 이 단계가 모든 보고의 토대가 됩니다.

테이블 간 관계 만들기

Sales 테이블과 Customers 테이블 사이에 관계를 만들려면 다음을 수행하세요.

1. 홈 탭에서 다이어그램 보기를 클릭합니다. 가져온 두 테이블이 표시됩니다
2. Sales 테이블의 CustomerID 를 클릭합니다
3. 이를 Customer 테이블의 CustomerID 로 끌어다 놓아 두 테이블 사이에 관계를 만듭니다

참고: 관계를 편집하려면 선을 마우스 오른쪽 버튼으로 클릭하고 관계 편집..을 클릭합니다. 창에서 관계를 만들 열을 선택하세요.

테이블 간 관계 만들기. 이미지: 작성자.

이 관계에서는 한 고객이 Sales 테이블에 여러 번 나타날 수 있지만, Customers 테이블에는 각 고객이 한 번만 나타납니다. 이는 간단한 일대다 관계로, 조회 수식 없이도 피벗 테이블에서 두 테이블의 필드를 함께 사용하고 계산할 수 있습니다.

스타 스키마로 설계하기

스타 스키마는 Power Pivot 모델을 구조화하는 가장 간단한 방법 중 하나입니다. 테이블을 정돈하고 계산을 예측 가능하게 만듭니다.

먼저 사실 테이블을 선택해야 합니다. 이 경우 Sales가 날짜, 고객, 제품, 수량, 금액 등 거래 레코드를 보유하므로 사실 테이블이 됩니다.

다음으로 차원 테이블을 식별합니다. 이는 Sales의 데이터를 설명합니다. 일반적인 예시는 다음과 같습니다.

• Customers(기본 키: CustomerID)
• Products(기본 키: ProductID)
• Regions(기본 키: RegionID)

각 차원 테이블에는 기본 키가 있습니다. 이 키를 사실 테이블의 해당 외래 키에 연결합니다.

• Customers.CustomerID → Sales.CustomerID
• Products.ProductID → Sales.ProductID
• Regions.RegionID → Customers.RegionID

연결이 완료되면 Sales 테이블이 중앙에 놓이고 주변으로 차원 테이블이 방사형으로 배치됩니다. 이것이 스타 형태입니다. 이 구조는 모델을 명료하게 유지하고 계산을 빠르게 하며 보고 일관성을 높입니다.

스타 스키마 만들기. 이미지: 작성자.

계산 열 추가

관계 설정을 마쳤다면, 데이터 모델에서 직접 새 필드를 만들 수 있습니다.

1. 데이터 보기로 전환합니다

2. 테이블 끝의 비어 있는 열 추가 필드를 선택합니다.

3. = [TotalAmount] / [Qty]를 입력하고 Enter 키를 눌러 전체 열에 채우게 합니다

4. 헤더 이름을 PricePerUnit으로 변경합니다

이처럼 계산 열은 테이블 자체의 일부가 됩니다. 모델에 저장되고, 데이터 새로 고침 시 함께 갱신되며, 이후 만드는 모든 피벗 테이블이나 DAX 측정값에서 사용할 수 있습니다.

계산 열 추가. 이미지: 작성자.

분석을 위한 DAX 수식 작성

모델이 준비되었으니, 이제 데이터를 분석할 DAX 수식을 만들어 보겠습니다. 이 수식은 보고서 내에서 합계, 비교, 시계열 기반 계산을 구축하는 데 도움을 줍니다.

측정값 만들기

피벗 테이블 내에서 자동으로 새로 고쳐지는 계산이 필요할 때는 측정값을 사용하세요.

측정값을 만들려면:

1. Power Pivot 창을 엽니다

2. 홈 > 계산 > 새 측정값으로 이동합니다

3. = SUM(Sales[TotalAmount]) 같은 수식을 입력합니다

4. 이름을 Total Sales로 지정하고 확인을 선택합니다

측정값 만들기. 이미지: 작성자. 

전체 대비 백분율 측정값 추가

다음 수식을 사용해 전체 대비 백분율 측정값을 추가할 수도 있습니다.

= DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Regions)))

이 측정값은 각 지역의 전체 매출 기여도를 보여줍니다.

전체 대비 백분율 측정값 추가. 이미지: 작성자.

시간 지능 사용

시간 지능 함수는 날짜, 월, 분기, 연도에 따라 데이터가 어떻게 변하는지 이해하는 DAX 수식입니다. 연초 대비 누계(YTD)를 계산하고, 이전 기간과 비교하며, 필터를 수동으로 조정하지 않고도 추세를 평가할 수 있게 합니다.

모델에서 이러한 함수가 작동하려면 먼저 올바른 날짜 테이블이 필요합니다.

날짜 테이블 설정

테이블을 설정하려면: 

1. Power Pivot > 데이터 모델에 추가로 이동합니다
2. Power Pivot에서 테이블을 선택하고 디자인 > 날짜 테이블로 표시를 선택합니다

데이터 테이블 만들기. 이미지: 작성자.

3. 이제 홈 > 다이어그램 보기에서 Date[Date] → Sales[OrderDate]로 관계를 만듭니다.

Date Table[Date]를 Sales[OrderDate]에 연결. 이미지: 작성자.

시간 지능 측정값 만들기

Date 테이블이 준비되면, 기간별 성과를 평가하는 측정값을 만들 수 있습니다.

연초 대비 누계(YTD):

Total Sales YTD :=
TOTALYTD([Total Sales], 'Date Table'[Date])

전년 동기 비교:

Sales Last Year :=
CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date Table'[Date]))

시간 지능 계산. 이미지: 작성자. 

측정값을 준비했으면 Excel로 돌아가 reate a PivotTable using 데이터 모델을 사용해 피벗 테이블을 만듭니다. 그런 다음 행 영역에는 날짜 테이블의 필드를, 값에는 Total Sales, Total Sales YTD, Sales Last Year를 배치합니다. 

이렇게 하면 모델 내부의 Date 테이블과 시간 지능 측정값이 함께 작동하는 방식을 확인할 수 있습니다.

총매출, YTD, 전년 수치를 보여주는 피벗 테이블. 이미지: 작성자. 

자주 쓰이는 DAX 패턴

데이터를 빠르게 분해하고 흔한 질문에 답하는 데 유용한 DAX 수식이 자주 등장합니다. 많은 모델에서 잘 작동하는 두 가지 패턴은 다음과 같습니다.

카테고리별 평균:

Average Sales Per Category :=
AVERAGE(Sales[TotalAmount])

날짜 기준 러닝 합계:

Running Total Sales :=
CALCULATE(
    [Total Sales],
    FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date]))
)

측정값을 만들 때는 다음 습관을 가지면 좋습니다. 

• 이름을 명확하게 짓기
• 수식을 읽기 쉽게 유지하기
• 측정값이 길어지면 변수(VAR) 사용하기. 

이렇게 하면 시간이 지나 다시 모델을 볼 때 이해하기 쉬워집니다.

모델 시각화 및 상호작용

모델과 측정값을 준비했으니, 이제 데이터를 실시간으로 탐색하고 조정할 수 있는 시각화로 바꿔봅시다.

피벗 테이블과 피벗 차트 만들기

연결된 테이블과 직접 작업할 수 있도록 데이터 모델에서 피벗 테이블을 삽입하는 방법은 다음과 같습니다.

1. Excel 시트를 엽니다
2. 삽입 > 피벗 테이블 > 데이터 모델에서로 이동합니다
3. 새 워크시트를 선택합니다

이제 피벗 테이블 필드 창에서 어떤 테이블의 필드든 끌어올 수 있습니다. 예를 들어:

• Regions 테이블의 RegionName을 행으로 끌어옵니다
• Total Sales를 값으로 끌어옵니다

앞서 관계를 구축했기 때문에 Excel이 자동으로 모든 것을 함께 가져옵니다.

Power Pivot 데이터를 사용해 피벗 테이블 만들기. 이미지: 작성자.

시각화가 필요하면 피벗 테이블 내부를 클릭한 후 삽입 > 피벗 차트로 가서 차트 유형(예: 묶은 세로 막대형)을 선택하고 확인합니다. 차트는 피벗 테이블과 연결되어 함께 업데이트됩니다.

피벗 차트 추가. 이미지: 작성자.

슬라이서와 필터 추가

슬라이서는 버튼 형태의 빠른 필터로 보고서를 대화형으로 만들어 줍니다. 추가하려면:

1. 피벗 테이블을 클릭합니다
2. 삽입 > 슬라이서로 이동합니다
3. RegionName이나 ProductName 같은 필드를 선택합니다

슬라이서는 시트의 상자 형태로 표시됩니다. 다양한 항목을 클릭하면 피벗 테이블과 차트가 즉시 업데이트됩니다. 피벗 테이블이 여러 개라면, 하나의 슬라이서를 모두에 연결하여 페이지 전반에서 일관된 필터링을 적용할 수 있습니다.

슬라이서 추가. 이미지: 작성자.

KPI 구축

KPI는 시트에 추가 계산을 넣지 않고도 목표 대비 성과를 한눈에 볼 수 있게 해줍니다. 만드는 방법은 다음과 같습니다.

1. Power Pivot 창에서 KPI > 새 KPI로 이동합니다
2. 기준 측정값으로 Total Sales를 설정합니다
3. 절대값을 사용하고, 목표(예: 4000)를 입력하며, 임계값을 조정하고 아이콘 스타일을 선택합니다
4. 확인을 클릭해 KPI를 만듭니다

측정값의 KPI 설정. 이미지: 작성자.

5. 피벗 테이블 필드 창에서 Sales 테이블을 확장한 뒤 Total Sales를 확장합니다
6. 그 안에서 Total Sales와 Status를 값 영역으로 끌어다 놓습니다

이제 임계값 대비 목표 성과를 확인할 수 있습니다. 

Excel 피벗 테이블에서 KPI 상태 표시. 이미지: 작성자.

Power Pivot 성능 최적화

모델을 만들었으면, 빠르고 사용하기 쉬운 상태를 유지하고자 합니다. Power Pivot은 대용량 데이터도 처리하지만, 몇 가지 작은 조정으로 시간이 지나 데이터를 더 추가하더라도 파일이 민첩하게 유지됩니다.

모델 크기 줄이기

모델이 가벼울수록 더 빠르게 동작하므로, 불필요한 항목은 제거하세요.

데이터 보기에서 사용하지 않는 열을 삭제할 수 있습니다. 피벗 테이블에 나타나지 않는 열이라도 메모리를 차지하므로, 정리하면 모델이 깔끔해집니다.

새 데이터를 가져올 때는 Power Query로 모델에 들어오기 전에 행과 열을 필터링하세요. 이렇게 하면 필요한 필드만 로드되어 전체가 더 깔끔하게 유지됩니다.

계산 열은 각 행에 값을 저장하므로 꼭 필요한 경우가 아니라면 피하는 것이 좋습니다. 반면 측정값은 피벗 테이블이 필요할 때만 계산되므로 더 효율적입니다. 

효율적인 데이터 형식 선택

Power Pivot은 데이터 형식에 따라 데이터를 다르게 압축합니다. 올바른 형식을 사용하면 눈에 띄는 차이가 납니다.

데이터 보기에서 열을 선택하고 리본의 데이터 형식에서 가장 정확한 형식을 선택하세요. 예:

• 정수 > 정수
• 소수 값 > 십진수
• 수치 연산에 쓰지 않는 ID나 코드 > 텍스트

올바른 형식을 선택하면 Power Pivot이 열을 더 잘 압축해 크기를 줄이고 계산 속도를 높입니다.

올바른 데이터 형식 확인 및 사용. 이미지: 작성자.

새로 고침 및 계산 문제 처리

피벗 테이블에 최신 데이터가 반영되지 않으면 Power Pivot 탭에서 모두 새로 고침을 클릭하세요. 원본 파일에서 모든 내용을 다시 로드합니다.

숫자가 이상해 보이면 다이어그램 보기를 열어 관계를 점검하세요. 누락되었거나 끊긴 관계는 합계가 튀거나 필터가 잘못 적용되는 원인이 됩니다.

복잡한 측정값에서 특히 DAX 오류가 발생한다면, 수식이 간접적으로 자기 자신을 참조하는 경우가 많습니다. 이때는 더 단순한 로gic 또는 VAR 블록을 사용해 순환 참조를 해소하세요.

Power Query 및 Power BI와의 통합

Power Pivot의 장점 중 하나는 Microsoft 데이터 스택과 쉽게 연동된다는 점입니다. 모델에 들어오기 전에 데이터를 정리하고 변형하기 위해 Power Query를 사용할 수 있고, 대화형 대시보드가 필요할 때 전체 모델을 Power BI로 이동할 수 있습니다.

Power Query에서 데이터 정리 및 변환

Power Query는 Power Pivot으로 로드하기 전에 데이터를 준비하기에 가장 좋은 곳입니다. 사전에 정리, 필터링, 변형하여 모델을 체계적으로 유지할 수 있습니다.

Power Query는 데이터 > 텍스트/CSV에서 > 변환으로 열 수 있습니다. 그러면 편집기로 데이터가 들어오며, 여기에서 다음을 수행할 수 있습니다.

• 중복 행 제거
• 열 이름 변경 또는 순서 재배치
• 불필요한 값 필터링
• 모델에 들어오기 전에 데이터 형식 변경

Power Query는 창의 오른쪽에 각 단계를 기록합니다. 즉, 파일을 새로 고침할 때마다 정리가 자동으로 실행됩니다.

모든 것이 올바르면 닫기 및 로드 대상을 선택하고, 데이터 모델을 지정하세요. 정리된 데이터가 Power Pivot으로 바로 로드됩니다.

모델을 Power BI로 내보내기

더 풍부한 시각화나 공유 대시보드가 필요할 때 Power Pivot 모델을 Power BI로 가져갈 수 있습니다. 방법은 다음과 같습니다.

1. Excel 통합 문서를 저장합니다
2. Power BI Desktop을 엽니다
3. 데이터 가져오기 > Excel 통합 문서로 이동합니다
4. 파일을 선택합니다

Power BI는 Power Pivot에 존재하는 그대로 테이블과 관계를 가져옵니다. 여기서 대시보드를 만들고 팀과 협업하며 예약 새로 고침을 설정해 수동 단계 없이도 보고서를 최신 상태로 유지할 수 있습니다.

지속 가능한 모델을 위한 모범 사례

모델이 커질수록 정리 정돈을 유지하는 것이 업데이트, 디버깅, 확장에 큰 도움이 됩니다. 시간이 지나도 모델을 깔끔하고 신뢰할 수 있게 유지하는 몇 가지 습관은 다음과 같습니다.

명명 규칙과 구성

몇 주나 몇 달 뒤 파일로 돌아왔을 때, 명확한 이름은 큰 차이를 만듭니다. 예를 들어 Total_Sales, Total_Quantity, Profit_Margin 같이 읽기 쉬운 측정값 이름을 사용해, 각 측정값이 무엇을 의미하는지 항상 알 수 있도록 하세요.

또한 Power Pivot 창에서 관련 측정값을 표시 폴더로 묶을 수 있습니다. 모델이 커질수록 필요한 계산을 더 쉽게 찾을 수 있습니다.

데이터 검증

숫자를 신뢰하기 전에 다음과 같은 간단한 점검을 하세요.

• 원본 데이터의 합계를 피벗 테이블의 합계와 비교합니다
• 다음과 같은 간단한 DAX 검사 사용:

• COUNTROWS()로 테이블의 행 수 확인

• DISTINCTCOUNT()로 고객이나 제품 등 고유 값 검증

이런 작은 테스트로 더 큰 문제로 번지기 전에 누락된 관계, 잘못된 필터, 데이터 이슈를 발견할 수 있습니다.

모델 유지 보수 및 업데이트

새 데이터가 도착하면 Power Pivot 탭에서 새로 고침 또는 모두 새로 고침을 선택하세요. 그러면 Power Pivot이 연결된 소스에서 모든 내용을 다시 로드합니다.

새 관계 추가나 핵심 측정값 재작성 같은 큰 구조 변경을 하기 전에는 파일의 백업 사본을 저장하세요. 계획대로 되지 않을 경우 안전하게 되돌릴 수 있습니다.

마무리

Power Pivot은 데이터를 한곳에 모으고, 명확하고 신뢰할 수 있는 보고서를 구축하도록 도와줍니다. 모델을 설정한 뒤에는 숫자를 탐색하고 시각화를 만들며 한 번의 새로 고침으로 모두 업데이트하세요.

Excel의 전체 도구 세트를 배우고 싶다면 Data Analysis with Excel Power Tools 트랙과 (물론) Power Pivot in Excel 과정을 확인해 보세요.