엑셀 다중조건(여러 개 조건 만족) 값 찾기

 

오늘은 엑셀을 쓸 때 자주 사용하는 다중조건(여러 개 조건 만족)에 대해서 알려드리고자 합니다.

A조건과 B조건을 모두 만족하는 값을 불러오기 위해 사용하는 방법입니다.

 

먼저 예제를 볼까요?

엑셀 다중조건(여러 개 조건 만족) 값 찾기

 

이번 우리의 목표는 명부데이터 시트(우측)에 소유자와 지번이 일치하는 사람의 감정평가금액(좌측시트 H열)을 Q열에 입력하는 것입니다.

 

예를 들어 소유자가 유 ○ ○ 이면서 1-1의 지번의 ○ ○감정평가 법인의 감정금액을 토지조서(좌측)에서 찾아서 명부 데이터(우측)에 입력하고 싶은 것이죠.

 

어떻게 하면 될까요?

 

크게 두 가지 방법이 있습니다

 

첫째, Vlookup 사용

둘째, Sumproduct 사용

 

1. Vlookup

먼저, Vlookup을 사용하는 법에 대해서 확인해 보도록 하겠습니다.

 

Vlookup은 찾고자 하는 값을 기준점(맨 왼쪽 열)과 일치하는 값으로부터 n번째 떨어진 열의 값을 찾아주는 함수입니다.

 

이를 위해서는 토지조서(좌측)와, 명부 데이터(우측)를 가공해야 하죠.

 

먼저 우리는 소유자와 지번을 모두 만족하는 것을 조건으로 결정했으므로, '=지번&소유자'의 방식으로 가장 왼쪽(A열)에 입력한 후, 채우기 핸들로 끌어줍니다.

 

 

토지조서(좌측)와 명부 데이터(우측) 모두 동일하게 작업해 줍니다.

 

 

그럼 토조지서 시트와(좌측) 명부 데이터 시트(우측)의 기준점(A열)이 같아졌으므로,

Vlookup함수를 사용해 줍니다.

통상 Vlookup은 많은 분들이 즐겨 사용하는 필수 함수니 여기서는 생략하고 넘어가겠습니다.

 

 

Vlookup완료 후 채우기 핸들로 값을 다 입력한 후의 모습입니다.

*Vlookup 함수의 수식은 수식입력줄을 참고해 주세요

 

2. Sumproduct

그럼 둘째 방법인 Sumproduct를 살펴볼까요?

 

Sumproduct를 사용하면 데이터를 가공해 줄 필요가 없습니다.

=sumproduct(('[통합 문서1]토지조서(○ ○감정평가법인)'!$D$3:$D$7=$H6)) 이 부분을 먼저 살펴볼게요

 

위 함수를 해석하면 토지조서 시트(왼쪽)의 D3부터 D7열까지의 값이(지번) 우측 시트의 H6값과 일치하는지 확인하는 것입니다.

위 그림에서 D3부터 D7까지 순서대로 첫 번째, 두 번째, 세 번째, 네 번째, 다섯 번째라고 한다면 우측 시트의 H6의 값은

세 번째에 위치한 "2-1"과 일치하므로 함수창에 보면 { FALSE; FALSE; TRUE; FALSE; FALSE }

즉, 세 번째 값만 일치한다라고 알려주는 것을 확인할 수 있습니다.

 

 

그럼 =sumproduct(('[통합 문서1]토지조서(○ ○감정평가법인)'!$I$3:$I$7=$C6))을 넣으면 어떻게 될까요?

명부 시트(우측)의 김○ ○과 일치하는 토지조서(좌측)의 값이 세 번째, 네 번째 위치하는 것을 볼 수 있습니다.

그렇다면 결괏값은 { FALSE; FALSE; TRUE; TRUE; FALSE } 가 나오겠죠

 

여기서 한 가지 알고 가야 할 내용이 있습니다

엑셀에서 TRUE = 1로, FALSE = 0으로 인식합니다.

따라서,

=sumproduct(('[통합 문서1]토지조서(○ ○감정평가법인)'!$D$3:$D$7=$H6)*('[통합 문서1]토지조서(○ ○감정평가법인)'!$I$3:$I$7=$C6)) 를 하면 어떻게 될까요?

 

맞습니다!

{ FALSE; FALSE; TRUE; FALSE; FALSE } * { FALSE; FALSE; TRUE; TRUE;  FALSE } 가 되겠죠?

즉, { 0; 0; 1; 0; 0 } * { 0; 0; 1; 1; 0 } 이 됩니다.

그럼 결괏값이 { 0; 0; 1; 0; 0 } 가 되겠죠..

*같은 번째 위치한 값끼리 곱해주는 겁니다. { 0*0; 0*0; 1*1; 0*1; 0*0 }

 

이걸 해석해 보면 "소유자가 김○ ○ 이면서 지번이 2-1인 사람은 세 번째에 위치한다"입니다.

 

그럼 우리는 세 번째에 위치하는 사람의 평가금액을 가져오면 되겠죠?

그럼 위 Sumproduct함수에 *('[통합 문서1]토지조서(○ ○감정평가법인)'!$H$3:$H$7) 를 추가하면 끝입니다.

 

위 그림을 보시면

=sumproduct(('[통합 문서1]토지조서(○ ○감정평가법인)'!$D$3:$D$7=$H6)*('[통합 문서1]토지조서(○ ○감정평가법인)'!$I$3:$I$7=$C6)*('[통합 문서1]토지조서(○ ○감정평가법인)'!$H$3:$H$7))

라는 함수를 입력하게 되면

 

그 결괏값으로

{ FALSE; FALSE; TRUE; FALSE; FALSE } * { FALSE; FALSE; TRUE; TRUE;  FALSE } * { 190000000; 44400000; 295750000; 295750000; 851000000 }이 나오게 될 겁니다.

 

그 말은 { 0; 0; 1; 0; 0 } * { 0; 0; 1; 1; 0 } * { 190000000; 44400000; 295750000; 295750000; 851000000 }

다시 말하면 { 0; 0; 1; 0; 0 } * { 190000000; 44400000; 295750000; 295750000; 851000000 }

295750000 (세 번째 위치한 값)라는 결과가 나타납니다.

 

 

이제 채우기 핸들을 통하여 마지막 부분까지 채워주면 아래와 같이 원하는 결괏값을 얻을 수 있게 됩니다.

 

 

짜잔!

 

어떠셨나요?

PRODUCT와 SUMPRODUCT는 배열을 이용하는 함수라 자칫 어려울 수 있지만 여러 번 반복해서 사용하다 보면 생각보다 편리하게 활용할 수 있다는 사실!

 

많이 애용해 주세요^^

엑셀 다중조건(여러 개 조건 만족) 값 찾기