빈칸을 제외하는 동적범위 공식

강의 요약

일일 장표나 재고 수불부처럼 매번 새로운 자료를 시트에 추가해야 하는 상황에서, 화면을 일일이 스크롤하지 않고 마우스 클릭만으로 마지막 셀로 이동해 데이터를 입력할 수 있다면 작업 속도가 한층 빨라집니다.

이번 강의에서는 매크로 없이 동적범위만 응용하여 시트의 마지막 셀로 한 번에 이동하는 버튼을 만드는 방법을 단계별로 살펴봅니다.

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

동적범위의 기초 이론과 활용법은 아래 10분 분량의 영상 강의에서 자세히 다루었으니 함께 참고하시면 좋습니다.

COUNTA를 사용한 동적범위의 한계와 해결 방법

1. COUNTA 함수를 사용한 동적범위의 문제점

OFFSET 함수를 이용한 동적범위

INDEX 함수를 이용한 동적범위

이전 강의에서는 COUNTA 함수를 활용한 동적범위를 살펴보았습니다. COUNTA 동적범위는 대부분의 상황에서 무리 없이 적용할 수 있고 작성이 간결하다는 장점이 있습니다. 하지만 참조범위 안에 빈칸이 포함되어 있을 경우 올바른 범위를 반환하지 못하는 한계가 있습니다.

따라서 참조범위에 빈칸이 섞여 있다면 MATCH 함수를 사용한 동적범위로 작성하는 방식을 권장합니다.

2. MATCH 함수를 사용한 동적범위

MATCH 함수를 활용해 동적범위를 구성하면 참조범위에 빈칸이 포함되어 있어도 마지막 셀을 정확히 인식할 수 있습니다. 다만 MATCH 동적범위는 참조범위에 들어가는 데이터의 종류에 따라 '문자'와 '숫자'에 각각 다른 수식을 사용해야 하므로 상황을 구분해 작성합니다. (이 규칙은 INDEX 동적범위에서도 동일하게 적용됩니다.)

문자가 포함된 참조범위일 경우

숫자가 포함된 참조범위일 경우

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

1. 버튼으로 사용할 셀을 먼저 만듭니다. 셀의 크기와 배경색, 테두리를 원하는 디자인에 맞춰 설정합니다.
2. 시트의 빈 셀을 아무 곳이나 선택한 뒤, '마지막셀'이라는 이름정의 범위를 생성합니다.
3. 버튼으로 사용할 셀의 크기에 맞춰 직사각형 도형을 추가합니다. 도형의 윤곽선과 배경색은 모두 '없음'으로 지정합니다.
4. 도형 위에서 우클릭한 뒤 [링크] 메뉴를 선택합니다.
5. 하이퍼링크 삽입 창이 열리면 좌측의 [현재 문서]를 선택한 뒤, [정의된 이름] 목록에서 '마지막셀'을 선택해 도형과 연결합니다.
6. 리본의 [수식] - [이름 관리자]로 이동해 앞서 만든 이름정의 범위를 편집합니다. (단축키 Ctrl + F3)
7. 참조 대상 입력란에 아래 수식을 그대로 복사해 붙여 넣습니다.
   =INDEX(주문!$A:$A,MATCH("*",주문!$A:$A,-1)+1)

   

8. 머릿글 행을 전체 선택한 뒤 [보기] - [틀 고정]을 클릭합니다. 틀 고정을 사용하면 화면을 아래로 이동하더라도 머릿글 행이 항상 시트 상단에 고정되어 표시됩니다.
9. 이렇게 마지막 셀 자동 이동 버튼이 완성되었습니다. 이제 버튼을 클릭하면 시트의 마지막 셀로 한 번에 이동하므로, 새 자료를 빠르고 편리하게 추가할 수 있습니다.
   Tip! : 키보드 Alt + ↓ 를 누르면 드롭다운 목록 상자가 즉시 나타나 자료를 더욱 편리하게 입력할 수 있습니다.

   

자주 묻는 질문

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

참조범위 안에 문자 앞 띄어쓰기(" ") 또는 특수문자("㈜", "☏" 등)가 포함되어 있을 경우, 마지막 셀 자동 이동 버튼이 의도대로 동작하지 않을 수 있습니다. 이런 경우에는 MATCH 함수의 첫 번째 인수에 띄어쓰기를 추가하여 아래와 같이 수식을 변경합니다.

Q2. 마지막 셀 이름정의 범위가 하이퍼링크 편집 목록에 표시되지 않습니다.

함수로 작성된 이름정의 범위는 도형에 연결되는 하이퍼링크 목록에 곧바로 표시되지 않습니다. 따라서 먼저 일반 범위를 이름정의 범위로 만들어 도형에 연결한 뒤, 이후 단계에서 해당 범위를 동적범위 수식으로 변경하는 순서로 작업해야 합니다.