데이터에서 통찰력까지 파워 쿼리, 파워 피벗 및 VBA를 사용하여 고급 Excel 대시보드 구축

이 자습서에서는 파워 쿼리, 파워 피벗 및 VBA를 사용하여 고급 Excel 대시보드를 구축하는 방법을 보여줍니다.

여러 지역에 걸쳐 제품을 판매하는 가상의 소매 회사를 위한 판매 대시보드를 만들어 보겠습니다. 다음 테이블이 포함된 데이터 세트로 작업하겠습니다.

• 판매 – 거래 데이터가 포함되어 있습니다.
• 제품 – 제품 세부정보 및 카테고리.
• 고객 – 고객 정보.
• 지역 – 지리 정보.

1단계:데이터 가져오기에 파워 쿼리 사용

데이터 가져오기:

• 데이터 로 이동 탭>> 데이터 가져오기 선택>> 파일에서를 선택하세요.>>텍스트/CSV에서를 선택합니다. .
• sales_data.txt로 이동합니다. 파일>> 가져오기를 클릭하세요. .

• 파워 쿼리 편집기가 열리면 데이터를 검토하고 다음과 같이 변환합니다.
• 데이터 유형을 변경합니다.
• 열을 마우스 오른쪽 버튼으로 클릭하고>> 데이터 유형 변경을 선택합니다.>> 데이터 유형을 선택합니다. .
  • 주문 날짜 날짜까지 .
  • 수량 정수로 .
  • 단가 및 할인 십진수로 .

• 중복 항목 제거를 사용하여 중복 행을 제거합니다. .
• 적절한 데이터 유형 변환을 적용하여 Products.csv, Customers.csv 및 Dates.csv에 대한 가져오기 프로세스를 반복합니다.

파워 쿼리로 데이터 변환:

파워 쿼리를 사용하여 작업을 수행하여 판매 데이터를 향상시켜 보겠습니다.

계산된 열 추가:

• 파워 쿼리 편집기에서 판매 데이터를 선택합니다.
• 열 추가로 이동합니다. 탭>> 맞춤 열을 선택합니다. .
• 이름:수익 .
• 다음 수식을 삽입하세요.

= [Quantity] * [UnitPrice] * (1-[DiscountRate])

• 확인을 클릭하세요. .

• 다른 맞춤 열을 추가하세요.
• 이름:이익 .
• 다음 수식을 삽입하세요.

Profit = [Revenue] - ([Quantity] * [UnitCost])

• 확인을 클릭하세요. .
• 비용을 얻으려면 Products 테이블과 병합해야 합니다.

추가 통계를 위한 표 병합:

• 판매 데이터가 열려 있는 파워 쿼리 편집기에서.
• 홈으로 이동 탭>> 쿼리 병합을 클릭합니다. 리본에서.
  • 제품 ID 선택 Sales 테이블에서.
  • 제품 선택 ProductID에 대한 테이블 및 조인 .
  • 확인을 클릭하세요. .

• 표 확장을 클릭합니다. 옵션>> UnitCost 만 선택 가져올 열입니다.
• 확인을 클릭하세요. .

• 이제 이익 을 드래그하세요. 확장 제품 아래 열 단계.
• 이익 금액을 받게 됩니다.

• 데이터 변환이 완료되면
• 클릭 닫기 및 로드...

• 데이터 가져오기에서 상자;
  • 연결 만들기만 선택 .
  • 이 데이터를 데이터 모델에 추가를 선택합니다. 4개의 테이블 모두에 대해.
  • 확인을 클릭하세요. .

2단계:Power Pivot을 사용하여 데이터 모델 구축

Power Pivot 열기:

리본에서 Power Pivot을 사용할 수 없는 경우 활성화하세요.

• 파일로 이동 탭>> 옵션 선택>> 추가 기능을 선택합니다. .
• 관리에서 상자>> COM 추가 기능을 선택합니다.>> 이동을 선택하세요. .

• Microsoft Power Pivot for Excel을 선택합니다. .
• 확인을 클릭하세요. .

• 파워 피벗 으로 이동 탭>> 관리 선택 .
• 파워 쿼리에서 가져온 데이터가 열립니다.

관계 만들기 :

• 홈으로 이동 탭>> 다이어그램 보기 선택 .
  • 판매[제품 ID] 를 드래그하세요. 제품[제품 ID]로 .
  • 판매[고객 ID]를 드래그하세요. 고객[CustomerID]에게 .
  • 고객[지역 ID] 드래그 지역[RegionID]로 .

• 또는 디자인 으로 이동하세요. 탭>> 관계 만들기를 선택합니다. .
• 그런 다음 일치하는 열을 선택하세요.

계산된 측정값 만들기:

• Power Pivot에서 Sales 테이블을 클릭합니다.
• 홈으로 이동 탭>> 측정값 을 선택합니다.>> 새 측정값을 클릭하세요. .
• 또는 홈으로 이동합니다. 탭>> 계산 영역 선택 .
• 여기에 계산된 측정값을 삽입하세요:

• 총 수익:

Total Revenue := SUM(Sales[Revenue])

• 총 이익:

Total Profit := SUM(Sales[Profit])

• 이익률:

Profit Margin := DIVIDE([Total Profit], [Total Revenue], 0)

• 총 주문 수:

Total Orders:=COUNTA(Sales[OrderID])

• 평균 주문 금액:

Average Order Value:=DIVIDE([Total Revenue], DISTINCTCOUNT(Sales[OrderID]), 0)

• YTD 수익:

YTD Revenue:=CALCULATE([Total Revenue], DATESYTD(Sales[OrderDate]))

• 전년도 수익:

Previous Year Revenue:=CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Sales[OrderDate]))

• YOY 성장:

YOY Growth := DIVIDE([Total Revenue] - [Previous Year Revenue], [Previous Year Revenue], 0)

3단계:피벗 테이블을 사용하여 대시보드 구성 요소 만들기

피벗 테이블 만들기

• 삽입으로 이동 탭>> 피벗 테이블 선택>> 데이터 모델에서를 선택합니다. .
• Power Pivot에서 홈으로 이동합니다. 탭>> 피벗 테이블을 선택합니다. .

• 피벗 테이블 만들기에서 상자;
  • 새 워크시트를 선택하세요.
  • 확인을 클릭하세요. .

• 총 수익, 총 이익, 이익 마진 및 성장에 대해 별도의 피벗 테이블을 만듭니다.
• 대시보드 구조에 맞춰 셀에 배치하세요.
• 적절하게 통화 또는 백분율로 형식을 지정하세요.

차트 및 시각화

수익 추세 차트:

• 데이터 모델에서 피벗 테이블을 만듭니다.
  • 행:판매액[주문 날짜[월]]
  • 값: 총 수익
• 피벗 테이블 분석으로 이동 탭>> 군집형 기둥형 차트 선택 .

상위 제품 차트:

• 데이터 모델에서 피벗 테이블을 만듭니다.
  • 행:제품[제품 이름]
  • 값:총 수익
• 총 수익을 기준으로 정렬 내림차순, 상위 10으로 필터링합니다.

• 피벗 테이블 분석으로 이동 탭>> 클러스터형 막대 차트 선택 .

카테고리 실적 :

• 데이터 모델에서 피벗 테이블을 만듭니다.
  • 행:제품[카테고리]
  • 값:총 수익, 총 이익, 이익 마진 .
• 피벗 테이블 분석으로 이동 탭>> 콤보 선택>> 클러스터형 열 - 보조 축의 선을 선택합니다. .

4단계:대화형 요소 삽입

슬라이서 및 타임라인 삽입:

제품 및 지역 슬라이서 만들기:

• 피벗 분석으로 이동 탭>> 슬라이서 삽입을 선택합니다. .
• 제품[카테고리] 선택 및 고객[지역] .
• 확인을 클릭하세요. .

• 대시보드 디자인에 맞게 형식을 지정하세요.

날짜 필터링을 위한 타임라인 추가:

• 피벗 분석으로 이동 탭>> 슬라이서 삽입을 선택합니다. .
• 판매[주문 날짜]를 선택합니다. .
• 확인을 클릭하세요. .

• 차트 위에 위치하세요.
• 대시보드 스타일에 맞게 형식을 지정하세요.

모든 슬라이서를 피벗 테이블에 연결 :

• 각 슬라이서를 마우스 오른쪽 버튼으로 클릭하고>> 연결 보고를 선택합니다. .

• 모든 피벗 테이블 확인 필터가 전역적으로 적용되도록 합니다.
• 확인을 클릭하세요. .

5단계:VBA를 통한 상호작용 자동화 및 향상

VBA를 사용하여 대시보드를 동적으로 만들고 탐색하기 쉽게 만들겠습니다.

예 1:데이터 새로 고침 버튼

• 개발자로 이동 탭>> 삽입 선택>> 버튼을 선택하세요. .
• 버튼 이름을 데이터 새로고침으로 바꿉니다. .
• 버튼을 마우스 오른쪽 버튼으로 클릭>> 매크로 할당>> 선택 새로 만들기를 선택하세요. .

• 다음 코드를 복사하여 붙여넣으세요.

VBA 코드:

Sub RefreshDashboard()
 ThisWorkbook.RefreshAll
 MsgBox "Dashboard data refreshed!", vbInformation
End Sub

예 2:대시보드 재설정 버튼

• 개발자로 이동 탭>> 삽입 선택>> 버튼을 선택하세요. .
• 버튼 이름을 대시보드 재설정으로 변경합니다. .
• 버튼을 마우스 오른쪽 버튼으로 클릭>> 매크로 할당>> 선택 새로 만들기를 선택하세요. .
• 다음 코드를 복사하여 붙여넣으세요.

VBA 코드:

Sub ResetDashboardFilter()
 Dim ws As Worksheet
 Dim slicer As slicerCache
 Dim pivotTable As pivotTable
 
 ' Clear all slicer caches
 For Each slicer In ActiveWorkbook.SlicerCaches
 slicer.ClearAllFilters
 Next slicer
 
 ' Reset any timelines (already using SlicerCaches)
 For Each slicer In ActiveWorkbook.SlicerCaches
 If slicer.SourceType = xlTimeline Then
 slicer.ClearAllFilters
 End If
 Next slicer
 
 ' Refresh pivot tables
 For Each ws In ActiveWorkbook.Worksheets
 For Each pivotTable In ws.PivotTables
 pivotTable.RefreshTable
 Next pivotTable
 Next ws
 
 MsgBox "Dashboard filters have been reset!", vbInformation, "Reset Filters"
End Sub

6단계:대시보드 레이아웃 생성

Dashboard라는 이름의 새 워크시트를 만듭니다.

• 대시보드 구조 설정:
  • 대시보드 제목 및 날짜 필터
  • KPI 섹션(수익, 이익, 마진, 성장)
  • 차트(판매 추세, 상위 제품, 지역 성과)

• 데이터 표와 세부 분석에 대한 요약 표를 만들 수 있습니다.

• 일관된 형식 적용 :
  • 전체적으로 일관된 색상 구성을 사용하세요.
  • 모든 요소를 올바르게 정렬하세요.
  • 별도의 대시보드 섹션에 테두리를 추가합니다.

• 데이터 표에 조건부 서식 추가:
  • 데이터 막대와 색상 눈금을 사용하여 중요한 값을 강조하세요.
  • 목표 대비 성과를 표시하려면 KPI 아이콘을 추가하세요.
• 안내 시트 만들기:
  • 지침이라는 이름의 새 워크시트를 만듭니다.
  • 대시보드 사용 방법을 설명하는 텍스트를 추가하세요.
  • 데이터 새로고침, 상호작용 및 사용 가능한 기능에 대한 정보를 포함합니다.

7단계:테스트 및 문제해결

모든 상호작용 요소 테스트:

• 슬라이서가 모든 관련 시각화를 필터링하는지 확인하세요.
• 버튼이 매크로를 올바르게 실행하는지 확인하세요.
• 타임라인 컨트롤을 확인하여 데이터 범위가 올바른지 확인하세요.
  • 뷰티를 선택합니다. 카테고리에서 슬라이서.
  • 2024년 1월~4월을 선택합니다. 타임라인에서.
• 이렇게 하면 필터 선택에 따라 전체 대시보드가 업데이트됩니다.

데이터 필터 재설정 테스트:

• 대시보드 재설정을 클릭합니다. .
• '대시보드 필터가 재설정되었습니다.'라는 메시지가 나타납니다. .
• 확인을 클릭하세요. .
• 모든 필터가 제거되고 새로운 대시보드가 제공됩니다.

테스트 데이터 새로고침 :

• 소스 데이터를 수정합니다.
• 데이터 새로고침을 클릭합니다. .
• 모든 계산이 올바르게 업데이트되었는지 확인하세요.
• 깨진 연결이나 수식이 있는지 확인하세요.

연습 문제집 다운로드

결론

모든 단계를 따르면 강력한 비즈니스 인텔리전스 도구로 작동하는 고급 Excel 대시보드를 구축할 수 있습니다. 이 대시보드는 데이터 준비를 위한 파워 쿼리, 모델링 및 분석을 위한 파워 피벗, 향상된 대화형 작업을 위한 VBA를 활용합니다. 이 대시보드는 제품, 지역 및 기간에 따른 판매 성과를 분석하기 위한 사용자 친화적인 인터페이스를 제공합니다. 대시보드를 시험해보고 더 많은 고급 기능을 추가하세요.