VBA 없이 대화형 Excel 통합 문서 만들기:단계별 가이드

이 튜토리얼에서는 VBA 없이 이러한 대화형 파일을 작성하는 방법을 보여 드리겠습니다.

1. 데이터 세트 준비

대화형 Excel 파일을 만들려면 데이터세트를 정리하고 구조화해야 합니다. 효과적인 대화형 동작을 생성하려면 형식이 잘 지정되고 구조화된 데이터세트가 중요합니다.

• 데이터세트를 정리하세요.
• 중복된 내용과 공백을 제거하세요.
• 올바른 형식 및 데이터 유형 문제.

데이터를 테이블로 변환

• 데이터세트를 선택하세요.
• 삽입으로 이동 탭>> 표 선택 .
• 내 테이블에 헤더가 있습니다를 확인하세요. .
• 확인을 클릭하세요. .

• 테이블 이름 바꾸기:
  • 테이블 디자인으로 이동 탭>> 테이블 이름 선택 판매와 같은 관련 이름을 지정합니다. .

2. 데이터 검증 드롭다운 목록 사용

드롭다운 목록은 대화형 Excel 파일을 만드는 데 필수적인 요소입니다. 드롭다운 목록을 사용하면 빠르고 일관되며 제어된 사용자 입력이 가능합니다. 나중에 이를 사용하여 데이터, 차트 및 KPI를 대화형으로 필터링할 수 있습니다.

단계:

도우미 목록 작성: 시트 상단이나 오른쪽에 도우미 목록을 작성하세요. 나중에 숨길 수 있습니다.

• 셀을 선택하고 다음 수식을 입력하여 고유한 지역을 나열하세요.

=SORT(UNIQUE(Sales[Region]))

• 셀을 선택하고 다음 수식을 입력하여 제품 이름을 나열하세요.

=SORT(UNIQUE(Sales[Product]))

• 선택사항: 고유한 목록을 표시하려면 수식을 삽입하기 전에 각 목록 위에 '모두'를 추가하세요.

드롭다운 목록 만들기:

• 드롭다운을 원하는 셀을 선택하세요(예:B2).
• 데이터로 이동 탭>> 데이터 유효성 검사를 선택합니다. .
• 허용에서 , 목록을 선택하세요. .
• 소스에서 , 도우미 열에서 지역 목록을 선택하세요.

• 확인을 클릭하세요. .

• 비슷한 단계에 따라 제품에 대한 드롭다운 목록을 만듭니다.
• 허용에서 , 목록을 선택하세요. .
• 소스에서 , 도우미 열에서 제품 목록을 선택하세요.

도움말: 목록에 대한 명명된 범위를 만든 다음 데이터 유효성 검사에 사용할 수 있습니다.

3. 드롭다운으로 동적 수식 사용

동적 수식을 드롭다운과 통합하면 자동으로 KPI가 업데이트되고 데이터가 필터링됩니다.

KPI 만들기:

• 총 수익:

=SUMIFS(
Sales[Revenue],
Sales[Region], IF($B$2="All","*", $B$2),
Sales[Product], IF($B$4="All","*", $B$4))

• 평균 할인율:

=AVERAGEIFS(
Sales[Discount],
Sales[Region], IF($B$2="All","*", $B$2),
Sales[Product], IF($B$4="All","*", $B$4))

깔끔한 모양을 위해 백분율 형식을 적용하세요.

• 총 주문:

=COUNTIFS(
Sales[Region], IF($B$2="All","*", $B$2),
Sales[Product], IF($B$4="All","*", $B$4))

• 총 판매 수량:

=SUMIFS(
Sales[Units],
Sales[Region], IF($B$2="All","*", $B$2),
Sales[Product], IF($B$4="All","*", $B$4))

상호작용 테스트:

• 드롭다운 목록에서 지역과 제품을 선택하세요.
• KPI는 자동으로 업데이트됩니다.

4. 명명된 범위로 동적 차트 만들기

사용자 선택에 따라 자동으로 업데이트되는 차트를 만드세요.

필터링된 데이터:

=FILTER(
CHOOSE({1,2}, Sales[Date], Sales[Revenue]),
IF($B$2="All", Sales[Region]<>"", Sales[Region]=$B$2) *
IF($B$4="All", Sales[Product]<>"", Sales[Product]=$B$4),
"No rows")

동적 명명 범위 정의:

• 수식으로 이동 탭>> 이름 관리자 선택>> 새로 만들기를 선택하세요. .
• 이름: 필터링된 날짜
• 참조:

=INDEX('Interactive Sheet'!$B$7#, ,1)

• 이름: 필터링된 수익
• 참조:

=INDEX('Interactive Sheet'!$B$7#, ,2)

차트 만들기:

• 삽입으로 이동 탭>> 차트에서>> 선 차트를 선택합니다. .
• 차트를 마우스 오른쪽 버튼으로 클릭하고>> 데이터 선택을 선택합니다. .

• 시리즈 값의 경우 다음을 입력하세요.

='Interactive Sheet'!FilteredRevenue

• 가로축 라벨의 경우:

='Interactive Sheet'!FilteredDates

상호작용 테스트:

• 동부 지역과 모든 제품을 선택하세요.
• 차트에는 모든 관련 데이터가 표시됩니다.

• 특정 지역과 특정 제품을 선택하세요.
• 차트가 즉시 필터링됩니다.
• 이것은 VBA가 없는 모든 조합에서 작동합니다.

5. 시각적 피드백을 위해 조건부 서식 사용

조건부 서식을 사용하여 주요 값이나 사용자 선택을 즉시 강조하세요.

단계:

• 데이터 범위를 선택하세요.
• 홈으로 이동 탭>> 조건부 서식을 선택합니다.>> 새 규칙을 선택합니다. .
• 수식을 사용하여 서식을 지정할 셀 결정을 선택합니다. .
• 다음 수식을 삽입하세요:

=AND(OR('Interactive Sheet'!$B$2="All", $C2='Interactive Sheet'!$B$2), OR('Interactive Sheet'!$B$4="All", $F2='Interactive Sheet'!$B$4))

• 하이라이트 색상을 선택하세요.
• 확인을 클릭하세요. .

일치하는 행만 강조 표시되어 필터 선택이 시각적으로 강화됩니다.

6. 피벗 테이블, 슬라이서 및 대화형 차트

피벗 테이블, 피벗 차트 및 슬라이서는 데이터 필터링을 매우 쉽게 해주는 Excel의 핵심 대화형 기능입니다.

피벗 테이블 만들기:

• 데이터 범위를 선택하세요.
• 삽입으로 이동 탭>> 피벗 테이블 선택 .
• 위치 선택:새 또는 기존 워크시트 .
• 확인을 클릭하세요. .

• 피벗 테이블 필드에서 :
  • 제품 드래그 행 지역.
  • 수익을 드래그하세요. 가치 지역.

피벗 차트 만들기:

• 피벗 테이블을 선택합니다.
• 피벗 테이블 분석으로 이동 탭>> 피벗 차트 선택 .
• 클러스터형 열 선택 차트.
• 확인을 클릭하세요. .

대화형 슬라이서 추가:

• 피벗 테이블 분석으로 이동 탭>> 슬라이서 삽입을 선택합니다. .
• 다음과 같이 필터링할 필드를 선택하세요.
  • 지역, 제품, 영업사원 등
• 슬라이서의 버튼을 클릭하면 차트와 표를 즉시 필터링할 수 있습니다.

• 슬라이서와 차트를 함께 유지하려면 그룹화하면 됩니다.
• 슬라이서를 차트 근처에 배치하고 모든 개체(차트 및 슬라이서)를 선택한 다음 마우스 오른쪽 버튼을 클릭하고 그룹화를 선택합니다. .

공유 슬라이서로 여러 피벗 테이블 연결

• 동일한 데이터 소스에서 여러 피벗 테이블을 만듭니다.
• 슬라이서 삽입 .
• 슬라이서를 마우스 오른쪽 버튼으로 클릭하고>> 연결 보고를 선택합니다. .
• 슬라이서를 여러 피벗 테이블에 연결합니다.

• 이제 슬라이서와 차트를 대시보드 시트에 복사하세요.
• 피벗 테이블 시트를 숨길 수 있습니다.

7. 탐색을 위해 하이퍼링크 사용

Excel 파일을 클릭 가능한 앱처럼 느껴지게 하려면 탐색용 하이퍼링크를 사용할 수 있습니다. 이를 통해 사용자는 다른 시트, 차트, 표로 이동할 수 있습니다.

단계:

• 삽입으로 이동 탭>> 일러스트에서>> 도형을 선택합니다.>> 직사각형을 선택합니다. .
• 셰이프 이름을 '판매 데이터'로 지정합니다.
• 도형을 마우스 오른쪽 버튼으로 클릭하고>> 링크를 선택합니다. .

• 하이퍼링크 삽입에서 대화 상자:
• 이 문서에 배치를 선택합니다.>> 셀 참조 A1을 입력하세요.>> 판매를 선택합니다. 시트.
• 확인을 클릭하세요. .

• 이 문서에 배치를 선택합니다.>> 셀 참조 A3를 입력하세요.>> 피벗 테이블 을 선택합니다. 시트.
• 확인을 클릭하세요. .

• 깔끔하게 보이도록 버튼처럼 형식을 지정하세요.
• 이러한 탐색 하이퍼링크를 통해 사용자는 '대시보드'와 다양한 '세부정보' 시트 사이를 쉽게 이동할 수 있습니다.

최종 대화형 Excel 파일:

• 아래 예는 모든 지역과 선택한 '액세서리' 제품을 보여줍니다.
• 슬라이서에서도 선택할 수 있습니다.
• 모든 데이터는 자동으로 업데이트됩니다.

팁

• 다양한 대화형 방법을 보여주기 위해 동적 차트(FILTER 함수 사용)와 피벗 차트를 모두 시연했습니다. 귀하의 필요에 가장 적합한 유형을 선택할 수 있습니다.
• VBA 없이도 더 많은 대화형 옵션을 제공하는 양식 컨트롤을 탐색할 수도 있습니다.
• 검색창을 만들려면 동적 XLOOKUP 기능을 사용할 수 있습니다.

연습 문제집 다운로드

결론

이러한 기능과 기술을 사용하면 VBA 없이도 정교한 대화형 Excel 파일을 만들 수 있습니다. 핵심은 상호작용성을 극대화하기 위해 여러 기술을 결합하는 것입니다. 항상 대화형 요소를 철저하게 테스트하고 사용자에게 명확한 지침을 제공하는 것을 잊지 마세요. 연습을 통해 단 한 줄의 VBA 코드도 작성하지 않고도 데이터 분석을 강력하고 사용자 친화적으로 만드는 인상적인 대시보드와 도구를 만들 수 있습니다.