드롭다운 목록 필터는 기본적으로 중복 없는 고유 항목들의 목록입니다. 드롭다운 목록에서 특정 항목을 선택하면, 그 선택에 해당하는 데이터만 표 형태로 나타납니다. 이 글에서는 엑셀에서 셀 값을 기반으로 드롭다운 목록 필터를 만드는 방법을 단계별로 자세히 살펴보겠습니다.
아래 링크에서 엑셀 파일을 다운로드하여 직접 따라 하며 연습해 볼 수 있습니다.
엑셀에서 셀 값 기반 드롭다운 목록 필터를 만드는 단계
1단계: 드롭다운 목록용 고유 목록 만들기
드롭다운 목록 필터를 만들려면 먼저 고유 항목 목록을 작성해야 합니다. 이 목록을 기준으로 나머지 작업을 진행할 수 있으므로, 먼저 중복 없는 고유 항목 목록부터 만들어 보겠습니다.
❶ 먼저 데이터 표에서 항목들을 복사합니다. 여기서는 데이터 표의 Category(분류) 열에 있는 항목들을 분리했습니다.
❷ 고유 목록을 만들 데이터 범위를 선택한 후 데이터 > 중복된 항목 제거 메뉴로 이동합니다.
❸ 중복된 항목 제거 대화상자가 나타나면 설정이 올바른지 확인하고 확인(OK) 버튼을 클릭합니다.
이렇게 하면 중복이 제거된 고유 항목 목록이 완성됩니다. 이제 드롭다운 목록 필터를 추가해 보겠습니다.
❹ 원하는 셀을 선택한 후 데이터 > 데이터 유효성 검사 > 데이터 유효성 검사 메뉴로 이동합니다.
그러면 데이터 유효성 대화상자가 나타납니다.
❺ 설정 탭에서 제한 대상 상자에서 목록을 선택합니다.
❻ 원본(Source) 상자에 아까 만든 고유 항목 목록의 셀 범위를 입력합니다.
❼ 확인(OK) 버튼을 클릭합니다.
마침내 아래 그림과 같이 엑셀에서 드롭다운 목록 필터가 완성됩니다.
함께 읽으면 좋은 글: 엑셀에서 고유 값으로 드롭다운 목록 만드는 4가지 방법
2단계: 드롭다운 목록 필터 작동시키기
드롭다운 목록 필터를 추가했으니, 이제 이 필터를 사용해 기존 데이터 표에서 데이터를 걸러내는 작업을 진행해야 합니다.
이를 위해서는 기존 데이터 표 옆에 3개의 보조 열(helper column)을 추가해야 합니다. 각각 Row SL(행 번호), Matched(일치), Ordered(정렬)라고 명명했습니다.
첫 번째 보조 열: Row SL
이 열에는 데이터 표의 각 행에 대한 일련번호를 저장합니다.
❶ 셀 F5에 아래 수식을 입력합니다.
=ROWS($E$5:E5)
ROWS 함수의 인수는 배열입니다.
- $E$5는 Row SL 열의 첫 번째 셀입니다. F4 키를 누르면 달러($) 기호가 추가되어 셀 주소가 고정됩니다.
- E5 역시 Row SL 열의 첫 번째 셀입니다.
즉, 이 수식은 셀 $E$5에서 E5까지의 행 개수 차이를 계산합니다. 채우기 핸들을 셀 F5에서 F12까지 끌면 $E$5는 고정되지만 E5는 점점 변경되고, 두 셀 주소 사이의 거리가 계속 늘어납니다. 결과적으로 데이터 표의 행 일련번호를 얻게 됩니다.
💡 참고: 원한다면 일련번호를 직접 입력할 수도 있습니다.
❷ Enter 키를 눌러 수식을 실행합니다.
❸ 채우기 핸들을 셀 F5에서 F12까지 드래그합니다.
두 번째 보조 열: Matched
이 열에서는 드롭다운 목록 필터가 있는 셀 K4에서 선택한 항목과 일치하는 행의 일련번호만 반환합니다.
❶ 셀 G5에 아래 수식을 입력합니다.
=IF(B5=$K$4,F5,"")
수식 구성 요소는 다음과 같습니다.
- B5: 드롭다운 목록 필터에서 선택한 항목과 비교할 첫 번째 항목의 셀 주소
- $K$4: 드롭다운 목록 필터가 위치한 셀 주소
- F5: B5와 $K$4가 일치할 경우 반환할 값의 셀 주소
- "": B5와 $K$4가 일치하지 않을 경우 빈 칸을 반환하기 위한 값
❷ Enter 키를 누릅니다.
❸ 채우기 핸들을 셀 G5에서 G12까지 드래그합니다.
세 번째 보조 열: Ordered
두 번째 보조 열인 Matched에서는 행 번호가 연속적으로 나오지 않을 수 있습니다. 행 번호가 하나씩 순서대로 배치되도록 하려면 Ordered 열이 필요합니다.
❶ 셀 H5에 아래 수식을 입력합니다.
=IFERROR(SMALL($G$5:$G$12,F5),"")
- $G$5:$G$12: SMALL 함수가 가장 작은 숫자를 찾을 셀 범위
- F5: SMALL 함수가 숫자를 순차적으로 찾도록 도와주는 값. 1부터 시작하며 채워질 때마다 1씩 증가합니다.
- "": SMALL 함수가 찾는 값이 더 이상 없어 오류가 발생할 경우 IFERROR 함수를 통해 셀을 비워 두는 역할
❷ Enter 키를 눌러 수식을 실행합니다.
❸ 마지막으로 채우기 핸들을 셀 H5에서 H12까지 드래그합니다.
이것으로 보조 열 작업이 모두 끝났습니다.
함께 읽으면 좋은 글: 엑셀에서 필터 기능이 포함된 드롭다운 목록 만드는 7가지 방법
3단계: 드롭다운 목록 필터 실전 활용
이제 드롭다운 목록 필터가 실제로 작동하도록 만들어 보겠습니다.
❶ 데이터 표를 다른 위치로 복사한 후, 내용 지우기(Clear Contents) 명령으로 복사된 표의 내용을 모두 삭제합니다. 복사된 표의 셀을 모두 선택한 뒤 Delete 키를 눌러도 됩니다.
❷ 복사된 데이터 표의 맨 첫 번째 셀에 아래 수식을 입력합니다.
=IFERROR(INDEX($B$5:$E$12,$G5,COLUMNS($M$5:M5)),"")
- $B$5:$E$12: 원본 데이터 표의 셀 범위
- $G5: 두 번째 보조 열(Matched)의 첫 번째 셀
- $M$5:M5: 복사된 데이터 표의 첫 번째 열에 해당하는 셀 범위
- "": 드롭다운 목록 필터에서 선택한 항목에 대한 데이터가 없을 경우 IFERROR 함수로 셀을 비워 두는 역할
❸ Enter 키를 눌러 수식을 실행합니다.
❹ 채우기 핸들을 복사된 데이터 표 전체로 드래그하여 모든 셀에 수식을 적용합니다.
함께 읽으면 좋은 글: 엑셀 드롭다운 목록이 작동하지 않을 때 해결하는 8가지 방법
마무리
지금까지 엑셀에서 셀 값을 기반으로 드롭다운 목록 필터를 만드는 과정을 단계별로 알아보았습니다. 이 글에 첨부된 연습용 통합 문서를 다운로드하여 직접 실습해 보시길 권장합니다. 궁금한 점이 있다면 아래 댓글로 남겨 주세요. 관련 질문에 최대한 빠르게 답변드리겠습니다. 더 많은 엑셀 팁이 필요하시면 Exceldemy 웹사이트를 방문해 보세요.
관련 글
- 엑셀에서 다중 선택 가능한 드롭다운 목록 만드는 방법
- 엑셀 VBA로 종속 드롭다운 목록 만들기 (3가지 방법)
- 엑셀 드롭다운 목록에서 여러 항목 선택하기 (3가지 방법)
- 엑셀에서 드롭다운 목록 자동 업데이트하기 (3가지 방법)
- 엑셀에서 다중 선택 리스트박스 만드는 방법