Para promediar solo las filas que quedan visibles después de aplicar un filtro, usa =SUBTOTALES(101,C2:C50). El 101 indica «promedio sin filas ocultas». La función PROMEDIO no sirve aquí: sigue calculando con todas las filas, las veas o no. Si además el rango tiene errores, =AGREGAR(1,7,C2:C50) promedia lo visible y salta los errores a la vez.
Sintaxis
=SUBTOTALES(núm_función, ref1, [ref2], …)
núm_función | Calcula | Filas ocultas por filtro | Filas ocultas a mano |
|---|---|---|---|
1 | Promedio | Las excluye | Las incluye |
101 | Promedio | Las excluye | Las excluye |
2 / 102 | Contar números | Igual que 1 / 101 | |
9 / 109 | Suma | Igual que 1 / 101 |
=AGREGAR(1, opciones, ref1) es la alternativa más flexible: opción 5 omite filas ocultas, 6 omite errores y 7 hace ambas cosas.
Una ventaja extra: SUBTOTALES y AGREGAR ignoran otras fórmulas SUBTOTALES o AGREGAR que estén dentro del rango, así que no cuentan dos veces los subtotales intermedios.
Paso a paso
- Selecciona cualquier celda de tus datos y activa el filtro con Datos > Filtro (o Ctrl + Mayús + L).
- Debajo o encima de la tabla, fuera del rango que vas a filtrar, escribe
=SUBTOTALES(101,C2:C50). - Filtra con la flecha del encabezado: elige una región, un rango de fechas, lo que necesites.
- El resultado cambia con cada filtro y muestra el promedio solo de las filas visibles.
- Si conviertes los datos en tabla (Insertar > Tabla), marca Diseño de tabla > Fila de totales y elige Promedio en la columna: Excel inserta solo una fórmula SUBTOTALES con 101.
Pon la fórmula en una fila que no quede dentro del área filtrada: si está entre los datos, el filtro puede ocultarla.
Ejemplos resueltos
Ejemplo 1: promedio de una región filtrada
La tabla tiene seis ventas: Norte 1,200, Sur 950, Norte 640, Centro 1,010, Norte 1,380 y Sur 700. En C9 está =PROMEDIO(C2:C7) y en C10 =SUBTOTALES(101,C2:C7).
Sin filtro, las dos dan 5,880 / 6 = 980. Al filtrar la columna Región por «Norte»:
PROMEDIOsigue en 980.SUBTOTALES(101,…)da 3,220 / 3 = 1,073.33.
El filtro te permite explorar sin escribir una fórmula por región. Si necesitas los tres promedios a la vista al mismo tiempo, es mejor PROMEDIO.SI o una tabla dinámica.
Ejemplo 2: ocultar un mes atípico a mano
Los consumos de enero a junio son 410, 380, 455, 120, 430 y 445 kWh. Abril (120) fue un mes de vacaciones y lo ocultas con clic derecho > Ocultar, sin filtro.
=SUBTOTALES(1,B2:B7) → 2,240 / 6 = 373.33 (abril sigue dentro)
=SUBTOTALES(101,B2:B7) → 2,120 / 5 = 424 (abril queda fuera)
Esta es la única diferencia entre 1 y 101. Ocultar filas a mano es cómodo, pero quien abra el archivo puede no notar que faltan datos; si lo haces, deja una nota junto al resultado o usa un filtro, que muestra su icono en el encabezado.
Ejemplo 3: filtrado y con errores a la vez
La columna D calcula un margen por producto, y dos filas muestran #¡DIV/0! porque el producto aún no tiene precio. Con el filtro aplicado, =SUBTOTALES(101,D2:D40) devuelve #¡DIV/0!, porque SUBTOTALES no salta errores.
=AGREGAR(1,7,D2:D40)
La opción 7 omite filas ocultas y valores de error: obtienes el margen promedio de los productos visibles que sí tienen cálculo. Más sobre errores en promedio ignorando errores.
Errores comunes
- Usar PROMEDIO con un filtro activo. El resultado no cambia al filtrar. Si en un reporte ves el mismo promedio para todas las regiones, probablemente es esto.
- Elegir 1 cuando ocultaste filas a mano. Esas filas siguen contando. Usa 101 si quieres excluirlas.
- Ocultar columnas y esperar que se excluyan. SUBTOTALES no ignora columnas ocultas en rangos horizontales.
- Escribir SUBTOTAL en Excel en español. Da #¿NOMBRE?: el nombre es SUBTOTALES, en plural.
- Fórmula dentro del área filtrada. El filtro la oculta junto con las filas. Ponla arriba de los encabezados o debajo de una fila vacía.
Para comparar el promedio de cada filtro con una referencia externa, puedes usar la calculadora de promedio de ventas. Google Sheets tiene la misma función, con una diferencia importante: no existe AGREGAR. Lo explicamos en promedio filtrado en Google Sheets.
Preguntas frecuentes
¿Qué diferencia hay entre SUBTOTALES(1) y SUBTOTALES(101)?
Las dos excluyen las filas que oculta un filtro. La diferencia está en las filas que ocultas a mano (clic derecho > Ocultar): 1 las incluye y 101 las excluye.
¿SUBTOTALES ignora columnas ocultas?
No. SUBTOTALES y AGREGAR están pensadas para rangos verticales; si ocultas columnas de un rango horizontal, las siguen contando.