Con bastante frecuencia me he encontrado con la necesidad de hallar el máximo valor de una serie de datos pero con una condición o con varias.
Para resolver este problema en una serie de datos muy grande creé mi propia función: la función MAXIF. Una función creada en VBA que necesitaba para un momento concreto pero esta semana, en un ratillo que me ha dado por jugar con Excel, he pensado que tiene una solución mucho más cómoda.
Máximo con una condición
Todo se entiende mejor con un ejemplo así que vamos allá.
La tabla de datos que hemos utilizado es la siguiente:
En esta tabla tenemos una serie de datos con animales y un valor asociado a cada uno de ellos. Si queremos conocer el mayor valor asignado al Animal “Perro” podríamos filtrar la tabla y luego ordenarla pero entonces no sería automático y esto no siempre nos sirve.
Para poder hallar el máximo valor condicional necesitaremos usar las funciones SI y MAX. Estas funciones nos permitirá calcular el máximo valor de una tabla con una condición.
La fórmula que necesitamos escribir es:
=MAX(SI(A6:A20=”Perro”;B6:B20;))
Pero vayamos por partes para entenderlo mejor.
Uso de la función SI
La función SI en este caso la usamos para todo el rango de datos… ¿pero cómo?. Pues muy sencillo, ahora lo vemos.
- La prueba lógica de la función es toda la columna de Animales = al animal seleccionado (en nuestro caso el Perro).
- El argumento si es verdadero es toda la columna de valores y si es falso es nada. Pero, ¿por qué no ponemos algo? Porque la función SI, cuando devuelve el resultado de Falso lo devuelve como un 0.
Uso de la función MAX
La función MAX nos da el valor más grande de la serie de datos.
Combinación en fórmula matricial
Hasta aquí no ha sido muy complicado, pero ahora se va a poner más interesante. ¿Por qué hemos puesto todo el rango dentro de la función SI? Porque vamos a usar una fórmula matricial para calcular todo esto.
¿Pero qué es lo que hace esta fórmula matricial? Lo que hacemos es decirle a Excel que haga el máximo de todas las celdas que tengan como condición la palabra Perro. Pero lo vas a entender mejor con una imagen.
Es decir, la función SI nos indica qué valores tenemos que seleccionar… y los valores que no seleccionemos los convertirá a 0.
Después la función MAX buscará el máximo valor de la lista que se crea dentro de la celda {2/6/0/0/0/12/9}.
Es decir, el orden de cálculo de Excel sería:
=MAX(SI(A6:A20=”Perro”;B6:B20;))
y…
=MAX(SI({Perro/Perro/Gato…Gato/Perro/Perro}=Perro;{2/6/0/…/0/12/9};0))
y…
=MAX({2/6/0/…/0/12/9})
y…
=12
Como puedes ver, finalmente lo que queda dentro de una celda es un listado del que elegir el valor máximo.
MUY IMPORTANTE: La fórmula que acabamos de crear es una fórmula matricial, es decir, en su interior usa matrices y esto se lo tenemos que indicar a Excel. Para ello debemos poner la fórmula entre corchetes ({}) como puede verse:
={MAX(SI(A6:A20=”Perro”;B6:B20;))}
Pero no pongas los corchetes a mano como me pasaba a mi sino que cuando tengas la fórmula escrita deberás apretar las teclas: Ctrl + Mayusc + Intro.
Máximo con dos condiciones
Pero también podemos hacerlo con dos condiciones y funciona de la misma manera. Simplemente lo que tendremos que hacer es anidar dos funciones SI.
Para ello usaremos la siguiente tabla de datos:
Donde lo que queremos hacer es buscar el máximo valor del Animal “Perro” cuyo tamaño sea “Grande”. Y la función será:
=MAX(SI(A6:A20=”Perro”;SI(B6:B20=”Grande”;C6:C20;)))
Y como una imagen vale más que mil palabras te dejo aquí como sería el esquema mental de Excel (siempre de dentro a fuera).
Creo que se entiende bastante bien la idea pero por si acaso os dejo una descarga del ejemplo que hemos usado.
¡Descárgate un ejemplo!
[sociallocker]
[/sociallocker]