엑셀 대시보드 만들기: 원본만 붙여넣으면 주간 리포트가 스스로 갱신됩니다

주간 리포트는 만드는 데 시간이 걸리는 게 아닙니다.

매주 다시 만드는 것이 문제입니다. 원본을 붙여넣고, 피벗을 새로 돌리고, 지난주 표를 복사해 숫자를 갈아 끼우는 일이 매주 돌아옵니다.

이 글은 엑셀 데이터 정리 여덟 단계 가운데 일곱 번째 단계입니다.

엑셀 대시보드 만들기: 원본 표를 먼저 고정합니다

엑셀 대시보드 제작방법을 찾으면 화면 꾸미기부터 나옵니다. 순서는 반대입니다. 화면부터 만들면 다음 주에 깨집니다.

원본 데이터를 범위로 두지 말고 표로 바꿉니다. 데이터를 선택하고 Ctrl+T를 누르면 표가 되고 이름이 붙습니다. 이름을 상담원본처럼 바꿔둡니다.

표로 바꾸면 두 가지가 달라집니다. 행이 늘어도 피벗이 그 범위를 자동으로 따라가고, 수식이 열 이름으로 참조되어 열 순서가 바뀌어도 안 깨집니다. 엑셀 대시보드 만들기에서 이 한 단계를 건너뛰면 매주 범위를 다시 잡게 됩니다.

원본 시트에는 아무것도 그리지 않습니다. 표 하나만 둡니다.

엑셀 대시보드 제작방법: 피벗을 계산 층으로 씁니다

새 시트를 하나 만들어 계산이라고 이름 붙이고, 여기에 피벗을 필요한 만큼 놓습니다. 유형별 건수, 주차별 건수, 상담사별 처리시간처럼 화면에 들어갈 숫자마다 하나씩입니다.

그리고 화면용 시트를 따로 만듭니다. 화면에서는 피벗을 직접 보여주지 않고 GETPIVOTDATA나 셀 참조로 값을 끌어옵니다. 이렇게 나누면 화면 배치를 바꿔도 계산이 안 흔들리고, 계산을 고쳐도 화면이 안 무너집니다.

엑셀 대시보드 제작방법에서 이 두 층을 나누는 것이 핵심입니다. 슬라이서는 화면 시트에 두고 보고서 연결로 피벗 여러 개에 한 번에 겁니다. 기간을 바꾸면 화면 전체가 같이 움직입니다.

엑셀 대시보드 양식에 꼭 들어가는 네 가지

숫자를 많이 넣을수록 안 읽힙니다. 상담 리포트에서 실제로 쓰이는 항목은 넷으로 좁혀집니다.

① 이번 주 건수와 지난주 대비 증감. 절대값만 있으면 많은지 적은지 판단이 안 됩니다. ② 유형별 상위 다섯. 전부 보여주면 아무것도 안 보입니다. 나머지는 기타로 묶습니다. ③ 평균 처리시간과 가장 오래 걸린 유형. 건수가 많은 유형과 오래 걸리는 유형은 대개 다르고 처방도 다릅니다. ④ 미처리 건수. 이게 있어야 리포트가 다음 행동으로 이어집니다.

날짜를 반드시 적습니다. 언제 기준인지 없는 리포트는 나중에 다시 못 씁니다.

엑셀 대시보드 템플릿은 그대로 쓰면 숫자가 안 맞습니다

내려받은 템플릿은 대개 판매나 재고를 전제로 만들어져 있습니다. 열 이름이 다르고 계산식이 그 전제에 맞춰져 있습니다.

그대로 쓰면 세 가지가 어긋납니다. 우리 유형 목록이 템플릿의 분류와 다르고, 처리시간처럼 우리한테 중요한 열이 아예 없고, 합계가 어떤 기준으로 잡히는지 안 보입니다.

템플릿에서 가져올 것은 배치와 서식입니다. 계산은 우리 원본 표를 참조해 새로 겁니다. 엑셀 대시보드 템플릿을 계산까지 그대로 쓰면 숫자를 설명할 수 없게 되고, 설명 못 하는 리포트는 회의에서 한 번 물어보면 끝납니다.

유형 목록이 정리돼 있어야 이 작업이 짧아집니다. 태그 목록을 먼저 정하는 순서를 같이 보시면 좋습니다.

갱신이 사람 손에 남으면 대시보드는 멈춥니다

여기까지 만들면 매주 할 일이 하나로 줄어듭니다. 원본 표에 이번 주 데이터를 붙여넣고 새로 고침을 누르면 끝납니다.

그런데 그 한 가지가 사람 손에 남아 있는 한, 담당자가 휴가를 가면 그 주 리포트는 없습니다. 실제로 대시보드는 화면 때문에 죽지 않고 이 붙여넣기에서 멈춥니다.

파워 쿼리로 폴더를 읽게 해두면 파일을 폴더에 넣는 것으로 바뀌고, 여기서 더 가면 원본이 들어오는 순간 갱신되게 만들 수 있습니다. 그때부터 사람은 보기만 합니다. 이 단계는 표 한 장으로는 잘 안 되고, 저희가 다루는 방식도 원본을 받아 두는 곳과 화면을 나누고 그 사이를 정해진 순서로 잇는 것입니다.

같은 형식으로 쌓여야 다른 표와 붙습니다

월별 손익을 매달 새로 그리지 않듯, 주간 리포트도 화면은 한 번만 만들고 데이터만 갈아 끼웁니다.

그리고 이 화면이 몇 달 쌓이면 숫자가 다른 표와 붙습니다. 반품 사유별 건수는 반품률로, 불량 문의 추이는 품질 비용으로, 미처리 건수는 인력 계획으로 넘어갑니다. 매주 손으로 만든 표는 이렇게 쌓이지 않습니다. 형식이 조금씩 다르기 때문입니다.

자주 받는 질문

Q. 피벗을 화면에 바로 놓으면 안 되나요

A. 놓아도 됩니다. 다만 필드를 하나 바꾸면 표 크기가 변해서 옆에 둔 것들이 밀립니다. 계산 시트와 화면 시트를 나누면 이 문제가 없어지고, 배치를 자유롭게 잡을 수 있습니다.

Q. 엑셀 대시보드 양식을 몇 장으로 만드나요

A. 한 장으로 끝냅니다. 스크롤 없이 보이는 만큼이 리포트이고, 더 필요한 사람은 계산 시트를 봅니다. 여러 장이 되면 아무도 끝까지 안 봅니다.

Q. 매주 원본을 어디에 붙여넣나요

A. 표 맨 아래 마지막 행 다음에 이어 붙입니다. 표는 범위를 자동으로 넓히므로 피벗은 새로 고침만 하면 됩니다. 주차 열을 따로 두면 나중에 기간별로 자르기 쉽습니다.

Q. 데이터가 만 줄이 넘으면 느려집니다

A. 피벗 개수를 줄이고 계산 시트에서 필요한 값만 남기면 상당히 나아집니다. 그래도 느리면 원본을 엑셀 밖에 두고 집계만 가져오는 쪽으로 옮길 시점입니다. 행이 계속 늘어나는 데이터는 표 한 장으로 오래 버티기 어렵습니다.

화면을 예쁘게 만드는 데 하루를 쓰는 것보다, 원본이 들어오면 저절로 갱신되게 해두는 편이 오래 갑니다.