엑셀 다중조건에 맞는 값 모두 가져오기 | 다중조건 검색, 다중조건 필터링

엑셀 다중조건에 맞는 값 모두 가져오기

다중조건에 맞는 값 가져오기

1. 다중조건에 맞는 값 모두 가져오기

1.1. INDEX, MATCH 함수 활용하기

다중조건에 맞는 값을 가져오기 위해 INDEX와 MATCH 함수를 활용할 수 있습니다. INDEX 함수는 범위에서 특정 위치의 값을 찾아내고, MATCH 함수는 특정 값을 범위에서 찾아 위치를 반환합니다.

여러 개의 조건에 맞는 값을 찾기 위해서는 INDEX 함수 안에 MATCH 함수를 조합하여 사용하면 됩니다. 예를 들어, “6월 5일”의 “제주”의 신규 확진자 수를 찾고자 할 때, 다음과 같은 수식을 사용할 수 있습니다.

=INDEX(D:D, MATCH(H2&H3, A:A&B:B, 0))

위의 수식에서 A:A와 B:B에 있는 데이터를 조합하여 1차원 배열을 생성하고, MATCH 함수를 사용하여 해당 조건의 데이터가 몇 번째 행에 위치하는지 찾은 후, INDEX 함수를 통해 신규 확진자 수를 가져옵니다.

1.2. 데이터 열 추가해서 VLOOKUP 사용하기

다른 방법으로는 데이터 열을 추가하여 VLOOKUP 함수를 사용하는 방법이 있습니다. 이 방법은 데이터를 가공하는 단계에서 추가된 열을 활용하여 원하는 조건에 맞는 값을 찾아내는 방법입니다.

예를 들어, 위의 예제에서는 “6월 5일”의 “제주”의 신규 확진자 수를 찾고자 할 때, 데이터 테이블에 “조건1″과 “조건2” 두 개의 열을 추가하여, VLOOKUP 함수를 사용하여 해당 조건에 맞는 값을 찾아냅니다.

1.3. 피벗테이블 형태로 가공해서 GETPIVOTDATA 함수 사용하기

또 다른 방법은 데이터를 피벗테이블 형태로 가공한 후, GETPIVOTDATA 함수를 사용하는 방법입니다. 피벗테이블을 사용하면 다양한 조건에 대한 데이터를 쉽게 분석할 수 있습니다.

GETPIVOTDATA 함수는 피벗테이블에서 원하는 조건에 맞는 값을 가져올 수 있는 함수입니다. 피벗테이블에서 원하는 조건을 설정한 후, GETPIVOTDATA 함수를 사용하여 해당 조건에 맞는 값을 가져올 수 있습니다.

2. 다중조건에 맞는 값 모두 가져오기

2.1. INDEX, MATCH 함수 조합하여 찾기

INDEX, MATCH 함수를 조합하여 다중조건에 맞는 값을 찾을 수 있습니다. 이 방법은 조건이 여러 개일 때 유용하게 사용할 수 있는 방법입니다.

예를 들어, “A001″과 “2021”이라는 두 개의 조건에 맞는 값을 찾고자 할 때, 다음과 같은 수식을 사용할 수 있습니다.

=INDEX($E$6:$E$14, MATCH(1, ($B$6:$B$14=”A001″) * ($D$6:$D$14=2021), 0))

3.2. 다중 조건을 만족하는 값 찾기 위한 배열 수식 사용하기

배열 수식을 활용하여 다중 조건을 만족하는 값을 찾을 수도 있습니다. 배열 수식은 여러 개의 조건을 동시에 비교하여 TRUE 또는 FALSE로 반환한 후, 해당 결과를 INDEX 함수와 MATCH 함수와 조합하여 원하는 값을 찾아냅니다.

예를 들어, “A002″이라는 상품코드와 “2020”이라는 적용연도에 해당하는 단가를 찾으려면 다음과 같은 배열 수식을 사용할 수 있습니다.

=INDEX($E$6:$E$14, MATCH(1, ($B$6:$B$14=”A002″) * ($D$6:$D$14=2020), 0))

3.3. 다중 조건을 만족하는 값 찾기 위한 배열 수식과 INDEX, MATCH 함수 조합하여 찾기

배열 수식과 INDEX, MATCH 함수를 조합하여 다중 조건에 맞는 값을 찾을 수도 있습니다. 이 방법은 좀 더 복잡한 조건으로 값을 찾을 때 사용할 수 있는 방법입니다.

예를 들어, “A002″이라는 상품코드와 “2020”이라는 적용연도에 해당하는 단가를 찾으려면 다음과 같은 배열 수식과 INDEX, MATCH 함수를 조합하여 사용할 수 있습니다.

=INDEX($E$6:$E$14, MATCH(C19 & B19, $B$6:$B$14 & $D$6:$D$14, 0))

위의 수식은 상품코드와 적용연도를 비교하여 TRUE 또는 FALSE로 반환하고, 배열 수식을 이용하여 해당 조건에 맞는 값을 가져옵니다.

위의 방법들을 적절히 활용하면 다중조건에 맞는 값을 쉽게 찾아낼 수 있습니다. INDEX, MATCH 함수나 배열 수식을 조합하여 값을 찾는 방법은 조건의 종류와 개수에 따라 다를 수 있으니 필요에 따라 적절한 방법을 선택하여 사용하시면 됩니다.

4. 다중조건에 맞는 값 모두 가져오기

4.1.INDEX, MATCH 함수 조합하여 찾기
4.2.조건에 맞는 모든 값 찾아내기 위한 배열 수식 사용하기
4.3.다중 조건에 맞는 값 찾아내기 위한 배열 수식과 INDEX, MATCH 함수 조합하여 찾기

이번 블로그 포스트에서는 엑셀에서 다중조건에 맞는 값을 찾는 방법에 대해 알아보겠습니다. VLOOKUP 함수로는 한 가지 조건에 맞는 값만을 찾을 수 있기 때문에, INDEX와 MATCH 함수를 조합하거나 배열 수식을 이용하여 다중조건에 맞는 값을 찾아내는 방법을 소개하겠습니다.

4.1. INDEX, MATCH 함수 조합하여 찾기

INDEX와 MATCH 함수를 조합하여 다중조건에 맞는 값을 찾아내는 방법을 알아보겠습니다. 이 방법은 배열 형태의 데이터에서 조건에 맞는 값을 찾을 때 유용합니다.

예를 들어, 다음과 같은 상품정보의 단가를 연도별로 관리하고 있을 때, 상품코드와 연도를 조건으로 주어 특정 연도에 해당하는 단가를 찾고자 합니다.

상품코드 | 적용연도 | 단가
A001 | 2020 | 30,000원
A001 | 2021 | 32,000원
A002 | 2020 | 35,000원
A002 | 2021 | 37,000원

이 때, ‘A002’ 상품의 2020년에 해당하는 단가를 찾으려면 다음과 같이 수식을 사용할 수 있습니다.

=INDEX($C$2:$C$5, MATCH(1, ($A$2:$A$5=”A002″) * ($B$2:$B$5=2020), 0))

이 수식을 셀에 입력하고 Ctrl+Shift+Enter를 누르면 배열 수식으로 입력됩니다. 수식이 제대로 입력되면 배열 수식을 의미하는 중괄호가 수식 양쪽에 붙어 있을 것입니다.

이제 위 수식을 살펴보면, MATCH 함수가 먼저 실행되어 A002인 상품코드와 2020년인 조건을 만족하는 행의 위치(4번째 행)를 찾아냅니다. 그리고 INDEX 함수에 해당 행의 단가를 적용하여 값을 찾아줍니다. 이렇게 해서 ‘A002’ 상품의 2020년 단가인 35,000원을 찾을 수 있습니다.

4.2. 조건에 맞는 모든 값 찾아내기 위한 배열 수식 사용하기

VLOOKUP 함수로는 한 가지 조건에 맞는 값만을 찾을 수 있기 때문에, 배열 수식을 이용하여 조건에 맞는 모든 값을 찾아낼 수 있습니다. 이 방법은 배열 수식을 사용하여 조건에 맞는 값을 TRUE와 FALSE로 바꾸어 찾는 방법입니다.

예를 들어, 상품코드와 연도를 조건으로 주어 특정 연도에 해당하는 모든 상품코드를 찾고자 합니다.

상품코드 | 적용연도 | 단가
A001 | 2020 | 30,000원
A001 | 2021 | 32,000원
A002 | 2020 | 35,000원
A002 | 2021 | 37,000원

이 때, 2020년에 해당하는 모든 상품코드를 찾으려면 다음과 같이 배열 수식을 사용할 수 있습니다.

{=IF(($B$2:$B$5=2020), $A$2:$A$5, “”)}

이제 위 수식을 살펴보면, 조건인 2020년과 일치하는 행의 상품코드를 가져오고, 그렇지 않은 경우에는 빈 문자열(“”)을 반환합니다. 이렇게 하면 조건에 맞는 모든 값을 가져올 수 있습니다.

4.3. 다중 조건에 맞는 값 찾아내기 위한 배열 수식과 INDEX, MATCH 함수 조합하여 찾기

위에서 이야기한 두 가지 방법을 조합하여 다중 조건에 맞는 값을 찾을 수도 있습니다. 이 방법은 INDEX, MATCH 함수를 조합하여 조건에 맞는 행의 위치를 찾고, 그 위치에 해당하는 값을 INDEX 함수로 찾아내는 방법입니다.

예를 들어, 상품코드와 연도를 조건으로 주어 특정 연도에 해당하는 모든 행의 단가를 찾고자 합니다.

상품코드 | 적용연도 | 단가
A001 | 2020 | 30,000원
A001 | 2021 | 32,000원
A002 | 2020 | 35,000원
A002 | 2021 | 37,000원

이 때, 2020년에 해당하는 모든 단가를 찾으려면 다음과 같이 수식을 사용할 수 있습니다.

{=INDEX($C$2:$C$5, MATCH(1, ($B$2:$B$5=2020), 0))}

이제 위 수식을 살펴보면, MATCH 함수가 먼저 실행되어 조건인 2020년과 일치하는 행의 위치(2, 3번째 행)를 찾아냅니다. 그리고 INDEX 함수에 해당 행의 단가를 적용하여 값을 찾아줍니다. 이렇게 해서 2020년에 해당하는 모든 단가를 찾을 수 있습니다.

5. 다중조건에 맞는 값 모두 가져오기

5.1.INDEX, MATCH 함수 활용하여 찾기
5.2.다중 조건에 맞는 값을 찾기 위한 배열 수식 사용하기
5.3.다중 조건에 맞는 값을 찾기 위한 배열 수식과 INDEX, MATCH 함수 조합하여 찾기

이번에는 조금 더 다양한 방법으로 다중조건에 맞는 값을 찾는 방법을 알아보겠습니다. 이 방법들은 위의 방법들보다 좀 더 정교한 조건을 사용하여 값을 찾을 수 있습니다.

6. 다중조건에 맞는 값 모두 가져오기

6.1.INDEX, MATCH 함수를 이용하여 찾기
6.2.다중 조건을 만족하는 값 찾기 위한 배열 수식 사용하기
6.3.다중 조건을 만족하는 값 찾기 위한 배열 수식과 INDEX, MATCH 함수 조합하여 찾기

마지막으로, 보다 다양한 다중조건에 맞는 값을 찾는 방법에 대해 알아보겠습니다. 이 방법들은 위의 방법들보다 더 복잡한 다중조건을 적용할 수 있습니다.

이상으로 다중조건에 맞는 값을 찾기 위해 INDEX, MATCH 함수와 배열 수식을 조합하여 사용하는 방법을 알아보았습니다. 이러한 방법을 통해 엑셀에서 다양한 조건에 맞는 값을 찾아내고 데이터를 분석하는 데 도움이 될 것입니다. 제안된 방법들을 활용하여 보다 효율적으로 데이터를 관리하고 분석하는 데 활용해보시기 바랍니다.

gotsen