엑셀을 사용하다 보면 특정 조건(기준)에 해당하는 데이터만 추출해 목록을 만들어야 하는 경우가 종종 있습니다. 이번 글에서는 엑셀에서 조건에 따라 목록을 생성하는 다양한 방법을 단계별로 살펴보겠습니다. 이 튜토리얼은 Excel 365 버전을 기준으로 작성되었지만, 다른 버전에서도 대부분의 방법을 그대로 적용할 수 있습니다.
먼저 예제의 기반이 되는 데이터셋부터 확인해 보겠습니다.
위 데이터에는 여러 지역에 거주하는 사람들과 그들이 보유한 차량 정보가 정리되어 있습니다. 이 데이터를 바탕으로 조건에 맞는 목록을 만들어 보겠습니다.
참고로 여기서는 설명을 단순하게 유지하기 위해 기본적인 더미 데이터를 사용했습니다. 실무에서는 훨씬 더 크고 복잡한 데이터셋을 다루게 될 수 있다는 점을 감안해 주세요.
연습용 워크북
아래 링크에서 연습용 워크북을 무료로 내려받아 직접 따라 해볼 수 있습니다.
조건에 맞는 목록 생성하기
예시로, 사람들의 지역(region)을 기준으로 명단을 만들어 보겠습니다.
데이터가 작기 때문에 지역이 총 4곳이라는 것을 알 수 있습니다. 미리 지역 이름을 입력해 두고, 각 지역에 해당하는 인원 목록을 찾아내는 방식입니다.
1. INDEX-SMALL 함수 조합으로 목록 생성하기
목록을 만들려면 하나의 값이 아니라 여러 개의 값을 테이블에서 불러와야 합니다. 이럴 때 INDEX 함수와 SMALL 함수의 조합이 매우 유용합니다.
각 함수에 대한 자세한 내용은 관련 문서를 참고하세요: INDEX, SMALL
이 두 함수 외에도 IF, ROW, IFERROR 같은 보조 함수가 필요합니다. 관련 내용: IF, ROW, IFERROR
그럼 수식을 살펴보겠습니다.
=IFERROR(INDEX($B$2:$B$12,SMALL(IF($C$2:$C$12=$G$2,ROW($B$2:$B$12)),ROW(1:1))-1,1),"")
수식의 각 요소는 다음과 같은 역할을 합니다.
- INDEX: 이름 열인 배열 B2:B12에서 값을 반환합니다.
- SMALL: 가져올 행 번호를 계산해 줍니다.
- IF: SMALL 내부에서 조건이 일치하는지 판별합니다.
- ROW: 열의 각 셀을 순회하며 행 번호를 생성하고, 바깥쪽 ROW는 SMALL 함수의 k번째 값을 지정합니다.
- IFERROR: 수식 실행 중 발생할 수 있는 오류를 처리합니다.
수식을 아래로 드래그하면 해당 지역에 속한 모든 인원이 표시됩니다.
같은 방식으로 나머지 지역에도 수식을 작성하면 됩니다(수식은 동일하고 참조 셀만 옮기면 됩니다).
INDEX-SMALL 조합의 변형 수식
같은 결과를 얻을 수 있는 또 다른 수식 표현도 있습니다. 사용하는 함수는 앞서와 동일하고, 구조만 약간 다릅니다.
=IFERROR(INDEX($B$2:$B$12,SMALL(IF($C$2:$C$12=G$2,ROW($B$2:$B$12)-1),ROW(1:1)),1),"")배열 수식이므로 실행할 때 반드시 Ctrl + Shift + Enter를 눌러야 합니다.
두 수식 사이에는 미묘한 차이가 있는데, 찾으셨나요?
네, 첫 번째 수식에서는 SMALL 부분의 맨 끝에서 1을 빼주었지만, 두 번째 수식에서는 IF 부분 안에서 1을 빼줍니다. 이렇게 1을 빼는 이유는 올바른 행 번호에 맞춰주기 위함입니다. 어디에서 처리하느냐의 차이일 뿐, 최종 결과는 같습니다.
나머지 조건에도 수식을 작성하면 전체 목록이 완성됩니다.
함께 읽으면 좋은 글: 엑셀 한 셀 안에 목록 만드는 방법 (3가지 빠른 방법)
2. AGGREGATE 함수로 목록 생성하기
엑셀은 다양한 작업을 수행할 수 있는 AGGREGATE 함수를 제공합니다. 이 함수를 활용해서도 조건에 맞는 목록을 만들 수 있습니다.
AGGREGATE 함수는 AVERAGE, COUNT, MAX 등과 같은 집계 계산 결과를 반환합니다.
AGGREGATE 함수의 기본 구문은 다음과 같습니다.
AGGREGATE(function_number, behavior_options, range)- function_number: 어떤 계산을 수행할지 지정하는 번호입니다.
- behavior_options: 함수가 오류나 숨겨진 데이터를 어떻게 처리할지 결정하는 번호입니다.
- range: 집계를 적용할 범위입니다.
AGGREGATE 함수는 여러 가지 작업을 수행하기 때문에 내부에 미리 정의된 함수 번호가 있습니다. 자주 사용되는 함수 번호 몇 가지를 정리하면 다음과 같습니다.
| 함수 | 함수 번호 |
|---|---|
| AVERAGE | 1 |
| COUNT | 2 |
| COUNTA | 3 |
| MAX | 4 |
| MIN | 5 |
| PRODUCT | 6 |
| SUM | 9 |
| LARGE | 14 |
| SMALL | 15 |
함수에 대해 더 알고 싶다면 Microsoft 공식 지원 페이지를 참고하세요.
그럼 실제 수식을 살펴보겠습니다.
=IFERROR(INDEX($B$2:$B$12,AGGREGATE(15,6,IF($C$2:$C$12=G$2,ROW($B$2:$B$12)-1),ROW(1:1)),1),"")
여기서는 AGGREGATE 함수와 함께 INDEX 함수를 사용했습니다. INDEX는 배열을 담고 있으며, 수식 뒷부분에서 찾은 일치 항목을 기반으로 값을 반환합니다.
수식을 보면 AGGREGATE의 function_number로 15를 사용했습니다. 위 표에서 확인할 수 있듯이 15는 SMALL 함수 동작을 의미합니다. 그래서 사실상 앞서 배운 INDEX-SMALL 수식을 AGGREGATE 함수 형태로 실행한 것과 같습니다.
behavior_options 자리의 6은 오류 값 무시를 의미합니다.
나머지 값들에도 같은 방식으로 수식을 작성하면 됩니다.
관련 글
- 엑셀로 할 일 목록(To Do List) 만드는 방법 (3가지 쉬운 방법)
- 엑셀에서 메일링 리스트 만들기 (2가지 방법)
- 엑셀에서 번호 매긴 목록 만드는 방법 (8가지 방법)
3. INDEX-MATCH-COUNTIF 조합으로 고유한 목록 생성하기
조건에 맞으면서도 중복 없는 고유한(unique) 목록을 만들 수도 있습니다. 이때는 INDEX, MATCH, COUNTIF 함수의 조합을 활용합니다.
COUNTIF는 범위에서 단일 조건을 충족하는 셀의 개수를 세는 함수이고, MATCH는 범위에서 찾는 값의 위치를 반환하는 함수입니다. 자세한 내용은 관련 문서를 참고하세요: MATCH, COUNTIF
수식을 살펴보겠습니다.
=IFERROR(INDEX($B$2:$B$12, MATCH(0, IF(G$2=$C$2:$C$12, COUNTIF($G$2:$G2, $B$2:$B$12), ""), 0)),"") 
수식 구성을 정리하면 다음과 같습니다.
- B2:B12: 고유값을 추출할 이름 열 범위
- C2:C12: 조건이 담긴 열 범위
- G2: 적용할 조건(기준)
MATCH 함수 안에서는 lookup_value로 0을 지정하고, lookup_array 자리에는 COUNTIF가 포함된 IF 부분을 넣었습니다. 이 부분은 0을 찾을 때까지 값을 반환하며, 그 결과값이 INDEX 함수의 행 번호 역할을 하게 됩니다.
수식을 아래로 드래그하면 모든 고유값이 추출됩니다.
배열 수식이므로 실행 시 Ctrl + Shift + Enter를 누르는 것을 잊지 마세요.
이 방법은 조건에 맞는 고유 목록을 생성하는 대표적인 접근 방식 중 하나입니다. 고유 목록 생성에 대해 더 자세히 알고 싶다면 관련 전용 글을 참고하세요.
4. FILTER 함수로 조건에 맞는 목록 생성하기
Excel 365를 사용하고 있다면 FILTER라는 단일 기본 제공 함수만으로 이 작업을 처리할 수 있습니다.
FILTER 함수는 지정한 조건에 따라 데이터 범위를 필터링하고 일치하는 레코드를 추출합니다. 함수에 대한 자세한 내용은 관련 문서를 참고하세요: FILTER
수식은 다음과 같이 아주 간단합니다.
=FILTER($B$2:$B$12,$C$2:$C$12=G$2)
B2:B12는 필터링할 배열이고, 그 뒤에는 목록을 생성할 기준이 되는 조건을 지정했습니다.
이 방법은 수식을 아래로 드래그할 필요가 없습니다. 한 번의 입력만으로 일치하는 모든 값이 자동으로 채워져 목록이 완성됩니다.
함께 읽으면 좋은 글: 엑셀에서 알파벳 순 목록 만드는 방법 (3가지 방법)
마무리
지금까지 엑셀에서 조건에 맞는 목록을 생성하는 여러 가지 방법을 살펴보았습니다. INDEX-SMALL 조합부터 AGGREGATE, INDEX-MATCH-COUNTIF, 그리고 Excel 365의 FILTER 함수까지 상황에 맞는 방법을 선택해 활용해 보시기 바랍니다. 이해하기 어려운 부분이 있다면 언제든지 댓글로 문의해 주세요. 이 글에서 다루지 않은 다른 방법이 있다면 함께 공유해 주시면 감사하겠습니다.
추천 추가 학습 자료
- 엑셀에서 글머리 기호 목록 만드는 방법 (9가지 방법)
- 엑셀에서 쉼표로 구분된 목록 만들기 (5가지 방법)
- 엑셀에서 가격표(Price List) 만드는 방법 (단계별 가이드)