엑셀(EXCEL) 배열 수식 / 배열 함수의 기초와 활용하기(결과가 여러 개인 함수 만들기)

엑셀에서 배열은 여러 데이터가 중첩되어 들어있는 한 셀 혹은 한개의 함수의 결과가 2개 이상인 셀을 말합니다.

배열을 직접 입력하여 만든 경우에는 배열 상수라고 부르고 아래 링크에서 만드는 방법을 포스팅 했습니다.

(엑셀(EXCEL)에서 배열 상수를 입력하고 함수에서 활용하는 방법과, INDEX 함수로 배열 표시하기)

이번 포스트에서는 배열의 계산(연산)하는 방법 중 기본적인 것들에 대해서 알아보겠습니다.

 

320x100

 

 

1. 배열수식과 배열함수

 

배열과 배열의 연산에 대해서 이야기 합니다.

예를 들어 아래 =B4:B8*C4:C8 가 있습니다.

배열 1. B4:B8 = 1, 2, 3, 4, 5

배열 2. C4:C8 = 1, 2, 3, 4, 5

곱연산 결과 : 1*1, 2*2, 3*3, 4*4, 5*5가 됩니다.

배열의 연산

 

이 결과를 다른 함수에 적용하면 많은 양의 데이터를 빠르게 해결하는 배열 함수가 됩니다.

예를 들어 =SUM(B4:B8*C4:C8)는 "1*1+ 2*2+3*3+4*4+5*5" 가 됩니다.

이런 배열함수를 입력할 때는 Alt+Shift+Enter를 입력해야 합니다.

 

 

 

2. 배열 함수의 적용

 

단일 셀을 연산하는 함수들 대부분을 배열함수로 사용할 수 있습니다.

아래의 =LEN(H3:H5) 함수는 {8, 7, 6}의 3가지 값으로 이루어진 배열이 됩니다.

따라서 =SUM(LEN(H3:H5)) : 8+7+6 = 21 이 되고 이것은 범위의 총 글자수가 됩니다.

 

배열 함수 만들기

 

 

3. 배열함수의 논리값

 

배열함수의 가장 강력한 사용법은 논리를 적용할 수 있는 것입니다.

예를 들어 점수가 B열 10보다 큰 값의 C열 합을 구합니다.

1은 10보다 작음으로 1>10은 거짓이고 0이 되어 0 * 23 = 0이됩니다.

15의 경우 10보다 큼으로 15>10은 참이고 1이 되어 1*34 = 34가 되어 더해집니다.

이렇게 계산하면 아래의 배열 함수 =SUM((B1:B11>10)*C1:C11)는 195가 됩니다.

이런 식으로 참값만 연산되고 거짓은 연산되지 않도록 배열 함수를 작성하는 것이 가능합니다.

배열 함수에서 논리 연산자

 

요즘은 엑셀의 경우 SUMIF, COUNTIF처럼 조건함수를 추가되어 이런 배열함수를 직접 사용하는 경우가 줄어듭니다.

실제로도 편리하니 최신 버전의 EXCEL을 사용하는 것이 좋습니다.

다른 셀의 배열을 참조하는 것이 불가능하고 내부에 작성해야 함으로 길어지는 약점이 있습니다.

저도 다른 함수로 대체가 가능하면 쓰지 않는 쪽을 추천하고 있습니다.

 

---

 

안쓰는 편이 좋고 어렵기는 하지만 배열을 꼭 사용해야 하는 경우가 여전히 많습니다.

앞으로도 지속적으로 엑셀의 함수조합으로 여러가지를 만들어보려 합니다.

그 전에 이렇게 배열을 먼저 소개하려고 작성했는데 이해가 되셨으면 좋겠습니다.