Por ejemplo, si tenemos esta tabla de ventas
con unos pocos clics creamos este gráfico
Ahora supongamos que queremos agregar a nuestro gráfico otra serie de datos, otra dimensión, la cantidad de unidades vendidas
Una posibilidad interesante es representar ambas series de datos en un gráfico de columnas de manera que la altura de cada columna represente el volumen de ventas y el ancho de cada columna represente la cantidad de unidades vendidas.
La idea es crear este gráfico
Excel no tiene un tipo de gráfico de columnas con ancho variable, pero podemos lograrlo usando algunos trucos.
La idea central es representar cada serie con una cantidad de columnas proporcional a la cantidad de unidades vendidas de cada producto en relación al total de unidades vendidas de todos los productos.
En nuestro ejemplo, determinamos que la cantidad total de columnas en el gráfico es 100. La cantidad de unidades vendidas del producto 1 es 5000. El total de unidades vendidas es 19000. Por lo tanto la cantidad de unidades del producto 1 estará representada por 26 columnas (5000/19000 *100). La altura de las 26 columnas será la misma y estará determinada por el monto de ventas del producto.
De esta manera, cada producto estará representado por un número variable de columnas de la misma altura. Estableciendo la superposición de las series a 100 y el ancho del rango a 0, creamos la ilusión de que cada producto está representado por una sola columna.
Todo esto quedará más claro siguiendo los pasos de construcción del gráfico. El archivo con el ejemplo "
se puede descargar aquí.Empezamos por crear dos tablas auxiliares. En la primera calculamos la cantidad de columnas.
La fórmula en la columna F es
=REDONDEAR(B2/SUMA($B$2:$B$5)*100,0)
Las datos en las columnas G y H son necesarios para los cálculos de la segunda tabla auxiliar.
La segunda tabla nos servirá como fuente de datos para el gráfico
Esta tabla repite el monto de ventas (la altura de cada serie de columnas) en mismo número de veces como la cantidad de columnas que calculamos en la primer tabla auxiliar. Esto lo hacemos con la fórmula
=SI(Y(FILA()>=$G$2,FILA()<=$H$2),$C$2,"")
Donde si el número de fila cae entre el dato de la columna G de la tabla auxiliar 1 y el dato de la columna H, da el monto de ventas del producto y en cas contrario un valor vacío.
Ahora que hemos construido la tabla de datos para el gráfico (la segunda tabla auxiliar), construimos el gráfico.
Seleccionamos la segunda tabla auxiliar (el rango J1:M101) y con el asistente de gráficos creamos un gráfico de columnas agrupadas. Nos aseguramos que se creen las 4 series, una para cada producto
El resultado será este
Quitamos la leyenda, el fondo y el borde del área de trazado; en ek menú de formato de series de datos, en la pestaña Opciones, llevamos el valor de Superposición a 100 y el ancho del rango a 0. Nuestro gráfico se verá ahora así
En la pestaña Tramas del menú formato de serie de datos, definimos el borde como Ninguno. También agregamos títulos al gráfico y a los ejes, con lo que nuestro gráfico se verá finalmente así
Stephen Bullen muestra como puede hacerse este gráfico sin tablas auxiliares, usando nombres.
También Andy Pope tiene un ejemplo usando gráficos de áreas en lugar de columnas.
3 comentarios:
Muchas gracias JLD
Traté de hacerlo buscando una función en Excel pero claro, creo que no existía.
Ahora me queda claro y me ayudará
para la representación del análisis ABC (o conocido 80-20)
Tabién gracias por exponer ejemplos en excel.
J.Menéndez E.
Hola Jorge,
Tal como aparece en el segundo gráfico de esta entrada, cómo colocas la palabra "monto" a la izquierda de las cantidades?? Muchas gracias!
En el menú Opciones del Gráfico, en la pestaña Títulos en la ventanilla Eje de valores (Y), pones "Monto"
Publicar un comentario