Máximo, Mínimo y K.esimo condicionales en Excel

lunes, junio 22, 2015

Excel no cuenta con funciones para calcular máximos o mínimos sujetos a condiciones. Por ejemplo, en esta lista de valores por departamentos


Para encontrar el valor máximo y el mínimo del Depto. 4 tenemos que usar funciones matriciales.
En la celda F3 ponemos la fórmula
=MAX((B3:B18=F2)*C3:C18) 
en forma matricial (se introduce apretando simultáneamente Ctrl-Mayúsculas-Enter), y en la celda F4 la fórmula matricial
=MIN(SI(B3:B18=F2,C3:C18))

La nueva función AGREGAR  (disponible a partir de la versión 2010 de Excel) nos permite encontrar máximos y mínimos, como también valores "k.ésimos" (segundo mayor, tercer menor, etc.) sin necesidad de usar fórmulas matriciales.



Para encontrar el valor máximo del Depto. 4 usamos esta fórmula

=AGREGAR(14,6,C3:C18/(B3:B18=$F$2),1)

y para el mínimo

=AGREGAR(15,6,C3:C18/(B3:B18=$F$2),1)

Veamos cómo funciona esta fórmula:



Como explicamos en la nota mecionada, AGREGAR cuenta con 19 operaciones siendo 14 K.ESIMO.MAYOR y la operación 15, K.ESIMO.MENOR.

El segundo argumento (Opciones = 6) indica que los errores serán ignorados.

La expresión C3:C18/(B3:B18=$F$2) en la fórmula crea una matriz que contiene errores DIV/0! como podemos ver en esta columna auxiliar


cuando la fila evaluada no corresponde al Depto. 4,que serán ignorados, dado que hemos establecido el argumento Opciones con el valor 6.

El argumento "k" funciona de la misma manera que en la función K.ESIMO.MAYOR. Al establecer 1, el resultado será el mayor de los valores.


Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP