배열함수와 배열수식 기초 및 사용법 총정리
배열수식은 범위를 값의 묶음으로 다뤄 항목마다 함수를 적용합니다. 예전 버전에서는 Ctrl + Shift + Enter 로 입력해야 합니다.
같은 계산을 하는데 어떤 수식은 중간 계산 열이 필요하고 어떤 수식은 한 줄로 끝납니다. 그 차이를 만드는 것이 배열수식입니다. 범위를 '위치'가 아니라 '값의 묶음'으로 다루는 것이 핵심입니다.
셀 · 범위 · 배열
| 용어 | 무엇인가 |
|---|---|
| 셀 | 행과 열이 만나는 한 지점 |
| 범위 | 시트 위에 보이는 셀의 묶음 — 어디에 있는지 |
| 배열 | 그 안에 든 값의 묶음 — 무엇이 들어 있는지 |
일반 함수는 인수로 범위를 받습니다. 그래서 범위 안 항목 하나하나를 따로 계산하지 못하고, 필요하면 중간 계산 열을 만들어야 합니다. 배열수식은 인수로 배열을 넘겨 항목마다 함수를 적용합니다.
배열 표기법
배열은 중괄호로 감싸고, 쉼표는 열, 세미콜론은 행을 구분합니다.
| A | B | C | |
|---|---|---|---|
| 1 | 가 | 나 | 다 |
| 2 | 라 | 마 | 바 |
| 3 | 사 | 아 | 자 |
범위 → A1:C3
배열 → {가,나,다; 라,마,바; 사,아,자}
배열수식 입력하기
예전 버전에서는 일반 수식과 달리 Ctrl + Shift + Enter 로 확정해야 합니다. 그러면 수식이 중괄호로 감싸지며 배열수식이 됩니다. 이 조합 때문에 CSE 수식이라고도 부릅니다.
차이를 직접 보겠습니다. A열과 B열에 아래 값이 있다고 하겠습니다.
| A | B | |
|---|---|---|
| 1 | 가 | 가 |
| 2 | 나 | 다 |
| 3 | 다 | 다 |
같은 수식을 Enter 로 넣으면 —
=SUM(IF(A1:A3<>B1:B3,1,0)) → 0 '// 범위가 배열로 펼쳐지지 않아, 각 범위의 첫 셀인 '// A1 과 B1 만 IF 의 인수로 들어갑니다. "가" = "가" 이므로 0.
Ctrl + Shift + Enter 로 넣으면 —
{=SUM(IF(A1:A3<>B1:B3,1,0))}
=SUM({IF(가<>가,1,0), IF(나<>다,1,0), IF(다<>다,1,0)})
=SUM({0,1,0})
→ 1
범위가 배열로 펼쳐져 세 쌍을 각각 비교하고, 그 결과 배열을 SUM 이 더합니다.
실전 예제 — 월별 매출 합계
날짜별 매출 표에서 4월 매출만 더해 보겠습니다.
방법 1. SUMIF (중간 계산 필요)
SUMIF 의 첫 인수는 범위만 받습니다. 그래서 '월' 값을 먼저 어딘가에 뽑아 두어야 합니다.
'// 1단계 — 보조 열에 MONTH 로 월을 뽑습니다 (D열) =MONTH(B7) '// 2단계 — 그 열을 기준으로 합계 =SUMIF(D7:D17, 4, C7:C17)
=SUMIF(MONTH(B7:B17), 4, C7:C17) 처럼 쓰면 오류가 납니다. SUMIF 의 첫 인수는 시트 위의 범위여야 하는데, MONTH(B7:B17) 은 계산된 배열이기 때문입니다.방법 2. SUMPRODUCT (배열수식)
SUMPRODUCT 는 배열을 그대로 받으므로 보조 열 없이 한 줄로 끝납니다.
=SUMPRODUCT(--(MONTH(F7:F17)=4)*G7:G17)
계산 순서를 따라가면 이렇습니다.
MONTH(F7:F17) → {1,1,3,4,4,4,5,5,5,6,6}
= 4 비교 → {FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,FALSE,…}
-- 로 숫자화 → {0,0,0,1,1,1,0,…}
매출액과 곱셈 → {0,0,0,47000,67000,68000,0,…}
SUMPRODUCT 합계 → 182,000
조건에 맞지 않는 행은 0 이 되어 곱셈에서 사라지고, 남은 값만 더해집니다.
장점과 단점
| 장점 | 단점 |
|---|---|
| 중간 계산 열 없이 한 수식으로 끝남 | 작성자 외에는 읽기 어려움 |
| 시트가 간결해짐 | A:A 같은 열 전체 참조를 쓸 수 없음 |
| 일반 함수로 못 하던 계산이 가능 | 넓은 범위에 여러 배열이 겹치면 오히려 느려질 수 있음 |
참고 문서
- 엑셀 배열이란? 배열과 범위의 차이 — 오빠두엑셀
- SUMPRODUCT 함수 사용법 — 오빠두엑셀
엑셀 배열이란? 배열과 범위의 차이
범위는 시트 위의 셀 묶음이고 배열은 값 자체의 묶음입니다. 배열은 항목마다 함수를 적용할 수 있어 중간 계산 없이 한 수식으로 끝낼 수 있습니다.
엑셀에서 자꾸 뜨는 OneDrive 알림, 완전히 끄는 방법
OneDrive 백업을 꺼도 알림이 남아 있다면, Office가 따로 띄우는 메시지 표시줄을 레지스트리로 꺼야 합니다.
GPT-4와 GPT-4o의 OCR 성능 비교 : 테스트 결과 공개
GPT-4o는 GPT-4 Turbo 대비 OCR 인식 정확도가 84%에서 98%로 오르고 처리 속도는 2배 이상 빨라졌으며, 비용은 약 60% 저렴해졌습니다.
단축키 실행이 안되신다면 다른 외부프로그램과의 충돌일 가능성이 높습니다.
다른 프로그램에서 Ctrl + Shift 관련 단축키를 사용중인지 확인해보시겠어요?
감사합니다!^^*
혹시 컴퓨터에 한글이 설치되어 있다면 자판입력기가 '한컴입력기'는 아닌지 확인해보세요.
'한컴입력기'는 <ctrl>+<shift>+<enter>를 눌러도 반응하지 않지만,
'Microsoft 입력기'는 정상작동되었습니다.
알려주셔서 감사드립니다!
친절한 설명 감사드립니다 .. 근데 무지 유용합니다 ㅎㅎ
수식 {=SUMPRODUCT(--(MONTH(F7:F17)=4)*(G7:G17)} 알려주셨는데
호기심에 --를 안 넣고, 컨트롤+쉬프트+엔터도 안 해봤는데 정상적인 값이 계산됩니다.
=SUMPRODUCT((MONTH(F7:F17)=4)*G7:G17) 이렇게요
사용하는 엑셀은 2013입니다.
혹시 왜 그런 것일까요?
헛 제가 수식 설명을 잘못 적어드렷습니다.^^; 잘못된 부분 확인해주셔서 감사드립니다.
네 말씀하신 게 맞습니다. SUMPRODUCT 함수는 배열로 입력하지 않아도, 함수 인수로 배열을 받을 수 있기 때문에 그냥 Enter로만 입력하셔도 잘 동작합니다.
=SUM(IF(A1:A3<>B1:B3,1,0))
'// 결과값으로 0이 출력됩니다.
엑셀 2021/M365 버전을 사용중이실 경우, ENTER로만 입력해도 자동으로 배열수식(=동적배열수식)으로 입력되어 1이 반환됩니다.^^
사용중인 버전을 확인해보시겠어요? 감사합니다.
1. "배열수식"이란 배열을 인수로 받아들이는 함수를 포함한 수식을 말하는 것이지요? 배열 자체는 아니지요?
2. Ctrl-Shift-Enter 치면, 엑셀이 기존 수식을 {=SUM(...)} 이렇게 표시해줍니다.
이것을 "배열수식으로 입력"이라고 하셨는데요,
범위 ( A1:B10 ) 인수가 있을 때, 이것을 내부적으로 배열로 입력해 준다는 뜻인가요?
우리가 배열수식을 만들 때도, 우리가 입력할 수 있는 것은 "범위" 뿐이죠?
"배열"을 직접 타이핑해서, 함수 인수로 넣을 수는 없죠?
3. "SUMIF 함수의 첫번째 인수는 '참조범위', [범위]형태로만 입력가능하므로 "고 설명해 주셨는데요,
배열수식을 사용할 수 있는 함수와 SUMIF처럼 사용할 수 없는 함수를 구분할 수 있는 자료뿐이가요?
SUMIF(범위,..), SUM(범위), SUMPRODUCT(범위) 모두, 사용할 때 인수의 모양은 동일합니다.
1. 네 맞습니다.^^ 수식을 말합니다.
2. 배열을 직접 타이핑할 수 있지만, 함수안에 이중으로 사용되는 배열일 경우에는 직접 타이핑할 수 없습니다. 이중 배열을 직접 타이핑 할 경우 #CALC! 오류가 발생합니다.
3. SUMIF 함수는 Range로 나오며, Sumproduct는 Array로 표시됩니다.^^
엑셀에 함수를 입력 후, 인수 설명 한번 확인해보세요 (오빠두엑셀 홈페이지에서 인수 설명은 이해를 쉽게 돕기 위해 모두 범위로 작성하였습니다.)
제시해드린 답변이 문제 해결에 도움이 되셨길 바랍니다. 감사합니다.