¿Ha podido encontrar el tipo de gráfico de columnas apiladas agrupadas en Excel?
Bueno, desafortunadamente no está ahí. Pero Excel es una herramienta poderosa y podemos utilizar otras opciones para crear el gráfico que estamos buscando con este sencillo consejo para crear su propio gráfico de columnas apiladas agrupadas en Excel. Este tutorial se centra en Microsoft Excel 2013, Excel 2016 y Excel 2019. Pero lo más probable es que funcione para las versiones posteriores a 2019. Si desea ver los pasos necesarios para Excel 20010 y Excel 2007, consulte este tutorial: cómo- Cree fácilmente un gráfico de columnas agrupadas apiladas en excel
El desglose
Estos son los pasos básicos que recorreremos en este tutorial. El archivo de descarga de muestra y la demostración en video se encuentran al final del artículo.
1) Datos de configuración
2) Crear gráfico de columnas apiladas
3) Cambiar filas / columnas del gráfico (si es necesario)
4) Disminuir el ancho del espacio de la serie (como se desee)
Pasos opcionales para las funciones de gráficos ampliados:
5) Agregar series de relleno para la separación
5a) Insertar filas según sea necesario en el gráfico creado previamente
6) Agregar Serie de etiquetas verticales del segundo eje
6a) Agregar serie
6b) Repita los pasos 2-3
6c) Mover la nueva serie de etiquetas Veritical del segundo eje al segundo eje
6d) Cambiar el nuevo relleno de la serie de etiquetas verticales del segundo eje a Sin relleno
6e) Disminuir el espacio de la serie Ancho (como se desee)
6f) Eliminar entrada de leyenda de etiqueta del segundo eje
Paso a paso
1) Datos de configuración
Para crear un tipo de gráfico de columnas apiladas agrupadas simulado, solo necesitamos realizar 4 pasos. Ahora, esta sugerencia utiliza la opción de gráfico «Etiquetas de categoría de varios niveles» de Excel para gráficos de columnas. Para poder usar esta opción, necesitamos configurar nuestros datos de una manera determinada. Ese es nuestro primer paso.
Nuestro escenario es que estamos creando un gráfico para el equipo de marketing sobre la cantidad gastada y la cantidad presupuestada para 2 productos en 4 tipos de medios (impresos, Internet, TV, radio). Los datos que recibimos estaban en este formato.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Presupuesto de ventas vs. td> | ||||||||
| 2 | Radio | Radio | Imprimir | Imprimir | TV | TV | Internet | Internet | |
| 3 | Presupuesto | Real | Presupuesto | Real | Presupuesto | Real | Presupuesto | Real | |
| 4 | Producto 1 | 123 | 166 | 128 | 91 | 163 | 181 | 171 | |
| 5 | Producto 2 | 63 | 149 | 157 | 101 | 53 | 140 | 198 | 153 |
Datos originales
Para utilizar la opción ”Etiquetas de categoría multinivel” en un gráfico de columnas, debemos configurar nuestros datos con categorías escalonadas. Eso significa que tenemos una categoría principal en una columna y muchas categorías en la columna de la derecha. Por lo tanto, necesitamos cambiar nuestros datos al siguiente formato.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Presupuesto de ventas frente a real | |||||
| 2 | Radio | Imprimir | TV | Internet | ||
| 3 | Producto 1 | Presupuesto | 123 | 155 | 91 | 181 |
| 4 | Real | 166 | 128 | 163 | 171 | |
| 5 | Producto 2 | Presupuesto | 63 | 157 | 53 | 198 |
| 6 | Real | 149 | 101 | 140 | 153 |
Configuración básica de gráficos apilados agrupados
Para que pueda ver que nuestros datos ha sido reorganizado para que El producto 1 tiene 2 líneas asociadas. Uno para los valores presupuestarios y otro para los valores reales de los 4 tipos de publicidad en medios. Ahora estamos listos para crear el gráfico de columnas apiladas agrupadas de Excel.
2) Crear gráfico de columnas apiladas
Para crear nuestro gráfico, resalte el rango de datos en el conjunto de datos anterior de A2: F6. Luego vaya a Insertar cinta y haga clic en el gráfico de columnas apiladas en el botón de gráfico como ve aquí.
Su gráfico ahora debería verse así:
3 ) Cambiar filas / columnas del gráfico (si es necesario)
Nuestro gráfico no se ve bien. En la mayoría de los casos, debe cambiar filas / columnas en su gráfico. ¿Por qué necesito hacer esto? Vea este video aquí
Para arreglar su gráfico, simplemente haga clic en el gráfico y luego vaya a la Cinta de diseño y haga clic en el botón Cambiar filas / columnas.
Su gráfico ahora está completo y debería verse así:
Si desea acercar las columnas, siga el siguiente paso, pero es opcional.
4) Disminuya el ancho del espacio de la serie (como desee)
Si lo desea, y es mi aspecto preferido, puede cerrar el ancho del espacio de las columnas. Para hacer esto, seleccione el gráfico y luego seleccione cualquier serie de datos y presione CTRL + F1 (o haga clic derecho y seleccione Dar formato a serie de datos). Luego, desde el cuadro de diálogo Formatear serie de datos, cambie el Ancho del espacio al 15% o lo que crea que se ve mejor.
Su gráfico final ahora debería verse como se ve arriba. Pero no hemos terminado si desea agregar algunos cambios adicionales. A continuación es completamente opcional y como lo desea, pero creo que agrega algo de claridad y detalles adicionales que ayudarán al lector.
Pasos opcionales del gráfico de columnas apiladas agrupadas
5) Agregar serie de relleno para la separación
Si desea separar las agrupaciones, siga este paso.
5a) Inserte las filas según sea necesario en el gráfico creado previamente
Haga esto como mejor le parezca , simplemente inserte una fila en sus datos y separará los grupos.
Después de insertar su fila, debe seguir los pasos 1-3 anteriores para crear su gráfico desde el principio.Cuando complete los pasos 1 a 3, verá que su gráfico no se ve bien, ya que su grupo de producto 1 incluye este nuevo espacio como puede ver aquí:
Para solucionar este problema, debe engañar a Excel creando otra agrupación de nivel uno que no se muestra en el gráfico. Esto es muy simple con solo poner un espacio en el conjunto de datos en la celda justo encima de la entrada del Producto 2.
Su gráfico final con el espacio adicional debe se ve así con la agrupación fija:
6) Agregar serie de etiquetas de segundo eje vertical
El paso 5 opcional anterior me parece bien, pero los datos del producto 2 están más lejos de los números, por lo que a veces me gusta agregar un series de gráficos adicionales en el eje secundario para que los números del eje vertical se muestren más cerca de esa serie. Este es un paso opcional, así que utilícelo solo si también le gusta el aspecto.
6a) Agregar serie
Para que el valor del límite máximo del eje secundario coincida con el límite máximo del eje primario, necesitamos crear una fórmula para nuestra serie adicional de ejes secundarios. Para hacer esto, cree una fórmula en la primera fila de datos con una fórmula que calcule la columna apilada máxima de todas las columnas apiladas que estarán en el gráfico. Por lo tanto, en la celda G3, creamos una fórmula MAX () con una suma de cada fila. Consulte la fórmula Máx. Y Suma a continuación para obtener más detalles.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Presupuesto de ventas frente al real | ||||||
| 2 | Radio | Imprimir | TV | Internet | Etiquetas del segundo eje | ||
| 3 | Producto 1 | Presupuesto | 123 | 155 | 91 | 181 | 628 |
| 4 | Real | 166 | 128 | 163 | 171 | ||
| 5 | |||||||
| 6 | Producto 2 | Presupuesto | 63 | 157 | 53 | 198 | |
| 7 | Actual | 149 | 101 | 140 | 153 |
Gráfico de segundo eje opcional
Fórmulas de hojas de trabajo
|
6b) Repita los pasos 2-3
Ahora que ha creado su conjunto de datos, simplemente seguirá todos los pasos básicos originales 1-3 anteriores para crear su gráfico de columnas apiladas agrupadas pero cambie el rango de su gráfico a A2: G6 o A2: G7 si ha agregado una fila adicional para un espacio entre agrupaciones. En mi caso, agregué la fila de espacios, por lo que mi rango de gráfico es A2: G7.Continúe y cree el gráfico inicial como ve aquí:
La inicial de su gráfico ahora debería verse así si tiene la fila de espacios en la serie de datos después del paso 2.
Después de cambiar de fila / columna, su gráfico se verá así después del paso 3:
6c) Mover la nueva serie de etiquetas Veritical del segundo eje al segundo eje
Nuestro gráfico se ve muy similar al gráfico básico, pero hay una serie adicional que usaremos para nuestro eje secundario. Así que seleccione el gráfico y luego seleccione la serie «2nd Axis Labels» y presione CTRL + F1 o haga clic derecho sobre él y seleccione la opción Format Data Series … Esto abrirá la opción para moverlo al eje secundario. Haga eso ahora .
6d) Cambiar el relleno de la serie de etiquetas del segundo eje vertical nuevo a Sin relleno
Cuando movimos la serie al eje secundario, cubre una de nuestras columnas apiladas. Por lo tanto, para evitar esto, debemos cambiar el relleno de la serie a Sin relleno. Si todavía está en el cuadro de diálogo Formato de serie de datos, haga clic en las Opciones de relleno (parece una lata de pintura vertiéndose) y cambie el relleno a Sin relleno.
6d) Disminuir el ancho del espacio de la serie (como Deseado)
Esto es lo mismo que el paso opcional 4 anterior. Cambié el ancho del espacio de las columnas apiladas en el eje principal al 15% como puede ver aquí:
6f) Eliminar la entrada de leyenda de la etiqueta del segundo eje
Lo último que tenemos que hacer es eliminar la entrada de la leyenda» Etiquetas del segundo eje «en la leyenda. Para hacer esto, seleccione el gráfico y luego seleccione la leyenda y luego seleccione la leyenda y luego seleccione la entrada de leyenda «2nd Axis Labels» y presione la tecla Suprimir.
Su gráfico final ahora se verá así:
Esta es una gran técnica para hacer un gráfico combinado que se necesita con frecuencia para conjuntos de datos complejos. Incluso funcionará para más conjuntos de datos de productos. A continuación, se muestra un ejemplo de cómo se vería otra serie.
Video de demostración
Vea este video tutorial sobre las técnicas presentado anteriormente.
Descarga del archivo de muestra
Haga clic aquí para descargar el archivo de plantilla de Excel de muestra gratuito:
Clustered-Stacked -Gráficos de columnas
