현대의 엑셀 대시보드는 단순한 차트와 표를 넘어 진화했습니다. 파워 쿼리(Power Query)로 데이터를 변환하고, 파워 피벗(Power Pivot)으로 고급 데이터 모델링과 분석을 수행하며, VBA로 자동화와 상호작용 기능을 더하면 전문가 수준의 대시보드를 만들 수 있습니다.
이 튜토리얼에서는 파워 쿼리, 파워 피벗, VBA를 활용해 고급 엑셀 대시보드를 구축하는 방법을 단계별로 살펴보겠습니다.
여러 지역에서 제품을 판매하는 가상의 소매 회사를 위한 매출 대시보드를 만들어 보겠습니다. 실습에 사용할 데이터셋은 다음과 같은 테이블로 구성되어 있습니다.
- Sales(매출) – 거래 데이터
- Products(제품) – 제품 정보 및 카테고리
- Customers(고객) – 고객 정보
- Regions(지역) – 지역 정보
1단계: 파워 쿼리로 데이터 가져오기
데이터 불러오기
- 데이터 탭 >> 데이터 가져오기(Get Data) >> 파일에서(From File) >> 텍스트/CSV에서(From Text/CSV)를 선택합니다.
- sales_data.txt 파일을 찾아 가져오기(Import)를 클릭합니다.
- 파워 쿼리 편집기가 열리면 데이터를 검토한 후 다음과 같은 변환 작업을 진행합니다.
- 각 열의 데이터 형식을 변경합니다.
- 아무 열이나 마우스 오른쪽 버튼으로 클릭 >> 형식 변경(Change Data Type) >> 원하는 데이터 형식을 선택합니다.
- OrderDate → Date(날짜)
- Quantity → Whole Number(정수)
- UnitPrice, Discount → Decimal Number(소수)
- 중복된 행 제거(Remove Duplicates) 기능으로 중복 데이터를 정리합니다.
- Products.csv, Customers.csv, Dates.csv 파일에도 동일한 방식으로 가져오기를 반복하며, 각 파일에 맞는 데이터 형식 변환을 적용합니다.
파워 쿼리로 데이터 변환하기
파워 쿼리를 사용해 매출 데이터에 유용한 계산 열을 추가해 보겠습니다.
계산 열 추가:
- 파워 쿼리 편집기에서 Sales 데이터를 선택합니다.
- 열 추가(Add Column) 탭 >> 사용자 지정 열(Custom Column)을 선택합니다.
- 열 이름을 Revenue(매출액)로 입력합니다.
- 아래 수식을 삽입합니다.
= [Quantity] * [UnitPrice] * (1-[DiscountRate])
- 확인(OK)을 클릭합니다.
- 같은 방식으로 다른 사용자 지정 열을 추가합니다.
- 열 이름을 Profit(이익)으로 지정합니다.
- 아래 수식을 입력합니다.
Profit = [Revenue] - ([Quantity] * [UnitCost])
- 확인(OK)을 클릭합니다.
- 원가(Cost) 정보를 가져오려면 Products 테이블과 병합이 필요합니다.
테이블 병합으로 추가 인사이트 확보하기
- 파워 쿼리 편집기에서 Sales 데이터를 연 상태로,
- 홈(Home) 탭 >> 리본 메뉴의 쿼리 병합(Merge Queries)을 클릭합니다.
- Sales 테이블에서 ProductID를 선택합니다.
- Products 테이블을 선택하고 ProductID를 기준으로 조인합니다.
- 확인(OK)을 클릭합니다.
- 테이블 확장(Expand Table) 옵션을 클릭 >> 가져올 열 중 UnitCost만 선택합니다.
- 확인(OK)을 클릭합니다.
- 이제 Profit 열을 Expanded Products 단계 아래로 드래그합니다.
- 이익 금액이 정상적으로 계산됩니다.
- 데이터 변환이 끝나면 닫기 및 로드(Close & Load To…)를 클릭합니다.
- 데이터 가져오기(Import Data) 창에서;
- 연결만 만들기(Only Create Connection)를 선택합니다.
- 네 개의 테이블 모두 이 데이터를 데이터 모델에 추가를 체크합니다.
- 확인(OK)을 클릭합니다.
2단계: 파워 피벗으로 데이터 모델 구축하기
파워 피벗 열기:
리본 메뉴에 파워 피벗이 없다면 먼저 추가 기능을 활성화해야 합니다.
- 파일(File) 탭 >> 옵션(Options) >> 추가 기능(Add-ins)을 선택합니다.
- 관리(Manage) 드롭다운에서 COM 추가 기능(COM Add-ins)을 선택한 후 이동(Go)을 클릭합니다.
- Microsoft Power Pivot for Excel을 체크합니다.
- 확인(OK)을 클릭합니다.
- Power Pivot 탭 >> 관리(Manage)를 선택합니다.
- 파워 쿼리에서 가져온 데이터가 열립니다.
관계 설정하기
- 홈(Home) 탭 >> 다이어그램 뷰(Diagram View)를 선택합니다.
- Sales[ProductID]를 Products[ProductID]로 드래그합니다.
- Sales[CustomerID]를 Customers[CustomerID]로 드래그합니다.
- Customers[RegionID]를 Regions[RegionID]로 드래그합니다.
- 또는 디자인(Design) 탭 >> 관계 만들기(Create Relationship)를 선택한 후 일치하는 열을 지정할 수도 있습니다.
측정값(Measure) 만들기
- 파워 피벗에서 Sales 테이블을 클릭합니다.
- 홈(Home) 탭 >> 측정값(Measures) >> 새 측정값(New Measure)을 클릭합니다.
- 또는 홈 탭에서 계산 영역(Calculation Area)으로 이동합니다.
- 아래의 측정값들을 입력합니다.
- 총 매출(Total Revenue):
Total Revenue := SUM(Sales[Revenue])
- 총 이익(Total Profit):
Total Profit := SUM(Sales[Profit])
- 이익률(Profit Margin):
Profit Margin := DIVIDE([Total Profit], [Total Revenue], 0)
- 총 주문 수(Total Orders):
Total Orders:=COUNTA(Sales[OrderID])
- 평균 주문 금액(Average Order Value):
Average Order Value:=DIVIDE([Total Revenue], DISTINCTCOUNT(Sales[OrderID]), 0)
- 연간 누적 매출(YTD Revenue):
YTD Revenue:=CALCULATE([Total Revenue], DATESYTD(Sales[OrderDate]))
- 전년 동기 매출(Previous Year Revenue):
Previous Year Revenue:=CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Sales[OrderDate]))
- 전년 대비 성장률(YOY Growth):
YOY Growth := DIVIDE([Total Revenue] - [Previous Year Revenue], [Previous Year Revenue], 0)
3단계: 피벗 테이블로 대시보드 구성 요소 만들기
피벗 테이블 생성
- 삽입(Insert) 탭 >> 피벗 테이블(PivotTable) >> 데이터 모델에서(From Data Model)를 선택합니다.
- 또는 파워 피벗에서 홈 탭 >> 피벗 테이블(Pivot Table)을 선택합니다.
- 피벗 테이블 만들기(Create PivotTable) 창에서;
- 새 워크시트(New Worksheet)를 선택합니다.
- 확인(OK)을 클릭합니다.
- 총 매출, 총 이익, 이익률, 성장률 각각에 대해 별도의 피벗 테이블을 만듭니다.
- 대시보드 구조에 맞게 적절한 셀 위치에 배치합니다.
- 필요에 따라 통화 또는 백분율 서식을 적용합니다.
차트와 시각화
매출 추이 차트:
- 데이터 모델에서 피벗 테이블을 생성합니다.
- 행(Rows): Sales[OrderDate[Month]]
- 값(Values): Total Revenue
- 피벗 테이블 분석(PivotTable Analyze) 탭 >> 묶은 세로 막대형 차트(Clustered Column Chart)를 선택합니다.
상위 제품 차트:
- 데이터 모델에서 피벗 테이블을 생성합니다.
- 행(Rows): Products[ProductName]
- 값(Values): Total Revenue
- Total Revenue 기준 내림차순으로 정렬하고, 상위 10개만 필터링합니다.
- 피벗 테이블 분석 탭 >> 묶은 가로 막대형 차트(Clustered Bar Chart)를 선택합니다.
카테고리별 실적:
- 데이터 모델에서 피벗 테이블을 생성합니다.
- 행(Rows): Products[Category]
- 값(Values): Total Revenue, Total Profit, Profit Margin
- 피벗 테이블 분석 탭 >> 콤보(Combo) >> 묶은 세로 막대형 – 보조 축의 꺾은선형(Clustered Column – Line on Secondary Axis)을 선택합니다.
4단계: 인터랙티브 요소 추가하기
슬라이서와 타임라인 삽입:
제품 및 지역 슬라이서 만들기:
- 피벗 테이블 분석(PivotAnalyze) 탭 >> 슬라이서 삽입(Insert Slicer)을 선택합니다.
- Products[Category]와 Customers[Region]을 선택합니다.
- 확인(OK)을 클릭합니다.
- 대시보드 디자인에 어울리도록 서식을 다듬습니다.
날짜 필터용 타임라인 추가:
- 피벗 테이블 분석 탭 >> 슬라이서 삽입(Insert Slicer)을 선택합니다.
- Sales[OrderDate]를 선택합니다.
- 확인(OK)을 클릭합니다.
- 타임라인을 차트 위쪽에 배치합니다.
- 대시보드 스타일에 맞게 서식을 조정합니다.
모든 슬라이서를 피벗 테이블에 연결하기:
- 각 슬라이서를 마우스 오른쪽 버튼으로 클릭 >> 보고서 연결(Report Connections)을 선택합니다.
- 모든 피벗 테이블에 체크하여 필터가 전체적으로 적용되도록 합니다.
- 확인(OK)을 클릭합니다.
5단계: VBA로 자동화와 인터랙티브 기능 강화하기
VBA를 활용하면 대시보드를 더욱 역동적으로 만들고 탐색 편의성을 높일 수 있습니다.
예제 1: 데이터 새로 고침 버튼
- 개발 도구(Developer) 탭 >> 삽입(Insert) >> 단추(Button)를 선택합니다.
- 버튼 이름을 Refresh Data(데이터 새로 고침)로 변경합니다.
- 버튼을 오른쪽 클릭 >> 매크로 지정(Assign Macro) >> 새로 만들기(New)를 선택합니다.
- 아래 코드를 복사해서 붙여넣습니다.
VBA 코드:
Sub RefreshDashboard()
ThisWorkbook.RefreshAll
MsgBox "대시보드 데이터가 새로 고침되었습니다!", vbInformation
End Sub
예제 2: 대시보드 초기화 버튼
- 개발 도구 탭 >> 삽입(Insert) >> 단추(Button)를 선택합니다.
- 버튼 이름을 Reset Dashboard(대시보드 초기화)로 변경합니다.
- 버튼을 오른쪽 클릭 >> 매크로 지정(Assign Macro) >> 새로 만들기(New)를 선택합니다.
- 아래 코드를 복사해서 붙여넣습니다.
VBA 코드:
Sub ResetDashboardFilter()
Dim ws As Worksheet
Dim slicer As slicerCache
Dim pivotTable As pivotTable
' 모든 슬라이서 캐시 초기화
For Each slicer In ActiveWorkbook.SlicerCaches
slicer.ClearAllFilters
Next slicer
' 타임라인 초기화 (SlicerCaches 사용)
For Each slicer In ActiveWorkbook.SlicerCaches
If slicer.SourceType = xlTimeline Then
slicer.ClearAllFilters
End If
Next slicer
' 피벗 테이블 새로 고침
For Each ws In ActiveWorkbook.Worksheets
For Each pivotTable In ws.PivotTables
pivotTable.RefreshTable
Next pivotTable
Next ws
MsgBox "대시보드 필터가 초기화되었습니다!", vbInformation, "Reset Filters"
End Sub
6단계: 대시보드 레이아웃 구성하기
Dashboard라는 이름의 새 워크시트를 만듭니다.
- 대시보드 구조 설계:
- 대시보드 제목과 날짜 필터 배치
- KPI 섹션 (매출, 이익, 이익률, 성장률)
- 차트 (매출 추이, 상위 제품, 지역별 실적)
- 데이터 요약 테이블과 상세 분석 섹션도 함께 만들 수 있습니다.
- 일관된 서식 적용:
- 전체적으로 통일된 색 구성표를 사용합니다.
- 모든 요소를 깔끔하게 정렬합니다.
- 테두리를 추가해 대시보드 섹션을 구분합니다.
- 데이터 테이블에 조건부 서식 적용:
- 데이터 막대와 색조 스케일을 활용해 중요한 값을 강조합니다.
- KPI 아이콘을 추가해 목표 대비 실적을 한눈에 보여줍니다.
- 사용 안내 시트 만들기:
- Instructions라는 이름의 새 워크시트를 생성합니다.
- 대시보드 사용 방법을 설명하는 텍스트를 추가합니다.
- 데이터 새로 고침, 인터랙티브 기능, 사용 가능한 기능에 대한 정보를 포함합니다.
7단계: 테스트 및 문제 해결
모든 인터랙티브 요소 테스트:
- 슬라이서가 관련된 모든 시각화 요소를 올바르게 필터링하는지 확인합니다.
- 버튼이 매크로를 정상적으로 실행하는지 점검합니다.
- 타임라인 컨트롤이 데이터 범위를 제대로 처리하는지 확인합니다.
- 카테고리(Category) 슬라이서에서 Beauty를 선택합니다.
- 타임라인에서 2024년 1월~4월을 선택합니다.
- 필터 선택에 따라 대시보드 전체가 업데이트됩니다.
필터 초기화 테스트:
- Reset Dashboard 버튼을 클릭합니다.
- "대시보드 필터가 초기화되었습니다"라는 메시지가 나타납니다.
- 확인(OK)을 클릭합니다.
- 모든 필터가 제거되고 깨끗한 상태의 대시보드가 표시됩니다.
데이터 새로 고침 테스트:
- 원본 데이터를 수정합니다.
- Refresh Data 버튼을 클릭합니다.
- 모든 계산값이 정확히 업데이트되는지 확인합니다.
- 연결 오류나 수식 오류가 없는지 점검합니다.
실습 파일 다운로드
결론
지금까지의 단계를 따라 하면 강력한 비즈니스 인텔리전스 도구로 활용할 수 있는 고급 엑셀 대시보드를 완성할 수 있습니다. 이 대시보드는 데이터 준비에는 파워 쿼리, 모델링과 분석에는 파워 피벗, 그리고 향상된 상호작용에는 VBA의 장점을 모두 결합했습니다. 제품, 지역, 기간별 매출 실적을 손쉽게 분석할 수 있는 사용자 친화적인 인터페이스를 제공하므로, 직접 실험해 보며 더 많은 고급 기능을 추가해 보세요.
무료 고급 엑셀 연습 문제와 해설을 받아보세요!