엑셀 FILTER 함수를 사용하면 특정 조건에 맞는 데이터만 별도로 추출할 수 있습니다.
여기에 드롭다운 목록을 함께 사용하면 조건을 직접 입력하지 않고 목록에서 선택하는 것만으로 원하는 데이터를 자동으로 조회할 수 있습니다.
예를 들어 직원 명단에서 드롭다운으로 영업팀을 선택하면 영업팀 직원만 표시되고, 개발팀을 선택하면 개발팀 직원만 자동으로 표시되도록 만들 수 있습니다.
직원 조회표뿐만 아니라 매출 현황, 거래처 관리, 재고 관리, 상품 검색 등에서도 활용하기 좋은 기능입니다.
이번 글에서는 엑셀 드롭다운 목록과 FILTER 함수 연동하는 방법을 기본 설정부터 다중조건 검색까지 알아보겠습니다.
드롭다운과 FILTER 함수를 연동하면 무엇이 편할까?
FILTER 함수의 기본적인 사용 방법은 다음과 같습니다.
=FILTER(A2:D10,B2:B10=”영업팀”,”결과 없음”)
이 수식은 B열에서 영업팀에 해당하는 데이터만 추출합니다.
문제는 다른 부서를 확인할 때마다 수식 안의 조건을 변경해야 한다는 것입니다.
이때 조건을 직접 입력하는 대신 별도의 셀에 드롭다운을 만들어 놓고 FILTER 함수가 해당 셀을 참조하도록 설정할 수 있습니다.
예를 들어 F2 셀에서
영업팀 ▼
을 선택하면 영업팀 데이터가 나타나고,
개발팀 ▼
으로 변경하면 개발팀 데이터가 나타나는 방식입니다.
조건을 자주 변경해야 하는 엑셀 문서라면 훨씬 편리하게 사용할 수 있습니다.
1. 예제 데이터 준비하기
먼저 다음과 같은 직원 명단이 있다고 가정해 보겠습니다.
| 이름 | 부서 | 직급 | 실적 |
|---|---|---|---|
| 김철수 | 영업팀 | 대리 | 120 |
| 이영희 | 총무팀 | 과장 | 80 |
| 박민수 | 영업팀 | 사원 | 95 |
| 김민지 | 개발팀 | 대리 | 110 |
| 최영수 | 영업팀 | 과장 | 150 |
| 정수진 | 개발팀 | 사원 | 90 |
데이터가 입력된 범위는 A1:D7이라고 가정하겠습니다.
A열 : 이름
B열 : 부서
C열 : 직급
D열 : 실적
이제 별도의 영역에 부서를 선택할 수 있는 드롭다운을 만들어 보겠습니다.
2. 엑셀 드롭다운 목록 만들기
먼저 드롭다운에 표시할 항목을 준비합니다.
예를 들어 H2:H4 셀에 다음과 같이 입력합니다.
영업팀
총무팀
개발팀
이제 F2 셀을 드롭다운으로 사용해 보겠습니다.
F2 셀을 선택한 후
상단 메뉴에서
데이터 → 데이터 유효성 검사
순서로 들어갑니다.
설정에서 허용을 ‘목록’으로 선택합니다.
원본에는 다음 범위를 지정합니다.
=$H$2:$H$4
확인을 누르면 F2 셀에 드롭다운 버튼이 만들어집니다.
이제 F2를 클릭하면
- 영업팀
- 총무팀
- 개발팀
중 하나를 선택할 수 있습니다.
3. 드롭다운과 FILTER 함수 연동하기
이제 드롭다운에서 선택한 부서에 따라 결과가 자동으로 변경되도록 FILTER 함수를 연결하면 됩니다.
예를 들어 F5 셀부터 검색 결과를 표시하고 싶다면 F5에 다음 수식을 입력합니다.
=FILTER(A2:D7,B2:B7=F2,”결과 없음”)
여기에서 각각의 의미는 다음과 같습니다.
A2:D7
검색 결과로 가져올 전체 직원 데이터입니다.
B2:B7=F2
B열의 부서가 F2에서 선택한 부서와 같은지 확인하는 조건입니다.
“결과 없음”
조건에 해당하는 데이터가 없을 때 표시되는 문구입니다.
이제 F2 드롭다운에서 영업팀을 선택하면 영업팀 직원만 표시됩니다.
| 이름 | 부서 | 직급 | 실적 |
|---|---|---|---|
| 김철수 | 영업팀 | 대리 | 120 |
| 박민수 | 영업팀 | 사원 | 95 |
| 최영수 | 영업팀 | 과장 | 150 |
F2를 개발팀으로 변경하면 결과도 즉시 변경됩니다.
| 이름 | 부서 | 직급 | 실적 |
|---|---|---|---|
| 김민지 | 개발팀 | 대리 | 110 |
| 정수진 | 개발팀 | 사원 | 90 |
FILTER 함수 수식을 다시 수정할 필요 없이 드롭다운만 변경하면 검색 결과가 자동으로 바뀌는 것입니다.
4. 부서 + 직급 드롭다운으로 다중조건 검색하기
드롭다운을 하나만 사용하는 것이 아니라 여러 개 만들어 다중조건 검색 기능도 만들 수 있습니다.
이번에는
F2 : 부서 선택
G2 : 직급 선택
으로 만들어 보겠습니다.
F2에는
- 영업팀
- 총무팀
- 개발팀
목록을 만들고 G2에는
- 사원
- 대리
- 과장
목록을 만듭니다.
이제 두 가지 조건을 모두 만족하는 데이터만 추출하려면 다음 수식을 사용합니다.
=FILTER(A2:D7,(B2:B7=F2)*(C2:C7=G2),”결과 없음”)
FILTER 함수에서 *는 여러 조건을 동시에 만족해야 하는 AND 조건을 만들 때 사용할 수 있습니다.
예를 들어
F2 = 영업팀
G2 = 과장
으로 선택하면 두 조건을 모두 만족하는 데이터만 표시됩니다.
즉,
영업팀이면서 과장인 직원
을 조회할 수 있습니다.
5. 부서 + 실적으로 조건 만들기
드롭다운 조건과 숫자 조건을 함께 사용하는 것도 가능합니다.
예를 들어 F2에서 부서를 선택하고 실적이 100 이상인 직원만 찾는다고 가정하겠습니다.
수식은 다음과 같습니다.
=FILTER(A2:D7,(B2:B7=F2)*(D2:D7>=100),”결과 없음”)
F2에서 영업팀을 선택했다면
영업팀이면서 실적이 100 이상
인 직원만 나타납니다.
조건을 추가하고 싶다면 같은 방식으로 계속 연결할 수 있습니다.
6. ‘전체’ 항목을 추가해 모든 데이터 표시하기
실제로 드롭다운 검색 기능을 만들어 사용하다 보면 특정 부서뿐만 아니라 전체 데이터를 다시 보고 싶은 경우가 있습니다.
이럴 때 드롭다운 목록에 전체라는 항목을 하나 추가하면 편리합니다.
드롭다운을 다음과 같이 구성합니다.
전체
영업팀
총무팀
개발팀
그리고 FILTER 함수는 다음과 같이 작성할 수 있습니다.
=FILTER(A2:D7,IF(F2=”전체”,A2:A7<>””,B2:B7=F2),”결과 없음”)
F2에서 전체를 선택하면 데이터가 있는 모든 행을 표시하고, 특정 부서를 선택하면 해당 부서만 표시됩니다.
사용자가 직접 조회 조건을 변경하는 엑셀 양식을 만든다면 전체 옵션을 추가해 두는 것을 추천합니다.
7. 다중조건에서도 ‘전체’ 옵션 사용하기
부서와 직급을 각각 드롭다운으로 만들었다면 두 조건 모두 전체 옵션을 사용할 수도 있습니다.
예를 들어
F2 : 부서
G2 : 직급
이라고 가정하겠습니다.
수식은 다음과 같이 구성할 수 있습니다.
=FILTER(A2:D7,IF(F2=”전체”,1,B2:B7=F2)*IF(G2=”전체”,1,C2:C7=G2),”결과 없음”)
이렇게 설정하면 다양한 조합으로 검색할 수 있습니다.
예를 들어
부서 : 영업팀 / 직급 : 전체
→ 영업팀 직원 전체 표시
부서 : 전체 / 직급 : 대리
→ 모든 부서에서 대리만 표시
부서 : 개발팀 / 직급 : 사원
→ 개발팀이면서 사원인 직원만 표시
부서 : 전체 / 직급 : 전체
→ 전체 데이터 표시
단순히 하나의 조건을 선택하는 것보다 실제 업무용 조회표를 만들 때 활용도가 높은 방식입니다.
8. 드롭다운 목록을 자동으로 관리하려면?
앞의 예제에서는
영업팀
총무팀
개발팀
을 별도의 셀에 직접 입력했습니다.
하지만 원본 데이터에 부서가 계속 추가된다면 드롭다운 목록도 매번 수정해야 하는 불편함이 있습니다.
최신 엑셀에서는 UNIQUE 함수를 활용해 중복을 제외한 목록을 만들 수 있습니다.
예를 들어 B2:B100에 부서명이 입력되어 있다면 다음과 같이 사용할 수 있습니다.
=UNIQUE(B2:B100)
그러면
영업팀
총무팀
개발팀
처럼 중복된 값을 제외한 부서 목록이 자동으로 만들어집니다.
원본에 새로운 부서가 추가되면 UNIQUE 함수의 결과에도 반영되므로 드롭다운 목록 관리가 한층 편리해집니다.
실제 문서에서는 원본 데이터를 엑셀 표(Table)로 만들어 범위가 확장되도록 구성하면 데이터가 계속 추가되는 환경에서 관리하기도 좋습니다.
9. FILTER 함수 결과가 나오지 않을 때 확인할 점
드롭다운과 FILTER 함수를 연동했는데 정상적으로 결과가 표시되지 않는다면 몇 가지 사항을 확인해야 합니다.
가장 먼저 드롭다운 값과 원본 데이터의 값이 정확히 일치하는지 확인합니다.
예를 들어 원본에는 영업팀이라고 되어 있는데 드롭다운에는 영업이라고 되어 있다면
B2:B7=F2
조건을 만족하지 않습니다.
또한 FILTER 함수의 결과가 여러 셀로 확장되어야 하는 영역에 다른 값이 입력되어 있으면 #SPILL! 오류가 발생할 수 있습니다.
FILTER 함수 결과가 표시될 아래쪽과 오른쪽 셀이 비어 있는지도 확인합니다.
마지막으로 사용 중인 엑셀 버전에서 FILTER 함수가 지원되는지도 확인할 필요가 있습니다.
10. 실무에서는 이렇게 활용할 수 있다
드롭다운과 FILTER 함수를 조합하면 별도의 복잡한 프로그램 없이도 간단한 엑셀 조회 화면을 만들 수 있습니다.
예를 들어 직원 관리표에서는
부서 선택 → 직급 선택 → 해당 직원 자동 표시
방식으로 사용할 수 있습니다.
매출 관리표에서는
담당자 선택 → 거래처 또는 매출 내역 자동 표시
형태로 구성할 수 있습니다.
재고 관리표라면
상품분류 선택 → 해당 상품의 재고 목록 표시
와 같은 기능을 만들 수도 있습니다.
직접 조건을 입력할 필요가 없기 때문에 여러 사람이 함께 사용하는 엑셀 문서에서도 입력 실수를 줄이는 데 도움이 됩니다.
엑셀 드롭다운과 FILTER 함수 연동 방법 정리
엑셀에서 드롭다운과 FILTER 함수를 연결하는 기본적인 과정은 어렵지 않습니다.
먼저 데이터 → 데이터 유효성 검사 → 목록에서 드롭다운을 만들고, 해당 셀을 FILTER 함수의 조건으로 지정하면 됩니다.
부서를 선택하는 셀이 F2라면 기본 수식은 다음과 같습니다.
=FILTER(A2:D100,B2:B100=F2,”결과 없음”)
부서와 직급처럼 두 조건을 모두 만족하는 결과가 필요하다면
=FILTER(A2:D100,(B2:B100=F2)*(C2:C100=G2),”결과 없음”)
처럼 AND 다중조건을 적용할 수 있습니다.
여기에 전체 항목이나 UNIQUE 함수까지 활용하면 데이터가 많아져도 사용하기 편리한 조회표를 만들 수 있습니다.
특히 드롭다운 + FILTER + UNIQUE 함수 조합을 알아두면 직원 관리, 매출 조회, 재고 관리, 고객 관리 등 다양한 엑셀 문서에 응용할 수 있으므로 자주 사용하는 데이터가 있다면 직접 만들어 활용해 보시기 바랍니다.