두 개 이상 조건을 만족하는 값을 찾는 여러가지 방법

 

자료를 작성하다 보면 자료별로 자료가 다른지 비교하거나 다른데서 자료를 가져와야 할 때가 있습니다.

 

이 때도 Vlookup함수를 사용할 수도 있고, SumProduct, SumIfs, Sum+If 배열 수식 등 다양한 방법을 적용할 수 있습니다. 

 

아래와 같이 왼쪽과 오른쪽에 지역별 월별 판매량 자료가 있고,

오른쪽에는 인원수 데이터만 있다고 할 때 왼쪽에 맞는 값을 찾아서 붙일 경우를 생각해 볼 수 있습니다.

  ※ 일반적으로는 시트를 달리하겠지만 이해가 쉽도록 왼쪽과 오른쪽에 배치했습니다.

vlookup and index(exam).xlsx

0.01MB

 

 

 

1. 왼쪽과 오른쪽의 판매량 검증하기

Vlookup함수는 조건에 맞는 값을 찾아주는 함수이기 때문에 이 함수를 맨 먼저 사용해야 하지만 조건 2개를 지정할 수 없기 때문에 새로운 열을 추가해야 하기 때문에 맨 나중에 설명하겠습니다.

 

가. SumIfs함수를 이용하는 경우

SumIfs함수는 여러 가지 조건에 부합하는 범위의 합계를 구하는 함수이기 때문에 매우 적절합니다. 그러나, 조건에 부합하는 데이터가 여러 개 있을 경우 합계를 반환한다는 점을 주의해야 합니다. 그러나, 위 경우는 조건에 부합하는 데이터가 하나뿐이기 때문에 문제 없습니다.

 

 

수식은

=SUMIFS($L$2:$L$10,$J$2:$J$10,A2,$K$2:$K$10,B2) 입니다.

더할 범위, 조건 범위1, 조건1, 조건 범위2, 조건2 순으로 입력하므로,

더할 범위는 오른쪽의 판매량인 L2:L10이 되는데, 절대참조형식으로 입력해야 하며,

 

조건 범위1은 오른쪽의 지역에 해당하는 J2:J10인데 마찬가지로 절대참조형식으로 입력해야 합니다.

조건1은 왼쪽의 지역명으로서 줄 별로 비교해야 하므로 첫번째 행은 A2가 되며, 내려갈 때 자동으로 변해야 하므로 상대참조형식으로 지정해야 합니다.

 

이런 식으로 조건 범위2는 K$2:$K$10이 되고,

조건2는 B2가 됩니다.

 

따라서 D2셀의 수식은 오른쪽 범위에서 A2셀 및 B2셀과 일치하는 판매량의 합계를 가져오는 것인데, L2셀의 값이 됩니다.

 

 

나. SumProduct 함수를 이용하는 경우

SumProduct 함수를 이용하면 2가지 이상의 조건에 만족하는 값을 *기호를 연결하여 추출한 후 합계를 구해주는데, 마찬가지로 아래 경우는 해당하는 값이 하나이기 때문에 합계를 해도 하나뿐이 안됩니다.

 

수식은 =SUMPRODUCT(($J$2:$J$10=A2)*($K$2:$K$10=B2)*$L$2:$L$10)으로

조건 2개가 * 기호로 연결되어 있고, 조건에 일치하면 1을 반환하므로 여기에 더할 범위를 곱하면 조건이 일치하는 더할 범위만이 남습니다. 그런 다음 합계를 반환해줍니다.

 

($J$2:$J$10=A2)*($K$2:$K$10=B2)*$L$2:$L$10까지 마우스를 끌어서 범위로 잡은 후 F9키를 누르면

오른쪽에서 첫번째 줄만 조건에 부합하므로 1222가 구해지고, 나머지는 조건에 부합하지 않기 때문에 모두 0입니다. 따라서, SumProduct의 값은 1222가 됩니다.

 

 

다. Sum + If 배열 수식을 이용하는 경우

SumProduct함수와 비슷한데,

수식이 =SUM(IF(($J$2:$J$10=A2)*($K$2:$K$10=B2),$L$2:$L$10))로서

더할 범위인 L2:L10을 *로 연결하지 않고, If함수를 사용했으므로 참일 때의 값에 입력한다는 점만 다릅니다.

 

그리고, Microsoft 365를 제외하고는 CSE(Ctrl + Shift + Enter)키를 입력해야 하며, 이 키를 입력하면 수식 입력줄에 중괄호({ })가 수식 바깥 쪽에 생깁니다.

 

그러나 Microsoft 365의 경우는 엔터키를 눌러 수식을 입력하면 중괄호 표시가 생기지 않습니다.

 

 

라. Index와 Match 함수를 결합해서 이용하는 경우

Match함수를 사용할 때 인수가 찾을 값(lookup_value), 찾을 범위(lookup_range), 일치 형태(match_type)인데,

찾을 값을 &(결합 연산자)로 연결하고, 찾을 범위도 &로 연결해서 지정할 수 있습니다. 그리고, 정확한 일치하는 값을 찾아야 하므로

Match함수를 이용한 수식은 MATCH(A2&B2,$J$2:$J$10&$K$2:$K$10,0)이 됩니다. 그러면 찾는 값의 위치를 반환하는데 첫번째 줄이 일치하므로 1이 반환됩니다.

 

MATCH(A2&B2,$J$2:$J$10&$K$2:$K$10,0)를 마우스로 범위를 잡은 후 F9키를 눌러 1이라는 값을 확인할 수 있습니다.확인한 후에는 Esc키를 눌러 원래 수식대로 돌립니다.

 

그리고 나서, 판매량에서 해당하는 값을 찾으려면 Index함수를 사용하면 됩니다. Index함수는 범위, 행수, 열수가 인수인데, 열수는 생략가능하므로 match함수와 연결하면

=INDEX($L$2:$L$10,MATCH(A2&B2,$J$2:$J$10&$K$2:$K$10,0))이 됩니다.

 

마. Vlookup함수를 이용하는 경우

Vlookup함수에서 찾을 범위를 Match함수처럼 &로 연결해서 지정해서 Vlookup함수를 

=VLOOKUP(A2&B2,$J$2:$J$10&$K$2:$K$10,3,0)라고 입력하면 #REF! 에러가 발생하므로 이렇게 하면 안되고, 찾을 조건과 찾을 범위에 해당하는 값을 열을 추가해서 새로 만들어야 합니다.

 

그런데, Vlookup함수의 경우는 찾을 값이 찾을 범위의 맨 앞에 있어야 하므로 J열 왼쪽에 열을 추가하고,

 

필드 값을 지역과 월을 결합한 값으로 입력합니다. 결합할 때는 & 연산자를 사용하며, J2셀에 =K2&L2라고 입력하고, J2셀의 채우기 핸들을 더블 클릭해서 J10셀까지 입력합니다.

 

그리고, Vlookup함수의 table_array를 J2:M10으로 바꾸고, 판매량이 네번째 있으므로 col_index_num을 4로 바꾸면 됩니다.

=VLOOKUP(A2&B2,J2:M10,4,0)

 

lookup_value는 그대로 A2&B2로 둬도 됩니다.

 

바. C열의 값과 D열의 값 비교하기

비교할 때는 비교 연산자인 =을 사용할 수도 있지만, 숫자끼리 비교할 때는 -(빼기)로 하는 것이 더 직관적입니다.

 

i2셀에 =c2-d2라고 입력하고

D2셀부터 i2셀까지 선택한 후 i2셀의 채우기 핸들을 더블 클릭해서 i10셀까지 복사합니다.

 

그러면 서울 2월과 강원 3월이 1과 -1이 차이나는 것을 알 수 있습니다.

 

데이터가 많을 때는 필터를 건 후

 

0인 것의 체크를 해제하면

 

값이 다른 것만 표시되므로 더욱 쉽게 다른 데이터를 찾을 수 있습니다.

 

 

2. 왼쪽에 오른쪽의 인원수 추가하기

i열 다음에 인원수를 추가할 수 있도록 필터를 해제한 후 J열과 K열을 선택한 후

마우스 오른쪽 버튼을 누르고 삽입 메뉴를 누릅니다.

 

J1셀에 인원수라고 입력하고,

J2셀에 Vlookup 함수를 이용해 

=VLOOKUP(A2&B2,$L$2:$P$10,5,0)라고 입력하면 A2셀과 B2셀을 결합 한 값을 L열에서 찾아 5번째 값을 가져오는 것이므로 인원수를 쉽게 구할 수 있습니다.

 

이제 수식을 채우기 위해 J2셀의 채우기 핸들을 더블 클릭하면 됩니다.

 

vlookup and index(final).xlsx

0.01MB