Excel에서 동적 대시보드를 만드는 방법: 단계별 가이드

Excel에서도 동적 대시보드를 만드는 방법 있다는 것은 익히 잘 알려져 있습니다.
다음은 챗지피를 이용해서 연습용 파일을 생성하고, 필요한 대시보드를 구성하는 작업을 실습한 것입니다.

 

프롬프트는 아래와 같습니다.

"엑셀에서 대시보드를 만드는 예제 문제를 만들어줘"

-> 뭔가 장황한 답변을 내어놓습니다. 예제파일을 만들어서 작업을 직접 해보라는 이야기들이었습니다.

 

다시 프롬프트를 넣어 봅니다.

"예제에 필요한 데이터셋도 만들어줘"

이런 데이터 파일을 생성해서 다운로드 받게 해줍니다.

ABC_Retail_Sales_Data.xlsx

0.20MB

 

 

세번째 프롬프트를 넣었습니다.

"네가 만들어준 데이터셋으로 대시보드를 만들어줘, 다운로드 받을 수 있게 링크도 만들어줘"

아래와 같은 엑셀 파일을 만들어줍니다.

열어보니, 대시보드가 있는데, rawdata와 연동이 안되어 있습니다.

ABC_Retail_Sales_Data_with_Dashboard.xlsx

0.20MB

 

 

네번째 프롬프트입니다.

"대시보드 시트에 있는 값들은 다른 시트에 있는 값들과 연동이 안되는거야?"

라고 물었더니,

"현재 대시보드 시트의 값은 정적이며 다른 시트의 데이터에 연결되지 않습니다.

Excel 수식을 사용하여 다른 시트의 값에 동적으로 연결되도록 대시보드를 업데이트해 보겠습니다."

그리고 아래와 같이 동적으로 연동된 엑셀파일을 반환했습니다.

아래 그림은 요청한 엑셀 대시보드입니다.

ABC_Retail_Sales_Data_with_Dynamic_Dashboard.xlsx

0.20MB

 

 

---

 

전체 단계별 진행과정은 아래와 같습니다.

마지막 대시보드 시트에 있는 값들에 들어 있는 함수에 주목해서 살펴보시면 됩니다. 

 

 

1단계: 데이터 준비

우선, 잘 구조화된 데이터셋이 필요합니다. 이 예제에서는 2024년 상반기 동안의 "ABC Retail"이라는 회사의 판매 데이터를 사용합니다. 데이터셋에는 다음 세 가지 시트가 포함되어 있습니다:

1. Sales_Data: Date, Product_ID, Product_Name, Category, Salesperson, Region, Units_Sold, Unit_Price, Total_Sales 열을 포함한 원시 판매 데이터를 포함합니다.
2. Product_Info: Product_ID, Product_Name, Category 열을 포함한 제품 세부 정보를 포함합니다.
3. Salesperson_Info: Salesperson_ID, Salesperson_Name, Region 열을 포함한 판매원 세부 정보를 포함합니다.

이 데이터는 ABC_Retail_Sales_Data.xlsx라는 Excel 파일에 제공됩니다.

2단계: 데이터 로드 및 정리

먼저, 데이터를 Excel에 로드하고 데이터가 깨끗하고 잘 구조화되어 있는지 확인합니다. 이는 누락되거나 잘못된 값을 확인하고 처리하는 작업을 포함합니다. 데이터는 분석하기 쉬운 형식이어야 하며, 각 열은 명확하게 라벨링되고 각 행은 하나의 레코드를 나타내야 합니다.

3단계: 주요 지표 계산

다음으로, 대시보드에 표시될 주요 지표를 계산합니다:

1. 총 판매: Sales_Data 시트에서 Total_Sales 열을 합산하여 2024년 상반기의 총 판매를 구합니다.
2. 월별 판매 추이: 월별로 Total_Sales를 집계하여 시간 경과에 따른 판매 추이를 보여줍니다.
3. 제품 카테고리별 판매: Category별로 Total_Sales를 합산하여 다양한 제품 카테고리 간의 판매 분포를 파악합니다.
4. 상위 5개 제품: 총 판매액 기준으로 상위 5개 제품을 식별합니다.
5. 지역별 판매: Region별로 Total_Sales를 합산하여 지리적으로 판매가 어떻게 분포되어 있는지 확인합니다.
6. 판매원 성과: Salesperson별로 Total_Sales를 집계하여 각 판매원의 성과를 평가합니다.

4단계: 대시보드 만들기

이제 "Dashboard"라는 새 시트에 대시보드를 만듭니다. 대시보드에는 다음이 포함됩니다:

1. 총 판매 개요: 연간 총 판매를 보여주는 카드 또는 배너.
2. 월별 판매 추이: 1월부터 6월까지의 판매 추이를 보여주는 선 그래프.
3. 제품 카테고리별 판매: 다양한 제품 카테고리 간의 판매 분포를 보여주는 원형 그래프.
4. 상위 5개 제품: 총 판매 기준으로 상위 5개 제품을 나열하는 막대 그래프.
5. 지역별 판매: 지역별 판매 분포를 보여주는 막대 그래프.
6. 판매원 성과: 판매원과 그들의 총 판매 및 월별 평균 판매를 나열하는 표.

대시보드를 동적으로 만들기 위해, Excel 수식을 사용하여 시각화를 다른 시트의 데이터에 연결합니다. 이렇게 하면 원시 데이터가 변경될 때 대시보드가 자동으로 업데이트됩니다.

5단계: Excel에서 대시보드 구현

대시보드의 각 구성 요소를 구현하는 방법은 다음과 같습니다:

총 판매 개요

1. A1 셀: "Total Sales (First Half of 2024)" 작성.
2. A2 셀: =SUM(Sales_Data!I:I) 수식을 사용하여 총 판매를 동적으로 계산.

월별 판매 추이

1. A4 셀: "Monthly Sales Trend" 작성.
2. A5 및 B5 셀: 각각 "Month" 및 "Total Sales"를 헤더로 작성.
3. A6에서 A11 셀: 월 이름 (1월부터 6월까지) 작성.
4. B6에서 B11 셀: =SUMIFS(Sales_Data!I:I, Sales_Data!A:A, ">=2024-01-01", Sales_Data!A:A, "<2024-02-01") (다른 달에 대해서도 동일) 수식을 사용하여 월별 판매를 동적으로 계산.

이 데이터 범위를 기반으로 선 그래프를 만듭니다.

제품 카테고리별 판매

1. A13 셀: "Sales by Product Category" 작성.
2. A14 및 B14 셀: 각각 "Category" 및 "Total Sales"를 헤더로 작성.
3. A15부터: 각 제품 카테고리 작성.
4. B15부터: =SUMIFS(Sales_Data!I:I, Sales_Data!D:D, "Electronics") (다른 카테고리에 대해서도 동일) 수식을 사용하여 카테고리별 판매를 동적으로 계산.

이 데이터 범위를 기반으로 원형 그래프를 만듭니다.

상위 5개 제품

1. A23 셀: "Top 5 Products by Sales" 작성.
2. A24 및 B24 셀: 각각 "Product Name" 및 "Total Sales"를 헤더로 작성.
3. A25에서 A29 셀: 상위 5개 제품 작성.
4. B25에서 B29 셀: =SUMIFS(Sales_Data!I:I, Sales_Data!C:C, "Product 1") (다른 상위 제품에 대해서도 동일) 수식을 사용하여 판매 동적으로 계산.

이 데이터 범위를 기반으로 막대 그래프를 만듭니다.

지역별 판매

1. A33 셀: "Sales by Region" 작성.
2. A34 및 B34 셀: 각각 "Region" 및 "Total Sales"를 헤더로 작성.
3. A35부터: 각 지역 작성.
4. B35부터: =SUMIFS(Sales_Data!I:I, Sales_Data!F:F, "North") (다른 지역에 대해서도 동일) 수식을 사용하여 지역별 판매를 동적으로 계산.

이 데이터 범위를 기반으로 막대 그래프를 만듭니다.

판매원 성과

1. A43 셀: "Salesperson Performance" 작성.
2. A44, B44, C44 셀: 각각 "Salesperson", "Total Sales", "Average Sales Per Month"를 헤더로 작성.
3. A45부터: 각 판매원 작성.
4. B45부터: =SUMIFS(Sales_Data!I:I, Sales_Data!E:E, "Salesperson 1") (다른 판매원에 대해서도 동일) 수식을 사용하여 총 판매를 동적으로 계산.
5. C45부터: =AVERAGEIFS(Sales_Data!I:I, Sales_Data!E:E, "Salesperson 1") (다른 판매원에 대해서도 동일) 수식을 사용하여 평균 판매를 동적으로 계산.

6단계: 대시보드 향상

대시보드를 더 대화형으로 만들고 사용자 친화적으로 만들기 위해 다음과 같은 향상 기능을 고려하십시오:

1. 슬라이서 및 필터: 사용자가 데이터와 상호 작용하고 특정 세그먼트를 볼 수 있도록 슬라이서와 필터를 추가합니다 (예: 지역, 제품 카테고리, 판매원별).
2. 조건부 서식: 주요 지표와 추세를 강조하기 위해 조건부 서식을 사용합니다 (예: 상위 판매, 성장 영역).
3. 동적 제목: 선택한 데이터 범위 또는 필터에 따라 변경되는 동적 제목을 사용합니다.

결론

Excel에서 동적 판매 대시보드를 만드는 것은 데이터를 준비하고, 주요 지표를 계산하고, 기본 데이터에 연결되는 대화형 시각화를 구축하는 것을 포함합니다. 이러한 접근 방식은 데이터가 변경될 때 대시보드가 자동으로 업데이트되어 판매 성과에 대한 실시간 통찰력을 제공합니다.

이 가이드에서는 Excel을 사용하여 포괄적이고 대화형인 판매 대시보드를 만드는 단계별 접근 방식을 제공했습니다. 이러한 단계를 따르고 Excel의 강력한 수식과 차트 기능을 활용하면 데이터 기반 의사 결정을 지원하는 대시보드를 만들 수 있습니다.