은행 거래내역으로 자동 확장 엑셀 가계부 대시보드를 어떻게 만드는가?

TL;DR

은행 거래내역을 엑셀 표로 변환하고 계정과목, 대분류, 거래일, 시간대를 추가한 뒤 피벗테이블과 슬라이서를 연결하면 데이터가 누적되어도 갱신 가능한 가계부 대시보드를 만들 수 있습니다. 단어표로 거래 내용을 자동 분류하고, 미분류 항목만 보완한 다음 수식을 값으로 고정하면 반복 작업도 줄어듭니다.

Transcript

예제 파일을 다운받아서 [통장내역] 시트로 이동해 주시고요 [통장내역]으로 오시면 일반적으로 은행 홈페이지에서 다운받은 은행 데이터를 준비했습니다 근데 한 가지 주의사항이 은행 홈페이지에서 다운받으시면 아마 다들 공감하시겠지만 엑셀 파일 형식이 대부분의 경우 .xls 형식으로 다운받아져요 아마 공감하실 겁니다 이런 정부 홈페이지나 또는 은행 홈페이지에서 엑셀 파일을 다운받으면 호환성 문제가 있을 수 있기 때문에 만약에 데이터를 다운받았는데 .xls 형식이라고 하실 때는 파일에서 다른 이름으로 저장해 오신 다음에 반드시 형식을 .xlsx로 바꾸신 다음에 작업하셔야 합니다 왜냐하면 그냥 .xls 형식으로 작업한 다음에 파일... Read More

Key Insights

• 은행에서 내려받은 .xls 파일은 먼저 .xlsx로 저장해야 피벗테이블과 피벗차트가 저장 과정에서 사라질 수 있는 호환성 문제를 줄일 수 있습니다.
• 누적되는 거래내역을 엑셀 표로 변환하면 새 데이터를 아래에 붙일 때 표 범위가 확장되어 피벗테이블의 원본 범위에도 반영할 수 있습니다.
• 표 변환 후 생기는 줄무늬가 기존 보고서 서식과 맞지 않으면 테이블 디자인의 표 스타일을 없음으로 변경해 기존 형태를 유지할 수 있습니다.
• 분류표와 단어표에도 표 이름을 지정하면 구조화 참조로 특정 열만 불러올 수 있고, 새 분류나 단어가 추가될 때 참조 범위도 늘어납니다.
• 데이터 유효성 검사의 목록 원본에 INDIRECT 함수와 분류표의 분류 열을 사용하면 자동 확장되는 계정과목 선택 목록을 만들 수 있습니다.
• 거래 내용과 포함 단어를 비교하는 공식은 일치하는 단어표의 분류를 반환하며, 단어표의 분류를 수정하면 계산 결과도 함께 변경됩니다.
• 자동 분류되지 않은 빈 항목만 필터링해 자주 반복되는 적금, 주식배당금, 쿠팡 같은 단어를 단어표에 추가하면 이후 분류 작업이 감소합니다.
• 계정과목 자동 분류를 검토한 뒤 수식 열을 복사해 값으로 붙여넣으면 각 거래의 확정된 분류가 이후 단어표 변경의 영향을 받지 않습니다.
• 대분류는 VLOOKUP으로 분류표에서 가져오고, 거래일은 원본 날짜를 참조하며, 시간대는 HOUR 함수로 거래 시간에서 시간만 추출합니다.
• 피벗테이블은 기본적으로 레이블 순으로 정렬되므로 지출 분석에서는 금액 합계를 기준으로 내림차순 정렬하고 수입 항목을 제외해야 합니다.
• 🎬 이 채널의 다른 영상:
  • 직장인 PPT를 읽지 않아도 핵심 메시지가 바로 보이게 디자인하는 방법은?
  • 노션 기본 기능과 AI로 실시간 공유 블로그를 어떻게 만드는가?
  • 엑셀 파워쿼리로 네이버·구글·쿠팡 키워드를 어떻게 실시간 자동 분석할까?
• 📺 이 채널: 오빠두엑셀 l 엑셀 강의 대표채널

Questions & Answers

Q: 은행에서 받은 .xls 거래내역은 왜 .xlsx로 바꿔야 하나요?

.xls 상태로 작업하기보다 다른 이름으로 저장하여 .xlsx 형식으로 바꾸는 것이 좋습니다. 강의에서는 정부나 은행 홈페이지에서 받은 .xls 파일에 호환성 문제가 있을 수 있고, 그대로 작업하고 저장하면 피벗테이블이나 피벗차트가 사라지는 경우가 있다고 설명합니다.

Q: 새 거래내역이 추가되어도 원본 범위가 자동으로 늘어나게 하려면 어떻게 하나요?

거래내역의 연속된 범위를 선택하고 삽입 탭에서 표로 변환하면 됩니다. 머리글 포함을 선택해 표를 만들면 아래에 새 데이터를 추가할 때 표의 끝이 자동으로 확장되며, 이 표를 기반으로 만든 피벗테이블의 원본도 확장된 범위를 사용할 수 있습니다.

Q: 엑셀 표로 변환한 뒤 기존 셀 서식을 유지하려면 어떻게 하나요?

표를 선택한 뒤 테이블 디자인 탭의 표 스타일에서 없음을 선택하면 됩니다. 표 기능을 적용하면 기본적으로 줄무늬와 테두리가 표시되지만, 스타일만 제거해도 자동 확장과 구조화 참조 같은 표의 데이터 관리 기능은 계속 사용할 수 있습니다.

Q: 자동으로 확장되는 계정과목 드롭다운 목록은 어떻게 만드나요?

분류 범위를 표로 바꾸고 표 이름을 분류표로 지정한 다음, 데이터 유효성 검사의 제한 대상을 목록으로 설정합니다. 원본에는 INDIRECT 함수를 사용해 분류표의 분류 머리글 범위를 참조하면 새 계정과목이 표에 추가될 때 목록 범위도 함께 확장됩니다.

Q: 거래 내용을 계정과목으로 자동 분류하려면 어떻게 구성해야 하나요?

포함 단어와 그에 대응하는 분류를 가진 단어표를 만들고, 거래 내용에서 포함 단어를 검색해 해당 분류를 반환하는 공식을 계정과목 열에 입력합니다. 예를 들어 단어표에서 GS25 편의점의 분류를 생활용품으로 바꾸면 연결된 계산 결과도 자동으로 갱신됩니다.

Q: 공식으로 분류되지 않은 거래는 어떻게 빠르게 처리하나요?

계정과목 열에서 빈 값만 필터링하고 거래 내용을 오름차순으로 정렬해 반복되는 미분류 항목을 찾습니다. 적금, 주식배당금, 쿠팡처럼 자주 등장하는 단어와 분류를 단어표에 추가하면 해당 거래가 다시 계산되고, 남은 소수의 항목만 직접 선택하면 됩니다.

Q: 자동 분류가 끝난 계정과목 수식을 왜 값으로 바꾸나요?

검토가 끝난 분류를 고정하여 계정과목이 확정된 상태로 유지하기 위해서입니다. 계정과목 열을 복사한 뒤 선택하여 붙여넣기에서 값을 선택하면 수식 대신 결과만 남으므로, 이후 단어표가 수정되더라도 이미 확정한 거래 분류는 바뀌지 않습니다.

Q: 대분류, 거래일, 시간대 열은 각각 어떻게 채우나요?

대분류는 계정과목을 기준으로 VLOOKUP을 사용해 분류표의 두 번째 값을 정확히 일치 조건으로 가져옵니다. 거래일은 원본 거래일자를 그대로 참조하고, 시간대는 HOUR 함수로 거래 시간에서 시간만 추출하며 0이 보이지 않으면 일반 서식으로 변경합니다.

Q: 대분류별 지출 피벗테이블은 어떻게 설정해야 하나요?

거래내역 표에서 피벗테이블을 만들고 대분류를 행 영역에, 출금 내역을 값 영역에 배치합니다. 기본 레이블 순서 대신 금액 합계를 기준으로 내림차순 정렬하고, 레이블 필터에서 수입을 포함하지 않도록 설정하면 지출 항목만 큰 금액부터 확인할 수 있습니다.

Q: 여러 피벗테이블을 하나의 조건으로 필터링하려면 무엇을 사용하나요?

슬라이서를 추가하고 보고서 연결을 사용해 대시보드의 여러 피벗테이블에 연결합니다. 그러면 하나의 슬라이서 선택으로 연결된 표들을 동시에 필터링할 수 있으며, 불필요한 버튼을 숨기고 버튼 개수와 머리글을 조정해 대시보드 구성에 맞게 정리할 수 있습니다.

Summary & Key Takeaways

• 먼저 은행에서 받은 거래내역이 .xls라면 피벗테이블이나 피벗차트가 저장 과정에서 사라지는 호환성 문제를 피하도록 .xlsx로 저장합니다. 거래내역과 분류표, 단어표를 각각 엑셀 표로 바꾸고 이름을 지정하면 새 행을 추가할 때 원본 범위와 구조화 참조 범위가 함께 자동 확장됩니다.

• 거래내역에는 계정과목, 대분류, 거래일, 시간대 열을 추가합니다. 계정과목 목록은 INDIRECT 함수로 분류표의 분류 열을 참조해 입력값을 제한하고, 단어표와 제공된 공식을 이용해 거래 내용에 포함된 단어를 분류로 변환합니다. 빈 항목을 필터링해 반복되는 단어를 보완한 뒤 결과를 값으로 고정합니다.

• 가공한 표를 원본으로 대분류별 지출, 상위 거래내역, 월별 수입과 지출, 시간대별 지출, 최근 거래내역 등의 피벗테이블을 구성합니다. 금액 기준 내림차순 정렬, 수입 제외 필터, 날짜 집계와 계산필드를 적용하고, 여러 피벗테이블을 슬라이서에 연결해 하나의 조건으로 대시보드 전체를 필터링합니다.