Excel에서 동적 정의 범위를 만드는 방법

Excel 데이터는 자주 변경되므로 데이터 범위의 크기에 따라 자동으로 확장 및 축소되는 동적 정의 범위를 만드는 것이 유용합니다. 방법을 봅시다.

동적 정의 범위를 사용하면 데이터가 변경될 때 수식, 차트 및 피벗 테이블의 범위를 수동으로 편집할 필요가 없습니다. 이것은 자동으로 발생합니다.

동적 범위를 생성하는 데 두 가지 공식이 사용됩니다: OFFSET 및 INDEX. 이 기사에서는 INDEX 함수가 보다 효율적인 접근 방식이므로 사용하는 데 중점을 둘 것입니다. OFFSET은 휘발성 함수이며 큰 스프레드시트의 속도를 늦출 수 있습니다.

Excel에서 동적 정의 범위 만들기

첫 번째 예의 경우 아래에 표시된 단일 열 데이터 목록이 있습니다.

더 많은 국가가 추가되거나 제거되면 범위가 자동으로 업데이트되도록 동적이어야 합니다.

이 예에서는 헤더 셀을 피하고 싶습니다. 따라서 $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에서 시작했지만 시작 셀을 지정할 수 있습니다.

이제 이 정의된 이름(판매)을 수식에서 사용하거나 차트 데이터 시리즈로 사용하여 동적으로 만들 수 있습니다.