엑셀 다중 선택 데이터 유효성 검사 (feat. indirect)

인터넷 사이트에서 회원가입을 하거나 할 때 정해진 선택지 내에서 고르는 형태가 있습니다.

예를 들어서 주소지를 입력할 때, "서울시"를 고르고 나면,

다음 하단 선택으로 서울시 내에 있는 "강남구, 강동구, 강북구 등"의 선택지에서 고르게끔 되어있습니다.

마찬가지로 최초 선택지에서 "경기도"를 고르고 나면,

하단 선택에서 다른 지역은 뜨지않고 경기도 내 구역인 "광명시, 구리시, 안산시 등"의 선택지도 바뀌어져 있습니다.

 

이와 같이, 앞선 선택에 따라서 다음 선택되는 목록이 변경되어, 정해진 다음 선택지를 고를 수 있게끔 엑셀의 데이터 유효성 검사 기능을 사용하면 가능합니다.

 

---

예시로 아래의 회사 내 조직도를 기준으로, 각 직원들의 소속을 고르는 목록을 만들어 보겠습니다.

다중 선택 유효성검사.xlsx

0.01MB

 

 

우선, 직군별로 관리직과 영업직으로 구분되며,

다음 레벨에서 관리직은 총무부 / 재경부 / 기획부로 나뉘며, 영업직은 국내영업부 / 해외영업부 / 신사업부로 구분됩니다.

마지막 레벨에서 각각의 부서별로 하단에 팀 구성도 되어 있습니다.

 

전체 순서는

1. 각 레벨에 따라 선택할 목록을 지정합니다.

2. 데이터 유효성검사 기능을 통해, 상위 레벨의 선택에 따라서 하단의 선택지 목록을 지정해줍니다.

   → 이때 indirect 함수를 사용합니다.

 

STEP 1. 각 레벨(선택)의 구성 목록 만들기

아래 그림과 같이 앞선 조직도를 선택 목록별로 구분하여 정리 및 나열 합니다.

 

 

STEP 2. 각 목록별로 [이름 정의] 하기. (상단 메뉴 중 [수식] > [이름 정의] 클릭)

나열한 목록별로 이름을 지정하여 저장합니다.

 

이름 정의할 때 주의하실 점은, 하단 레벨의 이름은 상단 레벨의 목록이어야 한다는 것입니다.

예를 들어, '총무부, 재경부, 기획부'의 리스트 이름은 상단 레벨의 목록중에 있는 "관리직"으로 이름 정의하셔야 합니다.

 

이렇게 모두 [이름 정의]가 끝나고 나면, [이름 관리자]를 클릭하여 모두 정의 되었는지 확인합니다.

여기까지 하셨다면 모든 준비는 완료하였습니다.

 

STEP 3. 데이터 유효성검사, 첫번째 레벨

첫번째 레벨에서의 데이터 유효성검사는 익히 알고 있는대로 입력해주면 됩니다.

ㅇ 제한 대상 : '목록' 선택

ㅇ 원본 : '=직군' 입력 (→ 앞서 "직군"이라는 목록을 이름 정의하였기 때문에, 해당 "직군" 이라는 목록이 선택지로 정해집니다.)

 

이렇게 해주면 위 사진처럼 첫번째 레벨에서는 "직군"으로 지정해준, 관리직과 영업직 중 택 1을 할수 있도록 리스트가 나옵니다.

 

STEP 4. 데이터 유효성검사, 두번째/세번째 레벨 indirect 함수 사용하기

첫번째 레벨은 아무런 영향을 주는 항목없이 무조건 "관리직"과 "영업직"이 리스트로 나오게 되는 반면에

두번째, 세번째 레벨에서는 앞선 레벨의 선택에 따라서 리스트가 변경되어야 합니다.

 

이를 위해서 데이터 유효성감사 '원본' 항목에 indirect 함수를 사용하여, 앞선 레벨의 선택에 따라서 선택할 리스트가 변동될 수 있도록 해줍니다.

 

 

ㅇ 제한 대상 : '목록' 선택

ㅇ 원본 : '=indirect(왼쪽셀)' 입력

 

왼쪽셀(상위 레벨) 값으로 들어있는 이름으로 지정된 리스트를 가져오도록 명령하게끔 됩니다.

따라서, 직군에서 "관리직"을 선택했다면, 그다음 레벨인 부서에서는 "관리직"으로 이름이 정의된 리스트,

즉, 총무부, 재경부, 기획부 리스트를 불러오게 됩니다.

 

 

또 만약 직군에서 "영업직"으로 선택하였다면, 부서 목록은 "영업직"으로 정의된 리스트인 국내영업부, 해외영업부, 신사업부 리스트를 불러오게 됩니다.

 

 

세번째 레벨에서도 동일하게 indirect 함수를 사용하여 왼쪽셀을 참조로 걸면, 두번째 레벨에서 선택한 기준으로 하단 리스트를 불러오게 됩니다.

 

 

이렇게 데이터 유효성검사와 indirect 함수를 사용하여 세번째 레벨까지 앞선 선택에 따른 목록이 지정될 수 있도록 해보았습니다. 생각보다 어렵지 않았을 듯 싶습니다. 물론, 동일한 방식으로 목록을 더 만들어가면 더 많은 레벨의 다중 선택도 가능하겠죠? 실생활에 맞춰서 한번 적용해보세요.

 

Fin.