엑셀 다중 목록 쉽게 만드는 방법 (상위목록-하위목록 드롭다운, feat. indirect)

 
안녕하세요. 콩장수입니다.

지난번에는 단순하게 목록을 만드는 방법을 배워보았는데요.  오늘은 목록 안에 다시 목록을 만드는, 그러니까 다중 목록을 만드는 방법을 알려드리려고 합니다. 예컨대, 우리가 살고 있는 집 주소를 보면,  [ㅇㅇ시 ㅇㅇ구 ㅇㅇ동]으로 나오죠. ’ 도시‘명을 지정하면, 이 도시에 속하는 ‘구'명이 나오고, '구'를 선택하면 해당하는 '동'이 나오는 것처럼요. 물건을 판매하는 분들은 카테고리별로 품목을 분류해서 목록을 만들 수도 있겠네요.

엑셀 상위목록 - 하위목록 드롭다운(자동목록) 만들기

 

■ 표 만들고 이름 만들기

▷  표 만들기

저는 패스트푸드점을 운영하고 있는 사장이라고 가정해 볼게요. 엑셀로 팔고 있는 메뉴를 선택하면, 해당 메뉴의 하위 품목이 나오게끔 하려고 합니다. 그래서 아래처럼 엑셀로 만들어주었습니다. 상위 목록과 하위 목록이 서로 안 헷갈리게 하려고 상위목록은 위쪽에, 하위 목록은 아래쪽이 위치하도록 만들어주었습니다.
 

 
각각을 모두 표로 만들어주었습니다. 표로 만드는 이유는 크게 두 가지인데요. 첫째로, 항목이 추가로 생기는 경우인데요. 셀의 위치값으로 범위로 지정이 되었다면, 범위를 수정해서 다시 지정해야겠지만 , 표로 만든 상태에서 마지막에 항목을 추가로 입력하게 된다면, 표가 자동으로 확장이 되어서 새로 추가된 항목까지도 범위에 반영하게 된답니다. 둘째로, 표의 이름을 만들 수가 있어서 (정확히는 표의 범위를 이름으로 지정) 편리하게 사용할 수 있어요.


그럼 각각의 테이터를 표로 만들겠습니다. 먼저 아래 보이는 영역을 먼저 표로 만들겠습니다. 범위를 마우스로 드래그하고 상단 메뉴에서 표를 누릅니다. (아래 붉은 점선 참조)
 

 
 
그러면 다음과 같은 창이 뜨는데요. 머리글 포함을 체크한 상태에서 확인을 누릅니다.
 

 
아래처럼 표가 만들어졌지요? 그럼 다른 데이터도 방금 했던 방식대로 표로 만들어줍니다. 그럼 아래처럼 총 4개의 표가 만들어졌습니다.

 

▷  표 이름 지정하기 

다음에는 표의 이름을 지정하는 작업을 할 거예요. 표이름은 머리글과 동일한 이름으로 만들어줍니다. 예컨대, 아래 빨간색 점선처럼 메뉴에 들어가는 항목들인, '햄버거'에서 '사이드'까지 마우스로 드래그하면, 왼쪽에 ’ 표 1‘이라고 자동으로 뜹니다. 표 1을 지우고 여기에 ’ 메뉴‘라고 입력을 하는 거지요. 나머지도 동일한 방식으로 만들어주면 됩니다. 총 4개의 표에 이름을 머리글과 동일하게 만들어줍니다.

그럼 아래 화면처럼 방금 만들었던 표 이름이 추가가 되어있는 걸 볼 수 있지요. 지정한 이름을 각각 눌러보면, 그 영역이 보입니다.

 

■ Indirect 함수를 사용해서 다중 목록 드롭다운 만들기

 

이제 기초 작업은 모두 끝났습니다. 이젠 실습을 위해서 노란 박스를 만들어보았습니다. 첫 번째 칸에는 메뉴에 항목이 나오게 하려고 합니다. 이건 [데이터 - 데이터유효성검사 - 데이터유효성검사] 로 들어가시면 됩니다.

그러면 아래와 같은 창이 보이는데요. 아래 화면처럼 입력을 합니다. 제한대상에는 '목록'이라고 입력을 하고, 원본에는 '=메뉴'라고 입력합니다. '메뉴'라는 글자를 입력하는 것은 바로 아까 우리가 정한 표이름 중에 '메뉴'라고 정한 표를 가지고 오는 것이지요. 이름을 정하면, 이렇게 쉽게 한글로 입력하면 된답니다. (만약 표 이름을 정하지 않았다면, '메뉴'라는 글자 대신 참조할 셀의 범위가 들어가겠지요.) 그러면 우측하단의 그림처럼 메뉴 항목이 드롭다운의 형태로 나타납니다.

 

 

그럼, 문제는 그다음인데요. 아래 화면에 표시된 노란색 영역의 빨간색 점선 부분은 바로 앞에 위치힌 값에 따라 해당하는 하위목록이 나와야 하는 건데요. 앞에 나오는 상위목록의 값에 따라 참조하는 값이 달라집니다. 이때 사용하는 함수가 바로 indirect함수입니다. 적용하는 방법은 굉장히 단순한데요. 동일하게 [데이터 - 데이터유효성검사 - 데이터유효성검사]로 들어갑니다.

그럼 아래와 같은 창이 나오는데요. 원본에 아까 전과 달리 indirect 함수를 넣습니다. 아래 화면처럼, 바로 앞에 있는 열의 값(J5)을 참조해야 하므로, 원본에 넣을 내용은 바로 '=indirect(J5)'를 입력하는 것이지요. 

 

그럼 잘 적용이 되었나 살펴보겠습니다. 성공적입니다.

 앞에 햄버거를 넣었더니, 햄버거 목록이 쭉쭉 나오네요. 별거 아니죠? 

■ 한 단계 아래의 하위목록을 더 만든다면

 

그럼 이런 식으로 indirect함수를 사용한다면 하위 목록을 연쇄적으로 만들어낼 수 있겠네요. 좀 더 욕심을 내 볼게요! 

아래처럼 '콜라'를 선택했을 때, 하위 목록을 추가로 넣어보았습니다. 콜라 하위목록으로 ’ 제로콜라', '일반콜라', '큰 사이즈' 넣어보았습니다. 이것도 방식은 앞서한 것과 동일하답니다. 하위목록을 만들고, 표로 만든 다음, 표 이름을 만들고, 데이터 유효성검사로 위처럼 indirect 함수를  빠르게 적용해 보겠습니다. 이때 참고할 앞의 값은 이제 K5가 되겠지요?

자, 그럼 잘 적용되었나 볼까요? 음료를 입력하면 음료 종류가 나오고, 음료 중에서 콜라를 선택하면 아래처럼 3가지로 다시 분류가 되었네요. 복잡하게만 느껴진 다중 목록 드롭다운을 만드는 과정이었는데, indirect 함수 한번 적용하니 금세 만들어졌네요!

 맨 위에 한 줄에만 적용이 되었으니, 셀의 오른쪽하단을 마우스로 찍고 아래로 내리면 셀에 적용된 수식이 복사가 되지요. 

그럼 노란색 영역 모두 아래처럼 서식이 적용이 되어서 클릭만으로 메뉴 목록을 입력할 수 있게 되었네요.

머리가 아프니 ‘그냥 저는 손으로 입력할게요 ‘하시는 분들도 있을 거라고 생각합니다. 저도 예전에는 엑셀을 그냥 메모장처럼 사용을 했었습니다. 메모창처럼 입력을 하면, 내 마음은 편하지만 오타가 생길 수밖에 없거든요.  이렇게 드롭다운 목록을 만들면 인적오류를 줄일 수가 있고, 무엇보다 키보드로 단순, 반복적으로 입력하는 시간을 줄여주기도 해서 효율적으로 작업을 할 수 있답니다. 

 

부족한 제 블로그를 찾아주시고 읽어주셔서 감사합니다.  방문하시는 모든 분들에게 행복이 함께 하셨으면 좋겠네요. 더 좋은 글로 찾아뵐게요. 지난 발행글에도 혹시 도움이 될 만한 글이 있을지 모르니, 시간이 되신다면 함께 읽어주세요 ^_^