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
Empezamos por crear los datos básicos de grafico (fechas y valores) y el gráfico
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
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
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
El segundo paso es cambiar el tipo de eje de las X de Automático a Categoría
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
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
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
# - En la pestaña Tramas señalamos “Ninguna” en la opción Marca de graduación principal
El resultado “casi” final es
# - 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
# - 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
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)
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
Con esto termina nuestra tarea
Technorati Tags: MS Excel
Que tal Jorge un Saludo desde Monterrey, Nuevo Leon, Mexico.
ResponderBorrarPor 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!!!
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).
ResponderBorrarHola 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.
ResponderBorrarsalud2.
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.
ResponderBorrarHola Jorge, ¿sabrías como se hace esto mismo pero para Excel 2010?
ResponderBorrarGracias!
Hola Gerardo, de la misma manera.
ResponderBorrarSe puede hacer lo mismo pero con fechas y horas del dia?
ResponderBorrarSi. 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.
ResponderBorraruna pasada, gracias por el comentario
ResponderBorrar