엑셀은 데이터 작업을 편하게 하는데 도움을 많이 줍니다. 특히 엑셀에서 제공하는 함수의 기능을 제대로 이용한다면 엄청 골치 아프고 노가다가 되는 작업도 손쉽게 처리가 가능합니다. 엑셀에서 자주 사용되는 함수 중의 하나인 VLOOKUP 함수의 사용법에 대해 알아보겠습니다.
VLOOLUP 함수 사용법
VLOOKUP함수를 엑셀 도움말에서 살펴 보면 "표 배열의 첫째 열에서 값을 찾고 표 배열의 다른 열에 있는 같은 행에서 값을 반환합니다. "라고 합니다. 이게 무슨 뜻인지 예를 이용하여 알아보겠습니다.
아래와 같은 시트가 있다고 가정합니다. 아래의 시트는 어느 가게의 하루의 판매액을 알아보기 위해 작성되었습니다.
우측의 제품 정보에는 가게에서 판매되고 있는 제품들의 단가등의 정보가 기록되어 있고 이 표를 참고 하여 왼쪽 표에 7월 13일의 판매현황을 매출표를 작성하는 예입니다.
오늘 하루 판매한 물건이 AA 1개, AC 3개, BA 5개 CC 1개라고 가정하면
위의 빨간 박스와 같이 입력할 수 있겠습니다. 다음으로 단가를 입력하면 합계에서 수량*단가가 자동으로 계산되어 합계와 매출 총합이 구해질 것입니다.
뭐 이 가게는 장사가 잘안되는 가게라서 옆에 표를 보고 단가 일일이 쳐 넣어서 매출을 구해 줄 수도 있겠습니다. 하지만 장사가 잘되서 하루에 수십, 수백가지 종류의 물건들이 판매되었다면? 엄청난 노가다가 됩니다. 엑셀은 노가다를 줄이기 위한 프로그램입니다.
이럴 경우에 VLOOKUP함수를 사용하여 손쉽게 구할 수가 있겠습니다.
d4셀에 =VLOOKUP(b4,$h$4:$J$12,3)을 입력한 뒤 마우스로 끌어서 채우기를 하면 아래와 같이 각 제품별 단가가 자동으로 입력되어 하루 매출이 계산되게 됩니다.
VLOOKUP 구문 해석
엑셀에서 제공하는 도움말에 VLOOKUP함수는 아래와 같은 형식의 구문으로 작성하게 되어 있습니다.
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
인자 요소들을 하나씩 해석해보자면
lookup_value : 찾을 값
table_array : 찾을 값에 대응하는 데이터를 참조할 범위
col_index_num ; 범위 내에서의 열 번호
range_lookup : 0 or 1 논리값으로 찾을 값이 정확하게 일치값인지 정확하게 일치값 or 근사값인지를 결정
=VLOOKUP(b4,$h$4:$J$12,3)을 다시 활용하여 설명하면
우선 $h$4:$J$12는 찾을 값에 대응하는 여러 데이터들의 범위로 찾을 값이 대응되는 값들이 1열에 옵니다. 여기서는 제품코드가 되겠습니다.
b4=AA는 찾을 값으로 위의 범위에서 1열 중에 b4의 값 AA를 찾게 됩니다.
3은 범위에서 열의 번호로 범위 첫열이 1열로 시작하여 2, 3, 4... 씩으로 증가하여 입력할 수 있으며 찾을값의 행 일치하는 해당열의 값을 반환하게 됩니다.
종합하면 table_array 내에서 lookup_value와 일치하는 값의 행에서 출발하여 col_index_num만큼 이동한 열의 값을 찾는 것입니다.
range_lookup은 =VLOOKUP(b4,$h$4:$J$12,3) 수식에 =VLOOKUP(b4,$h$4:$J$12,3) 뒤에 0이나 1의 값을 추가 할 수 있는 옵션인데
1을 추가하는 것은 안쓰는 것과 같으므로 의미가 없고 =VLOOKUP(b4,$h$4:$J$12,3,0)과 같이 사용하게 됩니다.
0의 옵션을 주게 되면 범위의 첫열에서 lookup_value와 정확하게 일치하는 값만 검색하게 됩니다. 정확하게 일치하는 값이 없을 경우 N/A 오류 값이 반환됩니다.
1이나 옵션을 추가하지 않게 되면 정확한 값이나 근사값 모두를 검색하게 됩니다. 정확하게 일치하는 값을 검색하고 정확하게 일치하는 값이 없으면 lookup_value보다 작으면서 그 다음으로 가장 큰 값을 반환합니다. 이때 중요한 것이 table_array의 첫 번째 열 값은 오름차순으로 정렬해야 합니다. 그렇지 않으면 VLOOKUP 함수를 실행하여 올바른 결과를 얻을 수 없습니다.
0과 1의 활용에 대해 헷갈릴 수가 있는데 0은 정확한 값만 +1한 1은 옵션이 +1 되어 정확한 값에 근사값도 검색한다고 생각하면 되겠습니다.
VLOOKUP함수 응용
위의 range_lookup은 어떻게 이용되는 것인지 알아보겠습니다.
아래 그림과 같이 오른쪽에 점수에 따른 등급표가 있고 왼쪽 표에는 학생들의 성적이 나와 있습니다.
50점미만이면 미달, 50이상 ~60점미만은 5등급 .... 해서 90점 이상은 1등급을 부여하도록 성적표를 만들려고 하는데 vlookup함수를 이용하면 등급을 간단히 매길 수 있습니다.
아래와 같이 C4셀에 =VLOOKUP(B4,$F$4:$G$9,2,1)과 같이 입력하고 마우스 끌어서 채우기를 하면 등급이 자동으로 매겨지게 되는데 0옵션을 주게 되면 일치하는 값만 검색하므로 원하는 값을 얻을 수 없게 되고 1을 주게 되면 다음으로 가장 큰 값을 기준으로 검색하게 되어 원하는 값을 얻을 수 있습니다.
끝으로 위에서 언급한대로 근사값을 검색하려면 아래와 같이 table_array의 첫 번째 열 값은 오름차순으로 정렬해야 합니다. 또한 table_array 범위는 $F$4:$G$9와 같이 절대참조로 하는 것이 오류를 범하지 않는 길입니다.
최근댓글