Excel은 VBA(매크로 코드) 없이도 강력한 기본 기능만으로 동적이고 대화형인 파일을 만들 수 있는 도구입니다. 보고서, 대시보드, 입력 양식 등을 구성하고, 데이터가 즉시 자동으로 갱신되는 인터랙티브 보고서를 손쉽게 제작할 수 있습니다.
이 튜토리얼에서는 VBA를 전혀 사용하지 않고 이러한 대화형 파일을 만드는 방법을 단계별로 소개합니다.
1단계: 데이터 세트 준비하기
대화형 Excel 파일을 만들려면 먼저 데이터 세트를 깔끔하게 정리하고 체계적으로 구조화해야 합니다. 잘 정돈된 데이터는 안정적인 인터랙티브 기능의 기반이 됩니다.
- 데이터 세트에서 불필요한 내용을 정리합니다.
- 중복 항목과 불필요한 공백을 제거합니다.
- 서식 오류와 데이터 유형 문제를 수정합니다.
데이터를 표(Table)로 변환
- 데이터 범위를 선택합니다.
- 삽입 탭 >> 표를 선택합니다.
- 머리글 포함에 체크합니다.
- 확인을 클릭합니다.
- 표 이름 변경:
- 테이블 디자인 탭 >> 테이블 이름에서 Sales처럼 알아보기 쉬운 이름을 지정합니다.
2단계: 데이터 유효성 검사 드롭다운 목록 활용
드롭다운 목록은 대화형 Excel 파일의 필수 요소입니다. 사용자가 빠르고 일관성 있게 통제된 방식으로 값을 입력할 수 있게 해주며, 나중에 데이터·차트·KPI를 인터랙티브하게 필터링하는 데 활용됩니다.
작업 순서:
도우미 목록 작성: 시트 상단이나 우측에 도우미 목록을 만듭니다. 나중에 숨길 수 있습니다.
- 셀 하나를 선택하고 아래 수식을 입력해 고유한 지역 목록을 추출합니다.
=SORT(UNIQUE(Sales[Region]))
- 같은 방식으로 제품명 목록을 추출하는 수식을 입력합니다.
=SORT(UNIQUE(Sales[Product]))
- (선택 사항) 수식을 입력하기 전에 각 목록 위에 "전체" 항목을 추가하면 전체 보기 옵션을 제공할 수 있습니다.
드롭다운 목록 만들기:
- 드롭다운을 넣을 셀을 선택합니다(예: B2).
- 데이터 탭 >> 데이터 유효성 검사를 선택합니다.
- 제한 대상에서 목록을 선택합니다.
- 원본에 도우미 열의 지역 목록 범위를 지정합니다.
- 확인을 클릭합니다.
- 동일한 방법으로 제품 드롭다운 목록을 만듭니다.
- 제한 대상에서 목록을 선택합니다.
- 원본에 도우미 열의 제품 목록 범위를 지정합니다.
팁: 목록에 이름 범위를 정의해 두면 데이터 유효성 검사에서 더 편리하게 사용할 수 있습니다.
3단계: 드롭다운과 연동되는 동적 수식 활용
드롭다운과 동적 수식을 결합하면 KPI가 자동으로 갱신되고 데이터가 실시간으로 필터링됩니다.
KPI 만들기:
- 총 매출액:
=SUMIFS(Sales[Revenue], Sales[Region], IF($B$2="전체","*", $B$2), Sales[Product], IF($B$4="전체","*", $B$4))
- 평균 할인율:
=AVERAGEIFS(Sales[Discount], Sales[Region], IF($B$2="전체","*", $B$2), Sales[Product], IF($B$4="전체","*", $B$4))
가독성을 높이려면 백분율 서식을 적용하세요.
- 총 주문 건수:
=COUNTIFS(Sales[Region], IF($B$2="전체","*", $B$2), Sales[Product], IF($B$4="전체","*", $B$4))
- 총 판매 수량:
=SUMIFS(Sales[Units], Sales[Region], IF($B$2="전체","*", $B$2), Sales[Product], IF($B$4="전체","*", $B$4))
인터랙티브 기능 테스트:
- 드롭다운 목록에서 지역과 제품을 선택합니다.
- KPI가 자동으로 업데이트되는지 확인합니다.
4단계: 이름 범위를 활용한 동적 차트 만들기
사용자 선택에 따라 자동으로 갱신되는 차트를 만들어 보겠습니다.
필터링된 데이터:
=FILTER(CHOOSE({1,2}, Sales[Date], Sales[Revenue]), IF($B$2="전체", Sales[Region]<>"", Sales[Region]=$B$2) * IF($B$4="전체", Sales[Product]<>"", Sales[Product]=$B$4), "일치하는 행 없음")
동적 이름 범위 정의:
- 수식 탭 >> 이름 관리자 >> 새로 만들기를 선택합니다.
- 이름: FilteredDates
- 참조 대상:
=INDEX('Interactive Sheet'!$B$7#, ,1)
- 이름: FilteredRevenue
- 참조 대상:
=INDEX('Interactive Sheet'!$B$7#, ,2)
차트 만들기:
- 삽입 탭 >> 차트에서 꺾은선형 차트를 선택합니다.
- 차트를 마우스 오른쪽 버튼으로 클릭 >> 데이터 선택을 고릅니다.
- 계열 값에는 다음을 입력합니다.
='Interactive Sheet'!FilteredRevenue
- 가로(항목) 축 레이블에는 다음을 입력합니다.
='Interactive Sheet'!FilteredDates
인터랙티브 기능 테스트:
- 지역을 'East'로, 제품은 '전체'로 선택해 봅니다.
- 차트에 해당하는 모든 데이터가 표시됩니다.
- 특정 지역과 특정 제품을 선택하면 차트가 즉시 필터링됩니다.
- VBA 없이도 어떤 조합이든 자유롭게 동작합니다.
5단계: 조건부 서식으로 시각적 피드백 제공
조건부 서식을 활용하면 주요 값이나 사용자 선택 결과를 즉시 강조 표시할 수 있습니다.
작업 순서:
- 데이터 범위를 선택합니다.
- 홈 탭 >> 조건부 서식 >> 새 규칙을 선택합니다.
- 수식을 사용하여 서식을 지정할 셀 결정을 선택합니다.
- 아래 수식을 입력합니다.
=AND(OR('Interactive Sheet'!$B$2="전체", $C2='Interactive Sheet'!$B$2), OR('Interactive Sheet'!$B$4="전체", $F2='Interactive Sheet'!$B$4))- 강조 색상을 선택합니다.
- 확인을 클릭합니다.
이렇게 하면 조건에 맞는 행만 강조되어 필터 선택 결과를 한눈에 시각적으로 확인할 수 있습니다.
6단계: 피벗 테이블, 슬라이서, 대화형 차트
피벗 테이블, 피벗 차트, 슬라이서는 Excel의 핵심 인터랙티브 기능으로, 데이터 필터링을 놀랍도록 쉽게 만들어 줍니다.
피벗 테이블 만들기:
- 데이터 범위를 선택합니다.
- 삽입 탭 >> 피벗 테이블을 선택합니다.
- 위치를 지정합니다: 새 워크시트 또는 기존 워크시트.
- 확인을 클릭합니다.
- 피벗 테이블 필드에서:
- Product를 행 영역으로 끌어다 놓습니다.
- Revenue를 값 영역으로 끌어다 놓습니다.
피벗 차트 만들기:
- 피벗 테이블을 선택합니다.
- 피벗 테이블 분석 탭 >> 피벗 차트를 선택합니다.
- 묶은 세로 막대형 차트를 고릅니다.
- 확인을 클릭합니다.
대화형 슬라이서 추가:
- 피벗 테이블 분석 탭 >> 슬라이서 삽입을 선택합니다.
- 필터링에 사용할 필드를 선택합니다.
- 지역, 제품, 영업 담당자 등
- 슬라이서의 버튼을 클릭하면 차트와 테이블이 즉시 필터링됩니다.
- 슬라이서와 차트를 함께 배치하려면 개체 그룹화를 활용하세요.
- 슬라이서를 차트 근처나 차트 위에 놓고, 모든 개체(차트와 슬라이서)를 선택한 뒤 마우스 오른쪽 버튼을 클릭하여 그룹을 선택합니다.
공유 슬라이서로 여러 피벗 테이블 연결하기
- 동일한 데이터 원본으로 여러 개의 피벗 테이블을 만듭니다.
- 슬라이서를 삽입합니다.
- 슬라이서를 마우스 오른쪽 버튼으로 클릭 >> 보고서 연결을 선택합니다.
- 슬라이서를 여러 피벗 테이블에 연결합니다.
- 이제 슬라이서와 차트를 복사해 대시보드 시트에 붙여넣습니다.
- 피벗 테이블 시트는 숨겨두면 됩니다.
7단계: 하이퍼링크로 탐색 기능 구현
Excel 파일을 클릭형 앱처럼 느껴지게 하려면 하이퍼링크를 활용한 탐색 기능을 추가하세요. 사용자가 다른 시트, 차트, 테이블로 손쉽게 이동할 수 있습니다.
작업 순서:
- 삽입 탭 >> 일러스트레이션에서 도형 >> 직사각형을 선택합니다.
- 도형 이름을 "판매 데이터"로 지정합니다.
- 도형을 마우스 오른쪽 버튼으로 클릭 >> 연결을 선택합니다.
- 하이퍼링크 삽입 대화 상자에서:
- 문서 안의 위치를 선택 >> 셀 참조에 A1 입력 >> Sales 시트를 선택합니다.
- 확인을 클릭합니다.
- 문서 안의 위치를 선택 >> 셀 참조에 A3 입력 >> PivotTable 시트를 선택합니다.
- 확인을 클릭합니다.
- 버튼처럼 보이도록 서식을 다듬으면 더 깔끔해집니다.
- 이런 탐색용 하이퍼링크를 활용하면 "대시보드"와 여러 "세부 정보" 시트 사이를 손쉽게 오갈 수 있습니다.
완성된 대화형 Excel 파일:
- 아래 예시는 모든 지역과 "액세서리" 제품을 선택한 상태입니다.
- 슬라이서에서도 선택할 수 있습니다.
- 모든 데이터가 자동으로 갱신됩니다.
활용 팁
- 이 글에서는 FILTER 함수를 이용한 동적 차트와 피벗 차트 두 가지 인터랙티브 방법을 모두 소개했습니다. 용도에 맞는 방식을 골라 사용하세요.
- VBA 없이 더 다양한 인터랙티브 옵션을 제공하는 양식 컨트롤(Form Controls)도 살펴볼 만합니다.
- 검색창을 만들고 싶다면 XLOOKUP 함수를 활용할 수 있습니다.
연습용 통합 문서 다운로드
마무리
이번 가이드에서 소개한 기능과 기술들을 활용하면 VBA 코드 한 줄 없이도 정교한 대화형 Excel 파일을 만들 수 있습니다. 핵심은 여러 기술을 조합해 최대한의 인터랙티브 효과를 내는 것입니다. 인터랙티브 요소는 반드시 꼼꼼히 테스트하고, 사용자에게 명확한 안내를 제공하는 것도 잊지 마세요. 꾸준히 연습하면 강력하면서도 누구나 쓰기 쉬운 멋진 대시보드와 도구를 직접 만들 수 있을 것입니다.