여전히 엑셀은 모든 실무의 기본 중 기본이다.
어떤 일을 하던 엑셀이 안들어가는 곳은 없는 법!
실무에서 가장 많이 활용되는 함수인 vlookup 함수에 대해 쉽게 사용하는 방법과 자주 발생하는 오류들이 무엇이고,
왜 이런 오류들이 발생하는지, 어떻게 해결하면 좋을지에 대해 알아보겠다.
엑셀 vlookup 함수
vlookup 함수는 실무에서 활용하는 다양한 함수 중 가장 기본적이면서 많이 사용되는 함수 중 하나이다.
엑셀에 표출되어 있는 수 많은 데이터 중에서 내가 원하는 값을 딱 매칭해서 보여주고 싶을 때 주로 사용한다.
쉽게 원하는 값을 찾아주는 함수라고 보면 된다.
보통 웹이던 문서던 키워드 들어가있는거 찾으려고 할 때 "컨트롤 + F " 통해서 검색하는 단어를 바로바로 찾는 것과 비슷하다고 볼 수 있다.
함수 구문은 아래와 같다.
=vlookup(찾을값, 참조범위, 열번호, 일치옵션)
- 찾을값 : 내가 찾고 싶은 값
- 참조범위 : 원본데이터 전체. 단, 찾을값이 가장 왼쪽 첫 번째 열에서 시작해야 함
- 열번호 : 찾을값에 매칭되는 열의 번호. 가장 왼쪽이 1부터 시작
- 일치옵션 : 정확히 일치할 때 찾아오고 싶다면 'FALSE' 또는 '0' 으로 입력. 미입력 시 TRUE가 기본
예제를 통해 쉽게 적용해보자.
아래는 공공테이터 포털에서 다운로드 받은 '서울특별시 관광 명소' 데이터이다.
공공데이터 포털에서 다양한 공개 데이터들을 받을 수 있으니 예제 데이터로 활용하기도 좋고 지자체 정보들을 얻기도 좋다.
서울특별시_관광 명소_20210904
서울의 랜드마크, 고궁, 역사적 장소, 오래 가게, 미술관, 박물관 등 놓칠 수 없는 서울의 명소 장소 정보를 각종 언어로 소개
www.data.go.kr
원본데이터는 1,903개나 되는 데이터를 가지고 있다.
많다면 많고 적다면 적은 데이터겠지만 아마 회사 실무에서는 훨씬 많은 데이터들일 것이다.
여기서 내가 원하는건 상호명 기준으로 신주소만 알고 싶다면!!
C 컬럼에 있는 상호명 기준으로 E 컬럼에 있는 주소를 매칭하여 찾아오고 싶은 것!
저 복잡하고 많은 데이터 중에서 나는 상호명 기준으로 주소만 가져오면 되는 것!
이럴 때 주소를 바로 찾기 위해 vlookup 함수를 쓰는 것이다.
1. 찾을값 선택하기
첫 번째 선택해야 하는건 찾을값!
나는 상호명인 B 컬럼의 4번 열인 "PKM갤러리"의 주소를 알고 싶으니 찾을 값은 'B4' 가 된다.
=vlookup(B4
2. 참조범위 선택하기
두 번째 선택해야 하는건 참조범위!
전체 원본 데이터를 드래그해서 전부 선택을 해도 상관 없지만,
내가 찾을값과 원하는 결과값은 C 컬럼에서 E 컬럼 사이에 모두 존재하니 딱 그렇게만 범위 선정을 해도 좋다.
단, 참조범위에서 찾을값은 가장 왼쪽 열인 첫 번째 열부터 시작해야 한다!
찾을값이 포함된 C 컬럼부터 내가 찾고싶은 값인 E 컬럼의 주소까지 드래그 한 뒤 'Ctrl + Shift + ↓' 를 눌러 전체 선택을 해주었다.
그리고 내가 한 값만 찾을게 아니고 vlookup 함수를 적용하여 결과를 도출한 뒤 다른 값들에도 동일하게 수식 복사 + 붙여넣기를 해주는 경우가 많으니 꼭 F4를 눌러 절대참조로 바꿔주자!!
그럼 범위 앞에 $ 표시가 뜰 것이다!
맨 앞에 'in' 은 sheet 명이다. 다른 sheet의 값들을 참조범위로 넣을 때 자동으로 해당 sheet 명이 표현된다.
=vlookup(B4, 'in'!C1:$E$5'in'!$C$1:$E$1903
3. 열번호 입력하기
내가 찾고 싶은 값은 바로 주소!
주소 정보가 있는 E 컬럼을 열번호로 입력해주어야 한다.
내가 지정한 참조범위를 기준으로 가장 첫 번째 열번호가 1 부터 시작하니,
내가 원하는 '주소'의 열번호는 3 이다.
=vlookup(B4, 'in'!C1:$E$5'in'!$C$1:$E$1903, 3
4. 일치옵션 입력하기
찾을값에 대한 일치옵션이다.
기본값은 TRUE로 유사일치를 의미하며 유사일치 시에는 찾을 값보다 '작거나 같은 값 중 최대값'을 조회한다.
대부분 정확한 값을 찾는 경우가 많기 때문에 FALSE로 지정하면 되고 편하게는 0이라고 입력해도 된다.
=vlookup(B4, 'in'!C1:$E$5'in'!$C$1:$E$1903, 3, FALSE)
그렇게 모두 입력하고 나면 아래와 같이 주소에 대한 정보를 바로 가져오게 되었다.
그럼 이제 아래 가회민화박물관, 김종영 미술관, 서울광장 등 다른 값에도 동일하게 적용해야 하는데,
수식 복사 + 붙여넣기 단축키가 있다!
ctrl + C 해서 복사를 한 뒤,
Alt > E > S > F (순서대로 누르기)
또는, ctrl + alt + V 누른 뒤 F
수식 복사 + 붙여넣기는 엑셀에서 많이 활용되기 때문에 단축기를 꼭 외워두도록 하자!
alt 뒤에 E → S 를 순서대로 누르거나 ctrl + alt + V 를 누르게 되면 선택하여 붙여넣기가 뜨게 된다.
여기서 F를 누르면 수식 붙여넣기가 되고, V를 누르면 값 붙여넣기가 된다.
값 붙여넣기는 수식 없이 표면적으로 뜬 값만 복사해서 붙여넣기 한다는 뜻이다.
이렇게까지 하면 vlookup 함수 활용하기 완전 정복!!
자주나는 오류 해결방법
오류가 발생하는 경우는 대게 아래의 경우이다.
1. #N/A가 나오는 경우
쉽게 말하면 찾을값이 없다는 뜻이다.
vlookup 마지막에 넣는 일치옵션이 무엇인지에 따라 원인이 다른데,
TRUE, 즉 유사일치로 선택했을 때 나왔다면 찾을값이 참조범위 첫 번째 열에 없고 범위 안의 최소값보다도 작을 경우 #N/A가 나온다.
FALSE, 즉 정확한일치로 선택했을 때 나왔다면 찾을값이 참조범위 첫 번째 열에 없을 때 #N/A가 나온다.
또는 찾을값에 "~" 물결표시가 들어있을 경우에 #N/A가 뜰 수도 있다.
이 때는 물결표시를 2개로 해서 "~~" 이렇게 변경해서 검색하면 된다.
2. #REF!가 나오는 경우
쉽게 말하면 참조할게 없다는 뜻이다.
참조범위에 해당하는 컬럼 개수보다 큰 열번호를 입력했을 때 이런 오류가 나온다.
위의 예제에서 내가 참조범위로 C 컬럼에서 E 컬럼까지 3개 컬럼만 선택했는데 내가 열번호로 4를 입력했다면 #REF!가 뜬다.
3. #VALUE!가 나오는 경우
쉽게 말하면 내가 원하는 값, 즉 결과값을 표출하는데 있어 문제가 있다는 뜻이다.
찾을값의 글자길이가 255자를 초과하는 경우에도 Value 오류가 발생할 수 있는데 이런 경우에는 INDEX 함수나 MATCH 함수를 조합하여 해결할 수 있다.
또는, 내가 원하는 값인 열번호를 잘못 입력했을 경우 발생할 수 있다.
열번호는 1부터 참조범위 내에 있는 숫자까지 입력해야 정상인데, 텍스트를 입력한다던지 0보다 작은 수를 입력한다던지 하면 #VALUE 오류가 나온다.
4. #NAME?가 나오는 경우
수식 자체가 잘못 입력되었을 때 #NAME?이 뜬다.
찾을값을 B4와 같은 컬럼과 열 번호로 표현할 수도 있지만 찾을값 자체를 입력해도 무방하다.
예를들어 예제에서 "PKM갤러리" 주소를 찾는다면 찾을값 자리에 "PKM갤러리"라고 입력해도 결과는 나온다는 의미이다.
그런데 이런 텍스트에는 반드시 큰따옴표 ""를 같이 써주어야 한다.
=vlookup("PKM갤러리", 'in'!C1:$E$5'in'!$C$1:$E$1903, 3, FALSE)
큰따옴표를 안해주면 #NAME?이 나올 수 있고 아니면 아예 함수명 자체를 잘못 입력한 것이다.
실무에서 가장 많이 활용하는 엑셀 함수 중 하나인 VLOOKUP에 대해서 알아보았다!
예제로 직접 연습도 해보면서 완벽 마스터 해봅시다!
'Excel 함수' 카테고리의 다른 글
| 엑셀 필터링하여 화면에 보이는 데이터만 부분합 계산하는 SUBTOTAL 함수 사용 방법 (1) | 2023.12.14 |
|---|---|
| 엑셀 INDEX 함수 사용법과 MATCH 함수와 함께 동적 데이터 검색하는법 알아보기 (11) | 2023.12.13 |
| 엑셀 #DIV/0!, #N/A, #REF! 등 에러 처리 위한 IFERROR 함수 사용 방법 (12) | 2023.12.10 |
| 엑셀 COUNTIF COUNTIFS 함수 사용법 조건을 만족하는 셀 개수 세기! (46) | 2023.12.09 |
| 실무에서 많이 쓰는 엑셀 SUMIF 함수, 조건에 맞는 합 구하는 방법 (39) | 2023.12.08 |
