피벗 테이블 및 슬라이서를 사용하여 Excel에서 실시간 대화형 대시보드 만들기

이 기사에서는 Excel에서 피벗 테이블 및 슬라이서를 사용하여 동적 대시보드를 구축하는 방법을 보여 드리겠습니다.

1단계:데이터 준비

대시보드를 만들기 전에 데이터를 올바르게 구성하는 것이 중요합니다.

데이터세트 요구사항:

• 표 형식이어야 합니다(병합된 셀 없음).
• 각 열에는 명확하고 고유한 헤더가 있어야 합니다.
• 빈 행이나 열이 없습니다.
• 모든 열의 형식이 적절한지 확인하세요.
  • 날짜 간단한 날짜로 열 .
  • 단가 및 총 매출 통화로 .
  • 판매 수량 숫자로 소수점 없음 .
• Excel 표로 변환:
  • 모든 데이터를 선택하거나 Ctrl+A를 누르세요. .
  • Ctrl+T 누르기 또는 삽입 탭>> 표 선택 .
  • 내 테이블에 헤더가 있습니다를 확인하세요. .
  • 확인을 클릭하세요. .

• 테이블 이름 지정:
  • 표의 셀을 선택한 경우
  • 테이블 디자인으로 이동 탭>> 테이블 이름 변경 판매 데이터로 .

2단계:첫 번째 피벗 테이블 만들기

지역 및 제품 범주별 매출을 보여주는 피벗 테이블을 만들어 보겠습니다.

• SalesData의 아무 곳이나 클릭하세요. 테이블.
• 삽입으로 이동 탭>> 피벗 테이블 선택 .
• 표 범위나 이름이 올바른지 확인하고 새 워크시트를 선택하세요. .
• 확인을 클릭하세요. .

• 피벗 테이블 필드 창에서:
  • 지역을 드래그하세요. 필드를 행 으로 지역.
  • 제품 카테고리를 드래그하세요. 필드를 열로 지역.
  • 총 매출을 드래그하세요. 필드를 값으로 지역.
• 이제 첫 번째 피벗 테이블에는 각 제품 카테고리가 각 지역에서 창출하는 수익이 표시됩니다.

3단계:슬라이서 및 타임라인 추가

이제 슬라이서와 타임라이너를 추가하여 이 데이터를 대화형으로 필터링하세요.

슬라이서 삽입:

• 피벗 테이블의 아무 셀이나 선택하세요.
• 피벗 테이블 분석으로 이동 탭>> 슬라이서 삽입을 선택합니다. .
• 영업 담당자 선택 및 날짜 .
• 확인을 클릭하세요. .

타임라인 삽입:

• 피벗 테이블의 아무 셀이나 선택하세요.
• 피벗 테이블 분석으로 이동 탭>> 타임라인 삽입을 선택합니다. .
• 날짜 선택 .
• 확인을 클릭하세요. .

• 피벗 테이블 옆에 슬라이서와 타임라인을 배치합니다.

다양한 영업 담당자를 클릭하고 피벗 테이블이 어떻게 자동으로 업데이트되는지 확인하여 슬라이서와 타임라이너를 테스트하세요!

4단계:추가 피벗 테이블 만들기

대시보드에 피벗 테이블을 두 개 더 추가해 보겠습니다.

피벗 테이블 2:월별 매출 추세

• SalesData의 아무 곳이나 클릭하세요. 테이블.
• 삽입으로 이동 탭>> 피벗 테이블 선택 .
• 표 범위가 올바른지 확인하고 새 워크시트를 선택하세요. .
• 확인을 클릭하세요. .

• 피벗 테이블 필드 창에서:
  • 날짜 를 드래그하세요. 행 지역. 표시됩니다.
    • 월(날짜)
    • 일(날짜)
    • 날짜
  • 총 매출을 드래그하세요. 값 지역.

피벗 테이블 3:판매량 기준 상위 제품

• 피벗 테이블 3과 동일한 워크시트에 다른 피벗 테이블을 만듭니다.
• 피벗 테이블 필드 창에서:
  • 제품 이름을 드래그하세요. 행 지역.
  • 판매 수량을 드래그하세요. 값 지역.

5단계:여러 피벗 테이블을 동일한 슬라이서에 연결

기존 슬라이서를 모든 피벗 테이블에 연결해 보겠습니다.

• 영업 담당자를 마우스 오른쪽 버튼으로 클릭하세요. 슬라이서.
• 연결 보고 선택 .

• 목록의 모든 피벗 테이블을 확인하세요.
• 확인을 클릭하세요. .

• 날짜 에 대해 반복 슬라이서.

이제 특정 영업 담당자나 날짜 범위를 클릭하면 세 개의 피벗 테이블이 모두 동시에 업데이트됩니다!

6단계:피벗 차트 만들기

피벗 테이블을 시각적 차트로 변환하세요.

지역/범주 피벗 테이블의 경우:

• 피벗 테이블의 아무 곳이나 클릭하세요.
• 피벗 테이블 분석으로 이동 탭>> 피벗 차트 선택 .
• 클러스터형 열 선택 차트.
• 확인을 클릭하세요. .
• 차트 형식 지정:
  • 차트 제목을 추가합니다.
  • 축을 맞춤설정하세요.
  • 불필요한 요소를 제거하세요(예:격자선, 필요하지 않은 경우 범례).

월간 판매 동향:

• 피벗 테이블의 아무 곳이나 클릭하세요.
• 피벗 테이블 분석으로 이동 탭>> 피벗 차트 선택 .
• 마커가 있는 선을 선택합니다. 차트.
• 확인을 클릭하세요. .

최고 제품의 경우:

• 피벗 테이블의 아무 곳이나 클릭하세요.
• 피벗 테이블 분석으로 이동 탭>> 피벗 차트 선택 .
• 바 선택 차트.
• 확인을 클릭하세요. .

피벗 테이블의 모든 변경 사항은 피벗 차트에 자동으로 반영됩니다.

7단계:대시보드 레이아웃 디자인

이제 모든 것을 응집력 있는 대시보드로 정리해 보겠습니다.

• 대시보드 워크시트의 이름을 판매 대시보드로 변경하세요. .
• 차트 위치 지정:
  • 지역/카테고리 왼쪽에 차트가 있습니다.
  • 월별 추세 중앙에 차트가 있습니다.
  • 인기 제품 오른쪽에 차트가 있습니다.
• 쉽게 접근할 수 있도록 상단에 슬라이서를 배치하세요.
• 텍스트 상자를 사용하여 제목 추가:판매 실적 대시보드 .

8단계:서식 개선

대시보드를 시각적으로 더욱 매력적으로 만들어 보겠습니다.

• 지역/범주 피벗 테이블에 조건부 서식 적용 :
  • 데이터 셀을 선택합니다.
  • 홈으로 이동 탭>> 조건부 서식에서>> 색상 스케일에서>> 녹색-노란색-빨간색을 선택합니다. .
• 월간 추세 형식 지정 차트:
  • 차트를 클릭하세요.
  • 차트 디자인으로 이동>> 스타일 5 선택 (또는 선호하는 스타일).
  • 차트 제목 추가:월간 판매 추세 .
• 인기 제품 형식 지정 차트:
  • 데이터 라벨을 추가합니다.
  • 차트 디자인으로 이동>> 차트 요소 추가를 선택하세요.>> 데이터 라벨을 선택하세요. .
  • 내림차순으로 정렬
  • 차트 제목 추가:인기 제품 .

상호작용 확인:

문제해결 팁

• 날짜가 제대로 그룹화되지 않은 경우 소스 데이터의 날짜 형식으로 지정되었는지 확인하세요.
• 슬라이서가 모든 피벗 테이블을 업데이트하지 않는 경우 보고서 연결을 다시 확인하세요.
• 더 큰 데이터 세트의 성능 문제에 대해서는 피벗 테이블 옵션에서 '레이아웃 업데이트 연기'를 사용하는 것이 좋습니다.

고급 기술

이익 마진을 표시하는 계산된 필드를 만듭니다.

• 피벗 테이블을 클릭하세요.
• 피벗 테이블 분석으로 이동 탭>> 필드, 항목 및 세트에서>> 계산된 필드를 선택합니다. .
• 이름을 이익 으로 지정하세요. 그리고 다음 수식을 입력하세요.

• 추가 를 클릭하세요. 그리고 확인 .

선택에 따라 변경되는 동적 제목 추가:

• 현재 필터링된 합계를 가져오려면 GETPIVOTDATA()를 사용하세요.
• 다음 수식을 삽입하세요.

="Sales Dashboard: "&TEXT(GETPIVOTDATA("Total Sales",$A$3),"$#,##0")

통합문서 다운로드

결론

위의 단계를 따르면 사용자가 판매 데이터를 쉽게 분석할 수 있는 전문적이고 대화형 대시보드를 만들 수 있습니다. 피벗 테이블을 사용하여 데이터를 요약하고 분석하고, 피벗 차트를 사용하여 데이터를 시각화할 수 있습니다. 대화형 슬라이서 및 타임라인은 데이터를 자동으로 필터링하는 데 도움이 됩니다. 데이터가 추가되면 동적 Excel 대시보드가 ​​쉽게 새로 고쳐집니다. 이러한 종류의 대시보드는 판매 보고서, KPI 추적, 재고 분석 등에 유용합니다.