강좌 > 엑셀중급강좌 > 엑셀 이름과 표 > 엑셀 동적 범위에 이름 정의하기

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

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

 

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

실습: [상품정보] 시트, [고정범위참조] 시트

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

고정된 범위에 이름 정의하기 - 참조 대상 시트

 

01 ① [상품정보] 시트에서 A2:C11 범위를 선택하고

 

② [수식] 탭 → [정의된 이름] 그룹 → [이름 정의]를 눌러 [새 이름] 대화 상자를 실행합니다.

 

02 ① [이름] 필드에 '상품'을 입력하고 ② [확인]을 눌러 이름을 만듭니다.

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

 

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

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

이름을 참조하여 상품명 가져오기

 

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

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

 

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

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

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

 

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

실습: [상품정보] 시트, [동적범위참조] 시트

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

① [수식] 탭 → [정의된 이름] 그룹 → [이름 정의]를 눌러 [새 이름] 대화상자를 실행합니다. 대화상자에서 ② [이름] 필드에 '동적범위상품'을 입력하고 ③  [참조 대상]에 다음 수식을 입력한 후 [확인]을 눌러서 이름을 만듭니다.

=OFFSET(상품정보!$A$2,0,0,COUNTA(상품정보!$A:$A)-1,3)

동적 범위에 이름 정의하기

 

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

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

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

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

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

동적 범위에 이름 정의하기

 

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

=VLOOKUP(B9,동적범위상품,2,FALSE)

동적범위로 만든 이름을 참조하여 상품명 가져오기

 

 


엑셀 수식 강좌 

[ 수식 다루기 ]

[ 엑셀에서 입력과 표시방법 ]

[ 수식에서 셀과 범위 참조 방식 ]

[ 엑셀을 제대로 쓰는 데이터처리 ]

[ 엑셀 이름정의와 표기능 ]

[ 수식을 분석하고 오류 해결하기 ]

 

엑셀웍스 책 출간 안내

그동안 엑셀웍스에 올린 '엑셀 강좌', '함수 사용법', 여러 '실무 예제'에서 핵심만 뽑아 '된다! 엑셀 수식 & 함수' 책으로 출간하였습니다.

엑셀웍스 책 출간안내  자세히 보기

온라인 구매처: 예스24   교보문고   알라딘 

댓글 남기기

Scroll to Top