엑셀 VBA 크롤링, 이 영상 하나로 마스터하세요! | 초보자 핵심 정리 | 구글 연관검색어 실시간 조회 — Transcript

Full transcript

1. 0:00여러분 안녕하세요 오빠두입니다
2. 0:03자 오늘 강의를 마무리하시면 이런 식으로 단어를 검색했을 때
3. 0:08실시간으로 연관검색어가 조회되는 서식을 만들 수 있습니다
4. 0:12그리고 검색 언어를 변경 하면 이렇게 한국어,
5. 0:16그리고 영어, 중국어까지도 검색할 수 있고요
6. 0:20그리고 검색 결과를 클릭하면
7. 0:22이런 식으로 해당 링크로 이동하는 서식까지 모두 완성할 예정입니다
8. 0:27지난번에 알아봤던 네이버 검색어 크롤링 강의에서는
9. 0:30엑셀함수만 사용하는 방법을 알아봤는데요
10. 0:33이 함수를 사용하는 방법은 내용이 쉬운 만큼,
11. 0:36사용하는데 제한 사항이 많이 있습니다
12. 0:39그래서 오늘 강의에서는 어느 웹페이지든 원하는 정보를 가져오는
13. 0:43엑셀 VBA 매크로를 알아볼 예정인데요
14. 0:46VBA를 처음 사용하는 초보자도 누구나 손쉽게 만들 수 있도록
15. 0:50기초부터 꼼꼼하게 정리했습니다
16. 0:53자 우선 영상 댓글에 적어드린 링크를 클릭해서
17. 0:56예제 파일을 다운 받아 주시고요
18. 0:58예제 파일을 실행하시면 화면 위쪽에 [편집사용]과 [콘텐츠 사용]버튼이 나옵니다
19. 1:03이 버튼을 클릭해서 매크로를 포함 해주시면
20. 1:06강의를 진행하기 위한 모든 준비가 끝납니다
21. 1:09만약에 안내창이 안 보이시거나 또는 화면에 보이시는 것처럼
22. 1:13빨간색 안내창이 나올 경우에는 영상 댓글에 적어드린 링크를 참고해서
23. 1:18설정을 바꾼 뒤에 진행해주세요
24. 1:21그리고 오늘 강의는 이전에 진행했던 라이브 강의의 중요내용 편집 영상입니다
25. 1:26라이브 전체 영상은 오빠두엑셀 위캔두 멤버십 회원으로 가입하시면
26. 1:31매주 토요일에 진행하는 라이브 강의 전체 영상을
27. 1:33시청할 수 있으니 참고해주시길 부탁드리겠습니다
28. 1:36그럼 오늘 강의 시작하겠습니다
29. 1:44예제파일 실행 하신 다음에 오늘은 매크로를 쓸거죠
30. 1:47그래서 매크로 편집기를 실행을 할 건데요
31. 1:50자 엑셀화면 탭 목록을 자세히 보시면
32. 1:53여기에 홈탭, 삽입탭 등이 있죠
33. 1:56여기 오른쪽에 보시면 아마도 개발도구 탭 이 보이시는 분이 있고
34. 2:01안 보이실 분도 계실거에요
35. 2:02네 맞습니다. Alt + F11 키에요
36. 2:04그래서 Alt + F11 키를 동시에 누르시면 이렇게 매크로 편집기가 실행됩니다
37. 2:10또는 여기 개발도구창이 안 보실 때는 여기 그 리본메뉴를 우클릭하시면
38. 2:16여기 리본메뉴 사용자지정 보이실거예요
39. 2:19여기 오신 다음에
40. 2:21중간에 보시면 개발도구 라는 리본메뉴가 있어요
41. 2:25이거 체크 하신 다음에 확인 누르시면
42. 2:29이렇게 위쪽에 개발도구가 추가되고요
43. 2:31여기 오신 다음에 여기 Visual Basic 버튼이 있습니다
44. 2:35버튼 클릭하면 매크로 편집기가 실행되요
45. 2:40그리고 나서 이제 매크로 편집기 구조를 한번 간단하게
46. 2:45오늘은 기초강의 니까
47. 2:46구조가 어떻게 이루어져 있는지 한번 살펴 볼 건데요
48. 2:50아마 처음 실행하시면 왼쪽에는 프로젝트 창이랑
49. 2:53속성창이 보이실겁니다
50. 2:55그래서 이 프로젝트 창을 자세히 보시면
51. 2:58여기에는 우리가 현재 실행 중인 통합문서나 또는 추가기능 등등이 보이실거에요
52. 3:04이 통합문서의 개수는 실행 중인 통합문서에 따라서 다르게 보이겠죠
53. 3:09아마 지금 여러 개를 실행중이라면
54. 3:11여기 파일이 여러개 보이실 거고요
55. 3:13또는 파일 한 개만 실행 중이시라면 이렇게 한 개만 보시겠죠
56. 3:17그리고 이제 통합문서를 보시면
57. 3:19여기 밑에 마이크로소프트 개체 또는 모듈 이렇게 있는데
58. 3:23크게 3가지로 나누어져요
59. 3:25첫 번째는 시트입니다
60. 3:26우리가 파일을 만들면은 그 안에다가 시트를 여러개 만들잖아요
61. 3:30그쵸? 그래서 시트가 여러개 들어가고요
62. 3:33그 다음은 모듈이 들어가요
63. 3:35이 모듈은 우리가 명령문을 작성하기 위한
64. 3:39어떤 하나의 노트라고 보시면 되고요
65. 3:41그 다음은 클래스모듈이 들어갑니다
66. 3:44이 세 가지가 들어가는데
67. 3:46근데 사실 클래스모듈은 이게 전문가 수준으로
68. 3:50고급 단계로 넘어가지 않는 이상
69. 3:52사실 잘 안 쓰지 않는 모듈이기 때문에
70. 3:55이거는 크게 중요하지 않고 넘어가도 괜찮은 내용이고요
71. 4:00우리가 실제로 많이 쓰는 거는 시트와 모듈이이에요
72. 4:04그래서 각각의 시트랑 모듈 안에다가 명령문을 쓸 건데
73. 4:09이 두 개 차이점이 무엇이냐하면
74. 4:12시트에 들어가는 명령문은
75. 4:14그 시트 안에서만 쓸 수 있어요
76. 4:16예를 들어서 어떤 시트에 값이 입력되었을 때
77. 4:20어떤 동작을 실행 해라 또는 이 시트에서만 어떤 동작을 실행할 수 있도록
78. 4:27명령문을 쓰는 거에요
79. 4:29그래서 예를 들어서 예제파일 보시면 현재 구글 검색어란 시트가 있죠
80. 4:33이 시트를 더블 클릭하시면
81. 4:35이렇게 시트 안에 명령문을 넣을 수가 있습니다
82. 4:39그죠 여기에 작성하는 명령문은 현재 내가 보고 있는
83. 4:43구글 검색어라는 시트 안에서만 실행 가능한 명령문이 들어간다
84. 4:49라고 보시면 되겠고요
85. 4:50그 다음은 모듈인데 이 모듈은 어떻게 추가 하냐하면
86. 4:54위에 보시면 삽입 보이실 거예요
87. 4:56여기 삽입 오신다음에 여기에서 모듈 클릭해 볼게요
88. 5:01모듈을 클릭하면 이렇게 모듈이라는 폴더가 보이면서
89. 5:06모듈이 추가되었죠
90. 5:07모듈을 더블클릭하시면
91. 5:10여기 작성 명령문은 우리가 앞으로 그 매크로를 실행할때마다
92. 5:15모든 시트에서 실행할 수 있는 함수나
93. 5:18또는 명령문을 작성하는 노트가 만들어진 거에요
94. 5:22그래서 오늘 우리가 실행할 명령문은
95. 5:25사실 구글 검색어라는 시트에서만 실행 되기 때문에
96. 5:31사실 여기 시트 명령문이죠
97. 5:33여기 시트 안에다가만 작성해도 사실 괜찮지만
98. 5:38근데 오늘은 모듈에다가 넣어 줄 거에요
99. 5:41그래서 이 모듈을 더블클릭 하신 다음에 여기에다가 명령문을 작성해줄거구요
100. 5:47그리고 나서 이 모듈을 더블클릭하시면 속성 보이실 거예요
101. 5:51그죠 이 속성에 오신다음에 한번 이름을 바꿔볼게요
102. 5:55아마 여기 속성이 안 보실 때는요
103. 5:57키보드 F4 키에요. F4 키를 누르시면 이렇게 속성창이 보입니다
104. 6:03그리고 여기에서 한번 이름을 바꿔 볼게요
105. 6:07mod_googlesearch 라는 이름으로 바꿔 주시면
106. 6:14이렇게 위에 모듈 이름이 같이 바뀌는 걸 볼수있죠
107. 6:19그 다음에 이제 모듈은 만들었으니까
108. 6:22오늘 우리가 진행할 크롤링 명령문을 넣어 줄 건데요
109. 6:25명령문은 오빠두엑셀 홈페이지에 오시면 완성된 코드를 미리 작성 해 드렸어요
110. 6:32여기에 명령문 전체 코드 보이실 거예요
111. 6:35코드를 복사 해 볼게요. 쭉 드래그 하신 다음에
112. 6:39여기에서 위에를 선택하신 다음에
113. 6:41드래그를 쭉 하시고 나서 이 스크롤을 쭉 내려 볼게요
114. 6:46쭉 내린 다음에 Shift 키를 누른상태로
115. 6:49마지막 명령문을 선택하시면
116. 6:52전체 명령문이 선택되요. 그쵸?
117. 6:54선택하신 다음에 복사 해 볼까요?
118. 6:56Ctrl + C 로 복사하신 다음에 여기에다가 붙여 넣기 할게요
119. 7:02이제 위에다가 본격적으로 명령문을 넣어 보겠습니다
120. 7:05자 그다음에 엔터키 입력해서 줄을 띄워주신 다음에
121. 7:10여기에다가 Sub 을 적어줄게요. 띄어쓰기하신 다음에
122. 7:15오늘 강의에서는 구글을 검색할 거니까
123. 7:19GetGooleKeyword 라는 명령문을 넣어볼게요
124. 7:24여기에서 Sub은 무엇이냐면 우리가 그 매크로를 실행 할 건데
125. 7:29각각의 명령문을 작성할 때 약자로 Sub 이라고 적어 주는거고
126. 7:35Sub 띄어쓰기를 적으시면 이제 뒤에 있는 명령문이 만들어진 거죠
127. 7:39이제 여기서 Sub을 적으면 뒤에 End Sub이 붙죠
128. 7:41그래서 이 사이에 있는 명령문이 우리가 방금 만든
129. 7:46GetGoogleKeyword 라는 하나의 매크로 코드가 만들어진거에요
130. 7:51그 다음은 변수를 만들어 줄게요
131. 7:52밑에 오신 다음에 dim 이에요.
132. 7:56Dim 띄어쓰기, s 라는 값을 무엇으로 만들 거냐
133. 8:03String 으로 만들어 줄게요
134. 8:05이렇게 만들어 주시면 우리가 명령문을 만들었어요
135. 8:09이거를 실행을 할 건데
136. 8:11이걸 실행하기 전에 컴퓨터에다 알려 주는 거에요
137. 8:14자, 컴퓨터야. 내가 이걸 실행을 할 건데
138. 8:17이걸 실행하기 전에 내가 이 매크로를 실행할 때
139. 8:20s 라는 값은 String 이에요. 이건 문자거든요
140. 8:25"s 라는 값을 문자로 쓸 거예요" 라고 미리 알려주는 거에요
141. 8:31그러면 컴퓨터는 이걸 미리 알려 주는 거니까
142. 8:33메모리를 미리 할당할 수 있겠죠 그죠?
143. 8:36그래서 이 dim 은 약자가 Declare In Memory 에요
144. 8:42다시 말해서 s 라는 변수를
145. 8:45메모리에 미리 할당할 수 있도록 알려 주는 거에요
146. 8:49그래서 변수를 할당하는 작업을 해 준 겁니다
147. 8:52그 다음으로는 똑같이 dim 으로 v를 만들어 줄 건데
148. 8:57이거는 Variant 라는 변수로 만들어줄 거고요
149. 9:01그리고 dim 으로 i 는 Long 이라는 변수로 만들어줄게요
150. 9:06여기에서 Variant는 모든 걸 받아올 수 있어요
151. 9:09이 String 은 문자만 입력하는 거고요
152. 9:12Long은 정수만 입력할 수 있고
153. 9:15Variant 는 모든 것을 받아올 수 있습니다
154. 9:18자 여기에서 이 변수를 왜 할당하냐 라고 하면
155. 9:22사실 VBA 는요 우리가 변수를 만들지 않아도 자동으로 할당을 해요
156. 9:28그렇기 때문에 매크로를 사실 처음 사용하시는 분들은
157. 9:31이 변수를 굳이 할당 하지 않아도 자동으로 할당이 돼요
158. 9:35근데 문제는, 변수를 할당 하지 않잖아요?
159. 9:39그러면 변수가 할당되지 않기 때문에 지금 보세요
160. 9:43제가 Variant 는 이거는 모든 것을 받아 올 수 있다고 말씀을 드렸어요
161. 9:48다시 말해서 어떤 개체나 또는 문자나 또는 숫자 나
162. 9:54다 받아올 수 있습니다
163. 9:55그 말은 이 Variant 라는 변수는
164. 9:58더 많은 메모리를 소비 하겠죠 그렇죠?
165. 10:01그리고 보시면 지금 String 은 이건 문자만 받아올 수 있고
166. 10:05이거는 숫자만 받아올 수 있어요
167. 10:08그렇기 때문에 적은 메모리를 소유한다는 거죠
168. 10:12그렇죠 그렇게 때문에
169. 10:13이렇게 변수를 선언해주면은 메모리가 절약되요
170. 10:16사실 이거는 크게 요즘에는 PC 가 너무 좋아졌기 때문에
171. 10:20사실 큰 차이는 없습니다
172. 10:22다만 이런 차이가 있는 거고요
173. 10:25자 그 다음을 볼게요 그 다음으로는 변수를 만들었으니까
174. 10:27한번 한번 직접 명령문을 작성 해 볼게요
175. 10:31우리가 방금 전에 밑을보시면 GetHTTP 라는 명령문을 만들어줬어요
176. 10:37이거는 무엇이냐면
177. 10:38URL 주소로 반환되는 결과를 출력하는 거에요
178. 10:42그래서 우리가 방금 전에 s 를 만들었잖아요 그죠?
179. 10:45그래서 s를 만들어 줄건데, s 는 무엇이냐
180. 10:49GetHTTP 라는 명령문으로 받아 올 거예요
181. 10:54그리고 받아올 URL은 www.naver.com 입력할께요
182. 11:02받아올 URL 주소는 네이버로 받아 올 거고
183. 11:05뒤에다가 body.innerhtml 이렇게 적어줄게요
184. 11:12이렇게 적는 이유는요
185. 11:13뒤에 이제 이렇게 들어가는거는 기본설정 값이에요
186. 11:16그렇기 때문에 이거는 나중에 혹시라도
187. 11:21GetHTTP 항수로 값을 바로 오실 때에는
188. 11:24뒤에다가 .body.innerhtml 적어준다라고 외우시면 좋을 것 같구요
189. 11:30여기에 대한 예제는 함수 페이지 있죠?
190. 11:32여기 위에 보시면 다 적어 드렸어요
191. 11:35이렇게 예제를 보시면은 이렇게 보면은 body.innerhtml 그렇죠?
192. 11:41여기에도 html 받아 오는 거 보이시죠?
193. 11:44이런 식으로 적어서 만들어 준 거구요
194. 11:47이렇게 하신 다음에 MsgBox 로 s를 띄워볼게요
195. 11:52밑에 다가는 우리가 방금 전에 받아 본 네이버였죠?
196. 11:56네이버라는 페이지의 html 결과물을
197. 12:00MsgBox 로 띄워줄거라고 적어준거에요.
198. 12:04그리고 나서 위에 보시면 재생 버튼 보이실 거예요
199. 12:08이 재생 버튼을 클릭하시거나
200. 12:09또는 여기 명령문 선택하신다음에
201. 12:12키보드 F5 키에요. F5 키를 딱 누르시면 잠시만요
202. 12:19이게 그 윈도우 10을 사용하시는데
203. 12:23업데이트가 되신 분들은 이게 보안 문제 때문에 안 되실 거예요
204. 12:28그러실 때에는 그 뒤에다가 콤마 찍으신 다음에
205. 12:31IncludeMeta 라는 변수를 땡땡 등호 입력하신 다음에
206. 12:35True로 이렇게 바꿔 주세요
207. 12:37이렇게 하셔야지만 됩니다
208. 12:38그래서 혹시라도 이제 사용하시면서 저처럼 개체 오류가 발생 하실 때에는
209. 12:43이렇게 IncludeMeta 를 True로 하신 다음에 실행하시면 잘 되실 거예요
210. 12:48우리가 이번에는 네이버를 받아왔잖아요
211. 12:50근데 우리가 지금 받아 와야 될 거는 구글에 검색 결과에요
212. 12:55그중에서도 이제 검색 결과가 아니라
213. 12:57구글에 와서 예를 들어서 오징어를 검색을 하면
214. 13:02여기 밑에 나오는 검색 결과를 보고 싶은 거 잖아요
215. 13:05여기에서 이 결과를 받아 올 수 있는 URL 주소를 받아 올 거에요
216. 13:11어떻게 하냐면 크롬도 가능하고, 웨일, 엣지 다 가능 하세요
217. 13:16크롬에 오신 다음에 단축키는 F12 키에요.
218. 13:19F12 키를 누르시면 이렇게 개발자도구가 나옵니다
219. 13:24그쵸? 이렇게 보이실 거고
220. 13:25만약에 F12 키가 없을 때에는 여기 위에 설정 버튼 보이실 거예요
221. 13:31그쵸? (...) 아이콘 클릭하신 다음, 도구더보기 에서 개발자 도구 입니다
222. 13:36이거 선택하시면 이렇게 나올 거에요
223. 13:38그래서 이 개발자도구는 오른쪽으로 가져와 주시고요
224. 13:42인터넷 창을 왼쪽으로 가져와서 진행해 볼게요
225. 13:47자 그다음에, 개발자도구 목록을 자세히 보시면
226. 13:49여기 Element, Console, Source, Network 등.. 여러 개 있죠
227. 13:55여기에서 네트워크로 와볼게요 밑을 보시면
228. 13:58네트워크로 오면 밑에 목록에 쭉 나오실 거예요
229. 14:01여기에다가 유튜브를 검색을 하면 검색한 결과가
230. 14:05여기 밑에 보이시는 검색 결과가 달라지는게 보이실거에요
231. 14:09그쵸? 여기에서 맨 밑에있는 search로 된 항목을 선택하신 다음 Response에 보시면
232. 14:19우리가 유튜브를 검색했을 때 검색 결과가 반환됩니다
233. 14:23그쵸? 우리가 알 수 있는 것은
234. 14:25Headers 에 와서 보시면, 여기 있는 url 을 검색을 하면
235. 14:30우리가 검색했던 그 연관검색어가 받아와 지는구나라고 알수있는 거죠
236. 14:34이 Headers에 있는 url을 복사 할게요
237. 14:38이 주소를 복사하신 다음에 메모장에 와서 메모장에다가 붙여 넣기 해 볼까요
238. 14:45붙여넣기 하시면 이렇게 우리가 방금 검색했던 결과를 볼 수 있습니다
239. 14:51url 주소죠?
240. 14:52여기에서 우리가 필요한 건 무엇이냐면
241. 14:55앞에 보시면 쿼리문 보실 거예요 그죠?
242. 14:57우리가 지난 시간에 알아봤지만 이 URL주소를 봤을 때
243. 15:01물음표를 기준으로해서 이 앞에 있는 거는 기본 URL 이고요
244. 15:06물음표 뒤에 있는 거는 쿼리문이라고 알아봤어요
245. 15:10여기에서 우리가 필요한 거는 일단 이 q 값이에요
246. 15:13& 기호 앞까지가 q 로 반환되는 값이겠죠
247. 15:16이 q의 경우에는 검색 단어예요
248. 15:19키워드가 들어가는 거고요
249. 15:21뒤에 cp는 지워줄게요. cp는 지워도 괜찮은 값이고
250. 15:25이 client는 중요한거기 때문에 client는 남겨 주시고요
251. 15:29그리고 xssi는 불필요해요 이거 지워줄게요
252. 15:33그 다음에는 hl은 언어거든요
253. 15:37이거는 검색언어니까 이거는 그대로 남겨주고 진행 하겠습니다
254. 15:43그리고 여기 authuser는 중요한 값이에요.
255. 15:46이것도 그대로 유지해야 되겠고요
256. 15:47나머지 뒤에 있는 거 psi 이런 값들은 지워 주겠습니다
257. 15:52이렇게 지워주시면 현재 들어간 값은 이거죠
258. 15:55검색한 단어, q는 무엇이고 클라이언트는 gws-wiz 네요
259. 16:03검색언어는 "ko" - 한국어고
260. 16:07그리고 현재 로그인한 유저는 0 인데
261. 16:11이건 무엇이냐하면 그 로그인 페이지를 보시면
262. 16:14구글은 그 로그인한 유저가 여러 명이 있을 거에요
263. 16:17그 첫 번째에 있는 유저이다 라고 알려 주는 거에요
264. 16:22이거는 반드시 필요한 값이니까
265. 16:24이렇게 만들어서 넣어 준 거예요 그죠?
266. 16:26복사하신 다음에 복사해서 다시 vba 창에 오신 다음에
267. 16:32여기에다가 GetHTTP 함수로 받아오는 url 주소 에다가
268. 16:37이거를 붙여넣기 할게요
269. 16:39이렇게 만들어 주시면
270. 16:41우리가 방금 전에는 네이버로 받아왔는데
271. 16:45우리가 방금 구글 연관검색어 url 주소를 받아 온 거죠
272. 16:48복사하신 다음에 gethttp 라는 함수 안에다가 붙여 넣기 한 거에요
273. 16:54그리고 나서 키워드를 바꿔 주면 되겠죠
274. 16:57키워드를 만약에, "오징어"로 해 볼까요?
275. 17:02오징어로 바꾸신 다음에
276. 17:04명령문을 실행하시면 보시는 것처럼
277. 17:07오징어라는 검색 결과로 반환 되었죠 그렇죠?
278. 17:13그리고 지운 다음에 이번에는 키워드를 달고나로 하면
279. 17:18이렇게 달고나에 해당하는 검색 결과가 안내 창으로 표시가 됩니다
280. 17:24이제 그 다음 단계를 볼게요
281. 17:25이제 그 다음으로는 이 검색 결과를 자세히 보시면
282. 17:29우리가 방금 봤던 검색어가 있고
283. 17:32검색어 앞/뒤를 보시면 이렇게 U003C, U003E 이런 게 보이실 거에요
284. 17:40이거는 그거예요
285. 17:41html 문서를 만들 때 <b>, </b>를 넣어주면
286. 17:45이거는 사이에는 값을 굵은 글씨로 표시해요
287. 17:48이게 왜 필요하냐 하면, 구글 검색 결과를 보시면요
288. 17:52우리가 검색을 할 때마다 이렇게 검색 결과를 제외한
289. 17:56나머지 단어는 굵게 표시가 돼요
290. 17:59이거를 표시하기 위해서 이렇게 검색 결과를 보시면
291. 18:04<b> 와 </b> 를 표시하기 위한 거거든요
292. 18:12이거를 지워줘야 하는데 한번 지워볼게요
293. 18:14그래서 우리가 방금 전에는 이렇게 안내창으로 봤거든요
294. 18:19이거를 지워줄게요
295. 18:22그 위에 보기탭을 시면 여기에 '직접 실행창' 이란 버튼 보실 거예요
296. 18:28여기에 직접 실행창 보이시죠?
297. 18:31이거를 클릭해서 보이게 해 볼게요
298. 18:35그럼 이렇게 왼쪽 아래 직접 실행창이 보이실 거예요
299. 18:39여기 오신 다음에 우리가 방그 s를 만들었잖아요
300. 18:43그래서 이번에는 debug.print 에요.
301. 18:47debug.print 를 사용해서 s를 띄워 줄게요
302. 18:51이렇게 하신 다음에 명령문을 실행하시면
303. 18:55검색 결과가 이렇게 나오시죠
304. 18:58직접 실행창에 나오실 거예요
305. 19:00그죠 여기에서 이걸 복사해 가지고 지워 주면 되겠죠
306. 19:04첫 번째로는 s를 지워 줄 건데
307. 19:07s 에서 Replace 함수로 찾을 값은 이거예요
308. 19:14첫 번째로 \wu003cb\u003e 를 빈칸으로 지워 주고요
309. 19:21두 번째로 Replace 함수로 s 에서 뒤에 있는 거죠
310. 19:26뒤에 보시면 \wu003c\/b\u003e 지워줄게요
311. 19:29이렇게 지워주신 다음에
312. 19:30이번에는 Debug.Print 잘라내기하신 후에
313. 19:34밑에다가 붙여 넣기 해 볼게요
314. 19:39이렇게 하고 실행하시면
315. 19:40이번에는 이렇게 중간에 있던 이 값들이 지워져서 출력되겠죠.
316. 19:48아까 우리가 만들었던 s 에는 이런 굵게 표시하는 기호가 있었는데
317. 19:53이 기호를 Replace 함수로 지워 주는 거예요
318. 19:56지워 주고 나서 출력을 하니까
319. 19:58이렇게 쭉 표시가 된 걸 볼 수 있죠
320. 20:01그 다음으로는 여기에서 검색 결과를 자세히 보시면
321. 20:05우리가 필요한 검색 결과는요
322. 20:08이거죠. 대괄호가 3개 들어간 부분부터가
323. 20:13검색 결과에요. 그래서 대괄호가 3개 들어가기 전
324. 20:18앞에 있는 것은 불필요한 값이죠
325. 20:20그래서 대괄호가 3개 시작하는 지점을 기준으로 잘라주는거에요
326. 20:27어떻게 잘라줄거냐하면, 이번에는 split 함수를 쓸 건데요.
327. 20:31자 s를 다시 불러 줄 건데, 이 s 를요 Split 으로 자를 거예요
328. 20:37자를 기호는 "[[["죠? 이렇게 잘라주면
329. 20:43이건 무엇을 반환하냐 하면
330. 20:45우리가 방금 전에 불러왔던 s 는 이거였어요
331. 20:48이걸 Split 으로 잘라줄건데
332. 20:50이거 잘라 주면 "[[[" 기호를 기준으로 배열이 반환되요
333. 20:56그래서 첫 번째 값으로는 이게 나오고
334. 20:59두번째로는 뒤에있는게 나오거든요 그죠?
335. 21:02그리고 우리가 필요한 거는 두 번째 있는 값을 불러오면 되겠죠
336. 21:07그쵸? 두 번째는 걸 받아오면 되기 때문에
337. 21:10Split 함수로는 뒤에다가 1을 넣어서 이렇게 불러 올 거예요
338. 21:16여기에서 주의사항은, Split 함수는 결과값을 쪼개겠죠
339. 21:20이 배열은 기본값으로 "0"이 첫 번째로 시작하기 때문에
340. 21:24거기에서 두 번째에 있는걸 받아오기 때문에
341. 21:26"1" 을 넣어서 두 번째 있는 값을 불러 오는 거에요
342. 21:30자 그리고 나서 다시 볼게요
343. 21:31똑같이 debug.print 로 이번엔 s 를 불러볼까요
344. 21:35똑같이 밑에 오신 다음에, s 적어주시고요 실행하시면
345. 21:39이런식으로 이번에는 "[[[" 가 잘리구요, 그렇죠?
346. 21:43이게 지워지고 뒤에는 값만 반환되겠죠
347. 21:46그 다음으로는 똑같이 나눠 줄 건데,
348. 21:50자세히 보시면 우리가 필요한 단어가 무엇이냐 하면
349. 21:55여기죠. 시작하는 대괄호([) 와 큰 따옴표(")를 기준으로 잘라 주면 될 거예요
350. 22:05그죠? 여기 있는 기호랑 큰따옴표를 기준으로 잘라 주면 키워드가 잘려지겠죠
351. 22:15뒤도 잘 보자고요
352. 22:16뒤 쪽도 쭉 보시면, 이렇게 다 시작하는 단어가
353. 22:19대괄호와 큰따옴표러 잘려 있어요
354. 22:22그래서 이번에는 대괄호 큰따옴표를 기준으로 잘라줄겁니다
355. 22:27그래서 위에 오신 다음에 똑같이 이번에는 v를 만들어 줄 건데
356. 22:31배열을 만들 거예요
357. 22:32배열은 방금 만들어던 s를 잘라 줄 거죠
358. 22:36Split 으로 s 를 잘라 줄 건데 자를 값은 시작하는 대괄호([) 였죠?
359. 22:43시작하는 대괄호([)가 있는거고
360. 22:45뒤에는 큰따옴표 두 개("") 이렇게 넣어 주는 거죠
361. 22:49이 큰따옴표를 넣어줄 때, 앞뒤에 있는 큰 따옴표는
362. 22:54문자를 구분하는 기호라고 말씀을 드렸고요
363. 22:57그리고 중간에 있는 두 개 연속된 큰따옴표("")를 넣어 주면
364. 23:01이거는 큰따옴표 자체로 입력 된다라고 말씀을 드렸어요
365. 23:05그래서 여기 있는 대괄호와 큰따옴표 두 개(["")를 넣어주시면
366. 23:08이거는 대괄호 큰따옴표([")를 검색해서 나눠 주는 게 되겠죠
367. 23:13그리고 나서 뒤에다가 Stop을 적어볼게요
368. 23:16그리고 보기창에 보시면 지역창 보이실 거예요
369. 23:20지역 창을 활성화하신 다음에, 명령문을 실행 해 볼게요
370. 23:28이렇게 밑에 v가 보이실 겁니다
371. 23:30우리가 방금 전에 만들었던 배열이 만들어진거고요
372. 23:33이 v의 + 버튼을 클릭하시면
373. 23:36여기 달고나 만들기 세트 이런 식으로 단어가 나누어진 게 보이실 거예요
374. 23:42이렇게 단어를 나누어 주신 다음에 진행하면 되겠습니다
375. 23:46이 중에서 쉼표죠. 쉼표를 기준으로 앞에 있는 값들만 불러 오면
376. 23:55우리가 필요한 연관 검색어만 반환되겠죠
377. 23:59그래서 똑같이 명령문을 작성 해 줄 건데
378. 24:02이번에는 중단 하신 다음에 왜 중지기호를 클릭해서 중단 하신 다음에
379. 24:09Stop을 지워주고요
380. 24:11자 이번에는 For 문을 사용 해서 적어줄거예요
381. 24:13For 문은 어떤 거를 반복하는 거에요
382. 24:17반복하는 건데 한번 적어볼게요
383. 24:34그리고 밑에는 Next 로 마무리하겠습니다
384. 24:37이 For 문은 무엇이냐면 우리가 아까 방금 전에 i 라는 변수를 만들었어요
385. 24:43이건 정수였죠? Lbound는 무엇이냐면
386. 24:47우리가 배열을 만들었거든요?
387. 24:49다시 볼게요. 배열을 자세히 보시면 배열이 현재 0부터 9까지 있죠.
388. 24:57그래서 Lbound 는 Lower Bound 에요
389. 25:00가장 배열의 작은 값, 첫 번째 값인 "0" 이구요
390. 25:05UBound 는 UpperBound 에요. 그래서 가장 큰 값인 "9"가 반환되요.
391. 25:10그래서 LBound(v)의 경우에는 가장 작은 값인 0이죠
392. 25:17UBound는 9까지 반환하는거에요.
393. 25:20이 For 문은요, i 라는 값이 숫자잖아요
394. 25:24이 숫자를 0부터 9까지 돌아가면서 반복하는 거예요
395. 25:30밑에다가 적어줄건데, Debug.Print 에요.
396. 25:36우리가 볼 것은 Instr 이거든요. 이건 찾는 거예요
397. 25:42시작 위치는 첫 번째 문자부터 볼 겁니다
398. 25:45우리가 검색할 문자 전체 문자는 이 배열의 i 번째 값이죠
399. 25:51왜냐하면 i는 현재 0부터 9까지 돌아가면서 값을 보는 거고
400. 25:58그러면은 이 v의 첫 번째값, v의 두번째 값, 세 번째, 네 번째로 가면서
401. 26:05아홉 번째 값까지 돌아가면서 검색을 하겠죠
402. 26:08그리고 나서 검색할 단어는 쉼표를 검색을 할 거예요
403. 26:12근데 우리가 이제 봐야할 거는, 이것도 봐야 돼요
404. 26:16그 앞에 큰 따옴표 까지 봐야 되기 때문에
405. 26:19우리가 검색할 위치는 이렇게 앞에 큰따옴표를 추가 해서
406. 26:23이런 식으로 이렇게 넣어 주시면 인제 큰따옴표가 있고
407. 26:28큰 따옴표 쉼표(",) 라는 문자가 몇 번째 있는지 검색하는 거에요
408. 26:34우리가 엑셀에서 FIND 함수 아시죠?
409. 26:36FIND 함수는 문자가 몇 번째 있는지 그 단어의 위치를 검색 하잖아요
410. 26:41그것처럼 Instr 함수는요, 첫 번째 위치부터 그 전체 문자 중에
411. 26:47큰 따옴표와 쉼표가 있는 문자가 몇 번째 있는지를 검색하게 됩니다
412. 26:53그래서 이렇게 적어 줄게요
413. 26:56명령문을 실행 해 볼게요
414. 26:58다시 명령문을 실행하면, 이런 식으로 숫자가 반환되겠죠
415. 27:04그래서 첫 번째 값은 5번째, 8번째, 11번째, 7번째 이런 식으로
416. 27:12그 큰 따옴표와 쉼표(",)가 있는 기호가
417. 27:15이 문자에서 몇 번째 있는지를 불러온 거에요
418. 27:18우리가 방금 전에 배열을 만들었어요
419. 27:21달고나를 검색을 했고
420. 27:23그 달고나를 검색한 연관검색어가 배열로 만들어진거에요
421. 27:28Instr 함수를 사용해서 큰 따옴표 쉼표(",)라는 각각의 기호가
422. 27:35검색된 결과에서 몇 번째 있는지를 본 거죠
423. 27:39그래서 밑을 보시면 첫 번째 값은 5번째, 두 번째는 8번째, 11번째 이런 식으로
424. 27:45그 큰 따옴표 쉼표가(",) 몇 번째 있는지를 불러온 거예요.
425. 27:50그러면 끝났죠? 이제 LEFT 함수를 사용해서
426. 28:04이렇게 적어 주시면 v의 i번째 값이에요.
427. 28:07지금 여기보시면, i는 LBound부터 0~9 까지 돌아가는거구요.
428. 28:13쭉 돌아가는거고
429. 28:16첫 번째는 달고나 이거였죠
430. 28:19여기에서 우리가 처음 검색했던 이 큰 따옴표 쉼표라는 문자는
431. 28:24현재 다섯 번째라고 검색을 했어요 그렇죠?
432. 28:27그러면 여기 있는 거
433. 28:28여기 있는 Instr은 5를 반환하겠죠
434. 28:33그리고 나서 LEFT 함수를 사용해서
435. 28:36우리가 보고 있는 이 문자열에서
436. 28:39다섯 번째 있는 값까지 잘라 주는 거죠
437. 28:43그러면 앞에 있는 달고나가 반환되겠죠
438. 28:47두 번째로 마찬가지로 달고나 만들기
439. 28:50그다음은 세트 이런 식으로 돌아가면서 문자가 만들어 질 겁니다
440. 28:56그죠 이제 이 상태에서 다시 명령문을 실행 해 볼게요
441. 28:59직접 실행창을 지워주신 다음에 Ctrl + A 로 모두 선택해서 지워 주시고요
442. 29:04그리고 나서 명령문을 다시 실행하시면
443. 29:08이렇게 우리가 보고자 하는 연관검색어만 반환되는 걸 볼 수 있죠
444. 29:13잘 되셨나요?
445. 29:15여기까지만 하시면 사실 거의 다 하신거에요
446. 29:17이제 남은 단계는 정말 쉽습니다
447. 29:20그 다음으로는 지금 보시면 이렇게 앞뒤로 큰따옴표가 있잖아요 그렇죠?
448. 29:25이것만 지워 주면 되겠죠
449. 29:28그래서 이제 마지막으로 여기 Replace 함수를 통해서
450. 29:33우리가 방금 받아왔던 검색 결과죠 이거를요
451. 29:37지워 줄 건데, 여기에서 큰따옴표를 검색해서 빈칸으로 지워 주는 거죠
452. 29:44이렇게 만들어 주면 여기 중간에 있는 큰 따옴표 두 개는
453. 29:47큰따옴표를 찾는 거구요
454. 29:49이거를 빈칸으로 지워 주는 거게 되겠죠 그렇죠?
455. 29:52이렇게 한 다음에 다시 명령문을 실행하면
456. 29:57이렇게 큰따옴표까지 지워진 검색 결과가 반환될 겁니다
457. 30:02자 그러면 끝났어요
458. 30:03이제 이 앞에 있는 debug.print 를 지워서
459. 30:08v의 i번째 값을 바꿔주면 되겠죠
460. 30:11이렇게 바꿔 주시면 우리가 맨 처음에 만들었던
461. 30:15배열에 있는 값들은 대괄호가 있고, 큰따옴표가 있고
462. 30:20뒤에 불필요한 값이 있는 상태였는데
463. 30:22이거를 바꾼 거에요.
464. 30:24쉼표를 찾아서 잘라 주고요
465. 30:27그리고 중간에 있는 큰따옴표를 불필요한 큰따옴표를 지워준거죠
466. 30:31그리고나서 배열의 값을 바꿔 주는 거에요. 그쵸?
467. 30:35그리고 나서 For 문 뒤에다가 Stop을 적어주신 다음에
468. 30:41그리고 나서 다시 명령문을 실행 해 볼게요
469. 30:45이렇게 v를 봤을 때 배열에 있는 값이 올바른 값으로 반환된 걸 볼 수 있죠
470. 30:51이제 우리가 여기에서 예를 들어 보자고요
471. 30:54검색 단어를 달고나에서 엑셀로 바꿔서 볼까요?
472. 31:00엑셀로 바꿔서 실행하시면
473. 31:02이렇게 엑셀에 연관된 검색어만 배열로 반환되겠죠
474. 31:07이제 여기서 이제 마지막 단계 인데
475. 31:10우리가 맨 처음에 명령문을 만들 때 이걸 Sub 으로 만들었어요
476. 31:14이 Sub의 경우에는 어떤 명령을 그 실행하는 보조 모듈이다.
477. 31:20명령문이라고 말씀을 드렸는데, 지금 우리가 실행할 거는요
478. 31:25이 시트에다가 사용하는거죠.
479. 31:28GetGoogleKeyword 라는 함수를 넣어서
480. 31:32여기에다 오징어를 검색을 했을 때
481. 31:34오징어로 반환되는 연관검색어가 쭈루룩 나오도록 함수를 만들 거예요
482. 31:40그죠 그렇기 때문에 여기에서, Sub을 지워주신 다음에
483. 31:46이거를 Function 으로 바꿔줄게요
484. 31:49Function 으로 바꿔서 함수로 바꿔 주는 거예요 그렇죠?
485. 31:54그리고 이걸 Function 으로 바꾸시면 이제 다시 시트로 와 볼까요?
486. 31:59시트로 오신 다음, 여기에다가 =GetGoogle을 넣으시면
487. 32:03이렇게 함수가 표시되는걸 볼 수 있죠?
488. 32:08우리가 함수를 만들면, 보통 이렇게 VLOOKUP 함수도 보시면
489. 32:13이렇게 인수가 들어가잖아요. 찾을값, 범위, 들어가는 것처럼
490. 32:17우리가 방금 만들었던 함수도 인수를 넣어줄건데
491. 32:21자, 인수를 만들어줄게요.
492. 32:23첫 번째는 Keyword 에요.
493. 32:28두 번째는 언어(lang)을 만들어 줄게요
494. 32:32첫 번째는 Keyword라는 인수구요
495. 32:35두 번째는 Lang 이라는 인수로 이렇게 넣어 주는 거에요
496. 32:39그리고 나서 Keyowrd 는 인수로 받아와서 어디에 넣을건가 하면,
497. 32:45여기 있는 키워드로 넣어 줄거구요
498. 32:50그리고 언어는 받아 와서 여기에다가 넣어 주면 되겠죠
499. 32:57엑셀은 지워줄게요 여기에서 키워드는 지워주신 다음에
500. 33:02큰따옴표(") 두 개 넣고, 다시 중간에다가 & 기호 두 개 넣구요
501. 33:08그리고 띄어쓰기로 하나씩 띄워 줄게요
502. 33:13똑같이 여기있는 언어에서도 지우신 다음에
503. 33:19큰따옴표 2개, 중간에 오셔서 & 기호 2개 넣어주시고
504. 33:25띄어쓰기를 하나씩 넣어줄게요.
505. 33:29이렇게 넣어주면, 문장이 나눠진 거고요
506. 33:32이 중간에다가 처음에는 Keyword 를 넣어 줄게요
507. 33:35Keyword 를 받아서 넣어 주는 거고
508. 33:39여기죠 hl 뒤에다가는 Lang 이라는 값을 받아서 넣어 주는 거죠
509. 33:45이렇게 넣어 주시면 우리가 함수를 작성할 때
510. 33:50Keyword 라는 인수랑 Lang 이라는 인수를 넣어주는데
511. 33:54그 인수를 각각 받아 와서 이렇게 넣어 주는 거에요.
512. 33:58마지막으로는 우리가 쓰는 GetGoogleKeyword 라는 함수를 반환해줘야겠죠
513. 34:04그래서 마지막에는, GetGoogleKeyword라는 함수의 값으로는 v를 반환하면 됩니다
514. 34:17왜냐면 우리가 아까 v 라는 배열을 만들었잖아요
515. 34:21이 배열을 만들어 준 거고, 이 배열을 받아와서 함수의 결과값으로 만들어 주는 거예요
516. 34:29이렇게 하시면 오늘 필요한 매크로 명령문 작성이 끝났습니다
517. 34:34뭔가 이게 길게 왔죠?
518. 34:36사실 보시면 명령문은 9줄이에요
519. 34:44굉장히 간단한 명령문이기 때문에
520. 34:47이거를 그 원리만 조금 이해하시면
521. 34:49사실 구글 말고도 여러 상황에 쓸 수 있겠죠
522. 34:52자 이렇게 하시면 끝났습니다 다 끝났고요
523. 34:56이제 마지막으로 시트에 와서 볼게요
524. 34:58자 시트에 오신 다음에 여기로 와 볼까요
525. 35:01J8셀로 와 볼게요
526. 35:04GetGoogleKeyword 로 검색을 할 거고
527. 35:06검색할 단어는 오징어를 검색해 볼게요
528. 35:09검색할 언어는 한국어로 검색을 하고
529. 35:13엔터 입력하시면 이렇게 반환되겠죠.
530. 35:18보면은 함수가 가로로 반환되죠?
531. 35:22그렇기 때문에 이거를 바꿔 줄게요
532. 35:24앞에다가 TRANSPOSE 함수를 써서, 결과를 세로 방향으로 바꿔 줄게요
533. 35:30이렇게 바꿔 준 다음에, 엑셀 2019 이전 버전 사용자 분들은요
534. 35:36TRANSPOSE 함수를 쓴 후, 함수를 작성합니다.
535. 35:48이렇게 입력을 해 볼께요.
536. 35:51그리고 엔터키로 입력하시면 이렇게 배열로 반환이 안 되고
537. 35:56하나만 보실 거예요
538. 35:59그쵸? 그러실때는 이걸 입력하시기 전에 범위를 넓게 선택할게요
539. 36:05넓게 선택한 상태에서 F2키를 누르시면 수식편집창이 나오거든요
540. 36:12이 상태에서 Ctrl + Shift + Enter로 배열함수를 입력하시면 이렇게 보일 거에요
541. 36:20자 또는 요 이제 만약에 이게 TRANSPOSE 함수를 쓰는 게 어렵다
542. 36:23나는 만약에 이걸 함수를 쓰지 않고
543. 36:27이거를 그냥 그대로 반환하고 싶다라고 하실 때에는요
544. 36:30여기 오셔서 이것도 가능하겠죠
545. 36:32이런 식으로 이제 v라는 값을
546. 36:36Application.WorkSheetFunction.Transpose
547. 36:45이렇게 입력하시면 이제 v 라는 배열을 반환하기 전에
548. 36:51명령문 자체에서 이거를 돌려서 반환하겠죠
549. 36:55근데 오늘 강의에서는 이거는 하지 않고
550. 36:58이런 식으로 v를 그대로 반환할게요
551. 37:05그리고 나서 이걸 바꿔 주면 되겠죠
552. 37:07오징어 라는 단어는 검색하는 셀, 예제파일에서는 C4셀이에요. 이렇게 넣어 주고
553. 37:16그리고 검색하는 언어는 H6셀이에요
554. 37:21이렇게 반환하도록 넣어볼게요
555. 37:23넣으신 다음에 엔터키로 바꿔주시면 되겠죠
556. 37:27이제 엑셀 2019 이전 버전 사용자 분들은요
557. 37:31바꾸실 때는 범위를 선택하신 다음에 미리 선택하신 다음에
558. 37:37F2키로 오셔서, 여기에서 바꾸셔야 되세요
559. 37:41이걸 이렇게 검색하는 단어랑 검색 언어까지 바꿔주시고
560. 37:48Ctrl + Shift + Enter로 바꾸시면 되겠죠
561. 37:51자 이렇게 하면 오류가 나 오실 건데 괜찮아요
562. 37:54오류가 나와도 괜찮습니다. 그 다음 단계로 넘어가 볼게요
563. 37:57그 다음으로는 여기에다가 오징어를 검색을 하고
564. 38:02여기에다가 ko를 넣어 주시거나 하면은
565. 38:04이렇게 검색 결과 바뀌겠죠
566. 38:08러시아 언어, 프랑스어, 중국어로 바꿔볼까요?
567. 38:18마지막으로는 여기에다가 이렇게 VLOOKUP 함수를 써서
568. 38:22여기 입력된 셀을 찾는 거죠
569. 38:25이거는 여기 표에서 두 번째는 값을 불러오고
570. 38:31여기다가는 목록 상자를 만들어 주면 되겠죠.
571. 38:35데이터 유효성 검사에서 목록 상자로
572. 38:38여기 입력된 범위의 값을 받을 수 있도록 만드시면
573. 38:43이렇게 값을 선택할때마다 바뀌겠죠.
574. 38:49마지막으로는 이전과 동일하죠
575. 38:51이전 시간과 동일하게, 여기에다 예를 들어서 엑셀을 검색을 했을 때
576. 38:55오빠두엑셀을 검색을 하면 검색 결과 보이시죠?
577. 38:59이렇게 Search 뒤에 q로 검색어가 있는 거죠
578. 39:03이 URL주소를 복사한 다음에, 이걸 복사해서 여기다 붙여 넣기 하는 거죠
579. 39:10이렇게 q 가 붙은 등호 뒤에 까지만 잘라주시구요
580. 39:18이렇게 한 다음에 이 검색 url 을 만들어 주는 겁니다 이런식으로요
581. 39:23그리고나서 이거를 복사한 다음에 검색창에 붙여넣기하면
582. 39:29이렇게 오징어 게임이 검색이 되는 거고
583. 39:31여기 보면은 이렇게 검색하는되죠.
584. 39:35마지막으로 여기 왼쪽에다가 예전과 동일하게 HYPERLINK 함수로
585. 39:41해당 링크 주소랑 겉으로 보일 값을 넣어주시고요
586. 39:47자동채우기를 쭉 하시면 되겠죠
587. 39:49그리고 글씨 색상 바꿔 주면 되겠죠
588. 39:51글씨 색상을 흰색으로 바꿔 주고 밑줄 제거해 주고요
589. 39:57바꿔 주면 검색 결과가 나오겠죠. 그렇죠?
590. 40:02이제 엑셀 검색하면 엑셀 나오고요
591. 40:04파워포인트 하면 파워포인트 나오고요
592. 40:06언어를 바꾸면, 한국어 검색 결과, 영어 또는 러시아어
593. 40:12이렇게 바뀌는걸 볼수있죠
594. 40:13파워포인트로 검색해 볼까요?
595. 40:15파워포인트 이렇게 러시아어 또는 한국어, 영어 이런 식으로
596. 40:23언어에 따라서 연관 검색어가 다르게 나오는걸 볼 수 있죠
597. 40:27그리고 한가지 주의사항은요
598. 40:29이제 오늘 강의는 예제파일을 공유해 드릴때,
599. 40:33파일 형식을 보시면 매크로가 포함 된 파일이에요 그렇죠?
600. 40:37이거는 이제 "xlsm" 파일 이거든요. 매크로가 포함 된 파일인데
601. 40:42근데 혹시라도 이제 실무에서 현업에서 실행하실 때에는
602. 40:46이게 파일형식이 기본 파일은 "xlsx" 겠죠
603. 40:50매크로 없는 파일이기 때문에
604. 40:52이렇게 매크로를 저장 하셨을 때에는 반드시 저장하실 때
605. 40:56저장하기 단계 오시면, 파일형식 중에 매크로 사용 통합문서가 있어요
606. 41:02이렇게 매크로 통합문서로 저장하셔야 하지만
607. 41:05매크로가 포함 된 상태로 파일을 쓸 수 있으니까
608. 41:09이 점만 주의하시면 좋겠습니다