예전 포스팅에서 한번 언급한 적이 있었는데요. 엑셀함수는 VLOOKUP, SUMIF, SUMPRODUCT 딱 3가지 함수만 알면된다! 고 했었습니다. 물론 IFERROR라던가 ROUNDUP, ROUNDDOWN, LEFT 같이 보조적인 함수를 제외한다면 말이죠. 보조함수는 차차 배워나가기로하고, 오늘부터 드디어 엑셀함수에 대해 포스팅을 하려고 합니다.
일단 가장 많이 사용되는 vlookup 함수는 데이터값을 찾는 함수입니다. 어떻게 사용하는지 방법에 대해 알아보고, vlookup함수를 사용하면서 일반적으로 발생하는 오류에 대해서 설명하도록 하겠습니다. 아래 그림은 vlookup함수 예제로 일반적으로 vlookup함수를 많이 사용하게되는 경우입니다.
좌측에 품목별 단가가 나와있고, 우측에 해당품목의 단가를 찾아야하는 경우인데요. 위 예제그림에서는 품목별단가가 15개로 한정되어있지만 실제 업무에서는 구매품목이 수백, 수천개는 되는데요. 이럴때 하나하나 개별단가를 찾으려면 시간이 오래걸립니다. 따라서 vlookup함수는 엑셀에서 필수 함수라고 생각되는데요. 그럼 지금부터 vlookup함수 사용법을 알아보겠습니다.
원하는 셀위치에서 '=vlookup'이라고 쓰니 위 사진처럼 vlookup함수에 대한 설명이 나옵니다.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
엑셀에서 함수사용시 위와같이 영어로 설명이 나오는데요. 하나하나 살펴보면 lookup_value는 찾는값, table_array는 필요한값이 있는 위치를 말하며, col_index_num은 collect index number의 줄임말로 찾아야하는 위치가 몇번째에 있는지를 말합니다. 일단 하나씩 넣어보기로 하죠.
E5 셀에 있는 품번 'TT302G'를 B열에서 찾아 C열에 있는 단가를 반환해야합니다. 찾는값은 E5, 필요한값이 있는 위치는 B5:C20 사이에 있고, 반환해야되는값은 두번째네요. 만약 품번과 단가사이에 하나의 열이 더있다면, 세번째가 됩니다. 이를 함수로 바꿔보도록 하겠습니다.
=vlookup(E5,B5:C20,2,0)
가장 마지막의 0은 정확하게 일치를 하느냐, 유사하게 일치를 하느냐라는 것인데요. 정확히 일치하는 값을 찾고싶다면 0또는 FALSE를 기입하고, 유사일치한 자료를 찾고자한다면 TRUE 또는 1을 쓰면됩니다. 그런데 유사일치자료를 찾을경우 자료에 오류가 발생할수도 있기때문에 가능한한 정확한 자료를 찾기위해 0또는 FALSE를 기입하는것이 좋습니다.
자 함수를 사용해 수식을 완성했다면 위 그림과 같이 자동채우기 기능으로 복사해서 붙여넣으면 되는데요. 위 그림만 보면 별다른 특이사항이 없어보이지만, 여기에 큰 오류가 있습니다.
두번째줄을 보면 자동채우기 기능을 할 경우 찾을 범위도 함께 변경되기때문에, B5:C5 위치가 누락됩니다. 점점 아래로 내려가면서 누락되는 위치가 증가되기때문에 자료가 부정확해질수도 있으니 절대참조로 변경해주어야 합니다. 지난 포스팅에서 상대참조/절대참조/혼합참조에 대한 글을 썼으니, 한번 확인해보는것도 좋겠습니다.
이처럼 찾는위치를 절대참조, 즉 $를 삽입해 절대값으로 변경을 해줘야 자료에 오류가 발생하지 않습니다.
자동채우기 혹은 복사해서 붙여넣어도 수식에 이상없이 완료되었네요.
참고로 찾을 범위를 절대값으로 변경해주더라도 해당위치 하단에 셀을 추가하게되면 역시 자료에 치명적인 오류가 발생합니다. 따라서 저는 위와같은 방법으로 찾을범위를 지정하는데요. 'B:C' 이런식으로 범위를 지정해놓으면 하단에 셀을 삽입하더라도 문제가 발생하지 않습니다. 요런게 꿀팁이지요.
vlookup 함수를 사용하면서 오류가 발생하지 않게하려면, 세가지만 주의하면 됩니다.
첫째. 찾을범위를 상대참조가 아닌 절대참조로 지정한다. 이건 위에서 설명했으므로 굳이 다시 설명하진 않겠습니다.
둘째. 찾을범위를 지정할때 찾을값이 가장좌측에 있어야 한다.
위 그림과 같이 찾을범위를 품번(C열)부터가 아니라 순번(B열)부터 지정했기때문에 #N/A 오류가 떴습니다. 찾는값이 위치한 범위가 가장 좌측에 있도록 지정해야합니다.
셋째, 찾고자하는 데이터가 한개이상일 경우 가장 위의 데이터값만 반영한다.
vlookup함수는 단순히 해당하는 데이터값을 찾는 함수입니다. 그래서 위에서 아래로 데이터값을 찾는데, 해당하는 데이터가 여러개가 있다면 가장 위의 데이터값만 보여줍니다. 위 그림을 보면 이해가 가겠지만 'TT302G'라는 품번의 단가가 55원, 78원, 62원 등 네가지가 있는데, 반영된 값은 제일 상단에 있는 데이터값만 반영되었습니다. 따라서 이부분을 잘 검증해서 자료에 오류가 발생하지 않도록 주의해야합니다.
이상으로 VLOOKUP함수에 대한 포스팅을 마치도록 하겠습니다. MS Office 카테고리에 보시면 더 많은 자료가 있으니 확인해보세요.
