1. 아별툴
  2. 아별툴 패밀리
  3. 엑셀 질문하기
  4. 엑셀 강좌
  5. 엑셀 팁
  6. 엑셀 자료실
  7. 엑셀 연구과제
  8. 엑셀 북마크
  9. 관련 프로그램 소개

[펌] sumproduct 함수를 이용한 다중 조건 계산

엑셀일반 조회 수 24829 추천 수 0 2008.04.11 23:37:12

아.. 이렇게 훌륭한 펑션을 왜 아직 활용하지 못했을까?
사업부별, 거래처별, 제품별, 단가표에서 단가를 갖고 오는 것을 만들기 위해서 VLOOKUP을 얼마나 돌려댔든가.. emoticon

다만, 주의할 건..
단가표에 동일한 데이터가 있다면.. 단가가 따블이 될 수 있다는.. =_=;;
VLOOKUP은 그래도 한개만 갖고 오는데.. 음.. 어떤게 더 안전하고 유용하고.. 편리할까.. 음.. =_=;;

단가표와 같이 기준정보에서 데이터를 불러오는게 아닌 반복이 허용된 데이터의 통계 산출을 위해서라면..
이보다 좋은건 없을 듯.. 아..피벗테이블이 있구나.. 머.. 용도가 다르니까.. ㅋㅋ

특히 원가 계산과 같은 복잡한 수식이 요구되는 계산에서 그 영향력은 지대.. 지대.. +_+

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

출처 : 네이버 지식인


SUMPRODUCT 함수에 대해

 도움말을 그대로 인용하자면 주어진 배열에서 해당 요소들을 모두 곱하고 그 곱의 합계를 반환한다. 즉 함수 자체가 배열수식형태로 이미 설정되어 있어 이를 잘 활용하면 다양하게 응용할 수 있다.

  SUMPRODUCT의 장점은 다중조건을 처리할 수 있다는 점이다. 논리연산자를 활용할 경우  보다 더 유연한 검색이 가능하다. 하나 또는 두개 이상의 다중조건을 만족시키는 값들을 추출해 낼 수 있다.

 다중조건을 처리하는 또 다른 방법은 SUM 함수와 IF함수를 중첩한 배열수식으로 처리할 수 있다. 또한 논리연산자 부분의 처리는 대등소이하다.

  (예제파일은 글의 마지막에 링크되어 있습니다.)

1.  SUMPRODUCT 함수사용
2.  SUMPRODUCT 함수 적용사례
3.  SUM,IF를 활용한 배열수식과  비교
4.  맺는 말

1.  SUMPRODUCT 함수사용 (그림참조)



1.1  배열요소들의 곱한 값의 합계  
                     / 단가 * 수량 한 값들의 합계

     =SUMPRODUCT(C3:C12,D3:D12)
          A3*D3, A4*D4, A5*D5, ……A12*D12  각 곱한값들의 합을 구함



1.2  단일 조건을 만족하는 건수      
                       / 지역(A열)이 "서울"인 행의 수

   =SUMPRODUCT((A3:A12="서울")*1)       cf: =COUNTIF(A3:A12, "서울")  동일한 결과   

        A3="서울",  A4="서울", A5="서울" ...... A12="서울" 의 논리값 참, 거짓으로 -> 합산할 수 없음
        ((A2:A12="서울")*1)  ==>  A열이 "서울"이면 1을 아니면 0을


1.3  단일 조건을 만족하는 수량의 합계 
                        / 지역(A열)이 "서울"인 수량(C열)의 합계

   =SUMPRODUCT((A3:A12="서울")*C3:C12)      cf: =SUMIF(A3:A12, "서울", C3:C12) 동일한 결과  
   =SUMPRODUCT((A3:A12="서울")*1,C3:C12)  위와 같은 결과

    (A3="서울")*C3, (A4="서울")*C4, (A5="서울")*C5....(A12="서울")*C12
         A열이 "서울"이면 C열값을 아니면 0을



1.4 다중조건의 건  수              
                        / 지역(A열)가 "서울"이고 품목(B열)이 "사과"인 행의 수

    =SUMPRODUCT((A3:A12="서울")*(B3:B12="사과"))
       (A3="서울")*(B3="사과"), (A4="서울")*(B4="사과"), (A5="서울")*(B5="사과"),
        ............(A12="서울")*(B12="사과")          둘다 만족시키면 1을, 아니면 0을



1.5  다중조건의 수량의 합계 
                       / 지역(A열)이 "서울"이고 품목(B열)이 "사과"인 수량(C열)의 합계

   =SUMPRODUCT((A3:A12="서울")*(B3:B12="사과")*(C3:C12))
   =SUMPRODUCT((A3:A12="서울")*(B3:B12="사과"),(C3:C12))   위와 같은 결과

       (A3="서울")*(B3="사과")*C3, (A4="서울")*(B4="사과")*C4, (A5="서울")*(B5="사과")*C5,
         ............(A12="서울")*(B12="사과")*C12     두가지 만족할 경우  C열 값을 아니면 0



1.6  부등호를 활용한 다중조건의 건 
                       / 지역(A열)가 "서울"이외의 곳에서  금액(E열)이 100만원 이상인 행의 수

   =SUMPRODUCT((A3:A12<>"서울")*(E3:E12>=1000000))

1.7  부등호를 활용한 다중조건의 합계
                       /  지역(A열)이 "서울"이외의 곳에서  금액(E열)이 100만원 이상인 경우의 금액 합계

   =SUMPRODUCT((A3:A12<>"서울")*(E3:E12>=1000000))

Exel_Sumproduct_001.jpg




2.  SUMPRODUCT 함수 적용사례

2.1 지역/품목별 건수
I3=SUMPRODUCT(($A$3:$A$20=$G5)*($B$3:$B$20=H$14))

2.2 지역/품목별 금액
I12=SUMPRODUCT(($A$3:$A$20=$G15)*($B$3:$B$20=H$14)*$C$3:$C$20)

2.3 품목별/날짜별 매출수량
G21=SUMPRODUCT(($A$3:$A$20=G$20)*($C$3:$C$20=$F21)*$D$3:$D$20)

Exel_Sumproduct_002.jpg




2.4 품목별 수량구간별 건수

H5=SUMPRODUCT(($B$3:$B$20=$G5)*($C$3:$C$20<=10))
         
I5=SUMPRODUCT(($B$3:$B$20=$G5)*($C$3:$C$20<=20))
    -SUMPRODUCT(($B$3:$B$20=$G5)*($C$3:$C$20<=10))
        
J5=SUMPRODUCT(($B$3:$B$20=$G5)*1)
     -SUMPRODUCT(($B$3:$B$20=$G5)*($C$3:$C$20<=20))


2.5  품목별 수량구간별 수량계       
         
H15=SUMPRODUCT(($B$3:$B$20=$G15)*($C$3:$C$20<=10)*$C$3:$C$20)
         
I15=SUMPRODUCT(($B$3:$B$20=$G15)*($C$3:$C$20<=20)*$C$3:$C$20)
      -SUMPRODUCT(($B$3:$B$20=$G15)*($C$3:$C$20<=10)*$C$3:$C$20)
         
J15=SUMPRODUCT(($B$3:$B$20=$G15)*$C$3:$C$20)
       -SUMPRODUCT(($B$3:$B$20=$G15)*($C$3:$C$20<=20)*$C$3:$C$20)

Exel_Sumproduct_003.jpg

 

2.6  거래처/품목별 단가 찾기
상품의 단가가 거래처별로 다르게 적용하는 경우 처리할 수있다.  (찾기함수 대용가능)

그러나 다음과 같은 전제가 필요하다.
  - 해당조건을 만족하는 값은 단 1개만 존재한다.
  - 찾고자 하는 값이 수치이어야 한다. (문자등은 불가능)

I5=SUMPRODUCT(($A$3:$A$14=G5)*($B$3:$B$14=F5)*$C$3:$C$14)

Exel_Sumproduct_004.jpg

 

3.  SUM,IF를 활용한 배열수식과  비교

SUMPRODUCT처럼 두개이상의 조건에 따라 값을 추출할 수 있는 방법이 있는데,  SUM 함수와 IF함수를 중첩하고 배열수식형태로 처리하여야 한다. 두가지 경우 모두 논리연산자를 활용한다는 점에서는 같다. 그러나 배열함수를 이용한 경우는 IF값에 따라 그 값들을 설정해 주고 그 값들을 더해주는 형태를 취하여야 한다.


3.1 조건에 맞는 건수
=SUMPRODUCT((A3:A12="서울")*(B3:B12="사과"))
=SUM(IF((A3:A12="서울")*(B3:B12="사과"),1,0))    입력후 Ctrl + Shift + Enter 동시에 누름
   건수를 구하려면 조건에 맞을 경우 1 을 아니면 0으로 처리하고 합산하는 처리 방식이다.


3.2  조건에 맞는 값의  합계

=SUMPRODUCT((A2:A11="서울")*(B2:B11="사과"),(C2:C11))
=SUM(IF((A3:A12="서울")*(B3:B12="사과"),(C3:C12),0))  입력후 Ctrl + Shift + Enter 동시에 누름
   합산처리시에 조건여하에 따라 합하고자 하는 값을 주거나 0으로 처리하여 합산시킨다.

Exel_Sumproduct_005.jpg




4.  맺는 말

SUMPRODUCT는 논리연산자를 활용한 조건비교로 SUMIF함수나 COUNTIF함수 보다 더 유연한 검색이 가능하다. 또한 배열수식의 기능이 이미 포함되어 있어 두개이상의 다중조건을 쉽게 처리할 수 있다. 조금만 응용하여 사용해도  조건에 따른 다양한 표현이 가능하다.


(엑셀 2003 을 기준으로 작성되었습니다.)

파일보기 :     blog.naver.com/park__jang/60036417761

파일 다운 받기 : Naver_sumproduct-park__jang.xls


댓글 '3'

[레벨:4]pgpoch

2012.07.18 22:24:38
*.115.163.106

헉... 링크 걸어주셔서 왔는데.. 너무 어렵네요. 쩝...꼼꼼하게 살펴보겠습니다~ 감사해요

lifephobia

2014.07.09 10:33:31
*.243.1.205

감사합니다. 겨우 찾던 기능을 찾았네요. ^-^

wilmorts

2015.02.12 13:11:10
*.105.5.253

항상 많은 도움 받고 있습니다.

감사 드립니다!



문서 첨부 제한 : 0Byte/ 2.00MB
파일 제한 크기 : 2.00MB (허용 확장자 : *.*)
List of Articles
번호 제목 글쓴이 날짜 조회 수sort
공지 엑셀일반 엑셀 짜투리 팁 모음.. imagefile [13] [레벨:30]아별 2005-07-22 52709
공지 엑셀일반 엑셀 잘 하는 요령은? _ 엑셀 추천사이트 포함 [2] [레벨:30]아별 2009-11-12 40736
공지 엑셀일반 엑셀 단축키(바로가는키, ShortCut) 총정리입니다. imagefile [247] [레벨:30]아별 2010-08-29 106158
72 엑셀일반 [엑셀에러] stdole32.tlb imagefile [레벨:30]아별 2013-09-27 112982
71 엑셀일반 엑셀에서 날짜와 시간 계산하는 법 image [레벨:30]아별 2008-06-27 26421
» 엑셀일반 [펌] sumproduct 함수를 이용한 다중 조건 계산 imagefile [3] [레벨:30]아별 2008-04-11 24829
69 엑셀일반 외부 파일에 연결된(외부 파일 참조) 셀 찾는 방법입니다. imagefile [4] [레벨:30]아별 2011-02-08 24360
68 엑셀일반 인쇄 설정 팁.. 머리글, 바닥글에 페이지 번호 설정하는 방법 imagefile [2] [레벨:30]아별 2011-08-03 22439
67 엑셀일반 셀 색상별 합계 구하기( GET.CELL( TYPE,REFERENCE) ) imagefile [레벨:30]아별 2008-03-12 21823
66 엑셀일반 엑셀 표시형식에 대해서 (셀서식, 숫자서식) file [1] [레벨:30]아별 2005-05-13 17955
65 엑셀일반 엑셀에서 유용한 단축키들.. [레벨:30]아별 2005-05-23 17397
64 엑셀일반 엑셀에서 줄바꿈 한 셀의 줄간격을 조절하기 [레벨:30]a☆ 2004-11-15 16247
63 엑셀일반 조건부 서식 적용하기 (로또 번호 맞춰 보기) imagefile [4] [레벨:30]아별 2011-04-11 16033
62 엑셀일반 엑셀에서 날짜 데이터 가공/계산/변환 등 종합 선물 세트. file [2] [레벨:30]아별 2008-06-30 15750
61 엑셀일반 두가지 조건을 만족하는 값 찾기 (직급별 연봉) imagefile [레벨:30]아별 2007-01-02 15161
60 엑셀일반 PDF파일을 EXCEL파일로 변환하기 file [1] [레벨:30]아별 2008-10-24 14086
59 엑셀일반 엑셀에서 다른 파일/시트 참조하기/영역설정하기 [레벨:30]a☆ 2004-10-15 13080
58 엑셀일반 열 방향의 데이터를 행방향 데이터로.. 데이터베이스(DB)형태로 변환하기.. imagefile [2] [레벨:30]아별 2008-08-20 12628
57 엑셀일반 [ XML ] 엑셀로 XML 데이터 다루기.. file [2] [레벨:30]아별 2011-12-26 12432
56 API Win32 API 를 공부하기 시작해봅시당~ 꺄~ >.< file [레벨:30]아별 2009-11-21 12381