엑셀 SUMPRODUCT
엑셀 SUMPRODUCT
오늘은 엑셀 SUMPRODUCT 에 대해서 알아볼려고 합니다.
엑셀 SUMPRODUCT 에 대한 설명을 보면 이해하기가 어렵게 설명이 되어 있습니다.
그러나, 알고보면 월마나 쉽게요 !!!
엑셀 SUMPRODUCT 함수를 엑셀 프로그램의 설명을 보면 뭔 외국말을
써놓은 것 같습니다.
뭐, 영어를 잘하는 사람도 이해하기가 그리 쉽지는 않을듯 !!
물론 저는 영어가 짧아서 그냥 한글로 표시된 내용을 보면
"배열 또는 범위의 대응되는 값끼리 곱해서 그 합을 구합니다"
이게 말이여 방귀여 ^^
배열은 뭐고 범위는 뭐라는 말인지 당췌 이해가 안가네요 !!
그래 그냥 제가 이해하는대로 설명하고, 따라 해보기를 통하여 습득해봅시다.
엑셀 SUMPRODUCT 의 의미
엑셀 SUMPRODUCT 는 보시다시피 SUM과 PRODUCT의 합성어로
구성된 함수 이름을 가지고 있습니다.
SUM은 다 아시다 시피 더하기이고,
PRODUCT는 상품, 제품이 아니라 수학 용어로 "곱하기"를
의미한다나 뭐래나!!
영어는 어순이 꺼꾸로이까 어원 그대로 해석하면
"곱하고(PRODUCT)것을 더한다(SUM)"
라는 뜻으로 해석하면 됩니다.
엑셀 SUMPRODUCT 사용법
이제 어떻게 되는지는 알았고, 그럼 어떻게 사용할거냐를 알아봐야겠네요!!
재들이 만들어놓은 순서는 곱하기 먼저 해야겠네요.!!
그런데 뭘 곱하지?
우선 "곱하고" 를 지정해야겠습니다.
곱하기를 수행할 범위를 지정해야것네요!!
그럼 더하기는 어떻게 지정하지? 이것 참 어렵네요 !!
대부분 SUMPRODUCT 함수를 처음 접하시는 분들은
여기서 너무나 이해하기가 어려워서 기냥
SUMIF하고 IF를 가지고 노가다 뛰지 뭐 이러면서
그냥 포기를 하십니다
.
배열이니, Arrary이니, 곱하기의 순서니 이런 말이 너무 어렵거든요 !!
그래서 저는 그냥 이렇게 기억을 합니다.
곱하기를 우선하든 배열을 뭔저하든 이것 저것 신경쓰지 않고,
범위를 여러개 지정할때 "," 로 구분하죠?
만약 하나의 범위를 지정할때 행으로 지정하면 열을 곱하고 행으로 더한다.
만약 하나의 범위를 지정할때 열로 지정하면 행을 곱하고 열으로 더한다.
이렇게 기억하면 됩니다. 행으로 지정하면 열을 곱하고 행을 더한다.
열을 지정하면 행을 곱하고 열을 더한다.
자!! 그럼 예제를 보시면 훨씬 더 이해가 빠르실겁니다.
자 "곱하고, 더한다"를 적용해봅시다.
우리가 B3:F3를 범위를 지정했어요. 행으로
그럼 행이면 열을 곱하고
(1*10*5) +
(2*20*10) +
(3*10*15) +
(4*40*20) +
(5*10*30)
의 순으로 계산됩니다.
참, 쉽지요!! 행으로 지정하면
열 곱하고 행으로 더한다.
열로 지정하면 행을 곱하고 열로 더한다.
단, 여기서 하나의 전제 조건이 있습니다.
무조건 사각형으로 범위가 지정되어야 합니다.
이게 설명에 보면 "대응되는"이라는 말과 매칭되는 말인데요
곱하고 더하는 행이나, 열의 수량이
일치되어야 한다는 말입니다.
정리를 하여 보면 아래 그림과 같이 정리가 되죠 !!
엑셀 SUMPRODUCT 활용 예제
위에서는 엑셀 SUMPRODUCT 가 작동하는 방식에 대해서
알아봤습니다.
그럼 엑셀 SUMPRODUCT 를 실무 업무에서는 어떻게 활용되는지를 살펴볼까요?
뭘로 알아보지?
실무 예제는 어떤것이 좋을까?
우선 엑셀 SUMPRODUCT 는 SUMIF나, COUNTIF를 활용하면
동일한 효과를 낼수 있습니다.
그럼 왜 엑셀 SUMPRODUCT 라는 함수를 만들었나?
실제 업무를 진행하다 보면 주로 판매 현황이나
재고현황 등에서 많이 활용되지요 !!
예를 들어 오늘 매출이 품목별로 얼마나 나왔는지는
판매 수량과 단가만 가지고 있을떄 활용할 수 있는 방법은
SUMIF를 사용하면 수량과 단가를 곱한 값을
다시 합산하고, 품목별로 구분하는 수식을 적용해야 합니다.
제법 손이 많이 가지요?
엑셀 SUMPRODUCT는 이런 수식이 필요할때 약간의 기술을 적용하면
한방에 해결할 수 있습니다.
자 그럼 예제를 통하여 한번 살펴 볼까요?
이전 포스팅에서 사용했던 부서별 판매 현황을 활용해서
엑셀 SUMPRODUCT 함수를 활용해서 품목별 합계를 만들어 보겠습니다.
뭔가 매우 복잡하지요?
하나씩 확인하여 보겠습니다.
품목별 판매 금액 합계를 계산하려면
1)"부서별 판매현황"에서 단일 품목을 꺼내옵니다.
"단일 품목은 어떻게 거내오는 건데" 라고 하시는 분들은
여기서 확인하세요 !!
2)"부서별 판매현황"의 품목과 비교할 품목이 같아야 하겠죠?
그래서 만들어진 수식이 이렇게 됩니다.
"($D$6:$D$17=$I13)"
이 수식은 감자와 같은 품목만 꺼내는 수식입니다.
"D6 셀부터 D17까지의 품목과 I13 셀에 있는 값"을 비교해서
"TRUE" 또는" FALSE" 의값을 만들어 내는거죠!!
$는 절대값을 의미합니다. 왜 절대값이 붙었는냐?
감자에 대한 값을 계산하고 동일한 수식을 다른 품목에 채울려면
$를 붙이지 않으면 상대값으로 셀의 위치를 변경하게 됩니다.
그래서 채우기를 위하여 절대값을 붙인겁니다.
3)열심히 했는데 전부 0으로 나오네요!!
음 뭔가가 틀렸다는 이야기인데,,,
여기서 숨겨진 팁이 나오는데요
곱하기를 할때 숫자끼리 곱하기를 해야 제대로 된 값이 나옵니다.
"($D$6:$D$17=$I13)" 이 수식을 값은 "TRUE", "FALSE"라는 이야기죠.
그래서 "TRUE"나 "FALSE"를 숫자로 강제로 변경하겠습니다.
"($D$6:$D$17=$I13)"여기에 숫자 1을 곱하기를 하겠습니다.
엑셀에서는 "TRUE"나 "FLASE"와 같은 값은
숫자를 곱하거나 더하는등의 산술적인 수식을 적용하면
TRUE는 숫자로 1로 자동변환되고,
FALSE는 숫자로 0으로 자동변환됩니다.
4)자 마지막으로 채우기를 실행하고 나면
완료되었습니다.
엑셀 SUMPRODUCT 함수는 이렇게 사용하면 됩니다.
약간 복잡하지요?
그래도 알아두면 생각보다 많이 쓰는 함수입니다.
그런데 SUMIF나 COUNTIF를 사용할줄 아시는 분들이면
대부분 SUMPRODUCT 함수를 보다는 SUMIF나 COUNTIF를
많이 사용하시더군요.. 제 주변 분들은!!!
어떤분들은 SUMIF나 COUNTIF를 사용하면 수식이 제대로 적용되지
않는 경우가 있을때, SUMPRODUCT 함수를 적용하면
제대로 적용된다고 하시는 분들이 있습니다.
솔직히 저는 그런 경우를 접해보지 않아서 잘 모르겠습니다.
두 함수 모두 동일한 효과를 낼수 있으니 이해하시기 편한
함수를 선택해서 사용하시면 될 것으로 보입니다.
엑셀 관련 교재 요청이 있어서 추가합니다.
필요하신분들 확인하세요!!
2020/12/21 - [회사생활] - 엑셀 괜찮은 교재 추천
엑셀 괜찮은 교재 추천
엑셀 배우기 교육 기초 실무 USB 동영상강좌로 이루어진 USB 교육자료입니다. 책으로 구성된게 아니라 USB에 동영상 자료로 엑셀을 따라하기를 하며 엑셀을 학습할 수 있는 자료입니다. 주의하셔
nolja119.tistory.com
댓글