Función FILTRO en Excel (Cómo + 8 ejemplos)

El filtrado es una acción cotidiana común para la mayoría de los usuarios de Excel. Ya sea que utilice Autofiltro o una tabla, es una forma conveniente de ver un subconjunto de datos rápidamente. Hasta que se lanzó la función FILTRO en Excel, no había una manera fácil de lograr esto con fórmulas. Cuando Microsoft anunció los cambios en el motor de cálculo de Excel , también introdujo una serie de nuevas funciones. Una de esas nuevas funciones es FILTRO, que devuelve todos las celdas de un rango que cumplen criterios específicos.

Al momento de escribir este artículo, la función FILTRO solo está disponible en Excel 365, Excel 2021 y Excel Online. No estará disponible en Excel 2019 o versiones anteriores.

Tabla de contenido

• Argumentos de la función FILTRO
• Ejemplos de uso de la función FILTRO
• Ejemplo 1: FILTRO devuelve una matriz de filas y columnas
• Ejemplo 2: ¡#CALC! error causado por la función FILTRO
• Ejemplo 3: FILTRO se expande automáticamente cuando se vincula a una tabla
• Ejemplo 4: uso de FILTER con múltiples criterios.
• Ejemplo 5: uso de FILTRO para listas desplegables dinámicas dependientes
• Ejemplo 6: uso de FILTER con otras funciones
• Ejemplo 7: uso de FILTRO para mostrar elementos coincidentes de una lista
• Ejemplo 8: simulación de búsqueda con comodines con FILTER

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre del archivo: 0035 Función FILTRO en Excel.zip

Tener acceso

Ver el vídeo:

Argumentos de la función FILTRO

Antes de ver los argumentos necesarios para la función FILTRO, veamos un ejemplo básico para apreciar lo que hace.

Aquí, la función FILTRO devuelve todos los valores en las celdas B3-B10 donde el número de caracteres es mayor que 15. No es un escenario que muchos de nosotros necesitaremos, pero demuestra perfectamente el poder de la nueva función FILTRO.

FILTRO tiene tres argumentos:

=FILTER(array, include, [if_empty])

• matriz: el rango de celdas o matriz de valores para filtrar.
• Incluye: Una matriz de resultados VERDADEROS/FALDOS, donde solo los valores VERDADEROS se retienen en el filtro.
• [if_empty]: el valor que se mostrará si no se devuelven filas.

Ejemplos de uso de la función FILTRO

Los siguientes ejemplos ilustran cómo utilizar la función FILTRO.

Ejemplo 1: FILTRO devuelve una matriz de filas y columnas

En este ejemplo, la celda F3 contiene una única fórmula, pero esta fórmula devuelve una matriz de valores en las filas y columnas vecinas.

La fórmula en la celda F3 es:

=FILTER(B3:D10,C3:C10100)

Esta fórmula única devuelve 2 filas y 3 columnas de datos donde los valores en C3-C10 son superiores a 100.

Ejemplo 2: ¡#CALC! error causado por la función FILTRO

La siguiente captura de pantalla muestra lo que sucede cuando el resultado de la función FILTRO tiene cero resultados; ¡Conseguimos el #CALC! error.

La fórmula en la celda F3 es:

=FILTER(B3:D10,C3:C10200)

Como ninguna fila cumple con el criterio de que el valor de la factura sea superior a 200 , el FILTRO no puede devolver un valor, por lo que #CALC! Se muestra el error.

Afortunadamente, Microsoft nos ha proporcionado el argumento if_empty, que muestra un mensaje si no se devuelven filas.

La fórmula en la celda F3 es:

=FILTER(B3:D10,C3:C10200,"No Results")

En la captura de pantalla anterior, se muestra Sin resultados en lugar de #CALC. error.

Si quisiéramos mostrar un resultado en cada columna, podríamos incluir una matriz constante dentro del argumento if_empty. A continuación se muestra n/a en las columnas Valor de factura y Días de vencimiento .

=FILTER(B3:D10,C3:C10200,{"No Results","n/a","n/a"})

Esta fórmula daría como resultado lo siguiente:

Ejemplo 3: FILTRO se expande automáticamente cuando se vincula a una tabla

Este ejemplo muestra cómo responde la función FILTRO cuando se vincula a una tabla de Excel.

El FILTRO está configurado para mostrar elementos donde el valor de la factura es superior a 100. Los nuevos registros agregados a la tabla que cumplen con los criterios se agregan automáticamente al rango de derrame de la función. ¡Cosas increíbles!

Ejemplo 4: uso de FILTER con múltiples criterios.

El ejemplo 4 muestra cómo aplicar FILTER con múltiples criterios.

La fórmula en la celda F3 es:

=FILTER(B3:D10,(C3:C1050)*(D3:D1030))

Para cualquiera que haya utilizado la función SUMAPRODUCTO, este método de aplicar múltiples condiciones le resultará familiar. La multiplicación crea la lógica Y (es decir, todos los criterios deben ser VERDADEROS). El ejemplo anterior muestra dónde el valor de la factura es mayor que 50 y los días de vencimiento son mayores que 30.

La suma crea una lógica OR (es decir, cualquier condición individual puede ser VERDADERA).

La fórmula en la celda G3 es:

=FILTER(B3:D10,(C3:C1050)+(D3:D1030))

El ejemplo anterior muestra dónde el valor de la factura es mayor que 50 o los días de vencimiento son mayores que 30.

Ejemplo 5: uso de FILTRO para listas desplegables dinámicas dependientes

Las listas desplegables son una técnica de validación de datos. Las listas desplegables dependientes son una técnica avanzada en la que las listas cambian según el resultado de otra celda. Por ejemplo, si la primera lista desplegable muestra nombres de países, la segunda lista desplegable La lista solo debe mostrar las ciudades que existen en ese país. En Excel 2019 y antes solo existen métodos tediosos para lograr este efecto, pero la nueva función FILTRO lo hace muy fácil.

La fórmula en la celda H3 es:

=UNIQUE(B3:B10)

La función ÚNICA crea una lista única para completar el menú desplegable en la celda F4 .

La fórmula en la celda I3 es:

=FILTER(C3:C10,B3:B10=F4)

Dependiendo del valor de la celda F4 , los valores devueltos por la función FILTRO cambian. El segundo menú desplegable de la celda F6 cambia dinámicamente según el valor de la celda F4 .

Ejemplo 6: uso de FILTER con otras funciones

En este último ejemplo, FILTRO está anidado dentro de la función ORDENAR.

La fórmula en la celda F3 es:

=SORT(FILTER(B3:D10,D3:D10=30))

Primero, la función FILTRO devuelve las celdas en función de que los días de vencimiento sean menores o iguales a 30. Luego, la función CLASIFICAR coloca a los Clientes en orden alfabético ascendente.

Ejemplo 7: uso de FILTRO para mostrar elementos coincidentes de una lista

¿Cómo podemos hacer coincidir una lista de elementos que podrían tener un tamaño desconocido? No podemos seguir actualizando nuestra función FILTRO agregando y eliminando criterios. Y, si tuviéramos muchos elementos que combinar, pronto se volvería inmanejable. Entonces, veamos cómo podemos resolver esto.

En el siguiente ejemplo, la fórmula de la celda H3 devuelve solo los clientes enumerados en F3:F4 .

La fórmula en la celda H3 es:

=FILTER(B3:D10,COUNTIFS(F3:F4,B3:B10),"No results")

La función CONTAR.SI devuelve un número positivo si el elemento existe tanto en los datos como en la lista, o cero si existe solo en uno. Dado que los números positivos siempre son VERDADEROS y los ceros siempre son FALDOS, esto proporciona la lógica VERDADERO/FALSO necesaria para que la función FILTRO devuelva solo los elementos coincidentes.

NOTA: Si la lista que comienza en F3 se generara mediante otra fórmula matricial o mediante Power Query, esta solución sería completamente dinámica (eso está fuera del alcance de la publicación actual, por lo que hemos utilizado rangos estáticos para este ejemplo).

Ejemplo 8: simulación de búsqueda con comodines con FILTER

La función FILTRO no permite caracteres comodín en los criterios. Sin embargo, al utilizar una combinación de BÚSQUEDA e ESNUMERO podemos simular un efecto similar.

En el siguiente ejemplo, la fórmula de la celda H3 devuelve solo los artículos donde el nombre del cliente contiene las letras de la celda F3 .

La fórmula en la celda H3 es:

=FILTER(B3:D10,ISNUMBER(SEARCH(F3,B3:B10)),"No results")

BÚSQUEDA devuelve un número si el término de búsqueda en la celda F3 se encuentra en cada valor en B3-B10 .

ESNÚMERO devuelve VERDADERO o FALSO para cada valor dependiendo de si BÚSQUEDA devuelve un número. Este valor VERDADERO/FALSO proporciona la lógica que necesita FILTRO para devolver los elementos coincidentes.

Solo en este escenario, Milkshake Junction y Sunset Satay contienen un como subcadena, por lo tanto, solo se devuelven estos clientes.

¿Querer aprender más?

Hay mucho que aprender sobre las matrices dinámicas y las nuevas funciones. Consulte mis otras publicaciones aquí para obtener más información:

• Introducción a las matrices dinámicas : aprenda cómo ha cambiado el motor de cálculo de Excel.
• ÚNICO : para enumerar los valores únicos en un rango
• ORDENAR : para ordenar los valores en un rango
• SORTBY : para ordenar valores según el orden de otros valores
• FILTRO : para devolver solo los valores que cumplen criterios específicos
• SECUENCIA – para devolver una secuencia de números
• RANDARRAY – para devolver una matriz de números aleatorios
• Uso de matrices dinámicas con otras funciones de Excel : aprenda a usar matrices dinámicas con gráficos, tablas dinámicas, imágenes, etc.
• Técnicas avanzadas de fórmulas de matrices dinámicas : aprenda las técnicas avanzadas para gestionar matrices dinámicas

---