TL;DR
- Power Query로 데이터 준비, Power Pivot으로 동적 분석 = 최강 조합
- 관계형 모델링으로 복잡한 비교도 단순하게
- 샘플 데이터와 단계별 스크린샷으로 30분 안에 구축 가능
- 한 번 만들면 매월 재사용, 슬라이서로 실시간 분석
왜 Power Pivot까지 써야 하나?
지난 포스트에서 Power Query로 테이블 비교를 자동화했습니다.
그런데 실무에서는 이런 요구가 더 있죠:
“9월 vs 10월만 볼게 아니라, 1월부터 12월까지 한 눈에 보고 싶어요”
“부서별로, 제품별로 필터링하면서 보고 싶어요”
“차트로 트렌드도 같이 보여주세요”
Power Query만으로는 한계가 있습니다. Power Pivot을 더하면 이 모든 게 가능해집니다.
실습용 샘플 데이터 준비
아래 데이터를 Excel에 복사해서 따라해보세요!
Sheet1: Orders_September
발주번호 제품명 부서 수량 단가 금액 발주일자
PO-001 노트북 IT팀 5 1500000 7500000 2024-09-05
PO-002 모니터 IT팀 10 300000 3000000 2024-09-08
PO-003 의자 관리팀 20 150000 3000000 2024-09-10
PO-004 책상 관리팀 15 200000 3000000 2024-09-12
PO-005 프린터 영업팀 3 500000 1500000 2024-09-15
PO-006 태블릿 영업팀 8 800000 6400000 2024-09-18
PO-007 키보드 IT팀 30 50000 1500000 2024-09-20
Sheet2: Orders_October
발주번호 제품명 부서 수량 단가 금액 발주일자
PO-001 노트북 IT팀 5 1500000 7500000 2024-10-05
PO-002 모니터 IT팀 12 320000 3840000 2024-10-08
PO-003 의자 관리팀 20 150000 3000000 2024-10-10
PO-005 프린터 영업팀 5 480000 2400000 2024-10-15
PO-006 태블릿 영업팀 10 850000 8500000 2024-10-18
PO-008 스캐너 관리팀 2 1000000 2000000 2024-10-22
PO-009 회의용TV 영업팀 1 3000000 3000000 2024-10-25
Sheet3: Departments (마스터 테이블)
부서코드 부서명 부서장 인원수
DEPT-01 IT팀 김철수 15
DEPT-02 관리팀 박영희 8
DEPT-03 영업팀 이민수 20
Sheet4: Products (마스터 테이블)
제품코드 제품명 카테고리 공급업체
PROD-01 노트북 IT장비 A사
PROD-02 모니터 IT장비 B사
PROD-03 의자 사무가구 C사
PROD-04 책상 사무가구 C사
PROD-05 프린터 사무기기 D사
PROD-06 태블릿 IT장비 A사
PROD-07 키보드 IT액세서리 E사
PROD-08 스캐너 사무기기 D사
PROD-09 회의용TV 회의실장비 F사
Step 1: Power Query로 데이터 준비
1.1 테이블 변환
- 각 시트를 선택 → “테이블로 변환” (Ctrl + T)
- 테이블 이름 지정:
- Orders_September
- Orders_October
- Departments
- Products
1.2 통합 테이블 생성 (Power Query)
let
// 9월 데이터에 Period 추가
Sept = Excel.CurrentWorkbook(){[Name="Orders_September"]}[Content],
SeptWithPeriod = Table.AddColumn(Sept, "Period", each "2024-09"),
SeptWithSource = Table.AddColumn(SeptWithPeriod, "DataSource", each "September"),
// 10월 데이터에 Period 추가
Oct = Excel.CurrentWorkbook(){[Name="Orders_October"]}[Content],
OctWithPeriod = Table.AddColumn(Oct, "Period", each "2024-10"),
OctWithSource = Table.AddColumn(OctWithPeriod, "DataSource", each "October"),
// 두 테이블 합치기 (Union)
Combined = Table.Combine({SeptWithSource, OctWithSource}),
// 데이터 타입 설정
TypedData = Table.TransformColumnTypes(Combined, {
{"발주번호", type text},
{"제품명", type text},
{"부서", type text},
{"수량", Int64.Type},
{"단가", Currency.Type},
{"금액", Currency.Type},
{"발주일자", type date},
{"Period", type text},
{"DataSource", type text}
}),
// RowHash 추가 (비교용 고유값)
AddRowHash = Table.AddColumn(
TypedData,
"RowHash",
each Text.Combine({[발주번호], [제품명], Text.From([수량]), Text.From([금액])}, "|"),
type text
),
// 중복 체크용 인덱스
GroupedWithIndex = Table.Group(
AddRowHash,
{"발주번호", "Period"},
{{"AllRows", each Table.AddIndexColumn(_, "DupIndex", 1, 1), type table}}
),
// 동적으로 확장할 열 리스트 생성 (그룹 키 제외)
ColumnsToExpand = List.Difference(
Table.ColumnNames(AddRowHash) & {"DupIndex"},
{"발주번호", "Period"}
),
FinalTable = Table.ExpandTableColumn(
GroupedWithIndex,
"AllRows",
ColumnsToExpand,
ColumnsToExpand
)
in
FinalTable
쿼리 이름: “Orders_Combined”로 저장
1.3 비교 분석용 쿼리
let
// Combined 테이블 참조
Source = Orders_Combined,
// 9월 데이터만
SeptData = Table.SelectRows(Source, each [Period] = "2024-09"),
// 10월 데이터만
OctData = Table.SelectRows(Source, each [Period] = "2024-10"),
// Full Outer Join
Joined = Table.NestedJoin(
SeptData, {"발주번호", "DupIndex"},
OctData, {"발주번호", "DupIndex"},
"October",
JoinKind.FullOuter
),
// 10월 데이터 확장
Expanded = Table.ExpandTableColumn(
Joined, "October",
{"제품명", "부서", "수량", "단가", "금액", "RowHash"},
{"제품명_Oct", "부서_Oct", "수량_Oct", "단가_Oct", "금액_Oct", "RowHash_Oct"}
),
// 변경 유형 판별
AddChangeType = Table.AddColumn(
Expanded, "ChangeType",
each
if [RowHash] = null then "Added"
else if [RowHash_Oct] = null then "Removed"
else if [RowHash] = [RowHash_Oct] then "Unchanged"
else "Modified",
type text
),
// 차이 계산
AddDifference = Table.AddColumn(
AddChangeType, "금액차이",
each
if [ChangeType] = "Modified" then [금액_Oct] - [금액]
else if [ChangeType] = "Added" then [금액_Oct]
else if [ChangeType] = "Removed" then -[금액]
else 0,
Currency.Type
)
in
AddDifference
쿼리 이름: “Comparison_Results”로 저장
Step 2: Power Pivot 데이터 모델 구축
2.1 데이터 모델에 테이블 추가
- Power Pivot 탭 → “관리” 클릭
- 다음 테이블들을 데이터 모델에 추가:
- Orders_Combined (팩트 테이블)
- Departments (차원 테이블)
- Products (차원 테이블)
- Comparison_Results (분석 테이블)
2.2 관계 설정 (Relationships)
Power Pivot에서 “다이어그램 뷰” 클릭:
관계 1: Orders_Combined[부서] → Departments[부서명]
관계 2: Orders_Combined[제품명] → Products[제품명]
관계 설정 시 주의사항:
– 카디널리티: 1:N (One to Many)
– 필터 방향: 단일 (Single)
– 활성 상태: 예
Note: Comparison_Results는 독립적인 분석 테이블이므로 별도 관계 설정이 필요하지 않습니다.
이미 Power Query에서 비교 분석이 완료된 결과를 담고 있기 때문입니다.
2.3 날짜 테이블 생성
방법 1: Power Query에서 생성 (권장)
– Power Query 편집기 → 새 쿼리 → 날짜 테이블 생성
– 테이블 이름: “달력”
– 자동으로 Date, 년, 월, 요일 등의 열이 생성됨
방법 2: Power Pivot에서 DAX로 생성
달력 =
CALENDAR(
DATE(2024, 1, 1),
DATE(2024, 12, 31)
)
관계 추가: Orders_Combined[발주일자] → '달력'[Date]
Step 3: DAX 측정값 생성
💡 측정값 조직화 팁:
– 측정값은 기술적으로 어느 테이블에서나 생성 가능하지만, 관리를 위해 관련 테이블에 생성하는 것이 좋습니다
– 아래 예시에서는 각 측정값을 가장 적합한 테이블에 생성하는 방법을 안내합니다
⚠️ 한글 테이블/열 이름 사용 시 주의:
– 한글 테이블명은 반드시 작은따옴표로 감싸야 합니다 (예: ‘달력'[Date])
– 특수문자나 공백이 있는 이름도 마찬가지입니다
3.1 기본 측정값
측정값 생성 방법:
방법 1: 측정값 대화상자 사용 (권장)
1. Power Pivot 창에서 홈 탭 → 새 측정값 버튼 클릭
2. 대화상자에서:
– 테이블 이름 선택
– 측정값 이름 입력
– 수식란에 = 로 시작하는 DAX 수식 입력 (예: =AVERAGE(Orders_Combined[금액]))
– 확인 클릭
방법 2: 계산 영역에 직접 입력
1. Power Pivot 창에서 홈 탭 → 계산 영역 표시
2. 해당 테이블 탭 선택 후 계산 영역의 빈 셀 클릭
3. 측정값이름:=수식 형태로 입력 (예: Avg_Order_Amount:=AVERAGE(Orders_Combined[금액]))
Orders_Combined 테이뺔에서 생성:
// 총 발주 건수 (대화상자 수식은 = 로 시작)
Total_Orders = COUNTROWS(Orders_Combined)
// 총 발주 금액
Total_Amount = SUM(Orders_Combined[금액])
// 평균 주문 금액
Avg_Order_Amount = AVERAGE(Orders_Combined[금액])
// 고유 제품 수
Unique_Products = DISTINCTCOUNT(Orders_Combined[제품명])
Note: 대화상자를 통해 만들 때는 =만 사용하고, 계산 영역에 직접 입력할 때는 측정값명:=수식 형태로 사용합니다.
3.2 비교 분석 측정값
달력(또는 Calendar) 테이블에서 생성 (날짜 관련 계산):
// 이전 기간 금액
// 주의: 테이블명과 날짜 열 이름을 실제 테이블에 맞게 수정 필요
// 예시 1: 한글 테이블명인 경우 (작은따옴표 필수!)
Previous_Period_Amount =
CALCULATE(
[Total_Amount],
DATEADD('달력'[Date], -1, MONTH)
)
// 예시 2: Orders_Combined의 발주일자를 직접 사용하는 경우 (달력 테이블 관계 설정 없이)
Previous_Period_Amount_Alt =
CALCULATE(
[Total_Amount],
DATEADD(Orders_Combined[발주일자], -1, MONTH)
)
// 기간 대비 변화율
Period_Change_Rate =
DIVIDE(
[Total_Amount] - [Previous_Period_Amount],
[Previous_Period_Amount],
0
)
Comparison_Results 테이블에서 생성:
// 변경된 주문 수
Modified_Orders =
CALCULATE(
COUNTROWS(Comparison_Results),
Comparison_Results[ChangeType] = "Modified"
)
// 신규 주문 수
New_Orders =
CALCULATE(
COUNTROWS(Comparison_Results),
Comparison_Results[ChangeType] = "Added"
)
// 삭제된 주문 수
Removed_Orders =
CALCULATE(
COUNTROWS(Comparison_Results),
Comparison_Results[ChangeType] = "Removed"
)
3.3 고급 분석 측정값
달력 테이블에서 생성:
// 누적 금액 (YTD)
YTD_Amount =
CALCULATE(
[Total_Amount],
DATESYTD('달력'[Date])
)
// 이동 평균 (3개월)
Moving_Avg_3M =
CALCULATE(
AVERAGEX(
DATESINPERIOD('달력'[Date], LASTDATE('달력'[Date]), -3, MONTH),
[Total_Amount]
)
)
// 상위 5개 제품 금액 합계 (선택사항 - 복잡한 측정값 예시)
// 측정값 이름: Top_5_Products_Amount
// 수식란에 입력 (간단한 버전):
=CALCULATE(
SUM(Orders_Combined[금액]),
FILTER(
ALL(Products[제품명]),
RANKX(ALL(Products[제품명]), SUM(Orders_Combined[금액]), , DESC) <= 5
)
)
// 또는 더 간단하게 (특정 제품만 필터):
// =CALCULATE(SUM(Orders_Combined[금액]), Products[제품명] IN {"노트북", "모니터", "키보드", "마우스", "프린터"})
Step 4: 피벗 테이블과 대시보드 생성
4.1 기본 피벗 테이블
💡 Power Pivot의 핵심 – 관계 활용:
차원 테이블(Departments, Products)의 필드를 사용해야 관계가 작동하고 Power Pivot의 진정한 가치를 경험할 수 있습니다!
중요: 먼저 관계 설정 확인
– Power Pivot → 다이어그램 뷰에서 관계 확인:
– Orders_Combined[부서] → Departments[부서명]
– Orders_Combined[제품명] → Products[제품명]
– Orders_Combined[발주일자] → '달력'[Date] (선택사항)
- 삽입 → 피벗 테이블 → 데이터 모델 사용
- 기본 필드 배치 (Power Pivot 관계 활용):
- 행: Orders_Combined[Period] 또는 Orders_Combined[DataSource]
- 열: Departments[부서명] (차원 테이블 사용!)
- 값: [Total_Amount]
- 필터: Products[제품명] 또는 Products[카테고리] (차원 테이블 사용!)
“비어 있음” 행이 나타나는 경우:
– 날짜 관계가 제대로 설정되지 않았거나
– 일부 데이터에 날짜/부서/제품 정보가 누락된 경우
– 피벗 테이블 옵션에서 “빈 항목 표시 안 함” 선택 가능
4.2 비교 분석 피벗
행: Comparison_Results[ChangeType]
열: Comparison_Results[부서]
값: Count of 발주번호, Sum of 금액차이
결과 예시:
ChangeType | IT팀 | 관리팀 | 영업팀 | 총합계
-------------|-----------|-----------|-----------|----------
Added | 0 | 1 | 1 | 2
Modified | 1 | 0 | 2 | 3
Removed | 1 | 1 | 0 | 2
Unchanged | 2 | 1 | 0 | 3
4.3 슬라이서 추가
대시보드를 인터랙티브하게 만들기:
- Period 슬라이서: 기간 선택
- 부서 슬라이서: 부서별 필터
- 카테고리 슬라이서: 제품 카테고리 필터
- ChangeType 슬라이서: 변경 유형별 필터
4.4 조건부 서식 적용
피벗 테이블에 시각적 효과 추가:
// 색상 규칙
- Added: 녹색 배경
- Modified: 노란색 배경
- Removed: 빨간색 배경
- Unchanged: 회색 배경
// 아이콘 세트
- 증가: ▲ (녹색)
- 감소: ▼ (빨간색)
- 동일: ● (회색)
Step 5: 차트와 시각화
5.1 월별 추이 차트
피벗 차트 만들기:
1. 피벗 테이블 선택 → 삽입 → 피벗 차트 → 꺾은선 그래프
2. 피벗 테이블 필드 배치:
– 행: Orders_Combined[Period] 또는 ‘달력'[YearMonth]
– 값: [Total_Amount]
– 열: 없음 또는 Departments[부서명] (관계 활용!)
– 필터: 원하는 경우 Products[카테고리]
5.2 부서별 비교 차트
피벗 차트 만들기:
1. 피벗 테이블 선택 → 삽입 → 피벗 차트 → 세로 막대형 (첫 번째 아이콘)
2. 피벗 테이블 필드 배치:
– 행: Departments[부서명] (차원 테이블 활용!)
– 열: Orders_Combined[Period]
– 값: [Total_Amount]
– 필터: 필요시 Products[제품명] 또는 Products[카테고리]
5.3 변경 유형 분포 차트
피벗 차트 만들기:
1. Comparison_Results 테이블 기반 피벗 테이블 생성
2. 삽입 → 피벗 차트 → 원형 차트
3. 피벗 테이블 필드 배치:
– 행: Comparison_Results[ChangeType]
– 값: Comparison_Results[발주번호] (개수)
– 차트에서 데이터 레이블 백분율 표시 설정
5.4 KPI 카드
대시보드 상단에 배치할 핵심 지표:
┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐
│ 총 발주 금액 │ │ 전월 대비 │ │ 변경된 건수 │
│ ₩45,240,000 │ │ +15.3% │ │ 5건 │
└─────────────────┘ └─────────────────┘ └─────────────────┘
Step 6: 자동 새로고침 설정
6.1 데이터 연결 설정
데이터 → 연결 → 속성:
- ✅ 파일을 열 때 데이터 새로 고침
- ✅ 백그라운드에서 새로 고침
- ✅ 60분마다 새로 고침
6.2 Power Query 새로고침 순서
- Orders_September (원본)
- Orders_October (원본)
- Orders_Combined (통합)
- Comparison_Results (비교)
6.3 VBA로 원클릭 새로고침
Sub RefreshAllData()
' Power Query 새로고침
ActiveWorkbook.RefreshAll
' Power Pivot 모델 새로고침
ActiveWorkbook.Model.Refresh
' 피벗 테이블 새로고침
Dim pt As PivotTable
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
MsgBox "모든 데이터가 새로고침되었습니다!", vbInformation
End Sub
실무 활용 시나리오
시나리오 1: 월말 마감 대시보드
자동화 프로세스:
1. 매월 말일 17:00 – ERP에서 데이터 Export
2. 지정 폴더에 CSV 저장
3. Power Query 자동 감지 및 로드
4. Power Pivot 계산 자동 실행
5. 대시보드 자동 업데이트
6. Teams/이메일로 리포트 자동 발송
시나리오 2: 부서별 성과 분석
Orders_Combined 테이블에서 생성:
// 부서별 평균 주문 금액
Dept_Avg_Amount =
AVERAGEX(
VALUES(Departments[부서명]),
[Total_Amount]
)
// 부서별 순위
Dept_Rank =
RANKX(
ALL(Departments[부서명]),
[Total_Amount],
,
DESC
)
시나리오 3: 제품 카테고리 분석
Products 테이블 활용:
// 카테고리별 매출 비중
Category_Share =
DIVIDE(
CALCULATE(
[Total_Amount],
ALLEXCEPT(Products, Products[카테고리])
),
CALCULATE(
[Total_Amount],
ALL(Products)
),
0
)
트러블슈팅 가이드
문제 1: 관계 설정 오류
증상: “관계를 만들 수 없습니다. 모호한 관계가 생성됩니다”
해결책:
1. 기존 관계 확인 및 비활성화
2. 중복 키 제거
3. Bridge 테이블 생성
문제 2: DAX 계산 느림
증상: 피벗 테이블 새로고침 5분 이상
해결책:
// Before (느림)
Slow_Measure =
SUMX(
Orders_Combined,
CALCULATE(SUM(Orders_Combined[금액]))
)
// After (빠름)
Fast_Measure =
SUM(Orders_Combined[금액])
문제 3: 메모리 부족
증상: “메모리가 부족합니다” 오류
해결책:
1. 불필요한 컬럼 제거
2. 데이터 타입 최적화
3. 64비트 Excel 사용
다음 단계: Power BI로 진화
이 Excel 모델을 Power BI로 마이그레이션하면:
- 자동 새로고침: Power BI Service에서 스케줄 설정
- 공유 및 협업: 팀 전체가 실시간 접근
- 모바일 접근: Power BI 앱으로 언제 어디서나
- AI 인사이트: 자동 이상치 감지, 예측 분석
- Row Level Security: 부서별 권한 관리
완성된 대시보드 구조
┌────────────────────────────────────────────────────────────┐
│ 월간 발주 현황 대시보드 │
├────────────────────────────────────────────────────────────┤
│ [기간 선택] [부서 선택] [카테고리 선택] [🔄 새로고침] │
├────────────────────────────────────────────────────────────┤
│ ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐ │
│ │총 발주금액 │ │전월 대비 │ │신규 건수 │ │변경 건수 │ │
│ │45.2M │ │+15.3% │ │2건 │ │3건 │ │
│ └──────────┘ └──────────┘ └──────────┘ └──────────┘ │
├────────────────────────────────────────────────────────────┤
│ ┌─────────────────────────┐ ┌──────────────────────────┐ │
│ │ 월별 발주 트렌드 │ │ 변경 유형 분포 │ │
│ │ [꺾은선 그래프] │ │ [도넛 차트] │ │
│ └─────────────────────────┘ └──────────────────────────┘ │
│ ┌─────────────────────────┐ ┌──────────────────────────┐ │
│ │ 부서별 전월 대비 │ │ 상위 5개 제품 │ │
│ │ [막대 그래프] │ │ [테이블] │ │
│ └─────────────────────────┘ └──────────────────────────┘ │
├────────────────────────────────────────────────────────────┤
│ 상세 데이터 테이블 │
│ [피벗 테이블 - 드릴다운 가능] │
└────────────────────────────────────────────────────────────┘
실제 구현 결과
Before (수작업)
– 작업 시간: 3-4시간
– 오류 가능성: 높음
– 재사용성: 없음
– 인터랙티브: 불가능
After (Power Query + Power Pivot)
– 작업 시간: 30초 (새로고침)
– 오류 가능성: 없음
– 재사용성: 100%
– 인터랙티브: 실시간 필터링
ROI 계산
– 월 20시간 절감 = 연 240시간
– 시급 5만원 기준 = 연 1,200만원 절감
– 구축 시간: 2시간
– 투자 대비 수익: 100배 이상
마치며
Power Query와 Power Pivot의 조합은 단순한 도구가 아닙니다.
회계 실무자가 데이터 분석가로 진화하는 가장 빠른 길입니다.
이제 여러분도:
– 매월 반복되는 비교 작업을 자동화하고
– 경영진이 원하는 대시보드를 직접 만들고
– 데이터 기반 의사결정을 주도할 수 있습니다
샘플 파일 다운로드
📥 Power Pivot 테이블 비교 샘플.xlsx
– 완성된 데이터 모델
– 모든 DAX 측정값
– 대시보드 템플릿
관련 글
– Power Query로 테이블 비교 자동화하기
Tags: #PowerPivot #PowerQuery #Excel #데이터모델링 #DAX #회계실무 #자동화 #대시보드 #BusinessIntelligence
카테고리: 회계, 데이터 분석, Excel, Business Intelligence
© 2025 B.J.T. – Boundless Journey of Thought
https://www.boundlessjourneyofthought.com/
Leave a Reply