유효성 검사와 Offset 함수를 사용하여 동적 범위를 설정하는 방법에 대해서 소개하겠습니다.
실습 데이터 다운로드 ↓
| 데이터 유효성 설정
먼저 품명 셀의 목록을 클릭하여 소모품명을 선택할 수 있도록 데이터 유효성을 지정하겠습니다.
품명 범위를 블록 지정하고 [데이터]-[데이터 유효성 검사] 메뉴를 실행합니다.
제한 대상을 '목록'을 선택합니다.
단가표 품명 범위(I4~I10)를 원본 범위로 지정하고 확인을 누릅니다.
품명 셀의 목록 버튼을 클릭하면 단가표에서 지정한 품명 범위의 소모품명이 목록 내용으로 표시가 됩니다.
이렇게 하면 직접 소모품명을 입력하지 않아도 소모품명을 입력할 수 있습니다.
잘못된 품명이 입력되는 것을 제한할 수 있습니다.
단가표에 소모품을 추가합니다.
추가한 소모품이 소모품 구입현황의 품명을 선택하는 목록에 자동으로 표시되도록 하려고 합니다.
지금 상태로는 단가표에 소모품을 추가하면 매번 유효성 검사 목록의 원본 범위를 수정해야 됩니다.
| 동적 범위 설정
단가표에 소모품 추가하면 추가된 품명이 자동으로 인식되도록 Offset 함수를 사용하여 동적 범위를 설정해 보겠습니다.
품명 범위에 블록을 지정하고 [데이터]-[데이터 유효성 검사] 메뉴를 실행합니다.
기존에 설정된 목록의 원본을 지우고 =offset()이라고 입력합니다.
Offset 함수는 동적 범위, 즉 영역을 설정하는 함수입니다.
수식은 다음과 같습니다.
=OFFSET($I$4,0,0,COUNTA($I:$I)-1,1)
Offset 함수의 Reference 인수는 기본 참조 영역으로 일반적으로 범위의 시작 셀이 됩니다.
단가표의 첫 번째 소모품명 셀(I4)입니다.
Rows, Cols 인수는 첫 번째 인수에서 지정한 기본 참조 셀과 실제 범위로 지정할 셀의 간격을 지정하는 인수입니다. 여기서는 기본 참조 셀이 범위를 지정할 시작 셀이기 때문에 0을 입력합니다.
Height 인수는 범위로 지정할 행 개수가 됩니다.
이때 행 개수는 고정된 값이 아니라 소모품이 추가되면 항상 바뀌는 값이 됩니다.
그래서 Counta 함수를 중첩하여 품명 열, 즉 I 열 전체를 범위로 지정합니다.
그러면 Counta 함수가 I 열 범위에서 데이터가 입력된 셀의 개수를 셉니다.
거기에서 제목 행에 해당하는 '품명' 셀을 빼 주면 됩니다.
Counta($I:$I)-1
마지막으로 Width 인수는 기본 참조 셀에서 범위를 지정할 열 개수를 의미합니다.
품명 범위만 이름으로 지정할 것이기 때문에 1을 입력합니다.
소모품을 추가하고 품명의 목록 버튼을 누르면 추가된 항목 지우개와 건전지가 자동으로 목록에 추가되었습니다.
유효성 검사의 목록 원본 범위 뿐만 아니라 Vlookup, 피벗테이블, Index, Match 함수에서 범위를 사용하는 경우 동적 범위 설정을 사용할 수 있습니다.
데이터가 자주 추가 또는 제거 되는 경우 범위를 매번 수정하기보다는 동적 범위를 사용하면 두 번 손댈 필요가 없게 됩니다.
동영상 강좌도 준비했습니다.
익히는데 도움이 되었으면 좋겠습니다.
짤막한 강좌 한정희 강사였습니다.
'엑셀' 카테고리의 다른 글
| 엑셀 51강] 시트 보호 (0) | 2017.01.08 |
|---|---|
| 엑셀 50강] 이름 범위를 동적 범위로 설정하여 함수식에서 활용하기 (0) | 2017.01.08 |
| 엑셀 47강] Vlookup 함수와 Index, Match 함수 사용의 구분 (2) | 2017.01.08 |
| 엑셀 48강] Index, Match 함수를 활용한 다른 시트의 그림 불러오기 (10) | 2017.01.08 |
| 엑셀 46강] 예제를 통한 index와 Match함수 익히기 (1) | 2017.01.08 |