Descarga el libro de trabajo de ejemplo
Este tutorial demostrará cómo encontrar el valor de celda más grande que cumpla con condiciones específicas en Excel y Google Sheets.
Función Max If Array
La función MAX identifica el valor más grande en una serie de números.
Podemos usar la función MAX combinada con una función IF para identificar el valor más grande que cumple una condición específica.
Se recomienda a los usuarios de Google Sheets y Excel 2022 o posterior que utilicen la función MAXIFS, más sencilla. Esto se explica en una sección posterior.
Este ejemplo utilizará las funciones MAX e IF en una fórmula de matriz para identificar la mayor Tamaño de la orden para cada Nombre de la tienda
| 1 | {= MAX (IF (B3: B8 = "A", D3: D8))} |
En Office 365 y las versiones de Excel posteriores a 2022, simplemente puede ingresar la fórmula anterior como lo haría normalmente (presionando ENTRAR).
Sin embargo, para Excel 2022 y versiones anteriores, debe ingresar la fórmula presionando CTRL + MAYÚS + ENTRAR. Después de hacerlo, notará que aparecen corchetes de matriz alrededor de la fórmula.
Para mostrar cómo funciona esta fórmula, vamos a dividirla en pasos.
Esta es nuestra fórmula final (que se muestra sin los corchetes de fórmula de matriz agregados automáticamente):
| 1 | = MAX (SI (B3: B8 = "A", D3: D8)) |
Primero, los valores del rango de celdas se agregan a la fórmula como matrices:
| 1 | = MAX (SI ({"A"; "B"; "A"; "B"; "A"; "B"} = "A", {500; 400; 300; 700; 600; 200})) |
Siguiente el Nombre de la tienda = La condición "A" produce una matriz de valores VERDADERO / FALSO:
| 1 | = MÁX (SI ({VERDADERO; FALSO; VERDADERO; FALSO; VERDADERO; FALSO}, {500; 400; 300; 700; 600; 200})) |
Luego, la función SI cambia todos los valores VERDADEROS en los valores relevantes. Tamaño de la orden:
| 1 | = MAX ({500; FALSO; 300; FALSO; 600; FALSO}) |
La función MAX identifica el número más grande en la matriz, mientras ignora cualquier valor FALSO, para mostrar el mayor Tamaño de la orden Para el Nombre de la tienda = "A":
| 1 | =600 |
Max If - Criterios múltiples
También podemos identificar el valor más grande en función de varios criterios mediante el uso de lógica booleana.
Este ejemplo mostrará el mayor Tamaño de la orden para cada Nombre de la tienda, pero para Fechas de pedido antes del 30/4/2021 utilizando las funciones MAX, IF y DATE:
| 1 | {= MAX (SI ((B3: B8 = "A") * (C3: C8 |
Observe que aquí multiplicamos dos conjuntos de criterios VERDADERO / FALSO juntos:
| 1 | (B3: B8 = "A") * (C3: C8 |
Si ambos criterios son VERDADEROS, la condición total se calculará como VERDADERA, pero si uno (o más) criterios es FALSO, se calculará como FALSO.
Usando esta metodología, es posible agregar muchos criterios diferentes a esta fórmula.
Max If: varios criterios con referencias de celda
Por lo general, no es una buena práctica codificar valores en fórmulas. En cambio, es más flexible usar celdas separadas para definir los criterios.
Para que coincida con el Nombre de la tienda al valor que se muestra en la columna F, podemos actualizar la fórmula para que sea:
| 1 | {= MAX (SI ((B3: B8 = F3) * (C3: C8 |
Referencias de celda de bloqueo
Para que nuestras fórmulas sean más fáciles de leer, mostramos las fórmulas sin referencias de celda bloqueadas:
| 1 | {= MAX (SI ((B3: B8 = F3) * (C3: C8 |
Pero estas fórmulas no funcionarán correctamente cuando se copien y peguen en otro lugar de su archivo. En su lugar, debe usar referencias de celda bloqueadas como esta:
| 1 | {= MAX (SI (($ B $ 3: $ B $ 8 = F3) * ($ C $ 3: $ C $ 8 |
Lea nuestro artículo sobre Referencias de celdas bloqueadas para obtener más información.
Función MAXIFS
Los usuarios de Google Sheets y Excel 2022 o posterior pueden usar la función MAXIFS única para replicar el comportamiento de las funciones MAX e IF que se muestran en los ejemplos anteriores.
El siguiente ejemplo utiliza las funciones MAXIFS y DATE para mostrar la mayor Tamaño de la orden para cada Nombre de la tienda por Fechas de pedido antes del 30/4/2021:
| 1 | = MAXIFS (D3: D8, B3: B8, "A", C3: C8, "<" & FECHA (2021,4,30)) |
La función MAXIFS no requiere que el usuario presione CTRL + MAYÚS + ENTRAR al ingresar la fórmula.
Max If (valor máximo con condición) en Google Sheets
Los ejemplos que se muestran arriba funcionan exactamente igual en Google Sheets que en Excel, pero como la función MAXIFS está disponible, se recomienda utilizar esta función única en lugar de combinar las funciones MAX e IF.
Si es necesario utilizar los ejemplos que usan las funciones MAX e IF, Google Sheets requiere que los ingrese como fórmulas de matriz. En lugar de mostrar la fórmula con corchetes de matriz rizada de Excel {}, presionar CTRL + MAYÚS + ENTRAR agrega automáticamente la función ARRAYFORMULA alrededor de la fórmula:
| 1 | = ARRAYFORMULA (MAX (IF ((B3: B8 = "A") * (C3: C8 |
