엑셀 배열함수와 배열수식 기초 및 사용법

요약

엑셀 배열함수/배열수식의 정의 및 대표적인 사용법을 간추려서 알아봅니다.

설명

배열함수 또는 배열수식을 이해하기 위해서는 '배열'이란 무엇인지 알아야합니다.
배열과 범위의 대표적인 3가지 차이점이 궁금하시다면 오빠두엑셀의 관련 포스트를 준비해드렸으니 해당 링크를 확인하세요!

시간이 없으신 분들을 위해 간단히 설명드리자면, 여러분은 셀과 범위의 차이점을 알고 계시나요? 그렇다면 축하드립니다! 여러분은 이미 배열의 90%를 이해하셨습니다!

• 셀 : [행과 열이 만나는 지점] 입니다.
• 범위 : [시트상에서 보여지는 셀의 집합] 입니다.
• 배열 : [데이터의 행렬 집합체] , 즉 [셀 안에 있는 데이터의 행렬집합체] 입니다.

간단히 예를 들자면, 범위는 '서울시' 또는 '경기도' 와 같은 지역이 되겠고, 배열은 '서울시의 공공유치원, ={강남유치원, 서초유치원, 압구정유치원, ...}' 또는 '서울시의 소방서, ={강남소방서, 서초소방서, 압구정소방서, ...}' 라고 표현할 수 있습니다.

범위는 겉으로 보이는 [셀]의 집합체, 배열은 안에 포함된 [데이터]의 집합체로 이해하면 편합니다.일반적인 함수의 경우 함수의 인수로서 '범위'가 들어가는 것이 대부분입니다. 따라서 처리영역이 범위 그 자체로 제한이 걸리게 되는데요. 예를들어, 서울시라는 범위는 강남구, 강서구 강북두 등등으로 범위에서 범위로의 이동은 편리하지만 실제로 처리하는 데이터 (예를들어 서울시의 중학교, 서울시의 병원 등)의 각 항목별로는 접근하기 어렵다는 단점이 있습니다.

반면 배열수식 또는 배열함수를 사용할경우, 함수의 인수로 '배열'이 입력되면서 배열의 각 항목 하나하나에 직접 함수를 적용할 수 있게 됩니다. 따라서 일반적인 함수보다 훨씬 간단하고 효율적으로 수식을 작성할 수 있게됩니다.

세부내용

1. 배열을 나타내는 방법

배열은 '데이터의 행렬 집합체'라고 말씀드렸습니다. 범위에서는 A,B,C 또는 1,2,3 으로 행과 열을 구분하였지만 배열에서는 콤마(",")와 세미콜론(";")으로 행과 열을 구분합니다. 하나의 배열은 중괄호("{}")안에 감싸집니다. 아래의 간단한 범위를 예로 보겠습니다.

[엑셀 시트 예제]
\    A    B    C
1  가   나   다
2  라   마   바
3  사   아   자
범위 => A1:C3
배열 => {가, 나, 다; 라, 마, 바; 사, 아, 차}

2. 배열수식을 입력하는 방법

일반 함수는 'Enter(엔터)'키로 입력하는 반면, 배열수식은 반드시 'Ctrl(컨트롤) + Shift(쉬프트) + Enter(엔터)'키로 입력을 해야합니다. 'Ctrl + Shift + Enter'로 입력을 하면 기존에 있던 함수가 중괄호("{}")안에 감싸지면서 배열수식으로 입력이 됩니다. (엑셀에서는 배열수식을 CSE함수(Ctrl+Shift+Enter)라고도 부릅니다.)

[배열수식 예제]
\    A    B
1  가   가
2  나   다
3  다   다

[상황1] C1셀에 다음과 함수를 입력한 뒤 'Enter(엔터)'로만 입력.
=SUM(IF(A1:A3<>B1:B3,1,0))
'// 결과값으로 0이 출력됩니다. 일반 함수로 입력되었으므로 IF함수의 첫번째 인수로 들어간 A1:A3, B1:B3이 배열로 반환되지 않게되고, 따라서 각 범위의 첫번째 셀인 A1과 B1만 IF함수의 인수로 입력됩니다.

[상황2] C2셀에 똑같은 함수를 입력한 뒤 'Ctrl + Shift + Enter'로 입력.
={SUM(IF(A1:A3<>B1:B3,1,0))}
=SUM({IF(A1<>B1,1,0), IF(A2<>B2,1,0), IF(A3<>B3,1,0)})
=SUM({IF(가<>가,1,0), IF(나<>다,1,0), IF(다<>다,1,0)})
=SUM({0,1,0})
=1
'//결과값으로 1이 출력됩니다. 배열수식으로 입력되면서 IF함수의 첫번째 인수로 들어간 A1:A3, B1:B3이 배열로 반환됩니다. 그리고 IF함수의 각 항목별로 출력된 값이 SUM함수의 인수로 들어가면서 최종 합계를 구하게 됩니다.

3. 기본적인 배열수식의 사용예제

아래 날짜별 매출액을 보겠습니다.
(예제파일은 상단의 이미지링크를 클릭하여 다운로드하세요)

3-A. SUMIF 함수를 사용 (일반수식)

SUMIF에 들어가는 인수는 아래와 같습니다.

= SUMIF ( 참조범위, 참조, [합계범위] )

보시다시피 SUMIF 함수에는 모두 '범위'가 인수로 들어가므로 배열수식으로 이용할 수 없는데요. 만약, 날짜별로 매출액이 적혀있고 각 월별로 매출액의 합계를 구할 때 SUMIF 함수를 이용해야 한다면 어떻게 해야 할까요?

우선 매출액의 합계 기준이 '월' 이므로, MONTH 함수를 이용하여 각각의 날짜에서 '월' 값을 출력해야 합니다.

'월'값을 출력했으면 SUMIF 함수를 이용하여 월별로 매출액의 합계를 계산합니다.

= SUMIF ( D7:D17, 4, C7:C17 )

[Q&A링크] "=SUMIF ( MONTH(B7:B17), 4, C7:C17 ) 로 입력하면 안되나요?"

3-B. SUMPRODUCT 함수를 사용 (배열수식)

SUMPRODUCT 함수는 SUMIF 함수, COUNTIF 함수, AVERAGEIF 함수 등을 대신하여 다양한 방법으로 응용할 수 있는 대표적인 배열수식 중 하나입니다. SUMIF 함수를 사용할 때에는 MONTH 함수를 이용하여 별도의 범위에 '월'값을 출력하여 월별합계를 계산해야했지만, SUMPRODUCT 함수를 사용할 경우 별도의 중간과정 없이 하나의 수식으로 간단하게 해결할 수 있습니다. SUMPRODUCT 함수에 관한 자세한 설명은 관련포스트를 참고하세요.

예제파일에서 사용된 배열는 아래와 같습니다.

{= SUMPRODUCT ( --(MONTH(F7:F17)=4)*G7:G17)}
'// 배열수식은 반드시 Ctrl + Shift + Enter로 입력해야 합니다.

위 배열수식을 각 단계별로 해석해보겠습니다.

• {MONTH(F7:F17)}의 배열화 :
  {1, 1, 3, 4, 4, 4, 5, 5, 5, 6, 6}
• 출력된 배열과 4를 비교 :
  {FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, FALSE, FALSE, ...}
• -- 를 통해 논리값을 숫자화 :
  {0, 0, 0, 1, 1, 1, 0, 0, ..}
  '// => 이 단계에서 4월이 아닌 값은 '0'이 출력되면서 곱셈값이 '0'이 됩니다.
  
• 각 매출액의 배열과 곱셈 :
  {0, 0, 0, 1, 1, 1, 0, 0, ... } * {40000, 74000, 57000, 47000, 67000, 68000, .. }
• SUMPRODUCT의 최종 출력 :
  = SUMPRODUCT ( {0, 0, 0, 47000, 67000, 68000 ... } )
  = 182,000

4. 결론 :: 배열수식 이용의 장점과 단점

요약해서 말씀드리자면, 현재(2018년 기준으로) 최신버전인 엑셀 2016을 이용하고 있을 경우, 배열수식의 사용은 필수가 아닌 선택입니다. 하지만 일반함수와 다르게 중간과정없이 수식을 입력할 수 있게되므로 여러방면에서 유용하게 이용할 수 있습니다.

■ 배열수식을 이용하는 것의 장점

• 배열수식을 이용하는 것은 (일반적으로) 적은양의 메모리를 사용하므로 계산속도가 빨라집니다.
• 중간과정 없이 수식을 입력할 수 있으므로 시트가 간결해지고 효율적으로 이용할 수 있게됩니다.
• 배열수식은 일반적인 함수로는 수행할 수 없는 (하지만 최신버전의 엑셀에서는 대부분 수행가능한) 계산을 도와줍니다.
• 사견이지만, 배열수식은 대게 엑셀 숙련자만 이해할 수 있는 내용이므로 시트보호를 하지 않더라도 일반 초보자의 경우 시트내용을 변경하지 않도록 하는 강제시트보호 기능이 있습니다.

■ 배열수식을 이용하는 것의 단점

• 배열수식은 전체 행 또는 열(예: A:A, B:B, 1:1 등) 을 참조범위로 이용할 수 없습니다.
• 배열수식은 대게 작성한 사람을 제외한 다른 사람이 함수를 분석하기 어려운 경향이 있으므로 검토에 어려움을 겪을 수 있습니다.
• 배열수식은 대게 일반함수보다 적은양의 메모리를 사용하지만, (적은 경우로) 많은 양의 배열이 동시에 적용된 배열수식을 넓은 범위에 걸쳐 적용할 경우 일반함수보다 느리게 동작할 수도 있습니다.

다른 주의사항

• 1. SUMIF 함수의 첫번째 인수는 '참조범위', 즉 [범위]형태로만 입력가능합니다. 만약 MONTH(B7:B17) 를 Ctrl + Shift + Enter키를 통해 배열수식형태로 입력하게되면 [범위]가 아닌 [배열] 형태로 입력되므로 엑셀에서 잘못된 수식이라는 오류메세지를 출력합니다.