데이터 정리 / 대시보드 꾸미기

실습가이드

이번 강의는 엑셀 대시보드 만들기 시리즈의 마지막 완성 강의입니다. 이전 강의에서 만든 기본 데이터와 차트를 토대로,

세 단계의 작업을 차례로 진행합니다. 이전 강의와 연계되는 내용이 많으므로, 이전 강의를 보지 못하신 분은 이전 강의를 반드시 확인하신 뒤에 이번 강의를 진행해주세요.

1. 대시보드 틀 만들기

상단 리본의 [삽입] – [도형] 메뉴에서 [둥근모서리] 도형을 추가합니다.

도형이 삽입되었으면 원하는 위치와 크기로 조절합니다. 도형을 선택한 뒤 상단의 [도형 서식]으로 이동하여 원하는 채우기 색상과 윤곽선 종류를 선택합니다.

각 항목별 제목으로 들어갈 도형을 우클릭한 뒤, [텍스트 편집]을 클릭하여 원하는 제목을 입력합니다.

[월별 수입/지출 요약] 항목 안에 들어갈 세부항목 도형을 추가합니다. 해당 도형에는 선택한 연도의 수입/지출 현황과 월별 수입/지출액을 전년도와 비교하여 표시합니다.

여러 개의 도형을 삽입한 뒤에는 도형들을 손쉽게 정렬하기 위해 [맞춤] 기능을 사용합니다. 키보드 Shift 키를 누른 채 마우스로 여러 개의 도형을 동시에 선택한 다음, 상단의 [도형 서식] – [맞춤]으로 이동하여 ‘가운데 맞춤’과 ‘중간 맞춤’을 적용하면 여러 도형을 깔끔하게 정렬할 수 있습니다.

테두리와 제목 삽입이 완료되었으면, 키보드 Shift 키를 누른 채로 삽입한 모든 도형을 동시에 선택합니다. 그 후 우클릭하여 [도형 서식]으로 이동한 뒤, 우측의 ‘도형 옵션’에서 십자가가 그려진 네모난 도형 버튼을 선택하고, [속성]에서 [변하지 않음]을 체크합니다.

같은 작업을 반복하여 아래 그림과 같이 대시보드의 테두리 생성을 마무리합니다.

2. 대시보드 백데이터 만들기

대시보드에 출력할 자료의 백데이터를 만들어 보겠습니다. 피벗테이블과 슬라이서를 활용해 사용자가 직접 연도와 월, 그리고 계정과목을 선택해 원하는 자료를 출력할 수 있도록 연동하는 방법은 이전 강의에서 자세히 설명드렸으니, 이전 강의를 참고해주세요.

이전 강의에서는 ‘월별 입출금 현황’과 ‘상세지출 현황’에 대한 차트와 슬라이서를 추가했습니다. 이번 강의에서는 ‘월별 수입/지출 요약’에 들어갈 세부 내용을 4개로 나누어 추가해 보겠습니다.

사용자가 연도를 선택하면 해당 연도에 따라 ‘XXXX년 총 수익’ 형태의 텍스트가 자동으로 연동되도록 수식을 작성합니다. ‘차트자료’ 시트의 B40셀에 아래 수식을 입력합니다. (B41셀과 B42셀에도 동일한 방식으로 ‘총 지출’과 ‘총 저축’ 텍스트를 만들어 줍니다.)

선택한 연도의 총 수입 합계는 ‘차트자료’ 시트의 B5:B16 범위 합계가 됩니다. 따라서 SUM 함수를 사용해 선택한 연도의 총 수입을 계산합니다. ‘차트자료’ 시트의 C40셀에 아래 수식을 입력합니다. (총 지출은 C5:C16 범위의 합계로 계산합니다.)

총 저축액은 ‘총수입 – 총지출’로 계산합니다. 따라서 C42셀에는 아래 수식을 입력합니다.

수식 입력을 모두 완료했으면 금액 셀의 사용자 지정 서식을 변경합니다. ‘차트자료’ 시트의 C40:C42 범위를 선택한 뒤, 키보드 Ctrl + 1 키를 눌러 사용자 지정 서식을 변경합니다. 사용자 지정 서식에 대한 자세한 내용은 아래 관련 포스트에서 확인할 수 있습니다.

사용자 지정 서식까지 변경을 완료하면, 아래와 같이 선택한 연도의 총 수익/지출/저축액 계산이 완료됩니다.

사용자가 월을 선택하면 선택된 월이 자동으로 표시되도록 수식을 입력합니다. ‘차트자료’ 시트의 B45셀에 아래 수식을 입력합니다. (B46, B47 셀에도 각각 ‘총 지출’과 ‘총 저축’으로 동일하게 수식을 입력합니다.)

선택한 월의 수입/지출 합계는 두 개의 연도에 대해 각각 계산해야 합니다. 첫 번째는 사용자가 선택한 해당 연도의 합계이고, 두 번째는 이전 연도의 합계입니다.

선택한 연도의 수입과 지출 합계는 이전 강의에서 미리 구해 두었으므로, 이번 강의에서는 전년도의 수입/지출 합계를 계산해 보겠습니다. ‘차트자료’ 시트의 D3셀에 아래 수식을 입력하면 사용자가 선택한 연도의 직전 연도가 계산됩니다.

‘차트자료’ 시트의 D4셀과 E4셀에는 각각 “전년도 수입”과 “전년도 지출”로 텍스트를 입력합니다.

이전 강의에서 작성한 SUMPRODUCT 함수 (‘차트자료’ 시트 B5셀) 에서 YEAR(연도) 조건이 되는 $B$3 셀의 셀 주소만 변경하면 이전 연도의 수입/지출 합계를 계산할 수 있습니다. 따라서 ‘차트자료’ 시트의 D5셀에 아래 수식을 입력합니다.

같은 방식으로 ‘차트자료’ 시트의 E6셀에는 아래 수식을 입력합니다.

수식을 입력했으면 D6:E16 범위에 수식을 자동 채우기합니다. 사용자가 선택한 월의 전년도 수입과 지출 합계가 계산됩니다.

다시 아래쪽으로 이동하여 올해와 작년의 수입/지출/저축액 합계, 그리고 전년 대비 변화량을 계산합니다.

‘차트자료’ 시트의 C45셀에 아래 수식을 입력합니다. 올해 선택한 월의 수입 합계는 ‘차트자료’ 시트의 수입 클릭 범위 합계, 즉 J5:J16 범위의 합계가 됩니다. (C46셀에는 K5:K16 범위의 합계를 계산합니다.)

이전과 동일하게 저축 금액은 ‘수입 – 지출’로 계산합니다. ‘차트자료’ 시트의 C47셀에 아래 수식을 입력합니다.

작년도의 수입/지출액 합계도 동일한 방식으로 계산합니다. ‘차트자료’ 시트의 D45셀에는 방금 입력한 D5:D16 범위의 합계, 즉 전년도 선택 월의 수입 합계를 계산해 넣어줍니다. (D46셀에도 E5:E16 범위의 합계를 계산합니다.)

전년 대비 변화량은 ‘올해 – 작년’으로 수식을 입력합니다. ‘차트자료’ 시트의 E45셀에 아래 수식을 입력한 뒤, E45:E47 셀에 수식을 자동 채우기 합니다.

사용자 지정 서식을 변경합니다. 우선 C45:D47 범위를 선택해 올해와 작년도의 수입/지출/저축액을 나타내는 셀의 사용자 지정 서식을 아래와 같이 변경합니다.

전년 대비 변화량을 나타내는 셀은 이후 ‘연결된 그림’을 통해 대시보드에 삽입하게 됩니다. 따라서 수입과 지출의 변화량에 따라 파랑/빨강색으로 구분되어 표시되도록 아래와 같이 사용자 지정 서식을 입력합니다. ‘차트자료’ 시트의 E45 및 E47 셀을 선택한 뒤 사용자 지정 서식을 아래와 같이 변경합니다.

E46셀 (지출)의 경우 금액이 증가하면 ‘빨강’, 감소하면 ‘파랑’으로 표시해야 의미 전달이 자연스럽습니다. 따라서 아래의 사용자 지정 서식을 입력합니다.

사용자 지정 서식까지 모두 변경을 완료하면, 아래와 같이 선택한 월의 백데이터 준비가 마무리됩니다.

사용자가 선택한 월의 마지막 날을 기준으로 마감된 계좌별 잔액 상세를 계산합니다. ‘차트자료’ 시트의 B49셀에 아래 수식을 입력합니다.

계좌별 잔액 상세는 선택한 월의 ‘마지막 날’을 기준으로 계산합니다. 따라서 ‘차트자료’ 시트의 B50셀에는 DATE 함수를 사용하여 아래와 같이 수식을 입력합니다.

수식을 입력했으면 D50셀의 사용자 지정 서식을 아래와 같이 변경합니다.

여기까지 완료하면 대시보드 만들기의 90%가 끝났습니다. 이제 대시보드를 시각적으로 마무리하는 10%의 작업만 남았습니다.

3. 대시보드 최종 꾸미기

‘대시보드’ 시트로 이동합니다. [삽입] – [텍스트 상자] 메뉴에서 ‘가로 텍스트 상자’를 삽입합니다.

삽입된 텍스트 상자를 선택한 뒤 [도형 서식]으로 이동합니다. 채우기 색상과 윤곽선을 모두 ‘없음’으로 선택합니다.

다시 텍스트 상자를 선택합니다. 이때 [중요!] ‘텍스트 상자의 커서가 깜빡이지 않은 상태’로 텍스트 상자의 모서리만 선택된 상태에서 상단의 ‘수식 입력줄’을 클릭합니다.

수식 입력줄에 ‘등호(=)’를 입력한 뒤, ‘차트자료’ 시트의 B40셀을 선택합니다.

선택 후 엔터를 입력합니다. 텍스트 박스의 값이 선택된 셀의 값과 자동으로 연동되는 것을 확인할 수 있습니다.

같은 방법으로 각각의 텍스트 박스를 생성합니다. 필요에 따라 글꼴, 색상, 글자 크기를 적절히 조정하여 텍스트 박스를 완성합니다.

텍스트 박스로 값을 연동할 경우, 안의 텍스트는 자동으로 변경되지만 색상까지 자동으로 연동되지는 않습니다. 전년 대비 변화량의 경우 증가/감소 현황에 따라 빨강과 파랑으로 자동 갱신되어야 하므로, ‘연결된 그림’ 기능을 사용해 대시보드에 삽입합니다.

‘차트자료’ 시트로 이동한 뒤, 상단의 [보기] – [눈금선] 체크박스를 해제합니다.

전년 대비 수입의 변화량을 나타내는 E45셀을 선택해 복사한 뒤, ‘대시보드’ 시트로 이동하여 아무 셀이나 우클릭 – [선택하여 붙여넣기] – [연결된 그림]을 선택해 이미지를 삽입합니다.

선택한 월의 마지막 날을 기준으로 각 계좌별 잔액 상세를 계산합니다. ‘대시보드’ 시트에서 셀의 너비와 높이를 조절해 ‘계좌명’, ‘잔액’, 그리고 ‘잔액 합계’를 나타내는 표를 만들어 줍니다. (강의에서는 L30:M37 범위에 표를 생성했습니다.)

이전 단계에서 각 도형의 [도형 서식] – [속성]에 ‘변하지 않음’을 선택해 두었다면, 셀 너비를 변경해도 도형의 위치나 크기는 그대로 유지됩니다.

표를 추가했으면 ‘환경설정’ 시트로 이동하여 원하는 계좌를 5개 정도 선택한 뒤, 만들어 둔 표 안에 ‘값만 붙여넣기’로 원하는 계좌를 추가합니다.

각 계좌별로 선택한 월의 마지막 날을 기준으로 한 잔액을 계산합니다. SUMIFS 함수를 사용해 계산합니다. ‘대시보드’ 시트의 M31셀에 아래 수식을 입력합니다. (입력한 표의 위치에 따라, 대시보드!L31셀의 값은 해당 계좌가 입력된 셀 주소로 변경합니다.)

수식을 입력했으면, 잔액이 들어갈 범위에 수식을 자동 채우기합니다. 이후 ‘잔액 합계’가 들어갈 셀에 SUM 함수를 사용해 잔액 합계를 계산하여 표를 마무리합니다.

4. 대시보드 동작 확인 및 마무리

자주묻는 질문

Q1. SUMPRODUCT 함수 사용으로 처리속도가 느립니다.

SUMPRODUCT 함수는 인수로 배열을 받기 때문에, 1만 행 이상 많은 양의 데이터를 다룰 경우 처리속도가 느려지는 한계가 있습니다. 따라서 처리속도가 느릴 경우 SUMIFS 함수를 사용하면 처리속도를 향상시킬 수 있습니다.

각 항목별로 사용된 SUMIFS 함수 대체 공식은 이전 포스트의 두 번째 패치노트에서 자세히 설명드렸으니, 필요하신 분은 참고해주세요. 아래 SUMIFS 함수를 사용한 공식의 동작 원리도 함께 확인하시면 공식을 이해하시는 데 큰 도움이 됩니다.

Q2. 전월 대비 수입 및 지출을 보려면 어떻게 해야 하나요?

1. 예제파일의 '차트자료' 시트로 이동합니다.
2. 차트자료 시트의 각 셀의 값을 아래와 같이 변경합니다.
   

   셀주소	값
   D40셀	전월
   D41셀	=SUMIFS(가계부입력!H:H,가계부입력!C:C,"수입",가계부입력!B:B,">="&DATE(차트자료!B3,SUM(차트자료!L5:L16)-1,1),가계부입력!B:B,"<="&EOMONTH(DATE(차트자료!B3,SUM(차트자료!L5:L16)-1,1),0))
   D42셀	=SUMIFS(가계부입력!H:H,가계부입력!C:C,"지출",가계부입력!B:B,">="&DATE(차트자료!B3,SUM(차트자료!L5:L16)-1,1),가계부입력!B:B,"<="&EOMONTH(DATE(차트자료!B3,SUM(차트자료!L5:L16)-1,1),0))

3. E41셀 ~ E43셀의 사용자지정서식에서 '전년대비'를 '전월대비'로 변경합니다.