Autofiltros (Autofilter) Excel

Escrito por Hector Miguel . Posted in Excel XP/2003/2007

Microsoft Most Valuable Professional

Extraer Información Autofiltros

El filtrado automático (autofiltros en Excel) es muy útil a la hora de filtrar una lista, tabla, base datos...que tengamos en nuestra hoja de Excel.

Alguna vez hemos necesitado extraer cierta información del filtrado automático o autofiltros y hemos necesitado echar mano de macros para realizarlo.

En este artículo mostramos como podéis Extraer cierta Información de los Autofiltros SIN macros →

Desarrollo archivo para obtener información de listas con Auto-filtros SIN macros

Nota e información sobre los Autofiltros y Formatos condicionales.

Obtener información de listas con Auto-filtros SIN macros

Este archivo trata de obtener información de listas con auto-filtros SIN macros, por lo que...

- sólo rescata información de los criterios de "selección directa" (y obviamente...)
- a mayor número de filtros/columnas, mayor complejidad en sus fórmulas
- toma en cuenta que la mayoría son fórmulas de "entrada matricial" (ctrl + shift + enter)

Imagen ejemplo de una formula Matricial >>

Desarrollo

1Tenemos los siguientes datos en la Hoja1 [fila 4 ] →

Nombre1 - Nombre2 - Paterno - Materno - Importe

Lista de datos en Hoja de Excel

2Si creamos un filtro por la columna Nombre y elegimos el nombre [ Miguel ], luego realizamos un filtro por la columna Nombre2 y elegimos [ Alberto] veremos lo siguiente en la hoja de Excel →

La [ Fila 1 ] Celdas [ B1:F1 ] nos dice que la aplicación de filtros nos devuelve una sola coincidencia para ello se han aplicado los siguientes formatos condicionales >>

Celda B1 →

=SI(SUMA(B2:E2)=4;"La aplicación de filtros devuelve una sola coincidencia";"")

Formato condicional aplicado a las celdas [ B1:F1 ] Formula ->

=$B1<>""

La [ Fila 2 ] celdas [ B2:E2 ]:

Cuenta los elementos únicos dentro del rango con auto-filtros

- cuando alguna columna "suma" uno (1), significa que el elemento mostrado es "el criterio"
- cuando todas suman uno (=4), significa que los auto-filtros devuelven un elemento único
(algún "reporte de criterio" podría NO SER, sino sólo "por coincidencia de únicos")

Las fórmulas empleadas en estas celdas y que son de entrada matricial >>

{=SUMA(SI(FRECUENCIA(SI(SUBTOTALES(3;DESREF(B4;FILA(Listado)-FILA(B4);));COINCIDIR("*"&DESREF(Listado;;COLUMNAS($B2:B2)-1;;1);DESREF(Listado;;COLUMNAS($B2:B2)-1;;1);0));FILA(Listado)-FILA(B4))>0;1))}

Listado -> es el rango con los auto-filtros que lleva el nombre de "Listado" (definido como rango "dinámico")

=DESREF(xFunciones!$B$4;1;;COINCIDIR("zzzzz";xFunciones!$B:$B)-FILA(xFunciones!$B$4);4)

3 Informacion autofiltros

Las celdas [I1:I3] son para determinar las filas ocultas, rango de filas y la inicial y significan:

- si no hay ocultas, los auto-filtros no están activos y se indica en la celda [K2]
- si algún filtro es aplicado, se muestran en orden secuencial en las 3 columnas siguientes
- la fila 1 indica el número, la fila 2 el título y la fila 3 el criterio (sólo 3 de 4)>>

Fórmulas de entrada matricial en -> Criterio:

{=SI(L2<>"";INDICE(INDIRECTO(CARACTER(COINCIDIR(L2;$B4:$E4;0)+COLUMNA($B4)-1+64)&SUSTITUIR($I2;":";":"&CARACTER(COINCIDIR(L2;$B4:$E4;0)+COLUMNA($B4)-1+64)));COINCIDIR(1;SUBTOTALES(3;DESREF(INDIRECTO(CARACTER(COINCIDIR(L2;$B4:$E4;0)+COLUMNA($B4)-1+64)&$I3);FILA(INDIRECTO($I2))-$I3+1;;1;));0)+1);"")}

4 Informacion autofiltros Excel

En la columna 'P' utilizada para revisar la información de alguna columna en específico, solo situando en la celda [ P1 ] la letra de la columna de los autofiltros [ B-C-D-E o F en este caso ] nos dará la información del filtro único o bien del Primer Dato filtrado.

5 El siguiente cuadro de texto que tenemos en el archivo >>

Muestra una forma "dinámica" de informar los auto-filtros

- estos datos se toman de la celda [G3] que tiene formato condicional de texto color blanco

=G3<>""

6 Si quisiéramos avanzar a un cuarto filtro o si tuviéramos más columnas para aplicar Filtros >>

Celda [O1]>>

=SI(Y(I1;CONTAR.SI(B2:E2;1)>2);"4º Filtro";"")

Celda [O2] >>

{=SI(Y(I1;CONTAR.SI(B2:E2;1)>2);DESREF(B4;;K.ESIMO.MENOR(SI(B2:E2=K.ESIMO.MENOR(B2:E2;1);COLUMNA(B2:E2));COINCIDIR(CONTAR.SI(B2:E2;K.ESIMO.MENOR(B2:E2;1));{1\2\3\4})-(SUMAPRODUCTO(COINCIDIR(CONTAR.SI(B2:E2;K.ESIMO.MENOR(B2:E2;1));{1\2\3\4}))=5))-COLUMNA(B2));"")}

Celda [O3] >>

{=SI(O2<>"";INDICE(INDIRECTO(CARACTER(COINCIDIR(O2;$B4:$E4;0)+COLUMNA($B$4)-1+64)&SUSTITUIR($I$2;":";":"&CARACTER(COINCIDIR(O2;$B4:$E$4;0)+COLUMNA($B4)-1+64)));COINCIDIR(1;SUBTOTALES(3;DESREF(INDIRECTO(CARACTER(COINCIDIR(O2;$B4:$E4;0)+COLUMNA($B$4)-1+64)&$I$3);FILA(INDIRECTO($I$2))-$I$3+1;;1;));0)+1);"")}

Y para el formato condicional >>

=O2<>"" y poner color al gusto

Nota: Las formulas aquí expuestas tienen el separador de argumentos [ ; ]