※ SUMPRODUCT 함수의 기본 원리는 아래 글에서 확인해주시기 바랍니다.
|
[MS EXCEL functions] - [간단한 엑셀 함수] SUMPRODUCT함수 사용법
|
｜SUMPRODUCT 함수의 활용
SUMPRODUCT 함수는 매우 유용한 함수입니다.
배열함수, 피벗테이블의 기능을 대체할 수 있는 함수입니다.
폐쇄적인 다른 함수들보다 확장성이 좋기 때문에 많은 분들이 SUMPRODUCT함수를 사용합니다.
SUMPRODUCT 함수는 배열수식과 기능이 같다고 할 수 있습니다.
｜SUMPRODUCT 함수의 이해
먼저 SUMPRODUCT의 가벼운 사용예시를 확인해보겠습니다.
위와 같은 좌측의 영역에 그에 대응하는 우측의 영역의 값을 곱한 총 합계를 구하고 싶을때,
좌측의 영역과 우측의 영역을 곱하는 작업을 한 뒤,
SUM함수를 사용하여 10,000이라는 값을 구할 수 있습니다.
하지만, SUMPRODUCT 함수를 사용하면
위처럼 곱연산의 과정을 생략하고, 훨씬 가볍게 같은 결과 값을 구할 수 있습니다.
({=SUM((B5:D8)*(F5:H8))} 라는 배열수식을 사용하는것과 같은 결과입니다.)
리소스를 훨씬 적게 사용하기 때문에, 엑셀사용에 편리함을 더할 수 있습니다.
｜SUMPRODUCT 함수 따라하기
SUMPRODUCT 함수는 확장성이 매우 뛰어납니다.
SUMPRODUCT 함수의 활용을 설명하기 위해, 화장품을 매입하는 유통업체에 근무한다고 가정하겠습니다.
위의 파일은 7월에 우리가 매입한 내역을 적어 둔 매입내역서입니다.
B열에는 화장품 매입처가 적혀있고, C열에는 매입한 상품이 적혀있습니다.
D열에는 단가가 적혀있고, E열에는 매입량이 적혀있습니다.
하나의 예로 "LG생활건강"으로부터 매입한 "치약"의 매입금액(단가×수량)이 얼마인지 알고 싶을때,
SUMPRODUCT 함수를 활용할 수 있습니다.
우리가 여기서 알고 싶은 '다중조건'은 아래와 같습니다.
-조건① : 매입처가 "LG생활건강"
-조건② : 매입상품군이 "치약"
|
=SUMPRODUCT((B2:B17=B19)*(C2:C17=C19),D2:D17,E2:E17)
|
이 표를 이해하기 위해서, SUMPRODUCT 함수를 하나씩 따라가보겠습니다.
|
=SUMPRODUCT(B2:B17,C2:C17,D2:D17,E2:E17)
|
위의 함수를 셀에 적어보면 0을 반환합니다.
그 이유는 B2:B17 , C2:C17에 적힌 모든 값이 숫자가 아닌 문자이기 때문에,
∑(0×0×단가×수량)=0이 됩니다. (이전글의 '주의사항' 참고)
그럼 조건①과 조건②는 아래와 같이 지정할 수 있습니다.
|
구분
|
의미 |
함수 |
|
조건①
|
매입처 = "LG생활건강" |
(B2:B17=B19) |
|
조건②
|
매입상품군 = "치약" |
(C2:C17=C19) |
위의 조건을 지정하고 "*"로 묶어주게 되면,
"LG생활건강"으로부터 매입한 "치약"의 매입금액(단가×수량)를 구한다는 의미가 됩니다.
먼저, 상단에 함수가 적혀있는 영역에서, "B2:B17=B19" 부분을 드래그 하여 F9를 눌러보게 되면,
드래그 한 영역이 아래처럼 변하는 모습을 볼 수 있습니다.
|
FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;
|
2번째, 6번째, 10번째, 14번째 부분은 조건에 맞기 때문에 TRUE로 표시됩니다.
또, 상단에 함수가 적혀있는 영역에서, "C2:C17=C19" 부분을 드래그 한 뒤, F9를 눌러보게 되면,
|
FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;
|
2번째, 5번째, 8번째, 11번째, 14번째 부분은 조건에 맞기 때문에 TRUE로 표시됩니다.
TRUE는 1, FALSE는 0으로 치환되기 때문에,
|
SUMPRODUCT((B2:B17=B19)*(C2:C17=C19),D2:D17,E2:E17)
|
위 표는
|
매입처 |
매입상품군 |
단가 |
수량 |
소계 |
합계 |
|
0 |
0 |
6,000 |
1,000 |
0 |
2,200,000 |
|
1 |
1 |
2,000 |
700 |
1,400,000 | |
|
0 |
0 |
8,000 |
900 |
0 | |
|
0 |
0 |
1,000 |
100 |
0 | |
|
0 |
1 |
7,000 |
300 |
0 | |
|
1 |
0 |
9,000 |
1,000 |
0 | |
|
0 |
0 |
5,000 |
100 |
0 | |
|
0 |
1 |
2,000 |
400 |
0 | |
|
0 |
0 |
8,000 |
900 |
0 | |
|
1 |
0 |
8,000 |
200 |
0 | |
|
0 |
1 |
5,000 |
900 |
0 | |
|
0 |
0 |
6,000 |
200 |
0 | |
|
0 |
0 |
8,000 |
500 |
0 | |
|
1 |
1 |
2,000 |
400 |
800,000 | |
|
0 |
0 |
9,000 |
200 |
0 | |
|
0 |
0 |
8,000 |
1,000 |
0 |
이런 의미를 가지기 때문에 "*"를 활용하여 다양한 다중조건을 엮어서 결과를 뽑아낼 수 있습니다.
'엑셀 함수' 카테고리의 다른 글
| [쉽게 배우는 엑셀 함수] LEFT, RIGHT 함수를 활용하기(텍스트 나누기) (1) | 2018.08.14 |
|---|---|
| [쉽게 배우는 엑셀 함수] IFERROR 함수 활용법(응용) (0) | 2018.08.01 |
| [쉽게 배우는 엑셀 함수] MATCH-INDEX 활용법 (0) | 2018.07.06 |
| [쉽게 배우는 엑셀 함수] 반올림 함수 사용법(ROUND, ROUNDUP, ROUNDDOWN, MROUND, CEILING, FLOOR) (0) | 2018.06.30 |
| [쉽게 배우는 엑셀 함수] SUMIFS 의 사용 방법 (0) | 2018.06.25 |
