[Excel] 조회 및 참조함수 Indirect 를 이용한 이중유효성 검사

이전 포스팅에서 데이터 유효성 검사의 개념 및 사용목적과 사용방법에 대해 설명드렸습니다. 셀에 데이터 입력시 데이터의 종류와 입력범위나 숫자의 크기나 텍스트의 길이 등을 제한하여 반복적인 데이터 입력을 간편하게 하고 입력시 발생할 수 있는 오타나 누락을 방지할 수 있는 장점이 있습니다.

오늘은 이 데이터 유효성 검사를 응용하는 방법에 대해 얘기하겠습니다. 입력된 항목에 따라 그 항목에 대한 세부항목이 연동되는데, 유효성 검사를 연동하여 연속으로 사용한다고 하여 이중 유효성 검사하고들 합니다.

이중 유효성 검사는 이름정의와 INDIRECT 라는 함수를 사용하여 설명을 드릴텐데요. 유효성 검사를 처음 접하시는 분이시거나 이름정의에 대해 아직 헷갈리신다면, 이전 포스팅을 빠른 속도로 한번 보고 오시는걸 권해드립니다.^^

가계부의 수입과 지출 항목

입력 항목

가계부의 수입과 지출 항목을 가지고 설명을 드리겠습니다. 아래 그림은 간단하게 작성해본 가계부의 수입 및 지출항목과 지출관련 세부항목입니다.

위의 목록을 이용해 아래와 같은 수입, 지출 내역을 작성합니다. 만약, 데이터 유효성 검사를 이용하지 않는다면 아래와 같은 내역을 작성하기 위해서 매번 항목을 직접 입력해야 하고 같은 종류의 지출이나 그 항목이름이 한글자라도 다르게 입력된다면 합계를 계산하기가 복잡하고 까다로워지게 됩니다.

혹시, 신년을 맞이하면서 새로운 기분으로 가계부를 만들어보고자 하신다면 꼭 이중 유효성 검사를 활용하여 보시기 바랍니다.^^

아래 동영상을 보시면, 왜 이중 유효성 검사를 사용하는지 이해하실수 있습니다.

이중 유효성 검사 만들기

이름정의

데이터 유효성 검사를 만드는 방법 3가지를 설명드렸었는데요. 여기서는 이름정의를 사용하는 방법을 이용합니다. 아래와 같이 사용될 목록을 별도의 시트에 정리합니다.(아래처럼 추후 추가될 항목을 고려하여 여유있게 범위를 지정합니다.) 각각의 항목그룹의 범위를 선택한 후 이름정의를 지정합니다. 저는 각 항목명과 동일하게 이름정의를 했습니다.

유효성 검사

가장 처음 입력해야할 항목(대항목)은 유효성 검사를 지정하는 것과 동일한 방법으로 하시면 됩니다.

 

이중 유효성 검사

두번째 유효성 검사를 지정하기 위해서는 아래 그림처럼 INDIRECT함수를 사용합니다. 유의해야 할 것은 소항목에 입력될 항목들의 항목명이 대항목에 입력된 항목(지출)과 동일해야 합니다.

INDIRECT함수에 입력된 "B3"는 B3셀을 참조하는 것이 아니라 B3에 입력된 "지출"(이름정의가 되어 있는 셀 범위)을 참조하고 있기 때문입니다.

위와 같은 방법을 사용하면, 그 단계가 많더라도 상위목록에 연계되어 하위목록이 자동으로 콤보박스 형태로 입력할 수 있도록 만들수 있습니다.

이해가 어려우시다면 아래 동영상을 참고하시기 바랍니다.

이상입니다.