엑셀 배열 수식 사용법을 알아보는 글입니다. 처음 접하는 경우 배열수식 쓰는 이유 등이 궁금할 수 있습니다. 이 글에서는 배열이란 무엇인지 알아보고 배열수식을 사용해서 합계를 구하는 방법을 알아보겠습니다.
엑셀 배열 수식 사용법 연습 파일을 다운로드 받아서 연습을 해보면 이해가 훨씬 쉽습니다.
엑셀 배열 수식이란?
배열 수식을 사용하는 방법을 알아 보기 전에 먼저 엑셀 배열 수식이 뭔지 알아 보겠습니다. 아래 사진을 보시면 A1 셀에서 C3 셀까지 입력이 되어 있습니다. A1~C3 범위는 A1:C3으로 표기가 됩니다. 즉, 콜론(:)으로 셀이 연결이 되어 표시가 됩니다.
그런데 배열로 입력을 할 경우 같은 데이터 범위인데도 위 사진처럼 다르게 표시가 됩니다. 가로로 연결된 부분의 경우 콤마(,)로 표시가 됩니다. 세로로 연결된 부분의 경우 세미콜론(;)으로 연결이 됩니다. 그리고 배열 수식 시작과 끝은 중괄호({})로 입력이 됩니다.
범위는 그저 셀에 표시되어 있는 셀의 집합이라고 할 수 있는 반면, 배열은 데이터 항목의 집합이라고 표현할 수 있습니다.
배열의 경우 데이터 항목끼리 모여있습니다. 때문에 배열을 이용하면 상황에 따라서 좀 더 빠르고 편리하게 값을 구할 수 있다는 장점이 있습니다.
배열수식 사용하지 않고 합계 구하기
우선 배열수식을 사용하지 않고 합계를 구하는 방법을 알아보겠습니다. 아래 사진을 보시면 과일종류, 단가, 판매개수가 나와 있는 데이터가 있습니다.
여기서 단가와 판매개수를 곱한 총판매금액을 각각 구하겠습니다. 그리고 SUM함수를 이용해서 총판매금액의 합계를 구해보겠습니다.
총판매금액(D2) 셀에 =을 입력하고 단가(B2)셀을 클릭합니다. 그 후 * 기호를 입력하고 판매개수(C2) 셀을 클릭합니다. 그 후 엔터키를 누릅니다. 그리고 아래 셀에도 자동채우기를 합니다. 그럼 위 사진처럼 각 과일 별 총판매금액을 구할 수 있습니다.
다음으로 아래 사진처럼 합계(D9) 셀에 =SUM이라고 입력을 한 후 D2부터 D8까지의 범위를 선택합니다. 그 후 엔터키를 누르면 총판매금액의 합계가 나옵니다.
이렇게 배열수식을 사용하지 않고 2단계의 과정을 거쳐서 합계를 구해볼 수 있습니다.
총판매금액 합계는 SUMPRODUCT라는 함수를 이용해서 구할 수도 있습니다. 아래 사진처럼 =SUMPRODUCT를 입력합니다. 그 후 단가 범위(B2:B8)을 선택합니다. 그리고 곱하기 기호(*)를 입력하고 판매개수 범위(C2:C8)을 선택합니다. 그 후 엔터키를 누릅니다.
그럼 마찬가지로 총판매금액 합계가 나오게 됩니다.
엑셀 배열 수식 사용법: 배열수식 사용해서 합계 구하기
이번에는 배열수식을 사용해서 합계를 구해보겠습니다. 우선 합계 옆 D9셀에 커서를 두고 =SUM을 입력합니다. 그 후 단가 전체 범위인 B2:B8을 선택합니다. 그리고 곱하기 기호(*)를 입력합니다. 이번에는 판매개수 전체 범위인 C2:C8 범위를 선택합니다.
그 후 엔터키를 누릅니다. 참고로 엑셀 2021, Microsoft365 이후인 경우에만 엔터키를 눌렀을 때 배열 수식이 적용됩니다. 엑셀 2021, Microsoft365 이전이라면 Ctrl + Shift + 엔터키를 눌러주세요.
그럼 이번에도 마찬가지로 같은 합계 결과가 나오게 됩니다. 배열 수식을 사용하지 않는 경우 2번의 과정을 거쳐야 합계를 구할 수 있었는데요. 배열 수식을 이용하니 한 번에 합계를 구할 수 있었습니다.
그런데 배열 수식을 이용하는 과정이 생략이 되어서 계산 과정이 궁금할 수 있습니다. 따라서 계산 과정을 좀 더 자세히 살펴보도록 하겠습니다.
그러기 위해 우선 배열 수식을 사용해 합계를 구한 셀을 클릭한 상태로 상단 메뉴 중 수식 – 수식 계산으로 들어갑니다.
그럼 아래 사진처럼 수식 계산 창이 나옵니다. 여기서 ‘계산’이라고 되어 있는 글자를 한 번 클릭하면 계산 식이 자세하게 나옵니다.
계산식을 보면 각 과일 별 총 판매금액이 세미콜론(;)으로 연결이 되어 있습니다. 배열 수식에서 세로로 값이 입력되어 있는 경우 세미콜론(;)으로 입력이 된다고 했었습니다. 따라서 위 사진처럼 각 값들이 세미콜론(;)으로 입력이 되어 있습니다.
위 사진의 수식은 세로로 입력되어 있는 각 값들을 SUM 함수를 이용해서 다 더하겠다는 의미입니다. 이 상태에서 ‘계산’ 글자를 한 번 더 클릭합니다.
그럼 아래 사진처럼 총판매금액의 합계 값이 산출됩니다.
배열수식 사용해서 조건을 만족하는 값의 합계 구하기
이번에는 배열수식을 사용해서 2가지의 조건을 만족하는 값의 합계를 구해보겠습니다. 예시로 상점 별, 과일 종류 별 단가 데이터를 토대로 2가지 조건에 만족하는 값을 구해보겠습니다.
<2가지 조건>
▶︎상점명이 ‘신선상점’에 해당되고
▶︎과일종류가 ‘사과’에 해당되는
→값의 합계 구하기
예시로 지정한 2가지 조건은 위와 같습니다. 위 조건의 합계를 구하기 위해서 함수를 사용할 수도 있는데요. SUMIFS 함수 또는 SUMPRODUCT 함수를 사용할 수 있습니다.
우선 SUMIFS 함수를 사용해서 합계를 구하는 방법입니다.
▶︎=SUMIFS(합계범위, 조건범위1, 조건1, 조건범위2, 조건2…)
SUMIFS 함수 내 인수는 위와 같습니다. 따라서 아래 사진처럼 함수 식을 입력할 수 있습니다. 엔터키를 누르면 4000이라는 값이 나오게 되는데요. 4000원이란 ‘신선상점’에 해당되면서 동시에 ‘사과’에 해당되는 값의 합을 말합니다.
▶︎입력해야 하는 함수식: =SUMIFS(D2:D8,B2:B8,”신선상점”,C2:C8,”사과”)
이번에는 SUMPRODUCT 함수를 이용해서 값의 합계를 구하는 방법입니다. SUMPRODUCT 함수는 지정한 배열을 곱한 후 곱한 값을 모두 더하는 함수입니다.
<2가지 조건>
▶︎상점명이 ‘신선상점’에 해당되고
▶︎과일종류가 ‘사과’에 해당되는
→값의 합계 구하기
아래 사진처럼 함수를 입력한 후 엔터키를 누르면 이번에도 4000이라는 값이 나옵니다.
▶︎입력해야 하는 함수식: =SUMPRODUCT((B2:B8=”신선상점”)*(C2:C8=”사과”)*D2:D8)
이번에는 드디어 배열 수식을 이용해서 합계를 구하는 방법입니다.
<2가지 조건>
▶︎상점명이 ‘신선상점’에 해당되고
▶︎과일종류가 ‘사과’에 해당되는
→값의 합계 구하기
우선 =SUM을 입력한 후 첫 번째 조건을 입력합니다. 상점명 범위를 선택한 후 =을 입력하고 큰 따옴표 안에 ‘신선상점’이라고 입력을 합니다. 그리고 곱하기 기호 (*)를 입력합니다.
▶︎입력해야 하는 함수식: =SUM((B2:B8=”신선상점”)*(C2:C8=”사과”)*(D2:D8))
괄호를 다시 열고 두 번째 조건인 과일 종류 범위를 선택한 후 =을 입력한 후 큰 따옴표 안에 ‘사과’를 입력합니다.
괄호를 닫고 곱하기 기호 (*) 를 입력하고 단가 범위를 모두 선택합니다. 그 후 괄호를 2번 입력한 후 엔터키를 누르면 마찬가지로 4000이라는 값이 나옵니다.
근데 값이 어떤 과정을 거쳐서 이렇게 나오게 되었는지 궁금할 수 있습니다. 그래서 계산 과정을 좀 더 상세히 뜯어 보겠습니다.
우리는 ‘신선상점’에 해당되면서 동시에 ‘사과’에 해당되는 값의 단가를 모두 더할 것이었습니다. 즉, 조건에 해당되는 경우 값이 True(참)이 됩니다.
따라서 아래 사진처럼 신선상점, 사과에 해당되는 값은 True라고 인식이 됩니다. 엑셀에서 True는 1, False는 0으로 바뀌어 표시가 됩니다.
바뀌어 표시된 1,0 숫자를 이용해서 곱하기를 해보겠습니다. 위 사진의 첫 번째 빨간 색 상자 부분을 보면 1 × 1 × 2000 = 2000이라는 결과값이 나옵니다.
그리고 위 사진의 3행의 경우 0 × 0 × 4500 = 0이라는 결과값이 나옵니다. 위 사진의 두 번째 빨간 색 상자 부분의 경우 1 × 1 × 2000 = 2000이라는 결과값이 나오게 되죠.
따라서 2가지 조건이 모두 True에 해당되는 2행, 8행의 값을 더하면, 2000 + 2000 = 4000이라는 결과값이 나오게 되는 것입니다.
상황에 따라 배열 수식을 이용 하는 것이 더 편리한 경우도 있습니다. 하지만 배열 수식을 사용하면 계산 과정이 생략이 되어서 어떻게 해서 이 값이 나오게 됐는지 이해 하기가 어려울 수 있습니다.
또 수식이 복잡한 경우에 속도가 느려질 수 있습니다. 따라서 배열 수식은 정말 꼭 필요한 경우에만 사용 하는 것이 좋겠습니다.
이상 엑셀 배열 수식 사용법에 관한 글이었습니다.