Max Si (Valor Máximo con Condición) – Excel y Google Sheets

Este tutorial demostrará cómo encontrar el mayor valor que cumple con condiciones específicas en Excel y Google Sheets.

 

Función MAX.SI.CONJUNTO

Los usuarios de Google Sheets y Excel 2019 o posterior pueden utilizar la función única MAX.SI.CONJUNTO. Este ejemplo utiliza las funciones MAX.SI.CONJUNTO y FECHA para mostrar el mayor tamaño de pedido de cada nombre de tienda para las fechas de pedido anteriores al 30/4/2021:

=MAX.SI.CONJUNTO(D3:D8;B3:B8;"A";C3:C8;"<"&FECHA(2021;4;30))

La función MAX.SI.CONJUNTO no requiere que el usuario pulse CTRL + SHIFT + ENTER al introducir la fórmula.

Función Max Si Array

Si no tiene acceso a la Función MAX.SI.CONJUNTO, puede crear una Fórmula de Matriz para simular la Función MAXIFS. Lo veremos a continuación. La Función MAX identifica el mayor valor en una serie de números.

=MAX(B2:B11)

Podemos utilizar la Función MAX combinada con una Función SI para identificar el mayor valor que cumple una condición especificada. Este ejemplo utilizará las funciones MAX y SI en una fórmula de matriz para identificar el mayor tamaño de pedido para cada nombre de tienda

{=MAX(SI(B3:B8="A";D3:D8))}

En Office 365 y en las versiones de Excel posteriores a 2019, puede simplemente introducir la fórmula anterior como lo haría normalmente (pulsando ENTER).

Sin embargo, en el caso de Excel 2019 y versiones anteriores, debes introducir la fórmula pulsando CTRL + SHIFT + ENTER. Después de hacerlo, notarás que aparecen paréntesis de matriz rizados alrededor de la fórmula.

Para mostrar cómo funciona esta fórmula, vamos a dividirla en pasos.

Esta es nuestra fórmula final (mostrada sin los paréntesis de fórmula de matriz añadidos automáticamente):

=MAX(SI(B3:B8="A";D3:D8))

En primer lugar, los valores del rango de celdas se añaden a la fórmula como matrices:

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

A continuación, la condición Nombre de la tienda =»A» produce una matriz de valores VERDADERO/FALSO:

=MAX(SI({VERDADERO;FALSO;VERDADERO;FALSO;VERDADERO;FALSO};{500;400;300;700;600;200}))

A continuación, la función IF cambia todos los valores TRUE por el tamaño del pedido correspondiente :

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

La función MAX identifica el mayor número de la matriz, ignorando los valores FALSO, para mostrar el mayor tamaño de pedido para el nombre de la tienda = «A»:

=600

Max Si – Criterios Múltiples

También podemos identificar el mayor valor basado en múltiples criterios utilizando la lógica booleana. Este ejemplo mostrará el mayor tamaño de pedido para cada nombre de tienda, pero para fechas de pedido anteriores al 30/4/2021, utilizando las funciones MAX, IF y DATE:

{=MAX(SI((B3:B8="A")*(C3:C8<FECHA(2021;4;30));D3:D8))}

Observe que aquí multiplicamos dos conjuntos de criterios VERDADERO/FALSO juntos:

(B3:B8="A")*(C3:C8<FECHA(2021;4;30))

Si ambos criterios son VERDADERO entonces la condición total se calculará como VERDADERO, pero si uno (o más) criterios es FALSO se calculará como FALSO. Utilizando esta metodología, es posible añadir muchos criterios diferentes a esta fórmula.

Max Si – Múltiples Criterios con Referencias de Celdas

Por lo general, no es una buena práctica codificar valores en las fórmulas. En su lugar, es más flexible utilizar celdas separadas para definir los criterios. Para hacer coincidir el Nombre de la tienda con el valor mostrado en la columna F, podemos actualizar la fórmula para que sea

{=MAX(SI((B3:B8=F3)*(C3:C8<FECHA(2021;4;30));D3:D8))}

 

Bloqueo de las Referencias de las Celdas

Para facilitar la lectura de nuestras fórmulas, hemos mostrado las fórmulas sin referencias de celdas bloqueadas:

{=MAX(SI((B3:B8=F3)*(C3:C8<FECHA(2021;4;30));D3:D8))}

Pero estas fórmulas no funcionarán correctamente cuando se copien y peguen en otro lugar del archivo. En su lugar, debes utilizar referencias de celdas bloqueadas como esta

{=MAX(SI(($B$3:$B$8=F3)*($C$3:$C$8<FECHA(2021;4;30));$D$3:$D$8))}

Lea nuestro artículo sobre el bloqueo de referencias de celdas para obtener más información.

Max Si (Valor Máximo con Condición) en Google Sheets

Los ejemplos mostrados anteriormente funcionan exactamente igual en Google Sheets que en Excel, pero como la función MAX.SI.CONJUNTO está disponible, se recomienda utilizar esta única función en lugar de combinar las funciones MAX y SI. Si quieres utilizar la fórmula de matriz MAX / SI, introduce la fórmula dentro de la función ARRAYFORMULA (o simplemente utiliza CTRL + SHIFT + ENTER y Google Sheets añadirá esta función por ti).

=ARRAYFORMULA(MAX(SI((B5:B10="A")*(C5:C10<FECHA(2021;4;30));D5:D10)))