이번 단계는 대시보드에 그동안 만들었던 기본 데이터와 피벗테이블을 기반하여 데이터를 시각화 하는 단계입니다.
데이터 시각화 첫번째 강의로 텍스트 상자와 붙여넣기를 활용하여 대시보드에 데이터를 표기 하도록 하겠습니다.
글의 순서
대시보드 기본 설정 하기
대시보드에 시각화 할 데이터는 아래와 같습니다.
- 통장잔액
- 지출금액
- 수입금액
- 대분류별 지출 상세
- 상위 10개 지출 항목
- 월별 지출/수입 현황
- 고정비 현황
- 최근 50개 거래내역
- 일별 지출/수입 현황
총 9개의 데이터 시각화 작업을 할 것이며 각각의 셀에 데이터를 입력하는 것이 아닌 차트와 그래프 그리고 슬라이서를 활용하여 작업하도록 하겠습니다. 먼저, 베이스가 되는 배경작업을 하겠습니다. 이 강좌에서 설명하는 스타일과 색상으로 작업을 하시되, 작업을 하시면서 색상과 디자인은 본인의 취향대로 얼마든지 변경하실 수 있음을 알려드립니다.
함께 참고하면 좋은 글 추천
- 따라하며 엑셀 가계부 대시보드 만들기 1편 – 기본 데이터 가공하기
- 따라하며 엑셀 가계부 대시보드 만들기 2편 – 피벗테이블 가공하기
- 따라하며 엑셀 가계부 대시보드 만들기 3편 – 자동 달력 만들기
1. 대시보드 배경 만들기
- 대시보드 시트에서 보기탭의 눈금선을 해제 하여 시트가 갈끔해 지도록 만듭니다.
- 많은 데이터가 표기 되기 때문에 배율을 55% 까지 축소하여 작업하겠습니다. 보기 탭에서 확대/축소를 클릭해 사용자 지정에 55를 입력합니다.
- 삽입 탭의 도형란에 직사각형을 선택합니다.
- 현재 축소되어 있는 배율 화면에 맞추어 직사각형을 그립니다. 저는 AJ73셀 까지 그리겠습니다.
- 도형의 색상은 #7F798A로 지정합니다. (추후 본인의 스타일대로 변경 가능 합니다.)
- 삽입 탭의 도형란에 둥근 모서리 사각형을 선택합니다.
- 채우기 색은 흰색으로 지정하고 외곽선은 선 없음을 선택합니다.
- 배경위에 적당한 크기로 그려줍니다.
- 삽입 탭의 도형란에 직선을 선택합니다.
- 배경위에 적당한 크기로 그려줍니다.
- 도형 서식으로 가서 너비는 7pt로 지정하고 색상은 #E25767로 지정합니다.
- 삽입 탭의 도형란에 둥근 모서리 사각형을 선택합니다.
- 아래그림과 같은 위치에 2개를 그려 주시고 색상은 #181D28로 지정합니다.
- 삽입 탭의 도형란에 둥근 모서리 사각형을 선택합니다.
- 아래그림과 같은 위치에 8개를 그려 주시고 색상은 옅은 회색으로 지정합니다.
- 도형 서식의 효과 에서 바깥쪽 그림자를 적용하여 도형에 입체감을 추가해 줍니다.
- 텍스트 상자를 활용하여 각각의 도형에 머리글을 작성해 주겠습니다.
- 삽입 탭의 텍스트 상자를 추가하여 각각의 머리글을 작성해 줍니다.
- 폰트 크기는 14pt로 지정하고 폰트 색상은 흰색과 검정색으로 지정 합니다.
- 도형의 채우기와 외곽선은 없음을 선택합니다. 머리글은 아래의 이름으로 각각 입력합니다.
- 통장잔액
- 지출금액
- 수입금액
- 대분류별 지출 상세
- 상위 10개 지출 항목
- 월별 지출/수입 현황
- 고정비 현황
- 최근 50개 거래내역
- 일별 지출/수입 현황
2. 통장잔액 표기하기
통장잔액 블럭에 각 월의 잔액을 표기하겠습니다.
먼저, 사용내역 시트로 가서 월마다 잔액이 변하도록 수식을 작성하겠습니다.
사용내역 시트 D5셀에 아래 수식을 입력합니다.
=INDEX(E:E,MAX(IFERROR(MATCH(“*”,E:E,-1),0),(IFERROR(MATCH(9E+307,E:E,1),0))))
다시 대시보드 시트로 이동하여 통장잔액 블럭에 텍스트 상자를 추가한 후 텍스트상자는 ‘채우기 없음’, ‘선 없음’ 으로 속성을 지정하고 텍스트상자를 선택한 상태에서 수식입력 창에 =사용내역!D5 수식을 작성해 줍니다. 글자 크기는 24pt, 글자색은 붉은색으로 지정합니다.
그럼 아래그림과 같이 텍스트 상자 안에 잔액 테이터가 표기되었습니다.
3. 지출금액 표기하기
지출금액 블럭에 월별 지출금액을 표기하겠습니다.
- 삽입 탭의 텍스트 상자를 추가합니다.
- 텍스트상자의 속성창에서 배경색은 없음, 외관선은 없음을 선택합니다.
- =피벗데이터!P3 수식을 작성하고 글자 크기는 24pt로 지정합니다.
4. 수입금액 표기하기
수입금액 블럭에 월별 수금액을 표기하겠습니다.
- 삽입 탭의 텍스트 상자를 추가합니다.
- 텍스트상자의 속성창에서 배경색은 없음, 외관선은 없음을 선택합니다.
- =피벗데이터!Q3 수식을 작성하고 글자 크기는 24pt로 지정합니다.
5. 최근 50개 거래 내역 표기하기
- 피벗데이터 시트의 5.최근50개 입/출금 내역 피벗테이블 옆에 만들어 놓았던 표에 테두리를 만들어 줍니다.
- 범위를 지정 후 홈 탭의 테두리에 ‘모든 테투리’를 선택합니다.
- 테투리 색상은 기본색인 검정색으로 지정해 줍니다.
- 표의 머리글 배경을 #53C6C8 색으로 지정해 줍니다.
- 날짜가 현재 숫자로 지정되어 있으므 사용자지정 서식에서 표시형식을 mm/dd로 변경해 줍니다.
- 날짜와 내용열에 있는 데이터의 글 색상을 흰색으로 변경합니다.
- 표를 복사하여 대시보드 시트의 최근 50개 거래내역 블럭에 붙여넣기 합니다.
- 이때 마우스 우클릭으로 연결된 그림으로 옵션을 선택하여 붙여넣기 합니다.
6. 자동 달력 표기하기
- 자동달력 시트로 이동하여 이전 강좌에서 만들었던 자동달력을 복사합니다.
- 대시보드 시트의 일별 지출/수입 현황 블럭에 붙여넣기 합니다.
- 마우스 우클릭하여 연결된 그림으로 붙여넣기 합니다.
- 적당한 크기로 맞추 줍니다.
여기까지 진행 하셨다면 현재 위의 그림과 같은 작업물을 만드셨을겁니다. 혹 빠진부분이 있다면 강의를 다시한번 보시면서 수정하시길 바랍니다. 수고하셨습니다.
다음 강의는 차트를 만들어서 데이터 시각화 작업을 하는 강의입니다. 감사합니다.
