Excel 파워 쿼리를 사용하여 동적 실시간 대시보드 만들기

Excel의 파워 쿼리는 데이터 연결, 변환 및 실시간 업데이트를 위한 가장 강력한 도구 중 하나입니다. 파워 쿼리는 다양한 원본에서 데이터를 가져와서 원본 데이터가 변경될 때마다 실시간으로 새로 고칠 수 있습니다. 이 문서에서는 파워 쿼리를 사용하여 실시간 데이터 대시보드를 구축하는 방법을 보여드리겠습니다.

확장된 데이터 세트에 대해 파워 쿼리에서 추가 작업을 수행하여 실시간 판매 대시보드를 만들어 보겠습니다. 판매 데이터 세트의 헤더에는 주문 날짜, 지역, 제품, 판매원, 판매 수량, 수익($) 및 이익($)이 포함됩니다.

1단계:파워 쿼리로 데이터 연결

외부 소스에서 데이터를 가져오려면:

• 데이터로 이동 탭>> 데이터 가져오기 선택>> 파일에서>> 통합문서에서 .
• 데이터세트 파일(예:SalesData.xlsx)을 선택하고 가져오기를 클릭합니다. .
• 탐색기에서 판매 데이터가 있는 시트를 선택하고 로드를 클릭합니다. .

기존 통합 문서의 데이터를 연결하려면:

• 데이터로 이동 탭>> 표/범위에서 선택 .

파워 쿼리는 파워 쿼리 편집기에 데이터를 로드합니다. .

2단계:고급 작업을 통한 데이터 변환

이제 데이터가 파워 쿼리 편집기에 로드되었으므로 데이터 변환 및 계산을 수행해 보겠습니다.

1. 열 데이터 유형 변경

파워 쿼리는 때때로 데이터 형식을 기본 형식으로 변경합니다. 데이터 유형에 따라 전체 열을 변경하세요.

• 주문 날짜 :날짜인지 확인하세요. 형식입니다.
  • 그렇지 않은 경우 날짜 열을 선택하세요.
  • 변환으로 이동 탭>> DataType에서>> 날짜만 선택 .
• 판매 수량: 정수인지 확인하세요. 형식입니다.
  • 그렇지 않은 경우 판매 수량 열을 선택하세요.
  • 변환으로 이동 탭>> DataType에서>> 정수만 선택 .
• 수익($) 및 이익($) :이 열이 통화로 되어 있는지 확인하세요. 형식입니다.
  • 그렇지 않은 경우 수익($) 및 이익($) 열을 선택하세요.
  • 변환으로 이동 탭>> DataType에서>> 통화만 선택 .

2. '월' 열 추가

• 주문 날짜 열을 선택하세요.
• 열 추가로 이동합니다. 탭>> 날짜를 클릭하세요.>> 월 선택>> 월 이름을 선택하세요. .
• 이렇게 하면 Order Date에서 월 이름을 추출하는 열이 생성됩니다. (예:1월, 2월)

3. 단위당 수익 계산

판매된 각 제품에 대해 발생한 수익을 계산해 보겠습니다.

• 열 추가로 이동합니다. 탭>> 맞춤 열을 선택합니다. .
• 맞춤 열 대화 상자;
  • 열 이름을 단위당 수익으로 지정합니다. 수식 상자에 다음 수식을 사용하세요:

[#"Revenue ($)"]/[Units Sold]

• • 확인을 클릭하세요. .

이 새로운 맞춤 열에는 판매된 각 제품에 대해 창출된 수익이 표시됩니다.

4. 이익률 계산

다음으로 이익률을 계산해 보겠습니다. 이익 마진은 다음과 같이 계산됩니다:

• 열 추가로 이동합니다. 탭>> 맞춤 열을 선택합니다. .
• 맞춤 열 대화 상자;
  • 열 이름을 이익률(%)로 지정합니다. 수식 상자에 다음 수식을 사용하세요:

([#"Profit ($)"]/[#"Revenue ($)"])*100)

• • 확인을 클릭하세요. .

이 맞춤 열에는 각 판매에 대한 이익 마진이 표시됩니다.

5. 집계된 통찰력을 위해 지역 및 제품별로 데이터를 그룹화합니다

총 수익을 보려면 , 판매 수량 및 이익 지역별 및 제품 , 데이터를 그룹화하겠습니다.

• 홈으로 이동 탭>> 그룹화 기준 선택 .
• 그룹화 기준 대화 상자에서 다음을 설정합니다:
  • 고급을 선택합니다. 여러 집계를 추가합니다.
  • 그룹화 기준 :지역, 제품.
  • 유효한 새 열 이름 삽입 .
  • 작업 :합계 
  • 열:
    • 판매 수량
    • 수익($)
    • 이익($)
  • 확인을 클릭하세요. .

그룹화 기준 기능은 지역 및 제품별로 데이터를 요약하여 판매 실적에 대한 개요를 제공합니다.

6. 데이터 정렬

매출($)을 기준으로 데이터를 내림차순으로 정렬하여 실적이 가장 우수한 제품과 지역을 확인하세요.

• 수익($) 드롭다운을 클릭합니다. 열 헤더.
• 내림차순 정렬을 선택합니다.>> 확인을 클릭하세요. .

이렇게 하면 수익을 기준으로 데이터가 가장 높은 것부터 낮은 것 순으로 표시되도록 데이터가 정렬됩니다.

3단계:변환된 데이터를 Excel로 로드

정리되고 변환된 데이터를 Excel 워크시트로 로드합니다.

• 홈으로 이동 탭>> 닫기 및 로드>> 닫기 및 로드를 선택합니다. .

4단계:대시보드 만들기

데이터가 변환되어 Excel에 로드되면 시각화가 포함된 대시보드를 만들 수 있습니다.

1. 피벗 테이블 만들기

• 변환된 데이터 표를 선택하세요.
• 삽입으로 이동 탭>> 피벗 테이블 선택 .

• 피벗 테이블 필드 목록:
  • 행 :지역, 제품.
  • 값 :판매량 합계, 수익 합계, 이익 합계.

2. 피벗 차트 만들기

지역별 수익 막대 차트 :

• 지역 및 수익으로 피벗 테이블을 강조표시합니다.
• 피벗 테이블 분석 으로 이동 탭>> 막대 차트를 선택합니다.>> 클러스터형 막대를 선택합니다. .

제품별 매출 분포 원형 차트 :

• 제품과 수익이 포함된 피벗 테이블을 선택하세요.
• 피벗 테이블 분석 으로 이동 탭>> 원형 차트를 선택합니다. .

대시보드의 상호 작용을 살펴보겠습니다. 원형 차트에서 동부 지역을 선택하세요.

영업사원용 슬라이서와 상호작용을 위한 주문 날짜 타임라인을 추가할 수 있습니다.

5단계:실시간 새로고침 설정

대시보드에서 실시간 데이터를 업데이트하려면:

• 데이터로 이동 탭>> 모두 새로고침을 클릭하세요. 소스 데이터에 새 데이터를 추가할 때마다

또는 통합 문서가 몇 분마다 자동으로 새로 고쳐지도록 설정할 수도 있습니다:

• 데이터로 이동 탭>> 쿼리 및 연결을 선택합니다.>> 쿼리 및 연결을 마우스 오른쪽 버튼으로 클릭하세요.>> 속성을 선택합니다. .

• 쿼리 속성에서 대화 상자;
  • 5분마다 새로고침을 활성화합니다. .
  • 확인을 클릭하세요. .

결론

다음 단계를 수행하면 파워 쿼리를 사용하여 동적 실시간 판매 데이터 대시보드를 구축할 수 있습니다. 파워 쿼리 편집기에서 고급 작업을 수행하여 사용자 지정 열을 그룹화, 계산, 정렬, 필터링한 다음 변환된 데이터를 Excel에 로드하여 실시간 대시보드를 구축할 수 있습니다. 파워 쿼리의 데이터 연결 및 변환 기능은 최신 데이터로 대시보드를 실시간으로 빠르게 업데이트합니다.