[모든 버전 가능] 엑셀 실시간 검색 보고서 만들기, 함수 3개로 가능합니다! | 완성 서식 제공 — Transcript

Full transcript

1. 0:00세로로 바꿔주시고요
2. 0:01[확인] 버튼 클릭할게요
3. 0:03쭉 클릭하시면 배경이 예쁘게 들어가죠
4. 0:05여기에서 삼성의 i7,
5. 0:07또는 HP의 1TB
6. 0:10이런 식으로 실시간 검색도 하고
7. 0:11이런 식으로 실시간 검색도 하고
8. 0:12정렬까지 할 수 있는 보고서를 완성했습니다
9. 0:16안녕하세요, 여러분. 오빠두입니다
10. 0:18오늘은 엑셀 모든 버전에서
11. 0:20기본으로 제공하는 함수만 사용해서
12. 0:22이런 식으로 단어를 검색했을 때
13. 0:25실시간으로 제품을 검색할 수 있는
14. 0:27검색보고서를 만드는 방법을 준비했습니다
15. 0:30엑셀 초보자도
16. 0:31누구나 편리하게 공식을 복사/붙여넣기해서
17. 0:34사용할 수 있도록 예제파일과 완성파일을
18. 0:37꼼꼼하게 정리했는데요
19. 0:39영상 고정댓글을 참고하셔서
20. 0:41여러분의 멋진 보고서를 만드시는 데
21. 0:43작은 도움이 되었으면 좋겠습니다
22. 0:45그럼 오늘 강의 시작하겠습니다
23. 0:48오늘은 라이브 방송을 꾸준히 시청해오셨다면
24. 0:53이렇게... 쉬우면서
25. 0:55그러면서도 실무에서 바로 적용할 수 있는
26. 0:59그런 보고서 스킬을 준비했습니다
27. 1:02완성 파일을 잠시 볼 건데요
28. 1:04만약 여러분들께 한 가지 미션이 주어졌어요
29. 1:08이렇게 내가 제조사별로 제품을 관리하고 있습니다
30. 1:12영업사원이에요
31. 1:13그런데 내가 이 목록에서 제조사가 LG고
32. 1:17그리고 제품 이름에 그램이 들어가고
33. 1:20그리고 CPU가 i7인 제품만 보고 싶다
34. 1:23라고 상황을 가정해 보겠습니다
35. 1:26그러면 보통 어떻게 하시죠?
36. 1:28아마 대부분의 경우에는 이렇게 하실 거예요
37. 1:30제조사에서 LG를 필터링하고
38. 1:34그중에서 그램인 것만 필터링하고
39. 1:37그중에서 또 i7인 것만 필터링을 하고
40. 1:41이런 식으로 보통 작업을 하실 겁니다
41. 1:43또는 우리가 예전에 많이 배웠던 기능이죠
42. 1:46피벗 테이블과 슬라이서 기능을 사용하면
43. 1:50버튼을 클릭해서 필터링하는 것도 가능해요
44. 1:52지금 여기 있는 범위는 표입니다
45. 1:53물론 지금 여기 있는 범위는 표입니다
46. 1:55그렇죠?
47. 1:56표로 만들었기 때문에 이 표에서는 기본적으로
48. 1:59엑셀 2013 이후 버전이라면 슬라이서를
49. 2:03기본적으로 사용할 수 있죠
50. 2:05그래서 이 표를 클릭하고 테이블 디자인에서
51. 2:08슬라이서를 내가 원하는 만큼 추가한 다음에
52. 2:12여기에서 LG를 보고 여기에서 LG를 클릭하고
53. 2:14그램만 제외하고
54. 2:16이런 식으로 버튼을 클릭하는 것도 가능은 합니다
55. 2:19하지만 이 슬라이서에는 약간 부족한 부분이 있어요
56. 2:22바로 이겁니다
57. 2:23이렇게 CPU나 또는 제조사같이요
58. 2:26딱 그룹핑이 돼 있어서
59. 2:28버튼을 클릭하기 좋은 형태면
60. 2:30굉장히 쓰기 좋은 기능인 건 맞아요
61. 2:33그런데 문제는 이렇게 제품 이름처럼 값이 엄청 많거나
62. 2:38또는 정제되지 않은 데이터일 때는요
63. 2:40이 슬라이서 기능만으로는 사실 데이터를
64. 2:43매번 클릭해서 필터링한다는 게 현실적으로 불가능합니다
65. 2:48그래서 오늘 우리가 무엇을 알아볼 거냐 하면 바로 이겁니다
66. 2:53LG를 보고 싶어요
67. 2:54LG, 그래서 i7 1TB
68. 2:58또는 HP 보고 싶어요
69. 3:00HP에서 i7 16GB 이런 식으로 필터링을 바로 걸 수 있고요
70. 3:05또는 이런 식으로 데이터를 선택해서
71. 3:07정렬하는 것도 가능합니다
72. 3:09이런 식으로 실시간으로 필터링하는
73. 3:11보고서를 만들어볼 거예요
74. 3:13그러면 한번 예제 파일을 실행해 볼게요
75. 3:16예제 파일을 실행하시면
76. 3:19첫 번째를 보니까 이렇게 제품이 있어요
77. 3:22HP, 삼성이 있고요
78. 3:24여기에서 삼성과 같은지 보고 싶어요
79. 3:27그럼 어떻게 하죠?
80. 3:28첫 번째로 등호 입력할게요
81. 3:30등호 입력하신 다음에
82. 3:31여기 왼쪽에 있는 HP가 여기 있는 삼성과 같은지 볼 겁니다
83. 3:38그러고 나서 우리는 이제 이 수식을
84. 3:40자동 채우기 할 거기 때문에
85. 3:42여기 위에 있는 C2 셀은 딱 고정되어야겠죠
86. 3:45그래서 절대 참조로 바꿔주면 됩니다
87. 3:47선택하신 다음에 F4 키 눌러서 $ 기호 추가할게요
88. 3:51그리고 엔터 키로 입력하신 다음에
89. 3:53수식을 자동 채우기 하면
90. 3:55이렇게 삼성과 같은지 TRUE, FALSE
91. 3:58참, 거짓으로 비교할 수 있습니다
92. 4:00이거 굉장히 간단하죠?
93. 4:01이제 그다음 보겠습니다
94. 4:02그다음이 오늘 강의의 핵심인데요
95. 4:04지금 우리가 방금 했던 이 작업은
96. 4:08여기 있는 단어와 정확히 일치하는가를 비교하는 거예요
97. 4:13그래서 여기 예를 들어 삼성 띄어쓰기가 있거나
98. 4:17또는 삼성전자 이렇게 있으면
99. 4:20이 값이 같다고 비교가 안 된다는 거죠
100. 4:24그래서 오늘 강의 핵심은 딱 두 개인데요
101. 4:26그중에 첫 번째는 여기 있는 단어가
102. 4:29이 문장에서 포함되어 있는지 볼 거예요
103. 4:33두 번째로, 그럼 단어가 포함돼 있어요
104. 4:35그러면 단어가 있는 항목들만 필터링해서 출력할 거예요
105. 4:41이 두 가지만 알고 있으면
106. 4:43이 완성 파일에 있는 이 보고서를 만들 수 있는데요
107. 4:46그중에 첫 번째죠
108. 4:47단어가 포함되어 있는지 검색하는 함수를 한번 알아볼게요
109. 4:51이렇게 외우시면 좋아요
110. 4:52단어 포함 여부 검색하는 공식은요
111. 4:55단어가 있어? 없어? 검색하는 거죠
112. 4:58단어가 있어? 없어? 검색하는 거예요
113. 5:01그래서 ‘있어?! 공식’이다
114. 5:03ISNUMBER(있) SEARCH(어)니까 ‘있어?! 공식’
115. 5:07억지 같지만 이렇게 외우면 편하다는 겁니다
116. 5:11‘있어?! 공식’이다
117. 5:12ISNUMBER SEARCH다
118. 5:13좀 더 편하게 외울 수 있지 않을까 싶습니다
119. 5:15그래서 ISNUMBER SEARCH를 써볼 건데요
120. 5:18첫 번째, SEARCH입니다
121. 5:20SEARCH 함수는요, 검색하는 거죠
122. 5:22이번에 볼 건 여기 있는 HP라는 단어를 검색할 거예요
123. 5:26첫 번째는 검색할 단어예요
124. 5:28그리고 쉼표 입력하신 다음에 두 번째 인수는요
125. 5:30이 단어를 어디에서 검색할지, 찾을지입니다
126. 5:34그래서 검색할 문장을 입력하면 돼요
127. 5:36이렇게 입력하시면 이 SEARCH 함수는요
128. 5:39단어가 검색한 문장 안에 있어요
129. 5:44그러면 이 단어의 순번을 출력합니다
130. 5:48이 문장에서 이 단어가 몇 번째에서 시작하는지
131. 5:53그 순번을 반환해요
132. 5:54그래서 이렇게 SEARCH 함수를 입력하면 몇 번째죠?
133. 5:57이 문장에서 HP는 네 번째에 있어요
134. 6:01네 번째부터 시작을 하죠
135. 6:03그래서 순번으로 4가 반환되는 거예요
136. 6:05만약에 이 값을 애플로 바꿔볼까요?
137. 6:09그러면 애플은 이 문장에서 없죠?
138. 6:11그럴 때에는 밸류 오류가 반환됩니다
139. 6:14그래서 SEARCH 함수를 써서 있으면 뭐가 나온다?
140. 6:18숫자가 나오고 없으면 밸류 오류가 나온다는 거죠
141. 6:23그럼 다 끝났어요
142. 6:24이제 이 SEARCH 함수를 묶어주는 거예요
143. 6:27ISNUMBER로 묶어준다
144. 6:31그러면 공식 다 끝났습니다
145. 6:33ISNUMBER는 뭐예요?
146. 6:34숫자인가? 물어보는 거죠
147. 6:36SEARCH 함수를 써서 이 단어가 있으면 숫자가 나오고
148. 6:40없으면 오류가 나온다면서요
149. 6:42그러면 ISNUMBER 함수를 쓰면 숫자일 때
150. 6:45TRUE를 반환하니까 결국은 뭐예요?
151. 6:47단어가 있으면 TRUE, 없으면 FALSE가 나오겠죠
152. 6:54이렇게 동작하는 거예요
153. 6:55그러고 나서 이제 검색하는 단어죠
154. 6:58C7 셀은 F4 키로 절대 참조로 바꿔줘야겠죠
155. 7:02수식을 자동 채우기 할 거니까요
156. 7:04이렇게 하신 다음에 엔터 키로 입력하시고요
157. 7:07자동 채우기를 쭉 하시면 단어가 있으면 TRUE
158. 7:11없으면 FALSE 나오는 겁니다
159. 7:13두 번째는 그러면 단어가 포함되었을 때
160. 7:18그 데이터만 필터링할 거니까 FILTER 함수를 알아볼 겁니다
161. 7:22버전에 따라 조금씩 다른데요
162. 7:24이 FILTER 함수는 2021 이후 버전에서만 제공됩니다
163. 7:28그래서 2019 이전 버전 사용자분들은
164. 7:32잠시 뒤에 이 공식을 써서 어떻게 쓰는지 같이 알아볼게요
165. 7:36FILTER 함수 보겠습니다
166. 7:38오른쪽에다가 데이터를 필터링할 거예요
167. 7:41필터링할 조건은 CPU가 i7일 때를 필터링해 보겠습니다
168. 7:48그래서 제품명 오른쪽에다가 FILTER 함수를 쓸게요
169. 7:51FILTER 함수로 필터를 적용할 범위
170. 7:54여기죠, 쉼표
171. 7:56두 번째는 조건이에요
172. 7:57조건은 뭐예요?
173. 7:58여기 있는 CPU 범위의 값이
174. 8:01i7인지, 같은지를 볼 거죠
175. 8:04그래서 이 CPU 범위의 값이 같아요
176. 8:08무엇과?
177. 8:09여기에 있는 i7과 같다
178. 8:12이렇게 하면 뭐예요?
179. 8:13이 파란색 범위를 필터링하는데
180. 8:15그 조건은 CPU가 i7인 거를 필터링할 거다
181. 8:19이렇게 입력하시고 엔터 입력하면
182. 8:21i7인 항목만 쭉 필터링되죠
183. 8:25이제 값을 바꿔볼게요
184. 8:26i7을 i5 또는 M2 바꾸시면
185. 8:31그 항목들만 실시간으로 필터링되는 거 볼 수 있습니다
186. 8:36그러면 다 끝났어요
187. 8:37이 두 개를 짬뽕시켜서 단어를 포함할 때
188. 8:41그것만 필터링하도록 공식 만들어주면 되겠죠
189. 8:45우리 방금 두 개 다 알아봤기 때문에
190. 8:47이 두 개를 합쳐서 한번 공식을 만들어보겠습니다
191. 8:50이번에는 제품명 값이 이 단어를 포함하는 값들만
192. 8:55필터링하도록 공식을 만들어볼게요
193. 8:58오른쪽 제품명에 오신 다음에 등호 입력하시고요
194. 9:00FILTER 함수를 만들어줄게요
195. 9:02FILTER 함수로
196. 9:03필터링할 범위는 이 전체 범위죠
197. 9:06전체 범위를 볼 거고 조건은 뭐예요?
198. 9:08조건은 단어를 포함하는지를 볼 거예요
199. 9:11있어 공식이에요
200. 9:12그래서 ISNUMBER SEARCH,
201. 9:13ISNUMBER 괄호 열고
202. 9:15그 안에 SEARCH 함수로 검색할 단어 선택하고요, 쉼표
203. 9:20두 번째는 이 제품명 전체 범위를 넣어주는 거예요
204. 9:23이 범위에서 HP를 포함하는지를 볼 겁니다
205. 9:27그러고 나서 괄호 두 번 닫아서 마무리하시고요
206. 9:30그럼 조건 끝났어요
207. 9:31그리고 마지막 세 번째 인수
208. 9:33세 번째는 이게 FILTER 함수의 결괏값이 없을 때
209. 9:36어떻게 출력할지 입력하는 거거든요
210. 9:39그래서 이번 강의에서는 결과 없음이라고 넣어줄게요
211. 9:43그리고 엔터 키 입력해 보세요
212. 9:45그러면 HP를 포함하는 제품이 필터링되죠
213. 9:50그다음 볼까요?
214. 9:51HP가 아니라 맥북 또는 LG, 에이서, 14
215. 9:56이런 식으로 입력하시면 단어 포함하는 값만
216. 9:59실시간으로 필터링되는 걸 볼 수 있습니다
217. 10:01정말 간단하죠?
218. 10:02하지만 한 가지 주의해야 할 내용이 있죠
219. 10:06이 FILTER 함수를 사용한 파일을
220. 10:08다른 사람과, 여러 명과 공유할 때
221. 10:10그런데 만약에 그 파일을 받은 사람이 2019를 쓰고 있어요
222. 10:14또는 2016을 쓰고 있어요
223. 10:16그러면 FILTER 함수를 제공하지 않기 때문에
224. 10:19이 함수가 동작하지 않겠죠
225. 10:21그래서 혹시라도 이 파일을 여러 사람과 공유해야 돼요
226. 10:24그럴 때는 2019 이전 버전 공식을 사용하시면 되겠습니다
227. 10:28그래서 2019 이전 버전 시트로 오시면
228. 10:29그래서 2019 이전 버전 시트로 오시면
229. 10:31여기에다가 공식을 적어드렸거든요
230. 10:34어렵지 않아요
231. 10:35이 공식을 나중에 회사에 가셔서
232. 10:37이 파일만 어디 잘 보관해 두셨다가
233. 10:40나중에 필요하실 때 복사, 붙여넣기 해서 사용하시면 되세요
234. 10:44그래서 이 공식을요, 쭉 복사할게요
235. 10:47복사하는데 이 앞에 있죠?
236. 10:48앞의 작은 따옴표는 제외하고, 이거는 빼고요
237. 10:51등호부터 끝까지 복사할게요
238. 10:55복사하신 다음에 다시 여기 단어 포함 여부
239. 10:59여기 검색이 오신 다음에 이게 첫 번째 셀이에요
240. 11:02필터를 출력할 첫 번째 셀에 붙여넣기 할게요
241. 11:05그럼 끝났어요
242. 11:06이제 여기에서 이거 하나씩 바꿔주면 되거든요
243. 11:09첫 번째 출력 범위 드래그해서 지우고요
244. 11:13출력할 범위는 어디예요?
245. 11:14제품명부터 가격까지죠
246. 11:17쭉 드래그해서 선택할게요
247. 11:19그러고 나서 F4 키 눌러서 절대 참조로 딱 고정시켜줄게요
248. 11:25그다음 찾을 값이에요
249. 11:27찾을 값은 지우고요
250. 11:29이거죠?
251. 11:30여기 F22 셀 선택하시고 F4 키
252. 11:34똑같이 절대 참조로 바꿔주시고요
253. 11:36그다음으로 이 값을 찾을 범위는 어디 있죠?
254. 11:39찾을 범위는 여기죠
255. 11:41제품명 범위죠
256. 11:42이렇게 드래그하시고 똑같이 F4 키 바꿔줄게요
257. 11:46나머지도 똑같아요
258. 11:47찾을 범위 드래그에서 F4 키로 바꿔주시고요
259. 11:51똑같이 찾을 범위 똑같이 드래그하고 F4 키로 바꿔주시면
260. 11:56공식 완성되었습니다
261. 11:59그 상태에서 이제 입력하시는데
262. 12:02이 수식은 배열 함수이기 때문에
263. 12:04엔터 키로 입력하시면 안 돼요
264. 12:05엑셀 2021 이후 버전 사용자분들은
265. 12:09엔터 키로 입력해도 괜찮아요
266. 12:11그런데 2019 이전 버전 사용자분들은 반드시
267. 12:14Ctrl+Shift+Enter 키로 입력하시면 되겠습니다
268. 12:19Ctrl+Shift+Enter 키로 입력하시고요
269. 12:21수식을 오른쪽, 아래쪽으로 자동 채우기 쭉 해보세요
270. 12:25그러면 필터링 끝났어요
271. 12:28이제 이 단어를 바꿔보세요
272. 12:29HP 또는 LG, 에이서 바꾸시면
273. 12:34그 단어를 포함하는 값들만 실시간으로
274. 12:37필터링하는 공식이 완성되었습니다
275. 12:40날짜 검색도 가능합니다
276. 12:42날짜를 검색하실 때에는요
277. 12:44이 조건을 바꿔주시면 되세요
278. 12:46날짜를 검색하시려면 IF 함수에서
279. 12:49보시면 이 공식이 지금 당장은 눈에 안 들어오실 거예요
280. 12:53그런데 이 공식을 자세히 보세요
281. 12:56눈에 들어오는 거 보이실 거예요
282. 12:57뭐죠?
283. 12:58맞아요, 여기 있죠
284. 13:00ISNUMBER SEARCH 공식 딱 보이시죠?
285. 13:03이거예요
286. 13:03그럼 만약에 내가 단어 포함을 보는 게 아니라
287. 13:06날짜를 보고 싶어요
288. 13:08그럼 어떻게 하면 돼요?
289. 13:09이 조건만 단어 포함 공식에서
290. 13:12‘있어?! 공식’에서 다른 걸로 바꿔주면 되겠죠
291. 13:16이렇게 사용하시면 되겠습니다
292. 13:17그다음으로 제품 목록 시트로 오셔서
293. 13:19한번 데이터를 만들어보겠습니다
294. 13:22이 제품 목록 시트로 오시면 현재 데이터는요
295. 13:26일반 범위로 만들어져 있습니다
296. 13:29이 범위를 표로 바꿔줄 거예요
297. 13:31그래서 이 데이터에서 아무 셀을 클릭하신 다음에
298. 13:34단축키 Ctrl+A 키 눌러서 전체 범위를 쭉 선택하시고요
299. 13:39그 상태에서 [삽입] 클릭할게요
300. 13:41[삽입] 클릭하시고요
301. 13:43밑에 있는 [표]를 클릭해서 이 범위를 표로 바꿔줄게요
302. 13:47그리고 밑에 보시면 [머리글 포함]
303. 13:48지금 선택한 범위에서 첫 번째 행은 머리글이죠
304. 13:52머리글이 있기 때문에 [머리글 포함] 체크하시고요
305. 13:55[확인] 버튼 클릭해서 이 범위를 표로 바꿔주겠습니다
306. 14:00범위를 표로 바꾸면 여러 가지 장점이 있습니다
307. 14:04바로 수식을 작성할 때 마우스로 드래그하지 않아도 된다
308. 14:11이거 중요합니다
309. 14:12특히 복잡한 공식 써야 될 때
310. 14:15매번 이거 하나하나 드래그해서 입력하기 어려웠잖아요
311. 14:19매번 드래그해서 절대 참조로 바꿔주고, 어렵잖아요
312. 14:23그런데 이 범위를 표로 바꾸는 순간 뭐다?
313. 14:26마우스로 드래그하는 게 아니라
314. 14:28키보드로 입력할 수 있다는 거죠
315. 14:30한번 해볼게요
316. 14:32이 표를 클릭하신 다음에, 표 클릭하시고요
317. 14:35위의 [테이블 디자인] 탭으로 이동할게요
318. 14:37[테이블 디자인] 탭으로 오신 다음에 왼쪽에 볼게요
319. 14:41왼쪽 보시면 [표 이름] 있죠?
320. 14:43이름을 바꿔줄게요
321. 14:44제품 목록으로 바꿔주겠습니다
322. 14:47우리가 방금 만들었던 이 표는요, 이름이 딱 정해진 거예요
323. 14:51이 표의 이름은 제품 목록이다
324. 14:54이름이 만들어진 거예요
325. 14:55그래서 sum 함수를 써서 이 가격의 합계를 구하고 싶어요
326. 15:01물론 가격의 합계를 구할 때 보통 실무에서는
327. 15:03머리글을 그냥 간단하게 클릭해서 구할 수 있겠죠
328. 15:07그런데 만약에 이거를 쭉 드래그한다고 가정했을 때
329. 15:11이거 마우스 매번 왔다 갔다 하기 어렵거든요
330. 15:13그런데 우리가 방금 만들었던 이 범위는
331. 15:16표이기 때문에 우리가 방금 만들었던 표의 이름 있죠?
332. 15:19이름을 입력하시면 돼요
333. 15:21제품 목록이에요
334. 15:24제품 목록, 표에서 대괄호 여시면 다 나오죠
335. 15:29표에서 어디 볼 거예요?
336. 15:30가격 볼 거죠
337. 15:32선택하시고 tab 키 입력하신 다음에
338. 15:35괄호를 딱 닫으시면 어떻게 된다?
339. 15:37이 가격 범위가 자동으로 선택된다는 거죠
340. 15:42굳이 마우스로 손을 가져가지 않아도 괜찮아요
341. 15:46마우스로 손을 가져가지 않아도
342. 15:48키보드로 수식을 입력할 수 있다는 거죠
343. 15:50그리고 엔터 키를 입력하시면
344. 15:52가격의 합계를 바로 구할 수 있습니다
345. 15:55그다음으로는 오른쪽에다가 새로운 행을 추가할 건데요
346. 15:59우리가 방금 아까 했던 단어 포함 검색 공식은요
347. 16:04제품명에서만 검색했어요
348. 16:07이렇게요
349. 16:08그런데 우리가 이번에 만들 완성 파일은요
350. 16:11제품명뿐만 아니라 제조사, 영문 모델명, CPU, 램까지
351. 16:16삼성의 8기가바이트 이렇게 다 볼 거란 말이에요
352. 16:20그래서 이번에 우리가 할 거는요
353. 16:21검색할 조건으로 오른쪽에다가 이거를 다 합친
354. 16:26하나의 검색 조건 열을 만들어줄 거예요
355. 16:29그래서 오른쪽에다가 이거를 합치는 거예요
356. 16:33제조사 & 제품명, 한글, 영문, CPU, RAM, HDD까지만
357. 16:39이렇게 해볼게요
358. 16:40이렇게 하시고 엔터 입력하면
359. 16:42자동으로 데이터가 쭉 완성되죠
360. 16:44표로 만드시게 되면 여기에 수식을 하나만 작성하셔도
361. 16:47나머지가 자동으로 완성됩니다
362. 16:50그리고 머리글은 검색 옵션으로 바꿔줄게요
363. 16:54이렇게 하시면 우리가 단어 포함 검색 공식 알아봤잖아요
364. 16:59제품명이든, 제조사든, CPU든, 램이든 다 검색할 수 있겠죠
365. 17:04방금 우리가 했던 것은 이걸 하나씩 & 기호로 연결했는데
366. 17:082021 이후 버전부터는 이것도 가능하세요
367. 17:10이거를 다 지우고요
368. 17:11CONCAT 함수예요
369. 17:12CONCAT 함수로, CONCAT으로 드래그를 쭉 하시면
370. 17:16이런 식으로 이 범위 내에 있는 값들을
371. 17:19한 번에 합치는 것도 가능합니다
372. 17:22마지막이에요
373. 17:22검색 보고서로 이동하겠습니다
374. 17:25검색 보고서 시트에 오시면 이렇게 머리글이 있거든요
375. 17:28여기 회색으로 제가 채워놨는데요
376. 17:30그런데 2019 이전 버전 시트를 보시면
377. 17:34이렇게 네이버 쇼핑과 같이
378. 17:37굉장히 예쁘게 그러데이션이 들어가 있습니다
379. 17:41그래서 이 검색 보고서에도요
380. 17:43여기처럼 예쁘게 그러데이션을 넣는 방법을 소개해 드릴게요
381. 17:48어떻게 하냐면 지금 여기 셀 병합돼 있죠?
382. 17:50이 병합된 셀을 클릭하신 다음에
383. 17:53여기 색상을 채워주는 거예요
384. 17:55[채우기]를 바꿔주는데 [홈] 탭 클릭해볼게요
385. 17:58[홈] 탭 클릭하시면 [채우기] 있죠?
386. 18:01이 페인트 모양을 클릭하시면 안 됩니다
387. 18:04그러데이션은 반드시 셀을 우클릭한 다음에
388. 18:07우클릭하시고요
389. 18:09이 상태에서 셀 서식을 클릭해서 셀 서식에 오신 다음에
390. 18:14여기에 있는 [채우기]로 오셔서 바꿔주시면 되겠습니다
391. 18:18밑에 [채우기 효과] 보이실 거예요
392. 18:21[채우기 효과] 클릭하신 다음에
393. 18:24여기에서 그러데이션을 바꿔주는 거예요
394. 18:27첫 번째 색은, 색깔 중에 초록색 보이시죠?
395. 18:30네이버 초록색 하나 클릭하시고요
396. 18:33두 번째 색은 보라색입니다
397. 18:35여기 옅은 보라색 보이시죠?
398. 18:37보라색, 자주색 클릭할게요
399. 18:41그다음으로 음영 스타일은 세로로 바꿔줄게요
400. 18:44세로로 바꿔주시고요
401. 18:45여기에서 초록색부터 보라색까지
402. 18:48그러데이션 되는 보기로 선택하신 다음에
403. 18:50[확인] 버튼 클릭할게요
404. 18:52쭉 클릭하시면 배경이 예쁘게 들어가죠
405. 18:55이 색상 코드는요, 오른쪽에 적어드렸습니다
406. 18:57혹시라도 색상이 궁금하신 분은
407. 18:59여기 RGB 값이랑 HEX 값 적어드렸으니까요
408. 19:02이거 참고하시면 좋을 것 같아요
409. 19:04그다음으로 도형만 이렇게 옮겨주시면
410. 19:06이렇게 예쁘게 만들어지겠죠
411. 19:09이제 위에다가 이번에 검색할 것은
412. 19:12한번 삼성을 검색해 볼게요
413. 19:14그다음으로 아까 썼던 공식이에요
414. 19:16등호 입력하신 다음에 FILTER 공식으로 필터링을 할 거예요
415. 19:20필터링할 범위는 어디예요?
416. 19:22원래 같으면 이거를 제품 목록 시트로 와서 드래그했겠지만
417. 19:27방금 우리가 어떻게 했어요?
418. 19:28이 범위를 표로 바꿔줬기 때문에 마우스를 쓸 필요가 없다
419. 19:33마우스를 쓰지 않고 키보드로
420. 19:36간단하게 입력할 수 있다는 거죠
421. 19:38제품 목록 범위죠
422. 19:40제품 목록을 필터링할 거고 그 조건은 뭐예요?
423. 19:44단어 포함 조건 공식
424. 19:46있어 공식이에요, 있어 공식
425. 19:48ISNUMBER 괄호 열고 SEARCH
426. 19:50있어 공식으로 검색할 단어 뭐예요?
427. 19:53이거죠? B2셀 검색할 거고
428. 19:57단어를 볼 범위는 제품 목록 표에서 대괄호 여시고요
429. 20:02그중에서 검색 옵션 볼 거죠
430. 20:04이 검색 옵션 범위에서 이 단어를 포함할 경우 볼 겁니다
431. 20:11그러고 나서 괄호 두 번 닫아서 마무리하시고요
432. 20:13그다음 마지막 세 번째 인수는 결과 없음
433. 20:16아까 했던 거랑 동일한 공식입니다
434. 20:19그러고 나서 엔터 키로 입력하시면 끝났죠
435. 20:23만약에 난 여기 마지막에 있는 검색 옵션 이게
436. 20:28같이 나와서 보기 안 좋아요
437. 20:30그럴 때에는 바꿔주면 되겠죠
438. 20:32이거는 직접 타이핑하기가 어려우니까 선택을 하죠
439. 20:36이렇게 여기 제품 제조사부터 가격까지만 선택하시면
440. 20:41제품 목록 표에서 제조사라는 머리글부터
441. 20:45가격까지만 불러올 겁니다
442. 20:47라고 선택이 되죠
443. 20:48이렇게 입력하시고 엔터 키 하시면
444. 20:52마지막은 빼고 이렇게 검색이 되겠죠
445. 20:54이제 바꿔보세요
446. 20:55HP 또는 i7 또는 asus 이렇게 검색하는 거죠
447. 21:01만약에 SPILL 오류가 나오시는 분은요
448. 21:04아마 여기 밑에 다른 값이 있을 거예요
449. 21:06다른 값이 있는 거기 때문에 밑에 지워주시면 되겠습니다
450. 21:10그다음으로 만약에 나는 아수스에서 i7을 보고 싶어요
451. 21:14그럴 때에는 아수스 입력하고요
452. 21:17*i7 이렇게 하면 아수스의 i7도 볼 수 있고요
453. 21:23또는 아수스 i7에 1TB 이렇게도 검색할 수 있겠죠
454. 21:28지우시면 다 검색되고요
455. 21:31이 별표 기호를 사용하시면 모든 문자를 대체하기 때문에
456. 21:35삼성의 무엇, HP의 무엇 이런 식으로 검색도 가능하세요
457. 21:39굉장히 편리하죠
458. 21:41이제 완성 파일을 보시면 여기에다가
459. 21:43단어를 검색할 때마다 HP, i7
460. 21:48또는 삼성 i5 검색할 때마다
461. 21:53그러니까 타이핑을 할 때마다 실시간으로 검색이 되거든요
462. 21:56지금 이 화면을 보시면 삼성의 i7
463. 22:00이렇게 실시간으로 검색이 돼요
464. 22:02그런데 우리가 방금 만들었던 FILTER 함수는요
465. 22:05여기에다가 값을, 삼성의 i7 입력했을 때만 값이 바뀌죠
466. 22:13그래서 오늘 1교시 마지막은요
467. 22:14여기에다가 어떤 단어를 검색했을 때
468. 22:17실시간으로 필터 결과가 나오도록 하는 방법 알아볼게요
469. 22:22방법은 간단합니다
470. 22:23어떻게 하냐면 개발 도구를 추가하는데요
471. 22:26만약에 위쪽에 [개발 도구]가 안 보이시는 분은요
472. 22:29리본 메뉴 우클릭해 볼게요
473. 22:31여기에서 리본 메뉴 아무 곳이나 우클릭하시면
474. 22:34중간에 [리본 메뉴 사용자 지정] 보실 거예요
475. 22:37[리본 메뉴 사용자 지정]에 오신 다음에
476. 22:39여기에서 [개발 도구] 보이실 겁니다
477. 22:41[개발 도구] 체크하시고요
478. 22:43[확인] 버튼 클릭하시면 여기에 [개발 도구]가 표시가 돼요
479. 22:48[개발 도구] 오신 다음에
480. 22:49여기 중간에 보시면 [삽입]이 있습니다
481. 22:52[삽입]에서 밑에 보시면 [Active X 컨트롤]이 있고요
482. 22:56[ActiveX 컨트롤] 밑에 보시면
483. 22:58ㄱ, ㄴ이 있는 텍스트 상자가 보이실 거예요
484. 23:02이거를 선택하시고요
485. 23:04중간에다가 쭉 드래그하는 거예요
486. 23:07쭉 드래그해서 추가할게요
487. 23:09그러면 상자가 추가되죠
488. 23:11그 상태에서 상자 우클릭할게요
489. 23:14우클릭하신 다음에 [속성] 이동할게요
490. 23:17[속성]에 오시면 뭔가 굉장히 복잡하게 쭉 나오는데요
491. 23:21어렵지 않아요
492. 23:22여기에서 딱 두 개만 바꾸시면 돼요
493. 23:24첫 번째는 이 밑에 보시면 여기예요
494. 23:28SpecialEffect거든요
495. 23:30SpecialEffect를 보시면 지금 현재 Sunken이에요
496. 23:32이게 뭐냐 하면 이 상자를 보시면
497. 23:34기본적으로 뭔가 안으로 들어간 듯한 효과가 적용돼 있어요
498. 23:40그런데 이거를 플랫하게 바꿔주는 거예요
499. 23:42그래서 이 Sunken 이펙트를
500. 23:45None으로, Flat으로 바꿔줄게요
501. 23:47플랫으로 평평하게 바꿔주는 거예요
502. 23:51그다음으로 중간에 보시면 LinkedCell 보이실 거예요
503. 23:55연결된 셀이거든요
504. 23:56셀은요, 지금 우리가 입력할 셀 어디죠?
505. 24:00여기죠, B2 셀이죠
506. 24:03그래서 LinkedCell은 B2로 바꿔줄게요
507. 24:08그러면 다 끝났습니다
508. 24:09끝났어요
509. 24:10이제 [속성] 종료할게요
510. 24:13종료하시고요
511. 24:14[개발 도구]에서 [디자인 모드] 체크 해제할게요
512. 24:17체크 해제하시고요
513. 24:18여기에서 검색해보세요
514. 24:20HP의 i7 또는 삼성의 i5 맥북의 M1
515. 24:27이런 식으로 검색하시면
516. 24:29실시간으로 검색되는 걸 볼 수 있습니다
517. 24:31이제 마지막으로는 도형만 적절하게
518. 24:34이렇게 옮겨서 만들어주고 돋보기도 옮겨주고
519. 24:39이 삼각형도 적절하게 우클릭해서
520. 24:42맨 위로, 맨 앞으로 가져오신 다음에 적절하게 바꿔주고요
521. 24:47추가하시면 보고서가 완성되겠죠
522. 24:52그럼 마지막 볼게요
523. 24:53이제 마지막은 SORT 함수로 정렬까지 해볼 건데요
524. 24:56SORT 함수는
525. 24:572021 이후 버전에서만 제공되는 함수이기 때문에
526. 25:012019 이전 버전은 사용이 안 되세요
527. 25:04그 점 참고해 주시면 좋을 것 같고요
528. 25:06SORT 함수는 인수가 3개예요
529. 25:08첫 번째로 정렬할 범위예요
530. 25:11정렬할 범위를 넣어주고요
531. 25:13두 번째는 열 번호예요
532. 25:15몇 번째 열을 기준으로 정렬할 건지 열 번호 넣어주고요
533. 25:19세 번째는 정렬 순서예요
534. 25:22이 정렬 순서는 마이너스 1이면 내림차순이고요
535. 25:261이면 오름차순으로 정렬하는 게 되겠습니다
536. 25:31그러면 볼게요
537. 25:33방금 추가했던 FILTER 함수를 SORT 함수로 정렬할 건데
538. 25:38예를 들어서 정렬 기준을 만약에 가격으로 볼 거다
539. 25:41라고 하면 가격은 현재 7번째 열이에요
540. 25:44일곱 번째 열로 가격 기준 오름차순으로 정렬되죠
541. 25:50만약에 나는 가격 기준 내림차순으로 보고 싶어요
542. 25:54그러면 마이너스 1로 넣어주시면
543. 25:57내림차순으로 정렬할 수도 있겠죠
544. 26:00그러면 이제 마지막인데요
545. 26:03여기에다가 목록 상자를 추가해서요
546. 26:06이 목록 상자를 바꿨을 때 열 번호랑
547. 26:09내림차순, 오름차순 숫자만 바꿔지도록
548. 26:12아까 추가했던 텍스트 상자같이
549. 26:15여기에다가 만들어주면 되겠죠
550. 26:17그래서 [개발 도구] 클릭하시고요
551. 26:19[개발 도구]에 오신 다음에 [삽입]에서 [컨트롤]을 보시면
552. 26:23양식 컨트롤에도 콤보 박스가 있고요
553. 26:25ActiveX에도 콤보 상자가 있어요
554. 26:28그런데 저는 이 콤보 상자는
555. 26:31양식 컨트롤에 있는 상자가 예뻐서
556. 26:34양식 컨트롤 콤보 상자로 추가해 주겠습니다, 이렇게
557. 26:38추가하신 다음에 이거 복사해서 붙여넣기 하면
558. 26:41이렇게 두 개를 추가할 수 있고요
559. 26:45이제 콤보 상자를 우클릭할게요
560. 26:47콤보 상자 우클릭하시고요
561. 26:49[컨트롤 서식]으로 이동을 하겠습니다
562. 26:52[컨트롤 서식]에 오셔서 입력할 범위는요
563. 26:55첫 번째는 정렬 기준이에요
564. 26:58이 정렬 기준은 제가 미리 적어드렸거든요
565. 27:00이렇게 여기 있는 목록을요
566. 27:03여기에 있는 목록을 미리 적어드렸습니다
567. 27:06지금 이렇게 세로로 넣은 이유는요
568. 27:09콤보 상자에 들어가는 값은
569. 27:11세로로 나열된 값들을 넣어주셔야 돼요
570. 27:14이거를 가로로 선택하시면 이 목록에 표시가 안 되기 때문에
571. 27:17반드시 세로로 정렬하신 다음에 넣으시면 되겠습니다
572. 27:21그래서 입력할 범위는 정렬 기준 넣어주시고요
573. 27:25그다음 연결할 셀은 이번에는 여기 넣어줄게요
574. 27:28C2 셀로 오른쪽에다가 넣어주겠습니다
575. 27:32그리고 [확인] 버튼 클릭하시면
576. 27:35여기 목록을 선택했을 때 이렇게 클릭하시면
577. 27:40CPU, RAM 클릭할 때마다 어때요?
578. 27:43여기 있는 연결된 셀의 값이 뭐가 나오죠?
579. 27:47여기 있는 순번이 나오죠, 순번
580. 27:51순번 뭐예요?
581. 27:52이 순번은 방금 우리가 SORT 함수로 썼던 열 번호죠
582. 27:58이렇게 선택하시면 열 번호가 바로 출력됩니다
583. 28:02그래서 방금 만들었던 이 SORT 함수에서
584. 28:06열 번호의 값을 이 콤보 박스와 연결된
585. 28:10셀 주소로 바꿔주면
586. 28:13제조사 기준, 모델명, CPU, RAM, 가격
587. 28:18이렇게 바뀌도록 할 수 있겠죠
588. 28:22첫 번째 끝났고요
589. 28:23두 번째는 뭐예요?
590. 28:24오름차순, 내림차순 바꿔주는 거죠
591. 28:27똑같이 콤보 박스 우클릭하신 다음에 [컨트롤 서식]에서요
592. 28:31이번에는 입력 범위로 이거예요
593. 28:35여기 있는 내림차순, 오름차순 정렬 순서 넣어주시고요
594. 28:40셀 연결 주소는 여기예요
595. 28:43저는 D2 셀로 넣어주겠습니다
596. 28:46그리고 [확인] 버튼 클릭하시면
597. 28:48여기에서 내림차순, 오름차순 클릭하시면 1번, 2번 나오죠?
598. 28:541번, 2번 나옵니다
599. 28:55그래서 오른쪽에다가 IF 함수를 써서
600. 28:59첫 번째, 내림차순이에요
601. 29:01오른쪽에 있는 값이 1이에요
602. 29:05그러면 내림차순은 -1을 넣어서
603. 29:08SORT 함수의 경우에는 -1이 내림차순이니까
604. 29:11-1을 넣어서 내림차순 정렬되도록 하고요
605. 29:16그렇지 않으면 1이 되겠죠
606. 29:19그렇죠? 그럼 끝났어요
607. 29:20이제 마지막으로 SORT 함수에서
608. 29:22여기 있는 정렬 순서는
609. 29:24방금 작성했던 IF 함수로 바꿔주는 거죠
610. 29:29그리고 엔터 키 입력하시면
611. 29:31가격 기준 내림차순, 오름차순, 내림차순
612. 29:34제조사, 모델 오름차순 이렇게
613. 29:38선택했을 때 실시간으로 정렬되고요
614. 29:40또는 아까 여기에서 [디자인 모드] 체크 해제하시고요
615. 29:44여기에서 삼성의 i7 이렇게, 또는 HP의 1TB
616. 29:53이런 식으로 실시간 검색도 하고
617. 29:57정렬까지 할 수 있는 보고서를 완성했습니다