엑셀 대시보드 만들기 완성 :: 심플한 엑셀 보고서 끝판왕

엑셀 가계부 대시보드 만들기 | 최종 완성 강의

엑셀 가계부 대시보드 최종완성 - 목차 바로가기

영상강의

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

강의소개

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

작업을 단계별로 알아봅니다. 이전 강의와 연계되는 내용이 많이 포함되어 있습니다. 이전 강의를 못보고 오신 분은 이전 강의를 반드시 확인하신 뒤에 이번 강의를 진행해주세요.

각 항목별 대시보드에 들어갈 자료를 구분하기 위한 틀을 추가합니다. 이번 강의에서는 가계부제작을 위해 아래 항목들로 구분했지만, 직접 제작하고자 하는 대시보드의 주제 또는 표현하고자 하는 내용을 미리 정한 뒤 틀을 생성합니다.

가계부 대시보드를 위한 항목 구분

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

선택한 년도의 수입과 지출 합계는 이전 강의에서 미리 구했으므로, 이번 강의에서는 전년도의 수입/지출 합계를 계산해주겠습니다. ‘차트자료’ 시트의 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%의 작업만 남았습니다.

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

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

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

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

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

같은 방법으로 각각의 텍스트박스를 생성합니다. 필요에 따라 글꼴과 색상, 그리고 글자 크기를 바꿔서 텍스트박스를 생성합니다.

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

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

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

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

이전에 각 도형의 [도형 서식]에서 [속성]에 ‘변하지 않음’을 선택하였으면, 셀 넓이를 변경해도 도형의 위치나 크기가 변하지 않고 그대로 유지됩니다.

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

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

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

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

자주묻는 질문

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

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

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

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