엑셀 다중 유효성 검사 상위 목록 선택에 따라 자동 업데이트되는 하위 목록 상자

다중+유효성+검사_대분류+소분류.xlsx

0.01MB

보통 이 기능을 다중 유효성 검사라고들 하는데, 이 단어만 뭘 하려는지 알기는 어려울 것 같습니다...

​

차리리 예시를 드는 게 이해하기 빠를 것 같습니다.

TV 제품의 매출 대장을 정리하려고 하는데... 매출 건마다 하나 하나 작성하기 힘들잖아요?

그래서 제조사 삼성/ LG 이렇게 목록에서 고르면, 그 다음 셀 목록에 해당 제조사가 만든 제품만 나오게 하는 기능입니다! 대분류/ 중분류/ 소분류 요렇게 선택한 분류의 하위 목록이 자동으로 나오게 하는 거죠!

​

자, 아래와 같은 표가 있다고 해봅시다.

그냥 제 마음대로 만든 분류입니다...

첫 번째 대분류 목록은 앞선 포스트에서 배웠던 Unique 함수를 활용하면 쉽게 만들 수 있습니다. Unique 함수에 A2:A15 를 지정해주면, 고유값이 짜잔!

그 후, 역시 전 포스트에서 배웠던, Data - 데이터 유효성검사 (Data Validation)을 선택후,

목록을 선택하고, Source 에 동적배열 시작하는 첫번째 셀 주소 뒤에 # 를 써주고 OK 를 누르면,

​

아래와 같이 첫 번째 대분류 목록이 만들어 졌습니다!!!

​

이제 대분류 목록의 선택에 따라, 다음 목록이 자동으로 나오게 할 건데요...

여기선 동적 범위를 참조해주는 Offset/ Match/ Countif 함수를 활용할 겁니다.

아마, 이 함수를 한 번에 쓰는 건 어려울 테니... Offset 과 Match 함수로 나누어 아래와 같이 표시해 두었습니다.

​

Match 함수: A2:A15 범위에서 G2 값과 정확하게 매칭되는 값이 몇 번째 행에 있는지 알려 줍니다.

Offset 함수: A2 에서 시작, "Match - 1" 만큼 행 이동, 열 1칸 이동, A2:A15 에서 G2 값 개수만큼 행 참조

예를 들어, 대분류 목록에서 Others를 선택했다면,

A2 에서 시작하여,

Match 함수 때문에 아래로 11칸 이동하고,

1을 써주었으니 오른쪽으로 1칸 이동한 B 13에서 부터,

A2:A15에 others 가 3개 있으니, B13:B15 를 참조하는 것입니다.

처음이 어렵지, 몇 번 보면 이해할 수 있습니다. 포기하지 마세요!

아, Offset 함수 작성하실 때 시작점 A2 를 절대참조 걸지 않아 오류가 가장 많이 납니다. A2 를 상대 참조로 해놓으면 나중에 이름 지정할 때 이름이 참조하는 범위가 막 바뀌게 됩니다...

​

자자, Offset 함수가 잘 작동하면, 그 밑에 여러 값들이 따라 나올 겁니다. 그러면 Offset 함수를 복사하시고

탭에서 이름 관리자 (Name Manager) 를 선택하셔도 되고, Ctr + F3 단축키로 이름 관리자에 들어가셔서

참조 범위에 Offset 함수 붙여 넣기 하시고, 적절한 이름을 지정해 줍니다..

이름이 잘 지정된 것을 확인할 수 있습니다.

그 다음, 하위 분류 목록표를 불러오고 싶은 셀을 선택한 후, 데이터 - 데이터 유효성 검사

목록 (List) 선택하시고, 아까 지정한 이름을 써주시면 됩니다.

여기서 또 가끔 짜증나는 게... 지정하 이름이 생각 안나거나, 소소하게 철자가 틀릴 경우가 있습니다...;;;

그래서 Source 클릭하시고, F3 눌러주시면, 아래와 같이 지정해 놓은 이름 리스트가 나오니 많이들 활용하세요!!!

예전에 철자 틀리고 하면 어디서 오류가 나는지 몰라서 많이 헤맸습니다... F3 F3 F3

마지막으로 OK 눌러 주시면, 아래와 같이 대분류 선택에 따라 하위 분류 목록표가 자동으로 업데이트되는 것을 확인할 수 있습니다. :) 파일도 첨부해 두었으니, 한 번 연습해보시기 바랍니다!

​

 

​