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: ambas fórmulas 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.
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, usamos la función SI para filtrar todos los valores que no están en abril de esta manera:
La función TEXTO se usa para construir una cadena como "April" para cada fecha en la fecha del rango con nombre, y SI prueba este valor contra 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 entran en la matriz devuelta por SI, que se ve así:
{10500;15200;18500;12500;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO}
Tenga en cuenta que todos los demás valores ahora son FALSO.
La función K.ESIMO.MAYOR usa el valor en F5 (también una referencia mixta) para devolver el valor más grande que permanece, en este caso, 18,500.
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 conocemos los 3 valores principales de cada mes, y podemos usar esta información como una "clave" para encontrar el nombre del cliente para cada uno.
Recuperar nombres de clientes
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, la fórmula devuelve los 3 clientes principales en cada uno de los tres meses.
0 comentarios:
Publicar un comentario