JLD Excel – balance del sexto año y un gráfico interesante

sábado, diciembre 29, 2012

Este blog cumple su sexto año de existencia. A lo largo de los años el número de lectores ha ido creciendo lo cual me llena de satisfacción. 2012 ha sido el año record en visitas y páginas vistas, más de 1,5 millón y más de 2.3 millones respectivamente.

Buscando como representar las estadísticas encontré en el excelente blog de Chandoo una nota sobre sobre la representación gráfica del precipicio fiscal de los Estados Unidos. Basándome en esa nota y en una posterior sobre un tema similar, he desarrollado este gráfico que muestra dinámicamente el número de páginas vistas en JLD Excel en los años 2007-2012



Este tipo de gráfico, permite una rápida comparación entre los años y ver cómo se ha ido desarrollando la popularidad del blog. La barra de desplazamiento permite señalar cada mes en particular, mostrando al mismo tiempo el número de páginas vistas.

Para crear este gráfico empecé por descargar la información sobre el número de visitantes y páginas vistas en el blog que llevo en el sitio StatCounter



Como puede apreciarse se tratan de datos diarios. Para poder crear nuestro gráfico tenemos que transformar estos datos a datos mensuales y anuales. Como ya habrán intuido la forma más eficiente de hacerlo es con tablas dinámicas



Ahora copiamos todo el contenido de la hoja y usamos Pegar Especial-Valores, para cancelar la tabla dinámica dejando sólo los valores.

El próximo paso es separar con una fila en blanco los años (para crear la separación en el gráfico) y agregar una serie de datos adicional en la columna D con esta fórmula

=SI(B2=$G$1,C2,NOD())



La celda $G$1 contiene el nombre del mes vinculado al valor de la barra de desplazamiento e identifica el mes a señalar (en la imagen el mes elegido es diciembre, por eso la celda D13 exhibe el valor de ese mes y el resto aparece con el valor #N#A; este valor de error no es representado en los gráficos).

Creamos un gráfico de área donde los valores del eje X (categorías) están en las columnas Ay B; los valores del área de cada año en la columna C y los valores del mes elegido en la barra de desplazamiento en la columna D.

Los valores de la serie de la columna D están representados con un gráfico de líneas.

La línea vertical que une el punto al eje de la X es una barra de error definida de esta manera



La barra de desplazamiento está ligada a la a la celda F2 en la hoja “motor”. Esta hoja contiene todos los datos del gráfico y todos los cálculos necesarios



Para calcular los acumulados en forma dinámica, tal como aparecen en el rango G5:G10 de la hoja “motor”, usamos esta fórmula

=SUMA(DESREF(INDICE($C$2:$C$78,COINCIDIR(F5,$A$2:$A$78,0)),,0,$F$2))

Finalmente creamos un cuadro de texto para cada año en el gráfico y lo ligamos a la celda con el acumulado



Este tipo de gráfico puede aplicarse a un sinnúmero de situaciones (ventas, eficiencia de servicios, etc.).

El archivo puede descargarse aquí.

Mis mejores de deseos de salud, paz y prosperidad en este nuevo año para todos mis lectores.

26 comments:

AlbertoMac 29 diciembre, 2012 19:09  

yo por ejemplo te tengo en mis RSS's en mi Outlook! esas cuentan?

Palel 29 diciembre, 2012 21:55  

¡Fantástico!
Solo una anotación, dices: "La celda $G$1 contiene el nombre del mes vinculado..." cuando en realidad es la celda $D$1

Feliz 2013

Jorge L. Dunkelman 30 diciembre, 2012 07:24  

Alberto,
no, creo que sólo cuentan las entradas a la página.

Jorge L. Dunkelman 30 diciembre, 2012 07:26  

Palel,
en el archividé de mover el nombre del mes a la celda G1, como puede verse en la imagen. Feliz año.

Dave 30 diciembre, 2012 20:17  

Hola Jorge. Primero que nada deserte felices fiestas y un venturoso 2013 lleno de trabajo, salud y el amor de los tuyos.
Jorge, para consultarte si es posible realizar en excel, un gráfico con tres o mas Ejes de "Y" en el cual se puedan mostrar primero el comportamiento mensual de cuatro o mas variables. Por ejemplo cada eje de "Y" corresponderia al año 1, año 2, año 3.... la idea es ver visualizar por ejemplo el comportamiento de las ventas mensuales por producto 1, producto 2, producto 3, producto 4

Juan Gabriel 31 diciembre, 2012 07:04  

¡Muchas gracias! ¿Para entender bien cómo realizar el gráfico debo recurrir a la página de Chandoo.org?
Me gustaria aprender más sobre la función DESREF pues no comprendo bien su uso, ¿ha publicado algún artículo sobre dicha función específica?

Juan Gabriel 31 diciembre, 2012 07:11  

¡Muchas gracias! ¿Para entender bien cómo realizar el gráfico debo recurrir a la página de Chandoo.org?
Me gustaria aprender más sobre la función DESREF pues no comprendo bien su uso, ¿ha publicado algún artículo sobre dicha función específica?

Jorge L. Dunkelman 31 diciembre, 2012 07:33  

Juan Gabriel,

la mejor forma de estudiar el gráfico es descargar el archivo y analizar como está hecho. En el blog hay varias notas donde muestro el uso de la función DESREF. Para ver todas las notas se puede apretar la etiqueta DESREF en la nube de etiquetas (en la parte superior de la plantilla). Te sugiero empezar por esta.

Jorge L. Dunkelman 31 diciembre, 2012 07:43  

Dave,
Excel permite crear gráficos con dos ejes Y (principal y secundario). Pero de tu descripción creo que podrías usar el gráfico que explico en esta nota o usar algunas de las técnicas para crear gráficos dinámicos, como por ejemplo esta o también esta.

salakazam 31 diciembre, 2012 12:29  

Muchas gracias por toda la labor que desarrollas en tu blog Jorge, es con diferencia una de los más útiles sites en Español sobre Excel que he encontrado. Y no porque no hayan otros, sino principalmente por tu compromiso y amabilidad al contestar dudas e incluso dar soluciones concretas a todos aquellos que, en alguna ocasión, nos hemos tomado la libertad de preguntar o pedir.
Te deseo una muy buena nochevieja y un mucho más feliz año nuevo.
Un saludo.

Juan Gabriel 01 enero, 2013 22:17  

¡Muchas gracias Jorge por tu atenta respuesta sobre la función DESREF! Voy a leer atentamente el artículo para entender mejor. Sería interesante que puedas desarrollar un artículo entero sobre cómo realizar paso a paso el gráfico, de ser posible. Me considero inexperto total en el tema de gráficos avanzados y me ayudaría mucho para poder aprender más. Me gustaría que me recomiendes tus posts anteriores para aprender más sobre el tema. Muchisimas gracias por todo y por crear este sitio tan espectacular, valoro tanto tu gentileza en compartir tus conocimientos sin egoísmos. Un cordial saludo desde Paraguay, ¡feliz y exitoso año 2013 lleno de paz, salud y buenos deseos!

Juan Gabriel 01 enero, 2013 22:29  

Estuve explorando el gráfico pero no sé ni por donde empezar, es un tema muy oscuro para mí por falta de conocimiento, ¿en qué consisten las barras de error? ¿cómo se logra esa línea vertical que marca cuando un mes está seleccionado? Al examinar la hoja de motor, he comprendido mejor las fórmulas pero cuando veo el gráfico me siento totalmente perdido, ni que decir al ver la barra de desplazamiento. Esperaré tus recomendaciones sobre tus artículos para crear gráficos, me serían de gran ayuda para el trabajo diario que realizo. ¡Muchísimas gracias de antemano, Jorge!

Jorge L. Dunkelman 02 enero, 2013 06:48  

Podés ver todas las notas sobre gráficos en el blog pulsando la etiqueta Gráficos en la nube de etiquetas (en la parte superior de la plantilla).
La barra de desplazamiento es un control.
También podés ver mi blog sobre gráficos.

Javi,  02 enero, 2013 13:26  

Muchas gracias por esta nota. Interesantísima a la par que útil, como todas.

Estoy trabajando sobre el archivo que has puesto para descargar y, para adaptarlo a lo que necesito, sería ideal que los meses estuvieran en otro idioma (catalán, concretamente), pero no logro hacerlo ni cambiando la configuración regional ni cambiando el formato de las celdas. ¿Se puede hacer lo que intento?
No hace falta que sea a catalán, si se puede cambiar a otro idioma, luego intento adaptarlo al que necesito.

Mil gracias. Reitero las felicitaciones.

Jorge L. Dunkelman 02 enero, 2013 18:20  

Javi,
se puede hacer con facilidad. En la hoja "motor" hay que reemplazar los nombres de los mese en la columna B por los correspondientes en catalán (o cualquier otro idioma). Las etiquetas del eje son valores de texto, no fechas (por eso el cambio de formato no produce ningún cambio).

Juan Gabriel 03 enero, 2013 03:17  

¿Como se hacen las líneas verticales mas pequeñas para separar los meses en el eje horizontal y las mas largas para separar los años? Muchísimas gracias Jorge por el blog de graficos,voy a visitar,que genial!

Javi,  03 enero, 2013 08:54  

Hola, Jorge,

muchas gracias por la respuesta.

Ciertamente, esa es la manera más rápida de conseguir lo que busco y con eso ya soluciono el entuerto.

Sin embargo, yo estaba probando de hacerlo directamente cuando le damos a Agrupar por meses y años en la tabla dinámica. En teoría, ahí sí que podría formatear los datos, ¿no? ¿O como el que agrupa es Excel directamente lo hace en Inglés y coge la configuración regional del sistema para 'traducirlo'?

Mil gracias por tu ayuda y dedicación.

Jorge L. Dunkelman 03 enero, 2013 10:47  

Juan Gabriel,
Excel lo hace por defecto, pero su pueden cambiar las definiciones en el enú de formato del eje.

Jorge L. Dunkelman 03 enero, 2013 10:52  

Javi,
Excel usa las definiciones regionales del Windows. En el Control Panel-Region and Language en la pestaña Formats puedes intentar cambiar a Catalán.

Javi,  03 enero, 2013 14:12  

Hola, Jorge,

efectivamente, si cambio la configuración regional y de idioma desde el panel de control de windows, al Agrupar la tabla dinámica los meses salen en catalán.

¡Mil gracias por tu ayuda!

magdajada 21 enero, 2013 01:46  

¡Hola, Jorge!
No estoy acostumbrada a trabajar con gráficos, por eso me costó un poco desentrañar la parte de los diferentes gráficos para cada serie y la parte de las barras de error pero al final, he conseguido entenderlo todo.

Muchas gracias por esta entrada en particular y por el blog en general.
Seguro que en los próximos años, la línea de tendencia de ese gráfico sigue al alza :)

Camellito 16 junio, 2013 17:25  

Hola Jorge. Me alegro de saludarte.
Estoy intentando descargar el archivo y no funciona el link.
Un saludo y muchas gracias.

Jorge Dunkelman 16 junio, 2013 18:47  

Acabo de descubrir que Google suspendió temporariamente mi sitio en Google Sites. Acabo de mandar una apelación y supongo que la cosa se arreglará en cuestión de días. Ya tuve una situación similar en el pasado, cosas que pasan cuando el control lo hace una máquina y no un ser humano.
Mientras tanto, podés mandarme tu dirección de mail (fijate en el enlace Ayuda en la parte superior de la plantilla) y te mandaré los archivos por mail privado.

Ricky 07 diciembre, 2013 00:32  

Jorge, gracias por tus publicaciones ...
Me gustaría consultarte cómo has hecho el grafico. Yo ya hice la tabla dinamica a partir de los datos de la hoja "Datos". Luego copie y pegue como valores en otra hoja separando los datos con una fila en blanco al comenzar el siguiente año. Despues en la columna D, realice la formula que dijistes =SI(B2=$G$1;C2;NOD()) pero a partir de ahi me pierdo... no se como se realiza un grafico de areas, como seleccionar para tomar en cuenta los datos, etc. No se si antes de insertar el grafico ya tienen que estar escritos los datos y formulas que tu tienes en el archivo de ejemplo enla hoja Motor (rango: F1:i12). Como realizo el grafico? como le añado despues esa barra de desplazamiento? podrias darme mayores detalles por favor, no pude avanzar mas en este ejercicio.. de nuevo muchas gracias por tu ayuda.

Jorge Dunkelman 07 diciembre, 2013 09:43  

Hola Ricky, en la nota hay un enlace para descargar el archivo. La mejor forma de aprender las técnicas es analizar como está construido. En cuanto a la barra de desplazamiento, en el blog hay varias notas sobre el tema. Puedes hacer un clic en la etiqueta "controles" en la nube de etiquetas para ver las notas.

Juan Manuel Jaime Tobón 10 enero, 2014 00:35  

Jorge, quiero que sepas que tu blog es una maravilla. Felicitaciones por tu trabajo con esto!

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP