엑셀로 마케팅 보고서 양식 만들기 - 대시보드, 피벗테이블 자동 업데이트

아래 이미지는 제가 1시간 만에 임의로 작성한 수치대로 만든 엑셀 마케팅 보고서입니다. 데이터를 수정하면 자동으로 그래프(대시보드)와 피벗테이블에 적용되며 월 또는 주 단위 보고서를 제출할 때 용이하게 쓸 수 있습니다.

간단하지만 효과적인 보고서를 빠르게 만들기 원하시는 분들을 위해 만들었으니 쉽게 따라하실 수 있을 겁니다.

1. 데이터 시트 만들기(Sheet 1)

일단 보고서의 내용을 구성할 데이터가 필요합니다. 광고 매체별로 성과를 집계하여 간략하게 정리해주세요. 보통은 월/주 단위, 접속 경로, 각 매체별 구좌 등 더 많은 기준이 필요하지만 지금은 빨리 보고서를 만드는 게 목적이니까요, 간단하게 만들고 넘어가겠습니다.

2. 피벗테이블 만들기(Sheet 2)

피벗테이블이란, 데이터 시트에서 필요한 항목을 선택하여 합계를 내고, 정리해주는 기능입니다. 저는 굉장히 대충 던져서 만들었지만 깔끔하게 정렬하고 필요한 요소만 넣어서 만들수록 관리가 편해집니다.

!! 꿀팁 !!

생성된 피벗테이블을 클릭하면 아래 사진처럼 어떤 값을 표시할지 정할 수 있습니다. 보고서에 표시되기 원하는 구성을 선택하세요.

여러 가지의 그래프와 표가 들어가야 마지막에 만들 대시보드에서 한 눈에 볼 수 있는 정보가 많아지기 때문에, 피벗테이블은 3개 이상 만들어두는 것이 좋습니다.

3. 대시보드 만들기

만들어둔 피벗테이블을 드래그해서 선택하고, 피벗 차트를 누르면 원하는 스타일의 그래프를 생성할 수 있습니다. 아래의 예시 이미지처럼 각 매체, 매출 금액이 값의 기준이 되었습니다.

그래프의 디자인과 값 표시 방식도 바꿀 수 있는데요, 그래프를 선택 후 상단의 디자인 탭을 누르면 '차트 요소 추가'와 '차트 스타일'을 선택할 수 있습니다.

4. 대시보드 정리하기

요약되길 원하는 정보가 그래프로 표현되었다면, 새로운 시트(Sheet 3)를 생성하여 정리하면 됩니다.

!! 꿀팁 !!

총 광고 매출 자동으로 업데이트 하는 법
1. 데이터 시트(Sheet 1)에 매체별 매출 총합을 계산해놓기 - 예시 : (Sum=B:B)
2. 대시보드 시트로 돌아와서 텍스트 박스 생성 후, Sheet 1의 매출 총합 셀을 입력하기 - 예시 : ='Sheet 1'!F13
수익률(ROAS) 자동으로 업데이트 하는 법
1. 데이터 시트(Sheet 1)에 광고 매출과 총 비용을 계산해두었다면, =총 매출/총 비용 수식 입력 후 해당 셀의 서식을 백분율로 변경하기
2. 매출 연동하는 방법과 동일하게 대시보드 시트에 Sheet 1의 ROAS 셀을 입력하기 - 예시 : ='Sheet 1'!F15
엑셀 줄배경 없애는 법
1. 좌측 상단 설정-옵션-고급-이 워크시트의 표시 옵션-눈금선 표시 해제

5. 피벗테이블과 대시보드에 자동 업데이트 기능 적용하기

지금까지의 방식대로 대시보드 보고서를 제작했을 때의 문제점은, 데이터가 수정될 때마다 수동 업데이트를 해줘야 한다는 점입니다. 이것을 간단한 입력 한 번으로 개선할 수 있습니다.

데이터 시트와 피벗 데이터, 대시보드를 각각 다른 시트에 생성한 경우만 이 방법을 적용할 수 있습니다.

Alt + F11 버튼으로 엑셀 개발 도구에 접속
피벗데이터 시트 더블 클릭(아래 사진에서는 Sheet 2로 표시)
아래 코드를 그대로 입력해 주세요.
Dim pvTbl As PivotTable

Application.EnableEvents = False
For Each pvTbl In Me.PivotTables
pvTbl.RefreshTable
Next
Application.EnableEvents = True

이제 X 버튼으로 개발 창을 종료하고 대시보드 화면으로 돌아오면, 데이터를 수정할 때마다 자동으로 업데이트 되는 것을 확인하실 수 있습니다.

주의사항

파일을 저장하실 때 'Excel 매크로 사용 통합 문서' 형식으로 저장해야 자동 업데이트 기능이 유지됩니다.

[추천 글]

쿠팡 애즈 광고 1주일 집행 후기_광고 수익률 2,981%

네이버에 치중되어 있던 광고 예산을 쿠팡에도 분배하기로 결정하면서, 4일 간 쿠팡 애즈를 집행했습니다. 3개의 그룹에 각 일 예산 3만원을 배정했고 광고 수익률이 어떻게 나오는지 보기로 했

cksl9508.tistory.com

필명록