Excel에서는 기준에 따라 목록을 생성해야 하는 경우가 있습니다. 오늘은 기준에 따라 목록을 생성하는 방법을 보여 드리겠습니다. 이 세션에서는 Excel 365를 사용하고 있지만 이 버전을 사용하는 것이 좋습니다. 자유롭게 사용하세요.
먼저 예제의 기반이 되는 데이터 세트에 대해 알아보겠습니다.
여기에 차량과 함께 서로 다른 위치에 있는 여러 사람들의 데이터 세트가 있습니다. 이 데이터를 사용하여 기준에 따라 목록을 구성합니다.
이것은 일을 단순하게 유지하기 위해 더미 데이터가 있는 기본 테이블입니다. 실제 시나리오에서는 훨씬 더 크고 복잡한 데이터 세트를 만날 수 있습니다.
연습 워크북
다음 링크에서 연습 워크북을 다운로드할 수 있습니다.
기준에 따라 목록 생성
예를 들어, 우리는 지역에 따라 사람들의 목록을 만들 것입니다.
작은 데이터 세트이기 때문에 4개의 지역이 있다는 것을 알고 있습니다. 지역 이름을 저장하고 지역에 따라 목록을 찾습니다.
1. INDEX-SMALL 조합을 사용하여 목록 생성
여기에 목록이 필요하므로 수식은 테이블에서 여러 값을 검색하는 수식이어야 합니다. 해당 작업을 위해 INDEX 조합을 사용할 수 있습니다. 및 작은 기능.
이러한 기능을 알아보려면 INDEX, SMALL. 문서를 확인하세요.
이 두 가지와 함께 몇 가지 도우미 함수가 필요합니다. IF , 행 및 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),"")
여기에서 모든 기능에는 목적이 있습니다. 색인 함수는 B2:B12 배열에서 값을 반환합니다. (이름 열) 및 큰 SMALL 부분은 가져올 행 번호를 제공합니다.
만약, 작은 기준이 일치하는지 여부를 확인하고 ROW 함수는 열의 셀을 반복합니다.
그런 다음 외부 ROW SMALL의 k번째 값을 나타냅니다. 기능. 이 함수는 함께 행 번호와 INDEX 를 반환합니다. 결과를 반환합니다.
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을 빼는 목적은 적절한 행 번호로 채널화하는 것입니다. 앞서 우리는 마침내 그것을 했고, 여기서는 더 일찍 그것을 하고 추가 작업을 진행합니다.
다른 기준에 대한 공식을 작성하여 목록을 완성하십시오.
자세히 알아보기:Excel에서 셀 안에 목록을 만드는 방법(3가지 빠른 방법)
2. AGGREGATE 함수를 사용하여 목록 생성
Excel은 AGGREGATE 라는 함수를 제공합니다. 다양한 작업을 수행하는 데 사용할 수 있습니다. 여기에서 함수를 사용하여 기준에 따라 목록을 생성할 수 있습니다.
집계 함수는 AVERAGE, COUNT, MAX 등과 같은 집계 계산을 반환합니다.
AGGREGATE 구문 기능은 다음과 같습니다.
AGGREGATE(function_number,behavior_options, range) 기능 번호: 이 숫자는 어떤 계산을 해야 하는지 지정합니다.
동작_옵션: 숫자를 사용하여 설정합니다. 이 숫자는 함수가 작동하는 방식을 나타냅니다.
범위: 집계하려는 범위입니다.
집계 함수는 여러 작업을 수행하므로 그 안에 함수의 수가 미리 정의되어 있습니다. 자주 사용하는 몇 가지 기능 번호를 나열합니다.
| 함수 | 함수 번호 |
|---|---|
| 평균 | 1 |
| COUNT | 2 |
| COUNTA | 3 |
| 최대 | 4 |
| 최소 | 5 |
| 제품 | 6 |
| 합 | 9 |
| 대형 | 14 |
| 작음 | 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를 사용했습니다. . 색인 수식의 뒷부분에서 찾은 일치 항목을 기반으로 값을 반환하는 배열을 보유합니다.
15를 사용한 것을 볼 수 있습니다. function_number로 집계 . 위의 표에서 15를 볼 수 있습니다. 작은 요청 기능 작동. 이제 공감할 수 있습니까?
예, INDEX-SMALL을 실행했습니다. AGGREGATE 방식의 공식 기능.
6 오류 값 무시를 나타내는 동작 옵션의 경우 .
나머지 값에 대한 공식을 작성하십시오.
유사한 수치
- Excel에서 할 일 목록을 만드는 방법(3가지 쉬운 방법)
- Excel에서 메일링 리스트 만들기(2가지 방법)
- Excel에서 번호 매기기 목록을 만드는 방법(8가지 방법)
3. INDEX-MATCH-COUNTIF를 사용하여 고유 목록 생성
기준에 따라 고유한 목록을 만들 수 있습니다. 이를 위해 INDEX 조합을 사용할 수 있습니다. , 매치 , 및 COUNTIF .
COUNTIF 단일 조건을 충족하는 범위의 셀을 계산합니다. 그리고 매치 범위에서 조회 값의 위치를 찾습니다. 이러한 기능에 대한 자세한 내용은 다음 문서를 참조하세요. 매치, 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_array로 0을 제공했습니다. lookup_range의 경우 IF를 사용했습니다. COUNTIF를 포함하는 부분 . 따라서 이 부분은 0이 발견되는 한 값을 반환합니다. 여기의 값은 INDEX의 행 번호로 작동합니다. .
아래로 드래그하면 고유한 값을 모두 찾을 수 있습니다.
CTRL+SHIFT + ENTER 를 활용하는 것을 잊지 마십시오. 수식을 실행합니다.
이것은 고유한 목록을 생성하기 위한 접근 방식에 대한 명예로운 언급이었습니다. 이 도움말을 따라 기준에 따른 고유 목록 생성에 대해 알아보세요. .
4. FILTER 함수를 사용하여 기준에 따라 목록 생성
Excel 365를 사용하는 경우 FILTER라는 단일 내장 함수로 작업을 수행할 수 있습니다. .
필터 함수는 주어진 기준에 따라 데이터 범위를 필터링하고 일치하는 레코드를 추출합니다. 이 기능에 대해 알아보려면 FILTER 문서를 방문하세요. .
이제 공식은 다음과 같습니다.
=FILTER($B$2:$B$12,$C$2:$C$12=G$2)
B2:B12 필터링할 배열입니다. 그런 다음 목록을 생성할 항목에 따라 조건을 제공했습니다.
여기에서 수식을 아래로 드래그할 필요가 없습니다. 한 번에 모든 값을 제공하고 목록을 채울 것입니다.
자세히 알아보기: 엑셀에서 알파벳순으로 나열하는 방법(3가지 방법)
결론
오늘은 그게 다야. 기준에 따라 목록을 생성하는 몇 가지 방법을 나열했습니다. 이 정보가 도움이 되기를 바랍니다. 이해하기 어려운 부분이 있으면 언제든지 의견을 말하십시오. 여기에서 놓친 다른 방법을 알려주십시오.
추가 자료
- Excel에서 글머리 기호 목록을 만드는 방법(9가지 방법)
- Excel에서 쉼표로 구분된 목록 만들기(5가지 방법)
- Excel에서 가격표를 만드는 방법(단계별 지침)