오늘은 이름으로 지정한 범위가 계속 변하는 경우에 필요한 '동적 범위에 이름을 정의'하는 방법을 알아 보겠습니다.
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)
엑셀 수식 강좌
[ 수식 다루기 ]
[ 엑셀에서 입력과 표시방법 ]
[ 수식에서 셀과 범위 참조 방식 ]
[ 엑셀을 제대로 쓰는 데이터처리 ]
- 엑셀에서 잘못된 날짜, 숫자 찾고 변환하기
- 수식으로 한 번에 텍스트 나누기
- 엑셀 고급필터로 다양하게 자료 필터링하기
- 정렬의 또 다른 방법, 사용자 지정목록/색으로 정렬
- 엑셀 오류데이터 처리하기
- 파워 쿼리(Power Query) 소개
- 파워쿼리로 외부 데이터 가져오기
- 엑셀에서 중복 데이터 제거하기
- 엑셀 텍스트 나누기의 모든 것
- 이동옵션으로 원하는 셀 한번에 선택하고 처리하기
- 데이터 유효성 검사로 상위목록에 종속되는 하위목록 만들기
- 사용자 지정 수식으로 데이터 유효성 검사하기
- 데이터 유효성 검사로 허용된 데이터만 입력
- 엑셀 데이터 다루기 첫 단추는 데이터 구조화
[ 엑셀 이름정의와 표기능 ]
[ 수식을 분석하고 오류 해결하기 ]
- 엑셀 순환참조 문제 해결하기
- 수식 계산 단계 알아보기
- 엑셀 수식 오류 검사하고 추적하기
- 엑셀에서 참조 셀 추적하기
- 엑셀 오류 유형(#DIV/0!, #N/A, #NAME?, #REF! 등)과 해결방법
