엑셀 배우기, SUMRPDUCT 함수로 다중 조건의 합 구하기!

드디어 SUMPRODUCT 함수를 다룰 시간입니다. SUMPRODUCT 함수의 기능은 크게 두가지입니다. 기본 기능은 SUM과 PRODUCT, 즉 두개 이상 범위의 대응하는 값끼리 곱해서(PRODUCT) 더하는(SUM) 기능입니다. 

예를 들어 대학 수업 중 하나에서 교수님이 성적 평가를 할때, 학년별로 가중치를 준다거나 할 때, 유용하게 활용할 수 있는 함수입니다. 1학년은 20%, 2학년은 30% 3학년은 40%, 4학년은 40%, 이런 식으로 평가 점수에 차등을 두는 것이죠. 시험에서 같은 점수를 받았더라 고학년일 수록 더 높은 점수를 받을 수 있도록 말입니다.

단, 이번 포스팅에서는 이 SUMPRODUCT 함수의 기본 기능이 아닌 다중조건의을 만족하는 합계를 구하는, SUMIFS 함수의 기능과 정확히 일치하는 기능을 SUMPRODUCT 함수로 구현하는 방법에 대한 내용입니다. SUMIFS 함수의 활용에 대한 설명은 아래의 포스팅에서 참고해 주시기 바랍니다.

엑셀강좌, 다중 조건의 합, SUMIFS 함수로 월별/품목별 판매수량/매출액 구하기!

아래의 매출 장부의 데이터를 바탕으로 각 주별로 또 각 상품별로 매출액 보고를 해야하는 상황이라고 가정하겠습니다. 즉, 주별, 그리고 상품별, 조건이 두개입니다. 먼저 아래의 매출 장부에서 '주별' 정보를 만들도록 하겠습니다. 위의 지난 포스팅에서는 '날짜' 데이터에서 '월별' 정보를 추출했었는데, 비슷한 구조입니다. 마찬가지로 '날짜' 데이터에서, '주별' 정보를 추출합니다. 그리고 사용하는 함수는 WEEKNUM입니다. 이름부터가 직관적이지요? 주 번호입니다. 함수 구조는 '=WEEKNUM(날짜)'로 더욱 간단합니다. 아래의 B열에 '주별' 정보를 넣었습니다.

 

이제 SUMPRODUCT 함수를 활용합니다. I11셀에 주번호를 입력하고 J11셀에 상품명을 입력하면, 해당 주에 특정 상품의 매출액이 K11에 자동으로 계산되게 만들고 싶습니다. 이렇게 다중 조건을 만족하는 합계를 SUMPRODUCT 함수로 구하는 것은, SUMPRODUCT 함수의 배열끼리 곱해서 합계를 내는 기본 기능의 함수 구조와는 조금 다릅니다. 그래서 일부러 이 기능은 스킵한 부분도 있습니다.

아무튼 SUMIFS 함수와 동일한 기능으로  SUMPRODUCT 함수를 구성하려면, '=SUMPRODUCT(합계할 범위 * (조건1의 범위=조건1)*(조건 2의 범위=조건2)*조건 3의 범위=조건3)'로 수식을 구성합니다. 합계할 범위를 함수 구성의 맨 앞에 두고, 필요한 조건의 범위와 조건을 * 연산자로 계속 이어주기만 하면 되는겁니다.

역시 어렵지 않습니다. 사실 K111셀의 수식은 아래와 같이 각 범위를 절대 주소로 지정할 필요는 없는데, 채우기 핸들을 사용해서 수식을 복사해야 하는 경우가 많으니, 절대 주소를 지정했습니다. 항상 말씀드리지만, 절대 주소는 습관적으로 지정해 주시는 것이 좋습니다. 물론 사용해야하지 않는 경우도 있을 수 있겠지만요. 이경우는 아마 조만간 다른 엑셀 강좌를 통해서 언급할 필요가 있을 듯합니다.

참고로 동일한 계산을 SUMIFS 함수로 처리하면 수식을 어떻게 구성해야 할까요? SUMIFS 함수의 구조는 '=SUMIFS(합계할 범위, 조건 1의 범위, 조건1, 조건 2의 범위, 조건 2, 조건 3의 범위, 조건 3, ...)'으로, 위의 K11 셀에 입력한다면, '=SUMIFS($G$3:$G$27, $B$3:$B$27, I11 ,$D$3:$D$27,J11)'됩니다. 저는 개인적으로 SUMIFS가 더 직관적으로 사용할 수 느낌이어서 SUMPRODUCT 함수보다는 SUMIFS를 더 많이 사용하긴 합니다. 다만, 당연히 본인에게 더 편한 함수를 사용하면 됩니다.

모로 가도 서울만 가면 된다는 말이 있지요? 엑셀 함수 사용에도 그대로 적용할 수 있는 표현입니다. 본인이 풀어야할 문제의 정확한 답을 구하기 위해서 어떠한 함수를 사용하든 전혀 상관 없습니다. 중요한 것은 정확안 답안을 찾아내는 것이니까요. 

즉, + 연사자를 사용해서, 셀값 하나하나씩 더해줘도 좋은 것이고, SUM 함수를 사용 보다 간단하게 합계를 구할 수도 있는 것입니다. 당연히 SUM 함수가 더 효율적인 방법인 것은 분명하지만, 자기가 알고 있는 것들을 어떻게 활요하는냐가 더 중요한 문제라는 겁입니다. 물론 컴퓨터활용능력 등의 엑셀 시험에서 특정 함수를 사용하라는 문제에서는 그 함수만 사용해야 겠지만 말입니다.