입금문자! 어떻게 정제할 것인가? 데이터를 뽑아 조합하려는 경우!

입금문자 데이터정제 취지

입금부서의 경우 입금 시 바로 타 부서 혹은 고객에게 전달해야 할 경우가 있다. 이부분을 엑셀로 다른 형식으로 변환하여 보낼 수는 없을까? 고민해 보았다. 멘트의 경우는 엑셀로 충분히 수식으로 원하는 형식으로 작성할 수 있다.

과거에 이런 단순한 업무에 대한 노가다가 있어서 염증을 느끼고 고안해낸 방법이다. 문자를 복붙하면 각 데이터가 분류가 되고 이것을 조합하여 보고하는 형식으로 만들 수 있고 이 형식을 그냥 복붙만 하면 아주 간단하게 처리할 수 있기에 소개하고자 한다. 누구에게는 반드시 의미가 있을 것이라고 확신한다.

보통 하단과 같이 문자가 오고 이에 대하여 보고하는 형식을 할 것이다.

[Web 발신]

은행명 05/11 15:44

업체명

입금 443,234

잔액 1,233,123 입금명

이 문자를 보고 엑셀시트에 작성할 것이다. 은행명, 날짜, 업체명, 입금금액, 입금명 이런 식으로 보고 타이핑한다. 이런 문자의 건수가 많아지면 어떻게 하겠는가? 다 타이핑해서 작성할 것인가? 시트가 여러개 있다면 그리고 그 시트마다 입력해야하는 경우가 다르다면 어떻게 하겠는가? 이 부분에 있어서 고민 후 효율적으로 어떻게 해야하는가 생각을 해야한다.

문자의 내용은 컴퓨터로 복사해야하는데, 일일히 다 붙여넣을 수가 없다. 윈도우의 기능중 모바일이라는 것이 있다. 그것을 연동시켜놓으면 컴퓨터에서도 실시간으로 문자의 내용을 볼 수 있어서 장점이다. 그리고 그 문자를 복붙하여 데이터를 가공한다면 더 효율적으로 하게 될 것이다.

우선 위의 문자내용을 가공을 해야한다. 한 셀안에 넣어지게 되므로 데이터를 뽑아내기 위해서는 각각의 속성에 맞는 함수를 쓰는게 맞다. 우선 은행명을 뽑아내보자. 관련 함수는 FIND가 가장 최적의 함수다. 관련 글자를 찾아서 위치를 찾아내 출력을 하게할 수 있다.

 

문자의 데이터 정제하는 방법

은행명의 전후를 살펴보면 웹발신과 날짜가 있다. 여기서 날짜로는 참고하기 어렵고 스페이스바로 여러군데 있어서 정말 힘든 구조로 되어있다. 그런데 일률적으로 적혀있는것이 웹발신이라는 문구이다. 저것을 참고로하여 찾아낸다면 은행명을 뽑아낼 수 있을 것이다.

은행명 뽑아내기 함수

우선 은행명에서 첫번째 글자인 "은"이라는 글자 자릿수를 찾고, 마지막 글자인 "명"이라는 글자 자리수를 찾아서 MID함수를 통해 뽑아내는 방법이 있다. "은"이라는 글자는 문자의 공통으로 보내지는 웹발신의 마지막글자인 "]"라는 글자를 참고해 자릿수를 추출했고, "명"이라는 글자는 뒤에 월일구분자인 "/"를 참고해 자릿수를 추출했다. 이 부분은 고정적인 틀이므로 이용할 가치가 있는 것이다. 이런 방법으로 일자와 시간, 업체명, 금액 등 모든 것들을 각 셀로 뽑아낼 수 있다.

일자 뽑아내기 함수

일자는 쉽다. 월일구분자인 "/" 이것을 찾은다음에 MID함수로 앞뒤 글자수를 지정하여 추출할 수 있다. 만일 다른 곳에서 "/"구분자를 사용하는 경우에는(뒤에 나오는 것은 상관없음) 사용이 어려울 수 있다. 시간도 응용하여 추출할 수 있다. 위 함수에서 "/"대신에 ":"로 바꾸어주면 된다.

업체명, 입금액, 잔액의 경우에는 앞뒤 구분자를 찾아서 위와같은 방식으로 지정해주면 바로 구할 수가 있다.

업체명 뽑아내기 함수

업체명의 경우는 앞에있는 고정적인 시간구분자 ":"를 기준으로 찾았지만, 다른 구분자를 이용해도 상관은 없다. 웹발신이나, "/"써도 된다. 단지 그마만큼 자릿수를 조정해주어야한다. 그리고 뒤에있는 입금이라는 단어를 찾아서 MID함수로 지정해주었다. 업체명의 글자수가 어떻게 되든지 정상적으로 출력이 될 것이다.

입금액과 잔액도 똑같은 방식이므로 생략하겠다. 이제 남은 것은 맨 뒤에있는 입금명이다. 이 부분은 애매한 부분이 있다. 뒤에 구분자도 없고 입금명의 글자 자릿수도 미지수이고(최대 6글자만 출력된다는 가정이 있다고 하지만 이부분도 미지수) 어떻게 구해야할까? 첫번째 방법은 "잔액" 글자의 위치를 구해서 +1 해준 후 전체에서 빼면 잔액 숫자부분부터 맨 뒤까지의 글자를 뽑아낼 수 있게되고 그 글자에서 스페이스바의 자릿수를 추출하면 입금명의 앞자리 숫자를 구할 수 있게된다.

입금명 추출방법

상당히 복잡해보일 수 있다. 입금명의 글자수가 이미 주어졌다면 RIGHT함수로 바로 때리면 나오지만 글자수가 어떻게 될 지 모르기에 앞에서 힌트를 얻어야한다. 앞에는 잔액이라는 것이 있는데 금액도 업체마다 다르기에 참고데이터로 절대로 쓸 수가 없다. 확실하게 픽스되어있는 "잔액"이라는 글자와 중간에 띄어쓰기 되어있는 스페이스바 " "이것이 전부이다.

따라서 잔액이라는 위치를 먼저 구해야하고, 그 위치부터 끝까지 글자를 추출한 다음에 그 글자에서 스페이스바 " "위치를 찾고 그 위치에서 끝자리까지 구하는 함수 RIGHT를 쓰면 된다. 위와같이 작성하면 입금명의 자릿수에 상관없이 올바르게 추출된다는 것을 볼 수 있을 것이다.

 

보고하는 방법

이렇게 각 셀로 뽑아낸 데이터들을 각 업무에 맞게 조합하면 완성이다. 조합할 때 많이 쓰는 연결자는 "&"라는 것이다. 마음대로 붙여넣기 할 수가 있는 것이다.

보고하는 방식

연결할 때는 "&"를 사용하면 되고, 글자의 경우는 ""의 사이에 쓰면 된다. 날짜와 금액, 시간의 경우에는 형식이 틀어질 수 있으므로 TEXT라는 함수로 형식을 정해주면 보기 좋게 바뀌게 된다. 이렇게 한 셀에 보고양식이 들거가게되고 셀을 복붙하면 원하는 보고 폼이 완성이 된다.