Explicación
Para mostrar una lista de nombres, clasificados por un valor numérico, puede usar un conjunto de fórmulas basadas en K.ESIMO.MAYOR, INDICE, COINCIDIR, con la ayuda de la función TEXTO. En el ejemplo que se muestra, la fórmula en G5 es:
=K.ESIMO.MAYOR(SI(TEXTO(date,"mmmm")=G$4,amount),$F5)
Y la fórmula en G10 es:
donde la fecha del cliente (B5: B17) (C5: C17) y la cantidad (C5: C17) se denominan rangos.
Nota: estas son fórmulas de matriz y deben ingresarse con control + shift + enter, excepto en Excel 365.
Cómo funciona esta fórmula
Este ejemplo se configura en dos partes para mayor claridad: (1) una fórmula para determinar las 3 principales cantidades para cada mes y (2) una fórmula para recuperar el nombre del cliente para cada una de las 3 principales cantidades mensuales.
Tenga en cuenta que no hay un rango real en los datos de origen. En cambio, estamos utilizando la función K.ESIMO.MAYOR para trabajar directamente con cantidades. Otro enfoque sería agregar rango a los datos de origen con la función JERARQUIA y usar el valor de rango para recuperar los nombres de los clientes.
Parte 1: recupere las 3 principales cantidades cada mes
Para recuperar las 3 principales cantidades para cada semana, la fórmula en G5 es:
=K.ESIMO.MAYOR(SI(TEXTO(date,"mmmm")=G$4,amount),$F5)
Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter, excepto en Excel 365.
Trabajando de adentro hacia afuera, primero usamos la función TEXTO para obtener los nombres de los meses para cada fecha en la fecha del rango con nombre:
TEXTO(date,"mmmm") // get month names
El formato de número personalizado "mmmm" devolverá una cadena como "abril", "mayo", "junio" para cada nombre en la fecha del rango con nombre. El resultado es una matriz de nombres de mes como este:
{"April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June"}
La función TEXTO entrega esta matriz a la función SI, que está configurada para filtrar fechas en un mes determinado al probar el nombre del mes con el valor en G4 (una referencia mixta, por lo que la fórmula se puede copiar hacia abajo y hacia adelante):
Solo las cantidades en abril sobreviven y pasan por SI; todos los demás valores son FALSO:
{10500;15200;18500;12500;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO}
Finalmente, la función K.ESIMO.MAYOR usa el valor en F5 (también una referencia mixta) para devolver el "enésimo" valor más grande que queda. En la celda G5, K.ESIMO.MAYOR devuelve 18,500, el "1er" valor más grande. A medida que la fórmula se copia y cruza la tabla, la función K.ESIMO.MAYOR devuelve las 3 principales cantidades en cada uno de los tres meses.
Ahora que conocemos los 3 valores principales de cada mes, podemos usar esta información como una "clave" para recuperar el nombre del cliente para cada uno.
Parte 2: recuperar nombres de clientes
Nota: Este es un ejemplo de uso de INDICE y COINCIDIR con múltiples criterios. Si este concepto es nuevo para usted, aquí hay un ejemplo básico.
Para recuperar el nombre asociado con los tres valores principales en G5: I7, utilizamos INDICE y COINCIDIR:
Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter, excepto en Excel 365.
Trabajando de adentro hacia afuera, la función COINCIDIR está configurada para usar lógica booleana como esta:
El valor de búsqueda es 1, y la matriz de búsqueda se construye con esta expresión:
(amount=G5)*(TEXTO(date,"mmmm")=G$9)
La expresión que crea la matriz de búsqueda utiliza la lógica booleana para "filtrar" cantidades que son (1) no en abril y (2) no el valor en G5 (18,500). El resultado es una matriz de 1s y 0s como este:
{0;0;1;0;0;0;0;0;0;0;0;0;0}
Con un valor de búsqueda de 1 y cero para el tipo de coincidencia (para forzar una coincidencia exacta) COINCIDIR devuelve 3 directamente a la función INDICE:
=INDICE(client,3) // returns "Janus"
INDICE devuelve el tercer valor en el cliente de rango con nombre, "Janus".
A medida que la fórmula se copia y cruza la tabla, devuelve los 3 principales clientes en cada uno de los tres meses.
0 comentarios:
Publicar un comentario