엑셀 실무] INDIRECT와 표 서식을 활용하여 동적 범위를 인식하는 이중 유효성 검사 목록 만들기

앞서 소개한 이중 유효성 검사 목록 만들기를 동적 범위를 설정하여 목록이 추가되거나 삭제된 목록 범위를 자동으로 인식하도록 변경해봤습니다. 

이중 유효성 검사 목록 만들기(데이터 유효성 검사, INDIRECT 함수)

 

결과물을 다시 정리해보겠습니다. 

물품종류와 세부종류에 유효성 검사 목록을 설정합니다.

물품종류 목록 버튼을 누르면 마른, 냉장, 냉동, 하드웨어가 나타나고 그중 냉장을 선택하면 냉장에 해당하는 세부종류가 나타나도록 합니다. 

 

 

실습 파일 다운로드

이중유효성검사_실습.xlsx

0.01MB

완성 파일 다운로드

이중유효성검사_완성.xlsx

0.01MB

 

실습 파일을 열어 소개하겠습니다.

먼저 [거래내역] 시트에 '거래일자'에서 '금액'까지 필드명을 입력합니다.

 

 

그리고 [자료] 시트는 [거래내역] 시트에서 물품종류, 세부종류의 유효성검사 목록의 원본으로 사용할 데이터를 정리해 둡니다.

첫 번째 행에 물품종류를 입력하고 두 번째 행부터 세부종류를 입력합니다.

 

 

이름 정의

 

유효성 검사 목록의 원본 범위는 이름으로 정의해 놓고 사용하겠습니다.

먼저 물품종류 목록 버튼을 눌러 나타낼 범위를 이름 정의하겠습니다.

[A1:D1] 셀 범위를 선택하고 이름 상자에 '물품종류'라고 입력한 후 <Enter>를 누릅니다.

 

 

이번에는 물품종류를 마른으로 선택했을 때 세부종류 목록에 나타낼 범위를 이름으로 정의하겠습니다.

[A2:A5] 셀 범위를 선택한 뒤 이름 상자에 [A1] 셀 내용인 '마른'을 입력하고 <Enter>를 누릅니다.

 

 

유효성 검사 목록

이제 거래내역 시트로 가서 유효성을 설정해보겠습니다.

데이터 유효성 검사를 설정하려면 설정할 셀 범위를 먼저 선택하고 [데이터] 탭 - [데이터 유효성 검사] 메뉴를 선택해야 합니다.

하지만 아래 그림처럼 거래가 21건만 일어나는 것은 아니겠죠?

거래 건수가 몇 번 일어나는지 알 수 없기 때문에 대략 1000행까지로 정하겠습니다.

 

1000행 범위를 마우스로 드래그해 선택하는 것은 쉽지 않습니다.

이경우 이름 상자를 사용하면 됩니다.

이름 상자에 [B2:B1000]을 입력하고 <Enter>를 누릅니다.

빠르게 범위가 선택되었죠?

 

범위가 지정되면 [데이터] 탭 - [데이터 유효성 검사]를 선택합니다.

 

[제한 대상]은 '목록'을 선택하고, [원본] 입력창에 =물품종류라고 수식을 작성하고 [확인]을 누릅니다.

 

물품종류 목록 버튼을 눌러보면 '마른', '냉장', '냉동', '하드웨어'가 나타납니다.

 

 

이제 물품종류에서 '마른'을 선택하면 세부종류에 마른에 해당하는 '냉면', '과자', '라면', '소스'가 나타나도록 설정해보겠습니다.

세부종류 범위도 C2:C1000 셀 범위에 유효성 검사 목록을 설정하겠습니다.

이름 상자에 [C2:C1000]을 입력하고 <Enter>를 누른 다음 [데이터] 탭 - [데이터 유효성 검사]를 선택합니다.

그런 다음 [제한 대상] - '목록'을 선택합니다.

여기서 원본은 INDIRECT 함수를 사용할거예요.

INDIRECT 함수는 이름으로 정의한 범위의 값을 가져오는 함수입니다.

먼저 원본 입력창에 =indirect(b2)를 입력하고 [확인]을 누릅니다.

 

 

세부종류 목록 버튼을 눌러보겠습니다.

어떤가요? 물품종류 '마른'에 해당하는 세부종류가 나타나죠?

INDIRECT 함수로 인해 [B2] 셀에 입력된 마른의 정의된 이름 범위를 원본으로 가져왔기 때문입니다.

 

 

이제 나머지 물품종류도 선택하면 해당되는 세부종류가 목록에 나타나도록 설정하면 됩니다.

하지만 여기서 고려해야할 점이 있습니다.

현재 '마른'으로 정의한 범위는 냉면에서 소스가 입력된 범위까지만 인식하는 고정 범위입니다.

세부종류가 추가되거나 삭제되더라도 자동으로 정의된 이름 범위가 변하도록 동적 범위로 설정해줘야 합니다.

현재는 고정 범위로 설정되어 있다보니 '쌀국수'를 추가했지만 세부종류 목록에는 쌀국수가 포함되지 않았죠.

 

 

어떻게 해야 할까요? 쉽고 간단한 방법이 있습니다!

바로 표 서식을 활용하면 됩니다.

 

표 서식을 사용하여 간단하게 동적 범위로 설정하기

 

[자료] 시트 [A1:A6] 셀 범위를 선택하고 [홈] 탭 - [스타일] 그룹 - [표 서식]을 선택하고 서식 아무거나 하나를 선택합니다.

 

 

표 서식 대화상자가 실행되면 [머리글 포함]에 체크를 한 후 [확인]을 누릅니다.

 

 

그리고 표 서식을 적용한 범위를 선택하면 [표 디자인] 탭이 표시되는데 그중 [표 이름]을 '마른'으로 변경합니다.

 

 

세부종류 목록의 원본에서 작성한 INDIRECT 함수 인수가 입력받는 값이 [B2] 셀의 값이기 때문입니다.

 

 

물품 종류가 '마른'이 선택되어 있는 상태에서 세부종류 목록 버튼을 눌러보겠습니다.

어떤가요? 마른에 해당하는 세부종류만 목록에 표시되죠?

 

 

그럼 세부종류를 추가해 목록에 표시되는지도 확인해보겠습니다.

[자료] 시트 [A7] 셀에 '당면'을 입력하고 <Enter>를 누르면 당면은 자동으로 표 서식 범위에 추가됩니다.

표 서식을 활용하면 동적 범위 설정이 너무 쉬워지죠?

 

 

그럼 [거래내역] 시트로 가서 세부종류를 확인해봅시다.

'당면'이 추가되었죠?

 

 

다시 [자료] 시트로 가서 냉장 범위도 표 서식으로 설정해보겠습니다.

[B1:B5] 셀 범위를 선택하고 [홈] 탭 - [스타일] 그룹 - [표 서식]을 선택하고 표 서식 종류 아무거나 선택한 후 [표 서식] 대화상자가 실행되면 '머리글 포함' 옵션을 선택합니다.

 

 

[표 디자인] 탭에서 [표 이름]을 '냉장'으로 변경하고 <Enter>를 누르면 됩니다.

 

 

다시 [거래내역] 시트로 가서 물품종류 '냉장'을 선택합니다.

그리고 세부종류 목록 버튼을 누르면 냉장에 해당하는 세부종류가 나타납니다.

 

 

[자료] 시트로 가서 나머지 냉동, 하드웨어 범위도 표 서식으로 설정하면 됩니다.

​

이번 강좌에서 이중 유효성 검사 목록을 만들어봤는데요, 필요하신 많은 분들과 공유합니다.

도움이 되길 바랍니다.