특정 데이터를 찾는 가장 편한 방법은 뭐니 뭐니해도 필터입니다. 하지만 필터는 특정 데이터의 모든 항목을 다 봐야 합니다. 가령 특정 모델의 제품명만을 보고 싶더라도 원본 표에 제조사, 금액, 사이즈 등의 항목이 있다면 다 봐야 합니다. 물론 고급 필터의 복사 위치를 사용하면 원하는 것만 볼 수 있습니다. 그러나 고급 필터 사용 시 원본, 즉 목록 범위의 내용이 바뀌더라도 이미 복사된 것의 값은 변하지 않는다는 문제가 남습니다. 그렇다면 고급 필터를 다시 적용해야 하죠.
이런 이유로 다소 불편하지만 어쩔 수 없이 함수를 사용합니다. 원본 데이터가 바뀌면 결과도 자동으로 바꾸어주는 자동화는 작업량을 엄청나게 단축하는 효과가 있기 때문이죠.
이번 강좌는 필터와 유사하지만, 자동화가 가능한 VLOOKUP 함수를 알아보겠습니다.
예제 파일을 위와 같습니다. 하고자 하는 것은 원본 표에서 특정 모델명을 찾아 제품명과 보유 수량을 알아보는 것입니다. 고급 필터를 사용하면 빠르게 처리할 수 있지만 찾고자 하는 모델명이 바뀐다면 다시 고급 필터를 적용해야 하기 때문에 VLOOKUP 함수를 이용해 보겠습니다.
VLOOKUP테이블이나 범위에서 특정 값을 기준으로 원하는 항목의 값을 찾습니다. (부품 번호를 기준으로 부품의 가격을, 회원 ID를 기준으로 주소등을 찾습니다.)
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) | lookup_value | 찾으려는 항목 값의 기준이 되는 값 또는 셀 참조 |
| table_array | 기준값(lookup_value)을 갖는 항목의 첫 셀부터 찾고자 하는 항목의 끝 셀까지의 범위
- 주의! 반드시 기준값(lookup_value)을 갖는 항목을 첫 번째 열로 설정해야 합니다. |
| col_index_num | table_array로 만들어진 범위의 열 번호(좌측 열부터 1, 2, 3, ...) |
| [range_lookup] | [선택] lookup_value 값과 정확히 일치하는 값을 찾을 것인지, 유사 일치 값을 찾을 것인지 결정. (정확히 일치 : FALSE(0), 유사 일치 : TRUE(1))
- 선택 사항으로 작성하지 않으면, 기본 TRUE(1)로 설정됩니다. (기본 : 유사 일치)
- 주의! 대부분 정확히 일치를 사용하기 때문에 FALSE(0)로 설정합니다. (선택 사항이지만 필수처럼 FALSE(0)로 사용합니다.) |
함수의 정리는 위와 같습니다. 처음 VLOOKUP 함수를 접하시면 굉장히 복잡해 보입니다. 알고 싶지 않죠. 하지만 역시 논리적으로 따져 보고 몇 번 사용해 보면 이것도 사람이 만든 것이니 못 할 것도 없습니다.
작업 단계를 간단하게 정리하면 위 그림처럼 2가지 단계로 구분할 수 있습니다. 모델명이 SM-S721의 제품명을 찾고자 한다면,
- 먼저 원본의 표에서 기준값부터 찾아야 합니다. 당연히 모델명 항목의 데이터 중에서 찾아야겠죠.
- 기준값을 찾았다면 그 기준값으로부터 찾고자 하는 항목(제품명)으로 이동하여 보면 됩니다.
여기서 찾을 기준값이 lookup_value입니다.
그리고 찾을 기준값을 항목인 모델명부터 찾고자 하는 항목인 제품명까지가 table_array가 됩니다. 위 그림에서 노란색 부분이죠.
이 table_array는 2개의 열로 구성된 범위입니다. 이 열의 순서가 col_index_num입니다. 1은 모델명, 2는 제품명입니다.
그럼, 실제 찾고자 하는 제품명은 table_array에서 col_index_num을 통해 알 수 있다는 결론이 됩니다.
그리고 마지막 range_lookup은 lookup_value로 설정된 값을 table_array 첫 번째 열에서 찾을 때 비슷한 값을 찾을 것인지 정확한 값을 찾을 것이지 결정하는 옵션입니다. 대부분 당연히 정확한 값을 찾습니다. 비슷한 값은 찾는 경우는 거의 없죠. 그러니 선택사항이지만 필수사항처럼 정확한 것을 찾는다고 명시해 주는 것이 일반적입니다.
나름 열심히 정리했지만, 여전히 어렵게 느껴지실 것으로 생각됩니다. 직접 해보시면 좀 더 이해가 쉽습니다.
- VLOOKUP을 작성할 셀(I5)을 클릭하여 선택합니다.
- 수식 입력줄에서 수식을 시작하고 함수명을 작성합니다. => =VLOOKUP(
- 첫 번째 인수 힌트(lookup_value : 검색할_값)를 확인하고 찾고자 하는 기준값 셀(H5)을 클릭하여 참조합니다. => =VLOOKUP(H5
- 쉼표로 구분, 두 번째 인수 힌트(table_array : 표_범위)를 확인하고 찾고자 하는 기준 항목의 끝까지 범위(B5:C18)로 선택합니다. => =VLOOKUP(H5,B5:C18
(참고, table_array의 범위는 불필요하지만, 표의 끝 항목(금액)까지 참조해도 됩니다. 어차피 찾는 열은 제품명까지이므로 제품명만 포함되게만 설정하면 됩니다. 물론 불필요한 열은 제외하는 것이 좋습니다. 하지만 안될 것은 없다는 것도 기억해 주세요.) - 쉼표로 구분, 세 번째 인수 힌트(col_index_num : 열_인덱스_번호)를 확인하고 찾을 항목의 열 번호(2)를 입력합니다. => =VLOOKUP(H5,B5:C18,2
- 쉼표로 구분, 네 번째 인수 힌트(range_lookup : 범위_검색)를 확인하고 0을 입력하거나 FALSE를 선택합니다. => =VLOOKUP(H5,B5:C18,2,FALSE
(참고, 논릿값에서 0은 FALSE를, 1은 TRUE를 의미합니다. 즉 0과 1을 사용하여 FALSE와 TRUE를 대신할 수 있습니다.) - 복잡해 보이지만 함수는 하나입니다. 최종 엔터(닫힌 괄호 자동 생성 후 셀 완성) => =VLOOKUP(H5,B5:C18,2,FALSE)
결과, 그림처럼 제품명이 잘 반환됩니다.
이제 나머지 제품명은 자동 채우기를 이용하면 바로 알 수 있습니다. 자동 채우기를 진행합니다.
그런데 자동 채우기를 하면 몇 개 데이터에 에러가 발생합니다. 왜일까요?
아마 강좌를 쭉 보신 분들은 눈치채셨을 것 같습니다. table_array를 참조할 때 상대 참조로 했기 때문이죠. LARGE와 SMALL 함수 편에서 이미 알아본 내용입니다. 범위를 참조하고 자동 채우기를 할 때는 대개 절대 참조해야 한다는 것이었죠.
참조를 적용한 셀을 더블 클릭하거나, 셀 클릭 후 수식 입력줄을 클릭하면 참조 셀을 확인할 수 있었습니다.
참조 후 자동 채우기를 하면, 참조 셀이 행으로는 숫자가 열로는 알파벳이 증가하는 원리가 있었죠. 범위도 마찬가지였고요.
위 VLOOKUP 함수는 2가지를 참조했습니다. lookup_value와 table_array였습니다. lookup_value는 행이 바뀜에 따라서 참조 셀의 위치가 아래로 하나씩 상대 참조되는 것이 맞습니다. 하지만 table_array의 범위는 위 그림처럼 행이 바뀜에 따라 범위도 아래로 바뀌면 안 됩니다. 위의 경우에서는 "SM-S931, SM-S721, SM-F966B"는 설정 범위에서 찾을 수 없습니다. 즉 table_array에서 lookup_value을 찾을 수 없단 얘기죠. 당연히 에러가 발생할 수밖에 없습니다.
그래서, 위와 같이 자동 채우기를 진행해야 한다면 table_array 범위는 고정해야 합니다. 절대 참조를 해야 한다는 것입니다.
- 함수를 작성한 첫 셀(I5)셀을 다시 클릭하여 선택합니다.
- 수식 입력줄에서 table_arrary 인수인 B5:C18를 클릭 드래그하여 블럭 선택 후 키보드 f4키를 눌러 절대 참조로 변경하고 엔터 => =VLOOKUP(H5,$B$5:$C$18,2,0)
- 대표 수식을 바꾸었으니, 나머지도 다시 자동 채우기로 변경해야 합니다. 다시 I5셀을 클릭하고 우측 하단에서 I15셀까지 클릭 드래그하여 자동 채우기.
결과, 그림처럼 에러 없이 정확히 제품명을 반환해 줍니다.
그래서 자동 채우기를 진행할 경우, 상대 참조, 절대 참조, 혼합 참조를 상황에 따라 항상 유념하셔야 합니다. 자동 채우기를 하기 전에 항상 참조 방식을 생각하는 습관을 들이시길 바랍니다.
이제 함수로 자동화된 것의 장점을 보겠습니다.
그림처럼 모델명이 바뀌어도 제품명이 자동으로 변경되는 것을 알 수 있습니다.
고급 필터를 사용했다면 복사된 결과는 바뀌지 않습니다. 결과를 확인하기 위해서 결과를 지우고 다시 또 고급 필터를 사용해야 합니다. 하지만 함수를 사용했으니 더 이상 작업할 필요가 없어진 것입니다. 물론 원본의 값이 바뀌어도 자동으로 반영됩니다. 굉장한 장점이죠.
다음, 보유 수량 차례입니다. 이것도 역시 VLOOKUP 함수를 사용하면 되겠죠.
제품명과 거의 유사하게 사용하시면 됩니다. 달라지는 것은 table_array의 범위와 col_index_num입니다. 직접해 보시기 바랍니다.
대표 셀의 함수만 확인하겠습니다.
=VLOOKUP(H5,$B$5:$E$18,4,FALSE)
성공 하셨길 바랍니다.
의견글 입니다.
()
