Gráfico Excel con fecha.

jueves, septiembre 17, 2009

Un lector me consulta cómo hacer este gráfico en Excel


Gráfico Excel con fecha.

Como pueden ver el eje de las X (categorías en Excel) es una escala de tiempo. Lo particular en este gráfico es que en el eje de las X sólo aparecen las fechas de los puntos de la serie de valores.
Crear este gráfico en Excel no es trivial y es lo que mostraremos en esta nota.

El resultado final será el siguiente

Gráfico Excel con fecha.

Empezamos por crear los datos básicos de grafico (fechas y valores) y el gráfico



Gráfico Excel con fecha.


Gráfico Excel con fecha.

Como puede observarse aparecen en el gráfico de las X valores que no figuran explícitamente en los datos de origen (en la columna A). Esto se debe a que Excel reconoce los datos en la columna A como fechas. Si nos fijamos en las definiciones del eje de la X

Gráfico Excel con fecha.

vemos que el valor mínimo es la primer fecha en el rango, el valor máximo es el último valor en el rango y la unidad principal es un mes.


Dado que las fechas en los datos no se distribuyen en intervalos regulares, no podemos crear el efecto deseado. No tenemos forma de ocultar las fechas para las cuales no hay datos. Para lograr el efecto deseado tendremos que transformar los datos.


Empezamos por crear datos auxiliares en las columnas D y E

Gráfico Excel con fecha.

En la columna D ponemos todas fechas, día por día, comprendidas entre la primer y la última fecha. En la columna E ponemos la fórmula

=BUSCARV(D3,$A$2:$B$10,2,0)

Esta forma pone el dato original en la fecha o #N/A si para la fecha no hay datos. Ahora cambiamos los datos en el gráfico por los del rango D1:E333

Gráfico Excel con fecha.

El segundo paso es cambiar el tipo de eje de las X de Automático a Categoría

Gráfico Excel con fecha.

Ahora tenemos que realizar una segunda transformación a los datos. En nuestro ejemplo creamos un rango de datos en las columnas G y H. En la columna G ponemos la fórmula


=SI(ESNUMERO(E2),D2,"")


y en la columna H sencillamente creamos una referencia a los datos en la columna E

Gráfico Excel con fecha.

El resultado es que en los puntos en los que no hay valores, la fecha no aparece.

Ahora podemos usar esta serie de datos en el gráfico, con este resultado

Gráfico Excel con fecha.

Como puede verse todavía no hemos llegado al resultado final. Nuestros próximos retoques son los siguientes:


# - abrimos el menú de formato del Eje de las X y en la pestaña Escala ponemos el valor 1 en la casilla de “Número de categorías entre rótulos de marcas de graduación



Gráfico Excel con fecha.

# - En la pestaña Tramas señalamos “Ninguna” en la opción Marca de graduación principal



Gráfico Excel con fecha.

El resultado “casi” final es



Gráfico Excel con fecha.

# - No queremos que el primer punto de la serie esté sobre el eje de la Y, para lo cual agregamos líneas en blanco al principio del rango y modificamos los datos de origen del gráfico de acuerdo



Gráfico Excel con fecha.

# - Para crear el efecto de retícula, cambiamos la trama de las líneas de división y definimos barras de error para los puntos de la serie.

Este paso implica cambiar la definición de la escala del eje de las Y a valores fijos, quitando las marcas de Automático de Mínimo y Máximo

Gráfico Excel con fecha.

Abrimos el menú formato de serie de datos activamos la pestaña Barras de Error Y, elegimos la opción Ambas y en Valor Fijo ponemos el máximo del eje de las Y (400 en nuestro ejemplo)

Gráfico Excel con fecha.

Finalmente seleccionamos las barras de error y abrimos el menú de formato. En la pestaña tramas elegimos la trama punteada para la línea y en Marcador la opción a la derecha

Gráfico Excel con fecha.

Con esto termina nuestra tarea

Gráfico Excel con fecha.




Technorati Tags:

9 comments:

carlos 18 septiembre, 2009 23:53  

Que tal Jorge un Saludo desde Monterrey, Nuevo Leon, Mexico.
Por medio de tu pagina me gustaria que se advirtiera a los usuarios que te visitan, que Excel 2007 tiene un Bug, esto lo encontre navegando en algunas paginas; bueno el problema es que cuando multiplicas "850 * 77.10" el resultado es "100000" y en realidad deberia ser "65535" (¿¿¿ tendra algo que ver con la candidad de filas de Excel 2003 ???), como podras darte cuenta es un gran problema el que se presenta.
Ahora bien,la solucion a este problema la encontre en esta pagina http://blogs.msdn.com/excel/archive/2007/10/09/calculation-issue-update-fix-available.aspx

que te permite baja un "parche" que Microsoft desarrollo para la solucion a esta problematica.

Bueno Jorge espero poder aportar algo a tu blog para que los usuarios tengan cuidado con este problema, pero que tambien lo puedan resolver con el link que te comento.

Saludos Jorge!!!

Jorge L. Dunkelman 19 septiembre, 2009 10:16  

Gracias por aporte Carlos. Sólo queda por aclarar que el problema consistía en el número exhibido en la pantalla, no en el resultado que quedaba guardado en la memoria. Si se usaba el resultado "incorrecto" como parte de un segundo cálculo, este último resultado era correcto (con algunas excepciones como el uso de la función REDONDEAR).

Hugo Lozano 24 enero, 2015 14:52  

Hola Jorge, cuando ingreso en un gráfico fechas lo que me ocurre es que excel me coge un formato que no corresponde al rango de fechas. Es decir, 1/1/2015 pues bien lo que aparece es 1/1/1900 no lo entiendo.
salud2.

Jorge Dunkelman 24 enero, 2015 19:14  

Lo más probable es que las fechas sean texto que parecen fechas. Puedes hacer la prueba usando la función ESNUMERO para determinarlo (las fechas son números) o que no hayas definido el rango de valores del eje de las X (categoría). En ese caso Excel poner por defecto los valores 1, 2,...etc. Al tener formato de fechas el 1 es exhibido como 01/01/1900, el 2 como 02/01/1900 etc.

Gerardo Mg 16 febrero, 2015 14:17  

Hola Jorge, ¿sabrías como se hace esto mismo pero para Excel 2010?

Gracias!

Jorge Dunkelman 16 febrero, 2015 19:32  

Hola Gerardo, de la misma manera.

Edgardo Ortega Yacante 31 agosto, 2015 16:57  

Se puede hacer lo mismo pero con fechas y horas del dia?

Jorge Dunkelman 31 agosto, 2015 17:52  

Si. En Excel tanto fechas como horas son números (como está explicado en esta nota). La fecha es un número entero y la hora asociada a esa fecha es la parte decimal del número. Así que aplicamos la misma técnica.

Hugo Lozano 01 septiembre, 2015 17:43  

una pasada, gracias por el comentario

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP