¿Cómo calcular el promedio de celdas de diferentes hojas en Excel?

Si necesitas calcular el promedio del mismo rango en varias hojas de trabajo, por ejemplo, para encontrar el promedio de ventas en el rango A1:A10 en las hojas llamadas Sheet1 a Sheet5, Excel proporciona una solución directa basada en fórmulas. Este enfoque funciona mejor cuando todas las hojas tienen la misma estructura y nombres consistentes.

Pasos:

Selecciona una celda en blanco donde deseas el resultado (por ejemplo, la celda C3) e introduce la siguiente fórmula:

=AVERAGE(Sheet1:Sheet5!A1:A10)

Después de presionar Enter, Excel devolverá el valor promedio del rango especificado en todas las hojas desde Sheet1 hasta Sheet5.

Si necesitas promediar valores de diferentes rangos en las hojas, puedes enumerarlos manualmente:

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

Esta versión es útil cuando los rangos varían entre las hojas. Introdúcela en tu celda de resultado y presiona Enter.

Kutools para Excel mejora tu capacidad de extraer y consolidar valores de la misma celda o rango en múltiples hojas de trabajo utilizando su función Autoincrementar referencias de hojas de cálculo. Esto es especialmente útil al trabajar con un gran número de hojas con diseño consistente.

Pasos de uso:

1. Abre una nueva hoja de trabajo (como una hoja resumen), luego selecciona la celda donde deseas calcular el promedio, por ejemplo, D7.

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

3. En el cuadro de diálogo:
  - Elige un orden de llenado desde el menú desplegable Orden de llenado (por ejemplo, Llenar por columna, luego por fila).
  - En la Lista de hojas de cálculo, marca las hojas que contienen la celda que deseas promediar.
  - Haz clic en Llenar Rango, luego cierra el cuadro de diálogo.

4. Los valores de las celdas seleccionadas se enumerarán en el rango (por ejemplo, D7:D11). Luego introduce la siguiente fórmula en otra celda en blanco para calcular el promedio:

=AVERAGE(D7:D11)

Presiona Enter para obtener el resultado. Esto simplifica la consolidación pero no se extiende automáticamente para incluir nuevas hojas; tendrás que volver a ejecutar la función cuando cambie la lista de hojas.

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

Cómo usar esta función:

1. Haz clic en Kutools Plus > Combinar para abrir el asistente Combinar Hojas de Trabajo.

2. En el asistente (Paso 1 de 3):
Marca Combinar y Calcular Datos de Varios Libros de Trabajo en una Sola Hoja de Cálculo, luego haz clic en Siguiente para continuar.

3. En el Paso 2 de 3:
  - Selecciona las hojas a incluir bajo Lista de hojas de cálculo.
  - Usa el botón Examinar botón para definir el rango para promediar.
  - Haz clic en Mismo Rango si los rangos son idénticos en todas las hojas.
  - Haz clic en Siguiente para proceder.

4. En el Paso 3 de 3:
Selecciona Promedio desde el menú desplegable Función Opcionalmente, configura etiquetas de filas/columnas según sea necesario, luego haz clic en Finalizar.

5. Un cuadro de diálogo te preguntará si deseas guardar la configuración actual como un escenario para uso futuro. Elige Sí o No según tus necesidades.

Ahora, cada celda en tu rango de salida definido reflejará el promedio de las celdas correspondientes de todas las hojas seleccionadas. Este método es especialmente útil para operaciones recurrentes o cuando se consolida rápidamente grandes cantidades de datos estructurados.

Para usuarios que necesitan automatizar el promedio de celdas en múltiples hojas de trabajo, especialmente cuando los nombres de las hojas no son adyacentes, cambian frecuentemente o cuando deseas especificar rangos en tiempo de ejecución, una macro VBA puede ser una solución eficiente. Este método es ideal para usuarios avanzados o libros donde las hojas se añaden o renombran con frecuencia.

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

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

1. Ve a la pestaña Desarrollador en Excel. Si no está visible, actívala a través de Archivo > Opciones > Personalizar Cinta. Haz clic en Visual Basic para abrir el editor. Luego ve a Insertar > Módulo y pega 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, presiona F5 en el editor, o ciérralo y ve a Desarrollador > Macros, selecciona AverageAcrossSheets y haz clic en Ejecutar.

3. Cuando se te solicite, introduce una lista separada por comas de nombres de hojas (por ejemplo, Sheet1,Sheet3,Summary), luego especifica el rango (por ejemplo, A1:A10).

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

Notas sobre 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 inválidas o ausentes se omitirán silenciosamente.