엑셀 마지막 셀 이동 버튼, 매크로 없이 구현하는 필수 자동화 기능 | MATCH 동적범위 | 엑셀고급 3강 — Transcript

Full transcript

1. 0:03지난주에 방을 이사하느라고 영상 업로드가 좀 늦었습니다.
2. 0:06지금 보시면, 아직.. 배경이랑 준비가 안되어있는데, 빠른시일내에 준비해서 좋은 영상으로 인사드리겠습니다.
3. 0:15이번 강의는 지난번 약소드렸던 내용 중 하나입니다. 대쉬보드 만들기를 할때 알면 굉장히 좋은 팁중에 하나입니다.
4. 0:23매크로를 사용하지 않고 한번에 클릭만으로 마지막셀로 이동하는 방법에 대해서 알아보겠습니다. 화면을 같이 볼께요.
5. 0:32가계부 대시보드페이지가 있습니다.
6. 0:39대시보드, 가계부입력, 환경설정 버튼이 있습니다. 버튼을 클릭할때마다 원하는 페이지로 이동을 합니다.
7. 0:48여기에서, 가계부 입력 버튼을 클릭하면, 이런식으로 가계부 페이지에서 마지막셀로 이동하는 방법에 대해서 알아볼겁니다.
8. 1:00주기적으로 입력하는 자료를 좀 더 효과적으로 관리할 수 있게 됩니다.
9. 1:05준비되셨나요? 함께 가시죠!
10. 1:12이번 강의에 내용을 이해하기 위해서는, OFFSET/INDEX 동적범위에 대해서 어느정도 숙지하고 있어야 합니다.
11. 1:22그 내용에대해 짧게 짚고 넘어가겠습니다.
12. 1:26일단 첫번째로, 동적범위를 효율적으로 사용하기 위해서는 고유값, 예를 들어 사원번호/재고번호/주민등록번호를 이용해야합니다.
13. 1:45즉 빈칸이 존재하지 않는 값들이 세로로 나열되어있을때, 값이 늘어날때마다 자동으로 확장되는 동적범위에 대해서 알아봤습니다.
14. 1:58만약 중간 사이사이에, 빈칸이 있을 경우, 이전에 배웠던 OFFSET/INDEX 동적범위로는 제대로 사용할 수 없습니다.
15. 2:08이번강의에서 배우는 MATCH 함수를 이용해서 문제를 해결합니다.
16. 2:18MATCH 함수는 찾을 값을, 참조 범위에서 MATCH옵션에 따라서 검색한 뒤, 찾을 값이 참조범위에서 몇번째에 위치하는지 숫자로 반환합니다.
17. 2:42A열 범위에서 이은지라는 값을 검색합니다.
18. 2:50MATCH 함수의 값으로 6이 반환됩니다.
19. 2:56F열 전체범위에서 130,000을 검색합니다. MATCH 옵션은 1 (기본값, 작거나 같은 값중 최대값)입니다.
20. 3:16MATCH 함수는 120,000이 위치하는 8을 반환합니다.
21. 3:28COUNTA함수를 이용했던 INDEX/OFFSET 함수의 빈칸인식문제를 해결합니다.
22. 3:40"*"(별표)는 와일드카드입니다. 모든 값을 검색합니다.
23. 3:51참조 범위에서 모든 문자를 찾아서 빈칸이 아닌 값의 마지막셀을 찾습니다.
24. 4:05다만 숫자를 빈칸으로 인식하기 때문에, 숫자가 입력된 참조범위에서는 두번째 동식을 이용하셔야합니다.
25. 4:17참조범위에서 엑셀에서 입력가능한 가장 큰 숫자인, 9.99 X 10^307 보다 작은 값을 검색합니다.
26. 4:33즉 어떠한 숫자가 입력되더라도 작은 값이 입력될겁니다. 즉 참조범위에서 숫자가 입력된 마지막셀을 반환하게됩니다.
27. 4:47MATCH 함수와 OFFSET 함수를 이용해서 아래와 같이 동적범위를 만듭니다.
28. 5:05여기에 관련된 내용은, 우측 상단 링크클릭하셔서 OFFSET 동적범위 관련 강의를 참고해주세요.
29. 5:23홈페이지에서 예제파일 다운 받아주세요.
30. 5:34이번 강의에서는, 이런 식으로 버튼을 클릭하면 신규주문을 위한 마지막세롤 이동하는 버튼을 만들어봅니다.
31. 5:45제품 시트로 이동해주세요.
32. 5:53Ctrl + A 키로 전체범위를 선택 해주세요.
33. 5:56여기에서.. 엑셀 버전을 한글로 바꾸고 올께요
34. 6:00키보드 Ctrl + A 키로 전체 범위 선택하신 뒤, 삽입 -> 표를 눌러 표형식으로 바꿔줍니다.
35. 6:12머릿글 포함에 체크하는것 잊지 말아주세요.
36. 6:21디자인에 오셔서 표 이름을 tbl제품으로 바꿔주세요.
37. 6:37주문 시트로 넘어오신 뒤, 거래처와 제품에 데이터유효성 검사를 적용합니다.
38. 6:48A3셀 클릭하신 뒤, Ctlr + Shift + 아래방향키로 A열 전체를 선택해주세요.
39. 6:59데이터 -> 데이터 유효성검사로 오신 뒤, 제한 대상은 '목록' 선택하세요.
40. 7:23방금 만들어줬던 tbl거래처의 값들을 선택할 수 있습니다.
41. 7:29만약, tbl거래처에 이런식으로 새로운값을 추가하면 주문시트에서 새로운 값이 추가된 것을 볼 수 있습니다.
42. 7:53제품도 똑같이 적용해주세요.
43. 8:06tbl제품에는 [제품명]열, [단가]열이 있었습니다. 이번에는 tbl제품 표에서 [제품명]에 해당하는 열만 불러옵니다.
44. 8:36tbl제품의 [제품명]에 해당하는 열 값을 불러옵니다.
45. 8:45이런 식으로 원하는 값만 불러올 수 있는 데이터 유효성 목록 상자를 만들어줬습니다.
46. 8:51단가는 제품시트에서, VLOOKUP 함수를 이용해서 입력해주세요.
47. 9:03가격은 단가 X 수량 입니다.
48. 9:15셀 이동을 위한 머릿글을 만들어주겠습니다.
49. 9:172열 전체 선택하신 뒤, Ctrl + Shift + "+" 버튼으로 열을 2개 추가해주세요.
50. 9:29열의 배경색을 회색으로 바꿔주세요.
51. 9:41Ctrl + B로 글꼴을 굵게 해주세요.
52. 9:48Ctrl + 1로 셀 서식 오신 뒤, 검은섹 테두리를 위에 굵게 넣어주세요.
53. 10:01아무곳이나 클릭 하신 뒤, 셀 이름상자에 "마지막셀"로 만들어주세요.
54. 10:20삽입에 오셔서, 도형을 추가해주세요. 네모난 직사각형을 추가합니다. 도형 채우기와 윤관석은 없음으로 바꿔주세요.
55. 10:41도형에서 우클릭하셔서 링크를 클릭하신 뒤, [현재문서]에서 정의된 이름에 [마지막셀]을 선택해주세요.
56. 10:56도형을 클릭할 때마다, 마지막셀로 이동하는 것을 볼 수 있습니다.
57. 11:03Ctrl + F3로 이름관리자 오신 뒤, 마지막셀을 MATCH 함수를 이용해서 만들어줍니다.
58. 11:11이번 강의에서는 INDEX/MATCH 함수를 이용합니다. INDEX/MATCH 함수는 이전에도 많이 다루었습니다. 우측 상단 링크 클릭해서 관련강의 참고해주세요.
59. 11:36검색할 범위인 A열은 문자열입니다. 두가지 MATCH 함수중 "*"(별표)를 이용하는 MATCH함수를 이용합니다.
60. 12:01마지막 셀이 변경되었습니다. 버튼을 클릭하시면, 마지막셀로 이동하는 것을 볼 수 있습니다.
61. 12:13마지막셀로 이동하는 것이 아닌, 그 다음 자료를 바로 입력하기 위한 마지막셀 다음칸으로 이동해야합니다.
62. 12:29괄호 2개 사이에 '+1'을 넣어주세요.
63. 12:39버튼을 클릭하시면, 마지막셀에서 다음셀로 이동하는 것을 볼 수 있습니다.
64. 12:513열 클릭하신 뒤, [보기] - [틀고정]을 해줍니다.
65. 13:11Alt + 아래방향키로 값을 바로 추가해주세요.
66. 13:27이번 강의에서는 매크로없이 버튼 클릭한번으로 마지막셀로 이동하는 방법에대해서 알아봤습니다.
67. 13:36이 기능을 이용하시면 엑셀 자동화프로그램 만드는데 큰 도움이 되실겁니다.
68. 13:39강의 중 궁금하신 내용은 아래 댓글로 남겨주세요.[00:13:51.21]이번 강의가 도움되셨으면 좋아요와 구독 부탁드리겠습니다.