[엑셀] 배열수식 개념과 활용

배열 수식

여기에서는 배열 수식에 대하여 상세히 확인해본다. 배열의 개념과, 연산방법, 함수에의 적용에 대해 상세히 확인해볼 것이다.

배열은 보통 프로그래밍에서 다수의 데이터를 다룰 때 사용하는 일종의 자료(Data)를 다루는 구조이다.(이를 전산쪽에서는 자료구조라 한다.)

엑셀에서도 배열을 다룰 수 있다.

먼저 배열의 형식을 보자. 배열은 1차원, 2차원, 3차원... 등으로 이루어지는데 엑셀에서는 2차원 배열까지 사용한다.

1차원 배열은 하나의 행이나 열에 데이터를 나열하는 형식이다.

1차원배열 형식

위 그림처럼

데이터를 나열한 것이 1차원 배열이다.

엑셀에서 1차원 배열 입력 형식은 다음과 같다.

형식 : {값1,값2,값3,값4,값5}

형식 : {값1,값2,값3,값4,값5}

형식은 위와 같이 중괄호를 먼저 넣은 후 값을 콤마로 구분한다.

위의 형식에 유의하여 엑셀의 1차원배열 작성 절차는 다음과 같다.

- 먼저 배열을 작성할 영역을 선택한다.

- 선택 영역의 첫 번째 영역에 수식기호(=)를 넣은 후 위의 형식처럼 데이터를 입력한다.

- 입력이 완료되면 ctrl+shift+enter를 누른다.

1차원배열입력

​

2차원 배열은 행과 열을 모두 사용하여 데이터를 나열한다.

2차원배열 입력

위 그림처럼 데이터를 나열하면 2차원 배열이된다.

위 배열은 2행 5열짜리 배열이된다.(행이 2개, 열이 5개로 이루어져 있다)

엑셀에서 2차원배열을 입력하는 형식은 다음과 같다.

형식 : {값1,값2,값3;값1,값2,값3....}

1차원배열 입력과 다른 점은 하나의 행이 끝나는 지점은 콤마(,)가 아니라 세미콜론(;)을 입력한다는 것이다.

위 형식에 유의하면서 1차원 배열의 입력절차와 마찬가지로 배열을 입력해보자

 

2차원배열 입력

​

상기 그림처럼 입력이 완료되면 된다.

이번에는 배열을 기반으로 하는 수식을 작성해 보자

배열수식을 작성하려면 수식 기반의 셀 선택 범위를 이해하여야 한다.

연속적인 셀 선택은? 시작셀주소:마지막셀주소 형식으로 입력한다.

 

셀선택

떨어진 셀 선택은? 단일셀, 시작셀주소:마지막셀주소, 단일셀... 형식으로 입력한다.

 

셀선택2

배열을 기반으로 수식을 작성하면 형식은 다음과 같다.

형식 : 배열범위+배열범위2 (연산자는 +,*,/,- 모두 가능하다)

위 형식을 기반으로 수식을 입력하는 절차는

- 선택범위를 지정하고 수식기호를 입력한다.

※ 이때 주의할 점은 더하기를 하는 양쪽 배열의 범위가 같아야 하며, 수식결과가 나타나는 곳도 동일하게 범위를 지정해야 한다는 것이다.

- 배열의 범위를 지정한다.

- 연산자를 넣는다

- 다음 배열 범위를 지정하고 ctrl+shift+enter를 누른다.

배열기반 수식

위의 그림처럼 수식을 =b2:f2 + b3:f3 으로 넣는다.

 

 

배열기반 수식2

​

ctrl+shift+enter을 입력하여 결과를 표시한다.

그렇다면 함수를 기반으로 하는 배열 수식은 어떻게 계산하여야 할까?

함수에서의 배열 수식을 사용하기 위해서는 두가지를 알아야 한다.

먼저 계산형태의 함수 배열 수식과 조건형태의 함수 배열 수식이다.

먼저 함수 배열 수식 형태를 확인하자.

sum 함수를 예를 들어 보면(위의 그림에서 b2:f2, b3:f3의 값을 곱하고 더해보자, 물론, 더하기만 해도 된다)..

이렇게 식을 사용하면 b2값(1)과 b3값(6)을 곱하고(1*6=6), c2값(2)과 b3값(7)을 곱하고(2*7=14), d2값(3)과 d3값(8)을 곱하는식(3*8=24)으로 나머지들도 다 계산하게 된다. 이렇게 곱해진 수들을 다 더하면(sum함수니까)? 6+14+24+36+50=130이 된다.(sumproduct()함수와 동일한 결과다!!)

 

함수배열 수식1

 

함수배열 수식2

위와 같이 하는 것이 함수형 배열 수식이고, 이런 형태는 모든 함수에 다 적용이 가능하다.

두 번째로 논리형 함수 배열 수식이다.(공식 명칭은 아니다).

논리형 함수 배열 수식을 이해하기 위해서는 무엇보다도 논리식에 대한 이해가 필요하다.

우선, TRUE값은 엑셀뿐만아니라 모든 프로그래밍 언어에서 0이 아닌값으로 취급한다. 즉, 중요한 것은 0이 아닌 값은 모두 TRUE라는 것이다. 그렇다면 FALSE는? 0으로 취급한다.

왜냐하면, 0이 아닌 값들은 내용이 있는 것이기 때문에 참으로 표시하는 것이고, 0은 값이 없음을 뜻하기에 부정적으로 판단하여 0으로 지정하는 것이다.

논리식은 항상 두가지 기본 연산법을 가지고 있다.(Bool이라는 사람이 만들었다.)

연산자는 우리도 잘 아는 AND와 OR이다.

AND는 모든 조건이 TRUE일 때 최종적으로 TRUE라고 판단하는 것은 우리도 잘 알고 있다.

이것을 우리가아는 사칙연산자에 적용시켜보면..

먼저 +의 경우는, 0+0=0이므로 AND에 적용가능하다. 그러나, 0(FALSE)+1(TRUE)이나 1+0을 하게 되면 AND논리에 어긋나게 되므로 사용할 수 없다. 0-1도 마찬가지로 –1이 나오므로 0이아닌 값이되어 TRUE값이 나오기 때문에 AND에 사용할 수 없다.

곱하기(*)의 경우는 0*0=0, 0*1=0, 1*0=0, 1(TRUE) *1(TRUE)=1(TRUE)의 계산이 이루어지기 때문에 AND와 딱 들어맞는다. 그래서 AND를 논리곱이라고 표현한다.

OR의 경우는 0+0=0, 0+1=1, 1+0=1, 1+1=2 이므로 OR와 딱 들어맞으므로 OR를 논리합이라 한다.

1+1=2 인데?? 위에서 말했듯 0이 아닌값은 참이므로 2도 TRUE에 해당하기 때문에 OR 계산이 맞는 것이된다.

그렇다면, 먼저 AND논리형 배열 수식을 확인하자.

우선 AND든 OR든 중요한 건 결과가 TRUE나 FALSE같은 논리값이 나와야 한다는 것인데.. 그럼 수식 중 TRUE나 FALSE가 나오는 것은 우리가 잘 알고 있듯이 비교연산밖에는 없다.(>,>=,<,<=,=,<>)

그래서 논리형 배열 수식을 사용하려면 한쪽이 무조건 비교식이 되어야만 한다.

 

AND 논리형 배열수식

위의 그림에서는 배열 수식으로 비교식을 사용하였다.

AND 논리형 배열수식2

비교식의 결과는 TRUE,FALSE 값이 됨을 확인할 수 있을 것이다.

자, 이제 여기에 배열의 2행을 덧붙여 계산해보자.

AND형 논리배열수식3

결과는 아래와 같이 표시될 것이다.

AND논리배열수식4

​

즉, 위의 그림에서 수식을 보면 b2:f2의 셀값이 3이상인지 비교하여(b2:f2>=3), 나온 결과가 각각 FALSE(0), FALSE(0), TRUE(1), TRUE(1), TRUE(1)이며, 여기에 배열 2행값인 6,7,8,9,10을 각각 곱해주는 것이다.(0*6=0, 0*7=0, 1*8=8, 1*9=9, 1*10=10)

바로 이것이 AND형 논리배열수식이고, 함수에도 똑같이 적용이 가능하다.(sum함수를 사용하면 5개의 값을 모두 더할 것이다)

자, 이번에는 OR형 논리배열수식을 보자

OR논리형 배열 수식

위의 수식처럼 OR형태의 논리형 배열 수식을 입력했다.

​

OR논리형배열수식

수식결과를 보면 6(FALSE(0) + 6), 7(FALSE(0) + 7)까지는 문제가 없는데 그 다음 부터는(d13:f13) 8, 9, 10이 아니라 9(TRUE(1)+8), 10(TRUE(1)+9), 11(TRUE(1)+10) 이 되는 것을 알 수가 있다.

이유는 일단 수식이 +연산을 하고 있기 떄문이다.

한번 OR에대한 확인을 해보자.

​

 

OR연산

b3:b6, c3:c6까지 OR연산을 해보면 FALSE(0)+FALSE(0)=FALSE(0), FALSE(0)+TRUE(1)=TRUE(1), TRUE(1)+FALSE(0)=1, TRUE(1) + TRUE(1)=TRUE(2) 가 된다. 즉, OR에서는 덧셈연산이 되기 때문에 실제 배열 수식에 적용하게 되면 제대로된 결과값이 나오지 않는다. AND는 결과에 따라 0을 곱하거나 1을 곱하기 때문에 값이 안나오거나 자기자신의 값으로 정상적으로 나온다.(위의 예를 참고하자)

그렇다면 OR연산 결과가 제대로 나오게 하려면? 수식을 약간 조정하여야만 한다.

아래처럼 수식을 만들어보자

​

OR논리형 배열 수식

엑셀에 ISLOGICAL()같은 함수가 있으면 논리값 여부를 따져서 계산하면 좋겠지만 없다.

따라서, 일단 배열 1행(b2:f2), 배열 2행(b3:f3)까지를 먼저 OR연산을 수행한다.

다음으로 OR연산결과가 0(FALSE)보다 큰지 확인한다.

이 의미는 OR연산결과 0이 아닌값이 나오면 TRUE이고, 0이면 FALSE라는 것이 된다.

여기에 나온 값을 곱연산으로 수행해주면 제대로된 결과값이 나오게 된다.

​

 

OR논리형 배열 수식

즉, OR연산은 결과값이 나오면, 해당 결과값을 논리값인지 여부를 다시한번 판단해야 한다.

OR연산도 역시 함수에 적용할 수 있다.

문자열 추출함수로 함수형 배열 수식을 예로 들어보자(sum()함수는 여러군데서 자세히 예를 들고 있다. 다른 사이트를 참고하자)

함수배열 수식

​

위의 예에서 함수를 이용한 배열 수식을 보이고 있다.

left함수를 사용하여 내용을 추출하는 것인데, 배열수식 형태로 계산을 하고 있다.

결과는 아래와 같다.

함수배열 수식

​

마지막으로 이번에는 OR논리형 함수에 대해서 이야기 해보자

이해하기 쉽도록 sum함수를 통해 알아보자.

둘 중에 하나만 80점 이상만 넘으면 평가항목 C의 합계를 구하도록 수식을 입력했다.

​

함수 OR 배열수식 사용

OR를 사용하기 위해 +를 했지만, 상기 OR의 성질에 따라 +연산을 한후 0(FALSE)값 보다 큰지 비교한 후 평가항목C를 더하도록 하고 있다.

결과는 다음과 같다.

함수 OR 배열수식 사용

이제 엑셀에서의 배열수식에 대한 개념을 익혔을 것이다.

다음은 함수를 활용하여 배열수식을 적용하는 것인데 위의 사항만 유의 하면 AND논리형 함수 배열식과 OR논리형 함수배열식을 모두 사용할 수 있을 것이며, 모든 함수에 이 배열수식을 적용하는 것도 가능해진다.