Excel 고급 사용자가 되기 위해서는 배열 수식을 사용하는 방법을 알아야 합니다. 배열 수식을 사용하면 다른 수식으로 할 수 없는 계산을 수행할 수 있습니다. Excel에서 복잡하고 정교한 연산을 수행할 수 있습니다. Excel의 수식을 완벽하게 익히려면 배열 수식을 사용하는 방법을 알아야 합니다. 배열 수식을 사용하면 다음과 같은 복잡한 작업을 수행할 수 있습니다.
- 셀 범위에 포함된 문자 수 계산
- 특정 범위 내의 최소값 또는 상한과 하한 사이에 있는 숫자와 같이 특정 조건을 만족하는 숫자만의 합계
- 값 범위에서 매 n번째 값의 합계
참고 배열 수식은 Ctrl+Shift+Enter를 눌러 입력하기 때문에 "CSE 수식"이라고도 함
● 배열수식으로 값 구하기
일반적으로 하단과 같이 전체 수량에 대한 판매금액을 구할 경우에는 =C3*D3를 한 후에 마우스를 G3를 선택한 후 하단부위로 드래그를 하면 전체 제품별 총 판매금액이 계산됩니다.
[이를 배열수식을 통해 구하면]
(계산결과가 표시될 셀 범위 블록지정[E3:E6] - 수식입력[=C3:C6*D3*D6] - Ctrl + Shift + Enter)를 하면 수식의 앞과 뒤에 중괄호{} (파란색동그라미)가 나타납니다. 하단과 같이 총 금액에 대한 결과값이 계산됩니다. 주의할 것은 중괄호를 직접삽입하면 안됩니다. 이 계산 결과값은 위의 계산결과값과 다를 것이 없습니다. 하지만 열의 값이 수천, 수만개의 경우에 상단과 같이 곱한값을 구한후 수천, 수만개 열을 드래그 하여 채울려면 많은 시간이 소요될 것입니다. 이때 간단하게 배열수식을 적용하여 입력을 하면 빠른 시간안에 값을 계산할 수 있습니다. 바로 배열수식을 이용하는 가장 큰 장점입니다.
예를 들어보면 하단과 같이 E열 항목에 금액[단가*수량]의 값이 없을 경우에도 하단에 [판매금액의 합계]를 배열수식을 입력하여 간단히 구할 수 있습니다.
● 상수를 직접입력해서 구하기
상수란 계산되어지는 값으로 변하지 않는 값을 나타냅니다. 여러개의 행과 열로 구성된 배열 형태의 상수를 '배열상수'라고 합니다. 간단하게 말하면 숫자를 의미합니다.
하단에서와 같이 수량에 대한 부분을 배열 수식에 직접입력을 합니다. 하단부서는 4행 1열의 값(23,21,45,64)이 배열상수에 해당됩니다. 배열상수에서 묶는 중괄호는 직접 입력합니다( →{ 23;21;45;64}← 이부분). 배열상수를 사용하는 값으로는 숫자, 텍스트, 논리값(TRUE 또는 FALSE)등을 사용합니다.
배열상수를 입력할 때 지켜야할 규칙으로는 중괄호{}를 묶엇 상수를 입력, 행은 세미콜론(;)으로 구분하고 열은 쉼표(,)로 구분합니다. 즉 4행 1열의 배열상수{1;2;3;4}, 1행 4열의 배열상수{1,2,3,4}입니다.
● 원하는 조건과 배열수식의 조합이용한 계산
하단과 같이 데이터시트에서 [판매금액]을 배열수식을 이용하여 구하고 하단의 제한조건(원하는 항목만 추출)에 값을 배열수식을 이용하여 구해보도록 하겠습니다.
[해당셀 선택(F3) - 배열수식입력 해당범위선택(F3:F14) - 수식입력(=D3:D14*E3:E14) - (CTRL+SHIAT+ENTER)]를 하면
아래와 같이 F열 전범위에 배열수식이 적용되어 해당값이 계산됩니다. 이번에는 제한조건(원하는 항목만 추출)을 이용하고 배열수식을 적용하여 계산하도록 하겠습니다. 아래와 같이 원하는 항목은 '지점은 '마포점' 판매자는 '한마음' 제품은 'TV'인 판매금액의 총합'을 구하는 것입니다.
[구하조가 하는 셀 클릭 - 수식입력 =SUM((A4:A15="마포점")*(B4:B15="한마음")*(C4:C15="TV")*((F4:F15)))을 하고 - CTRL+SHIAT+ENTER]
● 결과 값(원하는 조건과 배열수식의 조합)
[원하는 조건]'지점은 '마포점' 판매자는 '한마음' 제품은 'TV'인 판매금액의 총합' 과 [배열수식]=SUM((A4:A15="마포점")*(B4:B15="한마음")*(C4:C15="TV")*((F4:F15))) 의 조합을 이용하여 결과값(512170)을 계산하였습니다.
