배열 수식
쉽게 말하면
배열 수식은 셀 하나하나가 아니라 범위 전체를 한꺼번에 계산하는 수식이에요. 예를 들어 '부서가 영업이면서 직위가 과장인 사람 수'를 함수 하나 없이 범위끼리 곱해서 구할 수 있어요. 입력할 때 Enter 대신 Ctrl+Shift+Enter를 누르면 수식 양쪽에 중괄호 { }가 생기는데, 이게 배열 수식이라는 표시예요.
시험용 정의
배열 수식은 범위(배열) 전체를 한 번에 계산하는 수식으로, 수식을 입력한 뒤 Ctrl+Shift+Enter를 누르면 앞뒤에 중괄호 { }가 붙는다(중괄호를 직접 쳐서는 안 된다). 결과가 여러 개면 결과 범위를 먼저 선택하고 입력하며, 배열 수식이 들어 있는 범위는 일부 셀만 고치거나 지울 수 없다. 배열 상수는 수식 안에 { }로 직접 쓴 값 묶음으로 쉼표(,)는 열, 세미콜론(;)은 행을 나눈다({1,2;3,4}는 2행 2열). 조건이 있는 계산은 (조건 범위=조건)이 TRUE/FALSE 배열이 되는 것을 이용한다. 개수는 SUM((조건)*1)·SUM(IF(조건,1))·COUNT(IF(조건,1)), 합계는 SUM((조건1)*(조건2)*합계 범위)·SUM(IF(조건,합계 범위))로 구한다. FREQUENCY(데이터, 구간)는 구간별 빈도를 배열로 돌려주고, INDEX와 MATCH를 함께 쓰면 두 조건으로 값을 찾을 수 있다(MATCH(값1&값2, 범위1&범위2, 0)).
비유로 이해하기
배열 수식은 반 전체 시험지를 한 번에 채점하는 기계다. 한 장씩 채점(셀마다 수식)하지 않고 묶음째 넣으면, 조건에 맞는 답안지만 골라 점수를 더해 준다.
자세히
먼저 알아 둘 말
| 말 | 뜻 |
|---|---|
| 배열 | 같은 모양으로 늘어선 값의 묶음. 범위 A1:A5도 배열이다 |
| 배열 수식 | 배열끼리 한꺼번에 계산하는 수식. Ctrl+Shift+Enter로 입력 |
| 배열 상수 | 수식 안에 직접 쓴 값 묶음. {10,20,30} |
| TRUE·FALSE 계산 | 곱셈·덧셈에 쓰면 TRUE는 1, FALSE는 0으로 바뀐다 |
배열 상수의 모양
| 쓴 모양 | 뜻 |
|---|---|
| {1,2,3} | 1행 3열 (쉼표 = 다음 열) |
| {1;2;3} | 3행 1열 (세미콜론 = 다음 행) |
| {1,2;3,4} | 2행 2열 |
| A1:A3*{30;20;10} | 세로 범위에는 세로 배열(;)을 곱해야 각 행끼리 짝이 맞는다 |
조건 계산 공식
| 구하려는 것 | 배열 수식 | 같은 결과의 일반 함수 |
|---|---|---|
| 조건에 맞는 개수 | {=SUM((C2:C9="영업")*1)} 또는 {=SUM(IF(C2:C9="영업",1))} | =COUNTIF(C2:C9,"영업") |
| 두 조건 개수 | {=SUM((C2:C9="영업")*(D2:D9="과장"))} | =COUNTIFS(…) |
| 두 조건 합계 | {=SUM((C2:C9="영업")*(D2:D9="과장")*E2:E9)} | =SUMIFS(E2:E9,C2:C9,"영업",D2:D9,"과장") |
| 조건 평균 | {=AVERAGE(IF(C2:C9="영업",E2:E9))} | =AVERAGEIF(…) |
| 구간별 빈도 | {=FREQUENCY(B2:B9,E2:E5)} (결과 범위를 먼저 선택) | =COUNTIFS로 구간마다 |
| 두 조건으로 찾기 | {=INDEX(값 범위, MATCH(조건1&조건2, 범위1&범위2, 0))} | 없음 |
헷갈리기 쉬운 것
- COUNT는 숫자의 개수다. (조건)*(조건)의 결과 배열에는 0도 숫자로 들어 있어 COUNT로 세면 전체 개수가 나온다. 조건 개수는 SUM이나 COUNT(IF(조건,1))로 센다
- 여러 셀에 복사할 수식에서는 조건 범위는 절대 참조, 기준 셀은 복사 방향에 맞게 혼합 참조($A17, B$16)로 쓴다
- DSUM 같은 데이터베이스 함수는 조건 범위를 따로 쓰므로 배열 조건식을 넣을 수 없다
- SUMPRODUCT(B2:B5,C2:C5)는 배열 수식 없이 같은 위치끼리 곱해 더한다
- 동점자 처리 순위: RANK(총점) + SUM((총점=내 총점)*(국어>내 국어))처럼 같은 점수 중 나보다 높은 사람 수를 더한다
시험에 이렇게 나와요
- 입력 → Ctrl+Shift+Enter, 중괄호는 자동으로 생김. 여러 결과는 범위를 먼저 선택
- 배열 상수 → 쉼표는 열, 세미콜론은 행. 세로 범위에는 {;;} 모양을 곱함
- 조건 개수 → SUM((조건)*1), SUM(IF(조건,1)), COUNT(IF(조건,1)). COUNT((조건)*(조건))은 틀림
- 조건 합계 → SUM((조건1)*(조건2)*값), 기준 셀 혼합 참조
- FREQUENCY(데이터, 구간) 순서, 결과 범위를 먼저 선택
- INDEX·MATCH 두 조건 → MATCH(값1&값2, 범위1&범위2, 0)
확인 문제
Q1. [A1:A3]에 1, 2, 3이 있을 때 {=SUM(A1:A3*10)}의 결과는?
- 30
- 60
- 10
- #VALUE!
정답 보기
2번 60
배열 수식은 범위의 값마다 계산한다. 1×10, 2×10, 3×10을 더해 60이다.
Q2. 배열 수식을 입력할 때 누르는 키는?
- Alt+Enter
- Ctrl+Shift+Enter
- Shift+Enter
- Ctrl+Enter
정답 보기
2번 Ctrl+Shift+Enter
Ctrl+Shift+Enter를 누르면 중괄호가 자동으로 붙는다. Alt+Enter는 셀 안 줄 바꿈, Ctrl+Enter는 선택 범위 같은 값 입력이다.
Q3. 배열 상수 {5,6;7,8}의 모양은?
- 1행 4열
- 4행 1열
- 2행 4열
- 2행 2열
정답 보기
4번 2행 2열
쉼표는 열, 세미콜론은 행을 나눈다. 5,6 / 7,8 두 행이다.
Q4. [B2:B9]가 '서울'이고 [C2:C9]가 '여'인 사람의 [D2:D9] 합계를 구하는 배열 수식으로 옳은 것은?
- {=DSUM((B2:B9="서울")*D2:D9)}
- {=COUNT((B2:B9="서울")*(C2:C9="여"))}
- {=SUM((B2:B9="서울")*(C2:C9="여")*D2:D9)}
- {=SUM(B2:B9="서울",C2:C9="여",D2:D9)}
정답 보기
3번 {=SUM((B2:B9="서울")*(C2:C9="여")*D2:D9)}
두 조건 배열과 값 범위를 곱해 SUM으로 더한다. COUNT는 0도 세고, DSUM은 조건 범위를 따로 써야 한다.
Q5. 점수 [B3:B12]를 구간 [E3:E6]별로 세어 [F3:F6]에 한 번에 표시하는 배열 수식은?
- {=PERCENTILE(B3:B12,E3:E6)}
- {=COUNTIF(E3:E6,B3:B12)}
- {=FREQUENCY(E3:E6,B3:B12)}
- {=FREQUENCY(B3:B12,E3:E6)}
정답 보기
4번 {=FREQUENCY(B3:B12,E3:E6)}
FREQUENCY(데이터 배열, 구간 배열) 순서다. 결과 범위 F3:F6을 먼저 선택하고 입력한다.
Q6. [A1:A3]에 1, 2, 3이 있을 때 {=SUM(A1:A3*{2;3;4})}의 결과는?
- #VALUE!
- 24
- 9
- 20
정답 보기
4번 20
1×2 + 2×3 + 3×4 = 20. 세로 범위와 세로 배열 상수의 같은 위치끼리 곱한다.
헷갈리기 쉬운 O·X
배열 수식의 중괄호 { }는 키보드로 직접 입력해야 한다.
정답 보기
X 직접 치면 문자열이 된다. Ctrl+Shift+Enter를 누르면 자동으로 붙는다.
배열 수식이 입력된 범위에서 셀 하나만 골라 지울 수 있다.
정답 보기
X 배열의 일부는 바꿀 수 없다. 범위 전체를 선택해 고치거나 지운다.
{=COUNT(IF(조건,1))}은 조건에 맞는 개수를 바르게 센다.
정답 보기
O 조건이 거짓이면 IF가 FALSE를 돌려주고 COUNT는 숫자 1만 센다.