Haremos un uso de una función muy potente en Excel como es SUMAPRODUCTO, que por su comportamiento 'matricial' a la hora de trabajar con rangos nos facilitará el objetivo del post del día: Obtener el ranking de un listado basado en varios criterios.
Partiremos del listado de la imagen:
Vemos un listado de años divididos por trimestres, a partir del cual queremos obtener, para cada año, el ranking de las ventas...
Nuestra fórmula buscada a incluir en D2 y siguientes será:
=SUMAPRODUCTO(--($B$2:$B$17=B2);--(C2>$C$2:$C$17))+ CONTAR.SI.CONJUNTO($B$2:$B2;B2;$C$2:$C2;"="&C2)
haz clic en la imagen
La explicación de nuestra fórmula corresponde a lo siguiente.
El primer argumento de SUMAPRODUCTO (--($B$2:$B$17=B2)) nos devolverá un conjunto de unos y ceros, con 1 para el año de estudio.. por ejemplo, para la celda D2 (el año 14) tendríamos:
{1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0}
El segundo argumento de SUMAPRODUCTO (--(C2>$C$2:$C$17)) nos devolverá un conjunto de unos y ceros, con 1 para los importes superiores al del estudio (al de la fila de la fórmula)... por ejemplo, para la celda D2 (el año 14) tendríamos:
{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
Aquí es importante tener claro el tipo de ranking u ordenación que deseemos:
1-Ascendente: --(C2>$C$2:$C$17)
2-Descendente: --(C2
El resultado de multiplicar uno a uno ambos rangos nos sumará la posición parcial para cada año...
Finalmente para corregir aquellos posible casos en que se den repeticiones de importes de ventas incluimos una función CONTAR.SI.CONJUNTO a nuestra fórmula que 'desempatará' solo en los casos de importes repetidos.
OJO que debemos emplear rangos corridos en sus argumentos!!:
CONTAR.SI.CONJUNTO($B$2:$B2;B2;$C$2:$C2;"="&C2)
Como curiosidad final. Si quisiéramos obtener un ranking absoluto, solo basado en los importes, podríamos plantear la siguiente fórmula:
=SUMAPRODUCTO(--(C2>$C$2:$C$17))+CONTAR.SI($C$2:$C2;"="&C2)
Esta fórmula corrige el defecto de la función
=JERARQUIA.EQV(C2:C17;C2:C17;1)
que por si sola no ordena los elementos repetidos...
Partiremos del listado de la imagen:
Vemos un listado de años divididos por trimestres, a partir del cual queremos obtener, para cada año, el ranking de las ventas...
Nuestra fórmula buscada a incluir en D2 y siguientes será:
=SUMAPRODUCTO(--($B$2:$B$17=B2);--(C2>$C$2:$C$17))+ CONTAR.SI.CONJUNTO($B$2:$B2;B2;$C$2:$C2;"="&C2)
haz clic en la imagen
La explicación de nuestra fórmula corresponde a lo siguiente.
El primer argumento de SUMAPRODUCTO (--($B$2:$B$17=B2)) nos devolverá un conjunto de unos y ceros, con 1 para el año de estudio.. por ejemplo, para la celda D2 (el año 14) tendríamos:
{1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0}
El segundo argumento de SUMAPRODUCTO (--(C2>$C$2:$C$17)) nos devolverá un conjunto de unos y ceros, con 1 para los importes superiores al del estudio (al de la fila de la fórmula)... por ejemplo, para la celda D2 (el año 14) tendríamos:
{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
Aquí es importante tener claro el tipo de ranking u ordenación que deseemos:
1-Ascendente: --(C2>$C$2:$C$17)
2-Descendente: --(C2
El resultado de multiplicar uno a uno ambos rangos nos sumará la posición parcial para cada año...
Finalmente para corregir aquellos posible casos en que se den repeticiones de importes de ventas incluimos una función CONTAR.SI.CONJUNTO a nuestra fórmula que 'desempatará' solo en los casos de importes repetidos.
OJO que debemos emplear rangos corridos en sus argumentos!!:
CONTAR.SI.CONJUNTO($B$2:$B2;B2;$C$2:$C2;"="&C2)
Como curiosidad final. Si quisiéramos obtener un ranking absoluto, solo basado en los importes, podríamos plantear la siguiente fórmula:
=SUMAPRODUCTO(--(C2>$C$2:$C$17))+CONTAR.SI($C$2:$C2;"="&C2)
Esta fórmula corrige el defecto de la función
=JERARQUIA.EQV(C2:C17;C2:C17;1)
que por si sola no ordena los elementos repetidos...
This post first appeared on EXCEL FORO: EJERCICIOS, EJEMPLOS, SOLUCIONES, DUDA, please read the originial post: here
