Resumen
Para mostrar una lista de nombres, clasificados por un valor numérico, puede usar un conjunto de fórmulas basadas en GRANDE, ÍNDICE, COINCIDIR, con la ayuda de la función TEXTO. En el ejemplo que se muestra, la fórmula en G5 es:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Y la fórmula en G10 es:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
donde el cliente (B5: B17) la fecha (C5: C17) y la cantidad (C5: C17) son rangos de nombres.
Nota: estas son fórmulas de matriz y deben ingresarse con control + shift + enter, excepto en Excel 365.
Explicación
Este ejemplo se establece en dos partes para mayor claridad: (1) una fórmula para determinar las 3 cantidades principales para cada mes y (2) una fórmula para recuperar el nombre del cliente para cada una de las 3 cantidades mensuales principales.
Tenga en cuenta que no hay una clasificación real en los datos de origen. En cambio, usamos la función GRANDE para trabajar directamente con cantidades. Otro enfoque sería agregar rango a los datos de origen con la función RANK y usar el valor de rango para recuperar nombres de clientes.
Parte 1: recupere las 3 principales cantidades cada mes
Para recuperar las 3 principales cantidades de cada semana, la fórmula en G5 es:
=LARGE(IF(TEXT(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 el rango de fechas nombrado :
TEXT(date,"mmmm") // get month names
El formato de número personalizado "mmmm" devolverá una cadena como "abril", "mayo", "junio" para cada nombre en el rango de fechas nombrado . El resultado es una matriz de nombres de meses como esta:
("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")
La función TEXT entrega esta matriz a la función SI, que está configurada para filtrar fechas en un mes dado probando el nombre del mes con el valor en G4 (una referencia mixta, por lo que la fórmula se puede copiar y cruzar):
IF(TEXT(date,"mmmm")=G$4,amount) // filter on month
Solo las cantidades en abril sobreviven y superan el IF; todos los demás valores son FALSOS:
(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Finalmente, la función GRANDE usa el valor en F5 (también una referencia mixta) para devolver el "n-ésimo" valor más grande que queda. En la celda G5, GRANDE devuelve 18.500, el "primer" valor más grande. A medida que la fórmula se copia hacia abajo y en toda la tabla, la función GRANDE devuelve las 3 cantidades principales 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 del uso de INDICE y COINCIDIR con varios criterios. Si este concepto es nuevo para usted, aquí tiene un ejemplo básico.
Para recuperar el nombre asociado con los tres valores principales en G5: I7, usamos INDICE y COINCIDIR:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter, excepto en Excel 365.
Trabajando desde adentro hacia afuera, la función MATCH está configurada para usar lógica booleana como esta:
MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)
El valor de búsqueda es 1 y la matriz de búsqueda se construye con esta expresión:
(amount=G5)*(TEXT(date,"mmmm")=G$9)
La expresión que crea la matriz de búsqueda usa lógica booleana para "filtrar" las cantidades que (1) no están en abril y (2) no son el valor en G5 (18,500). El resultado es una matriz de 1 y 0 como esta:
(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), MATCH devuelve 3 directamente a la función INDICE:
=INDEX(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 clientes principales en cada uno de los tres meses.
