엑셀 시트의 마지막 셀 자동 이동 버튼 만들기 :: 엑셀 자동화

실무 엑셀 자동화를 위한 필수 스킬, 마지막셀을 자동으로 찾아 이동하는 버튼의 동작원리와 제작방법을 단계별로 살펴봅니다.

홈페이지 » 엑셀 시트의 마지막 셀 자동 이동 버튼 만들기 :: 엑셀 자동화

엑셀 마지막 셀 자동 이동 버튼 만들기 :: 동적범위 응용

마지막 셀 자동 이동 버튼 목차 바로가기
강의 요약

엑셀 시트에 매번 새로운 자료를 입력해야 할 경우... (일일 장표, 재고 수불부.. 등) 화면을 이동하지 않고, 마우스 클릭만으로 새로운 데이터를 쉽게 추가하는 방법이 없을까요?

이번강의에서는 매크로를 사용하지 않고, 동적범위만 응용하여 시트의 마지막셀로 한번에 이동하는 버튼을 같이 만들어보겠습니다.

 

엑셀 매크로로 시트의 마지막 행/열번호를 찾는 방법이 궁금하신가요? 아래 포스트에서 예제파일과 함께 자세히 설명해드렸습니다.

영상강의


예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [함수마스터] 동적범위로 마지막셀 이동버튼 만들기
    예제파일
  • [함수마스터] 동적범위로 마지막셀 이동버튼 만들기
    완성파일

관련 기초함수

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))

마지막 셀을 자동으로 찾아 이동하는 버튼 만들기

  1. 버튼으로 사용할 셀을 만들어줍니다. 셀의 크기와 배경색, 테두리를 설정합니다.

    마지막 셀 추가
    마지막 셀 이동 버튼을 만들기 위하여 셀을 꾸며줍니다.

  2. 시트의 아무 곳이나 빈 셀을 클릭한 뒤, '마지막셀'이라는 이름정의범위를 생성합니다.

    시트 이름 정의범위 생성
    동적범위를 만들기 위한 이름정의범위를 추가합니다.

  3. 버튼으로 사용할 셀 넓이에 맞춰 직사각형 모양의 도형을 추가합니다. 도형의 윤곽선과 배경색은 없음으로 설정합니다.

    마지막셀 도형 입히기
    버튼으로 만들어 준 셀의 크기에 맞춰 직사각형 도형을 추가합니다.

  4. 도형을 우클릭한 뒤,  [링크]를 선택합니다.

    마지막 셀 이동 링크 추가
    도형을 우클릭 한 뒤, '링크' 로 이동합니다.

  5. 하이퍼링크 삽입창이 나타나면, 좌측의 [현재문서]를 선택합니다. 이후 [정의된 이름]에서 '마지막셀'을 선택합니다.

    마지막 셀 이동 링크 연결
    '하이퍼링크 편집'창이 나오면, '현재문서' 에서 '마지막셀'을 선택합니다.

  6. '수식' - '이름관리자'로 이동하여 이름정의범위를 편집합니다. (단축키 Ctrl + F3)

    이름관리자 이동
    '수식' - '이름관리자'로 이동합니다.

  7. 참조대상에 아래 수식을 복사하여 붙여넣기 합니다.
    =INDEX(주문!$A:$A,MATCH("*",주문!$A:$A,-1)+1)

    마지막 셀 동적범위 수정
    마지막셀 이름정의범위를 '동적범위'로 변경합니다.

  8. 머릿글 행을 전체 선택한 뒤, [보기] - [틀고정]을 클릭합니다. 틀고정 기능을 이용하면 화면이 아래로 이동하더라도, 틀고정 된 범위는 항상 시트위에 출력됩니다.

    틀고정 추가
    머릿글 전체행을 선택한 뒤, 틀고정합니다.

  9. 마지막 셀 자동 이동 버튼이 완성되었습니다. 이제 버튼을 클릭하면 시트의 마지막 셀로 한번에 이동하게 되어, 새로운 자료를 더욱 쉽게 추가할 수 있습니다.
    Tip! : 키보드 Alt + 를 누르면, 드롭다운 목록상자가 바로 출력되어 자료를 더욱 편리하게 입력할 수 있습니다.

    신규자료입력 GIF
    마지막 셀 자동 이동 버튼이 완성되었습니다.

자주 묻는 질문

Q1. 마지막 셀 자동이동이 제대로 동작하지 않습니다.

참조범위의 값 중, 문자 앞에 띄어쓰기(" ") 또는 특수문자("㈜", "☏" 등..)이 포함되어 있을경우, 마지막셀 자동 이동 버튼이 올바르게 동작하지 않을 수 있습니다. 그럴 경우, MATCH 함수의 '첫번째 인수'에 띄어쓰기를 추가하여 아래와 같이 입력합니다.

= MATCH(" *", 참조범위, -1)
Q2. 마지막 셀 이름정의 범위가 하이퍼링크 편집 목록에 표시되지 않습니다.

함수로 작성된 이름정의범위는 도형에 연결되는 하이퍼링크의 목록으로 표시되지 않습니다. 따라서 '반드시' 일반 범위를 이름정의범위로 생성하여 도형에 우선 연결한 뒤, 이후에 해당 범위를 동적범위로 변경해야 합니다.

4.7 17 투표
게시글평점
guest
35 댓글
Inline Feedbacks
모든 댓글 보기
이웃집또털어
이웃집또털어
2019년 12월 24일 3:36 오전

좋은 자료 감사합니다. 위 내용을 다 이해하지 못했지만 따라해보니 만들기는 했습니다. 몇가지 문의를 드리고 싶습니다... 1. 3-B 에서 사용자 지정 범위를 만들어서 링크에서 활용하는거 같은대 이러한 과정이 어떤 결과에 활용하기… 더보기 »

오빠두엑셀
오빠두엑셀
2019년 12월 24일 3:37 오전
답글 남기기  이웃집또털어

1. 특별한 목적은 없습니다. 사용하기 편한 방식으로 적용하시면 되겠습니다. Ctrl + F3 키로 사용자지정범위를 추가하셔도 괜찮답니다. 2. '맨 위' 라는 것은 항상 고정되어 있지 않을까요?^^ 셀 주소로, =A1 처럼 지정해주시면… 더보기 »

이춘호
이춘호
2020년 1월 8일 9:34 오전
게시글평점 :
     

보면서 항상 느끼는건데........
신 세 계 입니다..
감사합니다....
2020년 새해 복 많이 받으시고, 건강 하세요...
요청이 있다면 지금보다 아주 조금은 천천히 진행하셨으면 합니다. ㅎ

김희진
김희진
2020년 2월 17일 11:15 오전
게시글평점 :
     

선생님 안녕하세요... 질문이 있어요... ㅠ_ㅠ
마지막셀을 구성하는 열 자체가 숫자일 경우에는 어떻게 하나요?
똑같이 했는데 숫자라서 그런지 안되어요 ㅠ_ㅠ

궁금합니다.
궁금합니다.
2020년 2월 28일 9:18 오전
게시글평점 :
     

하이퍼링크 삽입에 정의된 이름에 목록이 안뜨는건 왜그럴까요?
첨부된 예제 파일도 마찬가지고, 새로 만들어 봐도 그런데요.

전상용
전상용(@1287)
2020년 3월 24일 11:23 오전
게시글평점 :
     

좋은 자료 감사합니다~~

kenmamin
kenmamin(@kenmamin)
2020년 4월 2일 4:48 오후
게시글평점 :
     

잘보고 갑니다.
중간부터 보니 동적범위가 이해가 잘 안되서 처음자료부터 정주행하고 있습니다.

0ugi
0ugi(@0ugi)
2020년 4월 13일 10:19 오전
게시글평점 :
     

역시 초고수.. 강의 감사합니다. 꼭 써먹어보렵니다!!

림수
림수(@1515)
2020년 4월 13일 10:22 오후
게시글평점 :
     

정말 액셀의 기능이란 ㅜ 감사합니다. 오늘도 하나매눠서 업무에 활용합니다.

Hyo
Hyo(@dekorinaver-com)
2020년 5월 3일 6:54 오전
게시글평점 :
     

!! 정리가 깔끔하네요~

f9****
f9****(@dekirunonidaum-net)
2020년 5월 13일 1:21 오후
게시글평점 :
     

첫 강의 수강 완료. 감사합니다.

굴레악
굴레악(@stcold12gmail-com)
2020년 7월 11일 9:46 오후
게시글평점 :
     

아 포스트 다시 보고 해결했네요. 날짜 범위로 정한 DB표에서는 잘 됐었는데 그만 문자 범위의 DB표 바뀐걸 놓치고 있었네요. 문자 범위 식으로 입력하니 바로 됩니다. 굿굿!! 숫자냐 문자냐 잘 확인하면 잘… 더보기 »

Last edited 1 년 전 by 굴레악
엑린이
엑린이
2020년 7월 25일 6:23 오후
게시글평점 :
     

숫자로 되어있는 탭이라 MATCH(9.999999999E+307, $참조범위, 1)+1) 활용해서 넣었는데 다른탭에서 링크로 이동을하면 한번에 마지막으로 이동하지 않고 꼭 한번더 클릭을 해줘야 맨 마지막으로 이동을 합니다. 왜그런걸까요?ㅠㅠ

피이써
피이써(@barnsonnaver-com)
2020년 9월 22일 3:26 오후
답글 남기기  엑린이

강의 과정에 설명해 주셨는데 빠트리신 내용 있을겁니다.
활용해 넣은 박스를 다른 시트에 복사해 주셔야죠.
기존 박스는 지우고!

스마일
스마일(@cnddnjs14naver-com)
2020년 8월 23일 10:21 오전
게시글평점 :
     

좋은내용 감사합니다. 근데 너무 빨라서 따라가기가 힘드네요 그래도 하나 하나 해볼게요

헐레벌떡
헐레벌떡(@nanana5737naver-com)
2020년 9월 2일 11:36 오전
게시글평점 :
     

이게 좀 비슷한듯 다른 질문이긴 한데요.
매크로 버튼 자체도 데이터가 입력된 마지막칸으로 이동할려는데 어떻게 해야할까요?

yachtclub
yachtclub(@yachtclub)
2020년 10월 26일 11:24 오전
게시글평점 :
     

안녕하세요. 선생님 항상 감사드립니다.
열값에 날짜가 있는 경우에는 어떻게 해야 할까요?
" *" 띄어쓰기하고 9.999999999E+307도 안됩니다.

yachtclub
yachtclub(@yachtclub)
2020년 10월 26일 4:57 오후
답글 남기기  오빠두엑셀

선생님 답글 감사드립니다.
따옴표가 안붙는것을 뒤늦게 발견하였습니다.^^;;
다음부터는 좀 더 세심히보고 질문하겠습니다.

퍼그
퍼그(@a8441naver-com)
2021년 1월 20일 10:49 오전
게시글평점 :
     

안녕하세요~선생님
새해 복 많이 받으시고~항상 감사히 보고 있답니다.
마지막셀 값의 일부만 가져오고 싶은데
수식을 어찌 적용해야 할까요?
셀값이 공백 포함 19자 인데 앞의10글자만 가져오고싶어요

박천호
박천호(@park8644gmail-com)
2021년 2월 9일 7:38 오전
게시글평점 :
     

딱 2년후에 업로드 파일 보네요. 잘 보고있습니다. 감사합니다.

Prankshe
Prankshe(@prankshenaver-com)
2021년 4월 9일 5:16 오후
게시글평점 :
     

감사합니다

Last edited 7 월 전 by Prankshe
그때의너
그때의너(@jinyoung_cholutronic-com)
2021년 4월 27일 9:29 오전
게시글평점 :
     

마지막셀 찾는 수식에서 참조할 셀 형식이 날짜인 경우에는 어떻게 해야 할까요?

그때의너
그때의너(@jinyoung_cholutronic-com)
2021년 4월 28일 10:02 오전
답글 남기기  오빠두엑셀

와 감사합니다!!

doleme
doleme(@doleme)
2021년 6월 7일 10:21 오후
게시글평점 :
     

=match("*",A:A,-1) 71 값이
=match("*",A:A,0) 2 값이
=match("*",A:A,1) N/A 로 출력됩니다.
문자에는 크거나 작다는 개념이 없는데 왜 이런 차이가 나는지 이해가 잘 되지 않습니다.

Doleme
Doleme
2021년 6월 15일 9:22 오전
답글 남기기  오빠두엑셀

답변 주셔서 감사합니다. 문자에 크기가 잇군요. 알려주셔서 감사합니다.

루팡46세
루팡46세(@46)
2021년 7월 22일 3:06 오후
게시글평점 :
     

정말 대단하십니다~
감탄 연발!!!!

35
0
여러분의 생각을 댓글로 남겨주세요.x