EXCEL: Rompa los lazos con la columna auxiliar y CONTAR.SI

Para romper los vínculos, puede usar una columna auxiliar y la función CONTAR.SI para ajustar los valores de modo que no contengan duplicados y, por lo tanto, no se generen vínculos. En el ejemplo que se muestra, la fórmula en D5 es:

=C5+(CONTAR.SI($C$5:C5,C5)-1)*0.01

Contexto

A veces, cuando usa funciones como K.ESIMO.MENOR, K.ESIMO.MAYOR o JERARQUIA para clasificar los valores más altos o más bajos, termina con vínculos, porque los datos contienen duplicados. Una forma de romper vínculos como este es agregar una columna auxiliar con valores que se hayan ajustado, luego clasificar esos valores en lugar de los originales.

En este ejemplo, la lógica utilizada para ajustar los valores es aleatoria: el primer valor duplicado "ganará", pero puede ajustar la fórmula para usar la lógica que se ajuste a su situación particular y caso de uso.

Cómo funciona esta fórmula

En el núcleo, esta fórmula utiliza la función CONTAR.SI y un rango expansivo para contar las ocurrencias de valores. La referencia expansiva se utiliza para que CONTAR.SIS devuelva un recuento de ocurrencias en ejecución, en lugar de un recuento total para cada valor:

CONTAR.SI($C$5:C5,C5)

A continuación, se resta 1 del resultado (que hace que el recuento de todos los valores no duplicados sea cero) y el resultado se multiplica por 0.01. Este valor es el "ajuste", y es intencionalmente pequeño para no afectar materialmente el valor original.

En el ejemplo que se muestra, Metrolux y Diamond tienen la misma estimación de $ 5000. Dado que Metrolux aparece primero en la lista, el recuento continuo de 5000 es 1 y se cancela restando 1, por lo que la estimación se mantiene sin cambios en la columna auxiliar:

=C8+(CONTAR.SI($C$5:C8,C8)-1)*0.01
=C8+(1-1)*0.01
=C8+0
=C8

Sin embargo, para Diamond, la cuenta corriente de 5000 es 2, por lo que la estimación se ajusta:

=C11+(CONTAR.SI($C$5:C11,C11)-1)*0.01
=C11+(2-1)*0.01
=C11+1*0.01
=C11+0.01

Finalmente, los valores ajustados se usan para clasificar en lugar de los valores originales en las columnas G y H. La fórmula en G5 es:

=K.ESIMO.MENOR($D$5:$D$12,F5)

La fórmula en H5:

=INDICE($B$5:$B$12,COINCIDIR(G5,$D$5:$D$12,0))

Consulte esta página para obtener una explicación de estas fórmulas.

Columna auxiliar temporal

Si no desea usar una columna auxiliar en la solución final, puede usar una columna auxiliar temporalmente para obtener valores calculados, luego use Pegado especial para convertir los valores "en su lugar" y eliminar la columna auxiliar después. Este video muestra la técnica.