직장인의 Excel 뽀개기 – 함수 1 (Vlookup)

▣ 직장인의 Excel 뽀개기 – 함수 1 (Vlookup)

 

◎    엑셀의 꽃(?)이라 불리우는 함수, Vlookup 을 소개하도록 하겠습니다. 저도 첫 업무를 맡고 시작한 엑셀의 함수가 바로Vlookup 이였습니다. 처음에는 어리둥절하여 실수도 많이 했지만, 이제는 모두가 저에게 와서 물어볼 정도로 저에게 있어서아주 친근한 함수가 Vlookup 입니다. 이번 포스팅에서는 그 대단한 함수를 먼저 다루어 보도록 하겠습니다. 

 

1.     Vlookup 소개

-       Vlookup 함수는 찾고자 하는 값이 들어있는 ‘행’에서 원하는 ‘열’을 추출하여 나타내는 함수

-       =Vlookup (Lookup_value, Table_array, Col_index_num, Range_lookup)

-        =Vlookup (찾고자 하는 값, 참조 범위, 도출할 값이 있는 열, 논리 값) 총 4가지로 구성.

-       Lookup_value: 궁극적으로 찾고자 하는 값. 이 값은 참조 범위의 첫 번째 열에 있어야 합니다.

-       Table_array: 참조 범위로 조회할 값이 들어 있는 범위를 지정해줍니다. 보통 절대 참조를 하기에 $표시를 F4(단축키) 하여 참조 범위가 움직이지 않고 고정되게 합니다.

-       Col_index_num: 참조 범위에서 도출할 값 열의 위치(고정 위치가 아닌, 참조 범위의 첫 번째 열부터의 상대적 위치)

-       Range_lookup: 논리 값은 True or False로 찾고자 하는 값과 일치를 원하면 false, 유사한 값을 찾고자 한다면 true를 입력 합니다.

 

2.     Vlookup 예시

-       예를 보면, 조금 더 빠른 이해가 됩니다. 아래 표의 왼쪽은 Raw data, 즉 여러 데이터가 있는 표이고, 오른 쪽은 제가 찾고 싶은 갚이 있는 표입니다. 

-       오른 쪽 표의 예시 1번은 Chicago 도시의 기름 값을 알고 싶어, 아래와 같이 Vlookup함수를 사용하였습니다. 

-       첫 번째=VLOOKUP(F4,$A$3:$C$10,3,FALSE) : 찾고자 하는 값 = Chicago(위치 F4), 범위 = ,$A$3:$C$10 (절대참조), 도출할 값이 있는 열 = 3번째 열, 논리 값 = false(정확히 일치) => 도출 값은 Chicago의 기름값인 $4.0 이 나왔습니다.

-       두 번째=VLOOKUP(F5,$A$3:$C$10,3,FALSE) : 찾고자 하는 값 = LA(위치 F5), 범위 = ,$A$3:$C$10 (절대참조), 도출할 값이 있는 열 = 3번째 열, 논리 값 = false(정확히 일치) => 도출 값은 N/A로 찾고자 하는 값인 LA가 범위 안에 없기 때문입니다.

-       세 번째 예시(3번)에서는 위 2번 예시의 오류를 바로잡아 LA가 아닌 Los Angeles 로 정확히 입력을 하여 도출 값 $5.3이 나왔습니다.

-       네 번째=VLOOKUP(F7,$A$3:$C$10,2,FALSE): 찾고자 하는 값 = Seattle (위치 F7), 범위 = ,$A$3:$C$10 (절대참조), 도출할 값이 있는 열 =2번째 열, 논리 값 = false(정확히 일치) => 도출 값은 Seattle의 State인 WA가 나왔습니다.

-      다섯 번째=VLOOKUP(F8,$A$3:$C$10,2,FALSE): 찾고자 하는 값 = New York (위치 F8), 범위 = ,$A$3:$C$10 (절대참조), 도출할 값이 있는 열 = 2번째 열, 논리 값 = false(정확히 일치) => 도출 값은 New York의 state인 NY 가 나왔습니다.

Vlookup 예시

Source: Gas price USA. https://gasprices.aaa.com/

 

3.     Vlookup 함수 사용 시 발생 가능한 오류들

-       참조 범위의 이동 => 위의 예에서 언급했듯이, 보통 범위를 설정할 때 절대 참조로 하는 것이 편리하고 오류를 적게 발생하게 됩니다. 엑셀 작업 시 마우스로 드래그를 하여 값은 함수를 복사하여 사용하는 경우가 많은데, 이 때 절대 참조를 하지 않으면, 참조 범위도 마우스 드래그를 하는 만큼 아래나 위로 혹은 좌 우로 따라 움직이게 되어 있어, 주의 하셔야 합니다.

-       오류의 대부분이라고도 할 수 있을 만큼, 자주 발생하게 되는 오류인데, 찾고자 하는 값이 참조 범위보다 앞 열에 있을 경우입니다.

-       찾고자 하는 값이 없거나, 띄어쓰기 및 오탈자로 잘못 입력한 경우입니다.(심지어 찾고자 하는 값 앞이나 뒤에 space가있을 경우도 오류의 원입니다.)

-       참조의 값과 찾고자 하는 값의 셀서식이 다른 경우 -> 찾고자 하는 값의 셀서식이 숫자로 되어 있고, 참조의 값은 text로되어 있는 경우도 오류의 원인입니다.)

-       논리 값을 True로 할 경우, 반드시 참조 범위의 내용이 오름 차순으로 정렬이 되어 있어야 합니다.

-       다른 Sheet, 다른 파일에 Data가 있는 경우, 이를 참조하려면, Table array 즉 참조 범위를 정할 때 반드시 해당 파일 혹은 sheet의 이름도 같이 지정해 줘야 합니다.

-       찾고자 하는 값과 동일한 값이 해당 참조 범위에 있으나, 중복으로 있거나 다중으로 있을 경우, 가장 위에 있는 값에 해당하는 열의 값을 가져오게 되어 있습니다. 위의 예시에서 왼쪽 참조 범위에 New York의 gas price는 $3.9인데, 만약New York의 이름으로 다른 값 $6.0 이 다른 행에 그리고 맨 위에 또 있다고 가정 할 경우, Vlookup 함수는 이를 구분하지 못하고, 가장 위에 있는 $6.0를 불러오게 됩니다. 그러므로, 반드시 참조할 범위의 데이터를 미리 중복되지 않도록미리 정리를 해 놓을 필요가 있습니다. (중복값 정리하는 방법은 다른 포스팅에 올리도록 하겠습니다.)

 

◈ Excel에는 다양한 함수와 서식 등 우리가 활용할 수 있는 tool이 많이 있습니다. 이러한 툴을 제대로 사용하려면 각각의 특징과 사용 목적 그리고 다른 함수 및 서식과의 연계성을 알아야 합니다. 다소 복잡해 보일 수 도 있겠지만, 우리가 자주 사용하는 것만 제대로 알아도 Excel이라는 프로그램을 다양하게 활용 할 수 있을 것입니다. 오늘도 행복한 하루 보내세요. 

 

#도움이 되셨으면 공감과 함께 댓글 남겨주세요.