본문 바로가기

재테크

후잉 거래 입력 도움 엑셀 파일 - 수기 입력 단순 버전

후잉 거래 입력을 위하여 금융기관에서 거래내역을 뽑아서 일괄 올리는 기능을 제공하고 있습니다.

거래 입력 화면에서 우측 상단 외부 입력인데요. 후잉이 제공하는 whooing_example.xls에서 제공하는 양식을 입력하기 편하게 중복데이터을 걸러내고 눈으로 보면서 수기로 입력할 수 있도록 템플릿 파일로 만들었습니다.

예전에 다음과 같이 관련 글을 작성했었는데

 

후잉 거래 입력 도움 엑셀(Excel) @ 복식부기 가계부 후잉

한동안 정신이 바빠서 몇 달간의 후잉 입력을 하지 못했습니다. 그래서 금융기관에서 거래내역을 다운 받아...

blog.naver.com

최대한 자동화를 하려고 노력했지만 많은 변수가 있어 포기했습니다.

결국, 후잉 거래 내역에서 이미 입력된 동일 데이터를 걸러 내고 거래내역에 있는 각종 데이터를 직접 보고 수기입력하는 방식으로 입력하게 되었습니다. 검색된 정보는 예상 데이터 탭에 입력을 하고 다음에 동일 입력 내용이 발생할 때 동일하다면 검색된 내용을 복사해서 수기입력할 수 있도록 하였습니다.

사용방법

1) 후잉 거래내역 다운로드 후 입력하기
먼저 제일 처음에 있는 후잉 내역 시트에 후잉으로부터 해당 기간의 거래내역을 복사해서 붙여 넣어야 합니다.

 

좌측에 보면 중복 여부 확인이란 셀 이 있는데 필터를 적용하여 2 이상인 것을 걸러 낸 후에 조치를 취하면 됩니다  이미 후잉에 입력된 데이터에 중복이 있을 수 있어 =COUNTIFS(A:A,A2,C:C,C2,F:F,F2,H:H,H2) 함수로 구현했습니다. 

다음으로 은행(범용) 시트로 이동하여 데이터를 입력할 준비를 합니다.

은행(범용) 시트의 화면인데요. 메모에 설명을 해놨습니다.

2) S2 셀에 은행별 양식 변환 시트에서 완성된 데이터를 복사하여 R2에 마우스 오른쪽 버튼으로 값 붙여넣기 합니다.

3) A열부터 Q 열까지 2행의 값과 수식들을 다음행에 복사한 후 Q 열인 후잉계정명에 후잉의 자산계정 이름을 입력합니다. 그러면 후잉 내역으로부터 해당 행이 후잉에 입력되어 있는지 검색해서 알려줄 텐데요.

다음과 같습니다.

후잉추적(날짜/금액 & 출금) 은 날짜와 금액 그리고 왼쪽 항목을 검색하여 동일한 내역이 있으면 행 번호를 검색합니다. 검색된 내역은 예상 데이터 시트에 복사하도록 합니다. 다음에 입력될 때 참고할 수 있도록 하기 위해서입니다.

L 와 M 열이 모두 #N/A인 내역이 후잉가계부에 없는 입력해야 할 내용입니다. 필터 값을 #N/A 로 설정합니다.

4) 위에 있는 첫 번째 화면에 주황색 부분이 후잉에 입력된 데이터 입니다.

왼쪽 / 오른쪽 중에 비어 있는 곳과 아이템(괄호) 부분이 입력 내용 및 입력 참고정보를 참고하여 수기로 입력할 부분입니다.

5) 입력 완성이 되면 주황색 부분(A:F)을 복사하여 후잉 입력 최종 홈 시트에 복사를 하거나 후잉에 외부 입력에 붙여넣기 하여 후잉 일괄 입력을 합니다. 메모에는 #일괄 입력이라는 태크를 붙여 입력하면 입력 후에 중복 입력을 막을 수 있습니다.

구성 시트는

1) 은행(범용), 2) 증권사(범용),3) 신용카드(범용)
4) 미래에셋(전용), 5) 신한증권(전용)
6) 예상 데이터 : 이미 입력되어 검색된 데이터를 참조하기 위해 이 시트에 입력해둡니다.
7) 기타 금융기관별 양식 변환 시트들입니다.

날짜의 형식이나 입금, 출금 등 데이터 포맷을 카카오뱅크의 포맷과 일치 시켰습니다.

증권사는 증권사(범용) , 신용카드는 신용카드(범용)을 이용하면 되며 모두 은행 내역 입력과 동작원리는 동일합니다. 
범용 폼은 금융기관별 양식 변환 시트에서 필요한 데이터 변환을 거친 후에 복사하는 방식이고 전용 폼은 내부에 거래내역 Raw 데이터를 직접 붙여 넣고 금융기관별로 약간 특화해서 만든 것입니다.

엑셀 파일은 다음과 같습니다.

whooing_template(수기-공개용).xlsx
0.36MB

구글 스프레드 시트 

 

whooing_template(수기-공개용)

후잉입력 최종폼 날짜,아이템(괄호),금액,왼쪽,오른쪽,메모

docs.google.com

검색 수식 설명

후잉추적(날짜/금액 & 출금)

={MATCH(A2&C2&Q2,후잉내역!A:A&후잉내역!C:C&후잉내역!F:F,0)}

A2&C2&Q2 는 "4437238264기업은행"
후잉내역!A:A&후잉내역!C:C&후잉내역!F:F 는 A2, C2, Q2가 있는 배열입니다. 다음과 같이 구성됩니다.

{ "4437338264기업은행", "4437238264기업은행","4437238264기업은행", ..... }

다음 파라미터 0 은 정확히 일치하는 것을 의미합니다.
MATCH 함수는 일치되는 데이터가 있는 시트의 열을 리턴합니다.
괄호는 배열 수식입니다. CTRL + SHIFT + ENTER를 동시에 누릅니다.

후잉 내역의 중복 데이터 걸러내기

=COUNTIFS(A:A,A2,C:C,C2,F:F,F2,H:H,H2)

COUNTIFS 의 사용법은 COUNTIFS(영역1, 값1, 영역2,값2,...) 이렇는데 배열 수식 함수라고 하네요.

영역 1에 값 1이 존재하는지 체크하고 영역 2에 값 2가 존재하는지 체크해서 AND 처리를 한 후 개수를 리턴합니다.
{ COUNTIF(A:A&C:C&F:F,A2&C2&F2)} 배열 수식과 같다고 보면 됩니다.

위의 검색 수식과 동일한데 MATCHIFS는 함수 자체가 없기 때문에 배열 수식으로 구현한 것입니다. 나머지 수식들은 기본적인 엑셀 함수이므로 셀에 들어가 있는 수식을 뒤적거리다 보면 쉽게 이해 가능할 것입니다

다음 포스팅에서 구체적으로 설명해뒀으니 참고하세요.

https://blog.naver.com/yonghan_shin/221952507580

 

후잉 거래 입력 도움 엑셀(Excel) @ 복식부기 가계부 후잉

한동안 정신이 바빠서 몇 달간의 후잉 입력을 하지 못했습니다. 그래서 금융기관에서 거래내역을 다운 받아...

blog.naver.com

이제 금융기관의 거래내역을 다운로드해서 후잉에 입력할 때 좀 더 시간을 단축할 수 있을 것 같습니다. 후잉 입력이 엄청나게 밀려 있는 분들에게 많은 도움이 되었으면 합니다.

이걸로 1년 치 밀린 것을 넣었는데도 여전히 맞지 않네요. 입력하는데 정말 생 고생을 했답니다. 
맞지 않으면 잔액 맞추기로 하는 수밖에 없겠습니다. 빠진 내역들을 순자산 처리하는 방식입니다. 

 

잔액 맞추기 - 계정별 잔액이 맞지 않는 경우 - 복식부기 후잉가계부

후잉 가계부를 사용하다 보면 깜박하고 거래 내역을 입력이 누락되는 경우나 월 말 결산시 계정별 잔액이 ...

blog.naver.com