Función JERARQUIA en Excel - Rangos continuos

viernes, octubre 27, 2006

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í:

valorjerarquía
55
74
101
101
83


¿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 rank_contfunció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<B2Resultado
9<9FALSO
7<9VERDADERO
1<9VERDADERO
5<9VERDADERO
9<9FALSO
2<9VERDADERO
6<9VERDADERO
7<9VERDADERO
2<9VERDADERO
4<9VERDADERO


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:

16 comments:

Anónimo,  11 noviembre, 2006 19:37  

Recien he comenzado a visitar tu blog y la verdad es que hay temas interesantes de los cuales yo y seguramente mucha gente tambien desconocia, pero por ejemplo tengo un caso particular es que tengo que elegir al mejor arquero de un campeonato (10 equipos 20 arqueros en total,2 x equipo-titular y suplente)pero de los veinte que es la base de datos no todos juegan debido a que los suplentes esperan su oportunidad, entonces el problema radica en que tengo que seleccionar solo a los que estan jugando y dejar al final a los que todavia no han tenido su oportunidad de jugar, la calificacion que se les da a ellos es 1º numeros de goles recibidos, 2º partidos jugados y 3º promedio de goles recibidos(1º/2º), entonces ordenarlos con estos criterios, lo he intentado hacer con la función jerarquia pero considera todos los jugadores y los ordena en orden descendente es decir 20,19,18, etc (si considero como criterio la cantidad de goles recibidos) y quisiera que el que tiene menos sea el numero 1,luego el 2 y asi sucesivamente, espero me puedas ayudar, gracias por tu ayuda .

Jorge L. Dunkelman 11 noviembre, 2006 20:54  

Hola, gracias por leer el blog.
Me parece que no desconces que JERARQUIA tiene tres argumentos, el número evaluado, el rango de los números y el orden de jerarquía.
Este último argumento es opcional, y si no lo pones, Excel ordena la jerarquía de mayor a menor. Para obtener un orden de menor a mayor debes incluir un valor distinto de 0 para el tercer argumento.
Tu función debe verse así:
=JERARQUIA(arquero, lista de arqueros,2)

Anónimo,  12 noviembre, 2006 02:53  

ok, lo voy a intentar pero como hacer para no considerar a los arqueros que todavia no han jugado.

Jorge L. Dunkelman 12 noviembre, 2006 23:43  

Hola, la solución es un tanto compleja, así que en breve estaré publicando la solución a tu pregunta en el blog.

Anónimo,  15 noviembre, 2006 03:22  

Te pasaste, muchas gracias por tu ayuda, sabia que me podias ayudar.

Anónimo,  17 mayo, 2011 23:40  

Quiero aplicar la funcion jerarquia a celdas separasdas (no contiguas) me podrian dar alguna opcion???

Jorge L. Dunkelman 19 mayo, 2011 07:07  

La función JERARQUIA funciona también con celdas no contiguas. El único detalle a tener en cuenta es usar un nombre que se refiera al rango de referencia.
Para crear el nombre puedes seleccionar las celdas, apretando la tecla Ctrl para hacer la selcción múltiple, y luego poner el nombre en el cuadro de nombres.
Digamos que creamos el nombre "rngNoContinuo" que se refiere a las celdas A1,A5,A8,A14.
La fórmula sería
=JERARQUIA(A1,rngNoContinuo)

Anónimo,  29 agosto, 2011 15:21  

La función JERARQUIA también funciona en celdas discontinuas de una manera sencilla. (si queremos poner la jerarquia de A1 hasta A9 y añadir la jerarquia de la celda A29 pondremos el parámetro del rango entre paréntesis.
La jerarquia de la celda A3 en el rango de estudio sería

=JERARQUIA(A3;(A1:A9;A29))

Como saben el rango continuo se establece con los dos puntos y añadir una celda se establece con el punto y coma.

Diana Berenice Ramírez Camarillo 26 agosto, 2012 09:11  

hola, gracias por tú blog, es muy bueno, sin embargo quisiera aplicar el comando de JERARQUIA para ordenar datos de panel, ¿es esto posible?

Jorge L. Dunkelman 26 agosto, 2012 12:15  

Diana, ¿podrías ser un poco más explícita? ¿Que es "datos de panel"?

The Julk 29 enero, 2014 06:09  

Hola Jorge, estoy estudiando todo lo que puedo sobre ordenar listas pero no acabo de entender la explicación de la fórmula matricial de este post, en concreto el punto 1: $b$2:$b$11<B2. ¿De dónde sale los valores de la matriz 9,7,1,5... y que la segunda parte sea siempre constante el 9?. Gracias anticipadas

Jorge Dunkelman 29 enero, 2014 19:55  

Hola, este post tiene más de aiete años y para serte sincero, no recuerdo por qué puse en la explicación la serie 9,7,1,5.. cuando en el rango B2:B11 están los valores 10,8,1,6..Pero esto no altera la explicación tal como está en la nota. Hay que prestar atención que la fórmula es matricial (se introduce apretando simultáneamente Ctrl-Mayúscula-Enter).

Cristian Méndez 14 mayo, 2015 18:23  

porque para evitar el error de jerarquía vuelves a considerar la primera celta restando CONTAR.SI????

Jorge Dunkelman 14 mayo, 2015 18:57  

Cristian, no es un error sino una adaptación para que la función funcione distinto. COmo pongo en la nota, si un valor aparece dos veces en la lista ambos reciben la misma jerarquía. AL usar esta fórmula
=JERARQUIA(A2, $A$2:$A$11)+CONTAR.SI($A2:A$11,A2)-1
la expresión CONTAR.SI($A2:A$11,A2) da 1 en la primer instancia y 2 en la segunda. Al restar 1, la expresión CONTAR.SI($A2:A$11,A2)-1 da 0 en la primer aparición del valor y no altera el resultado de JERARQUIA; en la segunda aparición de 1 (2 -1) y suma 1 al valor de JERARQUIA.

Dennis Funez 12 enero, 2016 16:53  

Hola, como puedo hacer si tengo una lista por ejemplo con 3 columas: 1 Vendedor, Sucursal, articulos vendidos; y quiero que la jerarquia se vaya asignando por sucursal, o sea que cuando el vendedor sea de la sucursal 1 la jerarquia empiece a contabilizarlo y que se reinicie cuando encuentre un vendedor de otra sucursal? gracias de antemano, saludos

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP