Ordenando rangos por jerarquía con Excel – Reseña

jueves, mayo 08, 2008

La función JERARQUIA de Excel calcula la posición relativa de un valor dentro de una lista. En notas anteriores hemos mostrado como usar esta función, sus limitaciones y algunas formas de superarlas.
En la nota Construir con Excel una tabla con las 10 primeras posiciones mostramos como superar el problema el "empate". Allí dimos el ejemplo de esta lista de calificaciones de alumnos



En la columna C usamos la función JERARQUIA para determinar la posición de cada alumno de acuerdo a la calificación. Allí vemos que Perla y Cristina, que tienen la misma calificación, obtienen el mismo número de rango (1) y Carlos recibe el número 3. Para solucionar esta situación usamos la función JERARQUIA combinada con CONTAR.SI para "desempatar" entre alumnos con la misma calificación. La fórmula en la columna D es

=JERARQUIA(B2,puntaje)+CONTAR.SI($B$2:B2,B2)-1


Un lector me pregunta como hacer para que el alumno Carlos, que tiene la segunda mejor nota, aparezca con el número 2 ya que ocupa el segundo lugar y no con el número tres. Es decir, que cada alumno aparezca con el numero de posición que ocupa.
Para lograr esto tendremos que usar una fórmula desarrollada por Tushar Mehta. En nuestra tabla hemos agregado la columna D, que muestra el resultado deseado



La fórmula en la columna D es

={SUMA(1/SI($C$2:$C$25<C2,CONTAR.SI($C$2:C25,$C$2:$C$25),9.999999999E+307))+1}

Esta es una fórmula matricial. . Primero la introducimos la fórmula en la celda D2 pulsando CTRL+MAYUSCULAS +ENTER simultáneamente. Luego la copiamos al esto del rango (hasta D25 e nuestro ejemplo).
Algunos detalles a tomar en cuenta en esta fórmula:

# - La fórmula usa la columna C como columna auxiliar. Esta columna contiene la posición de cada alumno calculada con la función JERARQUIA.

# - La expresión CONTAR.SI($C$2:C25,$C$2:$C$25) calcula cuantas veces aparece un determinado valor en el rango, sólo si no se trata de la primera posición. Para determinar si el valor evaluado no es el de la primera posición, usamos la expresión $C$2:$C$25<C4.

# - La segunda parte la función SI, 9.999999999E+307 es un número expresado en forma exponencial y es el mayor número que Excel puede aceptar. Esto es necesario para evitar la división por cero cuando evaluamos el valor del primer puesto. La operación 1/9.999999999E+307 da como resultado 0.

Para ver como funciona esta fórmula, lo mejor es usar el botón Evaluar de la barra de auditoría de fórmulas.



Por ejemplo, seleccionamos la celda D5 y apretamos el botón Evaluar fórmula



Apretamos el botón Evaluar del diálogo y vemos la expresión que va a ser evaluada



Al volver a apretar Evaluar vemos el resultado de la operación



En nuestro caso, se genera una matriz con 4 resultados VERDADERO y el resto FALSO.
En el próximo paso, Evaluar nos muestra la matriz generada por CONTAR.SI



Ahora vemos que el resultado de CONTAR.SI es la matriz {2,2,1,1,1,9,99..9E+07…}



Al dividir los miembros de esta matriz por 1 obtenemos



Y al sumar la nueva matriz, nos da



Es decir, 4, la posición del valor en la lista.



Technorati Tags:

13 comments:

Francesc Prats,  16 mayo, 2008 23:13  

brutal Jorge. brutal.

eres el amo.

Tengo una pregunta, tengo una matriz, y en ella quiero buscar el valor más proximo, por encima, a otro número que tengo en una celda distinta (fruto de otro proceso de calculo). y una vez tengo el número de la matriz, quiero que me diga a que fila y columna corresponde. cómo puedo hacerlo?

muchas gracias

Jorge L. Dunkelman 17 mayo, 2008 01:09  

Hola Francesc

fíjate si esta nota te ayuda.

Anónimo,  23 mayo, 2008 19:56  

Efectivamente Jorge, es lo que buscaba. gracias de nuevo.

Saludos.

Francesc Prats.

Ángel,  27 mayo, 2008 21:21  

Hola Jorge, necesito reflejar en la última celda de una columna el último valor escrito en dicha columna.
Gracias de antemano
Ángel

Jorge L. Dunkelman 07 junio, 2008 23:14  

Hola Ángel y mis disculpas por la demora.

Fijate en esta nota

ängel,  12 junio, 2008 20:33  

Muchas gracias Jorge, me has resuelto el problema. Si no es molestia volveré a utilizar tu bloc para próximas dudas.
Saludos
Ángel

Ángel,  20 junio, 2008 17:22  

Hola de nuevo. Con la opción BUSCAR(LOOKUP) resuelví el problema anterior pero, no funciona si el contenido de la columna está en formato texto. ¿Cómo puedo hacer para que me devuelva lo último que he escrito en un rango si es texto?
Muchas gracias
Ángel

Edwin Arnaldo 29 enero, 2009 18:41  

Hola Jorge, Un millon de gracias por su inavaluable aporte al uso eficiente de Excel...

Tengo un pequeño comentario, el cual espero sea correcto:

1. Borramos la columna C y D
2. En la celda D2 hacemos una copia de la celda C2, la cual siempre tendria que ser "UNO"
3. En la celda D3:D25 copiamos la siguiente formula

=IF((COUNTIF($C$2:$C3,C3)=1)*1=0,D2,D2+1)

y obtendremos unresulatdo similar.

Espero estar en lo corecto y saludes a todos

Anónimo,  07 abril, 2009 09:11  

Buenos días.
Me llamo Lorenzo Cotrina y tengo una duda. Me gustaría hacer una clasificación obtenida por dos datos; (puntos y dinero) y así en caso de empate de puntos, que quede por encima el que más dinero posea.
Si fuera tan amable de guiarme un poco, gracias.
Atentamente:
Lorenzo Cotrina
lorenzocotrina@gmail.com

Jorge L. Dunkelman 07 abril, 2009 20:22  

Tendrías que ser un poco más explícito. ¿Cómo se obtienen los puntos y cómo el dinero?

Anónimo,  17 junio, 2009 04:15  

hola jorge..

esta muy interesante tu blog...
una duda..
sera posible sacar jerarquias basandose en dos referencias..

ejemplo: tengo empleados los cuales debo jerarquizar por su sueldo diario y su antiguedad en dias..

juan garcia... 250.00....365 dias
manuel torres..250.00....366 dias

manuel torres debe ser el #1 porque tiene un dia mas de antiguedad ( tienen el mismo sueldo..)

de que forma se puede usar la funcion jerarquia para dar el orden jerarquico a una base de datos de empleados con datos como los del ejemplo..

Jorge L. Dunkelman 17 junio, 2009 18:50  

La técnica más sencilla es combinar ambos valores en una columna auxiliar y aplicar JERARQUIA a esa columna.
Pero antes hay que establecer que valor prevalece, el sueldo o la antigüedad?
En tu ejemplo si sumamos el sueldo y la antigüedad Manuel Torres tiene un indicar de 616 (250+366) y Juan García 615. Así que Torres va delante de García. Pero qué pasa si el sueldo de Torres es 248?
Es decir, hay que ponderar el sueldo y la antigüedad antes de efectuar el cálculo.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP