[엑셀과블로그] vlookup을 이용한 데이터 자동입력,입금확인

[엑셀과블로그] vlookup을 이용한 데이터 자동입력,입금확인

뜬금없는 엑셀과 블로그라는 주제로 글을 써보려고 합니다.
엑셀은 일상적인 일부터 업무까지 다양하게 사용이 가능합니다.
저는 블로그를 운영하면서 엑셀을 종종 사용하고 있는데
포스트관리나 포스트 재활용, 입금확인(통장정리), 주문, 매출정리, 포스팅등에 주로 활용등 아주 다양하게 활용을 하고 있습니다. 

오늘은 vlookup()이라는 엑셀 함수를 이용하여 특정값을 찾아서 값을 자동으로 입력하는 방법을
소개할까 합니다. 

[내용정리]

• 업무에 많이 활용되는 VLOOKUP의 기능과 활용법 익히기
• 이용사례 : 통장입금 확인 쉽게 하기

저와 같이 블로그관련일에 활용할 분들이나 업무처리에 도움이 되길 바라겠습니다.

VLOOKUP 함수

표 배열의 첫번째 열(세로) 값을 찾아 다른열에 있는 값을 구해주는 함수
(a열에서 값을 찾고 b열의 값을 구해주는 함수) 

사용형식> VLOOKUP(찾을값,코드표,코드표중 출력할 값,찾는 방법)

• 찾을값:   문서중 찾을 데이터나 위치
• 코드표 : 참조에 사용할 코드표로 첫번째 열에 입력한 내용은 검색의 대상이 되는 열
• 코드표중 출력할 값:  비교값과 같은 행에 있는 값을 표시할 코드테이블의 열 번호
• 찾는 방법: 정확하게 일치하는 값을 찾을때는 0(false), 근사값을 찾을때는 1(true)

사용예) 상품테이블을 이용하여 매출내역의 G열에 상품명을 입력해보자!

상품테이블 : 상품리스트를 위와 같이 A5셀부터 C9셀까지 미리 입력해놓음
매출내역 : 매출내역은 매일 추가되는 내용으로 여기서는 E열과 F열에 입력한다.

G5셀에 수식입력 : =VLOOKUP(E5,$A$5:$C$9,2,0)  (위 그림 참조)

설명 : E5셀에 입력된 값을 코드테이블(A5:C9)에서 찾아서 2번째 열의 값(상품)을 구해주는 식

수식을 입력후 복사하면 간단하게 상품명이 입력이 된다.

[결과]

 

블로그 관련 이용 사례 : 입금내역을 엑셀로 정리

저는 vlookup()을 아주 많이 활용합니다. 그 중에서 입금확인하는 사례를 하나 들어보도록 하겠습니다.

수익블로그의 경우에 이용하는 수익모델에 비례하여 입금되는 회사의 수도 많아집니다.
그런데 입금내역을 보면 이용하는 수익모델 사이트의 이름과 입금자가 틀린경우가 많습니다.
예를 들면 "아이라이크클릭"은 통장 입금자란에 "(주)인터랙티비"로 표시가 됩니다.
이렇게 사이트와 입금자가 다른 경우가 많다보면 서로 매치가 되지 않아서 입금확인이하기가 매우 불편합니다.

저는 은행사이트에서 입금내역을 엑셀로 다운로드 하여 엑셀에서 vlookup을 이용하여 쉽게 입금확인도 하고..
입금내역을 보관하고 있습니다.

비고란에 입금확인을 알기 쉽게 정리

 

[블로그팁] - 블로그로 돈벌기 제휴마케팅! 그 가능성을 보다
[블로그팁] - 제휴마케팅으로 돈버는 노하우

해결방 방법 >>

1. 은행에서 입금내역을 엑셀로 저장한 다음 파일을 연다.

2. 코드테이블을 만든다.
   ( I열에는 I4셀부터 입금자 이름을 정리하고.. J열에는 J4셀부터 입금자에 해당하는 회사(사이트이름)을 정리 )

[코드테이블]

3. 아래와 같은 입금내역의 끝(G열)에 비고란을 하나 만든다음 다음과 같이 수식을 입력한다.

  G4셀에 입력 : =vlookup(b4,$I$4:$J$10,2,0)

문의가 있어서 이곳에 정리합니다.  =vlookup(b4, $I$4:$J$10, 2, 0)는  B4셀에 있는 데이터를 코드테이블( $I$4:$J$10 ) 에서 찾아서 두번째열(2)의 값을 구하되, 검색시 정확한(0) 값만 검색하라는 의미이다. 즉 2, 0에서 2는 코드테이블에서 두번째열의 값을 구하는 것이고.. 0은 정확한 값만, 1은 정확하거나 근사값을 구해주는 검색옵션입니다.

 

은행 입금내역

4. 수식을 입력한 다음 아래로 복사를 하면 된다.

에러처리 >>

수식을 복사했는에 아래와 같이 #N/A 오류가 발생하는 경우는 찾는 값이 코드테이블에 없기 때문에 발생한다.

이렇게 에러가 발생할때는 ISERROR() 함수와 IF를 이용하여 처리할 수 있다.

G4셀에 입력된 수식을 아래와 같이 수정한 후에 다시 복사를 한다.

 =IF(ISERROR(VLOOKUP(B4,$I$4:$J$10,2,0)),B4,VLOOKUP(B4,$I$4:$J$10,2,0)) 

결과는 아래와 같이 깔끔하게 입력이 됩니다.

이후 부터는 입금내역을 아래로 추가한 후에 수식을 복사만 해주면 됩니다.

이상 vlookup을 모르는 분들을 위한 간단한 활용법이었습니다.
저의 경우는 주로 매주 월요일날 입금확인을 합니다.
저와 같이 입금확인에 활용하는 분도 계실테지만..
다른용도로 무한하게 이용이 가능하므로 활용처를 찾아보시기 바랍니다.

참고로 작업 동영상을 추가 합니다.

2010/02/26 - [엑셀과블로그] 하이퍼링크함수, 문자결합

덧.

- VLOOKUP의 사용법을 알아두면 여러모로 활용이 가능합니다.
- 반복적이고 지속적인 업무에 이용시 이용해야 효과적입니다.
- HLOOKUP은 찾을 값들을 가로로 배치한 경우 사용합니다.
- 제 통장에 저렇게 돈이 들어온다고 믿지 마십시요.

엑셀 온라인동영상 공부사이트