Max If(조건이 있는 최대 값) – Excel 및 Google 스프레드시트

이 튜토리얼에서는 Excel 및 Google 스프레드시트에서 특정 조건을 충족하는 가장 큰 값을 찾는 방법을 보여드립니다.

MAXIFS 함수

Google 스프레드시트 및 Excel 2019 이상 사용자는 단일 MAXIFS 함수를 사용할 수 있습니다.

이 예제에서는 MAXIFS 및 DATE 함수를 사용하여 2021년 4월 30일 이전 주문 날짜의 각 스토어별 최대 주문량을 표시합니다.

=MAXIFS(D3:D8,B3:B8,"A",C3:C8,"<"&DATE(2021,4,30))

MAXIFS 함수는 수식을 입력할 때 사용자가 CTRL + SHIFT + ENTER를 누를 필요가 없습니다.

조건별 최대값 배열 함수

MAXIFS 함수를 사용할 수 없는 경우 배열 수식을 생성하여 MAXIFS 함수를 시뮬레이션할 수 있습니다. 아래에서 이에 대해 설명하겠습니다.

MAX 함수는 일련의 숫자에서 가장 큰 값을 식별합니다

=MAX(B2:B11)

MAX 함수를 IF 함수와 함께 사용하여 지정된 조건을 충족하는 가장 큰 값을 식별할 수 있습니다.

이 예제에서는 배열 수식에 MAX 함수와 IF 함수를 사용하여 각 스토어 별 최대 주문량을 반환합니다.

{=MAX(IF(B3:B8="A",D3:D8))}

Office 365 및 2019 이후 버전의 Excel에서는 평소와 같이 위 수식을 입력(Enter 키를 누름)하면 됩니다.

하지만 Excel 2019 및 이전 버전에서는 CTRL + SHIFT + ENTER를 눌러 수식을 입력해야 합니다. 이렇게 하면 수식 주위에 배열 중괄호가 나타나는 것을 볼 수 있습니다.

이 수식이 어떻게 작동하는지 보여드리기 위해 수식을 단계별로 나눠 보겠습니다.

다음은 최종 수식입니다.(자동으로 추가된 배열 수식 괄호 없이 표시됨)

=MAX(IF(B3:B8="A",D3:D8))

먼저 셀 범위 값이 수식에 배열로 추가됩니다.

=MAX(IF({"A"; "B"; "A"; "B"; "A"; "B"}="A",{500; 400; 300; 700; 600; 200})))

다음으로 상점이름 =”A” 조건은 TRUE/FALSE 값의 배열을 생성합니다.

=MAX(IF({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE},{500; 400; 300; 700; 600; 200})))

그런 다음 IF 함수는 모든 TRUE 값을 관련 주문량으로 변경합니다.

=MAX({500; FALSE; 300; FALSE; 600; FALSE}))

MAX 함수는 배열에서 가장 큰 숫자를 식별하고 모든 FALSE 값을 무시하여 상점이름 = “A”에 대해 가장 큰 주문 크기를 표시합니다.

=600

MAX If – 다중 조건

부울 논리를 사용하여 여러 기준을 기준으로 가장 큰 값을 식별할 수도 있습니다.

이 예제에서는 MAX, IF 및 DATE 함수를 사용하여 주문 날짜가 2021년 4월 30일 이전인 각 스토어별 최대 주문량을 표시합니다.

{=MAX(IF((B3:B8="A")*(C3:C8<DATE(2021,4,30)),D3:D8))}

여기서 두 세트의 참/거짓 기준을 함께 곱한 것을 주목하세요.

(B3:B8="A") * (C3:C8<DATE(2021,4,30))

두 기준이 모두 TRUE이면 전체 조건이 TRUE로 계산되지만, 하나 이상의 기준이 FALSE이면 FALSE로 계산됩니다.

이 방법론을 활용하면 이 공식에 다양한 기준을 추가할 수 있습니다.

Max If – 셀 참조를 이용한 여러 기준

일반적으로 수식에 값을 직접 입력하는 것은 좋은 방법이 아닙니다. 대신 별도의 셀을 사용하여 기준을 정의하는 것이 더 효율적입니다.

상점 이름을 F 열에 표시된 값과 일치시키기 위해 수식을 다음과 같이 업데이트할 수 있습니다.

{=MAX(IF((B3:B8=F3)*(C3:C8<DATE(2021,4,30)),D3:D8))}

셀 참조 고정하기

수식을 더 쉽게 읽을 수 있도록 셀 참조를 고정하지 않고 수식을 표시했습니다.

{=MAX(IF((B3:B8=F3)*(C3:C8<DATE(2021,4,30)),D3:D8))}

하지만 이러한 수식은 파일의 다른 곳에 복사하여 붙여넣으면 제대로 작동하지 않습니다. 대신 다음과 같이 셀 절대참조를 사용해야 합니다.

{=MAX(IF(($B$3:$B$8=F3)*($C$3:$C$8<DATE(2021,4,30)),$D$3:$D$8))}

자세한 내용은 셀 참조 고정하기 문서를 읽어보세요.

Google 스프레드시트의 최대값(조건이 있는 최대값)

위에 표시된 예제는 Google 스프레드시트에서 Excel과 동일하게 작동하지만, MAXIFS 함수를 사용할 수 있으므로 MAX 함수와 IF 함수를 결합하는 대신 이 단일 함수를 사용하는 것이 좋습니다.

MAX / IF 배열 수식을 사용하려면 배열 수식 함수에 수식을 입력하세요.(또는 간단히 CTRL + SHIFT + ENTER를 사용하면 Google 스프레드시트가 이 함수를 추가합니다.)

=ARRAYFORMULA(MAX(IF((B3:B8="A")*(C3:C8<DATE(2021,4,30)),D3:D8))))