[EXCEL] 엑셀기본함수(WEBSERVICE)로 네이버 연관검색어 크롤링하기

EXCEL

Yeenn 2021. 12. 28. 01:14

728x90

이번 포스팅에서는, 엑셀 기본함수인 WEBSERVICE 함수로 네이버 연관검색어를 크롤링하는 방법을 알아보겠다.

1. DATA형태, 대표적으로 JSON로 값이 반환될 때에만 사용 가능

(그 외(ex. HTML, TEXT)로 반환될 시 #VALUE 오류 출력)

2. 반환 결과값이 너무 길 경우(32,767자 이상) #VALUE 오류 출력

3. GET 요청만 보낼 수 있음(POST 요청ㅇㄹ 경우 #VALUE 오류 출력)

4. 웹 접속이 필요한 함수이므로, 최초 실행시 '콘텐츠 사용'으로 기능을 활성화해야 함

5. EXCEL 2010 이전 버전에서는 사용 불가, WINDOWS 엑셀에서만 지원

[응답(Response) 탭]

1. 네트워크 탭 켜놓고 검색창에 검색어를 누른다.

2. 마지막 링크를 클릭하고 응답 탭 을 누르면 연관검색어들이 텍스트 형태로 뜨는 것을 확인할 수 있다.

[헤더(Header) 탭]

1. 위와 동일

2. 마지막 링크 클릭하고 헤더 탭 누르면 요청 URL(Request URL)이 뜬다. 이를 복사하자.

3. 복사한 URL을 새 창에 복사해서 확인해보면, 네이버 검색 창에 나오는 연관검색어가 데이터로 반환되는 것을 확인할 수 있다. ('ㅅ파이더'는 스파이더맨 오타난 것..)

따라서,

https://ac.search.naver.com/nx/ac?q=스파이더맨&st=100 만 남기면 '스파이더맨'으로 반환되는 연관 검색어 목록 출력이 아래와 같이 가능하게 된다.

이후, 엑셀로 다시 와서, 위와 동일한 주소의 q="& &" 안에 검색어를 입력하고자 하는 셀 주소를 넣는다.

이후, 위 수식앞에 WEBSERVICE 함수를 넣고 괄호를 닫아주면,

엑셀 검색창에 검색어를 입력할 때 마다 해당 검색어의 연관검색어를 우측 수식란에서 확인할 수 있다.

쿼리가 포함된 셀의 너비를 늘리고, 자동 줄바꿈을 선택하면, 조금 더 편하게 연관검색어 결과를 볼 수 있다.

크롤링을 통해 불러온 값을 살펴보면, "query"와 "items"부분은 사실 불필요한 부분이다. 아래 대괄호부터 찾고자 하는 연관검색어가 있기 때문에,

우선 우측 셀에서 FIND함수를 사용하여 대괄호의 위치를 찾는다.

이후, 아래 셀에서 MID함수를 통해, 33번째 위치부터 LEN(전체입력한 문자개수만큼) 글자를 끊어온다.

다음과 같이 대괄호에 포함된 결과값만 반환된다.

SUBSTITUTE 함수를 통해 앞 대괄호 > 뒤 대괄호 > 큰 따옴표 > 마지막 중괄호 순으로 ""(공백)으로 대체해주면 된다.

검색어를 바꾸어 입력했더니, 깔끔한 결과값이 반환된 것을 확인할 수 있다.

포스팅에 사용한 엑셀 파일은 참고용으로 하단에 첨부해두었다.

※ 해당 포스팅은 '오빠두엑셀' 유튜브 채널의 '크롤링, 아직도 코딩하세요?' 영상을 참고하여 작성되었습니다.

728x90