VBA에서 SumProduct 사용하기

1. SumProduct 함수

SumProduct 함수는 Product, 다시 말해 곱한 값을 더해서 반환해 주는 함수입니다.

 

가. Product와 Sum함수를 이용하는 경우

아래와 같이 숫자1과 숫자2를 곱한 다음 합계를 낸다고 할 때

 

Product와 Sum 함수만 알고 있다면

먼저 줄별로 Product, 예를 들어 =product(a2:b2)를 한 후

 

C7셀에서 Sum을 하면 됩니다.

 

 

나. SumProduct 함수를 사용하는 경우

(1) 구문

=SUMPRODUCT(array1, [array2], [array3], ...)

배열로 되어 있는데 범위라고 생각하면 간단합니다.

 

(2) 사용 예

배열을 세로로 해서 A열을 배열1, B열을 배열2로 지정하면 됩니다.

따라서, 위와 같이 곱한 것의 합을 구하려면

=SUMPRODUCT(A2:A6,B2:B6)라고 입력해서 한 번에 원하는 값을 구할 수 있습니다.

 

 

2. VBA에서 SumProduct 함수 사용하기

 

먼저 개발도구 - Visual Basic을 누르고, 삽입 메뉴에서 모듈을 추가합니다.

 

그런 다음 에디터 창에 Sub라고 입력한 다음 프로시저명으로 '곱해서합함'이라고 입력하고 엔터키를 누릅니다.

그러면 아래와 같이 됩니다.

 

그다음 SumProduct함수를 사용하는데 계산한 값만 반환하도록 할 수도 있고,

엑셀에서 처럼 수식을 넣을 수도 있습니다.

 

가. 값만 넣기

D7셀에 넣는다고 하면

Range("d7")= 이라고 입력하고

SumProduct함수를 이용하는데

 

(1) 에러 1

엑셀(워크시트)에서 A2:A6,B2:B6라고 입력했으므로

Range Object(함수)를 사용해서 

SumProduct(Range( "A2:A6,B2:B6")) 라고 입력하고

Sub 곱하고합함()
    Range("d7") = sumproduct(Range("a2:a6,b2:b6"))
End Sub

 

F5키 또는 기본 아이콘 그룹에서 오른쪽 세모 아이콘을 눌러 실행하면 

SumProduct를 가리키면서 "Sub 또는 Function이 정의되지 않았습니다."란 에러 메시지가 표시됩니다.

 

 

이것은 SumProduct함수가 VBA함수가 아니라 엑셀 워크시트에서만 사용하는 함수입니다.

 

(2) 에러 2

따라서, SumProduct 앞에

WorksheetFunction.(점까지 입력) 이라고 입력하면

SumProduct함수 등이 표시되므로 탭 키를 눌러 입력합니다.

 

그리고 다시 실행하고 엑셀 보기 아이콘을 눌러서

 

엑셀로 돌아가서 보면 

B7셀에 33109가 아니라 818이 구해집니다.

 

모니터가 2개라면 아래와 같이 워크시트와 VBA 에디터를 나란히 열어놓고 확인할 수 있으므로 편리합니다.

 

818은 SumProduct의 Array2가 없어서 1이 곱해지기 때문에 A2셀부터 D6셀까지의 합이 구해진 것입니다.

이것은 범위 전체가 Array1으로 입력되어 그런 것입니다.

 

(3) 성공

따라서, Range를 두 개로 나눠서 입력해야 합니다.

수정한 수식은 아래와 같습니다.

Sub 곱하고합함()
    Range("d7") = WorksheetFunction.SumProduct(Range("a2:a6"), Range("b2:b6"))
End Sub

 

이제 실행을 하면 원하는 값 33109가 나오는데, 수식이 아니라 값입니다.

 

 

나. 수식을 넣기

수식을 넣으려면 큰따옴표 안에 위 수식을 넣으면 됩니다.

따라서 아래와 같이 됩니다.

Sub 곱하고합함()
    Range("d7") = "=WorksheetFunction.SumProduct(Range("a2:a6"), Range("b2:b6"))"
End Sub

 

그런데 입력하고 아래 화살표 키를 누르거나 실행면 아래와 같이 a2를 가리키면서 에러가 발생하고

 

 

실행 아이콘을 누르면 문장 전체가 선택되면  구문 오류란 에러 메시지가 발생합니다.

 

이것은 앞에 큰따옴표가 추가되다 보니 Range("에서 큰따옴표가 닫히기 때문에

"a2:a6"에서 앞의 큰 따옴표가 없어지기 때문입니다.

 

따라서, Escape문자인 "(큰따옴표)를 범위를 입력하는 큰따옴표 앞에 붙여줘야 합니다.

그러면 코드가 아래와 같이 됩니다.

Sub 곱하고합함()
    Range("d7") = "=WorksheetFunction.SumProduct(Range(""a2:a6""), Range(""b2:b6""))"
End Sub

 

 

이제 실행하면 아래와 같이 수식에 @ 표시가 들어가 있고, D7셀에는 #NAME?라고 WorksheetFunction이 뭔지 모르겠다는 에러가 표시됩니다.

 

 

 

수식을 입력해야 하므로 SumProduct만 사용해야 하고, 셀 주소도 Range 없이 사용해야 하는데 Range함수를 사용해서 그런 것이므로 이들을 모두 제거합니다.

다시 말해 워크시트에서 입력했던 그대로 입력하는 것입니다.

Sub 곱하고합함()
    Range("d7") = "=SumProduct(a2:a6, b2:b6)"
End Sub

 

 

이제 실행하면 D8셀에 입력한 수식 그대로 입력되고, 맞는 값 33109가 구해졌습니다.

 

이와 같이 값으로 입력하는 경우와 수식으로 입력하는 경우 차이가 있습니다.

VBA에서 SumProduct함수 사용하기.xlsm

0.01MB