이 글에서는 데이터 유효성 검사(Data Validation) 드롭다운 목록을 활용해 엑셀 데이터를 필터링하는 방법을 자세히 알아보겠습니다. 일반적으로 마이크로소프트 엑셀에서는 필터(Filter) 기능을 사용해 특정 데이터만 추출하지만, 드롭다운 목록을 통해서도 원하는 데이터를 손쉽게 걸러낼 수 있습니다. 먼저 데이터 유효성 검사 기능으로 드롭다운 목록을 만들고, 이후 선택한 항목에 따라 해당 행들이 자동으로 필터링되도록 구현해 보겠습니다.
이 글에서 사용한 연습용 워크북 파일을 다운로드하여 직접 따라 해볼 수 있습니다.
드롭다운 목록과 필터를 활용하는 2가지 예제
여기서는 여러 과일의 지역별 판매 데이터가 담긴 데이터셋을 예시로 사용하겠습니다. 데이터셋에 포함된 지역(Area) 목록으로 데이터 유효성 검사 드롭다운 목록을 만든 뒤, 선택한 지역에 맞는 과일 판매 데이터를 추출하는 방식입니다.
1. 보조 열(Helper Columns)을 활용해 드롭다운 목록 값 필터링하기
첫 번째 방법은 기본 데이터셋에 3개의 보조 열을 추가하는 것입니다. 그런 다음 드롭다운 선택값에 따라 데이터를 추출하게 됩니다. 보조 수식을 입력하기 전에 고유한 지역(Area) 값들로 드롭다운 목록을 먼저 생성해야 합니다. 아래 단계를 따라 진행해 보세요.
단계:
- 드롭다운 목록을 만들기 전에 아래와 같이 고유한 지역(Area) 값을 미리 나열합니다.
- 그다음 드롭다운 목록을 배치할 셀(여기서는 H5 셀)을 클릭합니다.
- 엑셀 리본 메뉴에서 데이터 > 데이터 도구 > 데이터 유효성 > 데이터 유효성으로 이동합니다.
- 데이터 유효성 대화상자가 나타나면 설정 탭에서 제한 대상 항목의 목록을 선택하고 원본(Source) 범위를 지정한 후 확인을 누릅니다.
- 확인을 누르면 아래와 같이 드롭다운 목록이 완성됩니다.
- 이제 첫 번째 보조 열(D5 셀)에 ROWS 함수를 활용한 아래 수식을 입력합니다. Enter 키를 누른 뒤 채우기 핸들(+)을 이용해 수식을 열 전체로 복사합니다.
=ROWS($A5:A$5)
- 수식을 입력하면 아래와 같은 결과를 얻을 수 있습니다.
- 다음으로 두 번째 보조 열(Helper 2)에는 IF 함수 수식을 사용합니다.
=IF(C5=$H$5,D5,"")
- 세 번째 보조 열(Helper 3)에는 아래 수식을 입력합니다.
=IFERROR(SMALL($E$5:$E$14,D5),"")
여기서 SMALL 함수는 범위 E5:E14에서 k번째로 작은 값을 반환하며, IFERROR 함수는 SMALL 수식의 결과가 오류일 경우 빈칸을 반환합니다.
- 이제 Baltimore 지역에 해당하는 모든 과일 판매 데이터를 필터링한다고 가정해 보겠습니다. 원하는 결과를 얻으려면 J5 셀에 아래 수식을 입력하고 Enter 키를 누릅니다.
=IFERROR(INDEX($A$5:$C$14,$F5,COLUMNS($J$5:J5)),"")
여기서 INDEX 함수는 행 번호를 기준으로 데이터를 추출하고, COLUMNS 함수는 범위 $J$5:J5 내의 열 번호를 반환합니다. 마지막으로 IFERROR 함수는 결과가 오류일 때 빈칸을 표시합니다.
- 수식을 입력하면 아래와 같은 결과가 나타납니다. 한 행의 전체 데이터를 얻으려면 채우기 핸들을 오른쪽으로 드래그하세요.
- 이어서 채우기 핸들을 아래로 드래그하면 Baltimore 지역의 최종 과일 판매 데이터를 확인할 수 있습니다.
- 이제 드롭다운 목록에서 Phoenix 지역을 선택하면 아래와 같이 Phoenix에 해당하는 행만 필터링됩니다.
함께 읽으면 좋은 글: 엑셀에서 데이터 유효성 검사 드롭다운 목록 만드는 8가지 방법
2. FILTER 함수로 드롭다운 목록 기반 데이터 추출하기
Excel 365를 사용 중이라면 FILTER 함수로 간편하게 데이터를 필터링할 수 있습니다. 시작하기 전에 Ctrl + T를 눌러 데이터 범위를 엑셀 테이블로 변환했습니다. 테이블로 변환하면 새로운 레코드를 추가할 때 드롭다운 목록도 새 데이터에 맞춰 자동으로 업데이트되기 때문입니다.
- 작업 편의를 위해 새로 만든 테이블에 이름을 지정합니다(예: Table4).
이제 아래 단계에 따라 본격적인 작업을 진행해 보겠습니다.
단계:
- 먼저 UNIQUE 함수를 사용해 고유한 지역 목록을 만듭니다. F5 셀에 아래 수식을 입력하고 Enter 키를 누르세요.
=SORT(UNIQUE(Table4[Area]))
여기서는 SORT 함수를 UNIQUE 함수와 함께 사용해 지역(Area) 데이터를 정렬했습니다.
- 수식을 입력하면 위와 같은 결과를 얻습니다. 이 수식은 정렬된 고유값 배열(파란색 테두리)을 반환합니다.
- 이제 H5 셀에 드롭다운 목록을 만듭니다. 데이터 > 데이터 도구 > 데이터 유효성 > 데이터 유효성 경로로 데이터 유효성 대화상자를 연 뒤, 제한 대상에서 목록을 선택하고 원본 입력란에 아래 수식을 입력한 후 확인을 누릅니다.
=F5#
여기서 # 기호는 F5 셀의 전체 배열을 드롭다운 목록의 원본으로 사용한다는 의미입니다.
- 확인을 누르면 아래와 같이 드롭다운 목록이 생성됩니다.
- 이번에는 Long Beach 지역의 과일 판매 데이터를 추출해 보겠습니다. 원하는 결과를 얻으려면 F11 셀에 아래 수식을 입력하고 Enter 키를 누르세요.
=FILTER(Table4,Table4[Area]=H5,"No Data Found")
- 마지막으로 FILTER 수식을 입력하면 Long Beach 지역의 모든 판매 데이터가 표시됩니다. 드롭다운 목록에서 지역을 변경하면 선택한 지역에 맞는 행이 즉시 필터링됩니다.
함께 읽으면 좋은 글: 다른 셀 값을 기준으로 설정하는 엑셀 데이터 유효성 검사
결론
지금까지 엑셀에서 데이터 유효성 검사 드롭다운 목록을 활용해 데이터를 필터링하는 두 가지 방법을 자세히 살펴보았습니다. 보조 열을 이용하는 전통적인 방식과 FILTER 함수를 활용하는 최신 방식 중 상황에 맞는 방법을 선택해 활용해 보시기 바랍니다. 궁금한 점이 있다면 언제든지 문의해 주세요.
관련 글
- 엑셀 데이터 유효성 검사에서 영숫자만 허용하기(사용자 지정 수식 활용)
- 엑셀에서 여러 조건으로 사용자 지정 데이터 유효성 검사 적용하기(4가지 예제)
- 엑셀 데이터 유효성 검사 드롭다운 목록 자동완성 기능(2가지 방법)
- 엑셀 테이블로 데이터 유효성 검사 목록 만들기(3가지 방법)
- 엑셀에서 다중 선택이 가능한 데이터 유효성 검사 드롭다운 목록 만들기
- 엑셀 한 셀에 여러 데이터 유효성 검사 적용하기(3가지 예제)