VLOOKUP 함수 예제를 통해 익히기

VLOOKUP 함수 예제를 통해 익히기

오늘은 실무에서 많이 쓰이는 VLOOKUP함수에 대해서 알아보겠습니다.  사무에서나 오픈마켓 데이터처리를 할때도 많이 쓰이는 함수입니다..  간단한 예제 하나와  실무에서 바로 적용할수 있는 예제 두개를 준비하였습니다. 

 

 

VLOOKUP 함수 예제를 통해 익히기

 

 

VLOOKUP 함수 기본개념 

별도의 참조자료를 토대로 필요한 데이터를 찾아 가져오는 기능을 갖고있습니다.  

기본구문	설명
=VLOOKUP(기준값, 참조범위, 열번호, 논리값)	셀범위나 배열에서 찾을 값에 해당하는 행을 찾은후 열번호에 해당하는 셀의 값을 구하는 함수

 

VLOOKUP 실전예제1

 

위의 그림은 VLOOKUP함수를 설명하기위한 간단한 예제로 위의 표는 각점포별 현황을 나타내고 , 아래표는 각 점포별 순이익집계표를 보여줍니다.. 아래의 표는 이미 마련되야할 참조데이터로서 이데이터를 참조하여 찾고자하는 데이터를 가져오는 기능이 있습니다.

 

우선 위의 표 각 점포별 현황의 표의 머리글을 보면 점포코드, 점포명, 언락처, 상반기순이익, 하반기순이익이 있습니다.  여기서 찾고자 하는 데이터는 각점포별 상반기순이익과  하반기순이익입니다.

 

D4셀에 강남역점 상반기순이익을 구하려면 D4셀= VLOOKUP(B4,B21:D29,2,0)  을 입력합니다.  이 구문은 B4셀은 강남역점즉 찾고자하는 셀이죠... 이셀과 일치하는 셀을 찾아 찾고자하는 데이터를 가져오겠다는 뜻입니다. 

 

다음에 B21:D29는 아래 표의 머리글인 점포명 아래 데이터인 강남역점의 셀부터 D29셀까지를 참조범위로 설정한 것입니다. 다음은  설정한 참조범위중에 첫번째열은 점포명열의 데이터이고 , 두번째열은 상반기 순이익열의 데이터이고, 세번째열은 하반기순이익열의 데이터입니다..

 

여기서는 2 즉 두번째 열인 상반기순이익의 데이터값을 가져오는 것이므로 2가 되었고요.. 만일 3을 쓰면 하반기 순이익열의 데이터가 됩니다..  

 

위의 표는 편의상 각점포별 현황표의 데이터와 각점포별 순이익집계표의 데이터는 순서와 데이터량이 동일한데,, 실무에서는 서로 동일하지 않는 경우가 많습니다... 참조데이터가 훨씬 많은 양이 되는 경우가 많고, 또 순서도 동일하지 않는 경우가  대부분입니다.. 하지만, 어떤 경우든 결과값은 동일하게 나옵니다..

 

 

D4셀에 VLOOKUP함수를 적용했을 때 나온 결과물입니다.. 즉 =VLOOKUP(B4,$B$21:$D$29,2,0) 이 식은 위에서 언급한 식과는 조금 달라보이는데요...

 

이유는 참조영역을 설정할때 싱대영역인 B21:D29를 쓰면 D4셀에는 동일하게 82500000으로 동일한 값을 덛을수 있지만, 자동채우기기능으로 D12까지의 셀을 채우고 나면 원하는 값을 얻을수 없습니다..

 

이것은 상대참조를 했기 때문이며,, 그래서 B앞에 $를  21앞에 $를 기입한 것입니다. 이것을 절대참조를 한것입니다.... 그러면 자동채우기로 나머지 데이터를 채워 넣더라도 참조영역의 데이터를 정확하게 찾을수 있게 되겠죠.. 

 

 

위의 D4셀에 절대참조를 이용하여 강남역점의 상반기순이익을 구하는 기본식이 됩니다.. 이런 쉬운 예제를 통해 기본개념을 익혔으면 다음에는 실무에서 많이 쓰이는 예제로 한번 더 해보겠습니다.

VLOOKUP 실전예제2

 

 

이번에는 각오픈마켓의 발송처리엑셀화일을 택배사로부터 넘어온 발송처리자료 즉 택배사와 송장번호를 가져와서 오픈마켓의 대량발송처리하는데 많이 활용되고 있는 예제화일입니다. 

 

위의 표는 오픈마켓의 발주데이터입니다.. 아래의 그림은 택배사가 송장을 출력하고 넘겨준 발송처리자료입니다. 

 

 

오픈마켓 발송처리리스트라고 가정해보겠습니다. 위의 데이터는 오픈마켓의 발주데이터와는 일치하지 않는 경우가 대부분입니다. 보통은 오픈마켓에서 가져온 발주데이터가 더 많은 경우가 많죠..

 

이유는 공급사에 발주를 넣어도 공급사마다 택배사마다 발송마감시간이 제각각 다르고 재고유무에 따라  발송처리가 다르기 때문입니다..

 

그럼 공급사 또는 택배사로부터 넘어온 발송처리데이터를 참조하여 우리가 찾고자하는 데이터인 택배사와 송장번호만 가져오면 되겠습니다.

 

위의 그림들은 편의상 예시일뿐이고,, 실무에서는 데이터순서나 양이 맞지 않는 경우가 많습니다.. 

 

그럼 우선 택배회사를 가져오는 참조범위를 이름이 다른 시트에 저장해놓았다면, 그 시트이름과 참조범위를 절대참조를 이용하여 참조하면 됩니다.. 

 

 

위의 그림처럼 찾고자 하는 데이터를 가져다 놓는 시트는 오픈마켓발송처리시트에 저장해놓고,  공급사나 택배사로부터 받아온 자료는 balsonglist라는 별도의 시트를 생성하여 저장해 뒀습니다. 

 

 

위의 그림은 오픈마켓에서 내려받은 발주내역입니다.  택배회사와 송장번호가 비어있는 걸 확인할수 있습니다.  여기 빈칸들은 택배사로부터 가져오는 발송처리리스트의 참조영역을 VLOOKUP함수를 이용하여 가져올것입니다.

 

그럼 D4셀의 기본식은 다음과 같습니다.. D4 =VLOOKUP(G4,balsonglist!$E4:$G10,2,0) 입니다. 여기서 끝의 0대신 FALSE를 입력해도 됩니다. 

 

 

택배사, 또는 공급사로부터 넘어오는 발송처리리스트

우리가 원본데이터와 참조하려는 데이터를 정확히 일치시켜주는 필드는 무엇일까요..?? 받는사람은 동명이인이 있을수 있고,  주문코드도 유일한 필드는 아닐것입니다..

 

그중에 연락처는 유일하게 구분시켜주는 데이터가 될것이고,, 그래서 G4셀을 기준으로 찾기를 시도할 것입니다..

 

 

그럼 해석하자면 G4셀을 기준으로 BALSONGLIST시트의 E4셀부터 G10셀까지의 참조범위를 두고 그 범위에서 두번째열에 있는 값과 정확히 일치하는 데이터를 가져오겠다는 의미입니다.

 

그럼 D5셀은 D4셀을 자동채우기 핸들러를 이용하여 구할수 있습니다... 식은 다음과 같습니다.

 

=VLOOKUP(G5,balsonglist!$E5:$G11,2,0)  상대참조인 G4셀에서 G5셀로 변하는 걸 볼수있습니다. 참조범위인 BALSONGLIST시트인 $E5:$G11 는 절대참조를 이용했습니다..

 

 

 E4셀의 송장번호도 이와 같은 방법으로 구할수 있습니다..  이렇게 해서  참조데이터에서 가져온 택배회사와 송장번호를 가지고 각 오픈마켓에 대량발송처리도 가능할것입니다..

 

각 오픈마켓별로 제공하는 발송처리양식이 제각각 다르므로 그양식에 맞는 양식으로 올려야합니다...  다른 마켓의 발송처리도 이런 개념을 익혀두었다면 충분히 응용하여 많은 발송데이터도 일괄처리가 가능할것입니다..

 

VLOOKUP함수와 매우 닮은 꼴인 HLOOKUP함수와 비교하여 익히면 좋습니다..  HLOOKUP함수는 행방향의 참조테이블에서 원하는 값을 찾는 함수입니다.. 사용방법은 VLOOPUP함수와 동일합니다..