이번 포스팅은 1편의 기본 데이터 자료를 토대로 피벗테이블을 만들고 이를 가공하는 강좌 입니다.
이번 강좌가 엑셀 가계부 대시보드 만들기의 핵심 내용이니 내용을 꼼꼼히 읽으면서 천천히 진행해 주시길 당부드립니다.
글의 순서
- 엑셀 피벗테이블이란?
- 엑셀 가계부 대시보드 만들기 – 피벗테이블 만들기
- 1. 엑셀 가계부 대시보드 만들기 – 대분류별 지출 내역 피벗테이블 만들기
- 2. 엑셀 가계부 대시보드 만들기 – 상위 10개 거래 내역 피벗테이블 만들기
- 3. 엑셀 가계부 대시보드 만들기 – 고정비 지출 내역 피벗테이블 만들기
- 4. 엑셀 가계부 대시보드 만들기 – 월별 입/출금 내역 피벗테이블 만들기
- 5. 엑셀 가계부 대시보드 만들기 – 최근 50개 입/출금 내역 피벗테이블 만들기
- 6. 엑셀 가계부 대시보드 만들기 – 월별 달력 연동 피벗테이블 만들기
엑셀 피벗테이블이란?
피벗테이블은 특정 기준에 따라 데이터를 재정렬하고 요약하여 다양한 관점에서 데이터를 볼 수 있게 만드는 도구입니다.
원본 데이터의 필드를 행, 열 값으로 끌어다 놓으면 정보를 동적으로 정렬하고 요약할 수 있으며 선택한 기준에 따라 데이터를 자동으로 집계하여 소계, 총계 및 다양한 요약을 제공합니다.
몇번의 클릭만으로 간결하고 체계적인 방식으로 정보를 제시함으로써 이해를 높이고 의사결정을 용이하게 하는 데이터의 시각적 표현을 제공합니다.
함께 참고하면 좋은 글 추천
엑셀 가계부 대시보드 만들기 – 피벗테이블 만들기
1편에서 만들었던 기본 데이터를 바탕으로 피벗테이블을 만들도록 하겠습니다.
총 6개의 피벗테이블을 만들것이며, 먼저 각 피벗테이블을 붙여넣고 관리하기 편하도록 피벗테이블 시트에 머리글을 작성해 주겠습니다.
- 대분류 지출 내역
- 상위 10개 거래 내역
- 고정비 지출 내역
- 월별 입/출금 내역
- 최근 50개 입/출금 내역
- 월별 달력 연동
1. 엑셀 가계부 대시보드 만들기 – 대분류별 지출 내역 피벗테이블 만들기
1. 사용내역 시트에서 거래내용으로 만들었던 표를 범위로 지정한 후 삽입 탭에서 피벗테이블을 클릭한 후 활성화 된 테이블/범위에서를 누릅니다.
2. 기존 워크시트를 선택하고 피벗테이블 시트의 B3셀을 지정합니다.
3. 만들어진 피벗테이블 필드에 데이터를 지정합니다. → 행 : 대분류, 값 : 출금
데이터를 지정하면 피벗테이블이 만들어 집니다. 여기서 출금 값의 숫자 형식을 1000단위 구분 기호 로 지정합니다.
4. 이제 만들어진 피벗테이블을 구분하기 쉽도록 ‘대분류별 지출 내역’으로 이름을 변경해 줍니다.
5. 그리고 현재 피벗테이블을 보면 수입 항목에 값이 비어있어서 추후 시각화 작업시 빈값으로 표기 되므로 값이 비어 있는 값을 제외하기 위해 행레이블 옆에 토글 버튼을 눌러 옵션을 활성화 해주고 값 필터에서 보다 큼을 누르고 값 필터에서 0을 입력 하여 빈 값을 제외해 줍니다.
6. 마지막으로 피벗테이블의 데이터를 출금 금액 기준으로 내림차순 정렬을 하겠습니다.
2. 엑셀 가계부 대시보드 만들기 – 상위 10개 거래 내역 피벗테이블 만들기
1. 위에서 만든 피벗테이블을 복사하여 상위 10개 거래 내역 머리글 E3셀에 붙여넣습니다.
2. 붙여넣은 피벗테이블의 필드를 행에는 소분류, 값에는 출금으로 수정해 주고 피벗테이블 이름을 ‘상위 10개 거래 내역’으로 변경해 줍니다.
3. 위에서 설정한 것과 동일한 방법으로 토글 버튼을 눌러 옵션을 활성화 해주고 값 필터에서 보다 큼을 누르고 값 필터에서 0을 입력 하여 빈 값을 제외해 주고 출금 금액 기준으로 내림차순 정렬해 줍니다.
4. 상위 10개 내역만 시각화 하기 위하여 피벗테이블 옆에 열을 삽입하여 그림과 같이 만들어 줍니다.
3. 엑셀 가계부 대시보드 만들기 – 고정비 지출 내역 피벗테이블 만들기
먼저 만든 피벗테이블 복사 하여 붙여 넣고 필드는 필터에 대분류, 행에 소분류, 값에 출금을 지정해 줍니다. 필터에 지정했던 대분류를 고정비로 변경해 줍니다. 피벗테이블 이름을 ‘고정비 지출 내역’으로 변경해 줍니다.
4. 엑셀 가계부 대시보드 만들기 – 월별 입/출금 내역 피벗테이블 만들기
- 1. 대분류별 지출 내역 피벗테이블을 복사해서 붙여 넣습니다.
- 2. 이때 머리글 아래 2개의 행을 비워두고 붙여 넣습니다.
- 3. 필드지정은 행에는 거래일자, 값에는 출금과 입금을 지정합니다.
- 4. 여기서 행레이블의 거래일자는 마우스 우클릭 하여 옵션창에서 그룹을 선택합니다.
- 5. 그룹화 옵션창이 활성화 되면 ‘월’ 과 ‘연’ 을 선택합니다.
- 6. 년도와 개월이 분리됩니다.
- 7. 년도와 개월을 각각 다른 열로 분리 하기 위해 디자인 탭의 보고서 레이아웃을 선택하여 테이블 형식으로 변경합니다.
- 8. 디자인 탭의 총합계를 선택하여 피벗테이블의 총합계를 해제하여 줍니다.
- 9. 디자인 탭의 부분합을 선택하여 피벗테이블의 부분합을 해제하여 줍니다.
- 10. 비워 놓았던 머리글 아래 2개의 행에 데이터를 채워 주겠습니다.
- 11. 합계와 년/월을 구분하는 표를 만들어 줍니다.
- 12. 합계 행에는 피벗테이블의 출금과 입금의 합계를 구하는 수식을 각각 작성해 줍니다.
- 13. 년/월 행에는 피벗테이블의 첫번째 행의 ‘년’ 과 ‘월’ 을 지정해 줍니다.
5. 엑셀 가계부 대시보드 만들기 – 최근 50개 입/출금 내역 피벗테이블 만들기
- 1. 마찬가지로 대분류별 지출 내역 피벗테이블을 복사해서 머리글 아래에 붙여넣기 합니다.
- 2. 피벗테이블 필드를 행에 거래일과 소분류, 값에 출금으로 지정해 줍니다.
- 3. 피벗테이블의 이름을 ‘최근 50개 입출금 내역’ 으로 변경해 줍니다. (방법은 위 내용과 동일 합니다.)
- 4. 거래일과 소분류를 각각 다른 열에 표기하기 위해 보고서 레이아웃을 테이블 형식으로 변경해 줍니다.
- 5. 디자인탭에서 부분합과 총합계를 해제하여 줍니다.
- 6. 피벗테이블 분석 탭에서 필드설정으로 들어 갑니다.
- 7. 레이아웃 및 인쇄 탭에서 ‘항목 레이블 반복’을 체크해 줍니다.
(피벗테이블을 보시면 거래일 항목에 빈 셀이 있는데 이는 피벗테이블이 중복되는 항목을 자동으로 제거하여 표기 되지 않으므로 표기하기 위해 ‘항목 레이블 반복’을 체크하여 줍니다.) - 8. 소분류열의 토글키를 눌러 값필터 옵션을 활성화 해줍니다.
- 9. 옵션중 ‘보다 큼’ 을 선택하고 값에 ‘0’ 을 입력 합니다.
- 10. 값의 출금항목을 입금과 출금을 동시에 나타내야 하므로 계산식을 하나 만들어 줍니다.
- 11. 피벗테이블 분석 탭에서 ‘필드, 항목 및 집합’으로 들어 갑니다.
- 12. 계산식 이름은 입출금으로 만들어 줍니다.
- 13. 수식은 ‘=입금-출금’으로 만들어 줍니다.
- 14. 위의 수식은 필드에서 입금 선택 후 필드 삽입 – 출금 선택 후 필드 삽입을 누르면 됩니다.
- 15. 추가를 클릭하여 수식을 추가해 줍니다.
- 16. 입출금이 값필드에 지정 되었으므로 출금은 삭제합니다.
5-1. 엑셀 가계부 대시보드 만들기 – 최근 50개 입/출금 내역 표 만들기
- 1. 거래내역이 쌓여감에 따라 데이터가 점차 방대해지기 때문에 50개 내역만 시각화 하기 위해 피벗테이블 옆에 따로 표를 만들어 줍니다.
- 2. 각각의 셀에는 수식을 사용하여 데이터를 채워 줍니다.
- 3. 수식은 =IF(S4=””,””,S4)를 날짜열에 작성해주고 옆으로 드래그해서 다른 열을 채워 줍니다.
- 4. 수식을 채워준 후 금액열의 숫자들의 서식을 지정해 주겠습니다.
- 5. ‘Ctrl + 1’ 을 눌러 셀 서식으로 들어가서 표시형식 중 가장 밑에 ‘사용자 지정’ 서식으로 들어 갑니다.
- 6. 형식란에 [색38]▲* #,##0;[색33]▼* #,##0 를 작성해 줍니다.
- 7. 숫자의 표시형식과 색이 변경되었습니다.
6. 엑셀 가계부 대시보드 만들기 – 월별 달력 연동 피벗테이블 만들기
6-1. 사용내용 범위에 데이터 추가하기
- 월별 달력 연동 피벗테이블을 만들기 전에 먼저 사용내역 시트에 거래내용 범위에 2개의 열을 삽입해 줍니다.
- 열은 각각 달력연동 거래연도, 달력연동 거래월 로 머리글을 만들어 줍니다.
- 달력연동 거래연도에 =YEAR([@거래일자]) 수식을 작성해 줍니다.
- 달력연동 거래월에 =MONTH([@거래일자]) 수식을 작성해 줍니다.
6-2. 월별 달력 연동 피벗테이블 만들기 및 가공하기
- 다시 피벗테이블 시트로 가서 대분류별 지출 내역 피벗테이블을 복사해서 머리글 아래에 붙여넣기 합니다.
- 이때 머리글 아래 1개의 행을 비워두고 붙여 넣습니다.
- 추가했던 필드를 불러오기 위해 데이터 탭의 ‘모두새로고침’ 을 클릭합니다.
- 필드지정은 행에는 추가했던 (달력연동 거래연도, 달력연동 거래월, 거래일), 값에는 (출금과 입금)을 지정합니다.
- 피벗테이블 이름은 ‘월별 달력 연동’으로 변경해 줍니다.
- 이후 4.엑셀 가계부 대시보드 만들기 – 월별 입/출금 내역 피벗테이블 만들기의 4번부터 ~ 9번까지의 절차를 똑같이 진행합니다.
- 필드설정에서 ‘달력연동 거래연도‘ 과 ‘달력연동 거래월‘ 을 ‘항목 레이블 반복’을 체크하여 데이터를 채워줍니다.
6-3. 월별 달력 연동 피벗테이블에 수식을 사용하여 데이터 추가하기
- 비워두었던 1개의 행에 수식을 사용하여 데이터를 채워줍니다.
- AA3셀에는 =MAX(AA5:AA10000) 의 수식을 작성합니다.
- AA4셀에는 =MAX(AB5:AB10000,INDEX(AB5:AB10000,MATCH(AA3,AA5:AA10000,0))) 의 수식을 작성합니다.
따라하며 엑셀 가계부 대시보드 만들기 2편 – 피벗테이블 가공하기 편을 마치겠습니다. 수고하셨습니다. 다음편은 월별 달력 피벗테이블과 연동시킬 자동 달력을 만드는 방법 가이드를 포스팅 하겠습니다.
