엑셀 마지막 셀 자동 이동 버튼 만들기 :: 동적범위 응용
마지막 셀 자동 이동 버튼 목차 바로가기
강의 요약
엑셀 시트에 매번 새로운 자료를 입력해야 할 경우... (일일 장표, 재고 수불부.. 등) 화면을 이동하지 않고, 마우스 클릭만으로 새로운 데이터를 쉽게 추가하는 방법이 없을까요?
이번강의에서는 매크로를 사용하지 않고, 동적범위만 응용하여 시트의 마지막셀로 한번에 이동하는 버튼을 같이 만들어보겠습니다.
범위의 마지막 셀을 찾는 완성 공식!
동적범위의 기초 이론부터 활용법까지 자세한 설명은 아래 10분 정도의 영상 강의에서 자세히 다루었습니다.
영상강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀고급] 동적범위로 마지막셀 이동버튼 만들기예제파일[엑셀고급] 동적범위로 마지막셀 이동버튼 만들기완성파일
관련 기초함수
COUNTA를 사용한 동적범위의 한계와 그 해결방법
1. COUNTA 함수를 사용한 동적범위의 문제점
OFFSET 함수를 이용한 동적범위
=OFFSET($기준셀, , , COUNTA($상하범위), COUNTA($좌우범위))INDEX 함수를 이용한 동적범위
=$시작셀:INDEX($참조범위, COUNTA($상하범위), COUNTA($좌우범위) )이전 강의에서는, COUNTA 함수를 사용한 동적범위에 대해 알아봤는데요. COUNTA함수를 사용한 동적범위는 대부분의 상황에서 모두 적용할 수 있고 사용하기 편리하다는 장점이 있습니다. 하지만 참조하는 범위에 ‘빈칸’이 포함되어 있을 경우 올바른 범위를 반환하지 않는 문제점이 있습니다.
따라서 참조범위에 빈칸이 포함되어 있을 경우, MATCH 함수를 사용하여 동적범위를 작성합니다.
2. MATCH 함수를 사용한 동적범위
MATCH 함수를 사용하여 동적범위를 만들면, 참조범위에 빈칸이 포함되어 있을경우 발생하는 문제를 해결 할 수 있습니다. 단, MATCH 함수를 사용한 동적범위는 참조범위에 포함된 값에따라 '문자' 와 '숫자'일 경우 각각 다른 수식을 사용해야합니다. (INDEX 동적범위에서도 동일하게 사용가능합니다.)
문자가 포함된 참조범위일 경우
=OFFSET($시작셀, , ,MATCH("*", $참조범위, -1))숫자가 포함된 참조범위일 경우
=OFFSET($시작셀, , ,MATCH(9.999999999E+307, $참조범위, 1))마지막 셀을 자동으로 찾아 이동하는 버튼 만들기
- 버튼으로 사용할 셀을 만들어줍니다. 셀의 크기와 배경색, 테두리를 설정합니다.
마지막 셀 이동 버튼을 만들기 위하여 셀을 꾸며줍니다. - 시트의 아무 곳이나 빈 셀을 클릭한 뒤, '마지막셀'이라는 이름정의범위를 생성합니다.
동적범위를 만들기 위한 이름정의범위를 추가합니다. - 버튼으로 사용할 셀 넓이에 맞춰 직사각형 모양의 도형을 추가합니다. 도형의 윤곽선과 배경색은 없음으로 설정합니다.
버튼으로 만들어 준 셀의 크기에 맞춰 직사각형 도형을 추가합니다. - 도형을 우클릭한 뒤, [링크]를 선택합니다.
도형을 우클릭 한 뒤, '링크' 로 이동합니다. - 하이퍼링크 삽입창이 나타나면, 좌측의 [현재문서]를 선택합니다. 이후 [정의된 이름]에서 '마지막셀'을 선택합니다.
'하이퍼링크 편집'창이 나오면, '현재문서' 에서 '마지막셀'을 선택합니다. - '수식' - '이름관리자'로 이동하여 이름정의범위를 편집합니다. (단축키 Ctrl + F3)
'수식' - '이름관리자'로 이동합니다. - 참조대상에 아래 수식을 복사하여 붙여넣기 합니다.
=INDEX(주문!$A:$A,MATCH("*",주문!$A:$A,-1)+1)
마지막셀 이름정의범위를 '동적범위'로 변경합니다. - 머릿글 행을 전체 선택한 뒤, [보기] - [틀고정]을 클릭합니다. 틀고정 기능을 이용하면 화면이 아래로 이동하더라도, 틀고정 된 범위는 항상 시트위에 출력됩니다.
머릿글 전체행을 선택한 뒤, 틀고정합니다. - 마지막 셀 자동 이동 버튼이 완성되었습니다. 이제 버튼을 클릭하면 시트의 마지막 셀로 한번에 이동하게 되어, 새로운 자료를 더욱 쉽게 추가할 수 있습니다.
Tip! : 키보드 Alt + ↓ 를 누르면, 드롭다운 목록상자가 바로 출력되어 자료를 더욱 편리하게 입력할 수 있습니다.
마지막 셀 자동 이동 버튼이 완성되었습니다.
자주 묻는 질문
Q1. 마지막 셀 자동이동이 제대로 동작하지 않습니다.
참조범위의 값 중, 문자 앞에 띄어쓰기(" ") 또는 특수문자("㈜", "☏" 등..)이 포함되어 있을경우, 마지막셀 자동 이동 버튼이 올바르게 동작하지 않을 수 있습니다. 그럴 경우, MATCH 함수의 '첫번째 인수'에 띄어쓰기를 추가하여 아래와 같이 입력합니다.
= MATCH(" *", 참조범위, -1)Q2. 마지막 셀 이름정의 범위가 하이퍼링크 편집 목록에 표시되지 않습니다.
함수로 작성된 이름정의범위는 도형에 연결되는 하이퍼링크의 목록으로 표시되지 않습니다. 따라서 '반드시' 일반 범위를 이름정의범위로 생성하여 도형에 우선 연결한 뒤, 이후에 해당 범위를 동적범위로 변경해야 합니다.
위 내용을 다 이해하지 못했지만 따라해보니 만들기는 했습니다.
몇가지 문의를 드리고 싶습니다...
1. 3-B 에서 사용자 지정 범위를 만들어서 링크에서 활용하는거 같은대
이러한 과정이 어떤 결과에 활용하기 위함인지 궁급합니다.
2. '맨 위로 이동' 버튼을 만든다고 하면 어떤 부분을 고쳐야 활용 할 수 있나요?
3. 과거에 데이터 입력을 위해 표와 표 입력 공간을(아래로 스크롤) 띄엄 띄엄 놓고 스크롤 이동 하기가 힘들어
이동 버튼을 만들자는 생각에 TEXT에 하이퍼 링크를 걸어 만든 다음 시트를 복사하여 사용하였더니
처음 하이퍼 링크를 연결 해놨던 시트로만 이동하여 제대로 활용하지 못했었는대
위 방법을 응용하면 시트를 복사하더라도 각 '시트'안에서 원하는 지점에 이동할 수 있게도 할 수 있나요 ?
아래와 같이 표를 만들었었습니다.
A. 상품 입력 표
입력을 위한 공간
B. 상품 가격 입력 표
입력을 위한 공간
C. 상품 유통 기한 표
입력을 위한 공간
바쁘실 텐대 위 질문에 짧게나마 답변 주신 다면 많은 도움이 될 듯 합니다.
긴 질문 읽어 주셔서 감사합니다.
오늘도 좋은 하루 되세요.
2. '맨 위' 라는 것은 항상 고정되어 있지 않을까요?^^ 셀 주소로, =A1 처럼 지정해주시면 될듯합니다.
만약 위쪽으로 데이터가 쌓여가는 형식이라면.. 아래 공식으로 이름정의범위를 추가해보시겠어요?
=MATCH("*",A:A,0)
3. 가능합니다. 다만, 복사붙여넣기가 아닌 [잘라서 붙여넣기로]으로 목적지 통합문서로 이동해주셔야 합니다. 또한 중복된 '이름정의범위'가 없다는 가정하에서만 정상동작합니다. :)
신 세 계 입니다..
감사합니다....
2020년 새해 복 많이 받으시고, 건강 하세요...
요청이 있다면 지금보다 아주 조금은 천천히 진행하셨으면 합니다. ㅎ
시대가 지날수록 많은 분들이 짧고, 간결하면서 보다 빠른 내용 전달을 원하시다보니, 반대로 그렇지 않은 분들과의 격차를 줄이며 진행하는게 매번 신경쓰지만 여간 쉽지만은 않은것 같습니다..
제 기억에 동적범위 강의는 처음 40분짜리 영상을 15분, 20분으로 줄이며 중요한 부분만 압축한다고 나름 신경쓴 영상인데도 실제 실무에 계신분들은 핵심 공식만 필요로 하는게 사실이여서, 영상 초반부보다 후반부 지속시간이 더 높게 나오기도 합니다..쿨럭..ㅠ-ㅠ;
이러한 상황에서 최대한 격차를 줄이고자 매 강의마다 홈페이지에 상세내용을 포스트로 적어드리고 있으니, 혹시라도 부족한 부분은 댓글로 남겨주시면 최대한 성심성의껏 답변드리겠습니다.
소중한 의견 감사드리며 항상 응원하겠습니다.
이춘호님도 새해복 많이 받으시고 건승하시길 기원합니다. 감사합니다.
마지막셀을 구성하는 열 자체가 숫자일 경우에는 어떻게 하나요?
똑같이 했는데 숫자라서 그런지 안되어요 ㅠ_ㅠ
match 함수 공식을,
로 변경해보시겠어요?^-^
해당포스트 2번 항목에 공식 적어드렸으니 확인해보세요 ^-^
제 답변이 도움이 되셨길 바랍니다.
감사합니다!
첨부된 예제 파일도 마찬가지고, 새로 만들어 봐도 그런데요.
영상강의에서 설명드린바와 같이, '동적범위로 설정된 이름정의범위'는 하이퍼링크 목록에 나타나지 않습ㄴ다.
따라서 1. 일반 범위로 하이퍼링크를 우선 연결한 뒤, 2. 해당 범위를 다시 동적범위로 변경하는 순서 작업하셔야 합니다 ^^
제 답변이 도움이 되셨길 바랍니다.
감사합니다.
중간부터 보니 동적범위가 이해가 잘 안되서 처음자료부터 정주행하고 있습니다.