엑셀 크롤링, 함수 하나로 해결하기 - 네이버 연관검색어 서식 제작

by 오빠두엑셀

학습시간 19분
난이도 고급
작성일 2021.12.09

엑셀에서 기본으로 제공하는 WEBSERVICE 함수를 사용해서 네이버 연관검색어 실시간 크롤링서식 만들기

이 강의에서는 WEBSERVICE 함수와 네이버 검색 API를 조합해 검색어가 바뀔 때마다 연관검색어 목록이 자동으로 갱신되는 엑셀 서식을 만드는 방법을 다룹니다. 크롬 개발자도구로 호출 URL을 분석하는 단계부터 SUBSTITUTE·TRIM·HYPERLINK 함수로 결과를 가공하고 링크 형태로 표시하는 마무리까지 단계별로 정리합니다.

실습 가이드

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

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

VBA 코드 10줄로 구글 연관검색어를 크롤링하는 방법이 궁금하다면, VBA 기초부터 크롤링까지 한 번에 정리한 40분 분량의 VBA 크롤링 기초 총정리 강의를 참고해 보세요.

크롬 개발자도구로 네이버 검색 URL 분석하기

개발자도구 실행하기 : 크롬을 실행한 뒤 네이버 홈페이지( https://www.naver.com)로 이동합니다. 이후 단축키 F12를 누르면 개발자 도구가 실행되며, 개발자도구 상단 메뉴에서 [Network] 탭을 클릭합니다.

오빠두Tip : 엣지, 웨일, 인터넷 익스플로러 등 다른 브라우저에서도 개발자도구를 실행한 후 동일한 순서로 진행할 수 있습니다.
URL 분석하기 : 크롬 창은 왼쪽, 개발자도구 창은 오른쪽으로 배치한 다음 네이버 검색창에 임의의 단어를 검색하면 [Network] 탭에 새로운 요청 항목이 표시됩니다. 표시된 항목 중 ac?q...로 시작하는 마지막 항목을 선택합니다.
해당 항목을 선택하면 우측에 새로운 패널이 나타나며, [Response] 탭을 클릭하면 이 URL이 반환하는 결과값을 확인할 수 있습니다. 결과값을 확인했다면 호출 URL을 살펴보기 위해 [Headers] 탭으로 이동합니다.
[Headers] 탭에서는 네이버의 연관검색어 목록을 반환하는 URL 주소를 확인할 수 있습니다. Request URL 주소를 복사한 뒤 메모장이나 엑셀에 붙여넣어 쿼리문 구조를 분석합니다.

API URL 요청 방식 두 가지

API를 호출할 때 사용하는 URL 주소 형식은 크게 두 가지로 나뉘며, 요청 방식은 정보 제공자가 정한 규칙을 따라야 합니다. 일반적으로 REST API(HTTP 요청, 웹 요청)를 통해 데이터를 가져오는 경우 첫 번째 방식이 자주 사용되며, 첫 번째 방식의 API는 WEBSERVICE 함수로 호출할 수 없다는 점에 유의해야 합니다.

① Request Header에 보안키를 담아 데이터를 요청하는 방식

가장 보편적으로 사용되는 방식입니다. Request Header에 보안키를 함께 담아야 하므로 WEBSERVICE 함수로는 데이터를 받아올 수 없으며, VBA나 다른 프로그래밍 언어를 사용해야 합니다. VBA 매크로로 크롤링하는 방법은 아래 영상 강의에서 자세히 설명해 드렸습니다.

API URL 요청 방식

② 쿼리문에 보안키 또는 조건을 함께 담아 데이터를 요청하는 방식

강력한 보안이 필요하지 않거나 대중에게 공개적으로 제공되는 API에서 사용하는 방식입니다. 기본 URL 주소 뒤에 ?를 붙인 다음 원하는 조건을 쿼리로 추가하여 특정 데이터를 요청합니다. URL 쿼리문은 다음과 같은 형태로 작성됩니다.

https://기본URL?조건1=값1&조건2=값2&조건3=값3...
예) https://www.naver.com?q=검색어&where=검색카테고리&ie=인코딩언어...

URL 쿼리문 구조

WEBSERVICE 함수로 연관검색어 가져오기

WEBSERVICE 함수로 크롤링하기 : 예제파일의 두 번째 시트인 [네이버 검색어] 시트로 이동한 후 G1셀에 아래 수식을 입력합니다. 엔터키로 수식을 입력하면 해당 단어의 연관검색어 결과가 G1셀에 출력됩니다. 결과가 정상적으로 표시되면 G1셀을 선택한 뒤 [홈] 탭 - [자동 줄 바꿈]을 활성화해 줄 바꿈이 포함된 전체 결과를 한눈에 확인합니다.
=WEBSERVICE("https://ac.search.naver.com/nx/ac?q="&C4&"&st=100")

오빠두Tip : WEBSERVICE 함수는 엑셀 2013 이후 윈도우 버전에서만 제공되므로 엑셀 2010 이전 버전이나 Mac 환경에서는 사용할 수 없습니다.
크롤링 결과 가공하기 : WEBSERVICE 함수로 받아온 문장 중 "items"로 시작하는 항목만 추출해 보겠습니다. G2셀을 선택한 뒤 아래 수식을 입력하면 연관검색어 응답 문장에서 items 부분만 추출됩니다.
=MID(G1,FIND("[[",G1),LEN(G1))
G2셀에 추출한 결과를 보면 대괄호([, ]), 큰따옴표("), 중괄호(}) 등 불필요한 기호가 포함되어 있습니다. 이번에는 SUBSTITUTE 함수로 불필요한 기호를 제거하겠습니다. 예제파일의 G4셀에 아래 수식을 입력하면 기호가 모두 제거된 깔끔한 문자열을 얻을 수 있습니다.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G2,"[",""),"]",""),"""",""),"}","")
연관검색어 나누어 출력 : 이제 연관검색어 목록이 쉼표(,)로 구분된 형태가 되었습니다. 이전 강의에서 살펴본 텍스트 나누기 공식을 활용해 연관검색어를 각 셀에 나누어 표시하겠습니다. 예제파일의 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
이번에는 C6셀을 선택한 뒤 HYPERLINK 함수로 클릭 가능한 링크를 생성합니다. C6셀에 아래 수식을 입력한 다음 자동 채우기로 아래쪽 셀까지 채워 줍니다.
=IFERROR(HYPERLINK(H6,G6),"-")

오빠두Tip : IFERROR 함수를 함께 사용해 결과값이 없거나 오류가 발생하면 하이픈(-)을 표시하도록 수식을 작성했습니다.
이제 각 셀을 클릭하면 해당 연관검색어의 검색결과 페이지로 바로 이동합니다.
연관검색어 서식 꾸미기 : 마지막으로 하이퍼링크가 적용된 셀의 서식을 정돈하겠습니다. C6:C15 범위를 선택한 뒤 글씨 색은 진한 회색으로, 밑줄은 없음으로 변경합니다. 이어서 [맞춤] 그룹에서 [들여쓰기]를 한 번 클릭해 한 칸 들여쓰고, 사용하지 않는 행과 열은 모두 숨겨 깔끔한 서식으로 마무리합니다.
이전 강의에서 소개한 기본값 설정 방법을 활용하면 검색할 단어가 비어 있을 때 "검색할 단어를 입력하세요"라는 안내 문구가 자동으로 표시됩니다. 마지막으로 기본값 안내까지 적용하면 네이버 연관검색어 서식이 완성됩니다.