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

Cuando tu objetivo es comparar series en varias hojas de cálculo con datos estructurados de la misma manera, como las ventas por región en cuatro meses diferentes ubicados en pestañas separadas, crear un gráfico de columnas consolidado con múltiples series de datos proporciona una comparación visual efectiva.

Este método requiere selección manual pero es sencillo y no necesita fórmulas o macros complejas. Funciona mejor cuando tus hojas de cálculo tienen un diseño idéntico y necesitas componer un gráfico directamente desde las hojas de origen.

Sigue estos pasos para configurar tu gráfico:

1. Haz clic en Insertar > Insertar Gráfico de Columnas (o Columna) > Gráfico de Columnas Agrupadas. Esto abrirá un gráfico en blanco en la hoja de cálculo.

2. Haz clic derecho en el gráfico en blanco recién insertado y selecciona Seleccionar Datos en el menú contextual.

3. En el cuadro de diálogo Seleccionar fuente de datos, haz clic en el botón Agregar para comenzar a agregar una nueva serie de datos.

4. En el cuadro de diálogo Editar Serie, introduce el nombre de la serie y especifica los valores de la serie navegando hasta la hoja de cálculo relevante y seleccionando el rango de datos requerido. Verifica que las referencias sean precisas, ya que errores en las referencias pueden hacer que el gráfico muestre datos incorrectos o errores como #¡REF!. Haz clic en Aceptar para confirmar.

Consejo: Para referenciar datos de otra hoja de cálculo en el cuadro de valores de la serie, cambia a la hoja de destino y luego selecciona el rango deseado. Excel incluirá automáticamente el nombre de la hoja de cálculo en la referencia.

5. Repite los pasos 3 y 4 para cada hoja de cálculo que quieras incluir en el gráfico. Después de agregar todas las series, las verás listadas bajo Entradas de Leyenda (Series) en el cuadro de diálogo.

Nota: Asegúrate de elegir siempre la misma estructura de rango de celdas en cada hoja; las inconsistencias pueden hacer que el gráfico sea engañoso o causar problemas de alineación.

6. Para ajustar finamente tu gráfico, haz clic en Editar debajo de Etiquetas del Eje Horizontal (Categoría) en la ventana Seleccionar Fuente de Datos. En el cuadro de diálogo Etiquetas del Eje, selecciona las etiquetas relevantes para alinear correctamente con tus datos. Haz clic en Aceptar cuando termines.

7. Cierra el cuadro de diálogo Seleccionar Fuente de Datos haciendo clic en Aceptar. Tu gráfico ahora combina series de datos de varias hojas de cálculo.

8. (Opcional) Para mejorar la claridad visual, selecciona el gráfico, luego ve a Diseño > Agregar Elemento de Gráfico > Leyenda, y elige una opción (como Leyenda > Inferior) para mostrar leyendas que identifiquen cada serie.

Este enfoque es ideal para conjuntos de datos estáticos. Sin embargo, si los diseños de las hojas de cálculo varían, o el rango de datos difiere entre las hojas, deberás abordar primero estas diferencias. Si tus datos se actualizan con frecuencia o necesitan consolidación automatizada, considera las otras soluciones a continuación para mayor eficiencia.

Aquí está el gráfico de columnas agrupadas resultante, que compara visualmente series de datos extraídas de cuatro hojas de cálculo diferentes:

En casos donde desees construir un gráfico seleccionando puntos de datos individuales de varias hojas de cálculo, en lugar de series completas, puedes recopilar primero las celdas objetivo en una hoja de resumen y luego graficarlas juntas. Esto es común cuando quieres comparar una sola métrica, como el valor 'Total', de varias hojas departamentales.

Kutools para Excel’s Dynamically Refer to Worksheets proporciona una forma eficiente de extraer valores específicos de varias hojas en una sola hoja de resumen, y es especialmente útil para datos dinámicos donde los valores de origen pueden cambiar con el tiempo.

Aquí está cómo recopilar puntos de datos y crear el gráfico:

1. En la barra de pestañas de la hoja, haz clic en el botón Nuevo hoja o para crear una hoja de trabajo nueva para consolidación.

2. En esta nueva hoja de trabajo, selecciona la celda donde deseas extraer datos de otras hojas. Luego ve a Kutools > Más (bajo el grupo Fórmula ) > Dynamically Refer to Worksheets.

3. En el cuadro de diálogo Rellenar Referencias de Hojas de Trabajo, realiza lo siguiente:

• Selecciona Llenar verticalmente celda por celda en el menú desplegable Orden de llenado. Esto organizará los valores devueltos en una lista vertical.
• Marca las hojas de trabajo que contienen las celdas que deseas referenciar, asegurándote de seleccionar solo las pestañas de origen relevantes.
• Haz clic en Rellenar Rango para extraer los valores, luego Cerrar una vez completado.
  

Consejo: Puedes repetir este paso tantas veces como sea necesario si necesitas recopilar diferentes tipos de datos, organizándolos como columnas o filas distintas en tu hoja de resumen según sea necesario.

Después de estos pasos, verás los datos que seleccionaste de cada hoja de trabajo organizados ordenadamente en tu nueva hoja.

4. Resalta los puntos de datos consolidados y luego procede a crear el gráfico como de costumbre: Insertar > Insertar Gráfico de Columnas (o Columna) > Gráfico de Columnas Agrupadas.

Ahora, has creado un gráfico de columnas agrupadas que compara visualmente los puntos de datos seleccionados, cada uno procedente de una hoja de cálculo diferente.

Consejos:

• Este método funciona mejor para gráficos que se actualizan dinámicamente, ya que los vínculos pueden actualizarse automáticamente cuando cambian los datos de origen (siempre que uses referencias directas o fórmulas).
• Verifica los nombres de las hojas de cálculo de origen si encuentras errores #¡REF!, ya que las hojas renombradas/eliminadas romperán las referencias.

Demo: 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 cuando se trata de un gran número de hojas de cálculo o cuando se necesitan actualizaciones rutinarias—, una macro VBA puede recopilar datos de múltiples hojas y generar un gráfico instantáneamente. VBA te permite personalizar el proceso de consolidación de datos, haciéndolo adecuado para usuarios avanzados o cualquier persona que actualice sus informes con frecuencia.

Ventajas: Automatización, alta flexibilidad para necesidades personalizadas, funciona bien con numerosas hojas de cálculo.
Posibles inconvenientes: Requiere permiso para ejecutar macros, y algunos usuarios pueden no estar familiarizados con la sintaxis de VBA o la solución de problemas.

Puedes usar 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 luego crear un gráfico de columnas agrupadas a partir de los datos combinados:

1. Haz clic en Herramientas de Desarrollo > Visual Basic para abrir la ventana Microsoft Visual Basic para Aplicaciones. Haz clic en Insertar > Módulo, luego pega el código a continuación 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. Haz clic en Ejecutar en el editor de VBA para ejecutar el código. La macro creará automáticamente una hoja de resumen ("SummaryChartData"), recopilará datos (en este ejemplo, el valor en la celda B2) de todas las hojas de cálculo excepto la hoja de resumen, y creará un gráfico basado en los datos compilados.

Nota:

• Si quieres extraer una celda diferente de cada hoja de cálculo, ajusta la referencia ws.Range("B2") según corresponda.
• Para incluir más columnas u rangos flexibles, puedes expandir la lógica del código o recorrer índices de columna.
• Si ocurre algún conflicto en el nombre de las hojas de cálculo, la macro sobrescribirá o recreará la hoja de resumen según sea necesario.
• Antes de ejecutar macros, asegúrate de que la configuración de Excel permita la ejecución de macros.

Si encuentras errores, verifica nuevamente los nombres de las hojas de cálculo, las referencias de celdas especificadas y asegúrate de que ninguna hoja esté protegida u oculta.

Reflejar/vincular celdas entre hojas de cálculo en Excel

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

Copiar datos hacia/desde varias hojas de cálculo en Excel

Aplicar formato condicional entre hojas de cálculo/libros