대화형 드롭다운과 연결된 지표

정적 보고서는 하나의 고정된 보기만 보여 줍니다. 대화형 대시보드를 사용하면 독자가 보고 싶은 내용을 선택할 수 있고, 수치가 즉시 반응합니다. 핵심 도구는 수식에 연결된 드롭다운 선택기입니다.

개념은 간단합니다. 하나의 셀에 지역이나 월과 같은 사용자의 선택 항목을 저장합니다. 대시보드의 모든 지표가 이 셀 하나를 참조합니다. 드롭다운을 변경하면 새 선택 항목을 기준으로 대시보드 전체가 다시 계산됩니다.

이 학습에서는 드롭다운을 만들고 합계, 개수, 필터링된 보기를 드롭다운에 연결합니다.

드롭다운은 데이터 유효성 검사에서 만듭니다. 선택기 셀(예: B1)을 선택한 다음 데이터, 데이터 유효성 검사를 차례로 열고 목록을 선택합니다.

원본으로 유효한 옵션이 있는 범위를 지정할 수 있습니다.

• 원본 범위: =Lists!A2:A6에 동부, 서부, 북부, 남부, 전체가 들어 있습니다.
• 또는 보조 열에서 =SORT(UNIQUE(Sales!A2:A500))와 같은 수식으로 목록을 생성한 다음 유효성 검사를 해당 범위에 연결할 수 있습니다.

이제 B1에 작은 화살표가 표시되고 목록의 값만 입력할 수 있습니다. 이 셀 하나가 대시보드의 제어 장치가 됩니다.

하나의 셀을 제어 셀로 정합니다. 예를 들어 B1입니다. 모든 수식이 이 셀을 참조합니다. 모든 상호작용을 하나의 셀을 통해 연결하면 대시보드를 쉽게 이해하고 관리할 수 있습니다.

첫 번째로 연결할 지표는 선택한 지역의 총매출입니다. B1에 선택 항목이 들어 있을 때 다음과 같이 합니다.

B1에서 서부를 선택하면 서부의 총매출이 반환됩니다. 북부를 선택하면 즉시 업데이트됩니다. 수식 하나로 무한한 보기를 만들 수 있습니다.

선택한 지역의 주문 수를 나타내는 두 번째 연결 수치를 추가합니다. COUNTIF는 같은 선택 셀을 읽습니다.

이 수식을 총합 옆에 배치합니다.

총합과 개수가 모두 B1을 참조하므로 항상 같은 선택 항목을 설명합니다. 모든 대시보드 항목이 제어 셀을 읽도록 만들면 서로 다른 내용을 표시하지 않습니다.

대시보드에는 보통 전체를 볼 방법이 필요합니다. 목록에 전체 옵션이 포함되어 있다면 수식에서 이를 처리해야 합니다. 그렇지 않으면 SUMIF가 전체라는 이름의 실제 지역을 찾기 때문입니다.

IF를 사용해 전체 선택에 따라 분기합니다.

B1이 전체이면 전체 합계를 얻고, 그렇지 않으면 필터링된 합계를 얻습니다. 이 패턴을 사용하면 조건 논리를 깨뜨리지 않고 전체 보기를 제공할 수 있습니다.

단일 수치를 넘어 드롭다운으로 세부 행 전체가 있는 표를 제어할 수도 있습니다. FILTER는 선택 항목을 읽고 일치하는 행을 펼쳐 표시합니다.

지표 아래에 다음을 입력합니다.

동부를 선택하면 모든 동부 행이 표시되고, 서부를 선택하면 해당 영역이 다시 작성됩니다. 세 번째 인수는 일치하는 항목이 없을 때 깔끔한 메시지를 표시하므로 빈 선택으로 인해 대시보드에 보기 흉한 오류가 표시되지 않습니다.

실제 대시보드에는 B1의 지역, B2의 분기처럼 선택기가 여러 개 있는 경우가 많습니다. 하나의 수식에서 두 항목을 모두 읽어 결합합니다.

SUMIFS를 사용해 두 선택 항목을 동시에 반영합니다.

이제 독자는 지역과 분기로 보기를 함께 좁힐 수 있습니다. 드롭다운을 하나 추가할 때마다 각자의 제어 셀에서 가져온 조건 쌍이 하나씩 더해집니다.

완성도 높은 대시보드는 현재 선택 항목을 제목에 표시하여 독자가 무엇을 보고 있는지 알 수 있게 합니다. 선택 셀의 값을 텍스트와 결합해 동적 제목을 만듭니다.

제목 셀에 다음을 입력합니다.

B1이 북부이면 제목은 북부 매출 요약으로 표시됩니다. & 연산자는 텍스트와 셀 값을 결합합니다. 이처럼 작은 요소만으로도 대화형 대시보드가 완성되고 별도 설명 없이도 이해하기 쉬워집니다.

데이터에 새 지역이 나타나면 고정 입력된 드롭다운 목록은 오래된 상태가 됩니다. 결과가 펼쳐지는 수식을 유효성 검사에 제공하여 목록을 최신 상태로 유지합니다.

보조 영역에 다음을 입력합니다.

그런 다음 해시 참조를 사용해 해당 결과 범위를 데이터 유효성 검사에 지정합니다. 예를 들어 =Lists!A2#와 같이 지정합니다. 새 지역이 추가되면 목록이 늘어나고 드롭다운에서 자동으로 제공됩니다. 수동으로 수정하지 않아도 제어 셀이 정확하게 유지됩니다.

대시보드에 차트가 있다면 차트 제목도 드롭다운을 따라가도록 만들 수 있습니다. 차트 제목이 셀을 참조하도록 설정할 수 있으므로 선택 항목을 읽는 수식 셀을 제목에 지정합니다.

남는 셀에 동적인 제목 문구를 만듭니다.

그런 다음 차트에서 제목이 이 셀을 참조하도록 설정합니다. 이제 B1을 동부에서 서부로 바꾸면 차트 제목도 바뀝니다. 수치와 시각 자료를 포함한 모든 표시 요소가 하나의 제어 셀을 따라갑니다.

몇 가지 원칙을 지키면 대화형 대시보드를 신뢰할 수 있게 만들 수 있습니다.

• 선택 항목마다 하나의 제어 셀: 각 선택기를 하나의 명확한 레이블이 붙은 셀을 통해 연결합니다.
• 읽고 복제하지 않기: 모든 항목이 제어 셀을 참조하므로 서로 같은 내용을 표시합니다.
• 전체와 빈 결과에 대비하기: 전체 항목과 일치하는 항목이 없는 경우를 자연스럽게 처리합니다.

이러한 습관을 들이면 독자가 드롭다운 하나를 변경할 때 합계, 개수, 표, 제목이 하나의 살아 있는 보고서처럼 함께 업데이트됩니다.

전체 보기가 계속 작동하도록 유지하는 방법을 이해했는지 확인합니다.

정적 보고서를 대화형 대시보드로 바꾸었습니다.

• 데이터 유효성 검사를 사용하면 B1과 같은 하나의 제어 셀에 드롭다운이 생성됩니다.
• SUMIF와 COUNTIF로 지표를 해당 선택 항목에 연결하고, IF 분기를 사용해 전체 옵션을 처리했습니다.
• FILTER로 같은 제어 셀을 기준으로 세부 표를 만들고, SUMIFS로 두 개의 드롭다운을 결합했습니다.
• 텍스트를 결합한 제목과 결과가 펼쳐지는 유효성 검사 목록으로 대시보드가 별도 설명 없이도 이해되고 최신 상태를 유지하도록 했습니다.

다음으로 핵심 KPI 카드와 가장 중요한 수치를 표시하는 조건부 강조 기능을 만들어 보겠습니다.