콘텐츠로 건너뛰기
Home » 엑셀에서 배열 수식 사용하기

엑셀에서 배열 수식 사용하기

엑셀에서 SUM()이나 COUNT() 같은 함수를 사용하면 특정한 사각형 범위 영역에 대해서 합계나 개수를 구하게 되는데, 이것은 “여러 값을 한 번에 계산”하는 기능으로 볼 수 있습니다. 우리는 이미 이러한 함수를 사용하는 것에 대해서는 큰 어려움 없이 받아들이는데, 이 함수들은 여러 개의 값을 하나의 값으로 압축하는 형식으로 볼 수 있습니다. 엑셀에서는 이렇게 여러 개의 값을 하나로 압축하는 기능외에도 여러 개의 값에 어떤 연산을 적용하고, 다시 여러 개의 값으로 만드는 수식도 작성할 수 있데, 이렇게 여러 값을 한 번에 계산해주는 수식을 ‘배열 수식’이라고 합니다. 간단히 말하면 배열 수식은 ‘배열’을 포함하는 수식입니다.

사실 배열 수식은 좀 특별한(?) 방법을 사용하여 입력했어야 했는데, 오피스2021 및 오피스365 이상 버전에서는 더욱 똑똑해진 엑셀이 배열 수식으로 처리할 수 있는 수식은 자동으로 배열 수식으로 처리하기 때문에 알아두면 좋을 것 같습니다. 배열 수식은 여러 값에 대해 같은 연산을 적용하기 때문에, 불필요한 중간값을 위한 셀을 만드는 일을 생략하게 해주거나, 비슷한 기능을 하는 여러 함수 각각 외워서 사용해야 하는 필요가 없습니다.

배열 수식을 사용하면 하나의 수식으로 여러 범위의 값을 한 번에 채우거나, 대상 데이터 중에서 특정한 조건을 만족하는 셀의 값만 구분하여 계산하는 일을 처리할 수 있게 됩니다. 그만큼 수식은 조금 복잡해질 수 있지만, 원하는 조건을 수식에서 세세하게 제어할 수 있고 이러한 기능을 대체해주는 여러 수식을 모두 외우고 있을 필요가 없다는 장점도 있습니다.

반대로 배열 수식 자체는 식이 조금 길어질 수 있고, 초심자에게는 제법 어렵게 느껴질 수 있습니다. 결국 배열 수식을 사용하여 몇 개의 기본 함수로 필요한 계산을 구현하느냐, 조건을 적용할 수 있는 여러 버전의 함수를 잘 조합하여 적재적소에 잘 사용하느냐…는 것은 사용자의 선택이겠죠. 어쨌든 오늘은 엑셀에서 배열이란 무엇이고, 배열 수식이란 무엇인지에 대해 알아보는 시간을 가져보도록 하겠습니다.

먼저 배열 수식이 뭔지부터 살펴보겠습니다. 오피스 도움말은 MS의 도움말이 대부분 그렇듯이 읽어봐도 그리 도움이 되지 않습니다.

배열 수식은 배열의 하나 이상의 항목에 대해 여러 계산을 수행할 수 있는 수식입니다. 배열을 값의 행 또는 열 또는 행과 값 열의 조합으로 생각할 수 있습니다. 배열 수식은 여러 결과 또는 단일 결가를 반환할 수도 있습니다.

아마 영어로 작성된 도움말을 기계 번역으로 만든 것 같은데, 엑셀 정도의 사용자 층을 가지고 있는 프로그램에 대해서 도움말 관리를 이정도 밖에 안하는 것은 매우 실망스럽네요. 배열 수식에 대한 이 내용은 다음과 같이 이해하면 됩니다.

배열은 가로나 세로 방향으로 늘어선 연속된 값의 모음입니다. 배열은 가로나 세로 방향의 한 줄의 범위이거나, 가로 세로가 조합된 여러 줄의 사각형 범위를 가리킬 수도 있습니다. 배열에 대한 연산은 기본적으로 배열에 속한 각 값에 적용되어, 연산이 적용된 값들의 배열로 계산됩니다만, sum()과 같은 일부 엑셀 함수들은 배열을 입력으로 받아서 하나의 단일 값을 반환하기도 합니다.

배열 수식은 수식 내에 이러한 배열이 포함된 계산식을 말합니다.

배열 수식의 결과가 배열을 반환한다면, 그 결과 역시 원래의 배열의 모양과 동일하게 수식을 입력하는 셀의 오른쪽이나 아래쪽으로 인접한 셀에도 배열의 내용이 자동으로 확장됩니다. (이를 배열 자동 확장이라고 합니다.) 기존에는 배열을 결과로 반환하는 수식을 작성할 때에는 값이 출력될 범위를 선택해야 했습니다만, Office 365나 Office 2021 이상을 사용중인 경우에는 enter 키만 누르는 것으로 자동으로 배열 수식을 감지하고 배열인 결과를 확장하여 표시합니다. 최근의 오피스 버전에서는 엑셀이 자동으로 감지하므로, 배열 수식을 사용하면 번거로운 중간 단계를 생략하고 결과를 계산할 수 있습니다. 또 행이나 열 단위로 계산한 후 이를 복사하던 것을 하나의 수식에서 범위에 대해 계산하여 한 번에 결과를 만들 수 있습니다.

배열 수식 기본 예제

간단한 예를 한 번 살펴보겠습니다.

왼쪽에 보이는 표는 엑셀 도움말에서 가져온 것입니다. 영업 사원별로 판매한 차종과 단가가 표시됩니다. 여기서 총 매출은 각 행의 수량과 단가를 곱한 값의 합계입니다. 따라서 총 매출을 계산하고 싶다면, 이전에는 다음과 같이 작업하였을 것입니다.

  1. 첫 행에 대해 총 판매액을 구하기 위해 E2 셀에 C2 * D2 를 계산하는 수식을 입력합니다.
  2. E2의 값을 복사하거나 채우기 기능을 사용하여 E2:E11범위에 대해 각각의 총 판매액을 구합니다.
  3. E12에서 =SUM(E2:E11) 을 입력하여 총판매액의 합계를 구합니다.

배열 수식에서는 이러한 중간 과정 없이 총 합계를 구할 수 있습니다. =SUM(C2:C11 * D2:D11) 을 E12에 입력하고 엔터키를 입력합니다. (엑셀 2019 이하 버전에서는 ctrl + shift + enter 로 입력) 그러면 한번에 총합계가 계산됩니다. 엑셀 2019이하 버전에서 입력된 수식은 중괄호로 둘러싸여진 모습으로 표시됩니다. 다만, 이것은 엑셀이 배열 수식을 표시하는 방식일 뿐, 중괄호를 직접 입력한다고 해서 배열 수식으로 계산되지는 않습니다. (중괄호를 수식에 직접 포함시켜서 작성하면 문법 오류가 납니다.)

배열 수식은 어떻게 이런 계산을 한 번에 수행할 수 있을까요? 엑셀 내에서 배열은 벡터(Vector)와 비슷하게 취급됩니다.

  1. 배열과 단일 값의 연산 : 배열에 어떤 값을 곱하거나, 더하는 연산을 수행하면 배열 내 각각의 요소값에 대해서 동일한 연산이 자동으로 반복됩니다. 예를 들어 {A2, B2, C2} * 4 = {A2 * 4, B2*4, C2*4} 와 같이 계산됩니다.
  2. 배열과 배열의 연산 : 방향이 같은 배열을 서로 더하거나 곱하면, 두 배열의 각각의 요소를 짝지은 다음, 이를 계산한 결과가 새로운 배열로 만들어집니다. {A2; A3; A4} * {B2; B3; B4} = {A2*B2; A3*B3; A4*B4}와 같이 순서대로 짝지어진 요소끼리 연산된 새로운 배열일 만들어집니다.
  3. 방향이 다른 배열끼리의 연산 : 방향이 다른 배열을 연산하면, 배열의 각 요소에 대해 다른 배열과 연산하게 됩니다. 보통 이런 경우 2차원 배열로 배열이 만들어집니다. {A2; A3} * {B1, C1} = {A2 * {B1, C1}; A3 * {B1, C1}} 이 되고 최종 결과는
    {A2 * B1, A2 * C1; A3 * B1; A3 * C1} 과 같은 식으로 계산됩니다. 수식으로 보면 좀 복잡한데, 아래에서 그림으로 확인해보겠습니다.

위 예에서 각 행의 판매액은 판매 수량의 각 요소와 단가의 각 요소끼리 곱한 것입니다. 그리고 그 합계를 구하면 총 판매액이 됩니다. 따라서 E12 에 다음과 같은 수식을 입력합니다. (엑셀 2019 이하 버전에서는 입력 시에 shift + ctrl + enter 를 눌러서 수식 입력을 완료해야 합니다.)

=SUM(C2:C11 * D2:D11)

배열 수식에서 범위는 자동으로 배열로 변경되고, 위에서 설명한 배열 계산 방법 중 두 번째 규칙에 의해서 각각의 수량이 단가와 곱해진 값이 되어, 이를 합산한 최종 결과를 한 번에 구하게 됩니다.

위 예제 표에서 각 차종별 총 판매액을 입력하고 싶다면 어떻게 할까요? 하나의 값을 구해서 복붙하거나 채우기 기능을 사용해도 되지만, 이것도 배열 수식으로 처리할 수 있습니다. 이 방법은 구버전 엑셀과 차이가 있습니다. 이 글에서는 최신 버전 엑셀을 기준으로 설명하겠습니다. 구버전 엑셀을 사용하시는 분들은 아래의 구버전 예제와 같은 방법을 계속 적용하면 됩니다.)

배열 수식으로 차종별 판매액 구하기

배열 자동 확장이 지원되는 엑셀(엑셀2021, 오피스365)에서는 E2 셀을 선택하고 =C2:C11*D2:D11 이라고 두 개의 범위를 곱하는 수식을 입력하고 enter를 누릅니다.

이 연산은 범위를 자동으로 배열로 인식하는데, 두 개의 배열을 곱하여 각 요소가 곱해진 배열을 만들고, 원래 배열의 방향대로 값을 채워줍니다. 따라서 엑셀2021, 엑셀365 버전에서는 자동으로 배열의 모양대로 결과의 범위가 확장되면서 표를 한 번에 채우게 됩니다.

엑셀 2019 이전 버전에서는 약간 차이가 있습니다. 이전 버전의 엑셀은 배열 인식이나 자동 확장을 지원하지 않습니다. 먼저 배열을 표시할 영역 E2:E11을 미리 선택한 다음에, 수식 편집창에서 같은 수식을 입력합니다. 그런 다음, ctrl + shift + enter 를 입력하여 해당 수식이 배열 수식으로 처리되도록 입력합니다. (그냥 enter만 누르면 수식 오류가 납니다.)

앞서 소개한 방향이 다른 배열을 계산하는 케이스에 대해서도 예제를 한 번 살펴보겠습니다. 간단한 곱셈표를 만들어보겠습니다.

구구단 곱셈표 만들기

먼저 A1셀에 제목을 적어 줍니다. 그리고 B열에 2~9까지의 값을 입력합니다. 일일이 입력해도 좋지만, 2 와 3까지만 입력한다음, 채우기 기능을 사용해서 자동으로 채워주는 것도 가능합니다. 그리고 2행에 표의 가로가 될 숫자들을 채워줍니다.

그런 다음 B3 셀에서 =A3:A10*B2:I2 라는 수식을 입력하고 엔터를 누르면 동적 배열 수식 계산이 적용되어 단 한 번에 곱셈표가 완성됩니다.

배열을 직접 입력하기

보통의 엑셀 수식에서 배열을 직접 입력하는 경우는 드뭅니다. 대개는 범위를 대상으로 지정하면 수식 내에서 자동으로 범위가 배열로 변환되는 방법을 사용합니다. 수식에서 배열을 직접 작성하는 방법도 있습니다. 배열을 직접 입력하여 작성할 때에는 중괄호를 사용합니다. {1, 2, 3, 4, 5} 와 같이 콤마를 사용하여 구분된 값은 가로 방향의 배열입니다. 세로 방향의 배열을 만들 때에는 세미콜론(;)으로 각 값을 구분합니다. {6; 7; 8; 9; 10} 은 세로 방향으로 구분된 배열입니다. 이를 조합하여 2차원 배열을 표기할 수도 있습니다. {1, 2, 3; 4, 5, 6} 은 2 * 3 크기의 2차원 배열을 표현합니다.

수식을 내에 범위를 쓴 후에 F4키를 누르면 해당 범위에 대한 참조를 상대참조와 절대참조로 변경할 수 있는데, 이와 비슷하지는 않지만 F9키를 누르면 선택한 범위를 배열 상수로 변환합니다. 키를 누르는 즉시 선택된 범위의 값이 배열로 변경되어 입력됩니다.

배열 수식으로 조건문을 대체하기

배열 수식을 사용하면 조건문을 쉽게 대체할 수 있습니다. 예를 들어 위의 자동차 판매 실적 표에서 단가 30,000 이상인 차량의 총 판매액만 구한다고 생각해보겠습니다. F2에 다음과 같은 수식을 입력합니다.

=D2:D11 >= 30000

이 수식이 입력되면 F2:F11의 범위에는 각 배열의 값이 채워지는데, TRUE / FALSE 가 들어갑니다. TRUE는 해당 요소가 조건을 만족하여 참이되었다는 의미이고, FALSE는 조건을 만족하지 않는다는 뜻입니다. 컴퓨터에서 보통 참은 1, 거짓은 0이 됩니다. 따라서 TRUE / FALSE 값에 1을 곱하면 숫자값으로 타입이 변환됩니다. 따라서 수식을 아래와 같이 변경하면 0, 1로 채워지는 결과를 얻게 됩니다.

=(D2:D11 >= 30000) * 1

따라서 배열을 총판매액을 구하는 수식에 곱해주면, 조건을 만족하지 않는 항목은 0이 곱해지기 때문에 합계에서 제외되므로 단가 30,000 이상의 차량의 총 판매액을 구하게 됩니다.

=SUM(C2:C11 * D2:D11 * (D2:D11 >= 30000))

만약 여러 조건을 동시에 사용하고자 한다면 조건식을 계속 곱해주면 간단하게 확장할 수 있습니다. 예를 들어 25,000 이상 35,000 이하의 단가의 차량에 대한 판매액만 구하고자 한다면 수식은 다음과 같을 것입니다.

=SUM(C2:C11 * D2:D11 * (D2:D11 >= 25000) * (D2:D11 <= 35000))

이 수식을 배열을 사용하지 않고 계산하려면 한 번에 계산할 수는 없습니다. 차종별 판매액을 미리 계산한 다음, SUMIFS() 함수를 사용해야 합니다.

그 외의 배열 수식 활용 방법

오류를 무시하고 합산하기

범위에 대해 계산하는 어떤 엑셀 함수들은 범위 내에 오류 값이 있는 경우, 연산이 불가능한 경우가 있습니다. 예를 들어 SUM() 함수는 주어진 범위 내에서 숫자값이 있으면 모두 합산하는 함수인데, 오류 값을 필터링하지 않기 때문에 범위 내에 오류 값인 셀이 있으면 에러가 납니다.

배열 수식과 ISERROR() 함수를 사용하여 오류가 있는 셀의 값만 빈 문자열로 치환하면, 안전하게 합을 구할 수 있습니다. 여기에는 IF() 함수와 ISERROR() 함수를 사용합니다.

# 오류가 있는 셀만 빈 문자열로 바꾸기
=IF(ISERROR(D2:D11), "", D2:D11)

# 오류가 없는 셀만 합산하기
=SUM(IF(ISERROR(D2:D11), "", D2:D11))

IF(조건, 참일때, 거짓일때) 함수는 조건을 판별하고 조건이 참이면 참일 때 값을, 거짓이면 거짓일 때 값으로 평가합니다. (참일때 값과 거짓일 때 값은 생략하면 각각 TRUE, FALSE가 됩니다.) 따라서 에러 여부가 참일 때는 빈 문자열을, 거짓일 때는 원래의 배열을 주면 에러가 있는 셀만 빈 문자열로 평가됩니다. SUM() 함수는 문자열은 계산에서 자동으로 제외하기 때문에 오류가 포함된 범위에 대한 합계를 안전하게 처리할 수 있습니다.

비슷하게 COUNT() 함수 역시 숫자값이 있는 셀의 수만 세기 때문에 비슷한 방식으로 사용할 수 있겠습니다만, COUNT() 함수는 자체적으로 오류값은 무시하는 기능이 있습니다.

IF() 함수로 배열의 각 값을 변환하기

바로 위에서 살펴보았듯이, =IF(조건식, A, B) 으로 사용하는 IF() 함수는 조건식이 맞으면 A값을, 조건식이 맞지 않으면 B 값을 반환하는 함수입니다. 이 때 조건식에서 조건을 따지는 값이 배열이라면, IF() 함수가 배열을 리턴할 수 있습니다. 보다 적극적으로 A, B 자리에도 배열 수식이 들어갈 수 있습니다. 따라서 배열에서 특정 조건에 해당하는 요소만 변환하는 것도 가능합니다. 예를 들어 =IF(MOD(H1:H10, 2) = 1, <code>H1:H10 * 3, H1:H10) 라는 수식은 “H1:H10“의 범위에서 홀수인 수만 3배한 배열을 만들게 됩니다.

배열 수식에서 OR 조건을 사용하기

조건이 여러 개 일 때 조건 수식을 곱하는 것은 0 혹은 1을 곱하기 때문에 AND 연산처럼 작동합니다. 만약 여러 조건 중 하나 이상을 만족하는 셀에 대한 합계를 구하고자 할 때에는 어떻게 할까요? OR은 ‘논리합’에 대응하기 때문에 덧셈으로 대신할 수 있습니다. 단, 조건식들 끼리는 괄호로 묶어야 올바른 계산 결과를 얻을 수 있습니다

단가가 25,000 이하이거나 35,000 이상인 차량의 총 판매액을 구하고자 한다면 해당 조건 식들을 + 로 연결하면 됩니다. 비슷하게 OR() 함수로 하나만 만족하면 되는 여러 조건식을 묶을 수 있습니다.

=SUM(C2:C11 * D2:D11 * ((D2:D11 <= 25000) + (D2:D11 >= 35000)))

=SUM(C2:C11 * D2:D11 * OR(D2:D11 <= 25000, D2:D11 >= 35000))

이렇게 오늘은 엑셀의 배열 수식에 대한 기본적인 사용법과 활용방법을 살펴보았습니다. 배열 수식은 특히 복합적인 조건에 따라서 각 열의 값을 곱하거나 합산하는 작업을 수행할 때 유용합니다. 개인적으로는 배열 수식과 MATCH(), INDEX() 정도만 사용할 줄 알면 일반적인 사무 업무에서의 집계는 모두 수행할 수 있다고 생각합니다. 물론 배열 수식 없이 여러 함수의 조합으로 계산할 수 있는 것들도 있고, 엑셀은 새로운 버전이 나올 때마다 이러한 기능을 보강하는 함수들을 새로 추가하고 있지만, 한 편으로는 배열 수식과 관련된 기능을 강화하는 것을 보면 배열 수식은 엑셀을 활용하는 가장 큰 줄기 중 하나입니다. 잘 익혀두면 업무 효율을 크게 높일 수 있으니, 한 번쯤 관심을 가지고 도움말이나 자습서를 찬찬히 들여다보면서 연습해 두는 것을 추천합니다.