이번 강의는 VBA를 처음 사용하는 초보자도 누구나 쉽게 영상 강의를 보고 쉽게 따라할 수 있도록 기초부터 모두 준비했습니다! 😉 VBA를 재밌게 시작하고 공부하고 싶으시다면, 이번 강의로 VBA 의 무한한 가능성을 한번 체험해보세요!
이번 강의에서는 VBA로 구글 연관검색어를 실시간으로 크롤링하는 방법을 단계별로 알아봅니다
오빠두Tip : 엑셀 VBA에 대한 더욱 체계적이고 자세한 설명은 위캔두 멤버쉽 회원에게 제공되는 VBA 4주 완성 특별 스터디를 참고해주세요!
개발도구 추가하기: 만약 엑셀 탭 메뉴 중, '개발도구'가 보이지 않는다면 간단한 설정으로 개발도구 탭을 추가할 수 있습니다. 예제파일을 실행한 후, 엑셀 리본메뉴를 우클릭합니다. 이후 '리본 메뉴 사용자 지정'을 클릭합니다.
리본메뉴를 우클릭 후, 리본메뉴 사용자 지정으로 이동합니다.
엑셀 옵션 창이 나오면, 오른쪽 목록에서 '개발도구'를 선택한 후, [확인] 버튼을 클릭하면 탭 메뉴에 개발도구가 등록됩니다. 엑셀 옵션 창이 실행되면, 개발도구를 선택 후 [확인] 버튼을 클릭합니다.
매크로 편집기 실행하기: 이제 [개발도구] 탭을 클릭한 후, 왼쪽의 [Visual Basic] 버튼을 클릭하면 VBA 매크로 편집기가 실행됩니다.
개발도구 탭으로 이둥 후, Visual Basic 버튼을 클릭합니다.
오빠두Tip : 또는 단축키 Alt + F11 을 눌러 매크로 편집기를 빠르게 실행할 수 있습니다.
새 모듈 추가하기: 이제 VBA 코드를 작성할 새로운 모듈을 추가합니다. 매크로 편집기에서 [삽입] - [모듈]을 클릭하면 모듈 폴더아래 'Module1'이 추가됩니다. 이제 Module1을 더블클릭해서 Module1 안에 새로운 코드를 작성합니다. 매크로 편집기가 실행되면 [삽입] - [모듈]을 추가합니다.
오빠두Tip :매크로 편집기 구조와 기초 사용법에 대한 설명은 영상강의 2:41 를 참고해주세요!
함수 설명 페이지 중간에 있는 GetHttp 전체 명령문을 복사한 후, 이전 단계에서 추가한 Module1 안에 붙여넣기 합니다.
복사한 명령문을 새롭게 추가한 모듈 안에 붙여넣기합니다.
GetHttp 함수 기초 사용법: 이제 GetHttp 함수를 사용해서 네이버 홈페이지를 크롤링해보겠습니다. 모듈 위에 새로운 명령문을 추가합니다. 이번 강의에서는 GetGoogleKeyword 라는 함수를 아래와 같이 추가합니다.
Sub GetGoogleKeyword()EndSub
웹 데이터를 크롤링 할 새로운 명령문을 추가합니다.
GetGoogleKeyword 명령문 안에 아래와 같이 코드를 작성합니다.
Sub GetGoogleKeyword()Dim s AsStringDim v As Variant
Dim i AsLong
s = GetHttp("https://www.naver.com", includeMeta:=True).body.innerhtml
MsgBox s
EndSub
오빠두Tip : 명령문에 사용된 변수 종류와 GetHttp 함수 사용법에 대한 설명은 영상강의 10:26을 참고해주세요!
GetHTTP 동작 테스트: 명령문을 다 작성하였으면, 명령문에 커서를 위치한 후 매크로 편집기의 재생버튼을 클릭하거나 키보드 F5 키를 눌러 매크로를 실행합니다.
그림과 같이 명령문을 작성한 후, 재생버튼을 클릭해서 매크로를 실행합니다.
매크로를 실행하면 네이버 홈페이지 내용이 엑셀 메세지박스로 출력되는 것을 확인할 수 있습니다.
원하는 결과를 얻기 위한 URL 주소 확인: 웹 데이터 크롤링의 가장 핵심은 원하는 데이터를 얻기 위한 타겟 URL 주소를 확인하는 것입니다. 이번 강의에서는 구글의 연관 검색어를 얻기 위한 URL 주소를 확인하겠습니다. 사용중인 웹 브라우저(크롬/엣지/웨일/파이어폭스 등..)을 실행한 후, 구글 홈페이지로 이동합니다.
오빠두Tip : 인터넷 익스플로러는 22년 6월 15일 이후로 업데이트/서비스가 중단되었으므로 되도록 사용하지 않는 것을 권장합니다.
이제 브라우저에서, 키보드 F12 키 (또는 Ctrl + Shift + I) 를 누르거나, 오른쪽 상단 설정 아이콘 클릭 - [도구 더보기] - [개발자 도구]를 실행합니다.
브라우저에서 개발자도구를 실행합니다.
오빠두Tip : 이번 강의에서는 크롬 기준으로 진행하였으며, 사용하고 계신 브라우저에 따라 화면 구성이 조금씩 다를 수 있습니다.
개발자 도구 네트워크 탭 분석: 개발자도구가 실행되면, 브라우저 창을 왼쪽으로, 개발자도구를 오른쪽으로 정렬합니다. 이후 개발자도구의 [네트워크] 탭으로 이동한 후, 구글 검색창에 원하는 단어를 검색해봅니다.
개발자도구의 네트워크 탭으로 이동한 후, 검색창에 단어를 검색합니다.
검색창에 단어를 검색하면, 네트워크창에 "search?q=..."와 같은 새로운 URL 주소가 추가되면서 페이지 구조가 계속 바뀌는 것을 확인할 수 있습니다. 네트워크 탭에서, "search?q=..." 로 추가된 새로운 URL 주소를 선택한 후, Response 탭으로 이동합니다.
새롭게 추가된 URL을 선택한 후, Response 탭으로 이동합니다.
Response 탭의 결과를 확인하니, 해당 URL 주소에서 특정 단어에 대한 검색 결과가 반환되는 것을 알 수 있습니다.
이제 Headers 탭으로 이동 후, 'RequestURL'에 있는 URL 주소를 복사합니다. 이후 메모장을 실행한 후, 복사한 URL 주소를 붙여넣기합니다.
Headers 탭에서 URL 주소를 복사한 후, 메모장에 붙여넣기합니다.
URL 주소 쿼리 분석:URL 주소에는 ? 뒤에 쿼리문을 추가해서 다양한 옵션을 설정할 수 있습니다. 기본 구조는 아래와 같습니다.
https://www.기본주소.com/?옵션1=값&옵션2=값&옵션3=값..
따라서 복사/붙여넣기 한 구글의 검색결과를 반환하는 URL주소는 아래와 같이 분석할 수 있습니다.
구글 연관검색어 목록 받아오기: 이제 연관검색어 목록을 반환하는 URL 주소의 결과값을 확인합니다. GetGoogleKeyword 명령문을 아래와 같이 수정한 후, 매크로를 실행하면 구글 연관검색어 목록이 아래 그림과 같이 안내창으로 출력됩니다.
Sub GetGoogleKeyword()Dim s AsStringDim v As Variant
Dim i AsLong
s = GetHttp("https://www.google.com/complete/search?q=오징어&client=gws-wiz&hl=ko&authuser=0", includeMeta:=True).body.innerhtml
MsgBox s
EndSub
구글 연관검색어 URL로 크롤링 명령문을 실행하면, 검색어 목록이 안내창으로 출력됩니다.
목록에서 불필요한 텍스트 제거하기: 이제 반환된 목록에서, 불필요한 문장은 지우고 필요한 부분만 추출합니다. 첫번째 단계로는 글씨를 굵게 표현하는 <b>와 </b> (URL로 인코딩되면, \u003cb\u003e 와 \u003c\/b\u003e로 표시됩니다)를 제거하기 위해, GetGoogleKeyword 명령문을 아래와 같이 수정합니다.
Sub GetGoogleKeyword()Dim s AsStringDim v As Variant
Dim i AsLong
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
EndSub
두번째로는, 연관검색어가 반환되기 시작하는 지점에서 문장을 잘라줍니다. 구글 연관검색어는 "[[[" 를 시작으로 목록이 나열되므로, Split 함수로 "[[[" 기준 문장을 나눈 후, 나눠진 문장의 두번째 값을 결과로 받아옵니다. 구글 연관검색어 목록은 [[[ 기호를 기준으로 시작합니다.
Sub GetGoogleKeyword()Dim s AsStringDim v As Variant
Dim i AsLong
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
EndSub
세번째 단계로는 각 연관검색어를 분리합니다. 반환된 결과를 자세히 보면, 대괄호와 큰따옴표 [" 를 기준으로 연관검색어가 나열되는 것을 확인할 수 있습니다. 따라서 Split 함수를 사용해 [" 기준으로 문장을 분리합니다.
그리고 각 연관검색어는 대괄호와 쉼표를 기준으로 구분됩니다.
v =Split(s, "[""")
마지막 단계로, 나눠진 각 배열에서 불필요한 문장은 지우고 실제 연관검색어만 깔끔하게 가공하면 연관검색어 추출이 마무리됩니다.
Sub GetGoogleKeyword()Dim s AsStringDim v As Variant
Dim i AsLong
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)ToUBound(v)
v(i)=Replace(Left(v(i), InStr(1, v(i), """,")), """", "")NextEndSub
오빠두Tip : 각 명령문의 단계별 동작원리는 영상강의에서 자세히 정리했습니다. 영상강의 17:24 내용을 확인해주세요!
구글 연관검색어 명령문 테스트: 이제 매크로 편집기에서 지역창을 추가한 후, 명령문이 올바르게 동작하는지 확인합니다. 매크로 편집기에서 '지역'창이 보이지 않을 경우, [보기] 탭 - [지역 창]을 선택해여 지역창을 표시합니다.
명령문을 모두 작성하였으면, 지역창을 표시한 후 최종 동작을 단계별로 디버깅합니다.
이후 아래 그림과 같이 GetGoogleKeyword 명령문 마지막 부분에 Stop 을 추가한 후 매크로를 실행하면 Stop 에서 명령문이 멈추면서 코드의 진행단계를 확인할 수 있습니다.
명령문 마지막에 Stop을 작성한 후, 명령문을 실행합니다.
이제 지역창에서 v 의 [+] 를 클릭하면 명령문의 v 배열의 결과값으로 연관검색어 목록이 올바르게 반환되는 것을 확인할 수 있습니다.
지역창에서 v 옆의 + 버튼을 클릭하면 배열의 각 값으로 연관검색어가 저장된 것을 볼 수 있습니다.
작성한 명령문을 함수로 변경하기: 이제 매크로 편집기의 중지 버튼을 클릭하고, Stop 구문을 지훈 후 작성한 명령문을 함수로 변경합니다.
중지버튼을 클릭한 후, 작성한 명령문을 함수로 수정합니다.
기존의 GetGoogleKeyword 코드에서 Sub 을 Function 으로 바꾼 후, 아래 그림과 같이 명령문의 구조를 전반적으로 수정합니다. 작성한 명령문을 함수로 바꾸는 과정은 영상강의 31:09 를 확인해주세요.
Function GetGoogleKeyword(keyword, lang)Dim s AsStringDim v As Variant
Dim i AsLong
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)ToUBound(v)
v(i)=Replace(Left(v(i), InStr(1, v(i), """,")), """", "")Next
GetGoogleKeyword = v
EndFunction
Sub 을 Function 으로 변경한 후, 함수에 사용할 인수를 추가합니다.
GetGoogleKeyword 함수 최종 테스트: 이제 매크로 편집기를 종료한 후, 예제파일의 C8:C17 범위를 선택합니다. 이후 아래 수식입력창에 아래 수식을 작성한 후, Ctrl + Shift + Enter로 배열수식을 입력하면 오징어에 대한 연관검색어 목록이 한 번에 출력됩니다.
=TRANSPOSE(GetGoogleKeyword("오징어","ko"))
시트에서 GetGoogleKeyword 함수를 입력하면 구글 연관검색어가 출력됩니다.
오빠두Tip : 엑셀 2021 이후 또는 M365 버전 사용자는 C8셀에 수식을 작성 후, 엔터키로 입력하면 배열이 바로 반환됩니다.
이 외에도 영상강의 38:19 에서 다룬 VLOOKUP 함수와 데이터 유효성 검사를 활용하면, 아래 그림과 같이 구글 연관검색어를 실시간으로 출력하는 자동화 서식을 완성할 수 있습니다.
이외에도 VLOOKUP 함수와 데이터 유효성 검사 등을 활용해 구글 연관검색어 서식을 완성합니다.
오빠두Tip : 매크로 없이, 엑셀 기본 함수만 사용해서 크롤링하는 방법이 궁금하시다면, 아래 네이버 연관검색어 크롤링 강의도 한번 참고해보세요!
안녕하세요.
그럴 경우 content-type 의 표시언어형태를 바꾸면 됩니다.
Content-Type: text/html; charset=utf-8
또는
Content-Type: text/html; charset=euc-kr
형태이며, 네트워크탭에서 해당 웹페이지의 표시언어를 확인해보세요.
ramires2023.03.20 11:26
답변 감사합니다.
제가 답변 주신 내용을 잘 이해를 못하겠습니다.
아래 링크는 네이버쇼핑에서 받아온 URL입니다. https://shopping.naver.com/api/modules/gnb/auto-complete?_vc_=167927885&keyword=%EB%B0%94%EB%82%98%EB%82%98
웹페이지에서 읽어올 경우 정상적으로 표시 되는데요.
엑셀 VBA 코드창에서 확인하려고 debug.print를 하면 직접실행창에
아래와 같이 표시된다는 말인데요.
{"query":["e°?i??","e°?i??","e°?i??"],"items":[[],[],[],[],[],[],[],[],[],[],[],[]]}
설명주신 content-type은 VBA 코드창에서 수정이 가능하다는 말인가요?
오빠두엑셀작성자2023.03.20 17:44
네 맞습니다.
Request header로 Content-type을 추가하면 됩니다.
대화가피로해2023.08.24 19:08
엑셀 버전 2019입니다.
그대로 따라했는데 결과값이 '0'으로 표현 되네요.
예제파일을 활용하였습니다.
VB에서 뭔가 잘 못 된거 같은데 어디서 잘 못 되었을까요 ?
=========================================================
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
GetGooleKeyword = V
End Function
==========================
확인 부탁 드립니다.
오빠두엑셀작성자2023.08.28 16:39
안녕하세요.
V = Split(s, "[""")
뒷 부분에 Stop 을 작성하신 후, vba 편집기의 [보기] - [지역창]을 활성화해서
S와 V로 결과가 올바르게 반환되는지 한번 확인해보시길 바랍니다.
만약 결과가 잘 반환된다면 마지막 부분을 아래와 같이 바꿔보세요.
근데 네이버쇼핑을 동일한 방법으로 진행하면
읽어온 URL을 코드창에 추가해서 확일할 경우 아래와 같이 보입니다.
{"query":["e°?i??","e°?i??","e°?i??"],"items":[[],[],[],[],[],[],[],[],[],[],[],[]]}
글씨가 깨지는 것 같은데요.
이럴경우에는 해결하는 방법이 있을까요?
아니면 해당 웹사이트에서 막아놓아서 이런걸까요?
그럴 경우 content-type 의 표시언어형태를 바꾸면 됩니다.
Content-Type: text/html; charset=utf-8
또는
Content-Type: text/html; charset=euc-kr
형태이며, 네트워크탭에서 해당 웹페이지의 표시언어를 확인해보세요.
제가 답변 주신 내용을 잘 이해를 못하겠습니다.
아래 링크는 네이버쇼핑에서 받아온 URL입니다.
https://shopping.naver.com/api/modules/gnb/auto-complete?_vc_=167927885&keyword=%EB%B0%94%EB%82%98%EB%82%98
웹페이지에서 읽어올 경우 정상적으로 표시 되는데요.
엑셀 VBA 코드창에서 확인하려고 debug.print를 하면 직접실행창에
아래와 같이 표시된다는 말인데요.
{"query":["e°?i??","e°?i??","e°?i??"],"items":[[],[],[],[],[],[],[],[],[],[],[],[]]}
설명주신 content-type은 VBA 코드창에서 수정이 가능하다는 말인가요?
Request header로 Content-type을 추가하면 됩니다.
그대로 따라했는데 결과값이 '0'으로 표현 되네요.
예제파일을 활용하였습니다.
VB에서 뭔가 잘 못 된거 같은데 어디서 잘 못 되었을까요 ?
=========================================================
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
GetGooleKeyword = V
End Function
==========================
확인 부탁 드립니다.
V = Split(s, "[""")
뒷 부분에 Stop 을 작성하신 후, vba 편집기의 [보기] - [지역창]을 활성화해서
S와 V로 결과가 올바르게 반환되는지 한번 확인해보시길 바랍니다.
만약 결과가 잘 반환된다면 마지막 부분을 아래와 같이 바꿔보세요.
감사합니다.