- Función de filtro de Excel: sintaxis
- Ejemplo 1: filtrado de datos según un criterio (región)
- Ejemplo 2: filtrado de datos según un criterio (mayor o menor que)
- Ejemplo 3: filtrado de datos con varios criterios (Y)
- Ejemplo 4: filtrado de datos con varios criterios (OR)
- Ejemplo 5: filtrado de datos para obtener registros por encima o por debajo del promedio
- Ejemplo 6: Filtrar solo los registros de números PAR (o registros de números impares)
- Ejemplo 7: ordenar los datos filtrados con fórmula
Ver video - Ejemplos de funciones de FILTRO de Excel
Office 365 incluye algunas funciones increíbles, como XLOOKUP, SORT y FILTER.
Cuando se trata de filtrar datos en Excel, en el mundo anterior a Office 365, dependíamos principalmente del filtro incorporado de Excel o, como máximo, del filtro avanzado o fórmulas complejas de SUMPRODUCT. En caso de que tuviera que filtrar una parte de un conjunto de datos, generalmente era una solución compleja (algo que he cubierto aquí).
Pero con la nueva función FILTRO, ahora es realmente fácil filtrar rápidamente parte del conjunto de datos según una condición.
Y en este tutorial, te mostraré lo increíble que es la nueva función FILTRO y algunas cosas útiles que puedes hacer con ella.
Pero antes de entrar en los ejemplos, aprendamos rápidamente sobre la sintaxis de la función FILTRO.
En caso de que desee obtener estas nuevas funciones en Excel, puede actualizar a Office 365 (únase al programa de información privilegiada para obtener acceso a todas las funciones / fórmulas)Función de filtro de Excel: sintaxis
A continuación se muestra la sintaxis de la función FILTRO:
= FILTRO (matriz, incluir, [if_empty])
- formación - este es el rango de celdas donde tiene los datos y desea filtrar algunos datos de él
- incluir - esta es la condición que le dice a la función qué registros filtrar
- [if_empty] : este es un argumento opcional en el que puede especificar qué devolver en caso de que la función FILTRO no encuentre resultados. De forma predeterminada (cuando no se especifica), devuelve el #CALC! error
Ahora echemos un vistazo a algunos ejemplos asombrosos de funciones de filtro y cosas que puede hacer que solían ser bastante complejas en su ausencia.
Haga clic aquí para descargar el archivo de ejemplo y seguir adelante
Ejemplo 1: filtrado de datos según un criterio (región)
Suponga que tiene un conjunto de datos como se muestra a continuación y desea filtrar todos los registros solo para EE. UU.
A continuación se muestra la fórmula del FILTRO que hará esto:
= FILTRO ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = "EE. UU.")
La fórmula anterior utiliza el conjunto de datos como matriz y la condición es $ B $ 2: $ B $ 11 = "EE. UU."
Esta condición haría que la función FILTRO verificara cada celda en la columna B (una que tiene la región) y solo se filtrarían aquellos registros que coincidan con este criterio.
Además, en este ejemplo, tengo los datos originales y los datos filtrados en la misma hoja, pero también puede tenerlos en hojas separadas o incluso en libros de trabajo.
La función de filtro devuelve un resultado que es una matriz dinámica (lo que significa que en lugar de devolver un valor, devuelve una matriz que se derrama a otras celdas).
Para que esto funcione, debe tener un área donde el resultado esté vacío. En cualquiera de las celdas de esta área (E2: G5 en este ejemplo) ya tiene algo, la función le dará el error #SPILL.
Además, dado que se trata de una matriz dinámica, no puede cambiar una parte del resultado. Puede eliminar todo el rango que tiene el resultado o la celda E2 (donde se ingresó la fórmula). Ambos eliminarían toda la matriz resultante. Pero no puede cambiar ninguna celda individual (o eliminarla).
En la fórmula anterior, he codificado el valor de la región, pero también puede tenerlo en una celda y luego hacer referencia a esa celda que tiene el valor de la región.
Por ejemplo, en el siguiente ejemplo, tengo el valor de la región en la celda I2 y luego se hace referencia a esto en la fórmula:
= FILTRO ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = I1)
Esto hace que la fórmula sea aún más útil y ahora simplemente puede cambiar el valor de la región 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 la selección y actualizaría instantáneamente los datos filtrados.
Ejemplo 2: filtrado de datos según un criterio (mayor o menor que)
También puede utilizar operadores comparativos dentro de la función de filtro y extraer todos los registros que sean más o menos que un valor específico.
Por ejemplo, suponga que tiene el conjunto de datos como se muestra a continuación y desea filtrar todos los registros donde el valor de ventas es superior a 10000.
La siguiente fórmula puede hacer esto:
= FILTRO ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> 10000))
El argumento de la matriz se refiere a todo el conjunto de datos y la condición, en este caso, es ($ C $ 2: $ C $ 11> 10000).
La fórmula comprueba cada registro para el valor en la columna C. Si el valor es más de 10000, se filtra, de lo contrario, se ignora.
En caso de que desee obtener todos los registros inferiores a 10000, puede utilizar la siguiente fórmula:
= FILTRO ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11 <10000))
También puede ser más creativo con la fórmula FILTER. Por ejemplo, si desea filtrar los tres registros principales en función del valor de ventas, puede utilizar la siguiente fórmula:
= FILTRO ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> = GRANDE (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, este valor se usa en los criterios de la función FILTRO para obtener todos los registros donde el valor de ventas es mayor o igual al tercer valor más grande.
Haga clic aquí para descargar el archivo de ejemplo y seguir adelante
Ejemplo 3: filtrado de datos con varios criterios (Y)
Suponga que tiene el siguiente conjunto de datos y desea filtrar todos los registros de EE. UU. Donde el valor de venta es superior a 10000.
Esta es una condición Y en la que debe verificar dos cosas: la región necesita los EE. UU. Y las ventas deben ser más de 10000. Si solo se cumple una condición, los resultados no deben filtrarse.
A continuación se muestra la fórmula de FILTRO que filtrará registros con los EE. UU. Como región y ventas de más de 10000:
= FILTRO ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "EE. UU.") * ($ C $ 2: $ C $ 11> 10000))
Tenga en cuenta que el criterio (denominado argumento de inclusión) es ($ B $ 2: $ B $ 11 = ”EE. UU.”) * ($ 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 estos dos criterios. Esto devuelve una matriz de 0 y 1, donde se devuelve un 1 solo cuando se cumplen ambas condiciones.
En caso de que no haya registros que cumplan con los criterios, la función devolvería el #CALC! error.
Y en caso de que desee devolver algo con significado (en lugar del error), puede usar una fórmula como se muestra a continuación:
= FILTRO ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "EE. UU.") * ($ C $ 2: $ C $ 11> 10000), "No se ha encontrado nada")
Aquí, he utilizado "No encontrado" como tercer argumento, que se utiliza cuando no se encuentran registros que coincidan con los criterios.
Ejemplo 4: filtrado de datos con varios criterios (OR)
También puede modificar el argumento "incluir" en la función FILTRO para verificar un criterio OR (donde cualquiera de las condiciones dadas puede ser verdadera).
Por ejemplo, suponga que tiene el conjunto de datos como se muestra a continuación y desea filtrar los registros donde el país es EE. UU. O Canadá.
A continuación se muestra la fórmula que hará esto:
= FILTRO ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "EE. UU.") + ($ B $ 2: $ B $ 11 = "Canadá"))
Tenga en cuenta que en la fórmula anterior, simplemente agregué las dos condiciones usando el operador de suma. Dado que cada una de estas condiciones devuelve una matriz de VERDADERO y FALSO, puedo agregar para obtener una matriz combinada donde es VERDADERO si se cumple alguna de las condiciones.
Otro ejemplo podría ser cuando desee filtrar todos los registros en los que el país es EE. UU. O el valor de venta es superior a 10000.
La siguiente fórmula hará esto:
= FILTRO ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "EE. UU.") + (C2: C11> 10000))
Nota: Cuando use los criterios Y en una función de FILTRO, use el operador de multiplicación (*) y cuando use los criterios O, use el operador de suma (+).
Ejemplo 5: filtrado de datos para obtener registros por encima o por debajo del promedio
Puede usar fórmulas dentro de la función FILTRO para filtrar y extraer registros donde el valor está por encima o por debajo del promedio.
Por ejemplo, suponga que tiene el conjunto de datos como se muestra a continuación y desea filtrar todos los registros donde el valor de venta está por encima del promedio.
Puedes hacerlo usando la siguiente fórmula:
= FILTRO ($ A $ 2: $ C $ 11, C2: C11> PROMEDIO (C2: C11))
Del mismo modo, por debajo del promedio, puede utilizar la siguiente fórmula:
= FILTRO ($ A $ 2: $ C $ 11, C2: C11<>
Haga clic aquí para descargar el archivo de ejemplo y seguir adelante
Ejemplo 6: Filtrar solo los registros de números PAR (o registros de números impares)
En caso de que necesite filtrar y extraer rápidamente todos los registros de filas de números pares o filas de números impares, puede hacerlo con la función FILTRO.
Para hacer esto, debe verificar el número de fila dentro de la función FILTRO y solo filtrar los números de fila que cumplan con los criterios del número de fila.
Suponga que tiene el conjunto de datos como se muestra a continuación y solo quiero extraer registros pares de este conjunto de datos.
A continuación se muestra la fórmula que hará esto:
= FILTRO ($ A $ 2: $ C $ 11, MOD (FILA (A2: A11) -1,2) = 0)
La fórmula anterior usa la función MOD para verificar el número de fila de cada registro (que viene dado por la función FILA).
La fórmula MOD (ROW (A2: A11) -1,2) = 0 devuelve VERDADERO cuando el número de fila es par y FALSO cuando es impar. Tenga en cuenta que he restado 1 de la parte FILA (A2: A11) ya que el primer registro está en la segunda fila, y esto ajusta el número de fila para considerar la segunda fila como el primer registro.
Del mismo modo, puede filtrar todos los registros impares utilizando la siguiente fórmula:
= FILTRO ($ A $ 2: $ C $ 11, MOD (FILA (A2: A11) -1,2) = 1)
Ejemplo 7: ordenar los datos filtrados con fórmula
El uso de la función FILTRO con otras funciones nos permite hacer mucho más.
Por ejemplo, si filtra un conjunto de datos usando la función FILTRO, puede usar la función CLASIFICAR con él para obtener el resultado que ya está ordenado.
Suponga que tiene un conjunto de datos como se muestra a continuación y desea filtrar todos los registros donde el valor de ventas es superior a 10000. Puede usar la función CLASIFICAR con la función para asegurarse de que los datos resultantes estén ordenados según el valor de ventas.
La siguiente fórmula hará esto:
= CLASIFICAR (FILTRO ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> 10000)), 3, -1)
La función anterior usa la función FILTRO para obtener los datos donde el valor de venta en la columna C es más de 10000. Esta matriz devuelta por la función FILTRO se usa luego dentro de la función CLASIFICAR para ordenar estos datos según el valor de ventas.
El segundo argumento de la función CLASIFICAR es 3, que se clasifica según la tercera columna. Y el cuarto argumento es -1, que sirve para ordenar estos datos en orden descendente.
Haga clic aquí para descargar el archivo de ejemplo
Entonces, estos son 7 ejemplos para usar la función FILTRO en Excel.
¡Espero que hayas encontrado útil este tutorial!
También le pueden gustar los siguientes tutoriales de Excel:
- Cómo filtrar celdas con formato de fuente en negrita en Excel
- Cuadro de búsqueda de filtro dinámico de Excel
- Cómo filtrar datos en una tabla dinámica en Excel
