엑셀 동적 이름 범위 만들기 [4가지 방법 총정리]

엑셀의 동적 이름 범위(Dynamic Named Range)는 의외로 많은 사용자가 잘 모르고 있는 강력한 기능입니다. 데이터가 늘어나거나 줄어들 때마다 범위를 일일이 수정하지 않아도 되므로, 반복 작업을 크게 줄여줍니다. 이 글에서는 OFFSET 함수, INDEX 함수, VBA, 빈 셀 처리까지 총 4가지 방법으로 동적 이름 범위를 만드는 방법을 예제와 함께 자세히 알아보겠습니다.

1. OFFSET 함수로 동적 이름 범위 만들기

동적 범위를 만드는 가장 대표적인 방법은 OFFSET 함수를 활용하는 것입니다.

엑셀 OFFSET 함수의 기본 구문

OFFSET 함수의 구문은 다음과 같습니다.

OFFSET(reference, rows, cols, [height], [width])

각 인수의 의미는 다음과 같습니다.

1. Reference(참조) – 오프셋 계산의 기준이 되는 셀 또는 범위입니다.
2. Rows(행) – 기준 셀에서 위 또는 아래로 이동할 행 수입니다.
3. Cols(열) – 기준 셀에서 오른쪽 또는 왼쪽으로 이동할 열 수입니다.
4. Height, Width(높이, 너비) – 기준 셀로부터 선택할 영역의 높이와 너비를 지정합니다.

이름 관리자에서 OFFSET 함수로 동적 범위 정의하기

이름 관리자(Name Manager) 대화 상자를 사용하면 동적 이름 범위를 손쉽게 정의할 수 있습니다.

설정 방법은 다음과 같습니다.

리본 메뉴에서 수식(Formulas) 탭 → 정의된 이름(Defined Names) 그룹 → 이름 정의(Define Name)를 클릭하면 아래와 같은 새 이름(New Name) 대화 상자가 나타납니다.

예를 들어 A1 셀보다 한 칸 아래에서 시작해, 같은 열에서 K1과 K2 셀에 입력된 값만큼의 행과 열을 선택하는 동적 이름 범위를 만든다고 가정해 보겠습니다.

이 경우 '참조 대상(Refers to)' 입력란에 들어갈 수식은 다음과 같습니다.

=OFFSET(Sheet1!$A$1;1;0;Sheet1!$K$1;Sheet1!$K$2)

OFFSET 함수의 모든 인수는 동적으로 구성할 수 있습니다. 예를 들어 특정 요일에 해당하는 날짜 개수만큼만 선택하고 싶다면 아래처럼 결과를 조절할 수 있습니다.

=OFFSET(Sheet1!$A$1;1;0;6;2) → A2:B7 범위를 참조합니다.

K1, K2, K3, K4 셀의 입력값에 따라 범위가 달라지도록 하려면 수식을 다음과 같이 작성하면 됩니다.

=OFFSET(Sheet1!$A$1;Sheet1!$K$3;Sheet1!$K$4;Sheet1!$K$1;Sheet1!$K$2)

또한 OFFSET 함수는 숫자를 반환하는 다른 함수들과 자유롭게 조합할 수 있습니다. 예를 들어 월요일을 기준으로 지난주 화요일을 제외한 나머지 모든 화요일 날짜를 선택하고 싶다면, 같은 예제에서 수식을 다음과 같이 구성할 수 있습니다.

=OFFSET(Sheet1!$A$1;1;1;COUNT(Sheet1!$B:$B);1)

2. INDEX 함수로 동적 이름 범위 만들기

OFFSET 외에도 INDEX 함수를 활용하면 동적 이름 범위를 만들 수 있습니다. 예를 들어 데이터 개수와 상관없이 '월요일'에 해당하는 모든 날짜를 선택하고 싶다면 다음과 같이 작성합니다.

=Sheet1!$A$2:INDEX(Sheet1!$A:$A;COUNTA(Sheet1!$A:$A))

참조 위치 자체도 동적으로 만들고 싶다면 여러 함수를 조합할 수 있습니다. 예를 들어 한 셀에 요일 이름을 입력하면, 표에서 해당 요일에 속한 모든 날짜를 자동으로 선택하도록 하려면 다음 수식을 사용합니다.

=OFFSET(INDIRECT(ADDRESS(2;MATCH(Sheet1!$K$6;Sheet1!$1:$1;0)));0;0;COUNT(Sheet1!$A:$A);1)

3. VBA로 동적 이름 범위 만들기

실무에서는 같은 작업을 반복해서 수행해야 하는 경우가 많습니다. 동적 이름 범위 생성도 마찬가지로 반복될 수 있는데, 이럴 때 VBA를 활용하면 반복 작업을 자동화할 수 있습니다.

VBA는 수식과 동일한 방식으로 작동하며, 이름 범위를 추가하는 데 특화된 구문만 익히면 됩니다.

기본 구문은 다음과 같습니다.

ActiveWorkbook.names.Add Name:="NAME", RefersTo:="RANGE THAT YOU WANT TO SELECT"

앞서 살펴본 예제를 VBA로 적용하면 전체 매크로는 다음과 같이 완성됩니다.

Sub naming()
    ActiveWorkbook.names.Add Name:="NAME9", RefersTo:="=OFFSET(Sheet1!$A$1,0,1,counta(A:A),2)"
End Sub

4. 빈 셀이 있을 때 동적 이름 범위 처리하기

실제 데이터에는 채워지지 않은 셀, 즉 빈 셀이 섞여 있는 경우가 흔합니다. 이럴 때 가장 중요한 것은 범위를 찾는 데 사용하는 함수의 정의를 정확히 아는 것입니다. 예를 들어 COUNTA 함수는 범위 내에서 비어 있지 않은(NON-BLANK) 셀만 계산합니다. 따라서 빈 셀을 포함할지 여부에 따라 적절한 함수를 선택해야 합니다.

아래 이미지는 빈 셀(A5)이 포함된 상황을 보여줍니다.

'월요일'에 해당하는 모든 날짜를 선택하려고 할 때, 앞선 예제의 수식을 그대로 사용하면 빈 셀인 A5 때문에 마지막 월요일은 누락됩니다. 이 문제의 해결책은 다른 수식을 조합하는 것입니다.

=OFFSET(Sheet1!$A$1;1;0;SUMPRODUCT(MAX((Sheet1!$A:$A<>"")*ROW(Sheet1!$A:$A)))-1;1)

이 수식을 사용하면 빈 셀이 있어도 A2부터 A7까지 모든 셀을 정확하게 선택할 수 있습니다.

지금까지 소개한 내용과 예제들이 업무 속도와 효율을 높이는 데 도움이 되기를 바랍니다. 동적 이름 범위는 탐색할 옵션이 무궁무진한 분야로, 여러분의 활용 능력과 창의성에 따라 매우 유용한 도구가 될 수 있습니다. 다양한 조합이 가능하니 직접 응용해 보시기 바랍니다.

실습 파일 다운로드

아래 링크에서 실습 파일을 내려받아 직접 따라 해 보세요.

함께 보면 좋은 글

• 엑셀에서 VBA로 동적 범위 활용하기 (11가지 방법)
• 엑셀 표 동적 범위를 활용한 데이터 유효성 드롭다운 목록 만들기
• 엑셀에서 동적 차트 범위 만들기 (2가지 방법)
• 엑셀에서 숫자 범위 만드는 방법 (3가지 쉬운 방법)