■ 목차
1. 함수 설명
2. Sumproduct 함수의 구문 / 사용방법
3. Sumproduct 함수 사용해 보기 - 판매량 합계 한 번에 구하기
4. Sumproduct 함수 응용 - 하나의 조건을 만족하는 합계 구하기
5. Sumproduct 함수 응용 - 여러 조건을 만족하는 합계 구하기
6. Sumproduct 함수 응용 - 고유한 데이터의 개수 구하기
** 핵심 요약
1. 함수 설명
- 엑셀 Sumproduct 함수의 기본 기능은 곱한(Product) 값을 더해주는(Sum) 함수입니다. (Sum + Product)
- 하지만 곱하기 외에 더하기(+), 빼기(-), 나누기(/) 모두 가능하고
- 특히 배열의 경우 배열 수식형식(Ctrl + Shift +Enter)으로 입력하지 않아도 배열 계산이 가능하기 때문에
- 다중 조건을 만족하는 개수나 합계 등을 구하는 응용형태로 다양하게 많이 사용되는 함수입니다.
- 물론 배열 수식에 익숙하신 분이라면 Sumproduct 함수를 굳이 사용하지 않으셔도 되지만
- 작성된 문서가 엑셀 초보분들에게 공유가 되는 경우 어려운 배열수식으로 작성하기보다는
- 엑셀이 기본으로 제공하는 Sumproduct 함수를 사용해서 수식을 만들어 공유하는 것이 필요한 경우도 있을 것입니다.
2. Sumproduct 함수의 구문 / 사용방법
- 아래 구문은 엑셀 도움말의 설명 내용입니다. 우리는 엑셀 함수의 구문을 다 외울 수도 없고 그럴 필요도 없습니다.
- 다만 엑셀 도움말과 함수사용 시 표시되는 풍선도움말의 용어들에 익숙해질 필요가 있기 때문에 기회가 될 때마다 편하게 봐주시기 바랍니다.
- SUMPRODUCT(array1, [array2], [array3],...)
- array1 : 계산하려는 배열의 첫 번째 인수입니다.
- [array2], [array3],... : 계산하려는 배열의 인수로서 2개에서 255개까지 지정할 수 있습니다.
- array 즉 배열을 입력하고 인수구분자 콤마( " , " )를 사용합니다.
- ① 배열이라고 하면 보통은 그냥 셀 범위(range)를 입력해 주시면 되고 비교연산자를 이용한 조건식 형태의 배열 (A1:A10 = "강북")로도 입력이 가능합니다.
- ② 두 번째로 인수 구분자가 콤마( " , " )만을 보통 사용하지만 Sumproduct 함수의 경우는 조금 확장되어 사용이 가능합니다.
- 즉 콤마( " , " )를 사용하게 되면 곱한 값의 합계를 구해주게 되지만 콤마 ( " , " ) 대신에 " +, -, / "를 구분자로 사용해서 더하기, 빼기, 나누기의 작업도 가능합니다.
- 이렇게 되면 각각의 행을 더한 값을 합계해 주거나 각각의 행을 나눈 값을 합계하는 형식으로 사용이 되는데 간단하게 다른 수식으로 대체가능하기 때문에 사용할 일은 없을 듯합니다.
- 그리고 주의해야 하는 부분은 모든 array의 차원 (개수, 크기)는 동일해야 합니다. 그래야 각각의 행단위로 곱하기 등의 작업이 가능하고 #Value! 오류가 없습니다.
- 만약 데이터가 숫자 데이터가 아닌 경우는 0으로 취급을 하게 됩니다.
- 우리가 일반적으로 True, Fasle을 숫자 1, 0으로 간주를 하지만 Sumproduct 함수의 경우는 논리값 (= 문자)은 숫자 0으로 취급을 하게 되니 주의해 주시기 바랍니다.
- 그래서 True, Fasle의 논리값을 숫자로 바꾸고 싶은 경우에는 곱하기 숫자 1 ( " *1 ")을 해줘야 합니다.
3. Sumproduct 함수 사용해 보기 - 판매량 합계 한 번에 구하기
- 아래와 같이 판매수량과 단가가 있는 경우에 전체 판매량을 한 번에 구하는 예제입니다.
- 각각의 판매수량 * 단가를 Sum해주는 방식으로 전형적인 Sumproduct 함수를 사용하는 예제입니다.
- 인수는 2개입니다. 판매수량 범위(B3:B6)와 단가 범위(C3:C6)를 인수로 입력을 해주게 되면
- 각각의 행에 해당하는 "판매수량 * 단가"를 계산해서 그렇게 계산된 값 전체에 대한 합계를 구해주게 됩니다.
- 실무에서는 "수량 * 단가"를 곱해서 구해주는 작업 없이 "총판매금액"을 바로 구할 수 있게 됩니다.
4. Sumproduct 함수 응용 - 하나의 조건을 만족하는 합계 구하기
- 다음은 Sumproduct의 배열을 입력하여 하나의 조건을 만족하는 합계를 구해보는 예제입니다.
- 여기서는 ① 조건을 입력하는 방법과 하나의 조건인 경우 ② 논리값을 숫자 데이터로 변형하는 방법을 잘 보시기 바랍니다.
- 다음의 판매량 정보에서 "강북" 지점의 판매수량 합계와 총판매금액을 구해보도록 하겠습니다.
- 1) "총판매수량" : =SUMPRODUCT((B3:B7=B10)*1, C3:C7)
- 배열 수식 계산이 들어가 있어서 초보분들에게는 조금 어려운 설명이 될 수 있을 듯합니다.
- 첫 번째 인수 : (B3:B7 = B10)*1
- 지점이 "강북"인 경우는 참(True)이 되고 여기에 곱하기 숫자 1 (= True * 1 = 1)을 하게 되면 최종적으로 참인 경우는 숫자 1이 되게 됩니다.
- 이렇게 엑셀은 내부적으로 논리값(True, False)을 사칙연산으로 계산을 하게 되면 True = 1, False = 0으로 취급되어 계산이 됩니다.
- 그래서 참인 경우는 결국 숫자 1이 되고 거짓인 경우는 숫자 0이 되어 Sumproduct 함수로 각각의 행별로 곱하기를 하게 되면
- 참인 행만 최종적으로 계산값을 가지게 되고 거짓인 행은 값이 0이 되어 전체 계산값에서 제외가 되는 원리입니다.
- 조금 내용이 어려우신 분들은 "배열수식"을 먼저 공부를 하시면 많은 도움이 될 듯합니다. ㅠㅠ
- =(B3:B7 = B10) * 1
- =( {"강북" ; "강남" ; "강북" ; "강남" ; "강동" } = "강북" ) * 1
- = ( { "강북" = "강북" ; "강남" = "강북" ; "강북" = "강북" ; "강남" = "강북" ; "강동" = "강북" } ) * 1
- = ( { True ; False ; True ; False ; False } ) * 1
- = ( {True * 1 ; False * 1 ; True * 1 ; Fasle * 1 ; Fasle * 1 } )
- = ( { 1 ; 0 ; 1 ; 0 ; 0 } )
- 결국 조건을 만족하는 경우는 숫자 1이 되고 조건을 만족하지 않는 경우는 숫자 0이 되어 Sumproduct 함수에 사용되게 됩니다.
- 두 번째 인수 : "C3:C7"로 "판매수량" 데이터를 입력합니다.
- 2) "총판매금액" : =SUMPRODUCT((B3:B7=B10)*1, C3:C7, D3:D7)
- "총판매금액"의 경우 "단가"를 3번째 인수로 추가해서 "단가"까지 곱하기를 해주게 되면 Sumproduct 함수로 쉽게 구할 수 있습니다.
- "=Sumproduct (지점이 강북인 것, 판매수량, 판매단가)"와 같이 수식이 만들어지게 됩니다.
5. Sumproduct 함수 응용 - 여러 조건을 만족하는 합계 구하기
- 이번에는 여러 조건을 만족하는 합계를 Sumproduct 함수로 구해보도록 하겠습니다.
- 한 개의 조건을 만족하는 합계를 구하는 것은 조금 전에 해봤습니다. 그럼 여러 조건을 만족하는 수식은 어떻게 만들 수 있을까요?
- 여기서 2가지 조건(여러 조건)을 만족한다는 것은 첫 번째 조건이 "True"이고(= And) 두 번째 조건도 "True"인 경우를 말합니다.
- 이렇게 2가지 조건의 경우 모두 만족하는 조건은 And 조건이 되고 And 조건은 엑셀에서는 곱하기 ( " * " )로 표현이 됩니다.
- Ture * True = 1 * 1 = 1 = True의 공식이 적용되기 때문입니다.
- 이 경우 한 개의 조건만을 만족하게 되면 True * False = 1 * 0 = 0 = False가 되어 대상에서 제외가 되는 방식입니다.
- 여러 조건을 만족하는 경우 = And 조건 = 곱하기 ( " * " )
- 한 개의 조건을 만족하는 경우 = Or 조건 = 더하기 ( " + " )로 표현을 할 수 있습니다. ^^
- 1) "총판매수량" 구하기
- 수식 : "=SUMPRODUCT((B3:B8=B11)*(C3:C8=C11), D3:D8)"
- 첫 번째 인수 : (B3:B8=B11)*(C3:C8=C11) = (지점이 강북인 것) * (상품이 모델B인 것)
- 2가지 조건의 수식이 곱하기( " * " )로 연산이 되어 And 조건인 여러 조건을 만족하는 경우의 수식으로 사용이 되었습니다.
- 2) "총판매금액" 구하기
- 수식 : "=SUMPRODUCT((B3:B8=B11)*(C3:C8=C11), D3:D8, E3:E8)"
- 수식에서 Product 함수에 사용될 "단가" 범위(E3:E8)가 추가되었습니다.
6. Sumproduct 함수 응용 - 고유한 데이터의 개수 구하기
- Sumproduct 함수의 가장 큰 장점이 배열수식을 그냥 입력할 수 있다는 부분입니다.
- 그래서 수식의 완성을 "Ctrl + Shift + Enter"로 마무리하지 않아도 배열수식 입력이 가능하고 바로 합계까지 구할 수 있는 함수입니다.
- 이러한 장점 때문에 배열수식으로 풀어야 하는 많은 문제를 Sumproduct 함수로 풀 수가 있는데 다음은 그러한 배열수식 문제 중에 하나입니다.
- 여러 개의 데이터가 있는 경우 고유한 데이터의 개수가 몇 개인지를 구하는 예제입니다.
- (AA, BB, AA, DD, AA)와 같은 데이터가 있다면 고유한 데이터는 AA, BB, DD이고 고유한 데이터의 개수는 3개입니다.
- 수식의 원리는 각각의 데이터를 Countif 함수로 몇 개인지를 찾게 되면 (AA, BB, AA, DD, AA) = (3개, 1개, 3개, 1개, 3개)와 같이 구할 수 있고
- 이를 바탕으로 Countif 함수의 역수의 합계를 구하는 방식입니다. Sum(1/3, 1/1, 1/3, 1/1, 1/3) = 3
- 즉 동일한 데이터가 3개인 경우는 각각의 데이터의 "1/Countif 함수"는 1/3, 1/3, 1/3이 되고
- 이를 모두 더하게 되면 1/3+1/3+1/3 = 1이 되는 방식으로 계산이 되어 고유한 데이터의 개수를 알 수가 있게 됩니다.
- 수식 : "=SUMPRODUCT(1/COUNTIF(B3:B8, B3:B8))"
- Countif 함수의 첫 번째 인수는 "찾으려는 범위"이고 두 번째 인수는 "찾는 항목"입니다.
- 여기서는 첫 번째, 두 번째 인수가 모두 "B3:B8"로 동일하게 배열수식으로 입력하게 되면
- 전체 범위에서 전체 데이터를 각각 한 번씩 개수를 헤아리도록 수식을 구성되게 됩니다.
- Sumproduct 함수가 배열을 인자로 받아서 계산이 가능하기 때문에 사용이 가능한 방식이며
- 일반 함수의 경우 "배열 수식(Ctrl +Shif + Enter)"을 사용해서 함수를 만들어줘야 합니다.
- Sumproduct 함수의 근본은 Sum + Product 함수입니다.
- 하지만 배열형식으로 인수 입력이 가능하기 때문에 다양한 배열수식을 활용한 응용이 가능하게 됩니다.
- 배열 수식의 근본적인 원리는 생각보다 어렵지 않기 때문에 한 번 진득하게 사례 위주로 살펴보시기를 추천드립니다.
- 오늘도 고생 많으셨습니다.
** 핵심 요약 : Sumproduct 함수 사용법 - 기본 + 응용하기 (다중조건, 배열 입력)
1. 함수 설명
- Sumproduct 함수의 기본 기능은 곱한(Product) 값을 더해주는(Sum) 함수이다. (Sum + Product)
- 특히 배열을 배열 수식형식(Ctrl + Shift +Enter)으로 입력하지 않아도 배열 계산이 가능하기 때문에
- 다중 조건을 만족하는 개수나 합계 등을 구하는 응용형태로 다양하게 많이 사용되는 함수이다.
2. Sumproduct 함수의 구문 / 사용방법
- SUMPRODUCT(array1, [array2], [array3],...)
- array1 : 계산하려는 배열의 첫 번째 인수입니다.
- [array2], [array3],... : 계산하려는 배열의 인수로서 2개에서 255개까지 지정할 수 있습니다.
- ① 배열로는 셀 범위(range) 혹은 비교연산자를 이용한 조건식 형태의 배열 (A1:A10 = "지점1")로도 입력이 가능하다.
- ② 두 번째로 인수 구분자가 콤마( " , " )만을 보통 사용하지만 Sumproduct 함수의 경우는 조금 확장되어 "+, -, /"를 인수 구분자로 사용가능하다.
- 주의해야 하는 부분은 모든 array의 차원 (개수, 크기)는 동일해야 한다.
3. Sumproduct 함수 사용해 보기 - 판매량 합계 한 번에 구하기
- 판매수량과 단가가 있는 경우에 전체 판매량을 Sumproduct 함수로 한 번에 구할 수 있다.
- 인수는 2개로 판매수량 범위 (B3:B6)와 단가 범위 (C3:C6)을 인수로 입력해 주게 되면
- 각각의 행에 해당하는 "판매수량 * 단가"를 계산해서 그렇게 계산된 값 전체 합계를 구할 수 있게 된다.
- 수식 : "=SUMPRODUCT(B3:B6, C3:C6)"
4. Sumproduct 함수 응용 - 하나의 조건을 만족하는 합계 구하기
- 판매량 정보에서 "강북" 지점의 판매수량 합계와 총판매금액을 Sumproduct 함수로 구해보자.
- 1) "총판매수량" : =SUMPRODUCT((B3:B7=B10)*1, C3:C7)
- 2) "총판매금액" : =SUMPRODUCT((B3:B7=B10)*1, C3:C7, D3:D7)
- 논리값(True, False)의 결과는 곱하기 숫자 1 ( *1 )를 해서 숫자 데이터로 바꿔주자.
5. Sumproduct 함수 응용 - 여러 조건을 만족하는 합계 구하기
- 2가지 조건(여러 조건)을 모두 만족하는 조건은 And 조건이 되고 And 조건은 엑셀에서는 곱하기 ( " * " )로 표현이 된다.
- Ture * True = 1 * 1 = 1 = True의 공식이 적용되기 때문이다.
- 1) "총판매수량" 구하기=SUMPRODUCT((B3:B8=B11)*(C3:C8=C11), D3:D8)"
- 2) "총판매금액" 구하기 : "=SUMPRODUCT((B3:B8=B11)*(C3:C8=C11), D3:D8, E3:E8)"
6. Sumproduct 함수 응용 - 고유한 데이터의 개수 구하기
- 수식의 원리는 각각의 데이터를 Countif 함수로 몇 개인지를 찾아서
- 이를 바탕으로 역수의 합계를 구하는 방식이다. Sum(1/3, 1/1, 1/3, 1/1, 1/3) = 3
- 수식 : "=SUMPRODUCT(1/COUNTIF(B3:B8, B3:B8))"
[엑셀 함수 강좌-플러스] - 999. 엑셀 함수 Plus 목차
999. 엑셀 함수 Plus 목차
[엑셀 함수 강좌-플러스] - 001. (엑셀 함수 Plus) Text 함수 사용법 - 원하는 서식(포맷, 날짜/시간, 소수점, 퍼센트, 시간, 요일)을 텍스로 만들어 보자 [엑셀 함수 강좌-플러스] - 002. (엑셀 함수 Plus) Tr
nextmeok.tistory.com
* 엑셀 관련 궁금하신 거나 어려운 점이 있으시면 자유롭게 질문을 해주세요.
* 가능한 도움을 드릴 수 있도록 하겠습니다.
* 저도 많이 알지는 못하지만 누구나 그렇듯 시작이란 게 있고 경험이란 게 다를 것 같습니다.
* 편하게 문의하시면 됩니다. 저도 모를 수 있다. 감안해 주시고.
- ILU, SH -
댓글