Cómo usar la función FILTRO de Excel

Office 365 trae algunas funciones excelentes como XLOOKUP, SORT y FILTER.

Al filtrar datos en Excel, en el mundo anterior a Office 365, confiábamos principalmente en los filtros integrados de Excel o, como máximo, en filtros avanzados o fórmulas complejas de SUMPRODUCT.Esto suele ser una solución complicada si tiene que filtrar parte del conjunto de datos.

Pero con la nueva función FILTRO, ahora es muy fácil filtrar rápidamente partes de un conjunto de datos según las condiciones.

En este tutorial, le mostraré lo increíble que es la nueva función FILTRO y algunas cosas útiles que puede hacer con ella.

Pero antes de entrar en el ejemplo, echemos un vistazo rápido a la sintaxis de la función FILTRO.

Si desea obtener estas nuevas características en Excel, puedeActualizar a Office 365(Únase al programa interno para acceder a todas las funciones/fórmulas)

Función de filtro de Excel: sintaxis

La siguiente es la sintaxis de la función FILTRO:

=FILTRO(matriz,incluir,[si_vacío])

• matriz - este es el rango de celdas donde tiene los datos y desea filtrar algunos datos de ellos
• incluir - Esta es la condición que le dice a la función qué registros filtrar
• [si_vacío] – Este es un parámetro opcional donde puede especificar qué devolver si la función FILTRO no encuentra resultados.Por defecto (cuando no se especifica) devuelve #CALC!Error

Ahora veamos algunos ejemplos sorprendentes de funciones de filtro y lo que puede hacer que solía ser muy complicado sin él.

Ejemplo 1: filtrar datos según una condición (región)

Suponga que tiene un conjunto de datos como el que se muestra a continuación y solo desea filtrar todos los registros en los Estados Unidos.

Aquí está la fórmula de FILTRO que hace esto:

= FILTRO ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = "EE. UU.")

La fórmula anterior toma el conjunto de datos como una matriz y la condición es $B$2:$B$11=”US”

Esta condición hará que la función FILTRO examine cada celda en la columna B (celdas que tienen el rango) y filtre solo aquellos registros que coincidan con esta condición.

Además, en este ejemplo, puse los datos originales y filtrados en la misma hoja, pero también puede ponerlos en hojas separadas o incluso en libros de trabajo.

La función de filtro devuelve una matriz dinámica de resultados (lo que significa que, en lugar de devolver un valor, devuelve una matriz que se desborda en otras celdas).

Para esto, necesita tener un área donde el resultado esté vacío.Ya hay algo en cualquier celda del rango (E2:G5 en este ejemplo) y la función le dará el error #SPILL.

Además, dado que se trata de una matriz dinámica, no puede cambiar parte del resultado.Puede eliminar todo el rango con el resultado o la celda E2 (donde se ingresa la fórmula).Ambos eliminarán toda la matriz de resultados.Pero no puede cambiar ninguna celda individual (o eliminarla).

En la fórmula anterior, codifiqué el valor del rango, pero también puede colocarlo en una celda y hacer referencia a esa celda con el valor del rango.

Por ejemplo, en el siguiente ejemplo, tengo el valor del rango en la celda I2 y luego hago referencia a él en la fórmula:

=FILTER($A$2:$C$11,$B$2:$B$11=I1)

Esto hace que la fórmula sea aún más útil, ahora solo necesita cambiar el valor del rango en la celda I2 y el filtro cambiará automáticamente.

También puede tener un menú desplegable en la celda I2 donde simplemente puede hacer una selección y actualizará instantáneamente los datos filtrados.

Ejemplo 2: filtrar datos según un criterio (mayor o menor que)

También puede usar operadores de comparación en funciones de filtro y extraer todos los registros mayores o menores que un cierto valor.

Por ejemplo, suponga que tiene el conjunto de datos que se muestra a continuación y desea filtrar todos los registros con ventas superiores a 10000.

La siguiente fórmula puede hacer esto:

=FILTER($A$2:$C$11,($C$2:$C$11>10000))

El argumento de matriz se refiere a todo el conjunto de datos, en este caso la condición ($C$2:$C$11>10000).

La fórmula verifica cada registro para el valor en la columna C.Si el valor es mayor a 10000, se filtra, de lo contrario se ignora.

Si desea obtener todos los registros inferiores a 10000, puede utilizar la siguiente fórmula:

=FILTER($A$2:$C$11,($C$2:$C$11<10000))

También puedes ser más creativo con la fórmula FILTER.Por ejemplo, si quisiera filtrar los tres registros principales según las ventas, podría usar la siguiente fórmula:

=FILTER($A$2:$C$11,($C$2:$C$11>=LARGE(C2:C11,3)))

La fórmula anterior usa la función GRANDE para obtener el tercer valor más grande en el conjunto de datos.Luego use ese valor en una condición de función FILTER para obtener todos los registros con ventas mayores o iguales al tercer valor más grande.

Ejemplo 3: Filtrado de datos usando múltiples condiciones (AND)

Suponga que tiene el siguiente conjunto de datos y desea filtrar todos los registros en los Estados Unidos con un valor de ventas superior a 10000 XNUMX.

Esta es una condición Y, debe verificar dos cosas: la región debe estar en los EE. UU. y las ventas deben ser superiores a 10000.Si solo se cumple una condición, los resultados no deben filtrarse.

Esta es la fórmula de filtro que filtrará los registros con EE. UU. como región y con más de 10000 XNUMX ventas:

=FILTER($A$2:$C$11,($B$2:$B$11="US")*($C$2:$C$11>10000))

請注意，標準（稱為包含參數）是 ($B$2:$B$11=”US”)*($C$2:$C$11>10000)

Como estoy usando dos condiciones y necesito que ambas sean verdaderas, he usado el operador de multiplicación para combinar las dos condiciones.Esto devuelve una matriz de 0 y 1, donde solo se devuelve 1 si se cumplen ambas condiciones.

Si no hay registros coincidentes, la función devolverá #CALC!Error.

Si desea devolver algo significativo (no un error), puede usar una fórmula como esta:

=FILTER($A$2:$C$11,($B$2:$B$11="USA")*($C$2:$C$11>10000),"Nothing Found")

Aquí, he usado "no encontrado" como el tercer parámetro, que se usa cuando no se encuentra ningún registro coincidente.

Ejemplo 4: Filtrado de datos usando múltiples criterios (OR)

También puede modificar el parámetro "contiene" en la función FILTRO para verificar las condiciones OR (donde cualquier condición dada puede ser verdadera).

Por ejemplo, supongamos que tiene el conjunto de datos que se muestra a continuación y desea filtrar registros donde el país es Estados Unidos o Canadá.

Aquí está la fórmula para hacer esto:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+($B$2:$B$11="Canada"))

Tenga en cuenta que en la fórmula anterior, solo estoy agregando las dos condiciones usando el operador de suma.Dado que cada una de estas condiciones devuelve una matriz de VERDADERO y FALSO, puedo agregar una matriz combinada que será VERDADERA si se cumple cualquiera de las condiciones.

Otro ejemplo podría ser cuando desea filtrar todos los registros donde el país es Estados Unidos o el valor de ventas es superior a 10000.

La siguiente fórmula hará esto:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+(C2:C11>10000))

NOTA: Cuando use la condición AND en la función FILTER, use el operador de multiplicación (*), y cuando use la condición OR, use el operador de suma (+).

Ejemplo 5: filtrar datos para registros por encima o por debajo del promedio

Puede usar fórmulas en la función FILTRO para filtrar y extraer registros con valores por encima o por debajo del promedio.

Por ejemplo, supongamos que tiene el conjunto de datos que se muestra a continuación y desea filtrar todos los registros con un valor de ventas superior al promedio.

Puedes hacerlo con la siguiente fórmula:

=FILTER($A$2:$C$11,C2:C11>AVERAGE(C2:C11))

Nuevamente, por debajo del promedio, puede usar la siguiente fórmula:

=FILTER($A$2:$C$11,C2:C11<AVERAGE(C2:C11))

Ejemplo 6: filtrar solo registros pares (o registros impares)

Si necesita filtrar y extraer rápidamente todos los registros en filas pares o impares, puede usar la función FILTRO para hacerlo.

Para hacer esto, debe verificar el número de línea en la función FILTRO y filtrar solo los números de línea que coincidan con la condición del número de línea.

Supongamos que tiene un conjunto de datos como el siguiente y solo quiero extraer incluso registros de este conjunto de datos.

Aquí está la fórmula para hacer esto:

=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=0)

La fórmula anterior usa la función MOD para verificar el número de fila (dado por la función FILA) de cada registro.

La fórmula MOD(FILA(A2:A11)-1,2)=0 devuelve VERDADERO si el número de fila es par y FALSO si es impar.Tenga en cuenta que resté 2 de la parte FILA (A11: A1) porque el primer registro está en la segunda fila, lo que ajusta los números de fila para tratar la segunda fila como el primer registro.

Asimismo, puede filtrar todos los registros impares con la siguiente fórmula:

=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=1)

Ejemplo 7: Ordenar datos filtrados usando una fórmula

Usar la función FILTRO con otras funciones nos permite hacer más.

Por ejemplo, si usa la función FILTER para filtrar un conjunto de datos, puede usar la función SORT para obtener resultados ordenados.

Suponga que tiene un conjunto de datos como el que se muestra a continuación y desea filtrar todos los registros con ventas superiores a 10000.Puede utilizar la función CLASIFICAR con esta función para asegurarse de que los datos resultantes se clasifiquen según las ventas.

La siguiente fórmula hará esto:

=SORT(FILTER($A$2:$C$11,($C$2:$C$11>10000)),3,-1)

La función anterior usa la función FILTRO para obtener los datos en la columna C con ventas superiores a 10000. Luego use la matriz devuelta por la función FILTRO en la función ORDENAR para ordenar los datos según las ventas.

El segundo parámetro en la función ORDENAR es 3, que es para ordenar según la tercera columna.El cuarto parámetro es -1, que ordena los datos en orden descendente.

Entonces, estos son 7 ejemplos del uso de la función FILTRO en Excel.

Espero que hayas encontrado útil este tutorial..