엑셀의 파워 쿼리(Power Query)는 데이터 연결, 변환, 실시간 업데이트를 처리할 수 있는 가장 강력한 기능 중 하나입니다. 원본 데이터에 변경 사항이 생길 때마다 파워 쿼리는 다양한 데이터 소스에서 최신 데이터를 자동으로 불러와 갱신할 수 있습니다. 이번 글에서는 파워 쿼리를 활용해 실시간 데이터 대시보드를 구축하는 전 과정을 단계별로 살펴보겠습니다.
판매 데이터 세트를 확장한 뒤 파워 쿼리에서 추가 작업을 수행하여 실시간 판매 대시보드를 만들어 보겠습니다. 사용할 판매 데이터 세트의 열 구성은 다음과 같습니다.
- 주문 날짜(Order Date)
- 지역(Region)
- 제품(Product)
- 영업사원(Salesperson)
- 판매 수량(Units Sold)
- 매출(Revenue ($))
- 이익(Profit ($))
1단계: 파워 쿼리로 데이터 연결하기
외부 파일에서 데이터를 가져오려면 아래 순서대로 진행하세요.
- 데이터 탭 >> 데이터 가져오기(Get Data) >> 파일에서(From File) >> 통합 문서에서(From Workbook) 선택
- 데이터 세트 파일(예: SalesData.xlsx)을 선택한 후 가져오기 클릭
- 탐색기(Navigator) 창에서 판매 데이터가 있는 시트를 선택하고 로드(Load) 클릭
현재 열려 있는 통합 문서 내의 데이터를 연결하려면 다음과 같이 하세요.
- 데이터 탭 >> 테이블/범위에서(From Table/Range) 선택
이렇게 하면 선택한 데이터가 파워 쿼리 편집기(Power Query Editor)로 불러와집니다.
2단계: 고급 작업으로 데이터 변환하기
데이터가 파워 쿼리 편집기에 로드되었으니, 이제 본격적으로 데이터 변환과 계산 작업을 진행하겠습니다.
① 열 데이터 형식 변경하기
파워 쿼리는 경우에 따라 데이터를 기본 형식으로 자동 인식하기 때문에, 열마다 올바른 데이터 형식을 지정해 주어야 합니다.
- 주문 날짜(Order Date): 날짜(Date) 형식인지 확인하세요.
- 형식이 맞지 않다면 해당 열을 선택한 뒤
- 변환(Transform) 탭 >> 데이터 형식(Data Type)에서 날짜만 선택합니다.
- 판매 수량(Units Sold): 정수(Whole Number) 형식인지 확인하세요.
- 형식이 맞지 않다면 해당 열을 선택한 뒤
- 변환 탭 >> 데이터 형식에서 정수만 선택합니다.
- 매출(Revenue ($))과 이익(Profit ($)): 통화(Currency) 형식인지 확인하세요.
- 두 열을 모두 선택한 뒤
- 변환 탭 >> 데이터 형식에서 통화만 선택합니다.
② '월(Month)' 열 추가하기
- 주문 날짜 열을 선택합니다.
- 열 추가(Add Column) 탭 >> 날짜(Date) >> 월(Month) >> 월 이름(Name of Month)을 차례로 선택합니다.
- 그러면 주문 날짜에서 월 이름만 추출하는 새 열이 생성됩니다(예: 1월, 2월).
③ 판매 단위당 매출 계산하기
이번에는 판매된 제품 한 개당 얼마의 매출이 발생했는지 계산해 보겠습니다.
- 열 추가 탭 >> 사용자 지정 열(Custom Column)을 선택합니다.
- 사용자 지정 열 대화 상자에서:
- 열 이름을 Revenue per Unit(판매 단위당 매출)으로 입력하고, 수식 입력란에 아래 수식을 작성합니다.
[#"Revenue ($)"]/[Units Sold]
- 확인을 클릭합니다.
새로 생성된 사용자 지정 열에는 판매 건수당 발생한 매출액이 표시됩니다.
④ 이익률 계산하기
다음은 이익률(Profit Margin)을 계산해 보겠습니다. 이익률은 다음 수식으로 구할 수 있습니다.
- 열 추가 탭 >> 사용자 지정 열을 선택합니다.
- 사용자 지정 열 대화 상자에서:
- 열 이름을 Profit Margin (%)(이익률)으로 입력하고, 수식 입력란에 아래 수식을 작성합니다.
([#"Profit ($)"]/[#"Revenue ($)"])*100)
- 확인을 클릭합니다.
이 사용자 지정 열에는 각 판매 건의 이익률이 백분율로 표시됩니다.
⑤ 지역·제품별 그룹화로 집계 인사이트 얻기
지역(Region)과 제품(Product)별로 총 매출, 판매 수량, 이익을 한눈에 보려면 데이터를 그룹화해야 합니다.
- 홈 탭 >> 그룹화(Group By)를 선택합니다.
- 그룹화 대화 상자에서 다음과 같이 설정합니다.
- 여러 집계 항목을 추가할 수 있도록 고급(Advanced)을 선택합니다.
- 그룹화 기준: Region, Product
- 새 열 이름(New column name)에 유효한 이름을 입력합니다.
- 연산(Operation): 합계(Sum)
- 열(Column):
- Units Sold
- Revenue ($)
- Profit ($)
- 확인을 클릭합니다.
그룹화 기능이 지역과 제품별로 데이터를 요약하여 전체 판매 실적을 한눈에 파악할 수 있게 해 줍니다.
⑥ 데이터 정렬하기
실적이 좋은 제품과 지역부터 확인하려면 매출 기준 내림차순으로 데이터를 정렬합니다.
- Revenue ($) 열 머리글의 드롭다운 화살표를 클릭합니다.
- 내림차순 정렬(Sort Descending)을 선택한 후 확인을 클릭합니다.
이제 데이터가 매출이 가장 높은 항목부터 가장 낮은 항목 순으로 정렬됩니다.
3단계: 변환된 데이터를 엑셀로 불러오기
정리하고 변환한 데이터를 엑셀 워크시트로 불러오겠습니다.
- 홈 탭 >> 닫기 및 로드(Close & Load) >> 닫기 및 로드를 선택합니다.
4단계: 대시보드 만들기
변환된 데이터가 엑셀에 로드되면 시각화 요소를 활용해 대시보드를 구성할 수 있습니다.
① 피벗 테이블 만들기
- 변환된 데이터 테이블을 선택합니다.
- 삽입 탭 >> 피벗 테이블(PivotTable)을 선택합니다.
- 피벗 테이블 필드 목록에서 다음과 같이 배치합니다.
- 행(Rows): Region, Product
- 값(Values): Units Sold 합계, Revenue 합계, Profit 합계
② 피벗 차트 만들기
지역별 매출 — 막대형 차트
- Region과 Revenue가 포함된 피벗 테이블을 선택합니다.
- 피벗 테이블 분석(PivotTable Analyze) 탭 >> 막대형 차트(Bar Chart) >> 묶은 세로/가로 막대형(Clustered Bar)을 선택합니다.
제품별 판매 비중 — 원형 차트
- Product와 Revenue가 포함된 피벗 테이블을 선택합니다.
- 피벗 테이블 분석 탭 >> 원형 차트(Pie Chart)를 선택합니다.
이제 대시보드의 상호작용 기능을 확인해 보겠습니다. 원형 차트에서 'East' 지역을 선택해 보세요.
여기에 슬라이서(Slicer)를 추가해 영업사원별 필터를 만들고, 타임라인(Timeline)을 추가해 주문 날짜별로 조회할 수도 있습니다. 이렇게 하면 대시보드의 인터랙티브성이 한층 높아집니다.
5단계: 실시간 새로고침 설정하기
대시보드의 데이터를 실시간으로 최신 상태로 유지하려면 다음 방법을 활용하세요.
- 원본 데이터에 새로운 데이터를 추가할 때마다 데이터 탭 >> 모두 새로 고침(Refresh All)을 클릭합니다.
또는 통합 문서가 일정 간격으로 자동 새로고침되도록 설정할 수도 있습니다.
- 데이터 탭 >> 쿼리 및 연결(Queries and Connections)을 선택한 후, 목록에서 마우스 오른쪽 버튼을 클릭하고 속성(Properties)을 선택합니다.
- 쿼리 속성(Query Properties) 대화 상자에서:
- 5분마다 새로 고침(Refresh every 5 minutes) 옵션을 활성화합니다.
- 확인을 클릭합니다.
마무리
위 단계를 따라 하면 파워 쿼리만으로 동적인 실시간 판매 데이터 대시보드를 완성할 수 있습니다. 파워 쿼리 편집기에서 그룹화, 사용자 지정 열 계산, 정렬, 필터링 등 고급 작업을 수행한 뒤, 변환된 데이터를 엑셀로 불러와 대시보드를 구성하면 됩니다. 파워 쿼리의 데이터 연결과 변환 기능 덕분에 대시보드는 원본 데이터가 바뀔 때마다 최신 데이터로 빠르게 갱신됩니다. 반복적인 수작업 없이 항상 신선한 데이터를 확인할 수 있는 것이 파워 쿼리 기반 대시보드의 가장 큰 장점입니다.