엑셀 크롤링, 함수 하나로 해결하는 방법 | 네이버 연관검색어 서식
엑셀 크롤링 함수 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀고급] 크롤링, 함수 하나로 해결! - 네이버 연관검색어 서식예제파일[엑셀고급] 크롤링, 함수 하나로 해결! - 네이버 연관검색어 서식완성파일[관련자료] 네이버/다음/쿠팡 연관검색어 서식무료서식
라이브 강의 전체영상도 함께 확인해보세요!
위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.
간단한 VBA 코드 10줄로 구글 연관검색어를 크롤링하는 방법이 궁금하다면? 👉 VBA 기초부터 크롤링까지! 모두 정리한 40분 VBA 크롤링 기초 총정리 강의를 참고해보세요!
- 개발자도구 실행하기 : 크롬을 실행 후, 네이버 홈페이지( https://www.naver.com)으로 이동합니다. 이후 단축키 F12키를 누르면 개발자 도구가 실행됩니다. 개발자도구가 실행되면, 상단 목록 중 [Network] 탭을 클릭합니다
크롬에서 개발자도구를 실행 후, Network 탭으로 이동합니다. 오빠두Tip : 다른 인터넷 브라우저(엣지, 웨일, 인터넷 익스플로러 등..)에서도 개발자도구를 실행 후 동일한 순서로 진행 가능합니다. - URL 분석하기 : 크롬 창을 왼쪽, 개발자도구 창을 오른쪽으로 이동 후 네이버 검색창에 임의의 값을 검색하면 네트워크 창에 새로운 항목이 표시됩니다. 표시된 항목 중 ac?q...로 시작하는 마지막 항목을 선택합니다.
검색어를 입력하면 연관검색어를 반환하는 새로운 네트워크 항목이 표시됩니다. - 이후 항목을 선택하면 나타나는 새로운 창에서 [Response] 탭을 클릭하면 해당 URL의 결과로 출력되는 값이 표시됩니다. 값을 확인했으므로 해당 URL주소를 확인하기 위해 [Headers] 탭을 클릭합니다.
Response 탭을 클릭하면 연관검색어 목록이 표시됩니다. - [Headers] 로 이동하면 네이버의 연관검색어 목록을 표시하는 URL 주소를 확인할 수 있습니다. Request URL 주소를 복사 후 메모장 또는 엑셀에 붙여넣기하여 쿼리문을 분석합니다.
Headers 탭으로 이동하여 연관검색어를 반환하는 URL을 확인합니다.
API를 사용하기 위해 요청하는 URL주소 형식은 크게 2가지로 나뉩니다. 그리고 요청방식은 정보제공자가 정한 규칙을 따라야합니다. 보통 REST API(HTTP 요청, 웹 요청)을 통해 데이터를 크롤링하는 경우 첫번째 방식이 많이 사용되며, 첫번째 방식의 API는 WebService 함수 사용 불가합니다.
① Request Header로 보안키를 넣어 데이터를 요청하는 경우
보편적으로 사용되는 방식입니다. Request Header에 보안키를 함께 요청해야 하므로 WebService 함수로는 데이터를 받아올 수 없고, VBA나 다른 프로그래밍 언어를 사용해야합니다. VBA 매크로로 크롤링하는 방법은 아래 영상 강의에서 자세히 설명해드렸습니다.
대부분의 API는 Request Header에 보안키 정보를 함께 담아 요청합니다. ② 쿼리문으로 보안키 또는 조건을 함께 넣어 데이터를 요청하는 경우
강력한 보안이 필요하지 않거나 대중에게 공개적으로 사용되는 API일 경우 사용하는 방식입니다. 기본 URL주소 뒤에 ?를 추가한 후, 원하는 조건을 쿼리로 추가하여 특정 데이터를 요청합니다. URL 쿼리문은 아래 형태로 작성됩니다.
https://기본URL?조건1=값1&조건2=값2&조건3=값3...
예) https://www.naver.com?q=검색어&where=검색카테고리&ie=인코딩언어...
일부 공공 API는 간단한 쿼리문을 작성하여 정보를 요청할 수 있습니다. - WebService 함수로 크롤링하기 : 예제파일의 두번째 시트인 [네이버 검색어] 시트로 이동 후, G1셀에 아래 수식을 입력합니다. 엔터키로 수식을 입력하면 해당 단어의 연관검색어 결과가 출력됩니다. 검색결과가 올바르게 출력되면, G1셀을 선택 후 [홈] 탭 - [자동 줄바꿈]을 활성화하여 줄바꿈이 포함된 온전한 검색결과를 표시합니다.
=WEBSERVICE("https://ac.search.naver.com/nx/ac?q="&C4&"&st=100")
WebService 함수로 네이버 연관검색어 결과값을 크롤링합니다. 오빠두Tip : WebService 함수는 엑셀 2013 이후 윈도우 버전에서만 제공되므로, 엑셀 2010 이전 또는 Mac 에서는 사용 불가합니다. - 크롤링 결과 가공하기 : 연관검색어로 받아온 문장 중, "items"로 표시된 항목만 추출하겠습니다. G2셀을 선택 후, 아래 수식을 입력하면 연관검색어 URL로 출력된 문장에서 items 항목만 추출됩니다.
=MID(G1,FIND("[[",G1),LEN(G1))
MID 함수와 FIND 함수로 원하는 결과값을 따로 추출합니다. - G2셀에 추출한 결과를 살펴보면, 불필요한 기호로 대괄호([,])와 큰따옴표("), 그리고 중괄호(})가 포함되어 있습니다. 이번에는 SUBSITUTE 함수를 사용하여 불필요한 기호를 제거하겠습니다. 예제파일 G4셀에 아래 수식을 입력하면, 불필요한 기호가 제거됩니다.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G2,"[",""),"]",""),"""",""),"}","")
SUBSTITUTE 함수로 불필요한 기호/단어를 제거합니다. - 연관검색어 나누어 출력 : 이제 연관검색어 목록이 쉼표(,)로 깔끔하게 구분되었습니다. 이전 시간에 알아본 텍스트 나누기 공식을 활용하여 연관검색어를 각 셀안에 나누어 표시하겠습니다. 아래 수식을 예제파일의 G6셀에 입력하면 연관검색어가 각 셀에 나누어 표시됩니다. 텍스트 나누기 공식의 동작원리 및 사용법은 이전 강의에서 자세히 설명해드렸습니다.
=TRIM(MID(SUBSTITUTE(","&G4,",",REPT(" ",LEN(G4)+1)),ROW(INDIRECT("A1:A"&LEN(G4)-LEN(SUBSTITUTE(G4,",",""))+1))*LEN(G4)+1,LEN(G4)+1))
텍스트 나누기 공식을 사용하여 연관검색어를 각 셀에 나누어 표시합니다. 오빠두Tip : M365/엑셀2021 이전 버전을 사용중일 경우, G6:G15 범위를 미리 선택한 후 수식입력줄에 수식을 입력한 다음 Ctrl + Shift + Enter로 배열수식으로 입력합니다. - 이제 검색할 단어를 변경하면 연관검색어 목록이 실시간으로 갱신됩니다.
- 검색결과 이동 링크 만들기 : 해당 연관검색어를 클릭하면 이동할 URL 주소를 만들어주겠습니다. 예제파일 H6셀에 아래 수식을 입력한 후, 수식을 아래로 자동채우기 하면 각 연관검색어의 검색결과 페이지 URL이 만들어집니다.
="https://search.naver.com/search.naver?query="&G6
연관검색어 검색 결과로 이동할 수 있는 URL 주소를 생성합니다. - C6셀을 선택한 후, HYPERLINK 함수를 사용하여 링크를 생성합니다. C6셀에 아래 수식을 입력한 후, 수식을 아래로 자동채우기 합니다.
=IFERROR(HYPERLINK(H6,G6),"-")오빠두Tip : IFERROR 함수를 사용하여 결과값이 없거나 오류를 반환할 경우 하이픈(-)을 표시하도록 함수를 작성했습니다.
- 이제 각 셀을 클릭하면 해당 연관검색어의 검색결과 페이지로 이동합니다.
이제 연관검색어를 클릭하면 해당 검색결과 페이지로 이동합니다. - 연관검색어 서식 꾸미기 : 마지막으로 하이퍼링크로 표시된 셀 서식을 꾸며주겠습니다. C6:C15 범위를 선택한 후, 글씨색은 진한 회색, 밑줄은 없음으로 변경합니다. 이후 [맞춤] 그룹에서 [들여쓰기]를 한번 클릭하여 한 칸 들여쓰기 합니다. 나머지 불필요한 행과 열은 모두 숨겨서 서식을 예쁘게 꾸며줍니다.
연관검색어 서식을 적절히 꾸며줍니다. - 이전 강의에서 소개해드린 기본값 설정 방법을 활용하여 검색할 단어가 비어있을 경우 "검색할 단어를 입력하세요"라고 기본값이 표시되도록 서식을 꾸며주면 네이버 연관검색어 서식이 완성됩니다.
검색어 셀에 기본값을 설정하면 네이버 연관검색어 서식이 완성됩니다.
