Cómo FILTRAR por una lista en Excel (incluidas varias listas)
Hace unas semanas, escribí una publicación sobre cómo filtrar por una lista en Power Query . No pasó mucho tiempo para que alguien preguntara si lo mismo es posible en Excel. ¡La respuesta es sí! Entonces, eso es lo que estamos viendo en esta publicación: cómo usar la función FILTRO de Excel basada en una lista.
Nota: La solución de esta publicación solo funciona en Excel 2021 y Excel 365.
Tabla de contenido
- Comprender la función FILTRO
- Calcular el valor VERDADERO/FALSO
- FILTRAR basado en una lista
- FILTRAR por elementos que NO están en la lista
- FILTRAR basado en múltiples listas
- Y condición (incluido en todas las listas)
- O condición (incluida en cualquier lista)
- Conclusión
Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.
Nombre de archivo: 0146 Filtrar por una lista.xlsx
Tener acceso
Comencemos mirando la función FILTRO. FILTER tiene una sintaxis simple con solo tres argumentos:
=FILTER(array, include, [if_empty])- matriz : el rango de celdas o matriz de valores para filtrar.
- incluir : 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.
El argumento de inclusión es donde debemos centrar nuestra atención en los requisitos actuales.
Para cada fila de los datos de origen, debe haber un cálculo que devuelva VERDADERO o FALSO. Sólo los valores VERDADEROS se retienen en el resultado del FILTRO.
Por lo tanto, la pregunta crítica es: ¿Cómo podemos crear un resultado VERDADERO/FALSO para cada elemento? Vamos a averiguar.
¿Quieres saber más sobre la función FILTRO? Consulte esta publicación: Función FILTRO en Excel (Cómo + 8 ejemplos)
Calcular el valor VERDADERO/FALSO
Para calcular VERDADERO o FALSO, podríamos usar la función CONTAR.SI:
La fórmula en la celda I4 es:
=COUNTIFS(ItemList[Item],Data[Item])Esto consiste en tomar los valores en la tabla ItemList y contar cuántas veces aparecen para cada fila de la tabla de Datos . Esta función utiliza matrices dinámicas y derrama los resultados.
Quizás estés pensando que no son valores VERDADEROS o FALDOS sino que son 0 y 1.
Pero lo mejor de Excel es que 0 se considera FALSO y cualquier otro valor se trata como VERDADERO. Entonces, para Excel, esto es lo mismo que una lista VERDADERO/FALSO. Lo que significa que podemos usar esto dentro del argumento de inclusión .
¿Quieres saber más sobre CONTAR.SI? Consulte esta publicación: https://exceljet.net/functions/countifs-function
FILTRAR basado en una lista
Bien, ahora es el momento de agregar la función FILTRO, usando CONTAR.SI como argumento de inclusión .
La fórmula en la celda I4 es:
=FILTER(Data,COUNTIFS(ItemList[Item],Data[Item]),"No values")La fórmula anterior CONTAR.SI está resaltada en negrita.
Solo se conservan los elementos de la tabla de datos donde CONTAR.SI calcula 1 o más.
FILTRAR por elementos que NO están en la lista
Para filtrar por elementos que no están en la lista, envolvemos CONTAR.SI en la función NOT.
=FILTRO(Datos, NO( CONTAR.SI(ItemList[Item],Data[Item]) ) ”,Sin valores”)
FILTRAR basado en múltiples listas
Sé que alguien preguntará esto, así que bien podría responderla aquí.
Sí, usando este método podemos filtrar por múltiples listas. Simplemente agregamos más CONTAR.SI al argumento de inclusión .
Y condición (incluido en todas las listas)
Si queremos elementos que aparezcan en varias listas:
- Utilice un asterisco ( * ) entre cada CONTAR.SI
- Envuelva todos los CONTAR.SI en el argumento de inclusión entre paréntesis
La fórmula en la celda I4 es:
=FILTER(Data,(COUNTIFS(ItemList[Item],Data[Item])*COUNTIFS(RegionList[Region],Data[Region])),"No values")Observe el símbolo * entre CONTAR.SI. Los elementos incluidos tanto en ItemList como en RegionList se retienen en el resultado del FILTRO.
O condición (incluida en cualquier lista)
Alternativamente, para conservar elementos que aparecen en cualquier lista:
- Utilice un signo más ( + ) entre cada CONTAR.SI
- Envuelva todos los CONTAR.SI en el argumento de inclusión entre paréntesis,
La fórmula en la celda I4 es:
=FILTER(Data,(COUNTIFS(ItemList[Item],Data[Item])+COUNTIFS(RegionList[Region],Data[Region])),"No values")Observe el símbolo + entre CONTAR.SI. Los elementos incluidos en ItemList o RegionList se retienen en el resultado FILTER.
Conclusión
En esta publicación, hemos visto cómo desglosar la lógica de la fórmula de la función FILTRO. A través de esto, pudimos filtrar por una lista en Excel usando FILTRO y CONTAR. También llevamos esto más allá y/o la lógica para filtrar por múltiples listas.
Deja una respuesta