VBA 없이 Excel 양식 컨트롤로 대화형 대시보드 만드는 방법

대화형 대시보드는 Excel에서 방대한 데이터를 동적으로 시각화하고 분석할 수 있는 강력한 도구입니다. 데이터를 유연하게 탐색하며 빠르게 인사이트를 얻을 수 있죠. Excel의 양식 컨트롤(Form Controls)은 VBA 코드 없이도 이러한 대시보드를 만들 수 있는 훌륭한 기능으로, 버튼·슬라이더·콤보 상자·확인란 등을 통해 사용자가 데이터와 직접 상호작용할 수 있게 해줍니다.

이 글에서는 매출 데이터셋을 예제로 삼아, 양식 컨트롤을 활용해 대화형 대시보드를 단계별로 만드는 방법을 소개합니다.

1단계: 데이터셋 준비하기

• 매출 데이터를 Excel 테이블로 변환합니다. 먼저 데이터 범위를 선택하세요.
• 삽입 탭 → 테이블을 선택합니다.
  • '머리글 포함'에 체크한 뒤 확인을 클릭합니다.
• 가독성을 위해 테이블 이름을 SalesData로 지정합니다.

Excel 테이블을 사용하면 수식과 차트의 범위가 자동으로 동적 확장되어 관리가 훨씬 편리해집니다.

2단계: 양식 컨트롤 삽입 후 셀과 연결하기

데이터셋의 성격에 맞게 적절한 양식 컨트롤을 선택하면 됩니다.

옵션 단추(Option Button) — 지역별 필터링

• 개발 도구 탭 → 삽입 → 옵션 단추를 선택합니다.
• 대시보드 영역에 플러스 아이콘(+)을 클릭해 배치합니다.
• 옵션 단추를 마우스 오른쪽 버튼으로 클릭 → 컨트롤 서식을 선택합니다.

• 개체 서식 대화상자에서:
  • 셀 연결: 선택된 지역 값을 저장할 셀 I1을 지정합니다.
  • 3차원 음영에 체크한 뒤 확인을 클릭합니다.
• 옵션 단추를 오른쪽 클릭 → 텍스트 편집을 선택하고, 지역명(North, South, East, West)을 하나씩 입력합니다.

콤보 상자(Combo Box) — 제품별 필터링

• 개발 도구 탭 → 삽입 → 콤보 상자를 선택합니다.
• 대시보드 영역에 배치한 뒤 마우스 오른쪽 버튼으로 클릭 → 컨트롤 서식을 엽니다.
• 개체 서식 대화상자에서:
  • 범위 입력: 제품 목록(Product A~Product D) 범위를 지정합니다.
  • 셀 연결: 선택된 제품 값을 저장할 셀 J1을 지정합니다.
  • 3차원 음영 체크 후 확인을 클릭합니다.

확인란(Check Box) — 지표 표시 전환

• 개발 도구 탭 → 삽입 → 확인란을 선택해 대시보드에 배치합니다.
• 확인란을 오른쪽 클릭 → 컨트롤 서식을 열고 개체 서식 대화상자에서:
  • 셀 연결: 각 지표별로 K1, K2, K3 셀을 지정합니다.
  • 3차원 음영 체크 후 확인을 클릭합니다.
• 오른쪽 클릭 → 텍스트 편집으로 지표명(Revenue, Units Sold, Profit)을 입력합니다.
• 각 확인란은 연결된 셀에 선택 여부에 따라 TRUE 또는 FALSE 값을 반환합니다.

3단계: 동적 수식 만들기

FILTER, SWITCH, IF 함수를 활용하면 양식 컨트롤의 선택값에 따라 매출 데이터가 자동으로 추출되어 대시보드가 실시간으로 반응하게 됩니다.

FILTER 함수로 동적 필터링

지역별 필터링:

선택한 지역에 따라 매출 데이터를 추출하려면 아래 수식을 입력합니다.

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

• FILTER 함수는 Region 열의 값이 셀 J1의 선택값과 일치하는 행만 SalesData 테이블에서 추출해 표시합니다.
• SWITCH 함수는 J1의 숫자 값(1~4)을 해당 지역명("North", "South", "East", "West")으로 변환해 줍니다.

제품별 필터링:

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

이 수식은 콤보 상자의 선택에 따라 매출 데이터를 필터링합니다. 예를 들어 Product A를 선택하면 Product 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")))

두 조건을 곱(*)으로 결합하면 옵션 단추와 콤보 상자의 선택을 동시에 반영할 수 있습니다. 예를 들어 South 지역과 Product B를 선택하면, 해당 지역의 Product B 매출 정보만 표시됩니다.

IF 함수로 동적 지표 표시하기

확인란의 선택 여부에 따라 매출 지표를 표시하거나 숨기려면 IF 함수를 사용합니다. 확인란을 선택하면 연결 셀이 TRUE, 해제하면 FALSE를 반환하고, IF 함수는 이 값에 따라 결과를 출력합니다.

매출액(Revenue):

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

Revenue 확인란을 선택하면 필터링된 데이터의 매출 합계가 표시됩니다.

이익(Profit):

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

Profit 확인란을 선택하면 이익 합계가 계산되어 나타납니다.

판매 수량(Units Sold):

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

Units Sold 확인란을 선택하면 총 판매 수량이 집계됩니다.

실행 결과:

4단계: 동적 차트 삽입하기

• 필터링된 데이터를 선택합니다.
• 삽입 탭 → 추천 차트(모든 차트) → 세로 막대형 차트를 선택합니다.

• 차트는 콤보 상자와 옵션 단추의 선택에 따라 자동으로 갱신됩니다. 아래 예시는 North 지역의 Product A 판매 현황을 보여줍니다.

• 필터 옵션을 바꿔 보면 차트가 실시간으로 업데이트되는 것을 확인할 수 있습니다. 여기서는 East 지역과 Product C를 선택했습니다.

5단계: 대시보드 디자인 다듬기

• 도형 삽입:
  • 필터 영역, 차트 영역, 지표 영역 등 섹션을 구분할 수 있도록 필요한 도형을 추가합니다.
• 서식 꾸미기:
  • 일관된 색상, 글꼴, 스타일을 사용해 통일감을 줍니다.
  • 조건부 서식을 활용하면 중요한 수치를 한눈에 강조할 수 있습니다.

마무리

지금까지 살펴본 단계를 따라 하면 VBA 없이도 Excel 양식 컨트롤만으로 대화형 대시보드를 완성할 수 있습니다. 대용량 데이터셋을 활용해 시각적으로 뛰어난 대시보드를 만드는 과정을 모두 다루었으며, 이렇게 만든 대시보드는 데이터를 동적으로 필터링하고 분석할 수 있어 의사결정에 강력한 도구가 됩니다. 양식 컨트롤의 다양한 추가 기능도 직접 실험해 보며 활용 범위를 넓혀 보세요.