엑셀 여러 조건 일치 데이터 추출 FILTER, INDEX, MATCH 함수 활용

 

 

데이터 분석의 핵심은 원하는 정보를 빠르고 정확하게 추출하는 것! 방대한 데이터 속에서 허우적거리는 대신, 엑셀 함수들을 제대로 활용한다면 분석 시간을 단축하고 효율성을 극대화할 수 있습니다. 이 글에서는 FILTER, INDEX, MATCH 함수를 활용하여 여러 조건에 일치하는 데이터를 추출하는 비법을 아낌없이 공개합니다. 실무 예시와 함께 함수의 작동 원리를 낱낱이 파헤쳐, 여러분의 엑셀 실력을 한 단계 도약시켜 드리겠습니다.

FILTER 함수: 데이터 필터링의 끝판왕

FILTER 함수는 마치 마법의 돋보기처럼, 원하는 데이터만 쏙쏙 골라내는 강력한 기능을 자랑합니다. 기존 IF 함수보다 훨씬 간결하고 직관적인 구문으로 복잡한 필터링 작업도 거뜬히 처리할 수 있죠! 마치 숙련된 데이터 분석가처럼 말이죠!

FILTER 함수 기본 구조

FILTER(array, include, [if_empty])

• array: 필터링 대상이 되는 데이터 범위입니다. 마치 그물을 펼치듯, 데이터를 싸그리 잡아들일 범위를 지정하는 겁니다.
• include: TRUE/FALSE 값을 반환하는 조건식입니다. 이 조건식이 TRUE인 데이터만 그물에 걸려 나오게 됩니다. 여러 조건을 사용할 땐 곱셈(*) 연산자나 AND 함수를 활용하면 됩니다. 마치 여러 개의 체를 거치는 것처럼, 원하는 데이터만 남게 되는 거죠.
• if_empty: 조건에 맞는 데이터가 없을 경우 표시할 값입니다. (선택 사항) 빈 그물을 보여주기보단, "데이터 없음!" 같은 메시지를 띄우는 게 훨씬 보기 좋겠죠?

FILTER 함수 실무 예시

자, 이제 실제 상황을 가정해 볼까요? 10,000명의 고객 데이터에서 '서울'에 거주하며 'VIP' 등급인 고객 정보만 추출해야 한다고 가정해 봅시다. 고객 데이터는 A1:C10000 범위에 있고, 도시 정보는 B열, 등급 정보는 C열에 있다고 합시다.

=FILTER(A1:C10000,(B1:B10000="서울")*(C1:C10000="VIP"),"조건에 맞는 데이터 없음")

이 수식은 B열이 "서울"이고 C열이 "VIP"인 행만 추출하여 새로운 배열을 생성합니다. 조건에 맞는 데이터가 없다면 "조건에 맞는 데이터 없음" 메시지가 띄워지겠죠. 참 쉽죠?

INDEX & MATCH 함수: 데이터 추출의 명콤비

INDEX와 MATCH 함수는 마치 찰떡궁합처럼, 특정 조건에 맞는 데이터 값을 정확하게 추출하는 데 탁월한 성능을 발휘합니다. FILTER 함수가 배열 전체를 반환하는 것과 달리, INDEX & MATCH 콤비는 특정 셀의 값만 쏙 뽑아낼 수 있다는 장점이 있습니다. 마치 데이터의 바늘에서 실을 뽑아내는 것처럼 말이죠.

INDEX 함수 기본 구조

INDEX(array, row_num, [column_num])

• array: 데이터 범위입니다. 여기서 원하는 값을 찾아낼 겁니다.
• row_num: 가져올 행 번호입니다. 몇 번째 줄에 있는 값을 가져올지 지정하는 거죠.
• column_num: 가져올 열 번호입니다. (2차원 배열인 경우) 몇 번째 칸에 있는 값을 가져올지 지정합니다.

MATCH 함수 기본 구조

MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: 찾을 값입니다. 바로 이 값을 찾아내기 위해 MATCH 함수를 사용하는 겁니다.
• lookup_array: 찾을 범위입니다. 어디에서 찾을지 범위를 지정해야겠죠?
• match_type: 일치 유형입니다. 0은 정확히 일치하는 값, 1은 근사값 이하, -1은 근사값 이상을 찾습니다. 정확한 값을 찾고 싶다면 0을 선택하면 됩니다.

INDEX & MATCH 함수 실무 예시

이번에도 10,000명의 고객 데이터를 예로 들어 보겠습니다. '김철수'라는 고객의 등급을 찾아야 한다면 어떻게 해야 할까요? 고객 이름은 A열, 등급 정보는 C열에 있다고 가정합니다.

=INDEX(C1:C10000,MATCH("김철수",A1:A10000,0))

MATCH 함수가 A1:A10000 범위에서 "김철수"를 찾아 해당 행 번호를 반환하고, INDEX 함수는 C1:C10000 범위에서 그 행 번호에 해당하는 등급 정보를 가져옵니다. 마치 데이터의 미로에서 정확한 위치를 찾아내는 것 같지 않나요?

FILTER, INDEX, MATCH 함수의 환상적인 콜라보: 복잡한 데이터 추출도 문제없다!

FILTER, INDEX, MATCH 함수는 각각 사용해도 강력하지만, 함께 사용하면 더욱 놀라운 시너지 효과를 발휘합니다. 예를 들어 특정 조건에 맞는 데이터 중 최댓값이나 최솟값을 찾아 그 데이터의 다른 정보를 추출하는 등, 복잡한 데이터 처리 작업도 손쉽게 해결할 수 있습니다. 마치 데이터 분석 어벤져스 같죠!

복잡한 조건 처리 실무 예시

'서울'에 거주하는 고객 중 가장 나이가 많은 고객의 이름을 찾아야 한다고 가정해 보겠습니다. 고객 이름은 A열, 도시 정보는 B열, 나이는 D열에 있다고 합시다.

=INDEX(A1:A10000,MATCH(MAX(FILTER(D1:D10000,B1:B10000="서울")),FILTER(D1:D10000,B1:B10000="서울"),0))

FILTER 함수로 '서울' 거주 고객의 나이를 추출하고, MAX 함수로 최댓값을 찾습니다. 그 후 MATCH 함수로 최댓값의 행 번호를 찾고, INDEX 함수는 해당 행의 고객 이름을 가져옵니다. 마치 데이터 정글에서 보물을 찾아내는 탐험가 같네요!

데이터 추출 함수 활용 팁: 실력 향상의 지름길

• 데이터 정리의 중요성: 분석에 앞서 데이터를 깔끔하게 정리하면 함수 활용의 효율이 배가됩니다. 마치 요리하기 전 재료를 손질하는 것처럼 말이죠!
• 조건 설정의 정확성: 정확한 조건 설정은 원하는 데이터를 정확하게 추출하는 핵심입니다. 조건이 모호하면 원치 않는 결과가 나올 수 있으니 주의해야 합니다!
• 함수 조합의 유연성: FILTER, INDEX, MATCH 외에도 다양한 함수들을 조합하여 더욱 복잡한 조건을 처리할 수 있습니다. 마치 레고 블록처럼 함수들을 조합하여 원하는 결과를 만들어낼 수 있는 거죠!
• XLOOKUP 함수: 엑셀 365 이상 버전에서는 INDEX & MATCH 조합과 유사한 기능을 제공하는 XLOOKUP 함수를 사용할 수 있습니다. 이 함수는 더욱 간편하고 강력한 기능을 제공하니, 꼭 활용해 보세요!
• AGGREGATE 함수: AGGREGATE 함수는 SUBTOTAL 함수의 업그레이드 버전으로, 다양한 조건과 함께 사용하여 특정 데이터를 효율적으로 추출할 수 있습니다. 숨겨진 행이나 오류 값을 무시하는 기능 등, 다양한 옵션을 제공하니 꼭 한번 사용해 보세요!
• 배열 수식: FILTER 함수는 배열 수식을 기반으로 작동합니다. 따라서 함수 입력 후 Enter 키 대신 Ctrl + Shift + Enter 키를 눌러야 제대로 작동합니다. 잊지 마세요!

FILTER, INDEX, MATCH 함수와 함께라면 더 이상 복잡한 데이터 앞에서 좌절할 필요가 없습니다. 이 강력한 도구들을 활용하여 데이터 분석의 달인이 되어 보세요! 여러분의 빛나는 데이터 분석 실력을 응원합니다!