엑셀 데이터는 빈번하게 수정되기 때문에, 데이터 범위의 크기에 따라 자동으로 확장되거나 축소되는 동적 범위 설정을 활용하는 것이 매우 효과적입니다. 이제 그 방법을 자세히 알아보겠습니다.
동적 범위를 사용하면 데이터 변경 시 수식, 차트, 피벗 테이블 등의 범위를 수동으로 조정할 필요가 없어집니다. 이 모든 과정이 자동적으로 처리됩니다.
동적 범위를 생성하는 데에는 OFFSET과 INDEX라는 두 가지 함수가 주로 사용됩니다. 이 글에서는 좀 더 효율적인 INDEX 함수를 중심으로 설명하고자 합니다. OFFSET 함수는 휘발성이 있어 대용량 스프레드시트의 성능 저하를 유발할 수 있습니다.
엑셀에서 동적 정의 범위 생성하기
첫 번째 예시로, 아래와 같이 단일 열에 데이터 목록이 있다고 가정해 보겠습니다.
여기에 국가 데이터가 추가되거나 삭제될 때마다 범위가 자동으로 업데이트되도록 설정해야 합니다.
이 예에서는 헤더 셀을 제외하고자 합니다. 따라서 $A$2:$A$6 범위를 동적으로 설정할 것입니다. 이를 위해 ‘수식’ 탭에서 ‘이름 정의’를 클릭합니다.
이름 상자에 “국가”를 입력하고, “참조 대상” 상자에 아래의 수식을 삽입합니다.
=$A$2:INDEX($A:$A,COUNTA($A:$A))
때로는 위 수식을 스프레드시트 셀에 먼저 입력한 후, 새 이름 상자에 복사하는 것이 더 빠르고 간편할 수 있습니다.
작동 원리
수식의 첫 부분은 범위의 시작 셀(여기서는 A2)을 지정한 후, 범위 연산자(:)가 뒤따릅니다.
=$A$2:
범위 연산자는 INDEX 함수가 셀 값 대신 범위를 반환하게 합니다. INDEX 함수는 COUNTA 함수와 함께 사용됩니다. COUNTA는 A열에서 비어 있지 않은 셀의 개수를 계산합니다(이 예에서는 6개).
INDEX($A:$A,COUNTA($A:$A))
이 수식은 INDEX 함수에 A열에서 비어 있지 않은 마지막 셀의 범위를 반환하도록 요청합니다. 결과적으로 $A$6 셀이 반환됩니다.
최종적으로 $A$2:$A$6 범위가 생성되며, COUNTA 함수 덕분에 마지막 행을 자동으로 찾아 동적으로 설정됩니다. 이제 데이터 유효성 검사 규칙, 수식, 차트 또는 국가 이름을 참조해야 하는 모든 곳에서 이 “국가” 정의 이름을 자유롭게 활용할 수 있습니다.
양방향 동적 정의 범위 만들기
앞선 예시는 높이만 동적인 경우였습니다. 하지만 약간의 수정과 COUNTA 함수를 추가하면 높이와 너비 모두 동적인 범위를 만들 수 있습니다.
다음 예시에서는 아래 데이터를 사용해 보겠습니다.
이번에는 헤더를 포함하는 동적 정의 범위를 생성합니다. ‘수식’ 탭에서 ‘이름 정의’를 클릭합니다.
이름 상자에 “sales”를 입력하고, “참조 대상” 상자에 아래의 수식을 입력합니다.
=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
이 수식은 $A$1 셀을 시작점으로 사용합니다. 그 다음, INDEX 함수는 전체 워크시트 범위($1:$1048576)에서 데이터를 찾고 반환합니다.
여기서 두 개의 COUNTA 함수 중 하나는 비어 있지 않은 행을 계산하고, 다른 하나는 비어 있지 않은 열을 계산하여 양방향으로 동적인 범위를 만들 수 있게 합니다. 이 수식은 A1 셀에서 시작하지만 시작 셀을 필요에 따라 지정할 수 있습니다.
이제 이 정의된 이름 (“sales”)을 수식에서 사용하거나, 차트 데이터 계열로 사용하여 데이터 변화에 따라 동적으로 작동하도록 설정할 수 있습니다.