FÓRMULAS DE EXCEL

Hallar la condición activa de un filtro en Excel

Hallar la condición activa de un filtro en Excel

Funciones utilizadas: SI.ERROR, SI, SUMA, SUBTOTALES, DESREF, FILA, MIN, FILAS, INDICE, K.ESIMO.MENOR, COINCIDIR, INDIRECTO

Suponiendo que disponemos de una lista de códigos y valores con encabezados y filtros aplicados en el rango A1:B15, y que necesitamos obtener en el rango E18:E21 los 4 primeros valores del filtro activo sobre la columna A «Código», podemos aplicar una fórmula que combina las funciones SI.ERROR, SI, SUMA, SUBTOTALES, DESREF, FILA, MIN, FILAS, INDICE, K.ESIMO.MENOR, COINCIDIR e INDIRECTO para lograr devolver múltiples valores activos aplicados en un mismo filtro. La sintaxis que debemos utilizar sobre la celda E18 para calcular el primer valor del filtro activo es la siguiente:

{=SI.ERROR(SI(SUMA((SUBTOTALES(3;DESREF(A2:A15;FILA(A2:A15)-MIN(FILA(A2:A15));;1))))=FILAS(A2:A15);"Sin filtro activo";INDICE(A2:A15;INDICE(K.ESIMO.MENOR(SI(FILA(A2:A15)-1=SI(SUBTOTALES(3;DESREF(A2:A15;FILA(A2:A15)-MIN(FILA(A2:A15));;1));COINCIDIR(A2:A15;A2:A15;0));FILA(A2:A15));FILA(INDIRECTO("1:"&FILAS(A2:A15))))-1;1)));"")}

Donde A2:A15 corresponde al rango de valores sin encabezado que podemos filtrar sobre la columna A «Código» y de los cuales obtendremos hasta 4 valores distintos dependiendo de la condición activa del filtro.

Notamos que la fórmula es matricial con lo cual debemos aceptar dicha fórmula con CTRL+SHIFT+ENTER.

Para obtener el segundo, tercer y cuarto valor del filtro activo debemos reemplazar el último dígito de la fórmula «1» por su posición respectiva. Es decir, para obtener el segundo filtro activo colocaremos un «2» dentro de la fórmula en la celda E19, para el tercer filtro activo un «3» dentro de la fórmula en la celda E20 y para el cuarto filtro un «4» dentro de la fórmula en la celda E21.

Es importante mencionar que la fórmula devolverá los filtros activos a medida que los vaya encontrando de arriba hacia abajo, y no devolverá ningún valor repetido. También vemos que la fórmula está englobada por la función SI.ERROR que nos permite devolver vacío cuando el resultado de la fórmula sea error, por ejemplo sobre la cuarta celda de filtro activo para cuando hayamos filtrado solo 3 valores, y que nos devolverá «Sin filtro activo» para los casos donde no haya ningún filtro aplicado sobre las filas de encabezado.

De esta forma, logramos obtener de forma rápida y práctica los valores que forman la condición activa de un filtro sobre un rango en Excel.

 

Te recomiendo ver el siguiente link con más videos de Fórmulas ingeniosas de Excel AQUÍ.

Salvo aclaración, todas las fórmulas y macros de este sitio están configuradas para aplicarse sobre la celda A1. Algunas fórmulas se encuentran encerradas entre llaves {} debido a que son fórmulas matriciales. Estas llaves no deben introducirse tecleándolas, sino que se generan automáticamente al aceptar la fórmula pulsando Control+Shift+Enter al mismo tiempo. Las fórmulas de este sitio son compatibles con versiones de Microsoft Excel® 2010 o superiores.