엑셀 대화형 대시보드 만들기

만든 사람에게는 잘 맞는 대시보드도 정작 그걸 볼 사람에게는 쓸모없을 때가 많아요. 임원은 회사가 목표대로 가고 있는지 5초 안에 알고 싶어 하고, 분석가는 어느 지역이 왜 뒤처졌는지 알고 싶어 해요. 이 글에서는 지역별 매출 모델 하나를 두 대상에 맞춘 두 가지 보기로 바꿔 볼 거예요. 헷갈릴 일 없는 컨트롤, 맨 위에 놓인 예외, 합계에서 원본 행까지 이어지는 경로, 깔끔하게 인쇄되는 레이아웃까지 함께 만들어요.

모델은 하나, 보기는 두 개로 계획하기

두 보기는 같은 숫자를 읽어요. 달라지는 건 보여 주는 세부 정보의 양뿐이에요. 무언가를 만들기 전에 대상마다 무엇이 필요한지 적어 보세요.

보기마다 시트를 하나씩 두고(예: Executive, Analyst), 두 시트 모두 같은 계산 시트나 같은 피벗 테이블(PivotTable)에서 값을 읽게 하세요. 한 보기의 숫자를 다른 보기로 복사해 붙이면 안 돼요. 임원용 타일은 339,000인데 분석가용 표를 더하면 338,000이라면, 아무도 둘 중 어느 것도 믿지 않아요.

이 글의 예시는 네 지역의 실적과 목표예요. D열은 =B2/C2를 아래로 복사한 것이고, E열은 상태 레이블이에요(아래에서 자세히 다뤄요).

fx

	A	B	C	D	E	F	G	H
1	지역	실적	목표	목표 대비	상태	지표	값	90% 미만
2	동부	92,000	100,000	92%	정상	총매출
3	서부	81,000	100,000	81%	목표 미달	목표 달성률
4	중부	96,000	95,000	101.1%	정상	정상 지역 수
5	남부	70,000	90,000	77.8%	목표 미달	가장 뒤처진 지역

임원용 타일 만들기

타일 하나는 수식 하나와 분명한 레이블 하나로 된 셀 하나예요. 수식은 계산 시트에 두고 타일은 그 셀을 가리키게 하세요. 그래야 타일 자체에 로직이 숨어 있지 않아요.

1. 총매출을 구하세요

   G2에 네 지역의 실적을 더하세요. 결과는 339,000이에요.

   =SUM(B2:B5)복사

2. 전체 목표 달성률을 구하세요

   G3에 실적 합계를 목표 합계로 나누세요. 결과는 88.1%예요(385,000 중 339,000).

   =SUM(B2:B5)/SUM(C2:C5)복사

   D열의 평균을 내면 안 돼요. =AVERAGE(D2:D5)는 여기서 88.0%가 나오는데, 비슷한 건 우연일 뿐이에요. 평균은 목표가 작은 지역과 큰 지역에 같은 비중을 줘요. 합계끼리 나눠야 지역마다 규모만큼 비중이 반영되고, 그게 바로 "회사 전체가 목표의 88%를 달성했다"는 말의 뜻이에요.

3. 정상 지역 수를 세세요

   G4에 목표의 90% 이상을 달성한 지역 수를 세세요. 결과는 2예요(동부와 중부).

   =COUNTIF(D2:D5,">=0.9")복사

   타일에는 =G4&"/"&COUNTA(A2:A5)처럼 "2/4" 형태로 전체 지역 수와 함께 보여 주세요. 그러면 읽는 사람이 지역이 모두 몇 개인지 따로 알 필요가 없어요.

4. 가장 뒤처진 지역을 찾으세요

   G5에 목표 대비 비율이 가장 낮은 지역의 이름을 표시하세요. 결과는 남부(77.8%)예요.

   =XLOOKUP(MIN(D2:D5),D2:D5,A2:A5)복사

G3fx=SUM(B2:B5)/SUM(C2:C5)

	A	B	C	D	E	F	G	H
1	지역	실적	목표	목표 대비	상태	지표	값	90% 미만
2	동부	92,000	100,000	92%	정상	총매출	339,000
3	서부	81,000	100,000	81%	목표 미달	목표 달성률	88.1%
4	중부	96,000	95,000	101.1%	정상	정상 지역 수	2
5	남부	70,000	90,000	77.8%	목표 미달	가장 뒤처진 지역	남부

KPI 셀을 Executive 시트의 타일로 만들려면 글꼴을 크게 하고, 위에 레이블을 달고, 주변에 여백을 넉넉히 두세요. 차트 제목도 같은 방식으로 셀에 연결할 수 있어요. 차트 제목을 클릭하고, 수식 입력줄을 클릭해 =를 입력한 다음, 연결할 셀을 클릭하고 Enter를 누르세요. 그러면 데이터가 바뀔 때 제목도 함께 바뀌어요.

예외부터 보여 주기

전체 표를 주면 읽는 사람이 문제를 직접 찾아야 해요. 예외 목록은 주의가 필요한 행만, 가장 심각한 것부터 바로 보여 줘요. 위 데이터에서는 두 지역이 목표의 90%에 못 미쳐요.

=SORTBY(FILTER(A2:A5,D2:D5<0.9),FILTER(D2:D5,D2:D5<0.9))복사

FILTER가 90% 미만인 지역만 남기고 SORTBY가 목표 대비 비율 순서로 정렬해요. 그래서 결과가 남부, 서부 순서로 분산돼요.

H2fx=SORTBY(FILTER(A2:A5,D2:D5<0.9),FILTER(D2:D5,D2:D5<0.9))

	A	B	C	D	E	F	G	H
1	지역	실적	목표	목표 대비	상태	지표	값	90% 미만
2	동부	92,000	100,000	92%	정상	총매출		남부
3	서부	81,000	100,000	81%	목표 미달	목표 달성률		서부
4	중부	96,000	95,000	101.1%	정상	정상 지역 수
5	남부	70,000	90,000	77.8%	목표 미달	가장 뒤처진 지역

Analyst 시트에서는 이름만이 아니라 행 전체를 보여 주세요. 다음 수식은 예외마다 지역, 실적, 목표, 비율을 돌려주고 네 번째 열 기준으로 정렬해요.

=FILTER(SORT(A2:D5,4),SORT(D2:D5)<0.9,"예외 없음")복사

FILTER의 세 번째 인수는 조건에 맞는 행이 없을 때 보여 줄 값이에요. 이 인수가 없으면 예외가 없는 달에 #CALC!가 표시돼서, 좋은 소식이 고장 난 대시보드처럼 보여요. 이 수식이 먼저 정렬하고 나중에 필터하는 것도 같은 이유예요. 순서를 반대로 감싸면 SORT(…,4)가 네 번째 열이 없는 한 칸짜리 "예외 없음" 메시지를 받아서 #VALUE!를 돌려줘요.

헷갈릴 일 없는 컨트롤 추가하기

컨트롤은 대시보드에 보이는 내용을 바꾸는 슬라이서(Slicer), 시간 표시 막대(Timeline), 드롭다운 목록 같은 것들이에요. 세 가지 규칙을 지키면 쓰기 쉬워져요.

• 한곳에 모으기. 모든 컨트롤을 시트마다 같은 띠 영역에 두세요. 보통 맨 위 몇 행이나 왼쪽의 좁은 열이에요.
• 데이터와 다르게 보이기. 컨트롤이 데이터처럼 보이면 안 돼요. 그 영역에 옅은 배경색을 주고 숫자는 두지 마세요.
• 선택 상태가 보이게 하기. 클릭하거나 마우스를 올려 보지 않아도 무엇이 선택되어 있는지 알 수 있어야 해요.

피벗 테이블에는 슬라이서

보기를 피벗 테이블로 만들었다면 슬라이서가 세 가지를 모두 해결해 줘요. 선택된 단추는 채워져 있고 선택되지 않은 단추는 흐리게 보이며, 필터가 걸려 있으면 슬라이서 머리글의 필터 지우기(Clear Filter) 단추가 활성화돼요.

1. 슬라이서를 삽입하세요

   피벗 테이블 안을 클릭하고 피벗 테이블 분석› then 필터› then 슬라이서 삽입을 고르세요. 지역 필드(예: 지역)에 체크하고 확인을 누르세요. 날짜 필드라면 피벗 테이블 분석› then 필터› then 시간 표시 막대 삽입으로 월, 분기, 연도를 고르는 시간 표시 막대를 넣을 수 있어요.

2. 필터할 모든 피벗 테이블에 연결하세요

   슬라이서를 선택하고 슬라이서 탭에서 보고서 연결(Report Connections)을 클릭하세요. 이 슬라이서에 반응해야 하는 피벗 테이블에 모두 체크하고 확인을 누르세요. 슬라이서 하나가 타일과 차트를 함께 움직여야 보기가 일관돼요. 그래야 차트만 필터해 놓고 그 위의 타일은 여전히 모든 지역을 보여 주는 상황이 생기지 않아요.

3. 스타일을 고르고 배치하세요

   슬라이서 탭에서 선택된 단추가 확실히 눈에 띄는 슬라이서 스타일을 고른 다음, 슬라이서를 컨트롤 영역으로 끌어다 놓으세요.

수식 기반 대시보드에는 드롭다운 선택기

보기를 수식으로 만들었다면 셀 하나에 만든 드롭다운 목록이 컨트롤 역할을 해요. 셀을 선택하고 데이터› then 데이터 도구› then 데이터 유효성 검사를 열어 데이터 유효성 검사(Data Validation) 대화 상자의 제한 대상에서 목록을 고르고 원본에 지역 이름 범위를 지정한 뒤 확인을 누르세요. 그러면 보기의 모든 수식이 그 셀 하나를 읽어요. 지역 표가 Regions라는 시트에 있고 드롭다운이 B1에 있다면 다음과 같아요.

=XLOOKUP($B$1,Regions!$A$2:$A$5,Regions!$B$2:$B$5)복사

B1fx

	A	B
1	지역	서부
2	실적	81,000
3	목표	100,000
4	목표 대비	81%
5	상태	목표 미달

드롭다운 셀은 값을 보여 주긴 하지만 생김새는 다른 셀과 똑같아요. 상태가 분명히 보이도록 컨트롤 영역과 같은 배경색을 주고, 보기 제목에 선택값을 다시 표시하세요. 예를 들면 ="지역별 매출: "&B1처럼요.

색에만 기대지 않고 상태 표시하기

남성 약 12명 중 1명은 색각 이상이 있고, 그중 가장 흔한 게 빨강과 초록을 구분하기 어려운 경우예요. "뒤처짐"을 셀을 빨갛게 칠하는 것만으로 표시하는 대시보드는 이런 사람들에게 정보를 전하지 못하고, 흑백으로 인쇄하면 모두에게 전하지 못해요.

모든 색에 색 없이도 읽히는 단서를 짝지어 주세요.

• 텍스트 레이블. 예시의 E열은 아래로 복사한 수식이에요. =IF(D2>=0.9,"정상","목표 미달")
• 모양이 바뀌는 아이콘. 조건부 서식(Conditional Formatting)의 홈› then 스타일› then 조건부 서식› then 아이콘 집합에서 색만 바뀌는 아이콘이 아니라 체크 표시, 느낌표, X처럼 아이콘마다 모양이 다른 집합을 고르세요.
• 위치. 예외 목록은 지역을 맨 위에 올리는 것만으로 "뒤처졌다"는 걸 알려 줘요.

숫자마다 드릴 경로 만들기

설명할 수 없는 KPI는 논쟁거리가 돼요. 요약 숫자마다 그 숫자를 이루는 행으로 이어지는 길이 있어야 해요.

• 피벗 테이블 드릴다운. 피벗 테이블의 값 셀을 두 번 클릭하세요. 엑셀이 새 시트를 만들고 그 숫자를 이루는 원본 행을 나열해 줘요. 다 봤으면 그 시트는 지우세요. 실시간 연결이 아니라 복사본이에요.
• 보기 사이의 링크. 타일마다 Analyst 시트의 해당 부분으로 이동하는 링크를 두고, 그쪽에는 "요약으로 돌아가기" 링크를 두세요. 삽입› then 링크› then 링크(Ctrl+K)에서 현재 문서(Place in This Document)를 고르거나, 수식을 쓰세요.

=HYPERLINK("#Analyst!A1","예외 보기")복사

#은 대상이 이 통합 문서 안에 있다는 뜻이에요. 시트 이름에 공백이 있으면 작은따옴표로 감싸세요. "#'Analyst view'!A1"처럼요.

인쇄 모드와 발표 모드 준비하기

컨트롤은 화면에서는 유용하지만 종이 위나 회의 중 화면 공유에서는 방해만 돼요. 두 경우를 모두 대비하세요.

1. 컨트롤을 인쇄 영역 밖에 두세요

   컨트롤 영역을 맨 위 몇 행에 두고, 그 아래의 타일과 차트만 선택한 다음 페이지 레이아웃› then 페이지 설정› then 인쇄 영역› then 인쇄 영역 설정을 고르세요. 이렇게 지정한 인쇄 영역(Print Area)만 인쇄돼요. 컨트롤은 화면에만 남고 인쇄되지 않아요.

2. 현재 선택을 인쇄되는 제목에 넣으세요

   인쇄한 종이에는 무엇으로 필터했는지 보여 줄 슬라이서가 없으니 제목이 대신 말해 줘야 해요. 앞에서 본 것 같은 수식을 인쇄 영역 안의 셀에 넣으세요.

3. 발표할 때는 눈금선을 숨기세요

   보기 탭의 표시 그룹에서 눈금선과 머리글의 체크를 해제하세요. 시트가 스프레드시트가 아니라 보고서처럼 보여요.

4. 결과를 확인하세요

   Ctrl+P를 눌러 다른 사람보다 먼저 미리 보기를 확인하세요. 타일이 여러 페이지로 나뉘면 설정에서 배율과 방향을 조정하세요.

자주 하는 실수

• 따로 계산하는 두 보기. 임원용 타일과 분석가용 표가 각자 수식을 가지고 있으면 언젠가 숫자가 어긋나요. 둘 다 같은 셀이나 같은 피벗 테이블을 읽어야 해요.
• 비율의 평균 내기. =AVERAGE(D2:D5)는 지역마다 규모가 다르다는 걸 무시해요. 합계끼리 나누세요.
• 페이지 절반만 필터하는 슬라이서. 차트에는 연결되어 있지만 타일에는 연결되지 않은 슬라이서는 서로 다른 두 가지 사실을 보여 줘요. 피벗 테이블을 추가할 때마다 보고서 연결을 확인하세요.
• 빨강과 초록만으로 표시하기. 레이블이나 모양이 바뀌는 아이콘을 함께 쓰세요.
• 고장 난 것처럼 보이는 빈 FILTER. FILTER에는 항상 세 번째 인수를 주세요.
• 컨트롤까지 인쇄하기. 인쇄 영역을 지정해서 슬라이서와 드롭다운은 화면에만 남기세요.

연습

연습

쓰고 있는 대시보드에 두 번째 보기 설계하기

1. 직접 만들었거나 자주 쓰는 대시보드나 요약 시트를 하나 고르고, 지금은 누구를 위한 것인지 정하세요.
2. 다른 대상을 위한 시트를 하나 추가하세요. 지금 세부 화면이라면 임원용 요약을, 요약이라면 분석가용 보기를 만드세요.
3. 두 시트가 복사한 값 없이 같은 계산 셀이나 피벗 테이블을 읽게 하세요.
4. "보고할 내용 없음" 메시지까지 넣은 FILTER로 예외 우선 목록을 추가하세요.
5. 컨트롤 영역에 컨트롤(슬라이서나 드롭다운)을 하나 추가하고, 보기 제목에 그 선택값을 다시 표시하세요.
6. 컨트롤이 빠지도록 인쇄 영역을 지정하고 인쇄 미리 보기를 확인하세요.

아래에서 임원용 KPI를 만들어 보세요. 문제 하나가 타일 하나예요.

직접 해 보기4개 중 0개 완료

1. 1.G2에 네 지역의 실적을 모두 더해 보세요.힌트 보기
2. 2.G3에 실적 합계를 목표 합계로 나눠 전체 목표 달성률을 구해 보세요.힌트 보기
3. 3.G4에 목표의 90% 이상을 달성한 지역 수를 세어 보세요.힌트 보기
4. 4.G5에 목표 대비 비율이 가장 낮은 지역의 이름을 표시해 보세요.힌트 보기

G2fx수식 입력 셀 G2Enter ↵

점선으로 표시된 셀을 클릭하고, 입력줄에 수식을 쓴 다음 Enter를 누르세요.

	A	B	C	D	E	F	G	H
1	지역	실적	목표	목표 대비	상태	지표	값	90% 미만
2	동부	92,000	100,000	92%	정상	총매출
3	서부	81,000	100,000	81%	목표 미달	목표 달성률
4	중부	96,000	95,000	101.1%	정상	정상 지역 수
5	남부	70,000	90,000	77.8%	목표 미달	가장 뒤처진 지역

실력 점검

• 같은 데이터에 대해 임원용 보기와 분석가용 보기가 각각 무엇을 보여 줘야 하는지 설명할 수 있나요?
• 클릭하거나 마우스를 올리지 않아도 선택 상태가 분명히 보이는 컨트롤을 만들 수 있나요?
• 주의가 필요한 행만, 가장 심각한 것부터 나열하는 FILTER 수식을 쓸 수 있나요?
• 색각 이상이 있는 사람이나 흑백 인쇄물에서도 읽히는 방식으로 상태를 표시할 수 있나요?
• 대화형 컨트롤이 빠진 인쇄 레이아웃을 설정할 수 있나요?