martes, 14 de julio de 2020

Alternativas a MAX.SI.CONJUNTO

Hace bastante tiempo que no escribo nada sobre funciones, y revisando material publicado me percaté que nunca escribí sobre la función MAX.SI.CONJUNTO (operativa desde la versión 2016 y +).
Esta función es muy interesante, dentro de la familia de las funciones .SI.CONJUNTO, ya que permite recuperar un valor máximo de entre distintos registros que verifiquen hasta 127 criterios... muy potente, sin duda!!.
Veamos un ejemplo:
Alternativas a MAX.SI.CONJUNTO

La función aplicada a este ejemplo que permite conocer la fecha mayor (más reciente) para un 'empleado' y un 'cliente' dado sería:
=MAX.SI.CONJUNTO(TblVisitas[Día];TblVisitas[Empleado];$F3;TblVisitas[Cliente];G$2)
función al uso, donde el primer argumento indica el rango de donde obtener el valor máximo, y el resto de argumentos alterna rango de criterio y criterio aplicado (ver funciones SUMAR.SI.CONJUNTO, CONTAR.SI.CONJUNTO o PROMEDIO.SI.CONJUNTO para mayor entendimiento).

La función es muy potente pero al no estar disponibles en versiones previas al 2016 nos da problemas de compatibilidad, que hoy veremos algunas formas de 'combatir'.

Una primera opción sería usar fórmulas matriciales (las clásicas CSE - ejecutadas presionando Ctrl Mayusc y Enter):
{=MAX(SI(TblVisitas[Empleado]=$F12;SI(TblVisitas[Cliente]=G$11;TblVisitas[Día])))}
donde hemos incluido tantos condicionales SI como criterios tengamos, para finalmente aplicar la función MAX.
Como inconveniente de este tipo de funciones tenemos el alto consumo de recursos que necesita para su recálculo.

Una alternativa diferente sería el uso de INDICE en su formato de referencia, donde al indicar como argumento de fila un cero 0, recuperamos un conjunto o matriz de valores...
=MAX(INDICE(TblVisitas[Día]*(TblVisitas[Empleado]=$F20)*(TblVisitas[Cliente]=G$19);0)) Además de forma interna evalúa las condiciones o criterios obtenidos del producto de rango condicionados.

Otra opción muy similar a la anterior vendría de la mano de SUMAPRODUCTO
=SUMAPRODUCTO(MAX((TblVisitas[Día]*(TblVisitas[Empleado]=$F28)*(TblVisitas[Cliente]=G$19))))
con una mecánica de cálculo casi idéntica a la anterior.

Y la última posibilidad que he trabajado responde al uso de las fórmulas desbordadas (disponibles a fecha de hoy a ciertos usuarios de 365):
=INDICE(ORDENAR(FILTRAR(TblVisitas[Día];(TblVisitas[Empleado]=$F36)*(TblVisitas[Cliente]=G$35);"");1;-1);1)
donde con la nuevas funciones básicamente se filtran por las condiciones necesarias de 'cliente' y 'empleado', para luego ordenar en sentido descendente... quedando la fecha mayor o más reciente en la primera celda/superior del rango desbordado.
Finalmente la funció INDICE recupera esa primera celda del rango desbordado obtenido.

Puedes ver en la imagen más arriba el resultado obtenido en cada caso.

Seguro habrá más formas y variantes, pero creo es un buen compendio de formulación avanzada ;-)

No hay comentarios:

Publicar un comentario

Nota: solo los miembros de este blog pueden publicar comentarios.