EXCEL 기본편 - VLOOKUP

 

오늘은 엑셀 함수인 VLOOKUP을 다뤄볼까 합니다. D-Tech 관련된 글을 적으면서 엑셀의 VLOOKUP을 사용하는 경우가 많기 때문에 VLOOKUP만 별도로 한번 다루는 것이 좋다고 생각했습니다.

 

먼저 엑셀의 VLOOKUP은 Vertical Lookup의 줄임말입니다. Lookup은 검색의 뜻을 가지고 있습니다. 즉, VLOOKUP은 세로 검색을 뜻합니다.

여기서는 예제를 다루기 위해 주식 데이터를 사용합니다.

일부 종목에 대한 2020년3월20일 종가와 2020년 12월30일 종가를 찾는 연습을 할 예정입니다. 아래는 연습에 사용할 파일입니다.

 

VLOOKUP_연습데이터.xlsx

0.27MB

 

 

먼저 엑셀에 아래와 같이 특정 종목 몇개를 적어줍니다.

 

반드시 위의 종목들이 아니어도 됩니다. 자신이 알고 싶은 종목을 넣으셔도 됩니다.

이번에는 2020년 3월 20일 종가와 2020년 12월 30일 종가를 구해야 합니다. KRX사이트에서 손쉽게 구할 수 있습니다.

KRX에서 데이터를 다운 받는 방법은 아래를 참고해주세요.(아래 글에도 VLOOKUP을 사용합니다. 아래 글에 비해 본 글은 VLOOKUP만 깊게 설명합니다.)

sweetquant.tistory.com/206 

코로나 이후 가장 많이 오른 업종은?(Feat. KRX산업분류)

 

KRX에서 2020년 3월 20일 데이터와 2020년 12월 30일 데이터를 다운 받았다면, 엑셀을 아래와 같이 구성합니다.

여기서는 2020년3월20일 주가 데이터는 F열부터 시작이고 2020년 12월 30일 주가 데이터는 N열부터 시작입니다. 이 위치에 따라 VLOOKUP에서 처리할 값이 달라지므로 본 문서와 똑같이 하려면 시작 위치를 맞쳐주셔야 합니다.

 

VLOOKUP을 처리하기 위해서는 위와 같이 한 시트에 필요한 데이터를 모아 놓는 것이 편합니다.

VLOOKUP은 총 4개의 값이 필요합니다. 정리하면 아래와 같습니다.

  - VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

   - lookup_value : 검색할 값(검색어)

   - table_array : 검색을 수행할 데이터

      : lookup_value는 무조건 table_array의 첫 번째 열과 검색을 수행합니다.

      : 그러므로 table_array를 정할 때 시작 위치를 잘 정해야 합니다.

   - col_index_num : table_array에서 검색한 후에 돌려줄 값의 위치를 정합니다.

      : 만약에 table_array의 첫 번째 열이 종목명, 두 번째 열이 산업분류, 세번째 열이 현재가라면,

      : col_index_num을 1을 입력하면 lookup_value에 해당하는 데이터를 찾 은 후 종목명 값을 돌려줍니다.

      : col_index_num을 2를 입력하면 lookup_value에 해당하는 데이터를 찾 은 후 산업분류 값을 돌려줍니다.

   - range_lookup : 정확히 일치하는 데이터만 찾을지 정도를 설정합니다. 

      : false를 입력해야 정확히 일치하는 데이터만 찾습니다. 대부분 false로 처리합니다.

 

이제 본격적으로 VLOOKUP을 적용해볼 차례입니다.

아래와 같은 과정으로 2020년 3월 30일 종가를 VLOOKUP으로 찾아냅니다.

1. B2셀에 아래의 내용을 입력합니다.

    =VLOOKUP(A2,H:J,3,false)

2. 첫 번째 파라미터에 검색할 값을 입력합니다. 여기서는 A2셀이므로 A2를 입력합니다.

3. 검색을 처리할 대상 테이블배열을 입력합니다. 여기서는 H열부터 J열까지이므로 H:J 라고 입력합니다.

4. 검색해서 찾아낼 값은 종가입니다. (3)번에서 입력한 대상 테이블 범위인 H~J에서 세 번째 열입니다.

    : H는 첫 번째 열, I는 두 번째 열, J는 세번째 열입니다.

 

위에서 입력한 '=VLOOKUP(A2,H:J,3,false)'의 내용을 풀어쓰면 'A2에 있는 값을 H~J의 첫 번째 열인 H열에서 검색해서 H열에서 A2와 같은 데이터가 나오면, 해당 데이터의 3번째 열(J열, 현재가(종가))의 값을 보여주세요'와 같습니다.

 

처리된 VLOOKUP의 결과를 아래 행으로 모두 카피해줍니다. 아래 그림과 같이 모서리 부분을 더블클릭하면 됩니다.

 

이제 C열에 2020년 12월 30일 종가를 가져와 볼까요? 아래와 같이 C2셀에 '=VLOOKUP(A2,P:R,3,FALSE)'를 입력하면 됩니다. VLOOKUP을 입력한 후에 바로 위에서 한 것처럼 아래 행으로 모두 카피해주세요.

기왕 VLOOKUP까지 한 거, 2020년 03월 20일부터 2020년 12월 30일 사이에 등락률까지 구해볼까요. 아래와 같이 D2셀에 '=(C2-B2)/B2'를 입력한 후에 상단 메뉴에 '%'를 클릭해 백분율로 표시되도록 합니다.

등락률을 구한후 아래 행까지 모두 카피하면 최종 아래와 같은 결과를 얻을 수 있습니다.

 

데이터를 분석할 때 VLOOKUP은 엑셀에서 가장 많이 쓰는 기능중에 하나입니다. 여러번 사용하면서 완전히 자신의 것으로 만드시길 바랍니다.~ 오늘은 여기까지입니다. 감사합니다.

 

이와 같은 분석은 데이터베이스를 구축해 SQL을 활용한다면 훨씬 더 쉽고 강력하게 처리할 수 있습니다.

SQL과 주식에 관심이 있다면 아래 책을 참고해주세요.

※ 주의 사항

  ▶ 절대 특정 종목을 추천하거나 투자를 권유하는 책이 아닙니다.

  ▶ 데이터 분석을 공부하기 위한 MySQL 책입니다.

  ▶ 책의 내용을 통해 얻은 종목에 투자해 발생한 손해는, 저를 비롯한 책 관계자 누구도 책임지지 않습니다.

​

※ 책 소개: https://sweetquant.tistory.com/243

※ 책 미리보기: https://sweetquant.tistory.com/257

※ 완전판 E-Book

  ▶ 유페이퍼: https://www.upaper.net/ryu1hwan/1142997

  ▶ 알라딘: https://www.aladin.co.kr/shop/wproduct.aspx?ItemId=273701425 

  ▶ Yes24: http://www.yes24.com/Product/Goods/102264444?OzSrank=1 

  ▶ 교보문고: https://digital.kyobobook.co.kr/digital/ebook/ebookDetail.ink?selectedLargeCategory=001&barcode=4801167630019&orderClick=LAG&Kc=