엑셀 배열 수식

Excel Array Formula

여러 값을 한 덩어리로 놓고 한 번에 계산하는 수식. 입력을 마칠 때 Ctrl+Shift+Enter 를 누르면 수식이 중괄호 { } 로 감싸여 표시된다. 중괄호를 직접 입력하는 것이 아니다.

쓰는 이유

조건이 여러 개일 때 보조 열을 만들지 않고 한 수식으로 끝낼 수 있다.
여러 열을 & 로 이어 붙여 복합 키로 찾기를 할 수 있다.

{=INDEX($C$10:$C$13, MATCH(MONTH(A2)&B2, $B$10:$B$13&$A$10:$A$13, 0))*C2}

MATCH 의 찾을 값과 찾을 범위가 같은 순서로 이어져야 한다. 위 식은 `월&제품명` 으로 찾으므로 범위도 `월범위&제품명범위` 순서여야 한다.

조건을 곱해 더하기

비교식 ($A$2:$A$13=$A17) 은 행마다 TRUE·FALSE 가 든 배열을 만든다. 배열끼리 곱하면 TRUE 는 1, FALSE 는 0 으로 바뀌므로 조건 배열을 곱하면 모든 조건이 참인 행만 1 이 남고(AND), 더하면 하나라도 참인 행이 1 이상이 된다(OR).

구하는 것	배열 수식	같은 일을 하는 함수
조건에 맞는 값의 합	`{=SUM((조건1)(조건2)합계범위)}`	SUMIFS
조건에 맞는 개수	`{=SUM((조건1)*(조건2))}`	COUNTIFS
조건에 맞는 값의 평균	`{=AVERAGE(IF((조건1)*(조건2), 범위))}`	AVERAGEIFS
조건에 맞는 값의 최댓값	`{=MAX(IF(조건, 범위))}`	MAXIFS

SUMPRODUCT((조건1)*(조건2)*합계범위) 는 Ctrl+Shift+Enter 없이 같은 값을 낸다 → 엑셀 수학 함수
행 머리글과 열 머리글이 만나는 표를 이 수식 하나로 복사해 채울 때는 자료 범위는 절대 참조, 조건 셀은 혼합 참조($A17·B$16)로 쓴다. 자세한 이유는 엑셀 셀 참조

{=SUM(($A$2:$A$13=$A17) * ($B$2:$B$13=B$16) * $E$2:$E$13)}

부서명([A17])과 직위([B16])가 모두 같은 행의 종합점수만 더한다.

동점자 순위

총점으로 순위를 매기되 총점이 같으면 국어 점수가 높은 사람을 앞 순위로 하려면

{=RANK($F2,$F$2:$F$10) + SUM(($F$2:$F$10=$F2) * ($B$2:$B$10>$B2))}

RANK 로 총점 순위를 구하고, 총점이 자기와 같으면서 국어 점수가 자기보다 높은 사람 수를 더한다
총점 180 인 세 사람의 국어가 90·80·70 이면 RANK 는 모두 2 다. 국어가 더 높은 사람 수 0·1·2 를 더해 2·3·4 위가 된다
총점 순위와 국어 순위를 더하거나 곱하는 식은 동점을 가르지 못한다. 순위 값이 터무니없이 커지거나 다른 사람과 같은 순위가 생긴다
비교 범위는 절대 참조, 자기 셀($F2·$B2)은 행이 바뀌도록 써서 아래로 채운다 → 엑셀 통계 함수

배열 상수

중괄호 안에 값을 직접 나열한 것이다.

가로는 쉼표, 세로는 세미콜론으로 구분한다. {1,2,3} 은 한 행, {1;2;3} 은 한 열이다.
숫자와 텍스트뿐 아니라 TRUE·FALSE 같은 논리값과

 #N/A 같은 오류 값도 넣을 수 있다.

셀 참조나 수식은 넣을 수 없다.

결과가 여러 셀인 경우

결과가 들어갈 범위를 먼저 선택한 뒤 수식을 쓰고 Ctrl+Shift+Enter 를 누른다.
배열 수식이 들어간 범위의 일부만 고치거나 지울 수 없다. 범위 전체를 함께 다뤄야 한다.

예: 수량 × 단가를 한 번에

[B2:B5] 에 수량, [C2:C5] 에 단가가 있을 때 [D2:D5] 를 먼저 선택하고 다음을 입력한 뒤 Ctrl+Shift+Enter 를 누르면 네 행의 금액이 한 번에 채워진다.

{=B2:B5*C2:C5}

같은 크기의 두 범위를 곱하면 같은 위치의 값끼리 곱한 배열(B2*C2, B3*C3, B4*C4, B5*C5)이 나오고, 선택한 범위의 각 셀에 차례로 들어간다
{=B2*C2} 는 셀 하나끼리의 곱이라 선택한 네 셀 모두에 같은 값(첫 행의 금액)이 들어간다
{=B2*C2:B5*C5} 는 범위 연산자(:)가 곱셈과 섞여 범위를 제대로 만들지 못하는 잘못된 식이다
=SUMPRODUCT(B2:B5,C2:C5) 는 같은 위치끼리 곱한 값을 모두 더한 합계 하나를 돌려준다. 행마다 금액을 구하는 식이 아니라 총액을 구하는 식이다 → 엑셀 수학 함수

같이 보기