Si eres un analista de datos que utiliza filtros constantemente, seguro que conoces la función SUBTOTALES. Es fantástica para sumar (función 9) o promediar (función 1) solo las filas que tienes visibles en pantalla.
Sin embargo, cuando intentas calcular la Mediana o la Moda, te encuentras con una pared: SUBTOTALES no incluye estas operaciones en su lista de funciones. ¿Por qué ocurre esto y, lo más importante, cómo podemos solucionarlo con fórmulas elegantes? Hoy vamos a hackear este límite.
El Problema: ¿Por qué no existe SUBTOTALES(MEDIANA;...)?
La función SUBTOTALES fue diseñada para operaciones aritméticas directas. La Mediana y la Moda son medidas estadísticas de posición que requieren analizar el conjunto de datos completo y ordenarlo internamente. Al filtrar filas, la lógica de cálculo se vuelve mucho más compleja para el motor de Excel.
Si intentas usar =MEDIANA(A1:A10) mientras tienes un filtro aplicado, el resultado será el mismo que si no hubiera filtro. Excel seguirá contando las filas ocultas.
La Solución Maestra: La combinación de AGREGAR y FILAS
Para los usuarios de Excel 2010 en adelante, la solución se llama AGREGAR. Esta función es como "Subtotales con esteroides". Pero, ¡un momento!, AGREGAR tampoco tiene la opción directa de Mediana.
Para resolverlo, utilizaremos una técnica de fórmulas matriciales (en versiones modernas de Excel como 365 o 2021) o una combinación con la función SI.
1. Calcular la Mediana para datos filtrados
Utilizaremos una lógica donde solo "dejamos pasar" los datos si la fila es visible.
La fórmula:
Desglose del truco:
SUBTOTALES(3; ...)actúa como un interruptor. Si la fila es visible, devuelve 1; si está oculta, devuelve 0.
DESREFcrea una referencia individual para cada celda del rango.El
SIdescarta los valores de las filas ocultas, dejando queMEDIANAtrabaje solo con lo que ves en pantalla.
2. Calcular la Moda para datos filtrados
La lógica es idéntica, solo cambiamos la función exterior.
La fórmula:
Alternativa en Excel Moderno (Office 365 / 2021)
Si tienes las nuevas funciones de matrices dinámicas, puedes usar la función FILTRAR combinada con SUBTOTALES de una manera más limpia, pero la técnica anterior sigue siendo el estándar de oro por su compatibilidad universal.
Comparativa de funciones
| Función | ¿Soporta Filtros? | ¿Calcula Mediana? | ¿Calcula Moda? |
PROMEDIO | ❌ No | ❌ No | ❌ No |
SUBTOTALES | ✅ Sí | ❌ No | ❌ No |
| Fórmula Pro (SI+SUBTOTALES) | ✅ Sí | ✅ Sí | ✅ Sí |
⚠️ Un detalle vital: El "Enter" especial
Si no usas Office 365, recuerda que estas son fórmulas matriciales. Después de escribir la fórmula, no presiones solo Enter. Debes presionar:
Ctrl + Shift + Enter
Sabrás que lo hiciste bien si ves que tu fórmula se encierra automáticamente entre llaves { }.
Conclusión
El hecho de que Excel no incluya una función directa para esto no significa que no se pueda hacer. Combinando SUBTOTALES como un "validador de visibilidad" dentro de una función SI, puedes obtener estadísticas precisas de tus datos filtrados sin recurrir a macros complejas.
¿Alguna vez has tomado decisiones basadas en una Mediana errónea por no considerar los filtros? Cuéntanos tu experiencia en los comentarios.
No hay comentarios:
Publicar un comentario