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

엑셀 VBA를 활용한 크롤링 핵심 기초부터 활용까지, 필요한 핵심 스킬을 단계별로 알아본 후, 구글 연관검색어 조회 서식을 완성합니다.

# VBA

작성자 :
오빠두엑셀
최종 수정일 : 2022. 11. 02. 04:38
URL 복사
메모 남기기 : (9)
엑셀 vba 크롤링 기초 완벽 정리_R
by

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

엑셀 VBA 크롤링 기초 목차 바로가기
  1. 예제파일 다운로드
  2. 개발도구 추가 및 매크로편집기 실행하기
  3. 웹 크롤링 기본 명령문 작성하기
  4. 연관검색어를 출력하는 URL 쿼리 분석하기
  5. 구글 검색어 크롤링 명령문 작성하기
  6. 구글 연관 검색어 함수 사용하기
영상 강의
동영상 재생이 안 되시나요? - 유튜브에서 시청 하기

  1. 예제파일 다운로드/실행방법
    00:54
  2. 개발도구/매크로 편집기 실행하기
    01:44
  3. 매크로 편집기 구조, 5분 핵심 요약
    02:41
  4. GetHTTP 명령문 복사/붙여넣기
    06:20
  5. 구글 검색어 크롤링 - 명령문 작성
    07:02
  6. 선생님! 변수를 꼭 작성해야 하나요?
    09:19
  7. GetHTTP 명령문 기초 사용법
    10:26
  8. 개체가 필요합니다 오류 해결방법
    12:20
  9. 연관검색어를 출력하는 URL 확인
    12:48
  10. 연관검색어 URL 쿼리문 분석하기
    14:54
  11. 검색결과에서 불필요한 텍스트 지우기
    17:24
  12. 검색결과를 구분하는 대괄호 제거하기
    20:02
  13. 각 연관검색어를 배열로 쪼개기
    21:48
  14. 각 배열에서 쉼표 위치 찾기
    23:47
  15. 쉼표 기준 불필요한 문자 제거
    27:51
  16. 나머지 큰따옴표 제거하기
    29:21
  17. 구글 연관검색어 명령문 테스트
    30:03
  18. 구글 연관검색어 함수, 최종 완성
    31:09
  19. 구글 연관검색어 함수 사용하기
    34:59
  20. 언어를 선택하는 목록상자 만들기
    38:19
  21. 클릭하면 이동하는 하이퍼링크 만들기
    38:51
  22. 엑셀파일을 매크로 문서로 저장하기
    40:29
큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [VBA프로젝트] VBA 초보자를 위한 크롤링 기초 - 핵심정리
    예제파일
    다운로드
  • [VBA프로젝트] VBA 초보자를 위한 크롤링 기초 - 핵심정리
    완성파일
.

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

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

개발도구 추가 및 매크로편집기 실행하기

이번 강의는 VBA를 처음 사용하는 초보자도 누구나 쉽게 영상 강의를 보고 쉽게 따라할 수 있도록 기초부터 모두 준비했습니다! 😉 VBA를 재밌게 시작하고 공부하고 싶으시다면, 이번 강의로 VBA 의 무한한 가능성을 한번 체험해보세요!

엑셀 구글 연관검색어 매크로
이번 강의에서는 VBA로 구글 연관검색어를 실시간으로 크롤링하는 방법을 단계별로 알아봅니다
오빠두Tip : 엑셀 VBA에 대한 더욱 체계적이고 자세한 설명은 위캔두 멤버쉽 회원에게 제공되는 VBA 4주 완성 특별 스터디를 참고해주세요!
  1. 개발도구 추가하기 : 만약 엑셀 탭 메뉴 중, '개발도구'가 보이지 않는다면 간단한 설정으로 개발도구 탭을 추가할 수 있습니다. 예제파일을 실행한 후, 엑셀 리본메뉴를 우클릭합니다. 이후 '리본 메뉴 사용자 지정'을 클릭합니다.

    엑셀 리본메뉴 사용자 지정
    리본메뉴를 우클릭 후, 리본메뉴 사용자 지정으로 이동합니다.
  2. 엑셀 옵션 창이 나오면, 오른쪽 목록에서 '개발도구'를 선택한 후, [확인] 버튼을 클릭하면 탭 메뉴에 개발도구가 등록됩니다.
    엑셀 개발도구 추가
    엑셀 옵션 창이 실행되면, 개발도구를 선택 후 [확인] 버튼을 클릭합니다.
  3. 매크로 편집기 실행하기 : 이제 [개발도구] 탭을 클릭한 후, 왼쪽의 [Visual Basic] 버튼을 클릭하면 VBA 매크로 편집기가 실행됩니다.
    엑셀 매크로 편집기 실행
    개발도구 탭으로 이둥 후, Visual Basic 버튼을 클릭합니다.
    오빠두Tip : 또는 단축키 Alt + F11 을 눌러 매크로 편집기를 빠르게 실행할 수 있습니다.
  4. 새 모듈 추가하기 : 이제 VBA 코드를 작성할 새로운 모듈을 추가합니다. 매크로 편집기에서 [삽입] - [모듈]을 클릭하면 모듈 폴더아래 'Module1'이 추가됩니다. 이제 Module1을 더블클릭해서 Module1 안에 새로운 코드를 작성합니다.
    엑셀 vba 모듈 추가
    매크로 편집기가 실행되면 [삽입] - [모듈]을 추가합니다.

    오빠두Tip : 매크로 편집기 구조와 기초 사용법에 대한 설명은 영상강의 2:41 를 참고해주세요!

웹 크롤링 기본 명령문 작성하기

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

    엑셀 크롤링 매크로
    GetHttp 전체 명령문을 복사합니다.
  2. 함수 설명 페이지 중간에 있는 GetHttp 전체 명령문을 복사한 후, 이전 단계에서 추가한 Module1 안에 붙여넣기 합니다.

    엑셀 gethttp 명령문
    복사한 명령문을 새롭게 추가한 모듈 안에 붙여넣기합니다.
  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. 매크로를 실행하면 네이버 홈페이지 내용이 엑셀 메세지박스로 출력되는 것을 확인할 수 있습니다.

    엑셀 네이버 크롤링 매크로
    매크로를 실행하면 그림과 같이 홈페이지 데이터가 메시지박스로 출력됩니다.

연관검색어를 출력하는 URL 쿼리 분석하기

  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=..." 로 추가된 새로운 URL 주소를 선택한 후, Response 탭으로 이동합니다.

    구글 네트워크 분석
    새롭게 추가된 URL을 선택한 후, Response 탭으로 이동합니다.
  5. Response 탭의 결과를 확인하니, 해당 URL 주소에서 특정 단어에 대한 검색 결과가 반환되는 것을 알 수 있습니다.
  6. 이제 Headers 탭으로 이동 후, 'RequestURL'에 있는 URL 주소를 복사합니다. 이후 메모장을 실행한 후, 복사한 URL 주소를 붙여넣기합니다.

    연관검색어 추출 url 확인
    Headers 탭에서 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 주소를 분석하면, 구글 연관검색어 목록을 얻기위한 최종 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

    구글 연관검색어 매크로
    구글 연관검색어 URL로 크롤링 명령문을 실행하면, 검색어 목록이 안내창으로 출력됩니다.
  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 을 추가한 후 매크로를 실행하면 Stop 에서 명령문이 멈추면서 코드의 진행단계를 확인할 수 있습니다.

    엑셀 vba stop
    명령문 마지막에 Stop을 작성한 후, 명령문을 실행합니다.
  3. 이제 지역창에서 v 의 [+] 를 클릭하면 명령문의 v 배열의 결과값으로 연관검색어 목록이 올바르게 반환되는 것을 확인할 수 있습니다.

    엑셀 vba 배열 결과 확인
    지역창에서 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

    엑셀 vba 구글 크롤링 함수 완성
    Sub 을 Function 으로 변경한 후, 함수에 사용할 인수를 추가합니다.
  6. GetGoogleKeyword 함수 최종 테스트 : 이제 매크로 편집기를 종료한 후, 예제파일의 C8:C17 범위를 선택합니다. 이후 아래 수식입력창에 아래 수식을 작성한 후, Ctrl + Shift + Enter로 배열수식을 입력하면 오징어에 대한 연관검색어 목록이 한 번에 출력됩니다.
    =TRANSPOSE(GetGoogleKeyword("오징어","ko"))
    엑셀 구글 연관검색어 함수 완성
    시트에서 GetGoogleKeyword 함수를 입력하면 구글 연관검색어가 출력됩니다.
    오빠두Tip : 엑셀 2021 이후 또는 M365 버전 사용자는 C8셀에 수식을 작성 후, 엔터키로 입력하면 배열이 바로 반환됩니다.
  7. 이 외에도 영상강의 38:19 에서 다룬 VLOOKUP 함수와 데이터 유효성 검사를 활용하면, 아래 그림과 같이 구글 연관검색어를 실시간으로 출력하는 자동화 서식을 완성할 수 있습니다.
    엑셀 구글 연관검색어 매크로
    이외에도 VLOOKUP 함수와 데이터 유효성 검사 등을 활용해 구글 연관검색어 서식을 완성합니다.
    오빠두Tip : 매크로 없이, 엑셀 기본 함수만 사용해서 크롤링하는 방법이 궁금하시다면, 아래 네이버 연관검색어 크롤링 강의도 한번 참고해보세요!
5 5 투표
게시글평점
9 댓글
Inline Feedbacks
모든 댓글 보기
9
0
여러분의 생각을 댓글로 남겨주세요.x