엑셀 MAX IF 배열 수식으로 가장 큰 양수·음수 찾는 방법

데이터 전체에서 최댓값을 구하는 것만으로는 부족할 때가 있습니다. 예를 들어 전체 데이터 중 양수만 또는 음수만 대상으로 가장 큰 값을 찾아야 하는 경우입니다.

데이터 양이 적다면 MAX 함수의 범위를 직접 선택해 손쉽게 해결할 수 있습니다. 하지만 데이터가 많거나 정렬되어 있지 않은 경우에는 조건에 맞는 범위를 정확히 선택하는 것이 어렵거나 사실상 불가능할 수 있습니다.

이럴 때 IF 함수와 MAX 함수를 배열 수식(array formula)으로 결합하면 '양수만', '음수만' 같은 조건을 쉽게 설정하고, 해당 조건에 맞는 데이터만 계산 대상으로 삼을 수 있습니다.

MAX IF 배열 수식 구조 살펴보기

이 튜토리얼에서 가장 큰 양수를 찾기 위해 사용하는 수식은 다음과 같습니다.

=MAX( IF( A1:B5>0, A1:B5 ) )

IF 함수의 value_if_false 인수는 선택 사항이므로 수식을 간결하게 하기 위해 생략했습니다. 만약 선택한 범위의 데이터가 설정한 조건(0보다 큰 숫자)을 충족하지 않으면 수식은 0을 반환합니다.

수식의 각 부분이 하는 역할은 다음과 같습니다.

IF 함수: 데이터를 필터링하여 선택한 조건(예: 0보다 큰 숫자)에 맞는 값만 MAX 함수로 전달합니다.
MAX 함수: 필터링된 데이터 중 가장 높은 값을 찾습니다.
배열 수식: 수식을 감싸는 중괄호 { }로 표시되며, IF 함수의 논리 검사 인수가 단일 셀이 아닌 전체 데이터 범위에서 일치하는 값을 검색할 수 있도록 합니다.

배열 수식은 수식을 입력한 후 키보드에서 Ctrl, Shift, Enter 키를 동시에 눌러 생성합니다.

그 결과 등호를 포함한 수식 전체가 중괄호로 감싸지며, 그 형태는 다음과 같습니다.

{ =MAX( IF( A1:B5>0, A1:B5 ) ) }

배열 수식을 만들 때 누르는 키 조합(Ctrl+Shift+Enter) 때문에 이런 수식을 흔히 CSE 수식이라고 부릅니다.

참고로 Excel 2019 이상 또는 Microsoft 365를 사용한다면 별도의 배열 수식 없이 MAXIFS 함수(예: =MAXIFS(A1:B5, A1:B5, ">0"))로 동일한 결과를 더 간단하게 얻을 수 있습니다.

아래 예제에서는 MAX IF 배열 수식을 사용해 숫자 범위에서 가장 큰 양수와 음수를 각각 찾아봅니다.

먼저 가장 큰 양수를 찾는 수식을 만든 뒤, 이어서 가장 큰 음수를 찾는 단계를 진행합니다.

중첩 수식과 배열 수식을 동시에 만드는 작업이므로, 수식 전체를 한 개의 워크시트 셀에 입력해야 합니다.

수식을 입력한 후에는 절대 Enter 키를 누르거나 마우스로 다른 셀을 클릭하지 마세요. 수식을 배열 수식으로 변환해야 하기 때문입니다.

=MAX( IF( A1:B5>0, A1:B5 ) )

B6 셀을 클릭하면 워크시트 위쪽의 수식 입력줄에서 완성된 배열 수식 전체를 확인할 수 있습니다.

{ =MAX( IF( A1:B5>0, A1:B5 ) ) }

가장 큰 음수를 찾는 수식은 첫 번째 수식과 비교 연산자 하나만 다릅니다.

이번 목표는 가장 큰 음수를 찾는 것이므로, 두 번째 수식은 '0보다 큼'(>) 대신 '0보다 작음'(<) 연산자를 사용해 0보다 작은 데이터만 검사합니다.

=MAX( IF( A1:B5<0, A1:B5 ) )

B6과 B7 셀에 기대했던 답 대신 #VALUE! 오류가 표시된다면, 대부분 배열 수식이 올바르게 생성되지 않았기 때문입니다.

이 문제를 해결하려면 수식 입력줄에서 해당 수식을 클릭한 후, 키보드의 Ctrl, Shift, Enter 키를 다시 한 번 동시에 누르세요. 그러면 수식이 제대로 된 배열 수식으로 변환되어 정상적인 결과가 표시됩니다.