Microsoft 365, Excel 2021 버전부터 기존의 배열 수식(레거시 배열수식이라고 함)과는 다른 동적 배열 수식을 사용할 수 있습니다.
동적 배열 수식을 사용하면 기존의 복잡한 수식을 단순하게 만들 수 있고, 절대참조, 혼합참조를 사용하지 않아도 되는 등 장점이 많습니다.
몇 가지 예를 통해 동적 배열 수식의 강력한 기능과 장점을 알아보겠습니다.
※ 동적 배열 수식의 전반적인 소개는 다음 글을 참고하세요.
[ 엑셀의 새로운 기능, 동적 배열 수식 ]
1. 판매이력 자료에서 계산
기존 방식으로 판매금액 계산
다음과 같이 판매이력 Data가 있을 때 동적 배열 수식을 사용하지 않고 판매금액(단가 * 수량)을 구하려면 [E5] 셀부터 [E14] 셀까지 수식을 각각 입력해야 합니다.
=C5*D5
=C6*D6
=C7*D7
...
물론 [E5] 셀에 입력하고 자동채우기 하면 간단히 해결되고 큰 문제가 없겠지만, 각각의 셀에 수식이 입력되어 있으므로 나중에 중간에 있는 수식이 수정되거나 삭제될 위험이 있습니다. 수정되거나 삭제되어도 주의해서 확인하지 않으면 알기가 어렵다는 문제점도 있습니다.
동적 배열 수식으로 판매금액 계산
이번에는 위와 동일한 결과를 구하기 동적배열 수식으로 바꿔서 입력해 보겠습니다.
[F5] 셀에 다음과 같이 여러 셀을 참조하여 판매수량과 단가를 곱하는 방식으로 수식을 입력합니다.=C5:C14*D5:D14
[F5] 셀에만 수식을 입력했는데 결과가 [F5] 셀부터 [F14] 셀까지 한 번에 구해졌습니다.
동적 배열 수식은 수식을 한 번만 입력해도 여러 셀에 결과가 반환되며 여러 가지 장점이 있습니다.
- 수식을 상대참조 방식으로 한번만 입력하면 되므로 $가 붙어서 알아보기 힘든 절대참조, 혼합참조를 사용하지 않아도 됩니다.
- 기존 수식은 입력 후 개별 수식을 수정, 삭제할 수 있지만 동적 배열 수식은 입력하고 나면 수식이 입력된 [F5] 셀 이외의 나머지 셀은 수정하거나 삭제가 불가능 합니다.
기존 방식으로 상품별 판매금액 합계 계산
앞에서는 비교적 단순한 수식을 동적 배열 방식으로 입력했습니다. 이런 수식뿐만 아니라 기존의 엑셀 함수에도 동적 배열 수식 기능을 적용할 수 있습니다.
실무에서 항목별 합계 금액은 보통 SUMIF 함수를 많이 사용합니다. 첫번째 셀 [C20]에 다음 수식을 입력하고 나머지 셀은 복사해서 붙여넣기 하거나 자동채우기로 채워 넣습니다.
=SUMIF($B$5:$B$14,B20,$E$5:$E$14)
이때 주의해야 할 점은 수식을 그대로 복사해서 붙여 넣으려면 상품 범위($B$5:$B$14)와 판매금액 범위($E$5:$E$14)를 절대 참조로 입력해야 참조 범위가 유지됩니다. 참조 범위에 대해서 항상 신경을 써야 하고 절대참조를 쓰지 않고 자동채우기를 하면 수식이 밀려서 잘못 계산되기도 하고 앞에서 살펴본 대로 누군가가 수식을 잘 못 수정하거나 삭제할 위험성도 있습니다.
동적 배열 수식으로 판매금액 합계 계산
이번에는 위와 동일한 결과를 구하기 위해 동적배열 수식으로 바꿔서 입력해 보겠습니다.
[C28] 셀에 다음 수식을 입력합니다.=SUMIF(B5:B14,B28:B32,E5:E14)
결과가 한번에 구해졌습니다.
위의 수식과 비교해 보면 2번째 인수 '조건(criteria)'이 차이가 있습니다.
=SUMIF($B$5:$B$14,B20,$E$5:$E$14)
기존 수식은 인수가 단일 항목(즉, 모나미 볼펜)만 참조하고 있지만
=SUMIF(B5:B14,B28:B32,E5:E14)
동적 배열 수식은 여러 항목(모나미 볼펜 부터 클립까지 5개 항목인 배열)을 참조하고 있습니다.
기존에 SUMIF 함수를 쓰던 방식과 다릅니다. 원래 SUMIF 함수는 두 번째 인수를 배열로 입력받는 함수가 아닌데 Microsoft 365 또는 Excel 2021버전 부터 2번째 인수를 배열로 입력할 수 있습니다. SUMIF 함수뿐만 아니라 엑셀의 모든 함수는 이러한 방식으로 배열을 입력받고 한번에 결과를 구해주는 것이 가능해졌습니다.
기존 방식으로 인센티브 계산
이번에는 인센티브 계산을 해 보겠습니다. 다음과 같은 인센티브 계산표가 있을 때 혼합참조를 주로 사용합니다.
[D38] 셀에는 =$C38*D$37 수식을
서로 다른 수식을 각각의 셀에 입력해야 합니다.
물론 혼합참조이므로 복사해서 붙여넣기하면 문제없이 적용됩니다. 그렇지만 위에서 살펴본 참조방식에 대해 신경을 써야 하고 나중에 누군가가 수식을 잘못 수정하거나 삭제할 가능성은 여전히 남아 있습니다.
동적배열 수식으로 인센티브 계산
이번에는 위와 동일한 결과를 구하기 동적배열 수식으로 바꿔서 입력해 보겠습니다.
다음 수식을 [D44]셀에 한 번만 입력하면 전체 셀에 결과가 반환됩니다.
=C44:C46*D43:F43
2. VLOOKUP 함수로 상품명 찾기
기존 방식으로 찾기
VLOOKUP 함수를 사용하여 상품코드로 상품명을 찾는 예입니다.
[C20] 셀에 다음 수식을 입력합니다.=VLOOKUP(B20,$B$5:$D$14,2,FALSE)
앞에서 살펴본 대로 나머지 셀에는 [C20] 셀의 수식을 복사해서 나머지 셀에 붙여 넣게 되는데, 절대참조인지 상대참조인지 확인을 해야 하고 나중에 수식이 수정되거나 삭제될 위험성이 있습니다.
동적 배열 수식으로 찾기
이번에는 위와 동일한 결과를 구하기 동적배열 수식으로 바꿔서 입력해 보겠습니다.
[C28] 셀에 다음 수식을 입력합니다.=VLOOKUP(B28:B32,B5:D14,2,FALSE)
한 번의 수식 입력으로 결과가 한 번에 구해졌습니다.
위의 수식과 비교해 보면 첫 번째 인수인 찾는 키값(lookup_value)이 차이가 있습니다.
=VLOOKUP(B20,$B$5:$D$14,2,FALSE)
기존 수식은 인수가 단일 항목(즉, 상품코드 A003)만 참조하고 있지만
=VLOOKUP(B28:B32,B5:D14,2,FALSE)
동적 배열 수식은 여러 항목(A003부터 A005까지 5개 항목인 배열)을 참조하고 있습니다.
기존에 VLOOKUP 함수를 쓰던 방식과 다릅니다. 원래 VLOOKUP 함수는 첫 번째 인수로 배열을 입력받는 함수가 아닌데 Microsoft 365 또는 Excel 2021버전 부터 첫 번째 인수를 배열로 입력할 수 있습니다. VLOOKUP 함수뿐만 아니라 엑셀의 모든 함수는 이러한 방식으로 배열을 입력받고 한 번에 결과를 구해주는 것이 가능해졌습니다.
3. 동적 배열 수식의 장점
이상으로 동적 배열 수식의 몇 가지 사용 예를 살펴보았습니다. 동적 배열 수식을 사용하면 기존의 복잡한 수식을 다음과 같이 단순하고 편리한 방식으로 입력하고 유지할 수 있습니다.
- 수식을 여러 셀에 건건이 입력할 필요 없이 한 번만 입력하면 여러 셀에 한 번에 적용된다.
- 수식을 배열 형식으로 한 번만 입력하면 되므로 배열 내의 다른 셀에 수식이 제대로 입력되었는지 확인할 필요가 없다.
- 동적 배열 수식은 수식을 입력한 셀만 수정, 삭제할 수 있으므로 배열 내의 다른 셀은 추후 수식이 수정되거나 삭제될 위험이 없다.
- 수식을 상대참조 방식으로 한 번만 입력하면 되므로 $가 붙어서 이해하기가 힘든 절대참조, 혼합참조가 대부분 필요 없다.
