¿Te imaginas tener el poder de transformar grandes volúmenes de datos en información clara y concisa al instante? Con la función FILTRO de Excel, ese sueño se convierte en realidad. En este artículo, te llevaremos a través de un viaje fascinante por el mundo del filtrado dinámico utilizando fórmulas, una herramienta que no solo optimiza tus hojas de cálculo, sino que también te proporciona un control sin precedentes sobre tus datos. Descubre cómo esta potente función puede revolucionar tu forma de trabajar, permitiéndote extraer la información que realmente necesitas de manera rápida y efectiva. ¡Prepárate para desvelar el potencial oculto de tus datos y llevar tus habilidades en Excel al siguiente nivel!
Si eres usuario de Excel y te encanta aprovechar al máximo todas sus funciones, entonces esta guía es para ti. En este artículo te presentamos la increíble función FILTRO de Excel, una herramienta poderosa que te permitirá realizar filtrados dinámicos utilizando fórmulas. ¡Prepárate para descubrir cómo puedes agilizar tus procesos de filtrado y ahorrar tiempo y esfuerzo en tus tareas diarias! Sigue leyendo para conocer todos los detalles y sacarle el máximo provecho a esta útil función de Excel.
En esta rápida lección, aprenderá cómo filtrar en Excel dinámicamente con fórmulas. Ejemplos para filtrar duplicados, celdas que contienen cierto texto, con múltiples criterios y más.
¿Cómo se suele filtrar en Excel? En su mayor parte, mediante el uso de Filtro automático y, en escenarios más complejos, con Filtro avanzado. Al ser rápidos y potentes, estos métodos tienen un inconveniente importante: no se actualizan automáticamente cuando cambian los datos, lo que significa que tendría que limpiar y filtrar nuevamente. La introducción de la función FILTRO en Excel 365 se convierte en una alternativa muy esperada a las funciones convencionales. A diferencia de ellas, las fórmulas de Excel se recalculan automáticamente con cada cambio en la hoja de cálculo, por lo que necesitarás configurar el filtro solo una vez.
Fórmula básica de FILTRO de Excel
Para empezar, analicemos un par de casos muy simples sólo para comprender mejor cómo funciona una fórmula de Excel para filtrar datos.
Del siguiente conjunto de datos, supongamos que desea extraer los registros con un valor específico en el Grupocolumna, digamos grupo C. Para hacerlo, proporcionamos la expresión B2:B13=»C» al incluir argumento, que producirá una matriz booleana requerida, donde VERDADERO corresponde a los valores «C».
=FILTER(A2:C13, B2:B13=»C», «No results»)
En la práctica, es más conveniente ingresar los criterios en una celda separada, por ejemplo, F1, y usar una referencia de celda en lugar de codificar el valor directamente en la fórmula:
=FILTER(A2:C13, B2:B13=F1, «No results»)
A diferencia de la función Filtro de Excel, la función no realiza ningún cambio en los datos originales. Extrae los registros filtrados en el llamado rango de derrame (E4:G7 en la captura de pantalla siguiente), comenzando en la celda donde se ingresa la fórmula:
>
Si ningún registro coincide con los criterios especificados, la fórmula devuelve el valor que usted puso en el si_vacío argumento, «Sin resultados» en este ejemplo:
>
Si prefieres no devolver nada en este caso, proporcione una cadena vacía («») para el último argumento:
=FILTER(A2:C13, B2:B13=F1, «»)
En caso de que tus datos estén organizados horizontalmente de izquierda a derecha como se muestra en la captura de pantalla siguiente, la función FILTRO también funcionará bien. Sólo asegúrese de definir rangos apropiados para el formación y incluir argumentos, de modo que la matriz fuente y la matriz booleana tengan el mismo ancho:
=FILTER(B2:M4, B3:M3= B7, «No results»)
>
Función FILTRO de Excel: notas de uso
Para filtrar eficazmente en Excel con fórmulas, aquí hay un par de puntos importantes a tener en cuenta:
- La función FILTRO derrama automáticamente los resultados vertical u horizontalmente en la hoja de trabajo, dependiendo de cómo estén organizados los datos originales. Por lo tanto, asegúrese de tener siempre suficientes celdas vacías abajo y a la derecha; de lo contrario, obtendrá un #ErrorDERRAME.
- Los resultados de la función FILTRO de Excel son dinámicos, lo que significa que se actualizan automáticamente cuando cambian los valores del conjunto de datos original. Sin embargo, la gama suministrada para el formación El argumento no se actualiza cuando se agregan nuevas entradas a los datos de origen. Si deseas el formación para cambiar el tamaño automáticamente, luego conviértala en una tabla de Excel y cree fórmulas con referencias estructuradas, o cree un rango dinámico con nombre.
Cómo filtrar en Excel: ejemplos de fórmulas
Ahora que sabe cómo funciona una fórmula de filtro básica de Excel, es hora de obtener algunas ideas sobre cómo podría ampliarse para resolver tareas más complejas.
Filtrar con múltiples criterios (lógica Y)
Para filtrar datos con múltiples criterios, proporcione dos o más expresiones lógicas para el incluir argumento:
FILTRO (matriz, (rango1=criterios1) * (rango2=criterios2), «No hay resultados»)
La operación de multiplicación procesa las matrices con el Y lógicaasegurando que sólo los registros que cumplan todos los criterios son devueltos. Técnicamente, funciona de esta manera:
El resultado de cada expresión lógica es un arreglo de valores booleanos, donde VERDADERO equivale a 1 y FALSO a 0. Luego, se multiplican los elementos de todos los arreglos en las mismas posiciones. Dado que multiplicar por cero siempre da cero, solo los elementos para los cuales todos los criterios son VERDADEROS entran en la matriz resultante y, en consecuencia, solo se extraen esos elementos.
Los siguientes ejemplos muestran esta fórmula genérica en acción.
Ejemplo 1. Filtrar varias columnas en Excel
Ampliando nuestro básico Fórmula de FILTRO de Excel un poco más adelante, filtremos los datos por dos columnas: Grupo (columna B) y gana (columna C).
Para ello, configuramos los siguientes criterios: escriba el nombre del grupo objetivo en F2 (criterios1) y el número mínimo requerido de victorias en F3 (criterios2).
Dado que nuestros datos fuente están en A2:C13 (formación), los grupos están en B2:B13 (rango1) y las victorias están en C2:C13 (rango2), la fórmula toma esta forma:
=FILTER(A2:C13, (B2:B13=F2) * (C2:C13>=F3), «No results»)
Como resultado, obtienes una lista de jugadores del grupo A que han conseguido 2 o más victorias:
>Ejemplo 2. Filtrar datos entre fechas
En primer lugar, cabe señalar que no es posible crear una fórmula genérica para filtrar por fecha en Excel. En diferentes situaciones, necesitará crear criterios de manera diferente, dependiendo de si desea filtrar por una fecha específica, por mes o por año. El propósito de este ejemplo es demostrar el enfoque general.
A nuestros datos de muestra, agregamos una columna más que contiene las fechas de la última victoria (columna D). Y ahora extraeremos las victorias que ocurrieron en un período específico, digamos entre el 17 y el 31 de mayo.
Tenga en cuenta que en este caso, ambos criterios se aplican al mismo rango:
=FILTER(A2:D13, (D2:D13>=G2) * (D2:D13<=G3), «No results»)
Donde G2 y G3 son las fechas entre las que filtrar.
>Filtrar con múltiples criterios (lógica O)
Para extraer datos basados en múltiples condiciones OR, también usa expresiones lógicas como las que se muestran en los ejemplos anteriores, pero en lugar de multiplicarlas, las suma. Cuando se suman las matrices booleanas devueltas por las expresiones, la matriz resultante tendrá 0 para las entradas que no cumplan ningún criterio (es decir, todos los criterios son FALDOS) y dichas entradas se filtrarán. Se extraerán las entradas para las cuales al menos un criterio sea VERDADERO.
Aquí está la fórmula genérica para filtrar columnas con la lógica OR:
FILTRO (matriz, (rango1=criterios1) + (rango2=criterios2), «No hay resultados»)
Como ejemplo, extraigamos una lista de jugadores que tienen esta o aquella número de victorias.
Suponiendo que los datos de origen están en A2:C13, las ganancias están en C2:C13 y los números de ganancias de interés están en F2 y F3, la fórmula sería la siguiente:
=FILTER(A2:C13, (C2:C13=F2) + (C2:C13=F3), «No results»)
Como resultado, sabes qué jugadores han ganado todos los juegos (4) y cuáles no han ganado ninguno (0):
>Filtrado basado en múltiples criterios AND y OR
En una situación en la que necesite aplicar ambos tipos de criterios, recuerde esta sencilla regla: junte los criterios Y con un asterisco
y criterios OR con el signo más (+).
=FILTER(A2:C13, (C2:C13=F2) * ((B2:B13=E2) + (B2:B13=E3)), «No results»)
Por ejemplo, para devolver una lista de jugadores que tienen un número determinado de victorias (F2) Y pertenecen al grupo mencionado en E2 O E3, cree la siguiente cadena de expresiones lógicas:
>Filtrado de datos basado en múltiples criterios AND y OR
Cómo filtrar duplicados en Excel
Cuando se trabaja con hojas de trabajo enormes o se combinan datos de diferentes fuentes, a menudo existe la posibilidad de que se cuelen algunos duplicados. Si estas buscando filtrar duplicados
y extraiga elementos únicos, luego use la función ÚNICA como se explica en el tutorial vinculado anteriormente. Si tu objetivo esfiltrar duplicados
es decir, extraiga las entradas que ocurren más de una vez y luego use la función FILTRO junto con CONTAR.SI. La idea es obtener los recuentos de ocurrencias de todos los registros y extraer los mayores a 1. Para obtener los recuentos, proporcione el mismo rango para cada uno. Rango de criterio / criterios
par de CONTAR.SI como este:FILTRAR(formaciónCUENTASI(columna1 , columna1,columna2 ,columna2
)>1, «Sin resultados»)
=FILTER(A2:C20, COUNTIFS(A2:A20, A2:A20, B2:B20, B2:B20, C2:C20, C2:C20)>1, «No results»)
>
Una fórmula para filtrar duplicados en Excel Consejo. Para filtrar duplicados según los valores en elcolumnas clave
incluya solo esas columnas específicas en la función CONTAR.SI.
Cómo filtrar espacios en blanco en Excel Una fórmula para filtrar celdas en blanco es, de hecho, una variación de laFórmula FILTRO de Excel con múltiples criterios Y
. En este caso, verificamos si todas las columnas (o determinadas) contienen datos y excluimos las filas donde al menos una celda está vacía. Para identificar celdas que no están en blanco, utilice el operador «no igual a» (<>) junto con una cadena vacía («») como esta:FILTRO (matriz, (columna1<>«») * (columna2
=<>«»), «Sin resultados»)
>Una fórmula para filtrar celdas en blanco
Filtrar celdas que contienen texto específico
Para extraer celdas que contienen cierto texto, puedes usar la función FILTRO junto con la clásica fórmula Si la celda contiene:FILTRO(matriz, ESNÚMERO(BUSCAR(«texto «,rango
)), «No hay resultados»)
- Así es como funciona:
- La función BÚSQUEDA busca una cadena de texto especificada en un rango determinado y devuelve un número (la posición del primer carácter) o #¡VALOR! error (texto no encontrado). La función ESNÚMERO convierte todos los números a VERDADERO y los errores a FALSO y pasa la matriz booleana resultante al incluir
argumento de la función FILTRO. Para este ejemplo, hemos agregado el Apellidos
=FILTER(A2:D13, ISNUMBER(SEARCH(G2, B2:B13)), «No results»)
de jugadores en B2:B13, escribimos la parte del nombre que queremos encontrar en G2, y luego usamos la siguiente fórmula para filtrar los datos:
>Una fórmula para filtrar celdas que contienen texto específico
Filtrar y calcular (Suma, Promedio, Mín, Máx, etc.)
Lo interesante de la función FILTRO de Excel es que no sólo puede extraer valores con condiciones, sino también resumir los datos filtrados. Para ello, combine FILTER con funciones de agregación como SUM, AVERAGE, COUNT, MAX o MIN.
Por ejemplo, para agregar datos para un grupo específico en F1, utilice las siguientes fórmulas:
=SUM(FILTER(C2:C13, B2:B13=F1, 0))
Victorias totales:
=AVERAGE(FILTER(C2:C13, B2:B13=F1, 0))
Victorias promedio:
=MAX(FILTER(C2:C13, B2:B13=F1, 0))
Victorias máximas:
=MIN(FILTER(C2:C13, B2:B13=F1, 0))
Victorias mínimas: Por favor preste atención que, en todas las fórmulas, utilizamos cero para el si_vacío
>Fórmulas para calcular datos filtrados
Fórmula FILTRO que distingue entre mayúsculas y minúsculas Una fórmula FILTRO de Excel estándar no distingue entre mayúsculas y minúsculas, lo que significa que no hace distinción entre caracteres en minúsculas y mayúsculas. Para distinguir entre mayúsculas y minúsculas, anide la función EXACTA en el incluir
argumento. Esto obligará a FILTER a realizar pruebas lógicas distinguiendo entre mayúsculas y minúsculas:FILTRO (matriz, EXACTO (rango ,criterios
), «No hay resultados») Supongamos que tienes ambos grupos. A y a
=FILTER(A2:C13, EXACT(B2:B13, «a»), «No results»)
y desea extraer registros donde el grupo es la «a» minúscula. Para hacerlo, use la siguiente fórmula, donde A2:C13 son los datos de origen y B2:B13 son los grupos a filtrar:
=FILTER(A2:C13, EXACT(B2:B13, F1), «No results»)
>
Fórmula FILTRO que distingue entre mayúsculas y minúsculas
Cómo FILTRAR datos y devolver solo columnas específicas
En su mayor parte, lo que quieren los usuarios de Excel es filtrar todas las columnas con una única fórmula. Pero si su tabla de origen contiene decenas o incluso cientos de columnas, es posible que desee limitar los resultados a algunas de las más importantes.
Ejemplo 1. Filtrar algunas columnas adyacentes En una situación en la que desee que aparezcan algunas columnas vecinas en un resultado de FILTRO, incluya solo esas columnas en formación
porque es este argumento el que determina qué columnas devolver.En el ejemplo básico de la fórmula FILTRO, supongamos que desea devolver las primeras 2 columnas ( Nombre yGrupo ). Entonces, usted proporciona A2:B13 para el formación
=FILTER(A2:B13, B2:B13=F1, «No results»)
argumento:
>Fórmula FILTRAR para devolver resultados de ciertas columnas adyacentes
Ejemplo 2. Filtrar columnas no adyacentes
argumento, donde VERDADERO (1) marca las columnas que se conservarán y FALSO (0) marca las columnas que se excluirán. Por ejemplo, para devolver sólo Nombres(1 calle columna) y gana(3 tercero columna), estamos usando {1,0,1} o {VERDADERO, FALSO, VERDADERO} para incluir
=FILTER(FILTER(A2:C13, B2:B13=F1), {1,0,1})
argumento de la función FILTRO exterior:
=FILTER(FILTER(A2:C13, B2:B13=F1), {TRUE,FALSE,TRUE})
>
Fórmula FILTRAR para devolver columnas no adyacentes
Cómo limitar el número de filas devueltas por la función FILTRO
Si su fórmula FILTRO encuentra muchos resultados, pero su hoja de trabajo tiene espacio limitado y no puede eliminar los datos a continuación, entonces puede limitar el número de filas que devuelve la función FILTRO.
=FILTER(A2:C13, B2:B13=F1)
Veamos cómo funciona con un ejemplo de una fórmula simple que atrae jugadores del grupo objetivo en F1:
- La fórmula anterior genera todos los registros que encuentra, 4 filas en nuestro caso. Pero supongamos que sólo tienes espacio para dos. Para generar solo las 2 primeras filas encontradas, esto es lo que debe hacer: Conecte la fórmula FILTRO en el formación
- argumento de la función ÍNDICE. Para el número_fila
- argumento de ÍNDICE, utilice una constante de matriz vertical como {1;2}. Determina cuántas filas devolver (2 en nuestro caso). Para el núm_columna
- argumento, utilice una constante de matriz horizontal como {1,2,3}. Especifica qué columnas devolver (las primeras 3 columnas en este ejemplo).
Para solucionar posibles errores cuando no se encuentran datos que coincidan con sus criterios, puede ajustar su fórmula en la función SIERROR.
=IFERROR(INDEX(FILTER(A2:C13, B2:B13=F1), {1;2}, {1,2,3}), «No result»)
>Limitar el número de filas devueltas por la función FILTRO
=IFERROR(INDEX(FILTER(A2:C13, B2:B13=F1), SEQUENCE(2), SEQUENCE(1, COLUMNS(A2:C13))), «No result»)
Cuando se trabaja con tablas grandes, escribir constantes de matriz manualmente puede resultar bastante engorroso. No hay problema, la función SECUENCIA puede generar los números secuenciales automáticamente:
La primera SECUENCIA genera una matriz vertical que contiene tantos números secuenciales como se especifica en el primer (y único) argumento. La segunda SECUENCIA usa la función COLUMNAS para contar el número de columnas en el conjunto de datos y produce una matriz horizontal equivalente. Consejo. Para devolver datos decolumnas específicas no todas las columnas, en la constante de matriz horizontal que usa para el núm_columna
argumento de ÍNDICE, incluya solo esos números específicos. Por ejemplo, para extraer datos de la primera y tercera columnas, utilice {1,3}.
Así es como archivar datos en Excel dinámicamente. ¡Te agradezco por leer y espero verte en nuestro blog la próxima semana!
Función FILTRO de Excel: filtrado dinámico con fórmulas
Función FILTRO de Excel: filtrado dinámico con fórmulas
La función FILTRO es una de las poderosas herramientas que nos ofrece Excel para realizar filtrados dinámicos mediante fórmulas. Esta función nos permite filtrar datos de manera más rápida y eficiente, sin necesidad de utilizar la función de filtro estándar que se encuentra en la barra de herramientas.
¿Qué es la función FILTRO?
La función FILTRO es una función incorporada en Excel que nos permite filtrar datos de una manera más avanzada y dinámica utilizando fórmulas. A diferencia del filtro estándar, que solo nos permite filtrar por un valor o una condición a la vez, la función FILTRO nos brinda la posibilidad de aplicar múltiples criterios en el filtrado de datos.
¿Cómo utilizar la función FILTRO?
Para utilizar la función FILTRO en Excel, simplemente sigue los siguientes pasos:
La función FILTRO devolverá un conjunto de datos filtrados que cumplen con las condiciones especificadas. Estos resultados se actualizarán automáticamente cada vez que se produzcan cambios en los datos de origen.
¿Para qué se puede utilizar la función FILTRO?
La función FILTRO puede ser utilizada en una amplia variedad de casos, como por ejemplo:
- Filtrar datos por múltiples criterios como fechas, valores numéricos, o texto.
- Realizar seguimiento de inventario o ventas, obteniendo solo los valores que cumplen con ciertos criterios establecidos.
- Analizar datos complejos y obtener subconjuntos específicos para realizar cálculos.
Para obtener más información sobre la función FILTRO en Excel, puedes visitar los siguientes recursos:
¡Esperamos que esta guía te haya sido útil para comprender y utilizar la función FILTRO de Excel!
[automatic_youtube_gallery type=»search» search=»Función FILTRO de Excel: filtrado dinámico con fórmulas» limit=»1″]
La función FILTER de Excel es una herramienta muy útil para realizar filtrados dinámicos en tus hojas de cálculo. Te permite extraer datos que cumplen ciertos criterios sin la necesidad de utilizar los filtros tradicionales de Excel. Vamos a desglosar los puntos clave sobre cómo utilizar FILTER con diferentes criterios y operaciones lógicas.
Filtrar con Múltiples Criterios AND
Para extraer datos que cumplen con múltiples condiciones en AND (es decir, todos los criterios deben ser verdaderos), utilizas el operador . Por ejemplo, si tienes una tabla de datos (A2:D13) y quieres filtrar las victorias que ocurrieron en un rango de fechas específico en la columna D, la fórmula sería:
excel
=FILTER(A2:D13, (D2:D13>=G2) (D2:D13<=G3), "No results")
Aquí, G2 y G3 contienen las fechas de inicio y fin.
Filtrar con Múltiples Criterios OR
Si en cambio quieres filtrar datos basados en múltiples condiciones OR (es decir, al menos uno de los criterios debe ser verdadero), usas el operador +. Por ejemplo, para extraer jugadores que tienen un número específico de victorias:
excel
=FILTER(A2:C13, (C2:C13=F2) + (C2:C13=F3), "No results")
Combinar AND y OR
Puedes combinar ambos tipos de criterios. Por ejemplo, para obtener jugadores con un número determinado de victorias y que pertenezcan a un grupo específico:
excel
=FILTER(A2:C13, (C2:C13=F2) ((B2:B13=E2) + (B2:B13=E3)), "No results")
Filtrar Duplicados
Para extraer entradas duplicadas, puedes usar FILTER junto con COUNTIFS. Aquí tienes cómo hacerlo:
excel
=FILTER(A2:C20, COUNTIFS(A2:A20, A2:A20, B2:B20, B2:B20, C2:C20, C2:C20)>1, "No results")
Filtrar Espacios en Blanco
Para filtrar celdas que no estén en blanco, usarías:
excel
=FILTER(A2:D13, (A2:A13<>"") (B2:B13<>"") * (C2:C13<>""), "No results")
Filtrar Texto Específico
Para filtrar celdas que contengan un texto específico:
excel
=FILTER(A2:D13, ISNUMBER(SEARCH(G2, B2:B13)), "No results")
Resumir Datos Filtrados
Puedes combinar FILTER con funciones como SUM, AVERAGE, MAX, etc.:
excel
=SUM(FILTER(C2:C13, B2:B13=F1, 0)) ' Suma de las victorias
=AVERAGE(FILTER(C2:C13, B2:B13=F1, 0)) ' Promedio de las victorias
Distinguir entre Mayúsculas y Minúsculas
Para hacer que FILTER distinga entre mayúsculas y minúsculas:
excel
=FILTER(A2:C13, EXACT(B2:B13, F1), "No results")
Devolver Solo Columnas Específicas
Para devolver solo ciertas columnas de los resultados filtrados, puedes usar:
- Columnas Adyacentes:
excel
=FILTER(A2:B13, B2:B13=F1, "No results")
- Columnas No Adyacentes:
excel
=FILTER(FILTER(A2:C13, B2:B13=F1), {1,0,1})
Limitar el Número de Filas Devueltas
Si necesitas limitar la cantidad de filas que devuelve FILTER:
excel
=IFERROR(INDEX(FILTER(A2:C13, B2:B13=F1), SEQUENCE(2), SEQUENCE(1, COLUMNS(A2:C13))), "No result")
Esto es un resumen de cómo se usa la función FILTER en Excel. Puedes aplicar todas estas estrategias dependiendo de las necesidades específicas que tengas al trabajar con tus datos. Si necesitas ejemplos más específicos o ayuda adicional con fórmulas, ¡no dudes en preguntar!
1 comentario en «Función FILTRO de Excel: filtrado dinámico con fórmulas»
Deja un comentario
Lo siento, debes estar conectado para publicar un comentario.
¡Increíble artículo! La función FILTRO de Excel me ha salvado la vida en mis últimos proyectos. Recuerdo que antes pasaba horas buscando datos a mano, pero desde que empecé a usar esta fórmula, todo es mucho más rápido y fácil. Sin duda, ¡es un must para todos los que trabajamos con hojas de cálculo! – liam