이번 글에서는 내용이 돋보이고 작성 및 관리하기 쉬운 프로다운 엑셀 보고서를 만드는 방법을 알아보겠습니다.
실습파일: 프로다운엑셀보고서만들기.xlsx
샘플 보고서
다음과 같은 상반기 판매이력 데이터를 기반으로 ‘실적요약 보고서’를 만들어 보겠습니다. 보고의 목적은 상반기 판매실적을 월별로 보여주되, 전체, 대리점별, 대리점에서도 품목별로 나누어 보여주는 것입니다.
<판매 이력>
<실적요약 보고서>
좋은 보고서를 만드는 원칙
엑셀의 프로들은 이 보고서가 제대로 작성되어 보고의 목적을 달성하고 다음 번 보고서 작성 시에도 손쉽게 작성하기 위해서 일반적으로 다음과 같은 원칙으로 작업합니다.
1. 보고의 목적을 명확히 정의한다.
보고의 목적을 명확히 정의한다는 것은 '이 보고서를 왜 작성하는지', '이 보고서를 통해 어떠한 정보를 전달할 것인지'에 대한 답을 구하는 것과 같습니다. 예시에서는 상반기 판매실적 추이를 보여주되, 전체, 대리점 유형별로 보여주고, 가맹대리점과 직영대리점 간의 판매실적 차이도 보여주는 것이 목적입니다. 보고의 목적이 정의되면 보고의 목적에 맞는 스토리라인을 구성하고 스토리라인대로 보고서의 구조를 디자인합니다.
2. 읽기 쉽고 보기 좋은 보고서를 디자인한다.
셀의 눈금선을 표시하지 않음
엑셀에서 기본으로 표시되는 눈금선은 자료를 입력하고 처리할 때는 셀의 위치를 쉽게 알 수 있어서 사용은 편리하지만, 보고서 작성 시에는 표가 정돈되지 못한 느낌을 줍니다.
다음과 같이 ‘눈금선’이 보이지 않도록 설정하면 훨씬 깔끔한 보고서를 만들 수 있습니다.
세로선을 사용하지 않고 가로선만 사용
가로선만 사용하고 세로선은 사용하지 않습니다. 세로선이 없어도 항목 간 구분이 되고 훨씬 깔끔하고 내용도 알아보기 쉽습니다. 주요 수치의 폰트는 굵게 표시하여 주요 수치임을 알려줍니다.
<가로선과 세로선을 모두 사용>
<가로선만 사용>
특별한 미적 감각이 없다면 유채색보다 무채색, 여러 색상보다 적은 수의 색상을 사용함
색상은 호불호가 있어서 나에겐 보기 좋은 색이 다른 사람에게는 촌스럽게 느껴질 수 있습니다. 무채색을 사용하거나 색상을 최소한으로 사용하면 그런 위험이 줄어듭니다.
다음 예시에서는 차트의 계열 색상과 맞추어 쉽게 알아볼 수 있도록 유채색이 2개 사용되었습니다. 이렇듯 특별한 목적이 있을 때 강조하기 위해 유채색을 제한적으로 사용하는 것이 좋습니다.
3. 수식은 이해하기 쉽고 다음에 작성할 때도 어려움이 없어야 한다.
수식 작성 시 값을 직접 입력하지 않고 상대참조/절대참조/혼합참조의 특성을 잘 이해하여 수식을 사용합니다.
다음 예시와 같이 직영대리점의 2021년 1월 노트의 판매실적을 계산한다고 할 때, 표 안에 있는 타이틀(1월, 2월, 3월...), 구분 값(직영대리점, 기타, 노트..) 등을 상대참조/혼합참조/절대참조 방식을 이용하여 참조하여 수식을 작성합니다. 이렇게 수식을 작성하면 복사하여 다른 월, 다른 품목 유형에 붙여넣어도 수정 없이 사용할 수 있습니다.
=SUMIFS(판매이력!$H$2:$H$255,판매이력!$C$2:$C$255,집계!$B$6,판매이력!$D$2:$D$255,집계!$B8,판매이력!$A$2:$A$255,">="&DATE($H$2,C$4,1),판매이력!$A$2:$A$255,"<="&EOMONTH(DATE($H$2,C$4,1),0))
위에서 사용된 수식은 월별 실적집계를 할 때 많이 사용하는 유형입니다. SUMIFS 함수를 기본으로 사용하고 월별로 실적을 구분하기 위해 DATE 함수와 EOMONTH 함수를 사용했습니다.
4. 오류가 있는지 체크하는 로직을 포함한다.
보고서의 집계 수식을 만들 때 수식을 잘못 작성하면 원자료의 수치의 합계와 집계된 수치의 합계가 틀린 경우가 있습니다. 이럴 때 수치 오류가 있는지 체크할 수 있는 방법이 있어야 합니다.
다음 예시의 수식과 같이 원자료 수치의 전체 합계와 집계 시트의 총합계를 비교하여 맞으면 'Ok', 틀리면 '합계오류'라고 표시해 주면 즉시 오류 여부를 판단할 수 있습니다.
=IF(I5=SUM(판매이력!H2:H255),"Ok","합계오류")
위의 보고서는 수식으로 만들었지만 피벗 테이블을 이용하여 작성할 수도 있습니다. 피벗 테이블을 이용하면 슬라이서 같은 도구를 이용하여 동적으로 보고서를 보여줄 수 있습니다. 수식으로 만든 보고서는 원하는 형태로 가공할 수 있다는 장점이 있는 반면, 만들 때 많은 노력을 해야 합니다. 피벗 테이블도 원하는 디자인대로 만들 수 있지만, 수식으로 만드는 보고서만큼 원하는 형태로의 가공이 어렵습니다. 어떤 보고서를 선택할지는 보고의 목적, 보고서를 만들 때 사용할 수 있는 시간 등을 고려하여 결정하는 것이 좋습니다.
이상으로 간단한 보고서를 예로 들어서 설명했지만, 복잡한 보고서도 이러한 원칙을 크게 벗어나지 않습니다. 이러한 원칙만 지켜도 쓸모 있고 사용이 쉬운 프로다운 보고서를 만들 수 있습니다.
