안녕하세요, 오늘은 드롭박스 드롭다운 만드는 방법에 대해서 알아보겠습니다. 요 근래 제 엑셀 방에서 한 번씩 드롭박스 혹은 드롭다운 만드는 방법에 대해서 여쭤보시는 분들이 많더라고요. 실무에서도 많이 쓰이고 원하는 백데이터만 있으면 드롭박스 드롭다운을 만들어서 원하는 내용만 쏙쏙 뽑아서 함수를 짤 수도 있고, 방대한 데이터를 다룰 때 드롭박스 드롭다운은 항상 많이 쓰이는 거 같습니다. filter함수, sort함수랑도 많이 쓰이기도 합니다. 복잡한 함수를 몰라도 따라오시면 드롭박스 드롭다운 자동화를 누구나 다 하실 수 있을 겁니다. 시작 전에 엑셀 2021 버전 이상 혹은 엑셀 365 유저들만 함수들이 적용되니까, 참고 부탁드립니다.
위 gif와 같이 드롭다운 드롭박스를 만들어서 함숫값을 참조해서 방대한 데이터를 가져올 수도 있습니다.
카테고리 나누기
필요한 함수들
Sort함수
Unique함수
Filter함수
Isnumber함수
Search함수
요 위의 함수 5개와 데이터 유효성 검사만 활용하면 드롭박스 드롭다운을 만들 수 있습니다. 천천히 한번 알아보겠습니다.
Search함수 =SEARCH(L1, I2:I16)
먼저 Search함수로 빈셀 (입력할 셀)을 find_text에 넣어주시고 범위를 I2:원하시는 범위까지 지정합니다. I100까지 하셔도 되고 따 맞게 하셔도 상관없습니다. 두 번째 gif를 보시면 L1의 텍스트를 찾아서 숫자 값으로 반환을 해주는 걸 보여드리기 위해서 gif 1-2를 만들었습니다. Search함수의 동작 원리를 이해하셔야 합니다. 숫자를 반환합니다. Search함수는 와일드카드(특수문자)까지 다 찾을 수 있습니다. 그와 반대인 find함수는 숫자, 텍스트만 find_text값이 참조 가능합니다.
Isnumber함수 =ISNUMBER(SEARCH(L1, I2:I16))
Isnumber함수는 숫자의 값을 True값으로 돌려주는 함수입니다. 그 외 Istext , Isblank와 같은 텍스트이면 True값을 빈 셀이면 True값을 반환하는 함수들도 있습니다. 과일 4개와 자동차 4개 행 총 8개 행까지는 Search함수로 숫자를 반환했기에 Isnumber 함수를 씌우면 8개 행이 True값이 되는 걸 확인할 수 있습니다.
Filter함수 =FILTER(I2:I16,ISNUMBER(SEARCH(L1,I2:I16)),"")
Filter함수는 다음에 찐하게 하고 이번 드롭다운 드롭박스 편에서는 간단히만 설명드리면 조건 ISNUMBER(SEARCH(L1, I2:I16))을 바탕으로 데이터를 필터링하는 함수라 생각하시면 됩니다. "포함" 인수의 조건으로 "배열" 인수 범위를 필터링하는 함수입니다. 여기서는 True값이 원하는 조건이니까 True값을 기준으로 배열을 필터 했다고 생각하시면 됩니다. (자세한 건 filter함수 후속 편에서 다루겠습니다) , ""을 넣은 이유는 마지막 인수는 if_empty 인수로 만약 빈 셀이면 어떤 값을 넣을래?라는 인수이므로 "" 빈셀을 넣겠다고 적어놓은 것입니다. "결괏값 없음"이라는 텍스트를 넣으셔도 되고 원하시는 데로 텍스트 값을 넣으시면 됩니다.
Unique함수 =UNIQUE(FILTER(I2:I16,ISNUMBER(SEARCH(L1,I2:I16)),""))
Unique함수는 범위 혹은 배열의 고윳값을 반환하는 함수입니다. 다시 말해 중복 값을 제거하는 함수라고 생각하시면 됩니다. 위의 GIF처럼 과일 4개와 자동차 4개의 중복 값은 과일 1개와 자동차 1개 이므로 2개의 행만 가져오게 됩니다.
Sort함수 =SORT(UNIQUE(FILTER(I2:I16,ISNUMBER(SEARCH(L1,I2:I16)),"")))
Sort함수는 오름차순으로 정렬해주는 함수입니다. ㄱ이 먼저 오기 때문에 과일 자동차 순서로 정렬이 되었습니다. 굳이 Sort함수를 안 쓰셔도 됩니다. 이렇게까지 1차 세팅을 해주시면 첫 번째 드롭박스 드롭다운을 만드실 수 있습니다.
여기서 천천히 잘 따라오셔야 합니다!
일단 B1과 C1 셀에 대 카테고리와 소 카테고리를 입력을 해놓았습니다.
B2셀에 놓고 데이터 유효성 검사 (alt+a+v+v 단축키)를 켜줍니다. 그럼 설정란 탭에 제한 대상에서 목록을 선택해주고 원본 탭에서 아까 만들어놓은 함수의 셀을 참조를 해줍니다. 위의 gif에서는 =$L$2가 되겠네요. 여기서 #을 꼭 넣어주셔야 합니다. =$L$2# 이 되겠네요. 그럼 첫 번째 드롭다운 드롭박스가 완성이 되었습니다.
두 번째 드롭박스 드롭다운인 소카테고리를 만들기 위해서 Filter함수를 써줍니다. 범위는 사과~bmw가 있는 열을 참조해주고, 포함에서는 대 카테고리 (드롭다운, 드롭박스)가 적용된 셀이 과일과 자동차가 있는 열에 있는 값을 필터링하기 때문에 과일은 사과~bmw 값들 중에 사과, 오렌지, 수박, 포도 값만 가져오게 됩니다. 두 번째 gif를 보시면 자동차로 바꾸었을 때 현대, 기아, 쉐보레, bmw값을 가져오는 것을 보실 수 있습니다. 여기서 소카 테 고리에 대 카테고리에 했던 데이터 유효성 목록을 만들어주면 모든 과정이 끝납니다.
대 카테고리에 했던 방법과 똑같습니다. 데이터 유효성에서 원본에 만들어 놓았던 배열 함수 셀을 참조하고 "#"을 넣어주면 됩니다. 오류 메시지는 안 뜨게끔 처리를 해야 하기 때문에 오류 메시지 무시 체크를 풀어주시면 되겠습니다.
드롭박스 드롭다운 만들기가 완성이 되었습니다. 이런 식으로 대, 중, 소 카테고리로 여러 개 만들 수도 있으며, 그에 맞는 함수를 넣어서 b2, c2... 드롭다운을 참조해서 함숫값을 만들 수도 있습니다. 이까지 천천히 따라오셨으면 드롭박스 드롭다운 만드는 건 여러 번 해보시면 잘 만드실 거라 생각합니다. 포스팅이 유익했으면 하트 부탁드리겠습니다 ( :
엑셀 문의 환영합니다.
https://open.kakao.com/o/sj8kxmde
엑셀공부즁님의 오픈프로필
#엑셀
open.kakao.com
'엑셀공부 > 엑셀 하루에 하나씩' 카테고리의 다른 글
| 엑셀 SUMIFS 함수 : 합계 더하기 2개 이상 복수 조건 사용법 (1) | 2022.05.21 |
|---|---|
| 엑셀 합계 수식 엑셀 더하기 수식 SUM (0) | 2022.05.19 |
| 엑셀 실무 단축키 열 병합 셀 병합 단축키 20초컷 엑셀 셀 서식하세요 (0) | 2022.05.13 |
| 엑셀 셀 병합 단축키 직장인 실무 셀 병합 하지마세요 셀 서식 하세요 (1) | 2022.05.12 |
| 엑셀 MROUND 함수 특정 배수에 맞게 반올림 혹은 반내림 (0) | 2022.05.10 |
