Home > 강좌 > 엑셀수식강좌 > 05. 엑셀 이름정의와 표기능 > 엑셀 동적 범위에 이름 정의하기

엑셀 동적 범위에 이름 정의하기

  • by

오늘은 이름으로 지정한 범위가 계속 변하는 경우에 필요한 '동적 범위에 이름을 정의'하는 방법을 알아 보겠습니다.

실습용 엑셀파일 다운로드 : 동적범위에-이름정의.xlsx

 

1. 고정된 범위에 이름 정의하기

동적 범위에 이름을 정의하기 전에 아래와 같은 시트의 고정된 범위에 이름을 정의해서 사용해 보고 어떤 문제가 있는지 확인해 보겠습니다.

고정된 범위에 이름 정의하기

 

리본 메뉴에서 [수식] 탭 → [정의된 이름] 그룹 → [이름 정의]를 누르면 [새 이름] 대화상자가 나타납니다.

[이름] 필드에 '상품정보'를 입력하고 [참조 대상]에 =Sheet1!$A$2:$C$11를 입력해서 이름을 만듭니다.

고정된 범위에 이름 정의하기

 

앞에서 정의한 이름을 이용하여 VLOOKUP함수로 상품명을 가져옵니다

=VLOOKUP(B4,상품정보,2,FALSE)

고정된 범위에 이름 정의하기

 

그런데 다음과 같이 상품정보 시트 [Sheet1]에 새로운 상품코드 'A011'이 12행에 추가되면...

고정된 범위에 이름 정의하기

'상품정보'라는 이름으로 정의된 범위 [$A$2:$C$11] 범위를 벗어나므로 VLOOKUP함수로 'A011'에 해당하는 상품정보를 찾지 못하는 문제점이 있습니다.

행이 추가되어도 이름의 범위가 자동으로 늘어나도록 할 필요가 있습니다.

고정된 범위에 이름 정의하기

 

2. 동적 범위에 이름 정의하기

위에서 문제가 된 고정된 범위 대신 이름의 참조 범위를 동적으로 정의해서 문제를 해결해 보겠습니다.

[새 이름] 대화상자에서 [이름] 필드에 '상품정보'를 입력하고 [참조 대상]에 다음 수식을 입력해서 이름을 만듭니다.

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,3)

동적 범위에 이름 정의하기

 

수식에서 OFFSET함수는 어떤 셀의 위치를 기준점으로 해서 높이와 너비만큼의 범위를 참조로 구해주는 역할을 합니다.

OFFSET함수에 대해서는 다음 링크를 참고하세요.
[엑셀함수 OFFSET - 행열이동 후 참조구하기]

OFFSET함수의 인수를 살펴보면 COUNTA함수가 핵심입니다.

  • Sheet1!$A$2,0,0 : [A2]셀에서 행방향으로 0칸, 열방향으로 0칸 이동이므로 결과적으로 이동하지 않고 [A2]셀이 기준점이 됨
  • COUNTA(Sheet1!$A:$A)-1 : COUNTA(Sheet1!$A:$A) 수식으로 A열에서 비어 있지 않은 셀의 갯수를 구해서 1을 뺌(첫행의 제목 행은 제외해야 하므로 1을 빼야 함)

결과적으로 [A2]셀을 기준점으로 해서 COUNTA함수로 A열에 입력된 행의 수에서 1을 뺀 높이 11, 너비 3(A-C열을 범위로 정해야 하므로 3칸)만큼의 범위를 구해줍니다. 행이 추가되면 COUNTA함수는 추가된 행을 포함한 갯수를 구해주므로 OFFSET함수는 항상 동적으로 범위를 참조하는 원리입니다.

동적 범위에 이름 정의하기

동적 범위에 이름 정의하기

 

이제 [C9]셀에 다음 수식을 입력하면 새로이 추가된 'A011'의 상품명을 제대로 가져오는 것을 확인할 수 있습니다.

=VLOOKUP(B4,동적상품정보,2,FALSE)

동적 범위에 이름 정의하기

 

 

[ 엑셀 수식 강좌  - 엑셀 이름정의와 표기능]

답글 남기기

이메일 주소는 공개되지 않습니다.