엑셀 50강] 이름 범위를 동적 범위로 설정하여 함수식에서 활용하기

 

동적 범위를 이름으로 정의하는 방법과 활용법에 대해 소개하겠습니다.

 

범위를 이름으로 정의해 두면 함수식을 작성할 때 편리합니다.

특히나 동적 범위로 만들면 데이터가 추가 또는 삭제가 되더라도 함수식을 수정할 필요가 없어 편리합니다.

 

실습 데이터 다운로드 ↓

이름범위를동적범위로설정.xlsx

다운로드

 

[수식]-[이름 정의] 메뉴를 실행합니다.

 

 

"단가표"라고 이름을 입력하고 참조 대상 입력창에 아래 수식을 입력합니다.

=OFFSET(동적범위설정!$I$4,0,0,COUNTA(동적범위설정!$I:$I)-1,2)

 

Offset 함수의 첫 번째 인수는 기준 셀입니다.

일반적인 경우 기준 셀이 이름을 정의할 시작 셀이 됩니다.

단가표의 첫 번째 품명 셀(I4)을 입력합니다.
두 번째 인수와 3번째 인수는 0이 됩니다.
네 번째 인수가 바로 동적 범위를 지정하는 역할을 합니다.

Counta 함수를 중첩하고 품명 열 전체($I:$I)를 인수로 지정합니다.

그럼 Counta 함수가 품명 셀의 개수를 구합니다.

거기서 1을 빼야 합니다.

'품명'이라는 필드명은 데이터의 행 개수에서 빠져야 합니다.
다섯 번째 인수는 2를 입력합니다.

'단가' 범위까지 포함해서 열 개수가 2가 됩니다.

 

 

방금 지정한 이름 '단가표'를 Vlookup 함수식에서 사용해 보겠습니다.

 

먼저 품명이 입력되면 단가가 자동으로 입력되도록 Vlookup 함수식을 작성하려고 합니다.
이때 품명이 입력되기 전에도 미리 수식을 작성했을 때 오류가 나지 않도록 Iferror 함수를 먼저 사용합니다.
Iferror 함수에 Vlookup()를 중첩합니다.

수식 입력줄에서 Vlookup 함수를 마우스로 클릭하면 함수 마법사는 Vlookup 함수 상자가 바뀝니다.

 

 

품명에 일치하는 단가를 단가표에서 찾아오는 함수식을 작성합니다.
Lookup_value 인수에는 찾으려는 값 즉, 조건 셀을 지정합니다.

'품명'이 됩니다.
Table_array 인수에 "단가표"라고 이름을 입력합니다.
Col_index_num 인수에는 2라고 입력합니다.

Table_array 인수 범위 두 번째 열이 단가 열입니다.
Range_lookup 인수에는 0을 입력합니다.

 

 

Vlookup 함수 작성이 끝나면 Iferror 함수에서 나머지 인수 Value_if_error에 0을 입력합니다.

수식을 나머지 단가 범위에 복사합니다.

 

 

맨 마지막에 품명을 추가해보겠습니다.

 

 

품명 목록을 선택하면 추가된 항목, '샤프'가 자동으로 목록에 추가되었습니다.

이미 유효성 검사 목록의 원본 범위를 동적 범위로 설정해 두었습니다.

 

 

품명 셀에 '샤프'가 입력되자 자동으로 단가가 입력됩니다.

Vlookup 함수식을 수정하지 않아도 추가된 소모품이 Table_array 범위에 자동으로 추가되었습니다.

 

 

동적 범위는 함수식에서 뿐만 아니라 피벗테이블의 원본 범위를 지정할 때도 사용하면 원본 데이터가 추가, 삭제되는 경우 데이터 범위를 매번 수정할 필요가 없어져 편리합니다.

 

동영상 강좌도 준비했습니다.  

 

https://youtu.be/d6s6ijyYMPk

 

 

이번 강좌도 필요한 분들께 도움이 되기를 바라면서 마치겠습니다.

짤막한 강좌 한정희 강사였습니다.