[엑셀 활용] 엑셀 배열 수식 배우기

이전 게시물에서 엑셀 배열 수식을 살펴 보았습니다. 이전 게시물을 읽었더라도 직접 엑셀 배열 수식을 사용해 보지 않았다면 쉽게 '아, 엑셀 배열 수식이 이런 것이구나' 감이 오지 않을 것입니다.

일단 한마디로 말하자면 배열 수식은 엑셀의 수식에서 단일 값 대신 배열값을 사용하는 것입니다. 또 배열 수식은 단일 셀 대신 셀 범위를 수식에 사용하는 것입니다. 엑셀에서의 범위 참조는 곧 배열의 형태로 처리되기 때문이지요. 이전 게시물에서 보셨을 것입니다.

오늘은 이제 이 엑셀 배열 수식을 문제 해결에 활용해 보겠습니다.

엑셀 배열 수식 사용하기 - 사칙연산

위 엑셀 표 [중간고산 성적표] 에서 총점을 구해 봅니다. 엑셀 단순 수식으로 구하자면

1) F4 에 셀 포인터를 두고
2) =SUM( C4:E4 ) 또는 =SUM( C4, D4, E4 ) 또는 =C4+D4+E4 을 수식 입력합니다.
3) F4 셀의 오른쪽 아래 채우기 핸들을 F11 까지 당겨 수식 복사합니다.

이상의 방법이 엑셀을 사용하는 많은 이들이 사용하는 방법일 것입니다. 그런데 우리는 배열 수식이란 것을 배우고 있습니다. 배열 수식을 위 문제 해결에 사용해 봅니다.

1) G4:G11 의 범위를 선택합니다.
2) =C4:C11+D4:D11+E4:E11 수식을 입력합니다.
3) CTRL+SHIFT+ENTER 로 마무리 하여 엑셀 배열 수식으로 입력합니다.

이상의 엑셀 배열 수식으로 G4:G11 셀의 값들을 동시에 구합니다. 2) 를 보시면 국어범위+영어범위+수학범위 입니다. 동일 행의 셀들의 값이 구해져 동일행의 결과 셀에 반환되고 다음행 , 다음행이 한번의 수식으로 결과가 결정되는 것입니다.

수식을 기준으로 설명드리면 C4+D4+E4 의 결과가 G4 셀에 반환되고 동일방법으로 5 행의 결과가 G5 셀에 반환되는 것입니다. 미리 선택한 결과 범위가 배열로 반환된 수식 결과의 각 원소가 되는 것입니다. 엑셀 배열 수식 사용의 방법 중 하나입니다. 만일 국어, 영어, 수학의 시작셀과 종료셀이 일정하지 않다면 오류가 발생합니다. (예: =C4:C8+D4:D9 )

엑셀 배열 수식 사용하기 - 활용하기1

위 [그림1] 의 성적표에서 '성별' 이 '여' 인 학생들의 '국어' 성적 합계를 구합니다. 엑셀 SUMIF 함수를 사용한다면 쉽게 해결됩니다. 이번은 엑셀 배열 수식을 활용해 봅니다.

1) 결과를 얻고 싶은 셀에 다음의 수식을 입력합니다.
2) =SUM( IF( B4:B11="여" , C4:C11 , 0 ))
3) CTRL+SHIFT+ENTER 로 마무리 하여 엑셀 배열 수식으로 계산합니다.

2) 의 IF 함수 비교 형태는 셀 범위와 '여' 라는 단일 값의 비교이지만 배열 수식이기에 B4:B11 의 값들이 배열로 저장되어 처음부터 차례로 '여' 와 비교됩니다. 결과는 배열로 반환 됩니다.

2) 의 수식 중 엑셀 IF 함수의 조건 비교 부분의 결과를 봅니다. {TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE} 의 배열 형태로 B4 ~ B11 의 셀들의 비교 결과는 저장됩니다.

일단 B4 셀을 봅니다. TRUE 가 비교 결과라면 참일때 반환값으로 사용된 C4:C11 의 범위 중 동일행 C4 셀(80)이 결과로 반환됩니다. 만일 B4 셀의 비교 결과가 FALSE 였다면 IF 함수는 0을 반환할 것입니다.

배열이 비교에 사용되고 배열이 참의 값으로 사용되기에 엑셀 IF 함수의 최종결과는 배열로 반환됩니다.

이상이 엑셀 배열 수식을 사용하여 위의 문제를 해결하는 1번째 방법입니다.

엑셀 배열 수식 사용하기 - 활용하기2

이번은 위와 동일한 문제를 엑셀 IF 함수를 사용하지 않고 엑셀 SUM 함수만으로 해결해 봅니다. IF 함수 없이 조건 비교하여 원하는 값들의 합을 구할 수 있을까요?

1) 역시 결과를 원하는 셀에 포인터를 두고 수식을 입력합니다.
2) =SUM((B4:B11=B4) * C4:C11)
3) CTRL+SHIFT+ENTER 로 엑셀 배열 수식을 완성합니다.

수식을 살펴봅니다. [-활용하기1] 에서와 다른 부분을 찾아 보세요.

바로 보이는 것은 IF 함수의 삭제 대신 (B4:B11=B4) 수식 구문이 삽입되었습니다. 물론 이 하나가 IF 함수를 모두 대신하지는 못합니다. 하지만 이번 수식에서 조건 비교 구문은 이 부분입니다.

B4:B11=B4 , 이는 논리식입니다. 결과는 TRUE, FALSE 로 반환 됩니다. 이 구문이 함수식 안에 사용된다면 수치 계산시 1 과 0 으로 사용될 것입니다. 다음의 논리식 예제로 이를 이해해 봅니다.

위 논리식의 예를 범위(배열) 로 바꿔 놓은 것이 위의 비교 구문입니다. 즉 위의 비교 구문도 B4:B11 의 범위를 B4와 같은지 비교하고 이를 만족한다면 TRUE 를 반환합니다. 여기서 단일셀 B4는 그 셀의 값 '여' 를 대신합니다. B4 ~ B11 의 셀들과 B4 값이 차례로 비교되고 배열로 저장되는 것입니다.

이제 '여' 인지를 비교하고 저장된 배열 결과가 C4:C11 의 범위(배열) 와 곱해집니다. B5셀의 단일 셀의 수식으로 위 배열 수식을 이해해 봅니다.

=SUM((B5=B4) * C5)

이 수식의 B5 셀의 값과 B4셀의 '여' 를 비교합니다. (B5=B4) 는 TRUE 를 반환합니다. 엑셀 SUM 함수 안에 사용되었기에 1로 변환되어 수치연산에 사용됩니다.

즉, =SUM( 1 * 95) 가 결과인 것입니다. 95 를 반환 하겠지요.

이런식으로 위의 엑셀 배열 수식의 각 셀들이 비교되고 계산되어 SUM 함수의 배열 인수로 저장됩니다.

위 2)의 SUM 함수의 인수는 배열*배열(논리값 배열*숫자값 배열) 의 곱이기에 결과도 배열로 생성되고 이것이 인수로 적용되어 최종 결과를 도출하는 것입니다. (논리값 배열이 참(1)이면 숫자값 배열의 값이 반환되고 논리값이 거짓이면 0을 곱하는 것과 같기에 결과는 0이 되는 것입니다.)

이렇게 엑셀 배열 수식을 사용하는 몇가지 방법을 살펴보았습니다. 첫번째로 결과 범위를 선택하고 수식을 입력하는 방법과 둘째로 셀 범위(배열)를 계산식에 사용하고 결과는 단일 셀에 반환되는 방법입니다. 엑셀 배열 수식의 커다란 두 유형 입니다.

물론 활용도는 보다 넓습니다. 위와 같이 IF 함수의 사용 없이 비교가 쉽다는 것이 하나의 장점이 될 수도 있습니다. 나름의 필요에 따라 잘 활용해 보시기 바랍니다.

[엑셀 활용] 엑셀 배열 수식 배우기 - 먼저 맛보기

마늘빵.