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

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

마지막 셀 자동 이동 버튼 목차 바로가기

예제파일 다운로드
COUNTA 함수를 사용한 동적범위의 문제점
MATCH 함수를 사용한 동적범위 공식
마지막 셀 자동 이동버튼 만들기
자주묻는질문1. 마지막 셀 자동이동 버튼이 제대로 동작하지 않습니다.
자주묻는질문2. 이름정의범위가 도형 연결 링크목록에 나타나지 않습니다.

강의 요약

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

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

범위의 마지막 셀을 찾는 완성 공식!

=INDEX($세로범위,MAX(IFERROR(MATCH("*",$세로범위,-1),0),IFERROR(MATCH(9E+307,$세로범위,1),0)))

동적범위의 기초 이론부터 활용법까지 자세한 설명은 아래 10분 정도의 영상 강의에서 자세히 다루었습니다.

오빠두Tip : VBA를 활용한 마지막 행/열번호를 찾는 방법은 아래 게시글을 참고해주세요.

엑셀 매크로로 시트의 마지막 행/열 번호 구하는 방법 바로가기

영상강의

예제파일 다운로드

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

[엑셀고급] 동적범위로 마지막셀 이동버튼 만들기
예제파일
다운로드
[엑셀고급] 동적범위로 마지막셀 이동버튼 만들기
완성파일
로그인하기

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. 마지막 셀 이름정의 범위가 하이퍼링크 편집 목록에 표시되지 않습니다.

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

4.8 36 투표

게시글평점