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))
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)
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)
2.6 거래처/품목별 단가 찾기
상품의 단가가 거래처별로 다르게 적용하는 경우 처리할 수있다. (찾기함수 대용가능)
그러나 다음과 같은 전제가 필요하다.
- 해당조건을 만족하는 값은 단 1개만 존재한다.
- 찾고자 하는 값이 수치이어야 한다. (문자등은 불가능)
I5=SUMPRODUCT(($A$3:$A$14=G5)*($B$3:$B$14=F5)*$C$3:$C$14)
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으로 처리하여 합산시킨다.
4. 맺는 말
SUMPRODUCT는 논리연산자를 활용한 조건비교로 SUMIF함수나 COUNTIF함수 보다 더 유연한 검색이 가능하다. 또한 배열수식의 기능이 이미 포함되어 있어 두개이상의 다중조건을 쉽게 처리할 수 있다. 조금만 응용하여 사용해도 조건에 따른 다양한 표현이 가능하다.
(엑셀 2003 을 기준으로 작성되었습니다.)
* 출처 : http://www.abyul.com/zbxe/xl_QnA/15146
댓글 없음:
댓글 쓰기