강좌 > 엑셀중급강좌 > 배열 수식 > 엑셀 배열수식 제대로 이해하기

엑셀 배열수식 제대로 이해하기

이번 글에서는 엑셀의 배열수식을 알아보겠습니다. 배열수식을 사용하면 복잡한 문제를 간단히 해결할 수도 있고 여러 방면으로 쓸모가 있지만 이해하기가 쉽지 않고 사용하기도 까다로우므로 꼭 필요한 경우에만 사용하는 것이 좋습니다.

 

배열이란?

배열수식을 알아보기 전에 배열이 무엇인지 알아 보겠습니다.

배열은 프로그래밍언어에서 많이 사용됩니다. 자료를 순차적으로 저장하기 위한 구조라고 생각하면 되는데 엑셀에서는 다음과 같이 구성됩니다.

1차원 배열 - 한개의 행 또는 열로 구성됩니다.

가로로 된 숫자 배열입니다.
1차원 가로 배열

엑셀에서 수식으로 다음과 같이 표현합니다.
{55,23,60,89,12}

 

세로로 된 배열입니다.

1차원 세로 배열

수식으로 다음과 같이 표현합니다.
{55;23;60;89;12}

가로로 된 배열은 값 사이를 쉼표(,)로 구분했는데 세로 배열은 세미콜론(;)으로 구분합니다.

 

텍스트로 구성할 수도 있습니다.
1차원 가로 텍스트 배열

수식으로 다음과 같이 표현합니다.
{"국어","영어","수학","과학","역사"}

 

2차원 배열 - 행과 열로 구성됩니다.

2차원 배열

수식으로 다음과 같이 표현합니다.
{55,23,60,89;30,14,75,88;55,20,32,65}

열의 값은 값 사이를 쉼표(,)로 구분하고 행은 세미콜론(;)으로 구분합니다.

 

첨부(실습용 엑셀파일) : 엑셀-배열수식-이해하기.xlsx

참고) 예제는 개념 이해를 돕기 위해 단순하게 만들었습니다. 이 예제를 이해하고 나면 실무에서 사용하는 복잡한 배열 수식을 이해하는데 도움이 되므로 잘 익혀 둘 필요가 있습니다. 배열 수식 실무 사례는 이 글의 끝에 소개한 링크를 참고하면 됩니다.

 

배열 수식 없이 합계 구하기

상품의 단가와 판매수량이 있는 표에서 전체 합계를 구할 때 일반적으로 다음과 같이 구합니다.

배열 수식 없이 합계구하기

단가에 판매수량을 곱하여 판매금액을 구한 후 SUM 함수로 판매금액 합계를 구하거나

  • 판매금액 : =C5*D5
  • 판매 금액 합계 : =SUM(E5:E7)

SUMPRODUCT 함수로 한 번에 구할 수 있습니다.

  • =SUMPRODUCT(C5:C7,D5:D7) => 195,000원

 

 

배열수식을 이용하여 합계 구하기

이번에는 배열 수식을 이용하여 위의 표에서 전체 합계를 한번에 구해보겠습니다.

배열수식을 이용하여 합계 구하기

[E12] 셀에 다음 수식을 입력하고 [Ctrl +Shift + Enter]를 누릅니다.
=SUM(C5:C7*D5:D7)

배열수식이 정상적으로 입력되었다면 전체 합계 195,000이 반환됩니다.

일반 수식과 다르게 배열 수식을 입력할 때는 수식 입력 후 [Ctrl +Shift + Enter]를 눌러야 합니다.
* 엑셀 2021, Microsoft 365 이상 버전은 동적 배열을 지원하므로 [Ctrl +Shift + Enter]를 누르지 않고 [Enter]만 눌러도 됩니다.

 

[E12] 셀을 선택하고 수식 입력줄을 확인해 보면 입력된 수식 양쪽에 중괄호({})가 붙어 있습니다. 배열수식이 입력되었다는 뜻입니다.
{=SUM(C5:C7*D5:D7)}

배열수식 입력 방법

 

이 수식은

  • 각 상품의 단가와 판매수량을 곱한 후에 메모리에 저장하고
  • 메모리에 저장한 것을 SUM하라는 의미입니다.

배열 수식 계산 과정

 

이 배열수식이 엑셀 내부에서 어떻게 작동하는지 확인해 보겠습니다.

배열 수식이 입력된 [E12] 셀을 선택하고  리본 메뉴 [수식] 탭 → [수식 분석] 그룹 → [수식 계산] 버튼을 누릅니다.

수식 계산 메뉴

 

[수식 계산] 대화상자가 나타나고 입력된 수식이 표시됩니다.

[수식 계산] 대화상자에서는 수식이 계산되는 단계를 차례대로 따라가면서 확인할 수 있고 배열 수식인 경우에는 수식에서는 보이지 않는 배열의 구조까지 표시해 줍니다.

수식이 계산되는 단계를 보려면 <계산> 버튼을 누릅니다.

수식 계산 대화상자

 

수식이 다음과 같이 바뀝니다.

수식 계산 대화상자 - 계산

=SUM({100000;35000;60000})
위에서 설명한대로 배열에서 값 사이가 세미콜론(;)으로 구분되어 있으면 세로 배열입니다.

{100000;35000;60000}는 아래 그림과 같이 두 셀의 값(단가와 판매수량)을 곱해서 메모리에 저장된 부분입니다.

배열 수식 계산 과정

 

<계산> 버튼을 또 누르면 SUM 함수는 배열 값 {100000;35000;60000}을 더해서 195,000원을 반환합니다.

배열 수식 계산 단계

 

 

배열수식을 이용하여 조건을 만족하는 합계 구하기

이번에는 배열 수식으로 판매 자료에서 거래처명이 '신림문구'이고 상품분류가 '노트'인 것의 판매금액 합계를 구해 보겠습니다.

이번 예제는 SUMPRODUCT 함수와 SUMIFS 함수로도 답을 구할 수 있습니다.

=SUMPRODUCT((C5:C14=”신림문구”)*(E5:E14=”노트”)*G5:G14)
=SUMIFS(G5:G14,C5:C14,”신림문구”,E5:E14,”노트”)

배열수식을 이용하여 판매자료에서 조건을 만족하는 합계 구하기

[B18] 셀에 다음 수식을 입력하고 [Ctrl +Shift + Enter]를 누릅니다.

=SUM((C5:C14="신림문구")*(E5:E14="노트")*(G5:G14))
배열수식이 정상적으로 입력되었다면 조건을 만족하는 합계 789,000이 반환됩니다.

[B18] 셀을 선택하고 수식 입력줄을 확인해 보면 입력된 수식 양쪽에 중괄호({})가 붙어 있습니다. 배열수식이 입력되었다는 뜻입니다.
{=SUM((C5:C14="신림문구")*(E5:E14="노트")*(G5:G14))}

 

배열수식을 이용해서 값을 가져왔는데 수식이 어떻게 작동되는지 확인해보겠습니다.

수식에서 C5:C14="신림문구", E5:E14="노트" 부분을 그림으로 표현하면 다음과 같습니다.

C5:C14="신림문구" => 거래처명이 '신림문구'이면 TRUE로 바뀌고 아니면 FALSE로 바뀝니다.
E5:E14="노트" => 상품분류가 '노트'이면 TRUE로 바뀌고 아니면 FALSE로 바뀝니다.

배열 수식에서 TRUE, FALSE 판별 과정

 

거래처명과 상품분류 조건을 비교해서 TRUE, FALSE로 바꾸고 판매금액과 곱하는 수식을 그림으로 표현하면 다음과 같습니다.
(C5:C14="신림문구")*(E5:E14="노트")*(G5:G14)

배열 수식에서 논리값 변환

 

엑셀에서 논리값 TRUE와 FALSE가 계산식에 포함되면 TRUE는 1, FALSE는 0으로 바뀝니다.

따라서 위의 그림은 아래와 같이 바뀝니다.

배열 수식에서 논리값을 숫자로 변환한 결과

 

첫 번째 항목을 예를 들어 보면
0 * 0 * 119,000 = 0 (숫자에 0을 곱하면 무조건 0이 됨)

다섯 번째 항목은
1 * 1 * 624,000 = 624,000

결과적으로 수식은 0이 아닌 것(624,000원, 165,000원)만 더해서 최종 결과 789,00원을 구해줍니다.

 

※ 배열수식의 단점, 한계

  • 배열수식은 계산의 중간 과정이 보이지 않아서 이해하기 어렵습니다. 위의 예에서도 보았듯이 값을 비교하여 TRUE, FALSE로 바꾸고 곱하는 과정이 시트에서 바로 보이지 않습니다. 수식을 선택하고 [F9]키를 누르거나 [수식 계산] 메뉴로 볼 수도 있지만 이 마저도 이해하기 쉽지 않습니다.
  •  배열수식을 잘 만들어도 나의 동료는 배열수식이 무엇인지도 모를 수 있고, 안다고 하더라도 내가 만든 배열수식을 제대로 이해하지 못할 수 있습니다. 동료들과 같이 사용해야 하는 엑셀 파일이라면 배열수식은 사용하지 않는 것이 좋습니다.
  • 수식이 복잡하고 배열의 크기가 큰 경우 엑셀의 속도가 늦어질 수 있습니다.
  • Microsoft 365, 엑셀 2021 이상 버전부터는 동적 배열 수식을 지원하므로 이전보다는 배열수식을 사용하기가 편해졌지만 근본적인 개념은 변하지 않았습니다.

 

 


배열수식 실무 응용 사례

대소문자 구분해서 찾기 엑셀에서 대소문자 구분해서 찾기 - 이번 글에서는 엑셀에서 대소문자를 구분해서 값을 찾는 방법을 알아보겠습니다. 아래와 같이 어떤 회사에서 컴퓨터 부품 코드 정보의 '부품 코드'를 대소문자를…

엑셀에서 대소문자 구분해서 찾기 더 보기 »

엑셀에서 조건을 만족하는 텍스트 합치기(병합) - 엑셀에서 다음과 같이 고객명이 동일한 조건을 만족하는 주문상품명 텍스트를 합쳐야 할 때 TEXJOIN 함수와 배열 수식을 조합해서 많이 사용했는데 FILTER,…

엑셀에서 조건을 만족하는 텍스트 합치기(병합) 더 보기 »

LARGE 함수 사용법 LARGE 함수 - 몇 번째로 큰 값 구하기 - 엑셀 LARGE 함수는 배열이나 범위에서 몇 번째로 큰 값을 구해줍니다. 가장 큰 값을 구하거나 2번째 또는 3번째 등 몇 번째로…

LARGE 함수 - 몇 번째로 큰 값 구하기 더 보기 »

SMALL 함수 사용법 SMALL 함수 - 몇 번째로 작은 값 구하기 - 엑셀 SMALL 함수는 배열이나 범위에서 몇 번째로 작은 값을 구해줍니다. 가장 작은 값을 구하거나 2번째 또는 3번째 등 몇 번째로…

SMALL 함수 - 몇 번째로 작은 값 구하기 더 보기 »

SUMPRODUCT 함수 - 숫자를 곱하고 합계 구하기 - SUMPRODUCT 함수는 SUM 함수와 PRODUCT 함수를 합쳐놓은 함수입니다. [PRODUCT함수 바로가기] PRODUCT 함수는 주어진 인수를 모두 곱해주고 SUM 함수는 인수를 모두…

SUMPRODUCT 함수 - 숫자를 곱하고 합계 구하기 더 보기 »

INDEX, MATCH를 이용한 다중조건으로 찾기 INDEX, MATCH 함수를 이용한 다중조건으로 값찾기 - 다음 그림과 같이 상품정보에서 단가를 연도별로 관리한다고 할때 단가를 찾으려면 상품코드에 연도 조건을 추가해서 찾아야 합니다. 이 경우에 VLOOKUP 함수나…

INDEX, MATCH 함수를 이용한 다중조건으로 값찾기 더 보기 »

 

엑셀웍스 책 출간 안내

그동안 엑셀웍스에 올린 '엑셀 강좌', '함수 사용법', 여러 '실무 예제'에서 핵심만 뽑아 '된다! 엑셀 수식 & 함수' 책으로 출간하였습니다.

엑셀웍스 책 출간안내  자세히 보기

온라인 구매처: 예스24   교보문고   알라딘 

“엑셀 배열수식 제대로 이해하기”의 5개의 댓글

  1. 길진건

    엑셀의 백과사전 엑셀웍스. 자주 들러서 도움을 받습니다. 감사합니다.

    1. 배열에 일부를 바꿀수없습니다. 나오고 확인 또는 취소 요청합니다. 어떻게 해야 하나요.

      1. 배열수식으로 결과가 표시된 범위 중 일부를 삭제하거나 수정하면 '배열의 일부를 변경할 수 없습니다' 오류가 발생합니다.
        결과가 표시된 전체 범위를 선택하고 삭제해야 삭제할 수 있습니다.

댓글 남기기

Scroll to Top