Excel 매크로 (VBA) - 018. Vlookup으로 데이터 조회하기 (주식 종목 코드 조회)

전체 목차

 - 001. 특정 행을 복사하여 삽입하기

 - 002. 선택하여 붙여 넣기 기능 정리

 - 003. 변수/Assignment/Loop/비교

 - 004. 데이터가 있는 셀의 범위 알아내기

 - 005. 데이터 시트(쉘) 순회하기

 - 006. 셀 병합 하기

 - 007. 변수에 저장되어 있는 데이터 확인하기 (디버그 기능) 

 - 008. 정렬하기 (Sort)

 - 009. 버튼 컨트롤 (Excel Form vs. ActiveX)

 - 010. 콤보 상자 컨트롤 (Excel Form vs. ActiveX)

 - 011. 확인란/옵션단추 컨트롤(Excel Form vs. ActiveX)

 - 012. 스핀단추 컨트롤(스피너, Excel Form vs. ActiveX)

 - 013. 자동필터(Auto Filter)

 - 014. 함수(function) (전편)

 - 015. 함수(function) (후편)

 - 016. 중복 데이터 제거하기 

 - 017. 텍스트 나누기 (공백, 특수문자)

 

포스트 목차.

01. 주식 종목 코드 확보하기

02. 엑셀에서의 vlookup 함수

03. VBA에서 vlookup 사용하기 및 예제

 

 

본 포스트에서는 엑셀의 vlookup 함수를 이용하여, 데이터 중에서 원하는 데이터를 찾는 방법을 소개한다.

 

01. 주식 종목 코드 확보하기

 

이번 포스트에서 사용할 데이터는 KOSPI 상장 주식들의 종목 코드 정보이다.  여러가지 루트로 수집이 가능하지만 여기서는 증권정보포털 SEIBro를 이용해보자.

 

다음 링크를 통해 이동한다.

www.seibro.or.kr  

https://seibro.or.kr/

 

상단의 주식 메뉴를 클릭 후, 왼쪽의 주식전체검색 > 주식종목전체검색을 이용하여 조회가 가능하다.

일단 우리는 유가증권시장에서 거래되는 주식의 정보에 대해 수집할 예정이므로 다음과 같이 설정한다.

  - 시장종류: 유가증권시장

  - 주식종류: 보통주, 우선주

  - 발행형태: 전체

  - 업종 : 전체

  - 명의개서대리인: 전체

  - 결산월:전체

 

항목 설정에서는 원하는 정보를 체크해서 가져오면 된다. 본 예제에서는 다음과 같이 설정했다.

조회버튼을 클릭하면 다음과 같이 종목 정보가 조회된다.

 

하단에 있는 엑셀 다운로드 버튼을 클릭하여 엑셀 형태로 다운로드 받자

 

다운로드 받은 정보를 새로운 엑셀파일에 저장하자.

기본적인 데이터는 A열에 종목코드, B열에 종목명이 나올 것이다. 우리는 종목명으로 종목코드를 조회하는 것이 목적이기 때문에, 종목명을 A열로 옮긴다.

 

 

02. 엑셀에서의 vlookup 함수

 

1) lookup

엑셀에서 데이터 조회를 위해 제공하는 가장 기본적인 함수이다.

 

사용법은 이전 포스트를 참고하자

https://eggdrop.tistory.com/14

Excel [지식인 해결 시리즈] 001. lookup 함수를 이용한 양도세 구간 구하기

 

2) vlookup

위의 lookup 함수의 강화된 버전이라고 볼 수 있다. v는 vertical을 뜻한다. hlookup 함수도 있는데, h는 horizon 을 뜻한다. 두 함수는 데이터의 배치 방향( v:위에서 아래, h:왼쪽에서 오른쪽)만 차이가 있고 동일한 기능을 제공한다.

 

vlookup 함수의 설명과 파라미터는 다음과 같다.

 

함수 설명

함수의 파라미터

 - lookup_value: 검색어

 - table_array: 검색의 대상이 되는 데이터 영역 (데이터 영역의 1번째 열에 검색어들이 위치해야 한다)

 - col_index_num: 검색어에 해당하는 행을 찾은 후, col_index_num에 해당하는 열의 데이터를 반환한다. (1부터 시작)

 - [range_lookup]: True인 경우 유사일치 적용 ,False인 경우 정확히 일치 적용

   * range_lookup은 생략가능하며, 생략 시에 유사일치 적용

    ** 유사한 데이터를 찾는 일은 거의 없기 때문에, False 사용을 권장한다.

 

 vlookup 함수를 사용해서, 종목명을 입력했을 때, 종목 코드를 가져오는 수식을 작성해보자.

 

lookup_value 지정 (B1셀)

table_array / col_index_num / range_lookup 지정

table_array는 미리 만들어둔 종목데이터 시트의 데이터 영역을 선택한다.

col_index_num는 종목코드가 2번째 열이기 때문에 2를 입력한다.

range_lookup은 정확하게 일치할 경우만 값을 반환하게 만든다.

 

삼성전자의 종목코드 005930이 조회되는 것을 알 수 있다.

 

range_lookup의 영향을 알기 위해 종목명을 삼진전자(데이터에 없는 회사) 로 변경하자

range_lookup이 FALSE인 경우 #N/A 를 반환한다.

range_lookup이 True인 경우 삼진전자와 유사한 삼정펄프의 코드를 반환한다.

*range_lookup를 제대로 사용하기 위해서는 조회할 데이터 열의 데이터가 오름차순으로 정렬되어있어야 한다.

 

3) lookup vs vlookup

아래는 lookup 함수로 종목명을 이용해 종목 코드를 조회하는 수식이다.

lookup 함수의 경우, 가져올 데이터의 영역을 별도로 지정해야된다는 번거로움이 있지만, 데이터 영역 중에 가장 첫 열에 조회할 값들이 있을 필요가 없다는 장점이 있다. 

 

 삼진전자를 입력했을 때, 009770이 조회되는 걸로 봐서, 기본적으로 range_lookup이 TRUE라는 것을 알 수 있다. 정확하게 일치할 경우만 조회하고 싶을 때는 사용하기 힘든 함수라고 볼 수 있다.

 

이에 비해서 vlookup함수는 col_index_num 값을 변경하여 다른 데이터를 쉽게 조회하는 것이 가능하다. 예를 들어 11을 입력하면 해당 종목의 PER(PER이 11번째 열이기 때문에)을 조회할 수 있다. 

 

03. VBA에서 vlookup 사용하기 및 예제

 

vlookup함수는 Appliction.WorkSheetFunction.VLookup으로 접근하여 사용이 가능하다.

파라미터는 엑셀에서 사용할 때와 동일하다.

 

Sub vlookupTest()
 
    Dim title As String
    Dim section As Range
    Dim code As String
    'B1 셀의 내용을 title에 저장한다.
    title = ActiveSheet.Cells(1, 2).Value
    '종목데이터 시트의 A1:AE823 영역을 range 객체에 할당한다.
    Set section = Worksheets("종목데이터").Range("A1:AE823")
    'VLookup함수를 실행한다. B1에 있는 데이터는  종목데이터 시트에서 찾아서
    '2번째 열의 데이터를 가져온다.
    code = Application.WorksheetFunction.VLookup(title, section, 2, False)
    'C3열의 서식을 텍스트로 변경한다.
    ActiveSheet.Cells(3, 3).NumberFormatLocal = "@"
    'VLookup함수의 결과값을 C3에 저장한다.
    ActiveSheet.Cells(3, 3).Value = code
 
End Sub
 

 

매크로를 실행한 결과는 다음과 같다.

 

삼성전자의 종목번호가 조회되는 것을 알 수 있다.