[직장인 엑셀 잘 하는 비법] 직장인의 필수 함수! Vlookup 함수 응용해보기

 

직장인 엑셀 잘 하는 비법

직장인의 필수 함수! Vlookup 함수 응용해보기

직장인의 필수 함수! 과연 어떤 게 있을까요? Vlookup함수를 꼽고 싶습니다. 

오늘은 Vlookup함수를 데이터 유효성 검사와 결합해 사용하는 법을 알려드릴게요. 어떤 이야기인지 감이 잘 안 오시죠? 실제로 하면서 보여드릴게요!

 

 

 

예를 들어 위와 같은 판매량이 있다고 가정할게요. 이 데이터에서 딱 필요한 정보만 찾아서 데이터를 찾아올 거랍니다.

 

 

 

여기는 새로운 탭의 비어있는 창입니다. 품목명은 채워져 있지 않아요. 직접 목록에서 선택할 수 있게 만들고(데이터 유효성 검사), 선택 시 해당 품목에 대한 1분기~4분기 값이 자동으로 입력(Vlookup함수)되게 할거랍니다!

 

 

목록 창을 만든 셀을 드래그로 선택하고 [데이터]-[데이터 유효성 검사]를 클릭합니다.

 

 

 

[제한 대상]을 "목록"으로 선택해주세요.

 

  

 

[원본]은 입력 창은 직접 입력할 수도 있지만 오른쪽에 있는 버튼을 클릭하면 실제 작업 창에서 직접 선택할 수 있어요.

 

 

 

 

기존 데이터가 있는 2012년 탭으로 가서, 목록을 드래그 해주세요. 자동으로 범위가 설정된답니다. 설정이 완료되면, 다시 [데이터 유효성]창으로 갈 수 있는 입력 창 오른쪽 버튼을 클릭해주세요.

 

 

 

 

이제 데이터유효성에서 설정을 완료했어요!

 

[확인]을 클릭해주세요.

 

 

 

설정했던 셀을 선택하니 자동으로 목록 등이 뜨네요.

  

 

 찾고자 하는 항목을 먼저 임의로 선택해볼게요!

 

 

 

마늘의 1분기에 해당하는 셀에 Vlookup함수를 적어볼게요

 

=vlookup(

이라고 입력해주세요!

 

 

 

 

어떤 값을 2012년 탭에서 찾아올지 정하는 거랍니다.

마늘을 찾아와야겠죠? 마늘이 있는 셀을 선택해주세요.

자동으로 A4라고 입력된답니다. 해당 셀이 마늘이 아닌 다른 값이 입력되어도 자동으로 찾아올 수 있겠죠?

 

=vlookup(A4,

 

 

 

그 다음에 들어갈 값은 어디에서 참고할지 범위를 지정해야 해요.

 

2012년 판매량이 있는 표를 전체 선택해줄게요.

* 참고: 선택 전에 콤마(,)로 앞에 값과 구분하는 것 잊지 마세요!

 

=vlookup(A4, '2012'!A2:E12

 

선택하니 자동으로 값이 입력되었답니다. 하지만 여기 있는 값은 범위가 변하면 안되겠죠?

[F4]키를 눌러 절대값을 지정해주세요.

 

 

 

 

절대값이 지정되면서 작업하던 창으로 다시 이동했어요!

 

 

 

이제는 방금 전 참고할 범위에서 몇 번째 열에 있는 정보를 찾아올 지 수치를 적어줍니다. 1분기 값은 2번째 열에 있으니 2 라고 적으면 되겠죠?

 

=vlookup(A4, '2012'!$A$2:$E$12,2

 

* 참고: 행에 있는 값을 찾으려면 VLOOKUP함수가 아닌 HLOOKUP함수를 사용해야 합니다. 다음 시간에는 HLOOKUP에 대해 배워볼게요!

 

 

 

 

콤마(,)를 입력하니 자동으로 true, false를 선택할 수 있게 메가 뜬답니다. 정확하게 일치하는 값을 찾아야 하기 때문에 false를 선택해주세요

 

 

 

완료가 되었다면 ")"를 닫아 마무리하고 enter를 쳐주세요!
 

* 참고: 굳이 ")"를 닫지 않아도 함수가 맞게 작성된 상태에서 enter를 치면 자동으로 ")"이 입력됩니다!

 

 

 

vlookup을 지정했으니 신나는 마음으로 4분기까지 쭉~ 복사를 했는데 #N/A가 나오네요. 무슨 문제일까요?

 

바로 찾고자 하는 값이었던 마늘(A4)라는 값이 복사가 되면서 B4, C4로 변경되었기 때문이랍니다.

 

 

 

그럼 A라는 값은 변하면 안되겠죠?

하지만 4라는 값은 행이 변할 때 같이 변해야 두부, 양파, 대파의 값도 vlookup이 될 것 같아요. 이럴 때는 A4에서 A값만 절대값을 지정해주면 된답니다.

 

직접 A앞에 $ 값을 입력하거나, A4를 드래그로 선택한 상태에서 [F4]키를 여러 번 누르면 절대값 위치가 변한답니다. $A4가 나올 때까지 [F4]키를 눌러 주세요!

 

 

 

이제 끝이라고 생각하고 다시 드래그를 했는데 이제는 전부 1002라는 1분기 값만 찾아와집니다. 그 이유는 방금 전 어떤 열을 가져올 것인지 정할 때 2를 기입해서 그래요. 2는 1분기의 값이죠?

 

 

 

2분기에서는 2가 아닌 3으로, 3분기는 4열에서, 5분기는 5열에서 정보를 가져올 수 있게 데이터를 변경해주세요!
 

 

이제 완벽한 값이 완성되었답니다!

(대조해보시면 정확히 값을 찾아왔어요!)

 

 

 

이제 마늘의 1분기~4분기 셀을 선택한 상태에서 아래 행에 모두 적용될 수 있게 선택된 오른쪽 아래 점을 쭉 드래그하거나, 더블클릭해주세요!
 

 

설정된 값이 모두 적용되었답니다!

 

값을 대조해보니, 모두 정확히 찾아왔네요!

하지만 여기까지만 된다면, ‘꼭 이렇게 어렵게 해야 하나?’란 생각이 들지도 몰라요.

 

 

 

하지만 목록 창에서 원하는 목록을 선택하는 순간, 해당 값으로 1분기~4분기 값이 자동으로 변한답니다.

  

 

[맺음말]

처음에 사용할 때는 vlookup 함수와 데이터 유효성을 각자 쓰기도 벅찰지 몰라요. 하지만 익숙해진다면 이렇게 두 기능을 합쳐서 사용해보는 것을 어떨까요?

업무 효율이 쑥쑥~! 늘어나겠죠? 다음 시간에는 행의 값을 찾는 HLOOKUP에 대해 배워볼게요!

혜강씨(본명: 이혜강)

2012년 웹∙프로그램 부문 파워블로거로 선정된 문서 제작의 달인. 블로그 ‘친절한 혜강씨(http://leehyekang.blog.me/)’에 파워포인트 및 엑셀 강의를 게재하고 있다. 올해부터는 코오롱 팬들과 함께 감각적인 ppt강의 및 직장인의 두려움을 해소시킬 엑셀 비법도 같이 알려줄 예정이다! 

 본 칼럼의 내용은 코오롱 그룹의 공식적인 의견과 다를 수 있습니다