Officefull.es

Excel, word, powerpoint, android

Autofiltros (Autofilter) Excel noviembre 28, 2008

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

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

Nombre1 – Nombre2 – Paterno – Materno – Importe

2 Si 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)

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 secuencialen 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);"")}

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.

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<>""

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

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

Descarga del Archivo

 Autofiltros.xls (6.16 kB)

Notas Información Adicional

Curso Microsoft Autofiltros Excel Versiones 97/2000/XP/2002/2003

http://office.microsoft.com/training/training.aspx?AssetID….

Para Office Excel 2007, valido también para versiones anteriores:

Cómo usar el filtrado de Excel para encontrar datos rápidamente

http://www.microsoft.com/spain/empresas/formacion/filtro_excel

Formatos condicionales Excel Versiones 97/2000/XP/2002/2003

http://office.microsoft.com/es-es/excel/HA010864933082.aspx

Formatos condicionales Versión Excel 2007

http://office.microsoft.com/es-es/excel/HP100739393082.aspx

 

Categorías: Sin categoría

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *