¿Cómo crear un gráfico de columnas/barra agrupadas y apiladas en Excel?

En el análisis de datos profesional, visualizar múltiples categorías y grupos de datos dentro de un solo gráfico puede proporcionar una comparación más clara e interpretación de datos más eficiente. Un requisito común es mostrar una combinación de datos apilados y agrupados, como mostrar valores de ventas por región y por trimestre, agrupados uno al lado del otro. Este artículo te guiará a través de los pasos para crear un gráfico de columnas agrupadas y apiladas en Excel, que te permite comparar los valores individuales de las categorías como barras agrupadas, mientras cada grupo consta de segmentos apilados. Este enfoque permite un poderoso análisis comparativo entre grupos y a través de múltiples variables, como se muestra en la captura de pantalla a continuación.

Crear un gráfico de columnas agrupadas y apiladas en Excel

Para crear un gráfico de columnas agrupadas y apiladas en Excel, es importante primero entender que Excel no admite de forma nativa este tipo de gráfico. Sin embargo, puedes simular el efecto preparando cuidadosamente tus datos y personalizando el diseño del gráfico.

✅ Lo que necesitas saber primero:

• Excel no ofrece un tipo de gráfico "columnas agrupadas y apiladas" incorporado. El resultado se crea mediante trucos de disposición de datos.
• Debes reestructurar tus datos fuente para simular agrupaciones.
• Se añaden filas en blanco entre los grupos de categorías para separar visualmente cada agrupación.

Recorramos el proceso paso a paso utilizando datos de ventas de ejemplo para productos durante varios trimestres.

1. Organiza tus datos brutos: En este ejemplo, tenemos nombres de productos en la columna A y datos de ventas (por ejemplo, Real frente a Objetivo para Q1 y Q2) en columnas adyacentes. El objetivo es agrupar los datos de cada producto uno al lado del otro y mostrar los valores real/objetivo apilados dentro de cada grupo.

2. Restructura los datos: Necesitas copiar cada grupo de datos (por ejemplo, cada fila de producto) en un nuevo diseño e insertar una fila vacía entre cada grupo. Esto ayuda a Excel a interpretar cada grupo como un grupo separado en el gráfico de columnas apiladas.

3. Crea el gráfico: Selecciona tus datos recién estructurados. Luego ve a Insertar > Gráfico de Columnas o Barras > Columna Apilada.

4. Formatea la serie: Haz clic derecho en cualquier columna del gráfico, selecciona Formato de Serie de Datos.

5. Reduce el Ancho de Espacio: En el panel Formato de Serie de Datos, ve a Opciones de Serie y establece Ancho de Espacio = 0% para comprimir visualmente cada grupo en un solo grupo apilado.

6. Ajusta la leyenda y el diseño: Haz clic derecho en la leyenda > Formato de Leyenda.

7. Selecciona la posición de la leyenda: Dentro del panel Formato de Leyenda, bajo las Opciones de Leyenda, selecciona la posición preferida de la leyenda (Derecha, Arriba, Izquierda o Abajo) para adaptar mejor tu diseño de gráfico y evitar superposiciones con los datos.

✅ Resultado: Ahora tienes un gráfico de columnas agrupadas y apiladas donde los datos real/objetivo de cada producto están agrupados y apilados uno al lado del otro para una comparación rápida.

⚠️ Limitación: Esta técnica funciona bien para conjuntos de datos pequeños. Pero para conjuntos de datos grandes o datos que cambian frecuentemente, la reestructuración manual puede ser propensa a errores. Consulta las siguientes secciones para soluciones de VBA y fórmulas para automatizar este proceso.

Código VBA – Automatizar el reordenamiento de datos y la generación de gráficos

Al manejar grandes conjuntos de datos o informes que cambian frecuentemente, reorganizar manualmente los datos para crear gráficos agrupados y apilados puede ser tedioso y propenso a errores. Usar VBA (Visual Basic for Applications) te permite automatizar todo el proceso, desde reestructurar la tabla fuente hasta insertar el gráfico, ahorrándote tiempo y esfuerzo.

Este método es más adecuado para usuarios familiarizados con macros de Excel o entornos donde se permite VBA. Es particularmente efectivo si la estructura de tu gráfico permanece igual pero los datos se actualizan regularmente. Aquí está cómo implementarlo:

Paso 1: Presiona Alt + F11 para abrir el editor de VBA. En el editor, haz clic en Insertar > Módulo.

Paso 2: Pega el siguiente código VBA en la ventana del módulo:

Sub CreateStackedClusteredChart()
    Dim ws As Worksheet
    Dim rngData As Range
    Dim chartObj As ChartObject
    Dim chartRange As Range
    Dim xTitleId As String

    On Error Resume Next
    Set ws = ActiveSheet
    xTitleId = "KutoolsforExcel"

    ' Prompt user to select original data
    Set rngData = Application.InputBox("Select the original grouped data (including all headers):", xTitleId, Selection.Address, Type:=8)
    If rngData Is Nothing Then Exit Sub

    ' Create new worksheet for reshaped data
    Dim wsChartData As Worksheet
    Set wsChartData = Worksheets.Add
    wsChartData.Name = "ChartData_" & Format(Now(), "hhmmss")

    Dim numRows As Long, numCols As Long, i As Long, j As Long, outRow As Long
    numRows = rngData.Rows.Count
    numCols = rngData.Columns.Count
    outRow = 1

    ' Add headers
    wsChartData.Cells(outRow, 1).Value = "Category"
    For j = 2 To numCols
        wsChartData.Cells(outRow, j).Value = rngData.Cells(1, j).Value
    Next j
    outRow = outRow + 1

    ' Copy data and insert blank rows
    For i = 2 To numRows
        For j = 1 To numCols
            wsChartData.Cells(outRow, j).Value = rngData.Cells(i, j).Value
        Next j
        outRow = outRow + 1
        If i < numRows Then
            wsChartData.Cells(outRow, 1).Value = ""
            outRow = outRow + 1
        End If
    Next i

    ' Define chart data range
    Set chartRange = wsChartData.Range(wsChartData.Cells(1, 1), wsChartData.Cells(outRow - 1, numCols))

    ' Insert chart
    Set chartObj = wsChartData.ChartObjects.Add(Left:=100, Top:=30, Width:=500, Height:=350)
    With chartObj.Chart
        .SetSourceData Source:=chartRange
        .ChartType = xlColumnStacked
        .HasTitle = True
        .ChartTitle.Text = "Stacked Clustered Column Chart"
        .Legend.Position = xlLegendPositionRight
        .ChartGroups(1).GapWidth = 0
    End With

    MsgBox "Chart generated successfully.", vbInformation, "KutoolsforExcel"
End Sub

Paso 3: Presiona Alt + F8 para abrir el cuadro de diálogo Macro. Selecciona CrearGraficoAgrupadoApilado y haz clic en Ejecutar.

Paso 4: Cuando se te solicite, selecciona tu conjunto de datos original (con encabezados). La macro generará una nueva hoja de trabajo con filas en blanco insertadas y creará automáticamente el gráfico de columnas agrupadas y apiladas.

📝 Consejos:

• Asegúrate de que tu tabla original tenga encabezados de columna consistentes y formato.
• Puedes volver a ejecutar la macro en cualquier momento que se actualice tu conjunto de datos, no es necesario repetir los pasos manuales.

✅ Ventajas: Ahorra tiempo, diseño preciso, perfecto para informes recurrentes.
⚠️ Desventajas: Requiere Excel habilitado para macros y conocimientos básicos de VBA.

Fórmula de Excel – Restructurar dinámicamente datos para gráficos agrupados y apilados

Si prefieres no usar VBA o necesitas una solución que permita actualizaciones dinámicas del gráfico a medida que cambia tu datos brutos, puedes usar fórmulas para reestructurar tus datos fuente en el diseño correcto para gráficos agrupados y apilados. Al aprovechar funciones integradas como ÍNDICE, TRANSPONER y columnas auxiliares, puedes configurar un área de transformación de datos que siempre entregue la estructura correcta para tu gráfico con el mínimo esfuerzo.

Este enfoque es especialmente práctico cuando tus datos originales se actualizan regularmente (nuevos períodos, categorías, etc.), y deseas que tu gráfico se ajuste automáticamente sin reestructuración manual. El principal requisito es construir una sección “auxiliar” que extraiga y organice bloques de datos y filas en blanco usando fórmulas, para que tu fuente de gráfico siempre esté actualizada.

Aquí hay un ejemplo de cómo podrías configurar esto:

• Supongamos que tus datos originales están en A1:D7 (con A1 como el encabezado superior izquierdo), estructurado como región/categoría en la columna A y valores de subcategoría (por ejemplo, T1, T2, T3) en las columnas B, C, D.
• Quieres mostrar cada categoría como un grupo con los valores de T apilados, usando filas en blanco para separar los grupos.

1. En tu nueva hoja u área adyacente, crea una estructura auxiliar para extraer cada grupo e insertar filas en blanco. Por ejemplo, para copiar la primera fila de datos a E2:G2:

=INDEX($A$2:$D$7,INT((ROW()-2)/2)+1,COLUMN()-4+1)

Arrastra esta fórmula hacia abajo según sea necesario. Para insertar filas en blanco entre grupos, configura una fórmula SI para devolver en blanco ("") en líneas alternas:

=IF(ISODD(ROW()), "", INDEX($A$2:$D$7,ROW()/2,COLUMN()-4+1))

Usa una combinación de estas fórmulas con referencias cuidadosamente estructuradas para generar tus datos reestructurados, incluidas filas en blanco a intervalos regulares.

2. Una vez que tu rango de transformación esté completo (con pilas y grupos), selecciona este nuevo rango y crea tu gráfico de columnas apiladas siguiendo el método original dado anteriormente (Insertar > Columna Apilada). El gráfico ahora reflejará automáticamente cualquier cambio que hagas en la tabla de datos original.

Para conjuntos de datos grandes, a menudo es útil usar la función DESREF para hacer que el proceso de extracción sea más flexible o aplicar rangos nombrados dinámicos para definir la fuente del gráfico.

Ventajas: No se requieren VBA ni macros, ideal para entornos con scripting restringido.
Desventajas: Configuración compleja de fórmulas para grandes volúmenes de datos, posible retraso de rendimiento con rangos dinámicos muy grandes.

Solución de problemas: Si tu gráfico no se actualiza correctamente, verifica errores de referencia o desajustes en las fórmulas auxiliares. Asegúrate de que las filas en blanco se inserten correctamente, ya que son clave para lograr la apariencia de “agrupamiento”.

Más artículos relacionados con gráficos:

---

Las Mejores Herramientas de Productividad para Office

Kutools para Excel resuelve la mayoría de tus problemas y aumenta tu productividad en un 80%

• Barra de Fórmulas Super (editar fácilmente múltiples líneas de texto y fórmulas); Diseño de Lectura (leer y editar fácilmente grandes cantidades de celdas); Pegar en Rango Filtrado...
• Combinar Celdas/Filas/Columnas manteniendo los datos; Dividir contenido de celdas; Combinar filas duplicadas y Sumar/Promediar... Prevenir celdas duplicadas; Comparar rangos...
• Seleccionar filas duplicadas o únicas; Seleccionar filas en blanco (todas las celdas están vacías); Búsqueda Avanzada y Búsqueda Difusa en muchos libros de trabajo; Selección Aleatoria...
• Copia Exacta de múltiples celdas sin cambiar la referencia de la fórmula; Crear automáticamente referencias a múltiples hojas; Insertar viñetas, casillas de verificación y más...
• Favoritos e inserción rápida de fórmulas, rangos, gráficos e imágenes; Encriptar celdas con contraseña; Crear lista de correo y enviar correos electrónicos...
• Extraer texto, Agregar texto, Eliminar por posición, Eliminar espacio; Crear e imprimir estadísticas de página; Convertir entre el contenido de las celdas y los comentarios...
• Super Filtro (guardar y aplicar esquemas de filtro a otras hojas); Clasificación avanzada por mes/semana/día, frecuencia y más; Filtro especial por negrita, cursiva...
• Combinar libros y hojas de trabajo; Combinar tablas basadas en columnas clave; Dividir datos en varias hojas; Conversión masiva de xls, xlsx y PDF...
• Agrupación de Tabla Dinámica por número de semana, día de la semana y más... Mostrar celdas desbloqueadas, bloqueadas por diferentes colores; Resaltar celdas que tienen fórmula/nombre...

Leer más... Prueba gratuita de 30 días con todas las funciones... Comprar... 

---

Office Tab - lleva la interfaz con pestañas a Office, y hace que tu trabajo sea mucho más fácil

• Habilitar edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
• Abrir y crear varios documentos en nuevas pestañas de la misma ventana, en lugar de en ventanas nuevas.
• ¡Aumenta tu productividad en un 50%, y reduce cientos de clics del ratón cada día!

Leer más... Prueba gratuita de 30 días con todas las funciones... Comprar...