엑셀 배열수식 기초 내용 정리 글입니다. 이 글에서는 엑셀 배열 수식이 무엇인지 알아보고 배열 수식 공식 및 사용법을 알아보겠습니다. 또 배열 수식을 이용해 엑셀 조건에 맞는 값 모두 가져오기 방법까지 알아보겠습니다.
엑셀 연습 파일을 다운로드 받아서 연습을 해보면 이해가 훨씬 쉽습니다.
엑셀 배열수식이란
이 글에서는 엑셀 배열수식이 무엇인지 알아보고 엑셀 배열수식을 사용하는 방법을 총 정리 해 볼 것입니다. 그 전에 엑셀 배열수식이 무엇인지 간단히 알아 보겠습니다.
우리가 함수를 사용할 때 주로 VLOOKUP, SUMIF, COUNT와 같은 함수를 이용해서 값을 구합니다. 그런데 이런 함수를 이용해서 값을 구할 때는 딱 하나의 값만 구할 수 있습니다.
그런데 구해야 할 값이 수만 개 이거나 함수만으로 값을 구할 수 없는 경우가 있습니다. 이러한 경우 배열 수식을 이용할 수 있는데요. 배열수식을 이용하면 아래 사진처럼 한 번의 수식 입력으로 여러개의 값을 한 번에 구할 수 있습니다.
배열 수식 사용하는 이유는 엑셀에 존재하는 함수 만으로 값을 구할 수 없는 경우 해당 값을 구하기 위해서 입니다.
배열 수식을 사용하면 작업 속도가 훨씬 빨라질 뿐만 아니라 기존 함수로 구할 수 없는 값까지 구할 수 있습니다. 따라서 배열함수가 어렵게 느껴지더라도 한 번쯤 반드시 집고 넘어 가는 것이 좋습니다.
엑셀 배열수식 공식 4가지
배열수식을 직접 사용해 보기 전에 배열 수식에서 사용 되는 4가지 중요한 공식을 알아 보겠습니다. 이 공식을 먼저 이해한 후에 배열 수식을 사용 한다면 훨씬 더 이해가 쉬울 것입니다.
엑셀 배열수식 공식 1.조건 입력 시 괄호 사용
첫 번째로 배열 함수 내에 조건을 입력 하는 경우 각 조건 마다 반드시 괄호를 입력을 해줘야 한다는 점입니다.
예를 들면 아래와 같이 말이죠.
▶︎=SUM(IF((B2:B10=”좋은상점”)*(C2:C10=”연필”),1))
위의 수식을 보시면 2가지 조건이 입력 되어 있는데요. 각 조건을 입력 할 때마다 괄호를 입력 하였습니다. 이런 식으로 배열수식 내에서 조건을 입력 할 때는 반드시 괄호를 입력을 해 주셔야 됩니다.
엑셀 배열수식 공식 2.대칭되는 배열만 지정 가능
배열 수식을 입력 할 때는 각각의 배열들을 모두 대칭되게 지정을 해야 합니다. 만약 아래 사진처럼 대칭 되지 않게 배열을 지정하면 값이 오류로 나오게 됩니다. 따라서 반드시 대칭되는 배열만 지정을 해 주세요.
엑셀 배열수식 공식 3.AND 는 곱하기 기호(*), OR 는 더하기 기호(+)
함수를 입력 할 때, 2가지 조건에 모두 해당 되는 조건을 입력 하고 싶은 경우 AND 함수를 사용합니다.
그런데 배열 수식에서는 2가지 조건에 모두 해당 되는 값을 구할 때 AND 함수를 입력 하지 않고 곱하기 기호(*)를 입력 합니다. 즉 배열 수식에서의 곱하기 기호가 바로 AND 함수의 역할을 하게 되는 것입니다.
그리고 2가지 조건 중 하나의 조건이라도 만족 하는 경우 해당되는 값을 모두 가져 오는 함수로 OR 함수가 있습니다. 그런데 배열 수식에서는 이 경우 OR 함수를 사용하지 않고 더하기 기호(+)를 입력 합니다.
즉, 배열수식에서 + 기호가 입력 되어 있다면 이는 2가지 조건 중 하나라도 만족 하는 경우를 모두 가져오겠다 라는 의미가 됩니다.
엑셀 배열수식 공식 4.M365 및 엑셀 2021 이후는 Enter 키
Microsoft 365 버전 또는 엑셀 2021 이후 버전을 사용하는 사용자의 경우 배열수식을 입력 할 때 수식을 입력 하고 엔터키만 누르면 바로 배열 수식이 입력이 됩니다.
하지만 2019년 이전 버전을 사용하는 사용자라면 배열 수식을 입력한 후 Ctrl + Shift + Enter 키를 반드시 눌러 줘야 합니다. 그래야 배열수식이 입력 되기 때문입니다.
2019년 이전 버전을 사용하는 경우, Ctrl + Shift + Enter 키를 누른 후 수식 입력 줄을 보면 내가 입력을 하지 않았는데도 중괄호가 입력이 되어 있는 것을 볼 수 있습니다.
이 중괄호는 배열 수식으로 입력이 되었다 라는 의미입니다. 그런데 365 버전 또는 엑셀 2021 이후 버전을 사용하는 경우에는 배열 수식을 입력하고 엔터키를 누르면 수식 입력 줄에 중괄호가 나타나지 않습니다.
이 경우 중괄호는 나타나지 않지만 배열수식은 제대로 잘 입력이 된 것입니다. 따라서 이 점에 유의하면서 배열 수식을 사용해 주세요.
엑셀 배열수식 사용법
이번에는 실제로 배열 수식을 사용하는 방법을 알아 보겠습니다.
1.2가지 조건을 모두 충족하는 값의 개수 구하기
아래 사진을 봅시다. 고객사명이 ‘좋은상점’이면서 동시에 판매 제품이 ‘연필’인 값의 총 개수를 구해보겠습니다. 그러기 위해 아래처럼 수식을 입력합니다.
▶︎=SUM((B2:B10=”좋은상점”)*(C2:C10=”연필”))
(B2:B10=”좋은상점”) 부분은 첫 번째 조건을 입력한 것입니다.
그리고 (C2:C10=”연필”) 부분은 두 번째 조건을 입력한 것입니다. 배열수식에서 조건을 입력할 때는 괄호를 입력해야 한다고 했었죠. 따라서 각 조건 부분에 괄호를 입력을 한 것입니다.
그리고 2개의 조건 사이를 곱하기 기호(*)로 입력을 했는데요. 우리는 2가지 조건을 모두 만족하는 경우(AND)의 값을 구하는 것이었습니다.
근데 배열수식에서는 AND 함수 대신 곱하기 기호(*)를 입력한다고 했습니다. 따라서 2개 조건 사이에 곱하기 기호를 입력한 것입니다.
수식을 다 입력한 후 마지막에 괄호를 반드시 2번 닫아주셔야 합니다. 그 후 엔터키를 누르면 2라는 값이 나오는데요, ‘좋은상점’ 이면서 동시에 ‘연필’에 해당되는 값이 2개 라는 의미입니다.
이번에는 다른 방법으로 위와 같은 결과값을 내는 방법을 알아보겠습니다. 이번에도 조건이 ‘좋은상점’이면서 동시에 ‘연필’인 값의 개수를 구하는 것입니다. 결과 값을 구하기 위해서 아래와 같이 수식을 입력합니다.
▶︎=SUM(IF((B2:B10=”좋은상점”)*(C2:C10=”연필”),1))
위에서는 SUM 함수만 이용해서 결과 값을 냈었는데요. 이번에는 SUM과 IF 함수를 둘 다 사용하는 방법입니다. 우선 SUM 함수를 먼저 입력합니다. 그리고 SUM 함수 내에 IF 함수를 입력합니다.
그 후 괄호를 반드시 한 번 더 입력을 해줘야 합니다. 그 후 첫 번째 조건인 ‘좋은상점’을 입력해주면 됩니다.
그리고 2가지 조건을 동시에 만족하는 값의 개수를 구할 것이므로 배열수식에서의 AND를 의미하는 기호인 곱하기 기호(*)를 입력합니다. 그리고 두 번째 조건인 연필 조건도 괄호 내에 입력해줍니다.
그리고 이번에는 IF 함수를 사용했기 때문에 반드시 콤마를 입력해줘야 합니다.
그리고 1을 입력합니다. 즉, IF 함수 내의 2가지 조건을 모두 충족하는 경우에 1로 카운트를 해라 라는 의미입니다. 이렇게 입력을 한 후 엔터키를 누르면 이번에도 2라는 값이 나옵니다.
2.2가지 조건을 모두 충족하는 값의 총 합계 구하기
위에서는 2가지 조건을 모두 충족 하는 값의 개수를 카운트 하는 것이었는데요. 이번에는 2가지 조건을 모두 충족 하는 값의 총 판매 금액을 구해보도록 하겠습니다.
조건은 고객사 명이 ‘좋은상점’이면서 동시에 판매 제품이 ‘연필’인 값입니다. 이 2가지 조건을 동시에 만족 하는 값의 총 판매 금액을 구해보겠습니다. 그러기 위해서 아래와 같이 수식을 입력합니다.
▶︎=SUM((B2:B10=”좋은상점”)*(C2:C10=”연필”)*D2:D10*E2:E10)
위에서 IF 함수를 사용했을 때는 수식 내에 콤마를 입력해야 했습니다. 즉, IF 함수를 사용하면 콤마를 입력해야 합니다.
하지만 여기서는 IF 함수를 사용하지 않고 SUM 함수만 사용했습니다. 따라서 이번에는 함수 내에 콤마 대신 곱하기 기호(*)를 입력해주세요. 그리고 조건 부분은 반드시 괄호로 묶어주시고요.
위와 같이 수식을 입력한 후 엔터키를 누릅니다. 그럼 ‘좋은상점’이면서 동시에 ‘연필’인 값의 판매단가와 판매개수를 곱한 총 판매금액의 합이 나옵니다.
그런데 이 계산 과정이 생략되어 있어서 어렵게 느껴질 수 있습니다. 따라서 세부적인 계산 과정을 알아보겠습니다.
우리가 지정한 조건은 ‘좋은상점’이면서 동시에 ‘연필’인 값이었습니다. 우리가 지정한 조건에 해당되는 셀의 경우 아래 사진처럼 TRUE라는 값으로 바뀌게 됩니다. 아래 사진의 경우 우리 눈에 보이지 않았던 중간 계산과정이죠.
위 사진을 보시면 조건에 해당되는 셀에는 TRUE라고 표시가 되었습니다. 반면 조건에 해당 되지 않는 셀의 경우 FALSE라고 입력이 됩니다. 그리고 위 예시의 경우 2가지 조건을 동시에 만족 하는 값의 개수는 2개입니다.
그 다음 계산 과정을 확인해 보겠습니다. 엑셀은 TRUE 값을 1로 바꿉니다. 그리고 FALSE 값은 0으로 바꿉니다. 따라서 아래 사진처럼 TRUE 및 FALSE 값이 0과 1이라는 값으로 바뀌어 데이터에 입력이 됩니다.
그리고 위에서 1과 0 숫자로 바뀐 각 값과 판매단가, 판매개수를 각각 곱합니다. 그럼 아래와 같이 계산식이 입력이 됩니다.
- 2행: 1 × 1 × 2000 × 10= 20000
- 3행: 0 × 0 × 1000 × 9= 0
- 4행: 0 × 0 × 3000 × 7= 0
- 5행: 1 × 1 × 2000 × 8= 16000
- 6행: 0 × 0× 1000 × 8= 0
- 7행: 1 × 0 × 5000 × 0= 0
- 8행: 0 × 1 × 2000 × 7= 0
- 9행: 1 × 0 × 6000 × 11= 0
- 10행: 0 × 0 × 9000 × 6= 0
여기서 각 결과 값들을 다 더하면 아래와 같은 결과가 나옵니다.
- 20000 + 0 + 0 + 16000 + 0 + 0 + 0 + 0 + 0= 36000
따라서 결론적으로 36000이라는 값이 나오게 되는 것입니다.
3.조건에 맞는 값 모두 가져오기 방법
이번에는 배열 수식을 이용해서 조건에 맞는 값을 모두 가져오는 방법을 알보겠습니다.
아래 사진의 왼쪽 데이터를 토대로 오른쪽 칸에 각 조건에 해당되는 값의 판매단가 합계를 구해보겠습니다.
우리는 첫 번째 셀(F5)에만 수식을 입력하면 됩니다. 입력한 후 자동채우기를 하면 되기 때문이죠. F5 셀에서는 좋은상점이면서 동시에 연필인 값의 판매단가 합계를 구할 것입니다.
따라서 아래와 같이 수식을 입력 해야 합니다.
▶︎=SUM(IF(($B2:$B$10=$E5)*($C$2:$C$10=F$4),$D$2:$D$10))
어떻게 이런 수식이 나오게 되었는지 그 자세한 과정을 살펴 볼게요.
우선 첫 번째 조건은 고객사명이 좋은상점인 값이었습니다. 따라서 SUM을 입력한 후 IF를 입력하고 괄호를 입력한 후 고객사명 전체 셀을 선택합니다. 여기서 IF 함수를 입력한 후 괄호가 2개가 입력되어 있어야 합니다.
고객사명이 좋은상점인 값을 찾을 것이므로 =을 입력한 후 좋은상점이라고 되어 있는 E5셀을 선택합니다. 여기서 중요한 것이 한 가지 있는데요. 바로 셀을 고정시켜 줘야 한다는 것입니다.
우리는 첫 번째 셀에만 값을 구하고 나머지 셀에는 자동채우기를 할 것입니다. 따라서 셀 위치가 이동할 때 처음에 지정 했던 범위는 바뀌면 안됩니다. 따라서 선택한 범위 부분에서 F4 키를 한 번 눌러서 셀을 고정시켜 주세요.
그럼 $ 기호가 생기면서 절대참조가 적용됩니다. 이제 E5셀에도 절대참조를 해줘야 하는데요. 여기서 E5 셀의 경우 행은 움직여야 하지만 열은 움직이면 안되며, 딱 고정이 되어 있어야 합니다. 따라서 E열에만 절대참조를 해줘야 합니다.
▶︎=SUM(IF(($B2:$B$10=$E5)*($C$2:$C$10=F$4),$D$2:$D$10))
그러기 위해서 커서를 E5 셀 부분에 두고 F4 키를 3번 누릅니다. 그럼 ‘&E5’ 로 바뀌게 되는데요. ‘&E5’의 의미는 E열에만 절대참조가 적용되어 E열만 고정이 되었다는 의미입니다.
다음으로 곱하기 기호를 입력하고 괄호를 엽니다. 그리고 두 번째 조건은 판매제품이 연필인 값이었습니다. 따라서 판매제품에 해당되는 전체 셀을 선택합니다.
그리고 =을 입력하고 연필 텍스트가 나와 있는 F4셀을 클릭합니다. 이번에도 F4키를 눌러서 셀을 고정시킵니다.
▶︎=SUM(IF(($B2:$B$10=$E5)*($C$2:$C$10=F$4),$D$2:$D$10))
그리고 F4 셀에도 절대참조를 적용해야 하는데요. 이번에는 열은 움직여야 하는 반면 행은 딱 고정이 되어 있어야 합니다.
따라서 F4 셀에 커서를 두고 F4 키를 2번 누릅니다. 그럼 F$4로 모양이 바뀌게 되는데요. 이는 4행만 딱 고정이 되었다는 의미입니다.
그 후 괄호를 닫습니다. 이번에는 IF 함수가 포함되어 있으므로 콤마를 입력 합니다. 그리고 판매단가 전체 셀을 선택합니다. 이번에도 F4 키를 눌러서 셀을 고정합니다. 그리고 마지막으로 괄호를 2번 닫은 후 엔터키를 누릅니다.
그럼 값이 나오게 됩니다. 이후 아래 셀에 자동채우기를 합니다. 그리고 오른쪽 셀에도 자동채우기를 합니다. 그럼 한 번의 배열수식 입력으로 전체 데이터를 구할 수 있게 됩니다.
배열 수식 안될 때
배열수식을 입력한 후 엔터키 또는 Ctrl + Shift + 엔터키 를 눌렀는데 수식이 입력이 안 되는 경우가 있습니다. 이러한 경우 한컴 입력기로 선택이 되어 있는건 아닌지 확인을 해봐야 합니다.
컴퓨터 우측 하단 부분의 날짜 왼쪽 부분을 보시면 ‘한’이라고 나와 있는 아이콘이 있습니다. 이 글자를 클릭하면 아래 사진과 같은 작은 창이 나옵니다.
여기서 ‘Microsoft 입력기’에 체크가 되어 있어야 합니다. 그런데 간혹 ‘한컴 입력기’에 체크가 되어 있는 경우가 있습니다. ‘한컴 입력기’에 체크가 되어 있는 경우 배열수식이 입력이 안됩니다.
따라서 ‘Microsoft 입력기’로 바꿔주세요. 그 후 배열수식 입력 후 엔터키를 누르면 결과 값이 정상적으로 나옵니다.
<결론>
▶︎엑셀 배열수식 기초
1.배열수식을 이용하면 여러 개의 값을 한 번에 구할 수 있음.
2.배열수식에서 조건 입력 시 괄호 입력하기
3.배열수식에서 2가지 조건 모두 충족하는 경우 * 입력, 2가지 조건 중 하나만 충족하면 되는 경우 + 입력
4.배열 수식 안될때 마이크로소프트 입력기로 지정되어 있는지 확인
이상 엑셀 배열수식 기초 내용 총정리에 관한 글이었습니다.