Excel 대시 보드 만들기 (예제 및 템플릿으로 설명)

Excel 대시 보드는 KPI를 추적하고 데이터 포인트를 비교할 때 놀라운 도구가 될 수 있습니다. 그리고 경영진이 의사 결정을 내리는 데 도움이되는 데이터 기반보기를 가져옵니다.

이 자습서에서는 Excel 대시 보드를 만드는 방법, 대시 보드를 만드는 동안 따라야 할 모범 사례, Excel에서 사용할 수있는 기능 및 도구에 대해 알아 봅니다. , 피해야 할 사항 및 권장 교육 자료.

Excel 대시 보드 란 무엇이며 보고서와 어떻게 다릅니 까?

우선 Excel 대시 보드가 무엇인지 이해하겠습니다.

Excel 대시 보드는 관리자와 비즈니스 리더가 주요 KPI 또는 메트릭을 추적하는 데 도움이되는 1 페이지 (대부분 항상 필요한 것은 아님)입니다. 그에 따라 결정을 내립니다. 여기에는 데이터로 뒷받침되는 차트 / 표 /보기가 포함되어 있습니다.

대시 보드는 종종 보고서라고 부르지 만 모든 보고서가 대시 보드는 아닙니다.

차이점은 다음과 같습니다.

p>

• 보고서는 한 곳에서만 데이터를 수집하고 표시합니다. 예를 들어, 관리자가 지난 기간 동안 매출이 어떻게 증가했는지, 어떤 지역이 가장 수익성이 높은지 알고 자하는 경우 보고서는 이에 답할 수 없습니다. 단순히 모든 관련 판매 데이터를보고합니다. 그런 다음 이러한 보고서는 의사 결정에 도움이되는 대시 보드 (Excel 또는 PowerPoint)를 만드는 데 사용됩니다.
• 대시 보드는 어떤 지역이 더 나은 성과를 내고 어떤 제품을 사용하는지와 같은 중요한 질문에 즉시 답변합니다. 경영진이 집중해야합니다. 이러한 대시 보드는 정적이거나 대화 형일 수 있습니다 (사용자가 선택하고보기를 변경할 수 있으며 데이터가 동적으로 업데이트 됨).

대시 보드가 무엇인지 이해 했으므로 이제 살펴 보겠습니다. Excel에서 대시 보드를 만드는 방법에 대해 알아 봅니다.

Excel 대시 보드를 만드는 방법은 무엇입니까?

Excel 대시 보드 만들기는 다단계 프로세스이며 만들 때 염두에 두어야 할 몇 가지 주요 사항이 있습니다.

Excel을 시작하기 전에도 대시 보드의 목표를 명확히합니다.

예를 들어 회사의 재무 KPI를 추적하기 위해 KPI 대시 보드를 만드는 경우 목표는 현재 기간과 과거 기간의 비교를 표시하는 것입니다. (s).

마찬가지로 인사부에서 직원 교육을 추적하는 대시 보드를 만드는 경우 목표는 교육을받은 직원 수와 교육이 필요한 직원 수를 표시하는 것입니다. 목표에 도달합니다.

Excel 대시 보드 만들기를 시작하기 전에해야 할 일

많은 사람들이 데이터를 손에 넣는 즉시 대시 보드 작업을 시작합니다.

대부분의 경우 고객 / 이해 관계자의 목표가 충족되지 않아 대시 보드에서 재 작업하는 비참함을 초래합니다.

다음은 몇 가지 문제입니다. Excel 대시 보드 구축을 시작하기 전에 답변해야 할 질문 :

Q : 대시 보드의 목적은 무엇입니까?

데이터를 가져 오자마자 가장 먼저해야 할 일 ( 또는 데이터를 얻기 전에) 이해 관계자가 원하는 것을 명확히하는 것입니다. 대시 보드가 제공해야하는 목적을 명확히하십시오.

KPI를 한 번만 추적하는 것입니까, 아니면 정기적으로 추적하는 것입니까? 회사 전체 또는 부서별 KPI를 추적해야합니까? 올바른 질문을하면 필요한 데이터와 대시 보드를 디자인하는 방법을 이해하는 데 도움이됩니다.

Q : 데이터 소스는 무엇입니까?

데이터의 출처와 출처를 항상 파악하십시오. 어떤 형식. 내 프로젝트 중 하나에서 데이터가 스페인어로 된 PDF 파일로 제공되었습니다. 이로 인해 범위가 완전히 바뀌었고 대부분의 시간은 데이터를 수동으로 컬링하는 데 쏟았습니다. 다음과 같은 질문을해야합니다. 누가 데이터를 소유합니까? 어떤 형식으로 데이터를 얻습니까? 데이터는 얼마나 자주 업데이트됩니까?

Q : 누가이 Excel 대시 보드를 사용합니까?

관리자는 대시 보드가 제공하는 통찰력에만 관심이있을 수 있지만 일부 데이터 분석가는 그의 팀은 더 자세한보기가 필요할 수 있습니다. 대시 보드를 사용하는 사람에 따라 데이터와 최종 출력을 구성해야합니다.

Q : Excel 대시 보드를 얼마나 자주 업데이트해야합니까?

대시 보드를 사용하는 경우 매주 또는 매월 업데이트되는 경우 플러그 앤 플레이 모델을 만드는 것이 좋습니다 (데이터를 복사하여 붙여 넣기 만하면 자동으로 업데이트 됨). 일회성 연습 인 경우 일부 자동화를 생략하고 수동으로 수행 할 수 있습니다.

Q : 클라이언트 / 이해 관계자가 사용하는 Office 버전은 무엇입니까?

클라이언트 / 이해 관계자가 최신 버전의 MS Office를 가지고 있다고 가정하지 마십시오. 이해 관계자가 Excel 2003을 사용하고 있다는 것을 알기 위해 대시 보드를 만든 적이 있습니다.이로 인해 IFERROR 기능이 Excel 2003 버전 (대시 보드를 만들 때 광범위하게 사용)에서 작동하지 않기 때문에 일부 재 작업이 이루어졌습니다.

Excel에서 데이터 가져 오기

무엇을 만들어야하는지 잘 알고 있다면 다음 단계는 데이터를 직접 가져 와서 Excel로 가져 오는 것입니다.

클라이언트가 Excel에서 데이터를 제공하면 삶은 쉽습니다. 그렇지 않은 경우 Excel에서 가져 오는 효율적인 방법을 찾아야합니다.

CSV 파일 또는 텍스트 파일이 제공되는 경우 Excel에서 쉽게 변환 할 수 있습니다. 데이터를 저장하는 데이터베이스에 액세스 할 수있는 경우 연결을 생성하고 간접적으로 업데이트 할 수 있습니다.

데이터가 있으면 데이터를 정리하고 표준화해야합니다.

예를 들어 선행, 후행 또는 이중 공백을 제거하고, 중복을 찾아서 제거하고, 공백과 오류를 제거하는 등의 작업을 수행해야 할 수 있습니다.

경우에 따라 재구성해야 할 수도 있습니다. 데이터 (예 : 피벗 테이블을 만들어야한다고 가정).

이 단계는 프로젝트와 Excel에서 데이터가 어떻게 보이는지에 따라 다릅니다.

대시 보드 구조 개요

Excel에 데이터가 있으면 Excel 대시 보드에서 사용할 수있는 것과 사용할 수없는 것을 정확히 알 수 있습니다.

이 단계에서는 사용자와 다시 동그라미를 치는 것이 좋습니다. 이해 관계자가 Excel 대시 보드의 개요를 확인합니다.

권장 사항으로 PowerPoint에서 추가 메모와 함께 간단한 개요를 만듭니다. 이 단계의 목적은 이해 관계자가 사용 가능한 데이터로 어떤 종류의 대시 보드를 기대할 수 있는지 이해하도록하는 것입니다.

또한 이해 관계자가 자신에게 더 많은 가치를 추가 할 수있는 변경 사항을 제안 할 수 있으므로 도움이됩니다.

다음은 KPI 대시 보드 중 하나에 대해 만든 샘플 개요의 예입니다.

개요를 정했으면 이제 Excel 대시 보드 만들기를 시작할 시간입니다.

우수 사례로서 Excel 통합 문서를 세 부분 (이 같은 이름으로 만든 워크 시트입니다.) :

• 데이터 – 원시 데이터가 포함 된 하나 이상의 워크 시트 일 수 있습니다.
• 계산 – 이것은 모든 계산을하는 곳입니다. 다시 말하지만, 계산을위한 시트가 하나 이상있을 수 있습니다.
• 대시 보드 – 대시 보드가있는 시트입니다. 대부분의 경우 데이터로 뒷받침되는 분석 / 통계를 보여주는 단일 페이지보기입니다.

Excel 표 – 효율적인 Excel 대시 보드의 비밀 소스

원시 데이터로 가장 먼저하는 일은이를 Excel 표로 변환하는 것입니다.

Excel Table은 다음과 같은 많은 이점을 제공합니다. Excel 대시 보드를 만드는 동안 중요합니다.

표 형식 데이터를 Excel 표로 변환하려면 데이터를 선택하고 삽입 탭으로 이동 한 다음 표 아이콘을 클릭합니다.

대시 보드에 Excel 표를 사용하면 다음과 같은 이점이 있습니다.

• 표 형식 데이터 세트를 Excel 표로 변환하는 경우 , 나중에 데이터가 변경되는 것에 대해 걱정할 필요가 없습니다. 추가 데이터를 얻더라도 수식이 망가질 염려없이 간단히 표에 추가 할 수 있습니다. 이것은 플러그 앤 플레이 대시 보드를 만들 때 매우 유용합니다.
• Excel 테이블을 사용하면 참조 대신 열 이름을 사용할 수 있습니다. 예를 들어 C2 : C1000 대신 판매를 사용할 수 있습니다.

대시 보드를위한 중요한 Excel 기능

다음과 같은 방법으로 훌륭한 대화 형 Excel 대시 보드를 많이 만들 수 있습니다. Excel 수식 만 사용합니다.

드롭 다운 목록에서 선택하거나 스크롤 막대를 사용하거나 확인란을 선택하면 결과에 따라 업데이트되고 업데이트 된 데이터를 제공하는 수식이 있습니다. 대시 보드에서보기.

다음은 Excel 대시 보드 용 Excel 기능 5 개입니다.

• SUMPRODUCT 기능 : 대화 형 Excel 대시 보드를 만들 때 가장 좋아하는 기능입니다. 변수가 많을 때 복잡한 계산을 할 수 있습니다. 예를 들어 판매 대시 보드가 있고 동부 지역에서 3 분기에 Bob 담당자가 수행 한 판매가 무엇인지 알고 싶다고 가정합니다. 간단히 SUMPRODUCT 수식을 만들 수 있습니다.
• INDEX / MATCH 함수 : 저는 Excel 대시 보드에서 데이터를 조회하기 위해 INDEX와 MATCH 수식의 조합을 사용하는 것을 큰 지지자입니다. VLOOKUP 함수를 사용할 수도 있지만 INDEX / MATCH가 더 나은 선택이라고 생각합니다.
• IFERROR 함수 : 원시 데이터에 대한 계산을 수행 할 때 종종 오류가 발생합니다. IFERROR를 광범위하게 사용하여 대시 보드의 오류를 숨 깁니다 (그리고 원시 데이터에서도 여러 번).
• TEXT 함수 : 동적 헤드 라인이나 제목을 만들려면 TEXT 함수를 사용해야합니다. 그것.
• ROWS / COLUMNS 함수 : 수식을 복사해야하고 셀의 아래 / 오른쪽으로 이동할 때 인수 중 하나가 증가해야 할 때 자주 사용합니다.

Excel 대시 보드에서 수식으로 작업 할 때 염두에 두어야 할 사항 :

• OFFSET, NOW, TODAY 등 변동적인 수식을 사용하지 마십시오. 의 위에..). 이렇게하면 통합 문서 속도가 느려집니다.
• 불필요한 수식을 제거합니다. 계산 시트에 추가 수식이있는 경우 대시 보드를 마무리하는 동안 제거합니다.
• 긴 배열 수식을 피하는 데 도움이 될 수 있으므로 도우미 열을 사용합니다.

대화 형 Excel 대시 보드를 멋지게 만드는 도구

Excel 대시 보드를 동적이고 사용자 친화적으로 만드는 데 사용할 수있는 대화 형 도구가 많이 있습니다.

다음은 정기적으로 사용하는 몇 가지 도구입니다.

• 스크롤 막대 : 스크롤 막대를 사용하여 통합 문서 공간을 절약하십시오. 예를 들어 100 행의 데이터가있는 경우 대시 보드에서 10 행만있는 스크롤 막대를 사용할 수 있습니다. 사용자는 전체 데이터 세트를보고자하는 경우 스크롤 막대를 사용할 수 있습니다.
• 확인란 : 확인란을 사용하면 선택을하고 대시 보드를 업데이트 할 수 있습니다. 예를 들어, 제가 교육 대시 보드를 가지고 있고 제가 회사의 CEO라고 가정하면 전체 회사 대시 보드를보고 싶습니다. 하지만 제가 영업 책임자라면 부서의 성과 만보고 싶을 것입니다. 이 경우 회사의 여러 부서에 대한 확인란이있는 대시 보드를 만들 수 있습니다.
• 드롭 다운 목록 : 드롭 다운 목록을 사용하면 사용자가 독점적으로 선택할 수 있습니다. 이러한 드롭 다운 선택을 사용하여 대시 보드를 업데이트 할 수 있습니다. 예를 들어 부서별로 데이터를 표시하는 경우 드롭 다운에 부서 이름을 포함 할 수 있습니다.

Excel 차트를 사용하여 Excel 대시 보드에서 데이터 시각화

차트는 Excel 대시 보드를 시각적으로 매력적으로 만들뿐만 아니라 쉽게 사용하고 해석 할 수있게 해줍니다.

다음은 Excel 대시 보드에서 차트를 사용하는 동안 몇 가지 팁입니다.

• 선택 올바른 차트 : Excel은 많은 차트 옵션을 제공하며 올바른 차트를 사용해야합니다. 예를 들어 추세를 표시해야하는 경우 꺾은 선형 차트를 사용해야하지만 실제 값을 강조하려면 막대 / 열 차트가 올바른 선택이 될 수 있습니다. 많은 전문가들이 원형 차트를 사용하지 말라고 조언하지만 귀하의 재량을 사용하는 것이 좋습니다. 청중이 원형 차트를 보는 데 익숙하다면이를 사용하는 것도 좋습니다.
• 조합 차트 사용 : 사용자가 값을 비교하고 의미 통찰력을 도출 할 수 있도록 조합 차트를 사용하는 것이 좋습니다. 예를 들어 매출액을 세로 막 대형 차트로, 성장률을 선 차트로 표시 할 수 있습니다.

• 동적 차트 사용 : 사용자가 선택하고 차트를 업데이트 할 수 있도록하려면 동적 차트를 사용합니다. 이제 동적 차트는 선택시 백엔드에서 데이터가 업데이트되는 일반 차트에 불과합니다.

• 스파크 라인을 사용하여 데이터를보다 의미있게 만들기 : 대시 보드 / 보고서에 많은 데이터가있는 경우 스파크 라인을 사용하여 시각적으로 만드는 것을 고려할 수 있습니다. 스파크 라인은 셀에있는 작은 차트이며 데이터 세트를 사용하여 만들 수 있습니다. 시간 경과에 따른 추세를 표시하고 동시에 대시 보드의 공간을 절약하려는 경우 유용합니다. 여기에서 Excel 스파크 라인에 대해 자세히 알아볼 수 있습니다.
• 대비되는 색상을 사용하여 데이터 강조 : 이해하기 쉽도록 차트에서 데이터를 강조 표시해야하는 일반적인 차트 작성 팁입니다. 예를 들어 판매 데이터가있는 경우 판매 가치가 가장 낮은 연도를 빨간색으로 강조 표시 할 수 있습니다.

여기에서 차트 작성 튜토리얼 중 일부를 살펴볼 수 있습니다.

또한 Excel 차트 작성에 대해 더 자세히 알아 보려면 Excel 차트 작성 전문가 Jon Peltier의 블로그를 방문하는 것이 좋습니다.

Excel 대시 보드해야 할 일과하지 말아야 할 일

먼저 금지 사항부터 시작하겠습니다!

다음은 Excel 대시 보드를 만들 때 피하는 것이 좋습니다. 다시 말하지만 이는 프로젝트 및 이해 관계자에 따라 다르지만 대부분의 경우에 유효합니다.

• 대시 보드를 어지럽히 지 마십시오. 데이터와 차트가 있다고해서 반드시 진행되어야한다는 의미는 아닙니다. 대시 보드에서. 대시 보드의 목적은 문제를 식별하거나 의사 결정을 돕는 것임을 기억하십시오. 따라서 관련성을 유지하고 거기에 속하지 않는 모든 것을 삭제하세요. 나는 종종 꼭 필요한 것이 있으면 좋은지 스스로에게 묻습니다. 그런 다음 계속해서 가지고있는 모든 것을 제거합니다.
• 변동적인 공식을 사용하지 마십시오. 계산 속도가 느려질 수 있습니다.
• 추가 데이터를 보관하지 마십시오. 통합 문서 : 해당 데이터가 필요한 경우 대시 보드 사본을 만들어 백업으로 보관하세요.

이제 몇 가지해야 할 일 (또는 모범 사례)을 살펴 보겠습니다.

• 차트 / 섹션 번호 지정 : 대시 보드는 임의의 차트 및 데이터 요소 집합이 아닙니다. 대신 한 것이 다른 것으로 이어지는 이야기입니다.시청자가 올바른 순서로 단계를 따르도록해야하므로 번호를 매기는 것이 가장 좋습니다. 실시간으로 발표 할 때 안내해 줄 수는 있지만 나중에 다른 사람이 대시 보드를 사용하거나 대시 보드에서 인쇄물을 가져 오는 경우 큰 도움이됩니다.
• 대시 보드 영역에서 이동 제한 : 사용자가 실수로 스크롤하지 않도록 모든 행 / 열을 숨 깁니다.
• 중요 행 / 열 고정 : 일부 행 / 열이 대시 보드에 항상 표시되도록하려면 창 고정을 사용합니다.
• li>
• 모양 / 차트 고정 : 누군가가 셀을 숨기거나 크기를 조정할 때 도형 / 차트 또는 대화 형 컨트롤이 숨기거나 크기를 조정하지 않도록합니다. Excel 카메라 도구를 사용하여 차트 / 표의 스냅 샷을 찍고 Excel 대시 보드에 배치 할 수도 있습니다 (이러한 이미지는 동적이며 백엔드 차트 / 표가 업데이트 될 때 업데이트 됨).
• 제공 사용자 가이드 : 대시 보드가 복잡한 경우 별도의 워크 시트를 만들고 단계를 강조 표시하는 것이 좋습니다. 사람들이 대시 보드에 없을 때에도 대시 보드를 사용하는 데 도움이됩니다.
• 조합 차트로 공간 절약 : 조합 차트 (예 : 글 머리 기호 차트, 온도계 차트 및 실제 대 목표 차트)를 사용하여 워크 시트를 저장합니다. 공백.
• 기호 사용 & 조건부 서식 : 기호 (예 : 위쪽 / 아래쪽 화살표 또는 확인 표시 / 십자 표시) 및 조건부 서식을 사용하여 분석 레이어를 대시 보드에 추가합니다 (하지만 과도하게 사용하지 마십시오).

Excel에서 전문 대시 보드를 만들고 싶으십니까? 세계적 수준의 Excel 대시 보드를 만드는 방법에 대한 모든 것을 보여주는 온라인 Excel 대시 보드 과정을 확인하십시오.

대시 보드 예제

다운로드하여 재생할 수있는 몇 가지 멋진 Excel 대시 보드 예제가 있습니다.

Excel KPI 대시 보드

이 대시 보드를 사용하여 다양한 회사의 KPI를 추적하고 그런 다음 글 머리 기호 차트를 사용하여 개별 회사의 실적을 자세히 살펴보십시오.

이 KPI 대시 보드를 다운로드하려면 여기를 클릭하십시오.

이 KPI 대시 보드를 만드는 방법을 배우고 싶다면 클릭하십시오. 여기.

Call Center Performance Dashboard

이 대시 보드를 사용하여 주요 KPI를 추적 할 수 있습니다. 콜센터. 이 대시 보드에서 조합 차트를 만드는 방법, 차트에서 특정 데이터 포인트를 강조 표시하는 방법, 라디오 버튼을 사용하여 정렬하는 방법 등을 배울 수 있습니다.

대시 보드를 다운로드하려면 여기를 클릭하세요.

이 대시 보드에 대한 자세한 내용은 여기에서 확인할 수 있습니다.

Excel 대시 보드에 시각화 된 EPL 시즌

이 대시 보드에서는 Excel 대시 보드에서 VBA를 사용하는 방법을 배웁니다. 이 대시 보드에서 왼쪽의 셀을 두 번 클릭하면 게임 세부 정보가 업데이트됩니다. 또한 VBA를 사용하여 사용자가이 대시 보드를 사용하도록 안내하는 도움말 메뉴를 표시합니다.

EPL 대시 보드를 다운로드하려면 여기를 클릭하세요.

여기에서이 대시 보드에 대해 자세히 알아볼 수 있습니다.

권장 대시 보드 교육

Excel에서 세계적 수준의 전문 대시 보드를 만드는 방법을 배우려면 무료 온라인 Excel 대시 보드 과정을 확인하십시오.