miércoles, enero 30, 2008

Orden automático de series en gráficos en Excel.

En una hoja tenemos una tabla cuyos valores están ligados a celdas de una tabla remota (que está en otra hoja o cuaderno Excel). Los valores de la tabla del gráfico se actualizan a medida que los datos de la tabla de origen van cambiando.
Nuestro problema es ordenar los valores de la tabla del gráfico en forma automática. Es decir, sin tener que recurrir cada vez a Datos--Ordenar.
En mi blog sobre Excel he mostrado esta técnica en varias oportunidades. Aquí mostraremos una aplicación relacionada a gráficos.

Supongamos esta tabla, que será nuestra tabla remota (el origen de los valores)



En otra hoja (o cuaderno) tenemos otra tabla, cuyos valores están ligados a la tabla de origen. Si creamos un gráfico, el orden de los puntos de la serie será el de la tabla de origen



Para la claridad del ejemplo he puesto las tablas en la misma hoja.

Si creamos un gráfico con los datos de de la Tabla Principal, obtenemos



Nosotros queremos este gráfico



donde los puntos de la serie están ordenados de menor a mayor.

La técnica para hacerlo es la siguiente:

1 – Agregamos una columna a la Tabla Principal con la fórmula

=JERARQUIA(F3;$F$3:$F$7;1)+CONTAR.SI($F$3:F3;F3)-1

La función JERARQUIA da un número de orden a cada miembro de la serie de acuerdo a su posición relativa (de acuerdo al valor). Para crear una serie ascendiente usamos el tercer argumento de la función con el valor 1. La función CONTAR.SI permite "desempatar" en caso que dos puntos tengan el mismo valor.



2 – Creamos una Tabla Auxiliar donde usamos las funciones INDICE y COINCIDIR para crear una fórmula que "ordene" los valores y los nombres de acuerdo al resultado de la función JERARQUIA



La fórmula es

=INDICE($E$3:$F$7;COINCIDIR(FILA()-13;$G$3:$G$7;0);2)

Usamos FILA()-13 para obtener el número de orden de acuerdo a la fila en la cual nos encontramos (FILA()-13 en nuestro ejemplo da 1). También se pueden usar números enteros (1,2, etc.)

El resultado es una serie ordenada



Los valores en la tabla de origen son producidos por la función ALEATORIO. Cada vez que pulsen el botón Recalcular Valores, estos cambiarán.

El archivo con el ejemplo se puede descargar aquí.

9 comentarios:

Anónimo dijo...

interesante!.
no se si conoscas que hay una herramienta especial para hacer campanas de gauss en excel sin la nesecidad de insertar un grafico simplemente se les da los datos y le damos en la herramienta y listo nos genera la campana de gauss. el problema es que no se que herramienta es tu sabes?

Jorge Dunkelman dijo...

Desde mi época de estudiante de economía que no me he visto en la necesidad de crear una campana de Gauss. Y eso fue hace mucho tiempo...
No conozco la herramienta, pero el tema parece interesante y tal vez sea útil a algunos de mis lectores. Trataré el tema en una futura nota.

MeteOritO dijo...

Esta Muy Bien esta Funcion.Necesitaria saber como puedo conseguir en una celda el maximo valor y en otra celda el nombre que hacer referencia al valor maximo.
explicado utilizando el Mismo ejemplo.
Gracias.

Jorge Dunkelman dijo...

Hola

usando la función MAX. En nuestro ejemplo =MAX(B3:B7) da como resultado 86. Luego usando INDICe y COINCIDIR puedes encontrar el valor correspondiente en el rango A3:A7

Anónimo dijo...

Hola Jorge, muy buenos días, soy Karlomagno. Te escribo para que me des una clase de excel (jajaja); porque he notado que tus respuestas son muy buenas, de verdad.

Yo quiero saber: ¿cómo hago para colocar una celda que arroje un resultado en función de otra? es decir; yo tengo un programa de ejercicios en excel, y cuando coloco mi peso y los minutos de ejercicio que hago a diario, me sale el resultado automático del gasto calórico que hice ese día.

YO quiero poner esa misma fórmula a mis promedios de nota de la universidad, pues me ahoarraría mucho tiempo, a parte de incremento en la eficiencia de mis progresos.

De antemano muchas gracias, un cordial saludo para todos los de este blog. Muy bueno

Jorge Dunkelman dijo...

Hola Karlomgano
tu consulta no está relacionada con el tema de la nota. De todas maneras no me queda claro que es lo que quieres hacer. Puedes conultarme en forma privada a través del mail.

Anónimo dijo...

Hola.
Soy un estudiante de secundaria. Es muy interesante el artículo

Me gustaria saber si hay alguna forma de hacer que una lista se ordene de manera descendiente automaticamente, sin necesidad de estar entrando al menú y sin necesida de realizar una tabla.

Me sería muy útil, lo necesito para un proyecto. Agradecería mucho la ayuda.
Te agradezco de antemano

Jorge Dunkelman dijo...

Hola

perdón por la demora en responder. En la fórmula
=JERARQUIA(F3;$F$3:$F$7;1)
reemplaza el 1 por 2
=JERARQUIA(F3;$F$3:$F$7;2)

Liz Castillo dijo...

Excelente Post! busqué por todos lados y este fue el mejor ejemplo Muchas Gracias por compartir la info :) Saludos!