엑셀] 배열 수식과 배열 함수 SUMPRODUCT

안녕하세요, 짤막한 강좌 한정희 강사입니다. 

이번 강좌는 구독자 신**님의 질문으로 엑셀 초보분들에게 배열 수식과 SUMPRODUCT 함수 사용법을 소개하기 좋을 것 같아 허락을 받고 데이터를 그대로 사용해 만들었습니다. 

 

실습 파일 다운로드 

실습파일.xlsx

0.01MB

질문 내용은 다음과 같습니다. 

각 동별로 요청된 모든 품명별 개수를 단가와 곱해 아래에 합계를 구하는 겁니다. 

구독자분께서 작성한 수식입니다. 

수식이 어마어마하죠?

품목 요청이 있을 때마다 값이 변하도록 일일이 빈 셀도 모두 참조해서 수식을 만들어 두셨네요. 

"아무리 생각해도 이런 강력한 프로그램에 이런 무식한 방법은 아닐 것이다 라는 생각이 들어서요..."라는 내용과 함께 보내주셨어요. ㅎㅎ

 

이런 경우 간단하게 배열 수식 또는 엑셀에서 제공되는 배열 함수 중 SUMPRODUCT 함수를 사용할 수 있습니다. 

두 가지 모두 설명해보겠습니다. 

배열 수식부터 보겠습니다. 

먼저 합계를 구하는 =SUM 함수를 입력하고 1동 범위 [D4:D41] 범위를 지정합니다.

그리고 *(곱하기) [$J$4:$j$41] 셀 범위를 참조한 다음 <F4>를 눌러 절대 참조합니다. 

절대 참조하는 이유는 1동 합계를 구하는 수식을 복사해서 2동, 3동 합계도 자동으로 구하려는 거죠. 

1동 범위는 상대 참조, 단가 범위는 절대 참조가 됩니다. 

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

간단하게 수식이 완성되었죠?

=SUM(D4:D41*$J$4:$J$41)

 

엑셀 2016, 2019, 오피스365를 사용 중이라면 수식을 작성한 후 Enter를 누르면 되지만 이전 버전을 사용하고 있다면 배열 수식은 작성 후 <Ctrl + Shift + Enter>를 눌러야 합니다. 

 

배열 수식을 작성하지 않는 다면 별도의 열에 수량 곱하기 단가 수식을 작성해서 합계를 구해야 하지만 지금과 같은 방법을 사용할 줄 안다면 한 번에 처리할 수 있습니다. 

수식을 복사하여 3동까지 결과를 구하면 됩니다. 

 

또 다른 방법으로 SUMPRODUCT 함수를 사용해 볼까요?

SUMPRODUCT 함수는 인수와 인수를 곱해 합계를 구하는 함수입니다. 

Array1과 Array2 인수에 각각 1동 범위와 단가 범위를 참조하면 됩니다. 

그럼 함수가 각각 대응되는 셀을 곱해서 합을 구해줍니다. 

 

데이터를 제공하신 구독자분께 감사드리고 필요하신 분들께 강좌가 도움이 되길 바랍니다.