본문 바로가기

컴퓨터 활용/엑셀

후잉 가계부 입력 도움 엑셀 (은행 내역 입력) @복식부기 가계부 후잉

한동안 정신이 바빠서 몇 달간의 후잉 입력을 하지 못했습니다. 그래서 빠진 것을 일일히 넣지 못하였는데
금융기관에서 거래내역을 다운 받아 양식에 맞춰 입력 하기 위하여 엑셀을 만든 후에 몇 달 간의 데이터를 모두 입력을 하였습니다.
금융거래가 일어났을 때 내역을 그때그때 입력을 하지 못하면 누락된 항목이 생길 수밖에 없는데요.
열심히 입력하더라도 갑작스러운 수익이나 거래가 발생하여 빠질 수밖에 없습니다. 누락된 거래는 결국 순자산 항목을 이용한 잔액 조정으로 퉁 치게 됩니다.

 

저는 해당 항목이 빠지면 토스의 내 소비 화면과 뱅크셀러드의 가계부 화면을 참조하여 그 날짜에 통합된 금융 정보를 참조하여 입력을 합니다.
토스의 경우 카카오뱅크 내역은 지원하지 않고 일반적으로 금융내역을 수집을 해서 저장을 하는데 특정일에 일어난 계좌 간 거래를 표시해 줍니다. 다른 계좌로 이체가 일어났고 어디에 소비했는지 알 수 있죠.
뱅크샐러드의 경우 이체 내역 이외에 증권사의 상세 거래내역을 지원을 하지 않습니다. 계좌 간 이체나 대체도 명확하게 나와있지 않으므로 자금의 흐름을 파악하기에는 무리가 있습니다.
결국 정확한 데이터는 금융기관의 거래내역을 보고 입력할 수밖에 없습니다.

 

그래서 이빨 빠진 가계부 내역을 넣기 위하여 부족한 실력으로 간단하게 만들었습니다.
누구나 엑셀을 조금 하면 만들 수 있는 아주 쉬운 것입니다.
 
 
엑셀(Excel)의 시트는 다음과 같이 구성되어 있습니다.

 

1) 후잉 내역 : 후잉의 거래내역을 복사. 카카오뱅크의 거래내역 포맷 기준.
A2 열부터 붙여 넣으시면 됩니다.
2) 은행 내역 : 은행의 거래내역 다운
3) 예상 항목: 금융거래내역에서 내용을 기반으로 좌/우 항목을 결정하기 위하여 짝지어 놓은 시트입니다.

 

4) 신한은행 양식 변환,
5) 기업은행 양식 변환
각 은행별로 양식이 약간 틀리므로 타 은행이 필요하다면 날짜 형식, 금액, 입출금 여부, 내역 등을 적절하게 카카오뱅크의 거래내역으로 변환하는 시트를 만드셔야 할 것입니다.
5) 후잉 입력 최종 양식( 후잉에서 제공한 whooing_example.xls 파일)
은행 내역의 파란 부분을 복사하여 후잉 입력 최종 양식에 붙여 넣고 입력하기 전에 틀린 곳이 없나 점검한 후
후잉의 외부 입력에서 붙여 넣거나 업로드를 합니다.
 
 

사용하는 방법을 설명드리면

3,4)에서 각 은행별 양식에서 변환된 금융 거래 내역을 은행 내역의 U 행의 제일 마지막에 붙여 넣으시면 됩니다.
그다음 S 열인 계좌명을 입력해야 하는데 조건에 따라 왼쪽, 오른쪽에 들어갈 예정이니 정확하게 후잉 자산 계정 이름으로 입력해 주시면 됩니다.

 

 

신한은행 양식 변환 시트

신한은행의 포맷입니다
 
거래일자
거래시간
적요
출금(원)
입금(원)
내용
2020-04-24
13:09:53
타행FB
 
8,825
홍길동(데일리
 
변환된 수식은 다음과 같습니다.
 
A3 열
DATEVALUE(I3) + TIMEVALUE(J3)
DATEVALUE 와 TIMEVALUE는 날짜와 시간을 숫자인 날짜로 변환합니다. DATEVALUE만 넣어도 됩니다
B3 열
=IF(L3>0,"출금","입금")
카뱅의 포맷으로 L3인 출금 항목이 양이면 출금, 음이면 입금
C3 열
=IF(L3>0,-SUM(L3+M3),SUM(L3+M3))
출금, 입금의 값을 한 열로 합치기 위한 목적
 
기업은행 양식 변환 시트
 
거래일시
출금
입금
거래후 잔액
거래내용
2020-03-20 22:55
0
200,000
200,647
 
 
기업은행의 경우 거래 일시가 조건을 만족하므로 변환할 필요는 없고 B,C 열만 적용됩니다.
다른 은행도 비슷하게 작업해서 카카오뱅크 데이터 포맷으로 변경하신 후에 은행 내역에 붙여 주시면 됩니다.

 

 

메인에 해당되는 은행 내역 시트의 동작 개념은
 

1) 후잉 내역 검색

후잉 내역에서 날짜와 금액으로 검색을 하여 M 열을 완성합니다.
={MATCH(A2&C2,후잉내역!A:A&후잉내역!C:C,0)}
배열 수식으로 2개 이상의 쌍으로 데이터를 검색하는 일반적인 수식입니다. SHIFT-ALT-ENTER 동시에 누릅니다.
A2&C2는 날짜&금액입니다. 후잉 내역의 A열&C열을 합쳐서 엑셀 배열로 처리하여 날짜와 금액의 연결 문자열이 정확하게 일치(3번째 매개변수가 0) 하는 줄의 번호를 알아냅니다.
M 열이 숫자라면 후잉에 들어가 있다고 생각해도 되지만 동일 금액과 날짜의 내역이 있을 수도 있으므로 추적 기반 아이템/좌우 항목을 잘 살펴서 맞는다면 엑셀 필터를 이용하여 제외하면 됩니다.

 

 

2) 좌, 우 항목 결정

H 열의 예상 항목에서 Z2인 은행 거래 내역의 내용을 검색하여 예상 항목에 예상 내역을 찾아냅니다.
=IFNA(VLOOKUP(Z2,예상항목!A:C,2,FALSE),"")
 
※ VLOOKUP 함수는 둘째 매개변수의 영역의 첫 번째 열에서 문자를 검색하여 세 번째 항목의 숫자의 열의 값을 리턴해주는 함수입니다. 엑셀을 공부하면 반드시 알아야 할 함수입니다.

 

 

3) 이체 여부 검색 : I 열

={MATCH(1,(날짜=A2)*(금액=C2)*(계좌<>S2)*(아이템=B2),0)+1}​

배열 수식으로 SHIFT-ALT-ENTER 동시에 누릅니다.
I 열의 수식은 배열 수식으로 날짜가 A2, 금액이 C2, 계좌가 S2 가 아닌 아이템이 B2 인 첫 번째 검색 내역의 줄번호를 리턴해 줍니다. 여기서 + 1 을 해준 것은 테이블의 제목 때문입니다.
MATCH 함수를 이용하여 검색기능을 하는 일반적인 배열 수식으로 {MATCH(1, (배열수식조건),0)} 으로 하면 조건에 맞는 데이터가 있는 줄 번호를 리턴해 줍니다.
날짜, 금액이란 이름은 엑셀의 이름관리자에 날짜의 경우 =은행내역!$A$2:$A$133 을 의미하는 것입니다.
그래서 새 데이타를 붙여 넣기를 하려면 반드시 2행과 133행의 중간에 삽입하거나 넣어야 이름에 대한 해당 영역이 보존 됩니다. 133 열 다음에 추가하게 되면 해당 열은 검색 영역에서 제외가 됩니다.
위 수식은 같은 날, 같은 금액, 자신의 계좌가 아닌 아이템이 홍길동(일반적으로 본인 이름)인 데이터를 검색하여 줄번호를 찾아주는 역할을 합니다.
즉 L 열의 타 계좌명이 완성 되게 됩니다.

 

줄번호가 찾아지면 해당 이체/대체 내역이 어떤 계좌로 이체 되었는지 알 수 있게 됩니다.
첨부 엑셀의 2, 3번 줄을 보시면 알 수 있습니다. 후잉에 입력해야 할 동일 데이터이므로 최종으로 입력할 때 한 줄은 지워야 합니다.

 

4) 왼쪽 , 오른쪽 판별 수식

 
 
왼쪽 D 열 
=IF(W2="입금", T2&"+", -- 입금
    IF(NOT(ISNA(J2)), M2&"+", -- 이체 여부 체크( 있으면 찾아진 계좌 입력) /  
       IF( I2="부채", H2&"-",   -- 기타 신용카드, 부채상환등 출금 체크 
          IF(R2<>"", R2&"+",H2&"+") -- 수동입력이 있으면 수동입력 아니면 예상항목(+)입력  
))) -- 최종 IF를 제외한 IF 3개 

오른쪽 E 열 
=IF(V2="출금",S2&"-",IF(NOT(ISNA(I2)),L2&"-",
   IF(V2="입금", 
      IF(H2<>"현금", H2,H2&"-"), 
      IF(Q2<>"",     Q2&"+",H2&"+")
   )
))
 
V2는 입/출금 여부로 왼쪽에 V2가
입금이면 S2&"+" 가 되고,
출금 이면 IF(NOT(ISNA(I2)),L2&"+", IF(Q2<>"", Q2&"+",H2&"+")) 가 되는데
I 열인 인 타 계좌 이체 정보가 있으면 L2 인 3번에서 구해진 타 계좌명이 들어가게 됩니다.
Q 열에 항목 수기입력 열이 있는데 여기다 데이터를 넣게 되면 Q2에 들어 가 있는 데이터가 우선적으로 사용 되게 됩니다.
첨부 엑셀의 11 열에 "보험료" 라고 넣었는데 왼쪽 항목에 사용자가 수동 입력한 항목이 들어가도록 해 놓았습니다. 판별하기 어려우면 Q 열에 입력하면 됩니다.
오른쪽 수식도 비슷합니다.

 

만약 왼쪽 오른쪽에 + , - 만 나타난다면 예상항목에 해당 내역을 추가해 주면 됩니다.
항목명이 현금입금 이라면 예상내역에 현금, 입력예상항목명에 현금입금 을 입력 해주고
내용에 따른 좌우항목에 들어갈 적절한 후잉 계정명을 적어주면 됩니다.
 

예상항목 시트는 이미 들어가 있는 후잉 데이터를 기반으로 적절하게 수정해 나가면 됩니다.

 

5) 아이템(괄호)
=IF(G2<>"",G2,Z2)
예상 아이템이 존재하면 표시하고 아니면 금융거래의 내용을 그대로 표시
차후에 입력시 이름이 결정 되도록 함.

 

엑셀 파일은 업데이트 일원화를 위하여 다음에서 받으세요.
 
  


증권사의 내역도 변환하여 입력할 수 있도록 작업 중입니다.
미래에셋의 경우 해외 주식을 매수했을 때 미수 출금과 환전 차액 입금 등을 검색하여 빼줘야 하는 부분이 있을 수 있고 금융기관 내에서 계좌별 대체한 내역 검색 등도 있습니다.
 
 
 

저의 엑셀 실력은 초중급밖에 되지 않고 시간은 남아돌지만 게을러서 완벽하지 않은 데도 공개를 하였습니다.설명드린 몇 가지 수식만 활용한다면 첨부된 엑셀보다 더 잘 활용하실 수 있으리라 생각합니다. 복식 부기 가계부 후잉에 대해서는 저의 첫 글인 다음을 참고하시면 됩니다.

 

복식부기 가계부 - 후잉 사용법(1편 소개, 설정 )

후잉(Whooing) 이란 복식부기 가계부입니다. ​가계부를 쓰는 목적이 지출통제 및 관리 목적 이외에 수익/...

blog.naver.com