¿Cómo crear un gráfico combinando datos de varias hojas de cálculo en Excel?

Cuando su objetivo es comparar series en varias hojas de cálculo con datos estructurados de forma idéntica —por ejemplo, ventas por región en cuatro meses distintos ubicados en pestañas separadas—, un gráfico de columnas consolidado con múltiples series de datos ofrece una comparación visual clara y eficaz.

Este método requiere una selección manual, pero es sencillo y no necesita fórmulas ni macros complejas. Funciona especialmente bien cuando sus hojas tienen un diseño idéntico y desea crear un gráfico directamente desde las hojas de origen.

Siga estos pasos para configurar su gráfico:

1. Haga clic en Insertar > Insertar gráfico de columnas(o)Columna) > Columna agrupada. Esto abrirá un gráfico en blanco en la hoja de cálculo.

2. Haga clic con el botón derecho en el gráfico en blanco recién insertado y seleccione Seleccionar datos en el menú contextual.

3. En el cuadro de diálogo Origen de Seleccionar Datos, haga clic en el botón Agregar para empezar a añadir una nueva serie de datos.

4. En el cuadro de diálogo Editar serie, introduzca el nombre de la serie y especifique los valores de la serie navegando hasta la hoja de cálculo correspondiente y seleccionando el rango de datos necesario. Compruebe cuidadosamente que las referencias sean correctas, ya que errores en ellas pueden hacer que el gráfico muestre datos incorrectos o errores como #¡REF!. Haga clic en Aceptar para confirmar.

Consejo: Para hacer referencia a datos de otra hoja en el cuadro Valores de la serie, cambie a la hoja de destino y seleccione el rango deseado. Excel incluirá automáticamente el nombre de la hoja en la referencia.

5. Repita los pasos 3 y 4 para cada hoja de cálculo que quiera incluir en el gráfico. Una vez añadidas todas las series, aparecerán listadas bajo Rango de nombres de la serie en el cuadro de diálogo.

Nota: Asegúrese siempre de seleccionar el mismo rango de celdas en cada hoja; las inconsistencias pueden hacer que el gráfico resulte engañoso o provocar problemas de alineación.

6. Para ajustar con precisión su gráfico, haga clic en Editar bajo Etiquetas del eje horizontal (categoría) en la ventana Origen de Seleccionar Datos. En el cuadro de diálogo Rango de etiquetas del eje, seleccione las etiquetas pertinentes para alinearlas correctamente con sus datos. Haga clic en Aceptar cuando haya terminado.

7. Cierre el cuadro de diálogo Origen de Seleccionar Datos haciendo clic en Aceptar. Ahora, su gráfico combina series de datos de múltiples hojas de cálculo.

8. (Opcional) Para mejorar la claridad visual, seleccione el gráfico y vaya a Diseño > Agregar elemento de gráfico > Leyenday elija una opción (por ejemplo,)Leyenda > Inferior) para mostrar leyendas que identifiquen cada serie.

Este enfoque es ideal para conjuntos de datos estáticos. No obstante, si los diseños de sus hojas difieren o el rango de datos varía entre ellas, deberá resolver estas discrepancias con antelación. Si sus datos se actualizan frecuentemente o requieren una consolidación automatizada, considere las soluciones alternativas que se indican a continuación para lograr una mayor eficiencia.

Este es el Gráfico de columnas agrupadas resultante, que compara visualmente series de datos procedentes de cuatro hojas de cálculo distintas:

Cuando desee crear un gráfico seleccionando puntos de datos individuales de varias hojas de cálculo —en lugar de series completas—, puede recopilar primero las celdas objetivo en una hoja de resumen y, a continuación, representarlas gráficamente juntas. Esta estrategia resulta especialmente útil para comparar una métrica concreta, como el valor «Total», procedente de distintas hojas departamentales.

Kutools para Excel incluye la herramienta Autoincrementar referencias de hojas de cálculo, que ofrece una forma eficaz de extraer valores específicos de múltiples hojas en una única hoja de resumen, especialmente útil cuando se trabaja con datos dinámicos cuyos valores de origen pueden cambiar con el tiempo.

A continuación se explica cómo recopilar puntos de datos y crear el gráfico:

1. En la pestaña Barra de pestañas, haga clic en el botón Nuevo o en para crear una hoja de cálculo nueva destinada a la consolidación.

2. En esta hoja de cálculo nueva, seleccione la celda en la que desea extraer datos de otras hojas. A continuación, vaya a Kutools > Más(en el grupo)Fórmula) > Autoincrementar referencias de hojas de cálculo.

3. En el cuadro de diálogo Rellenar referencias de hojas de cálculo, realice lo siguiente:

• Seleccione Llenar por columna, luego por fila en el menú desplegable de Orden de llenado. Así, el valor de devolución se organizará en una lista vertical.
• Marque las hojas de cálculo que contienen las celdas que desea referenciar, asegurándose de seleccionar únicamente las pestañas de origen pertinentes.
• Haga clic en Rellenar rango para extraer los valores y, a continuación, en Cerrar una vez finalizado.
  

Consejo: Puede repetir este paso tantas veces como necesite para recopilar distintos tipos de datos, organizándolos en columnas o filas separadas en su hoja de resumen según le convenga.

Tras estos pasos, verá los datos seleccionados de cada hoja de cálculo organizados de forma ordenada en su nueva hoja.

4. Seleccione los puntos de datos consolidados y, a continuación, cree el gráfico como de costumbre: Insertar > Insertar Gráfico de columnas(o)Columna) > Columna agrupada.

Ahora ha creado un gráfico de columnas agrupadas que compara visualmente los puntos seleccionados en Datos, cada uno procedente de una hoja de cálculo distinta.

Consejos:

• Este método es ideal para actualizar gráficos de forma dinámica, ya que los vínculos se actualizan automáticamente cuando cambian los datos de origen (siempre que se utilicen referencias directas o fórmulas).
• Compruebe las hojas de origen en el nombre de la hoja de cálculo por si aparecen errores #¡REF!, ya que las hojas renombradas o eliminadas romperán las referencias.

Demostración: crear un gráfico a partir de varias hojas de cálculo en Excel

Para usuarios que buscan una solución más automatizada y escalable —especialmente al trabajar con un gran número de hojas de cálculo o necesitar actualizaciones periódicas—, una macro VBA puede recopilar eficazmente datos de múltiples hojas y generar instantáneamente un gráfico. VBA le permite personalizar el proceso de consolidación de datos, lo que lo hace ideal para usuarios avanzados o para cualquiera que actualice con frecuencia sus informes.

Ventajas: Automatización, alta flexibilidad para adaptarse a necesidades personalizadas y excelente rendimiento incluso con numerosas hojas de cálculo.
Inconvenientes potenciales: Requiere permiso para ejecutar macros, y algunos usuarios podrían no estar familiarizados con la sintaxis de VBA ni con su depuración.

Puede utilizar el siguiente ejemplo para combinar una celda específica (por ejemplo, B2) de todas las hojas de cálculo en una nueva hoja de resumen y, posteriormente, crear un Gráfico de columnas agrupadas a partir de los datos combinados:

1. Haga clic en Herramientas para desarrolladores > Visual Basic para abrir la ventana Microsoft Visual Basic para Aplicaciones. A continuación, haga clic en Insertar > Módulo y pegue el siguiente código en el módulo:

Sub CombineDataAndChart()
    Dim ws As Worksheet
    Dim summarySheet As Worksheet
    Dim lastRow As Long
    Dim destRow As Long
    Dim wsCount As Integer
    Dim i As Integer
    Dim rng As Range
    
    On Error Resume Next
    
    ' Create summary sheet or clear previous one
    Application.DisplayAlerts = False
    For Each ws In Worksheets
        If ws.Name = "SummaryChartData" Then
            ws.Delete
            Exit For
        End If
    Next
    Application.DisplayAlerts = True
    
    Set summarySheet = Worksheets.Add
    summarySheet.Name = "SummaryChartData"
    
    destRow = 1
    
    ' Set header
    summarySheet.Cells(destRow, 1).Value = "Sheet"
    summarySheet.Cells(destRow, 2).Value = "Value"
    destRow = destRow + 1
    
    ' Collect data from all sheets (change range as needed)
    For Each ws In Worksheets
        If ws.Name <> "SummaryChartData" Then
            summarySheet.Cells(destRow, 1).Value = ws.Name
            summarySheet.Cells(destRow, 2).Value = ws.Range("B2").Value ' Modify "B2" as needed
            destRow = destRow + 1
        End If
    Next
    
    ' Create chart
    Dim chartObj As ChartObject
    Set chartObj = summarySheet.ChartObjects.Add(Left:=250, Width:=350, Top:=20, Height:=250)
    
    chartObj.Chart.ChartType = xlColumnClustered
    chartObj.Chart.SetSourceData Source:=summarySheet.Range("A1:B" & destRow - 1)
    chartObj.Chart.HasTitle = True
    chartObj.Chart.ChartTitle.Text = "Combined Data from All Sheets"
    
    xTitleId = "KutoolsforExcel"
End Sub

2. Haga clic en el botón Ejecutar del editor de VBA para ejecutar el código. La macro creará automáticamente una hoja de resumen («SummaryChartData»), recopilará los datos (en este ejemplo, el valor de la celda B2) de todas las hojas de cálculo excepto la de resumen y generará un gráfico a partir de los datos recopilados.

Nota:

• Si desea extraer una celda distinta de cada hoja de cálculo, ajuste la referencia ws.Range("B2") en consecuencia.
• Para incluir más columnas o rangos flexibles, puede ampliar la lógica del código o recorrer los índices de columna mediante un bucle.
• Si se producen conflictos con algún nombre de hoja de cálculo, la macro sobrescribirá o volverá a crear automáticamente la hoja de resumen según sea necesario.
• Antes de ejecutar macros, asegúrese de que la configuración de Excel permita su uso.

Si encuentra errores, compruebe cuidadosamente el nombre de la hoja de cálculo, las referencias de celda especificadas y asegúrese de que ninguna hoja esté protegida u oculta.

Vincular o reflejar celdas entre distintas hojas de cálculo en Excel

Definir un rango con nombre en varias hojas de cálculo en Excel

¿Copiar datos desde o hacia varias hojas de cálculo en Excel?

Aplicar Usar formato condicional en varias hojas de cálculo o libros de trabajo