양식 컨트롤을 사용하여 대화형 Excel 대시보드 구축(VBA 필요 없음)

대화형 대시보드는 Excel에서 대규모 데이터를 동적으로 시각화하고 분석하기 위한 강력한 도구입니다. 데이터를 분석하여 빠른 통찰력을 얻을 수 있는 동적 방법을 제공합니다. Excel의 양식 컨트롤은 VBA 없이 이러한 대시보드를 만드는 데 탁월한 기능입니다. 이를 통해 사용자는 버튼, 슬라이더, 콤보 상자 및 확인란을 통해 데이터와 상호 작용할 수 있습니다. 이 글에서는 Excel에서 양식 컨트롤을 사용하여 대화형 대시보드를 만드는 방법을 보여드리겠습니다.

대화형 대시보드를 만드는 데 필요한 양식 컨트롤을 표시하는 판매 데이터 세트를 고려해 보세요.

1단계:데이터세트 준비

• 판매 데이터를 Excel 표로 변환합니다. 데이터 범위를 선택하세요.
• 삽입으로 이동 탭>> 표 선택 .
  • 내 표에 헤더가 있습니다를 선택합니다.>> 확인을 클릭하세요. .

• 더 나은 가독성을 위해 테이블 이름을 SalesData로 지정합니다.

Excel 표는 수식과 차트에 대한 동적 범위를 제공합니다.

2단계:양식 컨트롤 삽입 및 셀에 연결

데이터 세트 유형에 따라 관련 양식 컨트롤을 선택할 수 있습니다.

• 옵션 버튼 (지역별로 필터링하려면):
  • 개발자로 이동 탭>> 삽입에서>> 옵션 버튼을 선택하세요. .
  • 더하기 아이콘(+)을 배치하세요. 대시보드 영역에 있습니다.
  • 옵션 버튼을 마우스 오른쪽 버튼으로 클릭하세요.>> 형식 제어를 선택합니다. .

• • 형식 개체 대화 상자:
    • 셀 링크 :선택한 지역을 저장하려면 I1 셀을 선택하세요.
    • 3D 음영 선택>> 확인을 클릭하세요. .
  • 옵션 버튼을 마우스 오른쪽 버튼으로 클릭하세요.>> 텍스트 수정을 선택하세요.>> 지역 삽입 (예:북쪽, 남쪽, 동쪽, 서쪽) 하나씩

• 콤보 상자 (제품별로 필터링):
  • 개발자 탭 → 삽입 → 콤보 상자 .
  • 대시보드 영역에 배치하세요.
  • 개발자로 이동 탭>> 삽입에서>> 콤보 상자를 선택합니다. .
  • 더하기 아이콘(+)을 배치하세요. 대시보드 영역에 있습니다.
  • 콤보 상자를 마우스 오른쪽 버튼으로 클릭하세요.>> 형식 제어를 선택합니다. .
  • 형식 개체 대화 상자:
    • 입력 범위 :  제품 목록을 선택합니다(예:제품 A, 제품 B, 제품 C, 제품 D).
    • 셀 링크 :선택한 제품을 저장하려면 J1 셀을 선택하세요.
    • 3D 음영 선택>> 확인을 클릭하세요. .

• 체크박스 (수익, 판매 수량, 이익 등의 측정항목을 전환하려면):
  • 개발자로 이동 탭>> 삽입에서>> 체크박스를 선택하세요. .
  • 더하기 아이콘(+)을 배치하세요. 대시보드 영역에 있습니다.
  • 체크박스를 마우스 오른쪽 버튼으로 클릭하세요.>> 형식 제어를 선택합니다. .
  • 형식 개체 대화 상자:
    • 셀 링크 :선택한 측정항목을 저장하려면 K1 셀을 선택하세요.
    • 3D 음영 선택>> 확인을 클릭하세요. .
  • 옵션 버튼을 마우스 오른쪽 버튼으로 클릭하세요.>> 텍스트 수정을 선택하세요.>> 측정항목 이름(예:수익, 판매 수량, 이익)을 하나씩 삽입하세요.
  • 각 체크박스는 셀에 연결되면 TRUE 또는 FALSE를 반환합니다(예:수익, 판매 수량 및 이익은 각각 K1, K2, K3).

3단계:동적 수식 만들기

동적 기능을 사용하여 판매 데이터를 형식 컨트롤과 연결하여 대시보드를 대화형으로 만들 수 있습니다. FILTER, SWITCH, IF 함수를 사용하여 양식 선택에 따라 동적으로 데이터를 추출해 보겠습니다.

동적 FILTER 기능 사용

지역별 데이터 필터링 :

선택한 지역을 기준으로 판매 데이터를 필터링하려면 선택한 셀에 다음 수식을 삽입하세요.

=FILTER(SalesData, SalesData[Region] = SWITCH(J1, 1, "North", 2, "South", 3, "East", 4, "West"))

• 필터 함수는 SalesData 테이블을 추출하여 지역이 셀 J1에서 선택한 값과 일치하는 행을 표시합니다.
• 스위치 함수는 J1의 숫자 값(예:1, 2, 3 또는 4)을 해당 지역 이름("북쪽", "남쪽", "동쪽" 또는 "서쪽")으로 변환합니다. 일치하는 행만 결과에 포함됩니다.

제품별 데이터 필터링 :

선택한 제품을 기준으로 판매 데이터를 필터링하려면 선택한 셀에 다음 수식을 삽입하세요.

=FILTER(SalesData, SalesData[Product] = SWITCH(K1, 1, "Product A", 2, "Product B", 3, "Product C", 4, "Product D"))

이 수식은 콤보 상자 선택에 따라 판매 데이터를 필터링합니다. 제품 A의 판매 데이터를 반환하도록 콤보 상자에서 제품 A를 선택했습니다.

지역 및 제품별로 데이터 필터링 :

선택한 지역 및 제품을 기준으로 판매 데이터를 필터링하려면 선택한 셀에 다음 수식을 삽입하세요.

=FILTER(SalesData, (SalesData[Region] = SWITCH(J1, 1, "North", 2, "South", 3, "East", 4, "West")) * (SalesData[Product] = SWITCH(K1, 1, "Product A", 2, "Product B", 3, "Product C", 4, "Product D")))

이 수식은 콤보 상자 및 옵션 버튼 선택에 따라 판매 데이터를 필터링합니다. 우리는 남부 지역과 제품 B를 선택했습니다. 필터 기능은 남부 지역의 제품 B 판매 정보만 반환합니다.

동적 측정항목 표시

IF 함수를 사용하여 확인란 선택에 따른 판매 지표를 표시할 수 있습니다.

확인란 값을 기반으로 지표에 대해 다음 조건부 논리를 삽입합니다. 확인란을 선택하면 연결된 셀에서 TRUE를 반환하고, 그렇지 않으면 FALSE를 반환합니다. 연결된 셀의 값에 따라 IF 함수가 결과를 반환합니다.

수익 :

총 수익을 구하려면 다음 공식을 입력하세요.

=IF(L1, SUM(SalesData4[Revenue ($)]), "")

필터링된 판매 데이터에서 총계를 얻으려면 다음 수식을 삽입하세요:

수익 확인란을 선택하면 이 공식을 통해 수익이 합산됩니다.

이익 :

총 수익을 구하려면 다음 공식을 입력하세요.

=IF(M1, SUM(SalesData4[Profit ($)]), "")

필터링된 판매 데이터에서 총계를 얻으려면 다음 수식을 삽입하세요:

이익 확인란을 선택하면 이 공식은 이익의 합을 계산합니다.

판매 수량 :

총 판매량을 구하려면 다음 공식을 입력하세요.

=IF(N1, SUM(SalesData4[Units Sold]), "")

필터링된 판매 데이터에서 총계를 얻으려면 다음 수식을 삽입하세요:

판매 수량 확인란을 선택하면 이 수식은 총 수량을 합산합니다.

출력:

4단계:동적 차트 삽입

• 필터링된 데이터를 선택하세요.
• 삽입으로 이동 탭>> 모든 차트를 클릭합니다.>> 열 선택 차트.

• 차트는 콤보 상자에 따라 자동으로 업데이트됩니다. 및 옵션 버튼 . 북부 지역의 제품 A 매출을 보여주는 차트입니다.

• 차트의 동적 업데이트를 보려면 필터 옵션을 변경하세요. 동부 지역과 제품 C를 선택했습니다.

5단계:대시보드 맞춤설정

• 도형 삽입:
  • 필터, 차트, 측정항목과 같은 섹션을 구분하려면 대시보드 요구 사항에 따라 다양한 항목을 추가하세요.
• 형식 지정:
  • 일관적인 색상, 글꼴, 스타일을 사용할 수 있습니다.
  • 조건부 서식을 사용하여 강조표시할 수 있습니다.

결론

위 단계를 사용하면 Excel에서 양식 컨트롤이 포함된 대화형 대시보드를 만들 수 있습니다. 우리는 대규모 데이터 세트를 사용하여 시각적으로 매력적인 대화형 대시보드를 만들어 모든 중요한 단계를 보여주었습니다. 데이터를 동적으로 필터링하고 분석하여 의사 결정을 위한 강력한 도구로 활용할 수 있습니다. 양식 컨트롤의 추가 기능을 실험해 보는 것을 잊지 마세요.