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 함수를 사용하면 높이와 너비에 따라 동적 범위를 만들 수 있습니다.
이 예에서는 아래 표시된 데이터를 사용합니다.
이번에는 헤더를 포함하여 동적으로 정의 된 범위를 만듭니다. 수식> 이름 정의를 클릭하십시오.
“이름”상자에“”판매”를 입력하고“참조”상자에 아래 공식을 입력하십시오.
=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
이 수식은 $ A $ 1을 시작 셀로 사용합니다. INDEX 함수는 전체 워크 시트 ($ 1 : $ 1048576)의 범위를 사용하여보고 돌아옵니다.
COUNTA 함수 중 하나는 공백이 아닌 행을 계산하는 데 사용되고 다른 하나는 공백이 아닌 열에 사용되어 양방향으로 동적입니다. 이 공식은 A1에서 시작되었지만 시작 셀을 지정할 수 있습니다.
이제이 정의 된 이름 (판매)을 공식 또는 차트 데이터 계열로 사용하여 동적으로 만들 수 있습니다.