이번 글에서는 엑셀의 배열수식을 알아보겠습니다. 배열수식을 사용하면 복잡한 문제를 간단히 해결할 수도 있고 여러 방면으로 쓸모가 있지만 이해하기가 쉽지 않고 사용하기도 까다로우므로 꼭 필요한 경우에만 사용하는 것이 좋습니다.
배열이란?
배열수식을 알아보기 전에 배열이 무엇인지 알아 보겠습니다.
배열은 프로그래밍언어에서 많이 사용됩니다. 자료를 순차적으로 저장하기 위한 구조라고 생각하면 되는데 엑셀에서는 다음과 같이 구성됩니다.
1차원 배열 - 한개의 행 또는 열로 구성됩니다.
가로로 된 숫자 배열입니다.
엑셀에서 수식으로 다음과 같이 표현합니다.
{55,23,60,89,12}
세로로 된 배열입니다.
수식으로 다음과 같이 표현합니다.
{55;23;60;89;12}
가로로 된 배열은 값 사이를 쉼표(,)로 구분했는데 세로 배열은 세미콜론(;)으로 구분합니다.
텍스트로 구성할 수도 있습니다.
수식으로 다음과 같이 표현합니다.
{"국어","영어","수학","과학","역사"}
2차원 배열 - 행과 열로 구성됩니다.
수식으로 다음과 같이 표현합니다.
{55,23,60,89;30,14,75,88;55,20,32,65}
열의 값은 값 사이를 쉼표(,)로 구분하고 행은 세미콜론(;)으로 구분합니다.
참고) 예제는 개념 이해를 돕기 위해 단순하게 만들었습니다. 이 예제를 이해하고 나면 실무에서 사용하는 복잡한 배열 수식을 이해하는데 도움이 되므로 잘 익혀 둘 필요가 있습니다. 배열 수식 실무 사례는 이 글의 끝에 소개한 링크를 참고하면 됩니다.
배열 수식 없이 합계 구하기
상품의 단가와 판매수량이 있는 표에서 전체 합계를 구할 때 일반적으로 다음과 같이 구합니다.
단가에 판매수량을 곱하여 판매금액을 구한 후 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,”노트”)
=SUM((C5:C14="신림문구")*(E5:E14="노트")*(G5:G14))
배열수식이 정상적으로 입력되었다면 조건을 만족하는 합계 789,000이 반환됩니다.
{=SUM((C5:C14="신림문구")*(E5:E14="노트")*(G5:G14))}
배열수식을 이용해서 값을 가져왔는데 수식이 어떻게 작동되는지 확인해보겠습니다.
수식에서 C5:C14="신림문구", E5:E14="노트" 부분을 그림으로 표현하면 다음과 같습니다.
C5:C14="신림문구" => 거래처명이 '신림문구'이면 TRUE로 바뀌고 아니면 FALSE로 바뀝니다.
E5:E14="노트" => 상품분류가 '노트'이면 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 이상 버전부터는 동적 배열 수식을 지원하므로 이전보다는 배열수식을 사용하기가 편해졌지만 근본적인 개념은 변하지 않았습니다.
배열수식 실무 응용 사례
엑셀의 백과사전 엑셀웍스. 자주 들러서 도움을 받습니다. 감사합니다.
완전이해잘됐어요! 감사합니다!!!
{=SUM(($V$3:$AZ$3<=$T$1)*($V4:$AZ4))} 셀 지우기가 안됩니다.
배열에 일부를 바꿀수없습니다. 나오고 확인 또는 취소 요청합니다. 어떻게 해야 하나요.
배열수식으로 결과가 표시된 범위 중 일부를 삭제하거나 수정하면 '배열의 일부를 변경할 수 없습니다' 오류가 발생합니다.
결과가 표시된 전체 범위를 선택하고 삭제해야 삭제할 수 있습니다.