한가지 방법만 고수 할 필요가 없다.
안녕하세요. 김형민입니다! 많은 분들이 직장 생활을 하시면서 엑셀을 활용하는 일이 많으실 것 같아요. 물론 저도 예전만큼은 아니지만 매일 수차례에 걸쳐 엑셀 데이터를 보고 가공하고 있습니다.
엑셀에는 기능이 너무 많아서 그 기능을 다 활용할 정도면 왠만한 업무도구가 필요 없을 정도죠. 직장생활 처음에는 엑셀 마스터를 꿈꾸며 600페이지 이상이 되는 엑셀책을 놓고 밤새 공부했던 적도 있었네요 ^^;
그런데 그 많은 것들 중 실무에서 자주 쓰는 기능은 따로 있더라구요. (물론 업무성격에 따라 다르긴 하겠지만) 저에게는 그것이 바로 ‘VLOOKUP’이었습니다.
#VLOOKUP이 있으면 일일이 정보를 찾을 필요가 없다.
vlookup은 그야말로 신이 내려준 함수였습니다. 예전에는 ‘A’라는 값이 파일내 어디에 있는지 하나 하나 찾아가며 입력했었는데 (정말 그랬습니다.) vlookup을 알게 된 순간 간단한 함수 설정으로 정확한 값을 가져올 수 있었습니다! 물론 업무속도도 향상.
위에 그림은 vlookup함수 활용 예시를 보여드리기 위해 간략히 작성한 것입니다. A열부터~E열까지가 상품별 판매데이터 정보이고 G~I열은 찾고자 하는 정보부분입니다.
위 예시를 기준으로 들자면, 상품코드 458000002에 해당하는 상품의 2월판매수량을 불러오는 건데요. 이 함수를 알기 전에는 당연한 얘기겠지만 D3에 있는 ‘4’라는 값을 일일이 찾아서 입력했었습니다. 물론 틀리는 경우도 많았죠…
그러나 vlookup함수를 알고서부터는 찾는 수고가 줄어들었고 정확도도 올라갔습니다. 모든 엑셀 데이터를 프린트해서 틀리지 않으려고 자를 대고 체크했던 제 모습이 아직도 잊혀지지 않네요. (부디 그런 분들이 없으시기를…)
=VLOOKUP(G2,A2:E4,4,0)
G2 찾고자하는 정보의 제목, A2:E4 찾고자하는 정보 데이터의 위치, 4 찾고자하는 정보 데이터의 열 순서, 0 정확도 여부. 이렇게 설정을 해주면 원하는 값인 ‘4’를 찾아 올 수 있습니다.
#VLOOKUP의 한계와 시련
이제 Vlookup만 있으면 엑셀로 원하는 데이터를 찾고 처리하는데 큰 어려움이 없을 거라고 생각했고 그 누구보다 Vlookup함수를 애용했습니다. 그런데, 이 함수에는 크나큰 한계점이 세가지가 있었습니다.
첫째. 정보 제목은 본 데이터의 가장 왼쪽에 있어야 한다!
위의 예시에서 본 데이터 범위 (A2:E4)가 변하지 않는다고 가정 했을때, 찾고자 하는 정보 제목인 상품코드는 반드시 A열에 위치해 있어야 합니다. 왜냐하면 왼쪽에 있는 정보를 기준으로 이후의 열들에 있는 값을 가져오기 때문입니다!
둘째. 불러오고자 하는 값의 열 번호를 알아야 한다!
위에서 저는 열 값(순서)을 ‘4’라고 입력하여 2월판매수량을 가져왔습니다. 다행이도 예시에서는 데이터 내용이 많지 않아서 단번에 4번째 열이라고 파악이 되지만 수십~수백열이 되는 데이터 안에서 내가 원하는 열이 어디있는지 파악하는 것도 여간 힘든 일이 아닙니다.
셋째. 정보제목보다 왼쪽에 있는 값은 불러오지 못한다!
이게 아마 Vlookup이 갖는 가장 큰 한계점일 것입니다. 간혹 정보 제목보다 왼쪽에 불러오고자 하는 값이 위치해 있을 경우도 있는데 Vlookup은 오른쪽에 있는 값들만 가지고 올 수 있다보니 이 함수 사용을 위해 데이터를 다시 재가공 하는 일도 빈번했습니다.
제가 네번째 회사에서 근무할 때 물류 관리를 했었는데 Vlookup으로 하다보니 미스가 정말 많았습니다. 열 번호를 틀렸을 경우도 있었고, 데이터 위치를 오른쪽으로 옮겨오다가 데이터가 제대로 복사되지 않는 경우도 있었습니다. 그러다보니 당연히 틀린 데이터가 가공이 되었고 그걸가지고 영업활동이 이루어지다 보니 작고 큰 혼선이 빚어졌습니다.
더군다나 패션업계와 비슷하게 한 아이템에도 다양한 색상과 사이즈가 존재하다보니 관리하는 상품수만 수천가지. 수천가지 이상 아이템의 수개월에 걸친 정보가 들어가 있다 보니 엑셀파일도 무겁고 Vlookup으로 정리하려고 하니 매일 3~4시간 이상이 걸리고 야근이 끊이질 않았습니다.
이렇게 해서는 업무 능률도 떨어지고 미스도 많아 더이상은 안되겠다는 생각으로 대응책을 찾기 시작합니다. 그렇게 한참을 헤매이다가 막강한 함수를 발견하게 됩니다!
#INDEX, MATCH 콤비와의 만남
엑셀 이론책에서도 대충 훑고 지나갔던 함수 INDEX와 MATCH.
INDEX 함수는 테이블이나 범위에서 값 또는 값에 대한 참조를 반환합니다.
MATCH 함수는 셀 범위에서 지정된 항목을 검색하고 범위에서 해당 항목이 차지하는 상대 위치를 반환합니다.
위는 각 함수의 정의입니다. 지금 읽어도 사실 크게 와닿지가 않군요… 어쨌거나 이 둘은 하나로 합쳐졌을 때 Vlookup보다 위대한 힘을 발휘한다는 것입니다. (실무에서는 이론보다 응용이 중요하다!)
위에 예시 I3행이 이 두가지 함수를 통해 가져온 것입니다. Vlookup보다 조금 복잡해 보이기는 하지만 같은 결과값인 ‘4’를 가지고 온 것을 확인 할 수 있습니다.
=INDEX(D2:D4,MATCH(G2,A2:A4,0))
INDEX함수가 MATCH함수를 감싸고 있는 형태입니다. INDEX함수에서 D:D4는 제가 원하는 정보인 ‘2월판매수량’ 데이터가 있는 열을 가르킵니다. MATCH함수에서 G2는 정보제목을, A2:A4는 정보제목이 있는 데이터 열을 가르킵니다. 그리고 마지막 ‘0’은 매칭 정확도입니다.
Vlookup처럼 열의 순서를 기억할 필요가 없이 MATCH함수로 정보제목 위치를 지정해주고 INDEX함수로 불러 오고 싶은 데이터의 열을 선택해 주면 끝입니다. 그렇기 때문에 찾는 데이터가 제목열의 왼쪽에 있건 오른쪽에 있건 상관 없습니다!
위의 예시는 ‘패션백 2패턴 노란색’이라는 상품명을 기준으로 상품코드를 가져온 모습입니다. Vlookup이었다면 상품코드는 상품명 오른쪽에 위치해 있어야만 합니다. 그러나 이 두 함수를 이용한다면 굳이 그럴 필요가 없습니다. 그저 상품코드 열 위치만 정확히 기록해주면 끝입니다!
이 두 함수를 사용하면서부터 엑셀 데이터편집에 자신감과 속도가 붙었고 물론 정확도도 높아졌습니다. 연구하면 더 좋은 방법이 있겠지만 그 이상의 힘은 엑셀보다 다른 프로그램의 힘을 빌리는 것이 좋을 것 같네요 ^^
어쨌건 저는 VLOOKUP도 INDEX, MATCH함수도 쓰면서 엑셀로 데이터를 관리할 수 있다는 자기자랑이었습니다!