Función JERARQUIA en Excel - Rangos continuos

En la nota sobre la función JERARQUIA vimos que esta función asigna la misma posición relativa a valores duplicados en el rango que estamos considerando. La presencia de números duplicados afecta la jerarquía de los números subsiguientes. Por ejemplo, en una lista de números enteros, si un número aparece dos veces y tiene una jerarquía de 5, entonces el número siguiente en tamaño tendrá una jerarquía de 7 y ningún número en la lista tendrá la jerarquía 6.
En esa nota vimos una técnica para lograr que cada valor en la lista tenga un número de jerarquía único.

Uno de mis lectores me hace la siguiente pregunta:

Tenemos valores con sus jerarquías así:

valor	jerarquía
5	5
7	4
10	1
10	1
8	3

¿No hay manera de que en efecto, los dos valores de 10 tengan la jerarquía de 1 pero el 8 tenga jerarquía de 2, el 7 tenga jerarquía de 3 y el 5 tenga jerarquía de 4?
En otras palabras, lograr un rango continuo de orden de jerarquía.

En esta nota veremos como hacerlo, pero primero daremos un repaso al tema de la función JERARQUIA.

Supongamos esta lista de valores




A los valores duplicados les hemos puesto un fondo de color de acuerdo al orden de aparición, para poder distinguirlos cuando ordenemos la lista de acuerdo al orden de jerarquía.

Aplicamos la fórmula =JERARQUIA(A2, $A$2:$A$11) a la celda B2 y la copiamos al resto del rango (hasta la celda B11)



En el rango E1:F11 mostramos la misma lista ordenada de acuerdo al resultado de JERARQUIA.
Podemos ver que los dos valores 7 reciben el mismo número de posición (2) y que no existe el número de posición 3. Lo mismo sucede con el valor 2 de la lista que también aparece dos veces.

Si queremos que cada valor en la lista reciba un orden de jerarquía único, aplicamos la fórmula

=JERARQUIA(A2, $A$2:$A$11)+CONTAR.SI($A2:A$11,A2)-1




Si queremos revertir el orden de aparición de los duplicados (los de fondo morado aparecerán antes de los de fondo turquesa), usamos la fórmula

=JERARQUIA(A2, $A$2:$A$11)+CONTAR.SI($A$2:$A$11,A2)-CONTAR.SI($A$2:A2,A2)



Si queremos que cada valor muestre su verdadero orden de jerarquía, pero que el rango de jerarquías sea continuo (sin "saltos") creamos primero una tabla corriente con la función JERARQUIA



Luego escribimos esta fórmula matricial en la celda C2

={SUMA((SI($B$2:$B$11<B2,1/CONTAR.SI($B$2:$B$11,$B$2:$B$11),0)))+1}

y la copiamos a lo largo del rango C2:C11



En la tabla E1:G11 podemos comparar los resultados de usar la función JERARQUIA y la fórmula matricial.

Una explicación sobre la fórmula, analizando el resultado de C2:

1 – La expresión $B$2:$B$11<B2 genera esta matriz

$B$2:$B$11<B2	Resultado
9<9	FALSO
7<9	VERDADERO
1<9	VERDADERO
5<9	VERDADERO
9<9	FALSO
2<9	VERDADERO
6<9	VERDADERO
7<9	VERDADERO
2<9	VERDADERO
4<9	VERDADERO

2 – La expresión 1/CONTAR.SI($B$2:$B$11,$B$2:$B$11) genera la siguiente matriz

0.5
0.5
1
1
0.5
0.5
1
0.5
0.5
1

El argumento si_es_falso genera una matriz de 0 (cero)

Finalmente sumamos el resultado de la función SI y le agregamos 1. Para la celda C2 el esquema de cálculo es el siguiente:



Referencias a esta nota: Tushar Metha – Excel




Categorías: Funciones&Formulas_, Manejo de Datos_, Formulas Matriciales_

Technorati Tags: Excel