¿Cómo calcular el promedio de celdas o una lista filtrada en Excel?

Cuando aplica la función PROMEDIO directamente a un conjunto de datos filtrado, esta sigue promediando todas las celdas del rango especificado, incluidas las ocultas por el filtro, lo que puede generar resultados incorrectos si su objetivo es promediar únicamente las filas visibles. Para obtener el promedio real de los datos filtrados (es decir, solo de las filas visibles), la función SUBTOTAL de Excel ofrece una solución eficaz: se adapta automáticamente al filtrado y excluye las filas ocultas, lo que la convierte en la opción ideal para este propósito.

Para calcular el promedio únicamente de los resultados filtrados, siga estos pasos:

• Identifique el rango que incluye todos los datos filtrados en la columna que desea promediar (en este ejemplo, suponga que los valores están en)C12:C24 de la columna Importe).
• En una celda vacía, introduzca la siguiente fórmula:

=SUBTOTAL(1,C12:C24)

Esta fórmula calcula el promedio de las celdas visibles (filtradas) en el rango limitado ()C12:C24). El parámetro 1 indica a SUBTOTAL que utilice la función PROMEDIO, y SUBTOTAL ignora automáticamente cualquier fila oculta por el filtro.

Después de escribir la fórmula, pulse Intro. Verá inmediatamente el valor promedio de las filas visibles, tal como se muestra a continuación:

Sumar/contar/promediar solo celdas visibles ignorando celdas/filas/columnas ocultas o filtradas

Las funciones habituales de Excel, como SUMA, CONTAR o PROMEDIO, operan sobre todas las celdas del rango especificado, independientemente de si están visibles u ocultas por filtros o por ocultación manual. Para un enfoque más preciso en estos escenarios, puede utilizar Kutools para Excel. Gracias a sus funciones especializadas SUMAVISIBLE, CONTARVISIBLE y PROMEDIOSVISIBLE, podrá calcular fácilmente totales, recuentos y promedios únicamente de las celdas realmente visibles en cualquier rango, excluyendo tanto las filas como las columnas filtradas u ocultas manualmente. Esta funcionalidad evita errores en hojas de cálculo complejas y le ahorra tiempo frente al uso de fórmulas complicadas o código personalizado.

Promediar datos/lista filtrados con la función AGGREGATE

Si utiliza Excel 2010 o una versión posterior, la función AGGREGATE ofrece aún más flexibilidad que SUBTOTAL para calcular promedios en datos filtrados, con opciones adicionales para gestionar errores y filas ocultas. A continuación se explica cómo aplicarla:

• En una celda vacía, introduzca la siguiente fórmula (suponiendo que sus datos filtrados están en C12:C24):

=AGGREGATE(1,5, C12:C24)

• El primer argumento ()1) indica la función PROMEDIO, tal como ocurre en SUBTOTAL.
• El segundo argumento ()5) indica a AGGREGATE que ignore tanto las filas ocultas (por filtrado) como los errores.

Pulse Intro tras escribir la fórmula para mostrar inmediatamente el promedio de las filas visibles en su rango de filtro. Si desea adaptarla a distintos métodos de ocultación de filas u otras funciones agregadas, basta con ajustar el segundo parámetro en consecuencia.

Macro VBA para promediar únicamente celdas realmente visibles

Para necesidades más avanzadas o personalizadas, puede usar una sencilla macro VBA para promediar únicamente las celdas visibles (no ocultas ni filtradas) en un Seleccionar rango. Esto resulta especialmente útil en hojas que contienen varios métodos de ocultación de datos. A continuación se explica cómo hacerlo:

1. Vaya a la pestaña Programador en Excel y seleccione Visual Basic para abrir el editor de VBA. En el editor, haga clic en Insertar > Módulo para crear un nuevo módulo.
2. Copie y pegue el siguiente código VBA en la ventana del módulo:

Sub AverageVisibleCells()
    Dim rng As Range
    Dim cell As Range
    Dim sum As Double
    Dim count As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select range to average only visible cells", xTitleId, rng.Address, Type:=8)
    
    sum = 0
    count = 0
    
    For Each cell In rng
        If Not cell.EntireRow.Hidden And cell.Rows.Hidden = False And cell.Columns.Hidden = False Then
            If cell.DisplayFormat.Hidden = False And IsNumeric(cell.Value) And cell.Value <> "" Then
                sum = sum + cell.Value
                count = count + 1
            End If
        End If
    Next cell
    
    If count > 0 Then
        MsgBox "Average of visible cells is: " & sum / count, vbInformation, xTitleId
    Else
        MsgBox "No visible numeric cells found.", vbExclamation, xTitleId
    End If
End Sub

3. Cierre el editor de VBA. De vuelta en su hoja de cálculo, pulse Alt+F8, seleccione PromedioCeldasVisibles y haga clic en Ejecutar. Cuando se le solicite, seleccione su rango de datos objetivo. La macro calculará y mostrará únicamente el promedio de las celdas numéricas actualmente visibles (ni filtradas ni ocultas).

Promedio ponderado

Calcular el promedio/tasa de crecimiento anual compuesta

Promedio móvil/deslizante

Total acumulado / promedio acumulado

Tasa media de cambio