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

2014.12.11
공유하기


 

직장인 엑셀 잘 하는 비법

직장인의 필수 함수! 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강의 및 직장인의 두려움을 해소시킬 엑셀 비법도 같이 알려줄 예정이다! 

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

 

  • vlookup 정말 유용한 함수입니다~hlookup도 기대되네요~
    2014.12.19 09:22
  • 항상 잘 배우고 있습니다. 감사합니다!!!
    vlookup이랑 피벗으로 찾는거랑 차이점이 뭔지 궁금합니다. 각자 더 잘 맞는 상황이 있는건가요?
    2015.03.16 10:16
    • 안녕하세요 이원희 님! 도움이 되셨다니 정말 기쁩니다.
      궁금하신 부분에 대해 확인하는대로 답변드리겠습니다! 정말 감사합니다!
      2015.03.16 17:05 신고
    • 문의하신 내용에 대한 답변을 드립니다.

      vlookup은 해당하는 값을 찾아서 입력하는 것이고 피벗테이블은 원하는 값을 찾아 그 값의 합계, 개수 등을 구하는 것이라고 생각하시면 됩니다. 따라서 값을 찾는 것은 같지만, 그 값을 그대로 보여주느냐 가공하느냐의 차이로 이해하시면 됩니다! ^^
      2015.03.16 17:59 신고
  • 안녕하세요:) 엑셀을 공부하고 있는 취준생입니다!
    좋은 글과 정보 잘 보고 있습니다.
    궁금한 점이 있는데 범위 지정을 할 때 2012년도 것을 했기 때문에 2013년, 2014년...은 자동으로 변경되는게 아니라 또 셀을 만들어야 하는 건가요?
    글이 몇년 전에 올려진거라 답변이 가능하실지 모르겠지만 혹시 댓들 보시면 답변 부탁드리겠습니다!!
    2018.03.17 17:06
맨 위로