Operaciones con constantes matriciales

En alguna de mis prehistóricas notas he tocado el tema de las constantes matriciales. Las constantes matriciales nos permiten simplificar nuestras fórmulas, como en el ejemplo que describo a continuación.

Supongamos que queremos calcular el promedio de los tres menores valores de esta lista, (señalados con fuente roja)

Una posibilidad es hacerlo usando una columna auxiliar con la función JERARQUIA para obtener el número de orden y luego usar PROMEDIO.SI

Pero si por alguna razón queremos evitar le uso de columnas auxiliares (por ejemplo, para impresionar al jefe), podemos combinar PROMEDIO.SI con K.ESIMO.MENOR y una constante matricial:

=PROMEDIO(K.ESIMO.MENOR(B3:B12,{1,2,3}))

Como puede apreciarse la fórmula es compacta y a pesar de que estamos usando tres criterios a la vez, no es matricial (la introducimos como toda fórmula corriente).
Si queremos, por ejemplo, calcular la suma de los 5 mayores números en la lista (señalados con fuente verde) usamos

=SUMA(K.ESIMO.MAYOR(B3:B12,{1,2,3,4,5}))

Podemos crear una constante matricial usando la función FILA, de esta manera

=SUMA(K.ESIMO.MAYOR(B3:B12,FILA(1:5)))

pero en este caso debemos usar la fórmula en forma matricial, es decir, introducirla apretando simultáneamente Ctrl.-Mayúsculas-Enter.

Otra posibilidad interesante es el uso de Tablas o nombres definidos para crear una referencia dinámica a los criterios.

Por ejemplo, creamos una tabla de criterios como ésta:

Ahora podemos usar la tabla (tblCriterios) como argumento en nuestra fórmula:

=PROMEDIO(K.ESIMO.MENOR(B3:B12,tblCriterios[#Datos]))

La ventaja de esta técnica es que podemos cambiar dinámicamente los criterios en la fórmulas sin necesidad de editarla.