엑셀 Filter 함수로 특정 문자 포함 셀 찾기 및 조건 정리 - Vlookup, Index, Match 함수 대체 방법

 

 

엑셀에 자료를 정리하다보면 "인사부", "특정 날짜", "불가능", "사용 불가" 등 특정 문구가 포함된 셀만 정리하거나 한눈에 보고 싶다는 생각을 해보신적이 있다고 생각합니다.

​

저의 경우에는, 다양한 장비를 사용하고 리스트화해서 보는데 교정 날짜가 도래한 장비만 한눈에 리스트업해서 봐야하는 상황이 있는데, 이런 경우 Filter 함수를 사용해서 편하게 정리할 수 있습니다.

​

* Filter 함수는 2021 버전보다 상위 버전에서만 사용이 가능하고, 이전 버전은 vlookup 함수나 match 함수 등을 활용해야하니 이전 글을 확인하세요.

 

---

 

필터링 활용 예시

 

 

저희 회사에서는 사용하는 장비들을 여러가지 정보를 정리해서 관리하고 있는데, 특정 사유로 사용하지 않게 되면 비고란에 "불용장비"를 적어놓고 사용하지 않습니다.

​

하지만, 불용장비 리스트도 따로 만들어야하는데, Filter 함수를 사용하면 간단하게 리스트업이 가능합니다.

 

 

=FILTER('장비리스트 Sheet'!B2:I1000, '장비리스트 Sheet'!K2:K1000="불용장비")

 

 

 

 

해당 함수는 K열에 "불용장비"라는 문구가 있는 모든 행을 B:I까지 불러온다는 의미입니다.

불용장비라고 적힌 장비들만 리스트화 되어서 잘 작성이 된 모습입니다.

 

 

---

필터(Filter) 함수

 

Filter 함수를 사용하려고하면 3가지 인수가 필요합니다.

배열, 포함, [if_empty] 각 인수에 대한 설정해야하는 값을 살펴보겠습니다.

​

1. 배열(Array): 불러올 데이터의 범위를 설정합니다.
2. 포함(Include): 특정 문서나 조건을 설정하여 필터링 할 내용을 설정합니다.
3. [if_empty]: (선택사항) 필터링된 결과가 없을 경우 반환할 값. 기본 값은 "#CALC!" 오류 입니다.

 

 

---

필터(Filter) 함수 활용 예시

 

 

이런 항목에서 "참석" 인원만 분류해보겠습니다.

 

 

 

=FILTER(A:C,D:D="참석")

 

 

저희가 가지고 오고 싶은 정보는 참석자에 대한 부서명, 이름, 전화번호입니다.

따라서 배열(Array)를 A:C로 설정한 뒤, 포함(Include)에 D:D를 설정해주면 D열에 참석표시한 모든 사람의 인적사항을 받아올 수 있습니다.

​

계속 사용하는 ':'는 어디부터 어디까지(~)의 개념이 들어갔다고 보시면 됩니다.

만약에 A~D열 전체를 선택하지 않고, 딱 지정된 범위에서만 보고 싶다면 아래와 같이 수정해주시면 됩니다.

 

 

 

 

=FILTER(A1:C27,D1:D27="참석")

 

 

---

주의 사항

 

 

​

하지만, 배열과 포함에 대한 지정 범위가 다를 경우 오류를 일으킬 수 있습니다.

위의 사진처럼 포함 인자의 배열의 범위를 다르게 할 경우 "#VALUE!" 오류를 일으키니 참고하세요.

 

 

 

---

그 외 필터 함수 활용 예시

 범위, 다중조건 필터링

 

 

Filter 함수는 특정 문자 외에도 범위로 지정할 수 있습니다.

 

예) 학생 점수 데이터에서 60점 이상의 학생 정보 필터링

 

전제 조건: 엑셀 문서상 학생 정보는 A:C열에 적혀있으며, B열에는 학생 점수가 적혀있다.

=FILTER(A2:C10, B2:B10 >= 60)

 

이렇게 설정하면 B열에 적혀있는 점수가 60점 이상인 경우 모든 정보를 필터링 할 수 있습니다.

 

 

---

 

예) 학생 점수 데이터에서 특정 과목(수학)에 70점 이상의 학생 정보 필터링

전제 조건: 엑셀 문서상 학생 정보는 A:C열에 적혀있으며 B열에는 학생 점수, C에는 과목이 적혀있다.

=FILTER(A2:C10, (B2:B10 >= 70) * (C2:C10 = "수학"))

이렇게하면 수학 점수가 70점 이상인 2가지 조건에 만족하는 학생들의 모든 정보를 필터링 할 수 있다.

 

 

---

결론

 

제 블로그에서 Index, Match, Vloopup 함수에 대한 글이 가장 조회수가 잘나오고 있습니다.

하지만, 비교적 최근에 나온 Filter 함수를 사용하면 더 편리하게 조건에 맞는 배열을 불러올 수 있고 다중 조건도 곱하기(*)로 쉽게 적용할 수 있습니다.

​

한번, 따라해보시고 어려운 점이 있으면 댓글 남겨주세요.