엑셀 배열 수식을 이용하여 결과값 얻기

이전 글에서 OFFSET 함수에 대해 알아보면서 동적으로 데이터를 참조하는 방법과 열 방향 데이터와 행 방향 데이터를 바꿔주는 방법에 대해 알아보았습니다. 이번 글에서는 배열 수식에 대해 알아보겠습니다.

 

엑셀 OFFSET 함수로 지정한 열 또는 행 참조하기

엑셀 OFFSET 함수로 지정한 열 또는 행 참조하기

 

엑셀 OFFSET 함수로 열 데이터와 행 데이터 전환하기

엑셀 OFFSET 함수로 열 데이터와 행 데이터 전환하기

 

 

 

 

1. 합계 구하기

  엑셀에서 합계를 구하는 방법 중 대표적인 것은 SUM 함수를 이용하는 것입니다. 예제를 보면서 확인해 보겠습니다.

 

[그림 1]과 같은 매출 장부가 있을 때 전체 매출이 얼마인지 계산하려고 합니다.

 

[그림 1] 매출 장부

SUM 함수를 사용하여 계산하려면 각 물품의 단가와 수량을 곱하여 각각의 매출을 계산하고 SUM 함수를 사용하여 총매출을 계산할 수 있습니다.

 

우선 E3 셀에 다음과 같이 수식을 입력합니다.

 

   = C3*D3

 

C3 셀의 값과 D3 셀의 값을 곱하여 배추 매출액이 얼마인지 계산합니다. 그리고, 자동 수식 완성을 이용하여 E9 셀까지 마우스로 드래그하여 각 물품의 매출액을 계산합니다.

 

임의의 셀에 SUM 함수를 이용하여 합계를 구합니다. 수식은 다음과 같습니다.

 

    =SUM(E3:E9)

 

 

 

 

위와 같은 방법보다 좀더 간단하게 전체 매출액 합계를 구할 수 있습니다. 이는 SUMPRODUCT 함수를 이용하는 것입니다.

 

[그림 1]의 매출 장부에서 임의의 셀에 다음과 같이 수식을 작성합니다.

 

    =SUMPRODUCT(C3:C9,D3:D9)

 

수식을 입력하시고 엔터를 치시면 위에서 구한 매출 합계와 동일한 값을 얻을 수 있습니다.

 

SUMPRODUCT 함수는 인수로 입력된 배열 또는 범위의 값들을 곱하고 그 합계를 구해주는 함수입니다. 사용 방법은 다음과 같습니다.

 

   = SUMPRODUCT( Array1, Array2, ..., ArrayN )

 

위의 예제에서 입력한 '=SUMPRODUCT(C3:C9,D3:D9)' 수식을 보시면 C3 셀부터 C9 셀의 값과 D3 셀부터 D9 셀의 값을 곱하고 그 곱한 값의 합계를 반환하라는 의미입니다. C3 셀의 값과 D3 셀의 값을 곱하고, 그다음 C4 셀과 D4 셀의 값을 곱합니다. 이런 방식으로 C9 셀 값과 D9 셀 값을 곱한 다음 곱하여 구한 값 모두를 더하여 합계를 구합니다.

 

SUMPRODUCT 함수를 사용하면 각 물품의 매출액을 구할 필요 없이 바로 원하는 매출액 합계를 구할 수 있습니다.

 

 

 

 

2. 배열 수식

  위의 예제를 배열 수식을 이용하여 계산하는 방법에 대해 알아보겠습니다.

SUM 함수를 이용하여 합계를 계산하는데 인수로 SUMPRODUCT 함수와 같이 범위를 입력하게 됩니다.

[그림 1]의 매출장부 임의의 셀에 다음과 같이 수식을 입력합니다.

 

  =SUM(C3:C9*D3:D9)

 

SUM 함수를 사용하면서 인수로 C3:C9*D3:D9를 입력하였습니다. 이는 C3 셀부터 C9 셀의 값과 D3 셀부터 D9 셀의 값을 곱하라는 의미입니다. 위의 SUMPRODUCT 함수 계산 방식과 동일합니다.

 

이처럼 계산식을 범위 * 범위로 입력하는 것을 배열 수식이라고 말하며 각 범위를 배열과 같이 각 번호의 방에 저장되어 있는 값끼리 곱하여 결과의 합을 반환하게 됩니다.

곱하기 뿐만아니라 사칙연산 모두 가능합니다.

 

한 가지 주의하실 점은 배열 수식을 이용하여 계산식을 작성하신 경우에는 반드시 Ctrl + Shift + Enter 키를 눌러 입력하셔야 원하는 결괏값을 얻을 수 있습니다. 이렇게 입력을 하시면 입력하신 수식이 중괄호({ }) 안에 들어간 상태로 입력되며 이는 배열 수식임을 나타냅니다.

 

참고로 뺄셈일 때 어떻게 되는지 확인하기 위해 임의의 셀에 다음과 같이 수식을 작성합니다.

 

    =SUM(C3:C9-D3:D9)

 

수식 입력 후 Ctrl + Shift + Enter 키를 누르면 결괏값이 4260이 나오며 3행부터 9행의 C열 값에서 D열 값을 뺀 후 합계를 구해보시면 동일한 값이 나오는 것을 확인하실 수 있습니다.