엑셀 다중 드롭다운 목록 만들기, 오류 주의

엑셀 다중 드롭다운 목록 만드는법

엑셀을 하다보면 종속적인 드롭다운 목록을 만들어야 할때가 있습니다. 저를 예로들면 설비를 장소별로 나누고싶을때, 그 나눈 설비도 공정별로 묶고 싶을때가 있었습니다. 쉬운예로들자면 다음과 같은 예시가 있습니다.

 

"대표적인 배달음식" : [ "한식" : {김치찌개, 해물파전, 된장찌개, }, "일식" : {초밥, 텐동, 오코노미야끼, 규동}, "중식" : {기스면, 자장면, 짬뽕, 울면} ]

 

배달음식에는 한식, 일식, 중식이있습니다. 그리고 한식, 일식, 중식안에서도 여러가지 음식이 존재합니다. 이러한 카테고리를 엑셀 상에서 나누어 보겠습니다. 해당 기능을 만들기위해서는 다음과 같은 목록이 필요합니다. 

 

• '이름관리자'
• INDIRECT  함수
• 데이터 유효성 검사

다중 드롭다운 만들어보기

 

다중 종속 드롭다운 목록 만들기.xlsx

0.01MB

 

 

 

먼저 한식, 일식, 중식을 이름관리자를 통해서 '배달음식' 이라는 이름으로 만들어줍니다.

[수식]-[선택 영역에서 만들기]-[왼쪽열] 로 이름만들기

[이름관리자] 에서 이름이 제대로 만들어졌는지 확인

위와 같이 셀을 선택하고나서 '수식' 선택영역에서 만들기를 누르고, '왼쪽 열'을 선택하고 '확인' 을 누른다.

각 한식, 일식, 중식 열에있는것들도 이름관리자를 통해서 이름을 만들어준다.

[수식]-[선택 영역에서 만들기]-[첫행] 선택하고 이름만들기

[이름관리자] 에서 이름이 제대로 만들어졌는지 확인

 

위와 같이 셀을 선택하고 '선택 영역에서 만들기'를 선택하고 이름을 만들어준다.

위와 같이 하고난후에 [이름관리자]를 보시면 다음과 같이 이름이 만들어진것을 확인할수있습니다. 

 

이름관리자를 보면 위와 같이 이름이 만들어진것을 볼수있다.

이제부터 데이터 유효성 검사를 통해서 드롭다운 목록을 만들어볼 차례입니다. 

먼저 목록을 만들고 싶은셀을 선택후에 [데이터]-[데이터유형검사] 를 눌러줍니다. 

 

셀하나 선택후 데이터 유형검사를 눌러준다.

그리고 난후에 ' 제한대상' 을 목록으로 선택, '원본' 부분에 한식,일식, 중식 셀을 선택해준다. 

 

드롭다운 목록 나온것 확인

그리고 해당 셀의 데이터가 바뀔때마다 해당셀 옆의 드롭다운목록이 달라지는것을 구현하려면 다음과 같이한다.

처음 상위드롭다운목록 옆에 [데이터유효성검사] 실행한다

제한대상을 목록으로 변경한다

원본에 '=indirect(상위드롭다운셀)' 을 넣어주고 '확인'을 눌러준다.

 

위와 같이 식을 넣어주고 확인을 넣어주면 다중드롭다운완성된다.

 

위와 같이 하면 구현이 가능합니다. 팁을 드리자면 위와 같이 하나의 셀만 다중 드롭다운을 하는것이 아니라 여러개의 셀을 한꺼번에 드롭다운을 만드는것도 가능합니다. 이럴때는 indirect내부의 셀의 참조를 선택을 잘해야합니다.셀의 참고를 '행'부분만 절대참고를 해주고 열에는 상대참조를해주면 아래로 복사하면 드롭다운도 그대로 복사됩니다. 

 

위와 같이 행만 절대참조, 열은 상대참조를 한다.

 

 

원본은 현재 오류 상태입니다 해결법

이 오류의 대부분은 이름관리자의 이름때문에 나타납니다.

이름에 띄워쓰기, 콤마, 앞에 숫자가 있는경우 등 여러문제가 있을수가있는데요. 이런 경우 이름관리자에서 자동 언더대쉬로 변환되거나 이름이 만들어지지 않는 경우가 있습니다. 그러므로 이러한 오류가 발생하신분은 indirect하는 부분의 이름과, 이름관리자의 이름이 정확하게 일치하는지 확인하고나서 다시 데이터 유효성 검사를 해보시길바랍니다.