필요한 시간에 데이터를 추출하는 것은 모든 스프레드시트를 사용하는 주요 요소이며 Excel도 예외는 아닙니다. Excel에서 데이터를 표 형식으로 저장하여 추출합니다. 오늘은 여러 기준에 따라 테이블에서 데이터를 추출하는 방법을 보여 드리겠습니다.
먼저 예제의 기반이 되는 데이터 세트에 대해 알아보겠습니다.
여기에 장르와 주연 배우와 개봉 연도가 있는 영화 목록이 포함된 테이블이 있습니다. 이 데이터 세트를 사용하여 여러 기준에 따라 데이터를 검색합니다.
이것은 일을 단순하게 유지하기 위한 기본 테이블입니다. 실제 시나리오에서는 훨씬 더 크고 복잡한 데이터세트를 접할 수 있습니다.
연습 워크북
다음 링크에서 연습 워크북을 다운로드할 수 있습니다.
여러 기준에 따라 테이블에서 데이터 추출
예를 들어 여기에서는 장르와 배우 이름을 기준으로 제공하고 이러한 기준에 따라 영화 이름을 추출합니다.
1. 단일 값 반환
이 섹션에서는 단일 값을 반환합니다. 기준에 따라 하나의 값만 가져옵니다. 탐색해 봅시다.
나. INDEX-MATCH 배열 수식
INDEX 조합을 사용할 수 있습니다. 및 매치 기능. 색인 범위의 지정된 위치에 있는 값을 반환합니다. 매치 범위에서 조회 값의 위치를 찾습니다.
이러한 기능을 알아보려면 다음 문서를 확인하세요. INDEX, MATCH.
기준 값을 설정해 보겠습니다. 당분간 장르 스릴러 배우의 휴 잭맨 필드.
공식은 다음과 같습니다.
=INDEX($B$4:$B$19,MATCH(1,($H$4=$C$4:$C$19)*($H$5=$D$4:$D$19),0)) B4:B19 값을 추출할 배열입니다. 그리고 MATCH 함수는 가져올 행 번호를 설정합니다.
lookup_value로 1을 제공한 것을 볼 수 있습니다. MATCH 내 . 그리고 lookup_array 기준 일치 논리의 곱셈을 통해 생성됩니다.
$H$4=$C$4:$C$19까지 장르 및 $H$5=$D$4:$D$19 를 확인했습니다. 배우 이름입니다.
곱셈 결과의 배열 중 1을 찾습니다. 행 번호와 INDEX를 반환합니다. 함수는 영화 이름을 반환합니다.
배열 수식이므로 CTRL + SHIFT + ENTER를 사용해야 합니다. 실행합니다.
기준 값을 변경하면 업데이트된 값을 찾을 수 있습니다.
자세히 알아보기: Excel의 셀에서 특정 데이터를 추출하는 방법(예제 3개)
Ⅱ. INDEX-MATCH 비배열 수식
INDEX 를 결합하여 비배열 수식을 만들 수 있습니다. 및 매치 .
먼저 공식을 봅시다
=INDEX($B$4:$B$19,MATCH(1,INDEX(($H$4=$C$4:$C$19)*($H$5=$D$4:$D$19),0,1),0)) 여기에서 몇 가지 INDEX를 사용했음을 알 수 있습니다. 거기에 기능. 외부 INDEX 함수는 내부자가 행 번호를 감지하는 데 도움이 되는 추출 작업을 수행합니다.
내부 INDEX, 우리는 기준 값을 확인했습니다. 여기서 INDEX 내에서 두 개의 논리 연산을 곱합니다. 배열 참조 역할을 합니다.
ENTER 수식을 실행합니다.
기준 값을 자유롭게 수정하면 업데이트된 값을 찾을 수 있습니다.
자세히 알아보기: 기준에 따라 Excel에서 데이터를 추출하는 방법(5가지 방법)
Ⅲ. INDEX-MATCH-IF 조합
이전 섹션에서 조건을 확인하고 곱하여 함께 작동하도록 했습니다. IF를 사용하여 곱셈을 남길 수 있습니다. 기능.
만약 논리적 테스트를 실행하고 부울 값을 반환합니다(TRUE 또는 거짓 ) 결과로. 기능에 대해 알아보려면 이 IF 기사를 방문하십시오.
공식은 다음과 같습니다.
=INDEX($B$4:$B$16,MATCH(1,IF($C$4:$C$16=$H$4,IF($D$4:$D$16=$H$5,1)),0))
여기에 일치하는 두 가지 조건이 있으므로 두 개의 IF . 중첩된 IF로 작동합니다. (하나 안에 다른 하나). 외부 IF 함수는 첫 번째 조건(순서에 관계없이)과 두 번째 조건(내부 IF)을 확인합니다. if_true_value입니다. 첫 번째 IF .
CTRL+SHIFT+ENTER를 사용해야 합니다. 수식을 실행합니다.
기준 값을 변경하면 업데이트된 값을 찾을 수 있습니다.
자세히 알아보기: 엑셀 시트에서 데이터를 추출하는 방법(6가지 효과적인 방법)
IV. 조회 기능
LOOKUP을 사용할 수 있습니다. 기준에 따라 데이터를 추출하는 작업을 수행하는 기능입니다.
조회 함수는 범위에서 일치하는 조회를 수행하고 해당 값을 반환합니다. 자세한 내용은 Microsoft 지원을 방문하세요. 사이트.
이제 공식을 살펴보겠습니다.
=LOOKUP(2,1/($C$4:$C$19=$H$4)/($D$4:$D$19=$H$5),($B$4:$B$19))
여기에서 lookup_value로 2를 설정했습니다. . 그리고 1로 나누는 형태의 두 가지 논리 연산은 lookup_vector입니다. .
여기에서 1을 TRUE/FALSE 배열로 나눕니다. 값($C$4:$C$19=$H$4 ), 다음 TRUE/FALSE의 다른 배열 값($D$4:$D$19=$H$5 ). 1 또는 #DIV/0!을 반환합니다. 오류.
lookup_value 범위의 숫자 값과 일치시키도록 공식에 지시합니다. 일치 항목이 발견되면 값이 배열 B4:B19에서 파생됩니다. .
CTRL + SHIFT + ENTER를 누를 필요가 없습니다. 실행합니다.
기준 값을 변경하여 수식이 다른 값에 대해 완벽하게 작동하는지 여부를 확인하십시오.
lookup_value로 2를 사용했습니다. . 1부터 시작하는 모든 숫자가 될 수 있습니다.
자세히 알아보기: 이미지에서 Excel로 데이터를 추출하는 방법(빠른 단계 사용)
유사한 수치
- 텍스트 파일을 Excel로 변환하는 VBA 코드(7가지 방법)
- Excel VBA:웹사이트에서 자동으로 데이터 가져오기(2가지 방법)
- 보안 웹사이트에서 Excel로 데이터를 가져오는 방법(빠른 단계 사용)
- VLOOKUP을 사용하여 한 Excel 워크시트에서 다른 Excel 워크시트로 데이터를 자동으로 전송
- 파이프 구분 기호를 사용하여 Excel을 텍스트 파일로 변환하는 방법(2가지 방법)
2. 여러 값 반환
나. INDEX-SMALL 조합
기준에 따라 여러 데이터를 추출하기 위해 다른 기능 조합을 사용할 수 있습니다. 조합 중 하나는 INDEX 입니다. – 작은 조합.
작은 함수는 값으로 순위가 매겨진 목록에서 위치에 따라 값을 반환합니다. 자세한 내용은 작은 확인 기사.
이 두 가지와 함께 몇 가지 도우미 함수가 필요합니다. IF , 행 및 IFERROR . 자세한 내용은 IF, ROW, IFERROR 문서를 확인하세요.
공식은 다음과 같습니다.
=IFERROR(INDEX($B$2:$B$17,SMALL(IF(($C$2:$C$17=$H$2)*($D$2:$D$17=$H$3), ROW($B$2:$B$17)),ROW(1:1))-1,1),"")
여기에서 모든 기능에는 목적이 있습니다. 색인 함수는 B2:B17 배열에서 값을 반환합니다. 큰 작은 부분은 가져올 행 번호를 제공합니다.
만약, 작은 기준이 일치하는지 여부를 확인합니다. 일치시킬 두 가지 조건이 있으므로 기준을 확인하기 위해 두 논리 연산을 모두 곱했습니다. 그리고 ROW 함수는 열의 셀을 반복합니다.
그런 다음 외부 ROW SMALL의 k번째 값을 나타냅니다. 기능. 이 함수는 함께 행 번호와 INDEX 를 반환합니다. 결과를 반환합니다.
IFERROR 공식에서 발생할 수 있는 오류를 처리합니다. 오류에 대해 빈 셀을 제공하도록 설정했습니다.
아래로 드래그하면 기준과 일치하는 모든 값이 표시됩니다.
자세히 알아보기: Excel 수식을 사용하여 목록에서 데이터를 추출하는 방법(5가지 방법)
Ⅱ. INDEX-AGGREGATE 조합
집계 Excel의 기능을 사용하면 다양한 작업을 수행할 수 있습니다. 여러 작업을 위한 하나의 기능. 이 함수를 사용하여 여러 기준에 따라 여러 값을 반환할 수 있습니다.
AGGREGATE 함수에 대해 조금 더 알아보겠습니다. 함수는 AVERAGE, COUNT, MAX 등과 같은 집계 계산을 반환합니다.
AGGREGATE 구문 기능은 다음과 같습니다.
AGGREGATE(function_number,behavior_options, range) 기능 번호: 이 숫자는 어떤 계산을 해야 하는지 지정합니다.
동작_옵션: 숫자를 사용하여 설정합니다. 이 숫자는 함수가 작동하는 방식을 나타냅니다.
범위: 집계하려는 범위입니다.
집계 함수는 여러 작업을 수행하므로 그 안에 함수의 수가 미리 정의되어 있습니다. 자주 사용하는 몇 가지 기능 번호를 나열합니다.
| 함수 | 함수 번호 |
|---|---|
| 평균 | 1 |
| 카운트 | 2 |
| COUNTA | 3 |
| 최대 | 4 |
| 최소 | 5 |
| 제품 | 6 |
| 합계 | 9 |
| 대형 | 14 |
| 작음 | 15 |
기능에 대해 자세히 알아보려면 Microsoft 지원을 방문하세요. 사이트.
이제 공식을 보겠습니다. INDEX 및 집계 .
=IFERROR(INDEX($B$2:$B$17,AGGREGATE(15,6,IF(($C$2:$C$17=$H$2)*($D$2:$D$17=$H$3), ROW($B$2:$B$17)),ROW(1:1))-1,1),"")
여기에서는 15를 사용했습니다. function_number로 집계 . 위의 표에서 15를 볼 수 있습니다. 작은 요청 기능 작동. AGGREGATE를 사용하는 것과 별개로 볼 수 있습니다. (및 기능 번호 및 동작 옵션 번호) 공식은 이전 INDEX와 정확히 동일합니다. –작은 공식.
메커니즘은 동일합니다. INDEX AGGREGATE 에서 찾은 일치 항목을 기반으로 값을 반환하는 배열을 보유합니다. 공식의 일부입니다.
6 오류 값 무시를 나타내는 동작 옵션의 경우 .
아래로 드래그하면 기준과 일치하는 모든 값이 표시됩니다.
CTRL+SHIFT+ENTER를 사용해야 합니다. 수식을 실행합니다.
자세히 알아보기: 단일 기준에 따라 Excel에서 여러 값 반환(3가지 옵션)
Ⅲ. INDEX-MATCH-COUNTIF 조합
여러 기준에 따라 여러 값을 반환하려면 INDEX 조합을 사용할 수 있습니다. , 매치 , 및 COUNTIF .
COUNTIF 단일 조건을 충족하는 범위의 셀을 계산합니다. 이 기능에 대해 자세히 알아보려면 다음 문서를 방문하세요. COUNTIF .
공식은 다음과 같습니다.
=IFERROR(INDEX($B$4:$B$19,MATCH(0,COUNTIF(H5:$H$5,$B$4:$B$19)+IF($C$4:$C$19<>$H$4,1,0)+IF($D$4:$D$19<>$H$5,1,0),0)),"")
MATCH 내 함수에서 lookup_array로 0을 제공했습니다. 및 lookup_range IF를 사용했습니다. COUNTIF를 포함하는 부분 .
여기에서 COUNTIF 함수는 이미 가져온 값을 제외합니다. 그리고 두 개의 IF 함수는 두 가지 조건을 확인합니다. We have added these functions so that they together form the lookup_range .
The MATCH portion returns the value as long as 0 is found. The value here works as the row number for INDEX .
Drag it down, you will get all the values that match the criteria.
자세히 알아보기: How to Extract Data from Cell in Excel (5 Methods)
IV. FILTER Function
If you are using Excel 365, then you can perform the task with a single built-in function called FILTER .
The FILTER function filters a range of data based on given criteria and extracts matching records. To know about the function, visit this article:FILTER.
Let’s explore the formula
=FILTER(B4:B19,(H4=C4:C19)*(H5=D4:D19))
B4:B19 is the array that is to be filtered. Then we have provided the condition, based on what we will extract values. Since we need to check two criteria, we have multiplied them.
Here you will not need to drag down the formula, at one go this will provide all the values and fulfill the list.
자세히 알아보기: Extract Filtered Data in Excel to Another Sheet (4 Methods)
결론
That’s all for today. We have listed several methods to extract data from table based on multiple criteria. Hope you will find this helpful. Feel free to comment if anything seems difficult to comprehend. Let us know any other approaches that we have missed here.
Further Readings
- How to Extract Data from Excel to Word (4 Ways)
- Extract Data from One Sheet to Another Using VBA in Excel (3 Methods)
- How to Pull Data from Multiple Worksheets in Excel VBA
- Transfer Data from One Excel Worksheet to Another Automatically
- How to Pull Data From Another Sheet Based on Criteria in Excel
- Excel Macro:Extract Data from Multiple Excel Files (4 Methods)
- How to Extract Year from Date in Excel (3 Ways)