sábado, diciembre 29, 2012

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

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 comentarios:

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

    ResponderBorrar
  2. ¡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

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

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

    ResponderBorrar
  5. 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

    ResponderBorrar
  6. ¡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?

    ResponderBorrar
  7. ¡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?

    ResponderBorrar
  8. 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.

    ResponderBorrar
  9. 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.

    ResponderBorrar
  10. 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.

    ResponderBorrar
  11. ¡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!

    ResponderBorrar
  12. 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!

    ResponderBorrar
  13. 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.

    ResponderBorrar
  14. 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.

    ResponderBorrar
  15. 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).

    ResponderBorrar
  16. ¿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!

    ResponderBorrar
  17. 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.

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

    ResponderBorrar
  19. 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.

    ResponderBorrar
  20. 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!

    ResponderBorrar
  21. ¡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 :)

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

    ResponderBorrar
  23. 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.

    ResponderBorrar
  24. 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.

    ResponderBorrar
  25. 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.

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

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.