배열을 이용한 다중 조건 값 계산, ARRAY와 RANGE그리고 SUM,SUMPRODUCT 차이

엑셀은...

공부하면 할수록 많은 것을 할 수 있는 프로그램인 것 같다. 오늘은 새롭게 터득한 ARRAY와 RANGE의 차이에 대해서 알아보려고 한다. 전문적으로 공부하고 이해한 것이 아니기 때문에 이해를 쉽게 하기 위해서 나만의 이해법을 이야기해서 틀린 부분이 있을 수도 있다.

ARRAY: 배열이라는 의미로 0,1,2,3,4,5,6,7,8,9 라는 숫자가 있을 때 이 숫자를 묶지 않고 늘여 뜨려 놓은 값이라고 이해한다.
RANGE: 범위라는 의미로 위의 숫자들을 0~9라고 표기하는 것과 같다.

둘의 차이점을 이해하기 위해서 SUM함수를 통해서 설명하겠다.

가령 A1 0으로 시작해서 1씩 증가하여 A10에는 9가 열이 있다고하면

{ } 대괄호 기호만 들어갔을 뿐인데 결과값은 같더라도 의미가 매우 달라진다.

즉 범위는 순서상관없이 그 해당 범위 안에 있는 숫자들의 집합인 것이고. (숫자)

배열은 그 숫자에 알맞은 위치까지 포함되어 있는 것이다. (숫자+위치)

그렇기 때문에 ARRAY(숫자+위치)>RANGE(숫자)의 개념이 된다고 볼 수 있다.

두 함수는 비슷한 결을 가지고 있다.

SUM은 단순히 합계를 구하는 함수이기에 RANGE로 범위를 지정해 줘도 결과값이 나온다.

SUMPRODUCT는 1대 1 대응하여 곱한 후 더하는 함수이기 때문에 RANGE로는 결과 값이 안 나오고 ARRAY를 사용하여야 한다.

가령 1 공장에서 생산되는 규격 100의 생산품을 재고금액을 알고 싶은 C18, D18, C19, D19의 함수 수식은 다음과 같다.

C18은 단순히 한 개의 ARRAY를 더한 값이다. '()*()*생산량범위' 앞에 괄호 앞에 조건 값을 1 공장과 100이라는 다른 열의 조건값을 넣어줌으로 써 1 공장이자 규격 100이라는 {10,14}라는 배열을 만들어줬고. 추가로 곱해지는 값을 넣지 않았기 때문에 이 배열 값들이 더해져서 24라는 숫자가 나온다.
D18는 앞서 {10,14}라는 순서값과 함수에 다음 인수 {1,5)를 추가해 준 것이다. 그래서 이 값들이 서로 대응돼서 곱해진 값 (10x1)+(14x5)=80이 나온 것이다.
E3:E13이라는 일반 범위값을 지정해 줬는데 {1,5} 값이 나온 것은 앞선 인수를 만족하는 셀의 위치 값과 1대 1 대응하는 위치의 값을 불러온 것이라고 이해된다.
C19에서는 만약 대괄호를 넣지 않으면 열로 인식해서 값이 오류가 나온다. 하지만, 대괄호(CTRL+SHIFT+ENTER)를 넣음으로써 배열로 인식하게 되고, 배열에 조건 값들을 찾아서 1공장 규격 100의 해당하는 합계를 구한다.
D19에서도 역시 배열로 인식하면서 괄호 조건값을 만족하는 배열들의 곱을 더하게 되는 것이다.

나도 엑셀 고수가 아니기 때문에, 완벽하게 설명이 됐을 것이라고는 보지 않는다. 그렇지만 나보다도 초심자입장에서는 충분히 도움이 됐을만한 정보가 됐으리라고 기대한다.

궁금한 점이나 오류가 있다면 댓글 바란다.