여러가지 중 한 가지 조건 일치 검색시 or 대신 배열 사용

1. 문제

아래와 같이 이름과 값이 자료가 있을 때, 이름에 해당하는 값의 합계를 구하려고 합니다.

여러 조건 검색.xlsx

0.01MB

 

2. 해법 1

위와 같은 표에서 이름이 홍길동이거나 장발산에 해당하는 값의 합계를 구하려면

=SUMPRODUCT(($A$3:$A$7=D3)+($A$3:$A$7=D4),$B$3:$B$7)

라고 입력해서 합계를 구합니다.

 

위 수식을 보면 ($A$3:$A$7=D3)+($A$3:$A$7=D4)라고

($A$3:$A$7=D3)과 ($A$3:$A$7=D4)가 +로 연결되어 있는데, 이것은 두 가지 조건 중 하나만 일치해도 된다는 Or 조건입니다.

 

다시 말해 위 수식은 이름이 홍길동이거나, 이무인 경우가 됩니다.

그리고, 값의 범위 $B$3:$B$7에서 조건에 맞는 값을 찾아 합계를 구하는 것입니다.

이때 쉼표(,)를 사용하지 않고 곱하기(*) 기호를 사용하려면 앞의 조건 부분을 다시 괄호로 감싸야합니다.

 

3. 해법 2

이름을 배열로 만들기 위해 이름을 중괄호 안에 넣고, 이름 범위와 =으로 연결하면 합계를 구할 수 있습니다. 이때 값 범위와는 쉼표로 연결하면 안 되고 반드시 *로 연결해야 합니다.

=SUMPRODUCT(($A$3:$A$7={"홍길동","이무기"})*$B$3:$B$7)

 

4. 안 되는 경우 1과 수정

=SUMPRODUCT(($A$3:$A$7={"홍길동","이무기"}),$B$3:$B$7)라고 쉼표로 연결해도 될 듯한데, 안됩니다.

 

따라서, 이 때는 if 함수와 연결해서

=SUMPRODUCT(IF(($A$3:$A$7={"홍길동","이무기"}),$B$3:$B$7))

라고 해야 합니다.

 

if 함수를 사용했으므로 조건이 만족하는 경우의 값을 반환하므로 맞는 합계가 나오는 겁니다.

 

5. 안 되는 경우 2는 해결 방안 없음

위에서는 이름을 직접 입력했는데, 셀 주소를 이용해 아래와 같이 입력하면

=SUMPRODUCT(($A$3:$A$7={d3,d4})*$B$3:$B$7)

"이 수식에 문제가 있습니다"라는 에러 메시지 창이 표시되고, 확인 버튼을 누르면

 

d3셀 주소에서 멈추고 엔터키를 눌러도 입력이 안됩니다. 아래 함수 설명을 보면 '배열(array1)이어야 한다고 합니다.

 

이름을 직접 입력하면 배열로 인식하고, 셀 주소를 입력하면 배열이 안되나 봅니다.

 

{d3,d4}를 마우스로 끌어서 범위로 잡고 F9키를 눌러보면 위와 같이 '위 수식에 문제가 있습니다"란 메시지 창이 표시됩니다.

 

안 되는 건 안 되는 거죠. 따라서, 이 때는 조건 2개를 +로 연결하는 첫 번째 방법을 사용해야 합니다.

해결 방법을 만드는 것과는 별개로, 안된다는 것을 아는 것도 지식입니다.

입력이 안되므로 수식을 알 수 있도록 맨 앞에 작은따옴표를 입력해 두겠습니다.

 

그렇다고, D3,D4의 중괄호를 제거하고, $D$3:$D$4라고 해도

=SUMPRODUCT(($A$3:$A$7=$D$3:$D$4)*$B$3:$B$7)

 

수식에 문제는 없는데, 행 별로 비교하기 때문에,

다시 말해 $A$3:$A$7=$D$3:$D$4의 결과가 {TRUE;FALSE;#N/A;#N/A;#N/A}로 네 번째가 True가 돼야 하는데, #N/A라고 표시되는 등의 문제가 있어서 전체적으로 #N/A 에러가 반환됩니다.

 

6. index + match 함수 사용

Microsoft 365의 경우 =MATCH(D3:D4,A3:A7,0)라고 입력하면

(이전 버전이라면 수식 입력 후 엔터키가 아니라 Ctrl + Shift + Enter키를 눌러야 함)

 

찾는 값이 배열이기 때문에 

 

반환 값도 {1;4}라고, 배열 형태로 반환됩니다.

 

따라서, index 함수와 결합해서 =INDEX(B3:B7,MATCH(D3:D4,A3:A7,0))라고 입력하면

마찬가지로 배열 형태로 값이 반환됩니다.

 

따라서, 이제 sum 함수와 결합해서 합계를 구하면 됩니다.

=SUM(INDEX(B3:B7,MATCH(D3:D4,A3:A7,0)))

 

함수를 여러 번 사용하면 의외로 되는 경우도 있습니다.

항상 갈고닦아야 합니다.

 

여러 조건 검색(완성).xlsx

0.01MB