엑셀(Excel): SUMPRODUCT함수 활용 및 이름 사용

 

 

[문제]

어떤 회사에서 Promotion 기간 중에 노트북을 판매하거나, 수도권 외의 지역에서 상품을 판매한 판매자에 대하여 판매금액의 5%에 해당하는 인센티브를 지급하기로 했다. 각 판매자별로 지급받을 인센티브를 구하시오.


[풀이 과정]

1. 판매자별 구분
2. 노트북 or 수도권 이외의 지역 (서울, 인천, 경기 해당 여부 판단)
3. 위의 조건을 모두 만족하는 판매금액을 합산


=SUMPRODUCT(($C$5:$C$29=H5)*((($D$5:$D$29="노트북")+($E$5:$E$29<>"서울")*($E$5:$E$29<>"인천")*($E$5:$E$29<>"경기"))>0)*($F$5:$F$29))


[이름 관리자를 활용하는 경우]

=SUMPRODUCT((판매자=H14)*(((판매상품="노트북")+(판매지역<>"서울")*(판매지역<>"인천")*(판매지역<>"경기"))>0)*(판매금액))


원본 데이터상의 테이블명을 변경하여 작업하는 방식은 Access에서도 활용하는 방식이다. 
이와 같이 이름 관리자를 활용하면 수식의 내용을 이해하는데 용이하다. 

 

sumproduct_vs_sum_if.xlsx

0.01MB

[엑셀에서 '이름'을 활용할 때의 장점]

개인적으로는 엑셀에서 '이름(Name)'을 정의하여 사용하는 것을 즐겨하지는 않는 편이다.
무엇인가를 외워서 하는 걸 좋아하는 편이 아니라서... 뭔가 새로운 것이 있으면 이해하고 넘어가고, 나중에 필요할 경우를 대비해서 정리해 놓거나, 과거의 이해를 되살려 풀어나가는 편이다.

엑셀에서 [이름 관리자]를 활용했던 이유도, 딱히 필요에 의한 것이기 보다는 나중에 잊었을 때 찾아가는 발자취 정도였다. 그런데, 이렇게 [이름]을 정의해서 사용하다 보니, 한 가지 좋은 장점이 있었다.

그것은 수식으로만 정리된 내용을 보면, 왜 이런 수식을 적용하게 되었는지에 대한 이해는 뒷전으로 사라져 버리고 만다. 
결국 수식을 적용할 당시에는 이러저러한 이유로 그렇게 한 것인데, 나중에 시간이 지나고 나면 정답은 기계적으로 맞히고 있지만, 어떠한 방식으로 계산되고 있는지는 다시 수식을 찬찬히 살펴봐야 설명이 가능하게 된다.

그런데, 이렇게 [이름]을 정의해서 적용을 해 두면, 나중에라도 그 수식의 내용을 보고 이해가 가능해 진다. 
뿐만 아니라, 다른 사람과 관련된 업무의 내용을 공유하고 있다면, 이 내용을 이해하는데 도움이 되기도 한다.


[엑셀에서 '이름'을 활용하는 방법]

엑셀에서 '이름'은 [수식]을 선택하면 나타나는 리본에서 [정의된 이름] 그룹에 있다.

 

 

위에 문제와 풀이를 담은 엑셀 파일에는 중첩된 SUM + IF를 활용했을 경우도 포함하고 있다.

이를 참고하면, SUMPRODUCT 함수와 중첩된 SUM + IF의 차이를 이해하는데 도움이 될 것이다. 개인적으로는 중첩된 SUM + IF의 수식이 더 작성하기도 쉽고, 이해도 잘 된다. 

 

🌻=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=🌻

      내용이 괜찮았다면 공감 ❤️을 눌러 주세요.    

      당신의 응원과 구독 🛎️이 큰 힘이 됩니다.