엑셀 VBA 크롤링, 이 영상으로 마스터하세요 | 구글 검색어 실시간 조회

실습 가이드

라이브 강의 전체영상도 함께 확인해보세요!

위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.

이번 강의는 VBA를 처음 접하는 초보자도 영상 강의를 따라가며 단계별로 익힐 수 있도록 기초부터 차근차근 구성했습니다. 😉 VBA 학습을 부담 없이 시작하고 싶으시다면, 이번 강의를 통해 VBA의 폭넓은 활용 가능성을 직접 체험해보세요.

1. 개발도구 추가하기 : 엑셀 탭 메뉴에 '개발도구'가 보이지 않는다면 간단한 설정만으로 개발도구 탭을 추가할 수 있습니다. 예제파일을 실행한 후, 엑셀 리본메뉴 위에서 마우스 우클릭한 다음 [리본 메뉴 사용자 지정]을 선택합니다.

   

2. 엑셀 옵션 창이 열리면, 오른쪽 목록에서 '개발도구' 항목을 체크한 후 [확인] 버튼을 클릭합니다. 그러면 리본 메뉴에 개발도구 탭이 등록됩니다.
   
3. 매크로 편집기 실행하기 : 이제 [개발도구] 탭을 선택한 후, 가장 왼쪽의 [Visual Basic] 버튼을 클릭하면 VBA 매크로 편집기가 실행됩니다.

   

   오빠두Tip : 단축키 Alt + F11 을 누르면 매크로 편집기를 더 빠르게 실행할 수 있습니다.
4. 새 모듈 추가하기 : VBA 코드를 작성할 새 모듈을 추가합니다. 매크로 편집기에서 [삽입] - [모듈]을 차례로 클릭하면 모듈 폴더 아래에 'Module1'이 생성됩니다. 이후 Module1을 더블클릭해 코드 창을 열고, 그 안에 명령문을 작성합니다.
   
   
   오빠두Tip : 매크로 편집기 구조와 기초 사용법에 대한 자세한 설명은 영상강의 2:41 부분을 참고해주세요!

1. GetHttp 마스터 코드 추가 : 오빠두엑셀에서 제공하는 GetHttp 마스터 코드를 사용하면 웹 데이터를 엑셀로 손쉽게 불러올 수 있습니다. 아래 링크를 클릭해 GetHttp 코드 설명 페이지로 이동합니다.
   

   

2. 함수 설명 페이지 중간에 안내된 GetHttp 전체 명령문을 복사한 다음, 앞 단계에서 추가한 Module1 안에 그대로 붙여넣기 합니다.

   

3. GetHttp 함수 기초 사용법 : 이번에는 GetHttp 함수를 활용해 네이버 홈페이지를 크롤링해보겠습니다. 모듈 위쪽에 새 명령문을 추가합니다. 본 강의에서는 GetGoogleKeyword 라는 함수를 아래와 같이 작성합니다.

   Sub GetGoogleKeyword()
    
   End Sub

   

4. GetGoogleKeyword 명령문 안에 아래와 같이 코드를 입력합니다.

   Sub GetGoogleKeyword()
    
   Dim s As String
   Dim v As Variant
   Dim i As Long
    
   s = GetHttp("https://www.naver.com", includeMeta:=True).body.innerhtml
    
   MsgBox s
    
   End Sub

   오빠두Tip : 명령문에 사용된 변수 종류와 GetHttp 함수의 기초 사용법은 영상강의 10:26 부분에서 자세히 설명드립니다.
5. GetHTTP 동작 테스트 : 명령문 작성을 마쳤으면 코드 안에 커서를 둔 상태에서 매크로 편집기의 재생 버튼을 클릭하거나 키보드 F5 키를 눌러 매크로를 실행합니다.

   

6. 매크로가 실행되면 네이버 홈페이지의 HTML 내용이 엑셀 메시지박스로 출력되는 것을 확인할 수 있습니다.

   

1. 원하는 결과를 얻기 위한 URL 주소 확인 : 웹 데이터 크롤링에서 가장 핵심이 되는 작업은 원하는 데이터를 반환하는 타겟 URL 주소를 정확히 찾아내는 것입니다. 이번 강의에서는 구글의 연관검색어를 가져오기 위한 URL 주소를 분석해보겠습니다. 사용 중인 웹 브라우저(크롬·엣지·웨일·파이어폭스 등)를 실행한 후 구글 홈페이지로 이동합니다.
   https://www.google.com
   오빠두Tip : 인터넷 익스플로러는 22년 6월 15일 이후로 업데이트와 서비스가 중단되었으므로 가급적 사용하지 않는 것을 권장합니다.
2. 브라우저에서 키보드 F12 키 (또는 Ctrl + Shift + I) 를 누르거나, 오른쪽 상단의 설정 아이콘 - [도구 더보기] - [개발자 도구]를 차례로 선택하여 개발자 도구를 실행합니다.

   

   오빠두Tip : 이번 강의는 크롬 브라우저 기준으로 진행되며, 사용 중인 브라우저에 따라 화면 구성이 일부 다를 수 있습니다.
3. 개발자 도구 네트워크 탭 분석 : 개발자 도구가 실행되면, 브라우저 창은 화면 왼쪽에, 개발자 도구는 오른쪽에 배치합니다. 이후 개발자 도구의 [네트워크] 탭으로 이동한 다음, 구글 검색창에 임의의 단어를 입력해봅니다.

   

4. 검색창에 단어를 입력하면, 네트워크 창에 "search?q=..."로 시작하는 새 URL 주소가 추가되며 페이지 구조가 실시간으로 갱신되는 것을 확인할 수 있습니다. 네트워크 탭에서 추가된 "search?q=..." 항목을 선택한 후, [Response] 탭으로 이동합니다.

   

5. Response 탭의 결과를 살펴보면, 해당 URL 주소가 입력한 검색어에 대한 연관검색어 결과를 반환한다는 사실을 확인할 수 있습니다.
6. 이제 [Headers] 탭으로 이동한 후, 'Request URL' 항목의 주소를 복사합니다. 메모장을 실행해 복사한 URL 주소를 붙여넣고, 구조를 자세히 살펴봅니다.

   

7. URL 주소 쿼리 분석 : URL 주소에서는 ? 뒤에 쿼리문을 추가해 다양한 옵션을 함께 전달할 수 있습니다. 기본 구조는 다음과 같습니다.
   https://www.기본주소.com/?옵션1=값&옵션2=값&옵션3=값..
8. 따라서 앞서 복사한 구글의 검색결과 URL 주소는 다음과 같이 항목별로 분석할 수 있습니다.
   https://www.google.com/complete/search?q=오징어&cp=4&client=gws-wiz&xssi=t&hl=ko&authuser=0&psi=pRwfY4JExqjV7w-iopqgBg.1662983333328&dpr=1.25

   항목	값
   기본URL	https://www.google.com/complete/search
   q	오징어
   cp	4
   client	gws-wiz
   xssi	t
   hl	ko
   authuser	0
   psi	pRwfY4JExqjV7w-iopqgBg.1662983333328
   dpr	1.25

9. URL 주소에서 쿼리문을 하나씩 변경하거나 제거하며 분석하면, 구글 연관검색어 목록을 받아오는 데 필요한 최종 URL 주소는 아래와 같이 정리할 수 있습니다.
   https://www.google.com/complete/search?q=검색어&client=gws-wiz&hl=사용언어&authuser=0

1. 구글 연관검색어 목록 받아오기 : 이제 연관검색어를 반환하는 URL 주소의 결과값을 직접 확인합니다. GetGoogleKeyword 명령문을 아래와 같이 수정한 후 매크로를 실행하면, 구글 연관검색어 목록이 아래 그림처럼 메시지박스로 출력됩니다.

   Sub GetGoogleKeyword()
    
   Dim s As String
   Dim v As Variant
   Dim i As Long
    
   s = GetHttp("https://www.google.com/complete/search?q=오징어&client=gws-wiz&hl=ko&authuser=0", includeMeta:=True).body.innerhtml
    
   MsgBox s
    
   End Sub

   

2. 목록에서 불필요한 텍스트 제거하기 : 반환된 결과에서 불필요한 문자는 제거하고 연관검색어만 깔끔하게 추출합니다. 첫 번째 단계로, 글씨를 굵게 표현하는 <b>와 </b> 태그(URL 인코딩 시 \u003cb\u003e 와 \u003c\/b\u003e 로 표시됩니다)를 제거하기 위해, GetGoogleKeyword 명령문을 아래와 같이 수정합니다.

   Sub GetGoogleKeyword()
    
   Dim s As String
   Dim v As Variant
   Dim i As Long
    
   s = GetHttp("https://www.google.com/complete/search?q=오징어&client=gws-wiz&hl=ko&authuser=0", includeMeta:=True).body.innerhtml
    
   ' 글씨를 굵게 처리하는 <b>, </b> 를 제거합니다.
   s = Replace(s, "\u003cb\u003e", "")
   s = Replace(s, "\u003c\/b\u003e", "")
    
   MsgBox s
    
   End Sub

3. 두 번째 단계로, 연관검색어 목록이 시작되는 지점에서 문자열을 잘라냅니다. 구글 연관검색어 결과는 "[[[" 기호를 기준으로 목록이 나열되므로, Split 함수로 "[[[" 기준 분할한 뒤 두 번째 요소만 결과로 가져옵니다.
   

   Sub GetGoogleKeyword()
    
   Dim s As String
   Dim v As Variant
   Dim i As Long
    
   s = GetHttp("https://www.google.com/complete/search?q=오징어&client=gws-wiz&hl=ko&authuser=0", includeMeta:=True).body.innerhtml
    
   s = Replace(s, "\u003cb\u003e", "")
   s = Replace(s, "\u003c\/b\u003e", "")
    
   ' [[[ 기준 문장을 나눈 후, 나눠진 문장에서 두번째 값을 반환합니다.
   s = Split(s, "[[[")(1)
    
   MsgBox s
    
   End Sub

4. 세 번째 단계로, 각 연관검색어 단위로 문자열을 분리합니다. 결과를 자세히 살펴보면 대괄호와 큰따옴표 [" 를 기준으로 연관검색어가 나열되어 있는 것을 확인할 수 있습니다. 따라서 Split 함수로 [" 기준 분할하여 배열로 변환합니다.

   

   v = Split(s, "[""")

5. 마지막 단계로, 분리된 각 배열에서 불필요한 문자를 제거하고 실제 연관검색어만 깔끔하게 정제하면 추출 작업이 마무리됩니다.

   Sub GetGoogleKeyword()
    
   Dim s As String
   Dim v As Variant
   Dim i As Long
    
   s = GetHttp("https://www.google.com/complete/search?q=오징어&client=gws-wiz&hl=ko&authuser=0", includeMeta:=True).body.innerhtml
    
   s = Replace(s, "\u003cb\u003e", "")
   s = Replace(s, "\u003c\/b\u003e", "")
    
   s = Split(s, "[[[")(1)
   '문장을 [" 기준으로 나눠 배열로
   v = Split(s, "[""")
    
   For i = LBound(v) To UBound(v)
   v(i) = Replace(Left(v(i), InStr(1, v(i), """,")), """", "")
   Next
    
   End Sub

   오빠두Tip : 각 명령문의 단계별 동작 원리는 영상강의에서 자세히 설명해드립니다. 영상강의 17:24 부분을 함께 확인해주세요!

1. 구글 연관검색어 명령문 테스트 : 매크로 편집기에 지역 창을 추가한 후, 명령문이 의도대로 동작하는지 확인합니다. 매크로 편집기에서 '지역' 창이 보이지 않는 경우, [보기] 탭 - [지역 창]을 선택해 지역 창을 활성화합니다.

   

2. 이후 아래 그림과 같이 GetGoogleKeyword 명령문 마지막 부분에 Stop 구문을 추가한 후 매크로를 실행하면, 해당 위치에서 명령문이 일시 정지되어 코드의 진행 상태를 단계별로 확인할 수 있습니다.

   

3. 이제 지역 창에서 v 항목 옆의 [+] 를 클릭해보면, 명령문에서 v 배열의 결과값으로 연관검색어 목록이 정상적으로 반환되는 것을 확인할 수 있습니다.

   

4. 작성한 명령문을 함수로 변경하기 : 매크로 편집기의 중지 버튼을 클릭해 실행을 종료하고, 추가했던 Stop 구문을 삭제합니다. 이어서 지금까지 작성한 명령문을 사용자 정의 함수 형태로 변경합니다.

   

5. 기존 GetGoogleKeyword 코드에서 Sub 키워드를 Function 으로 변경한 다음, 아래 그림과 같이 명령문 구조를 함수에 맞게 전반적으로 수정합니다. 명령문을 함수로 변환하는 과정은 영상강의 31:09 부분에서 자세히 설명해드립니다.

   Function GetGoogleKeyword(keyword, lang)
    
   Dim s As String
   Dim v As Variant
   Dim i As Long
    
   s = GetHttp("https://www.google.com/complete/search?q=" & keyword & "&client=gws-wiz&hl=" & lang & "&authuser=0", includeMeta:=True).body.innerhtml
    
   s = Replace(s, "\u003cb\u003e", "")
   s = Replace(s, "\u003c\/b\u003e", "")
    
   s = Split(s, "[[[")(1)
   v = Split(s, "[""")
    
   For i = LBound(v) To UBound(v)
      v(i) = Replace(Left(v(i), InStr(1, v(i), """,")), """", "")
   Next
    
   GetGoogleKeyword = v
    
   End Function

   

6. GetGoogleKeyword 함수 최종 테스트 : 매크로 편집기를 종료한 후, 예제파일에서 C8:C17 범위를 선택합니다. 이어서 수식 입력창에 아래 수식을 입력하고 Ctrl + Shift + Enter 로 배열 수식으로 입력하면, '오징어'에 대한 연관검색어 목록이 한 번에 출력됩니다.
   =TRANSPOSE(GetGoogleKeyword("오징어","ko"))

   

   오빠두Tip : 엑셀 2021 이후 또는 M365 버전 사용자는 C8 셀에 수식을 작성하고 엔터키를 누르면 배열 결과가 자동으로 반환됩니다.
7. 이 외에도 영상강의 38:19 부분에서 다룬 VLOOKUP 함수와 데이터 유효성 검사를 함께 활용하면, 아래 그림처럼 구글 연관검색어를 실시간으로 출력하는 자동화 서식을 완성할 수 있습니다.

   

   오빠두Tip : 매크로 없이 엑셀 기본 함수만으로 크롤링하는 방법이 궁금하시다면, 아래 네이버 연관검색어 크롤링 강의도 함께 참고해보세요!