엑셀 SUMPRODUCT 함수는 배열간의 계산(곱셈)을 위한 함수다. 사실 sumproduct 함수가 없이도 이 함수의 기능을 수식을 통해서 사용할 수 있다. 개인적인 생각으로 많이 사용하는 기능이기 때문에 하나의 함수로 만들어 놓은 듯 싶다. 여기서는 sumproduct함수의 순수 기능을 예를 들어 설명하겠다. 앞으로의 이야기는 배열에 관한 이해가 있다면 쉽게 이해할 수 있다.
SUMPRODUCT 함수 이해하기
먼저 이 함수는 다음처럼 SUM 함수와 동일한 사용이 가능하다.
다음은 함수의 인수가 한 개가 아닌(두 개) 경우 SUM과 SUMPRODUCT를 비교했다.
SUM함수 : 인수가 여러개라도 전체 인수에 해당하는 셀의 범위의 합을 구한다. 주황,녹색 부분의 모든 수를 더함.
SUMPRODUCT 함수 : 각각의 인수가 배열이 되어 각 배열의 요소간에 곱을 하여 더한다. 배열의 요소간의 곱을 하는 방법은 같은 위치의 요소간에 곱을 함.
이렇게 곱을 하면 7개의 결과가 나오는데 이 값을 합친 결과가 140이 된다.
이렇게 SUMPRODUCT함수는 인수의 셀 범위내의 각 셀을 요소로 하는 배열이 만들어지고 인수간에 동일한 위치의 배열 요소를 곱한 후 이 값을 더한 결과를 돌려준다. 따라서 각 인수가 가르키는 셀의 범위의 크기는 동일해야 한다. 위에서 사용한 식은 다음과 같은 식이다.
= SUMPRODUCT({1;2;3;4;5;6;7} , {1;2;3;4;5;6;7})
배열에 관해 간단히 설명하겠다. 배열은 요소들의 집합으로 { 요소1, 요소2,...}와 같이 표현할 수 있는데, 구체적은 방법은 다음과 같다.
{1;2;3;4;5;6;7}은 세로배열을 나타낸다.(;사용)
{1,2,3,4,5,6,7}은 가로 배열을 나타낸다.(,사용)
위 표는 값들이 세로로 써 내려져 갔으므로 세로배열이다. 따라서 ;을 사용한다.
엑셀은 2차원 배열까지 표시할 수 있다.
실용적인 예
이 예는 매우 실용적인 예로서 난이도가 꽤 있다.
설명을 하자면 세 개의 테이블이 있는데 오른편 두 개의 테이블은 이름정의를 위한 테이블로 상품정보의 지표가 되는 표다. 첫 번째 테이블에서 품명과 규격은 드롭다운목록을 통해서 선택할 수 있게 만들고, 품명과 규격이 선택되면 위와 같이 지표가 되는 테이블을 참조하여 가격이 자동으로 입력되도록 하는 예다. 우선 이름정의와 드롭다운 목록을 만드는 방법을 모른다면 다음 글을 참고하고, 이 글에서는 자세한 설명은 생략하겠다.
2015/07/25 - [엑셀] - 엑셀 이름정의 가지고 놀아 보자
2015/07/29 - [엑셀] - 엑셀 드롭다운목록 대분류, 소분류 만드는 방법
1. 우선 위와 같이 엑셀에서 표를 만든다.
품명의 하위 목록은 용지, 연필이 되고, 용지의 하위 목록은 A2~ A5, 연필의 하위목록은 2B~4B다.
수식->이름정의에서 만든 이름정의는 이름 관리자에서 확인하거나 편집할 수 있다. 값은 위에서 설명한 배열이므로 참고하자.
2. 이름정의를 만들었으면 드롭다운 목록을 만들어야 한다.
데이터->데이터 유효성검사에 들어가면 위와 같이 나온다. 제한 대상(A)을 목록으로, 원본(S)에 =품명이라고 적는다. 여기서 품명은 1에서 만든 이름정의다.
드롭다운 목록이 완성되었다. 규격은 품명이 어떤 것이냐에 따라 유동적으로 달라지기 때문에 데이터 유효성의 원본(S) 에 INDIRECT(셀주소)를 넣어야 한다. 여기서 셀 주소는 품명에 해당하는 위치다.
셀을 선택하면 절대 주소로 입력이 되는데 이때 행은 상대주소가 되어야 하므로 주의하자.
3. 이번엔 SUMPRODUCT 함수를 이용해야 하는 부분이다.
위 식은 D4셀의 내용이고 값은 30이 나온다.
첫 번째 테이블과 두 번째 테이블에서 동일한 색으로 칠해 놓은 부분은 주목하자. 위 수식에서 좌변=우변 의 비교하여 두번째 테이블(좌변)에서 우리가 선택한 품목과 같은 경우는 1을 반환한다.(조건이 참이면 1을 반환하므로) 조건식은 2개가 있고 두 조건식을 곱한다. 이 의미는 두 조건식이 참인 경우만 1을 반환한다는 의미다. 1을 반환한 경우 SUMPRODUCT 함수를 통해 배열간 동일위치에 있는 값인 30을 추출 할 수 있다. (1*30=30이므로)
$F$4:$F$10=B4 두 셀의 값이 같을 때 1이 된다. 2번 째 테이블에서 용지에 해당하는 값이 B4의 값과 같으므로 이 경우에만 값이 1이 된다.
$G$4:$G$10=C4 이 부분은 A3인 경우에만 값이 1이 된다.
$H$4:$H$10 위에서 두 값이 모두1이 아니면 0 , 모두 1이면 그에 해당하는 가격을 반환한다. (0+0+30+0+0+0+0) 따라서 D4부분은 가격 30이 표시된다.
주의할 점은 절대주소의 사용인데 2번째 테이블을 참조하는 경우는 반드시 절대 참조$를 해주어야 한다. 참고로 조건이 들어간 경우나 인수에 수식이 들어간 경우는 SUM함수로 위 같은 표현을 할 수 없다.(이럴 경우 셀 범위의 첫 번째 값만 참조된다.) 이 예제처럼 조건이나 연산이 들어간 경우는 SUMPRODUCT함수를 사용한다.