domingo, agosto 28, 2011

Totales en gráficos de columnas apiladas

Sin lugar a dudas no es comparable a la invención de la rueda o de la pólvora, pero aquí les traigo un tip sobre como agregar automáticamente totales en gráficos de columnas apiladas en Excel.

Supongamos esta tabla de datos que muestra las ventas del año de las tres sucursales de una empresa imaginaria



Con los datos creamos este gráfico que muestra las ventas por mes



Hemos eliminado el eje de los valores (Y) y puesto etiquetas con los valores (en miles) en las porciones de las columnas. Lo que falta es una etiqueta para cada mes que muestre el total de las ventas.

Podemos, por supuesto, poner cuadros de texto sobre cada columna e inclusive ligarlos a los datos en la tabla. Pero queremos algo más automático.

Empezamos por seleccionar el rango F3:F14 (el total de ventas), lo copiamos y agregamos la serie al gráfico con Pegado Especial



Al hacer esto, Excel agrega la serie a las columnas apiladas deformando nuestro gráfico



Para solucionar esto relacionamos la nueva serie al eje de valores secundario, cambiamos el tipo de gráfico a “línea” y agregamos etiquetas



La línea es innecesaria y la hacemos desaparecer poniendo la opción “color de línea” a “sin línea”



Para que las etiquetas queda inmediatamente por encima de las columnas, usamos la opción “encima” de alineación de las etiquetas



Para quitar “serie 4” de la leyenda, lo seleccionamos y lo borramos (un clic para seleccionar la leyenda y un clic más para seleccionar la etiqueta “serie 4”)

Hacemos desaparecer los ejes de valores poniendo las opciones de marcas de graduación y etiquetas del eje a “ninguno”



Con esto terminamos. Podemos agregar un borde y sombra a las etiquetas del total


31 comentarios:

  1. Muchas gracias, llevaba un rato pelandome con otra cosa y me has dado una pista de como solucionarlo. ¿Sabes si es posible comparar gráficos de columnas apiladas con gráficos de columnas totales? Es decir, q el total en vez de salir en una línea, o con el númerito arriba salga en una columna a parte.

    ResponderBorrar
  2. No estoy seguro que pueda hacerse, ya que ambas series comparten el mismo eje X. Lo que podría hacerse es agregar la serie de los totales usando columnas en vez de línea y definiendo la serie sin relleno (pero dejando el borde). El total apareceería en el tope del rectángulo que representa el total del mes.

    ResponderBorrar
  3. El problema es q no estoy trabajando con totales. Lo que quería comparar es un valor estimado frente al valor real que se calcula como la suma de lo que ya se ha usado frente a lo que queda aún. Es decir, la columna del real está partida, y la del estimado es completa. En cualquier caso la solución de utilizar el gráfico de línea frente al de columnas apiladas también me permite comparar, aunque no sea exactamente lo que estaba buscando. Muchísimas gracias.

    ResponderBorrar
  4. ¿ Muy bueno ! Me ha sido muy útil. En un momento he solucionado el problema que tenía.

    ResponderBorrar
  5. Te felicito por todo! Siempre he ido leyendo libros de excel, y las cosas que he visto por aquí han sido muy interesantes.

    PREGUNTA: En el último caso, "Totales en gráficos de columnas apiladas", sería posible que por cada mes tuviéramos dos columnas. Me explico, suponer que las sucursales 1, 2 y 3 son de la zona 1, y que hubieran las sucursales 4, 5 y 6 de la zona 2, y por cada mes, tuviéramos las columnas apiladas zona 1 y zona 2.

    ResponderBorrar
  6. Se puede pero la explicación es un poco larga para ponerla en el marco de un comentario.
    Como alternativa, tal vez sería más efectivo construir dos gráficos, uno para cad zona, y poner uno debajo del otro.

    ResponderBorrar
  7. Ok! sabiendo que se puede ya lo investigaré un poco más. La idea de ponerlos uno al lado de otro, no lo acabo de ver claro ya que la intención era que por cada mes hubiera cuatro zonas con sus respectivas columnas! quizás me he pasado, pero lo voy a intentar!

    Gracias por todo!

    ResponderBorrar
  8. Yo igual, necesito poder comparar barras apiladas... alguien sabe cómo???

    ResponderBorrar
  9. Espero publicar algo sobre el tema en breve

    ResponderBorrar
  10. si tengo un grupo que representa mis ingresos (digamos dos cuentas principales) y tengo otro grupo que representan mis gastos (digamos con 4 cuentas principales) y me gustaría poner ingresos y gastos en un mismo gráfico, como lo hago? Osea quisiera que mi gráfico posea dos grupos de columnas apiladas

    ResponderBorrar
  11. Alguno de ustedes sabe si se pueden poner los totales sobre las columnas apiladas de un gráfico dinámico??

    ResponderBorrar
  12. No estoy seguro a que te refieres con gráficos dinámicos (todos los gráficos lo son ya que cambian al cambiar los datos que representan), pero sería con la misma técnica (ua columna con el total).

    ResponderBorrar
  13. ¿Un grafico de columnas apiladas con dos series? Interesante, trataré de encontrar una solución y publicarla.

    ResponderBorrar
  14. Je, me encantó esta solución.

    Desafortunadamente no me sirvió porque yo lo estaba necesitando para barras, no para columnas. Jugué con algunas cosas, que salieron mas o menos bien, pero con detalles que no servían.

    Pero como siempre, lo solucioné de forma alternativa:
    1. Al nombre de la categoría le incluí al final, una coma y el total de la categoría (con fórmula para que se actualice solo)
    2. Cree una nueva serie, con todos los datos en Cero (0)
    3. Modifiqué la etiqueta de dicha serie, para que no mostrara el valor, sino unicamente el nombre de la categoría
    4. Modifiqué el eje para que no mostrara el nombre de las categorías

    Y listo, al final de las barras, aparece el nombre de la categoría, y tras una coma el total

    ResponderBorrar
  15. buen dia, no entiendo muy bien la parte "Para solucionar esto relacionamos la nueva serie al eje de valores secundario, cambiamos el tipo de gráfico a “línea” y agregamos etiquetas", hasta aqui trate de seguir pero no me sale la linea. Tengo excel 2013 agradeceria mucho la retroalimentacion y gracias por compartir los conocimientos.

    ResponderBorrar
  16. La idea es agregamos una nueva serie cuyos puntos son los totales; ocultamos la línea y los marcadores y dejamos sólo los rótulos.
    Esta solución funciona en todas las versiones de Excel.

    ResponderBorrar
  17. Hola Muchas Gracias por la informacion de mucha ayuda

    ResponderBorrar
  18. Sabía que encontraría aquí algo similar a lo que estaba buscando. ¡Muchísimas gracias otra vez, Jorge!

    ResponderBorrar
  19. Hola que tal, te comento mi situación: tengo datos sobre marcas de producto y cuanto peso tienen en cada región. hice un grafico de columnas apra ver esto, pero además quiero ver como puedo hacer que una de las columnas sea el total de peso nacional (suma de todas las regiones).

    Saludos!

    ResponderBorrar
  20. Tienes que crear una nueva serie con el total e incluirla en el gráfico.

    ResponderBorrar
  21. Lo cual es excelente, ya que hay que evitar el uso de las gráficas "3D". Las únicas gráficas realmente 3D son las que tienen 3 ejes (X, Y y Z) que no es el caso de columnas apiladas.
    El asunto es que no se pueden combinar gráficos "2D" con gráficos "3D". Pero lo podés hacer es agregar la serie, agregar las etiquetas y luego quitar el relleno y las líneas de la serie para volverla "invisible". De esa manera se verán sólo las etiquetas de los valores del total.

    ResponderBorrar
  22. Hola!
    Sabes alguna forma para hacer un Top 10 o Top 20 de una forma automática? solo lo he visto con filtros en los gráficos dinámicos, no en los gráficos normales...
    Gracias!

    ResponderBorrar
  23. Hola Juán,
    no me qued claro qué querés decir con "automáica". En los gráficos normales las series tán definidas en rangos de datos. Podrías crear un evento para ordenar los valores de la serie de mayor a menor cada vez que hay algún cambio en la hoja y definir la serie para los primeros 10 o 20 valores.

    ResponderBorrar
  24. Este comentario ha sido eliminado por el autor.

    ResponderBorrar
  25. Este comentario ha sido eliminado por el autor.

    ResponderBorrar
  26. Pablo, esta nota trata sobre un gráfico de columnas apiladas con tre series.

    ResponderBorrar

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