[ 엑셀 Tip ] sumproduct 함수를 활용한 합계 구하기

엑셀 sumproduct 함수를 활용한 합계를 구해 보도록 하겠습니다.

sumproduct 함수는 배열함수로 함수를 나눠보면 sum( 합계 ) + product( 곱하기 ) 입니다.

영어라서 어순이 한국과 반대라고 생각해도 될 것 같습니다.

곱한 후에 더한다라는 개념은 배열과 동일한 개념으로 볼 수 있습니다.

배열이 궁금하시면 아래 글을 읽어 보시면 도움이 되실 것 같습니다.

[ 엑셀 Tip ] 배열 수식 활용

---

 

데이터를 준비해 보겠습니다.

최종 합계 구하기

=SUMPRODUCT(	B2:B14,	C2:C14   )
곱하고 더해줘	수량의 값과	단가의 값을

 

 

sumproduct 함수 B2:B14를 선택 후 F9 단축키를 누르면 결과 값을 확인할 수 있는데 다음과 같이 배열로 값이 들어가 있는 것을 볼 수 있습니다. 그리고 C2:C14도 같이 F9를 눌러주면 단가의 값이 배열로 입력되어 있는 것을 볼 수 있습니다.

B2 * C2, B2 * C3 ......... B14 * C14를 곱해서 배열로 반환하고 이 배열을 합해준 결과를 반환하는 구조입니다.

어렵게 보이지만 위에 예전 배열을 보시면 계산되는 방식을 그림으로 보실 수 있는데 같은 원리입니다.

---

특정 조건이 맞는 개수 구하기

구분에 "a"의 개수를 세어 보도록 하겠습니다.

 

다음과 같이 수식을 입력했는데 0의 값이 반환이 됩니다.

 

이때 수식 앞에 ( - ) 기호를 붙여주면 다음과 같이 -3이 반환이 됩니다.

이 때 반환되는 원리는 다음과 같습니다.

처음에 - 기호를 붙이기 전에는 결과가 논리값 TRUE, FALSE로 반환하기 때문에 숫자가 아닙니다.
하지만 - 기호를 붙이면 숫자로 변환이 가능해지므로 TRUE가 1이 되고 앞의 -를 받아 -1이 됩니다.

 

 

오른쪽과 같이 TRUE가 1이 되고 -로 인해서 -1이 반환되는 것을 확인할 수 있습니다.

수식에서 원리가 궁금하면 F9를 눌러보는 습관을 들이시면 좋습니다.

그렇지만 원하는 결과는 -3이 아닌 3개를 구하고 싶습니다. 

이때 앞에 -를 하나 더 붙여주면 3으로 반환되는 것을 볼 수 있습니다. ( 이중 부정은 긍정 )

 

---

특정 조건을 만족하는 합계 구하기

위 쪽의 예제를 활용해서 "a"의 합계만 구해 보도록 하겠습니다.

 

"a"의 값만 해당되는 subtotal의 값이 구해졌습니다. F9를 눌러보도록 하겠습니다.

구해지는 논리는 아까 위에서 본 것처럼 앞에는 1이 반환되고 뒤에는 0이기 때문에 뒤의 계산은 모두 0이 되어 "a"값만 계산이 되는 구조입니다.

반응형

 

※ 단일 항목만 변수로 추가할 경우 합 도출 가능