엑셀 sumproduct 함수를 설명하기 전에 product가 무엇을 뜻하는지 잠시 설명하고 넘어가겠습니다.
수학을 싫어하시는 분들도 계시겠지만 product는 벡터의 내적(inner product)과 외적(cross product)을 의미하는 용어입니다.
특히 sumproduct는 내적과 관계가 있습니다. 그렇다고 내적을 공부할 필요는 없습니다. 단지 어떻게 동작하는지만 알면 되니까요.
쉽게 말해 좌표 (a, b, c)와 (d, e, f)가 있을 때 두 좌표를 내적하면 ad+be+cf가 됩니다.
그럼 이제 sumproduct 함수를 살펴보겠습니다.
엑셀 sumproduct
다음은 파란 범위와 빨간 범위를 SUMPRODUCT 함수에 인수로 전달한 결과입니다.
32가 나왔군요.
=SUMPRODUCT(B2:B4, C2:C4)
두 범위를 좌표로 보고 내적을 해보면 다음과 같습니다.
(1, 2, 3) · (4, 5, 6) = 1x4 + 2x5 + 3x6 = 32
다음과 같이 범위를 추가할 수도 있습니다.
결과는 1x4x7 + 2x5x8 + 3x6x9 = 270이 됩니다.
그렇다면 엑셀 SUMPRODUCT 함수가 무슨 쓸모가 있을지 궁금하실겁니다.
아주 간단한 예로는 물품의 가격 데이터, 수량 데이터가 있을 때 총 물품의 가격을 계산할 때 쓸 수 있습니다.
그런데 굳이 SUMPRODUCT를 사용하지 않아도 SUM함수를 사용하여 같은 작업을 할 수 있습니다. 그런데 이 때는 다음과 같이 배열 수식을 사용해야합니다.
배열과 배열수식을 이해하지 못하면 SUMPRODUCT 함수를 완벽히 이해하기 힘들 수 있습니다. 다음을 참고하세요.
이렇게 엑셀 SUMPRODUCT는 배열이나 셀 범위 간의 곱을 해서 총 합을 구하는 함수입니다. 배열 간에 곱을 하므로 다양한 응용이 가능한데 대표적인 예를 살펴보겠습니다.
- 물품을 입력하면 자동으로 가격이 입력되도록 만들기
가격은 구별될 수 있게 아무렇게나 적었습니다.
위 물품들 중에 다음과 같이 구입을 했다고 해보죠.
이 때 가격란에 구입물품의 가격이 정확하게 입력되도록 하려면 D3 셀에 다음과 같은 수식을 넣어주면 됩니다.
=SUMPRODUCT(($F$3:$F$7=B3)*($G$3:$G$7=C3), $H$3:$H$7)
($F$3:$F$7=B3)*($G$3:$G$7=C3)
① $F$3:$F$7=B3
미리 정해놓은 물품목록 중 구입물품과 일치하는 값이 있으면 1을 반환 아니면 0을 반환한다.
② $G$3:$G$7=C3
미리 정해놓은 옵션목록과 구입물품의 옵션과 일치하는 값이 있으면 1을 반환하고 아니면 0을 반환한다.
이 수식은 앞에서 ①과 ②가 모두 1을 반환하는 경우는 1 x 가격을 나머지 경우는 0 x 가격을 계산하여 이 값들을 모두 합산한다.
쉽게말해 구입물품과 옵션이 모두 일치하는 경우의 가격을 반환하는 것이다.
옵션에 대해서는 다음과 같이 해줍니다.
레어, 미듐, 웰던은 스테이크에 대해서 종속적인 옵션이므로 스테이크가 대분류면 레어, 미듐, 웰던은 소분류가 됩니다. 따라서 레이, 미듐, 웰던의 이름정의를 스테이크로 해둡니다.
마찬가지로 야채와 소고기에 대해서도 같은 작업을 해줍니다.
이제 구입물품의 빈 셀에서 데이터 -> 데이터 유효성 검사를 선택한 후 다음작업을 해 줍니다.
해당 셀에 ▽표시가 생긴 것을 볼 수 있을 겁니다. 이 것은 드롭다운의 대분류에 해당합니다.
같은 작업을 옵션의 첫 번째 빈 셀에서도 해주는데 드롭다운의 두번째 분류는 소분류에 해당하며 앞의 작업과 약간의 차이점이 있습니다. 정의된 이름대신 INDIRECT 함수를 사용합니다.
모두 마쳤으면 나머지 빈셀도 적용되도록 구입물품, 옵션의 빈셀들을 자동채우기를 해줍니다.
=INDIRECT($B3)
$B3에서 대분류를 선택하면(예를들어 스테이크를 선택) 선택된 이름을 주소 또는 이름정의로 인식하여 참조합니다.
$B3
아래쪽으로 자동채우기를 하기 위해 절대참조와 상대참조가 혼합된 혼합참조를 사용했습니다. 주의하세요.
구입물품(대분류)에 대해서 옵션(소분류) 목록이 적절히 바뀌는 것을 볼 수 있습니다.
이 때 대분류 소분류를 모두 선택하면 가격이 알아서 표시됩니다. 가격에 대해서 SUMPRODUCT 함수로 세운 수식은 수정할 필요 없이 앞에서 만든 그대로입니다.