¿Cómo se calcula el promedio de celdas ubicadas en distintas hojas de Excel?

Si necesita calcular la media del mismo rango en varias hojas de cálculo —por ejemplo, para hallar la media de ventas en el rango A1:A10 en hojas llamadas Hoja1 a Hoja5—, Excel ofrece una solución directa mediante fórmulas. Este enfoque funciona especialmente bien cuando todas las hojas tienen la misma estructura y una nomenclatura coherente.

Pasos:

Seleccione una celda vacía para el resultado (por ejemplo, la celda C3) e introduzca la siguiente fórmula:

=AVERAGE(Sheet1:Sheet5!A1:A10)

Tras pulsar Intro, Excel devolverá el valor medio del rango limitado en todas las hojas desde Hoja1 hasta Hoja5.

Si necesita obtener la media de valores procedentes de rangos distintos en diferentes hojas, puede indicarlos manualmente:

=AVERAGE(A1:A5, Sheet2!A3:A6, Sheet3!A7:A9, Sheet4!A2:A10, Sheet5!A4:A7)

Esta versión resulta útil cuando los rangos varían entre hojas. Introdúzcala en su celda de resultados y pulse Intro.

Kutools para Excel potencia su capacidad para extraer y consolidar valores de la misma celda o rango en múltiples hojas de cálculo gracias a su función Autoincrementar referencias de hojas de cálculo. Es especialmente útil cuando trabaja con un gran número de hojas que comparten una disposición uniforme.

Pasos de uso:

1. Abra una hoja de cálculo nueva (por ejemplo, una hoja de resumen) y seleccione la celda en la que desea calcular la media; por ejemplo, D7.

2. Vaya a Kutools > Más(dentro del grupo)Fórmula) > Autoincrementar referencias de hojas de cálculo.

3. En el cuadro de diálogo:
- Seleccione una Orden de llenadoen el menú desplegable (por ejemplo,)Llenar por columna, luego por fila).
- En el campo Lista de hojas de cálculo, marque las hojas que contienen la celda cuya media desea calcular.
- Haga clic en Rellenar rango y, a continuación, cierre el cuadro de diálogo.

4. Los valores de las celdas seleccionadas aparecerán en el rango (p. ej.,)D7:D11). A continuación, introduce la siguiente fórmula en otra celda vacía para calcular la media:

=AVERAGE(D7:D11)

Pulse Intro para obtener el resultado. Este método simplifica la consolidación, pero no se amplía automáticamente para incluir hojas recién añadidas; deberá volver a ejecutar la función siempre que cambie la lista de hojas.

En algunos casos, es posible que necesite calcular el promedio de varias celdas correspondientes en múltiples hojas de cálculo simultáneamente —por ejemplo, resumir los resultados de A1, B1 y C1 de cada hoja—. Esto puede volverse engorroso con fórmulas estándar, pero la utilidad Combinar (hojas de cálculo y libros de trabajo) de Kutools para Excel simplifica enormemente este proceso.

Cómo utilizar esta función:

1. Haga clic en KUTOOLS PLUS > Combinar para abrir el asistente Combinar Hojas de Cálculo.

2. En el asistente (paso 1 de 3):
Marque la casilla Combinar y calcular datos de varios libros de trabajo en una sola hoja de cálculo y haga clic en Siguiente para continuar.

3. En el paso 2 de 3:
- Seleccione las hojas de cálculo que desea incluir en la Lista de hojas de cálculo.
- Utilice el botón Examinar para definir el rango sobre el que calcular la media.
- Haga clic en Mismo rango si los rangos son idénticos en todas las hojas.
- Haga clic en Siguiente para avanzar.

4. En el paso 3 de 3:
Seleccione Media en el menú desplegable Función. Opcionalmente, configure las etiquetas de filas o columnas según sea necesario y haga clic en Finalizar.

5. Aparecerá un cuadro de diálogo preguntando si desea guardar la configuración actual como un escenario para usos futuros. Elija Sí o No según sus necesidades.

Ahora, cada celda del área de ubicación de la lista definida reflejará el promedio de las celdas correspondientes de todas las hojas de trabajo seleccionadas. Este método resulta especialmente útil para operaciones recurrentes o para consolidar rápidamente grandes volúmenes de datos estructurados.

Para usuarios que necesiten automatizar el cálculo del promedio de celdas en múltiples hojas de cálculo —especialmente cuando los nombres de las hojas no son consecutivos, cambian con frecuencia o cuando deseen especificar el rango en tiempo de ejecución—, una macro de VBA puede ser una solución eficaz. Este enfoque resulta ideal para usuarios avanzados o para libros de trabajo en los que las hojas se añaden o renombran habitualmente.

El siguiente código VBA le permite introducir dinámicamente nombres de hojas y rangos de celdas, y luego calcula el promedio del rango especificado en todas las hojas indicadas. Es ideal para consolidar valores en libros de trabajo complejos o que se actualizan con frecuencia.

Cómo configurar y utilizar esta solución VBA:

1. Vaya a la pestaña Programador en Excel. Si no es visible, actívela desde Archivo > Opciones > Personalizar cinta de opciones. Haga clic en Visual Basic para abrir el editor. A continuación, vaya a Insertar > Módulo y pegue el siguiente código:

Sub AverageAcrossSheets()
    Dim xSheetNames As String
    Dim xCellRange As String
    Dim xArr As Variant
    Dim xSheet As Worksheet
    Dim xTotal As Double
    Dim xCount As Long
    Dim i As Integer
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    xSheetNames = Application.InputBox("Enter sheet names separated by commas (e.g., Sheet1,Sheet3,Summary):", xTitleId, Type:=2)
    If xSheetNames = "" Then Exit Sub
    
    xCellRange = Application.InputBox("Enter cell or range to average (e.g., A1 or A1:B10):", xTitleId, Type:=2)
    If xCellRange = "" Then Exit Sub
    
    xArr = Split(xSheetNames, ",")
    xTotal = 0
    xCount = 0
    
    For i = LBound(xArr) To UBound(xArr)
        Set xSheet = Nothing
        Set xSheet = ThisWorkbook.Sheets(Trim(xArr(i)))
        
        If Not xSheet Is Nothing Then
            If Not IsError(Application.WorksheetFunction.Average(xSheet.Range(xCellRange))) Then
                xTotal = xTotal + Application.WorksheetFunction.Sum(xSheet.Range(xCellRange))
                xCount = xCount + xSheet.Range(xCellRange).Count
            End If
        End If
    Next i
    
    If xCount = 0 Then
        MsgBox "No valid data found!", vbExclamation, xTitleId
    Else
        MsgBox "The average across selected sheets and range is: " & xTotal / xCount, vbInformation, xTitleId
    End If
End Sub

2. Para ejecutar la macro, pulse F5 en el editor o ciérrelo y vaya a Programador > Macros, seleccione AverageAcrossSheets y haga clic en Ejecutar.

3. Cuando se le solicite, introduzca una lista de nombres de hojas de cálculo separados por comas (por ejemplo,)Hoja1,Hoja3,Resumen) y, a continuación, especifique el rango (por ejemplo, A1:A10).

4. La macro calculará el total y el recuento de cada hoja válida, y mostrará la media en un cuadro de mensaje.

Notas sobre los parámetros:

• Los nombres de las hojas no distinguen entre mayúsculas y minúsculas, pero deben coincidir exactamente.
• El rango puede ser una sola celda, una columna completa (por ejemplo, B:B) o un rango rectangular (por ejemplo, D2:E12).
• Las hojas no válidas o ausentes se omitirán de forma silenciosa.