Cómo usar la función de matriz dinámica FILTER () en Excel

El filtrado es una gran parte de muchas hojas de Microsoft Excel y, afortunadamente, hay muchas formas de hacerlo. La nueva función FILTER () de Microsoft 365 es una potente actualización. No necesariamente obtendrá resultados diferentes usando esta nueva función de matriz dinámica, ¡pero obtendrá esos resultados usando una sola función! En este artículo, revisaremos la función de filtro incorporada, luego aprenderemos cómo usar la nueva función FILTER ().

Estoy usando Microsoft 365 (escritorio) en un sistema Windows 10 de 64 bits. La función de filtro incorporada solo está disponible en versiones de cinta. FILTER () está disponible para Microsoft 365, Excel 2021, Excel para la web, Excel para iPad y iPhone, Excel para tabletas y teléfonos Android. Para su comodidad, puede descargar el archivo .xlsx de demostración. Este artículo asume que tiene habilidades básicas de Excel, pero incluso un principiante debería poder seguir las instrucciones para tener éxito.

VER: 83 consejos de Excel que todo usuario debería dominar (TechRepublic)

Cómo utilizar la función de filtro en Microsoft Excel

La función de filtro de Excel le permite filtrar por múltiples valores, por lo que es posible que no necesite la función FILTRO () en absoluto. Por esa razón, trabajaremos con un ejemplo rápido, para que pueda tomar una decisión informada. Figura A muestra un conjunto de datos simple con filtros. Para aplicar filtros, haga clic en la pestaña Datos y luego haga clic en Filtro en el grupo Ordenar y filtrar.

Figura A

Para usar los filtros, simplemente haga clic en la flecha desplegable correspondiente en la celda del encabezado. Pruébelo ahora haciendo clic en el menú desplegable de la región. El panel resultante le permite filtrar de muchas formas diferentes. Cuando no se establece ningún filtro, se comprueban todos los valores. Para ordenar por uno o más valores, pero no todos los valores, desmarque el elemento (Seleccionar todo). Luego, marque los elementos que desea ver. Figura B muestra el noroeste y el suroeste seleccionados. Posteriormente, el conjunto filtrado que se muestra en Figura C muestra solo los registros de esos dos valores; no se muestran registros de Central.

Figura B

Figura C

Esta función es lo suficientemente flexible para la mayoría de las situaciones, pero tiene al menos una limitación: no puede mostrar el conjunto filtrado en otra ubicación. Esta función solo filtra los registros in situ. Gracias a la nueva función FILTER (), hacerlo es casi tan fácil como usar la función Filter.

Acerca de la función FILTRO () en Excel

Mostrar un conjunto filtrado en otra ubicación facilita la creación de informes y funciona especialmente bien en una configuración de panel. Hasta que se introdujo FILTER (), obtener un conjunto filtrado en otra ubicación requería un poco de esfuerzo y habilidad.

FILTER () es una de las nuevas funciones de matriz dinámica de Microsoft 365. Admite lo que se conoce como rango de derrame, que es el resultado de una fórmula de matriz dinámica que devuelve varios valores: su salida derrames más allá de la celda de entrada. En resumen, un rango de derrame es un rango de resultados calculados a partir de una fórmula. Cuando selecciona cualquier celda en un rango de derrame, Excel resalta todo el rango con un borde azul. Siempre encontrará la fórmula en la celda superior de ese rango.

Esta función usa la sintaxis

FILTRO (matriz, incluir, [if_empty])

donde matriz identifica los datos de origen, incluir identifica los valores que desea ver en el conjunto de datos filtrados y el opcional if_empty especifica el valor que se mostrará cuando el resultado sea un conjunto vacío.

Puede usar FILTER () para devolver una sola columna o varias. Además, puede filtrar por una sola columna o varias. Ahora, pongamos en funcionamiento la función FILTRO ().

Cómo usar la función FILTRO () en Excel

El uso de FILTER () tiene un requisito: debe usar los mismos encabezados en el rango del conjunto filtrado. El conjunto de datos original en B2: F14 es el fuente datos. Simplemente copie los encabezados en otra ubicación.

Aunque no es un requisito, la función es más flexible si usa celdas de entrada para el argumento de inclusión. Figura D muestra la configuración necesaria. Usaremos la columna I para crear funciones más flexibles y dinámicas. El conjunto de datos filtrado se mostrará en las columnas K a 0.

Figura D

Aunque rara vez querrá mostrar los resultados filtrados de una sola columna, es posible, así que trabajemos con un ejemplo rápido. Específicamente, muestre el personal de la región suroeste ingresando a la función

= FILTRO (D3: D13, E3: E13 = I5, “Sin resultados”)

en M3, como se muestra en Figura E. Luego, ingrese Suroeste en I5, la celda de entrada Región. (Debido a que la función se ingresa en una sola celda y derrama los resultados en el rango adyacente, no se requiere una referencia absoluta).

Figura E

El resultado es fácil de descifrar revisando los argumentos de la función:

• El argumento de la matriz, D3: D13, muestra solo los valores coincidentes de la columna Personal (columna D).
• El argumento de inclusión, E3: E13 = I5, filtra la lista por el valor en la celda I5, que es Southwest.

La función devuelve solo los valores de personal para la región suroeste.

Compliquemos un poco las cosas agregando más valores de visualización y filtrado. Entrar en la función

= FILTRO (B3: F13, (D3: D13 = I4) * (E3: E13 = I5), “Sin resultados”)

en K3, como se muestra en Figura F. (Apliqué el formato apropiado porque la función FILTER () no puede).

Figura F

El argumento de inclusión, (D3: D13 = I4) * (E3: E13 = I5), puede que no le resulte familiar. El carácter * sirve como operador AND. En términos sencillos, este argumento se filtra por el valor del personal en I4 y el valor de la región en I5. El conjunto filtrado resultante contiene solo un registro para James en la región suroeste. Para aplicar un operador OR en el argumento de inclusión, utilice el carácter + en lugar del carácter *.

Sin embargo, hay una forma más de mejorar los resultados. Cuando el conjunto filtrado tiene varios registros, es posible que desee ordenar los resultados usando SORT ()

Cómo agregar la función SORT () en Excel

La función SORT () es otra nueva función de matriz dinámica. Devuelve una matriz ordenada y usa la siguiente sintaxis:

SORT (matriz,[sort_index],[sort_order],[by_col])

donde matriz es el único argumento requerido e identifica el rango a ordenar. Los argumentos opcionales son los siguientes:

sort_index: un valor de desplazamiento numérico que identifica la fila o columna por la que se debe ordenar

sort_order: el número 1 para ordenación ascendente o -1 para descendente, siendo 1 el valor predeterminado

by_col: los valores lógicos VERDADERO para una ordenación por filas y FALSO para una ordenación por columnas, siendo VERDADERO el valor predeterminado

El FILTER () actual devuelve solo un registro, así que eliminemos James de I4 y devolvamos un conjunto filtrado ordenado descendente para la región suroeste, ordenado por la columna Valor. Para hacerlo, ingrese a la función

= CLASIFICAR (FILTRO (B3: F13, E3: E13 = I5, “Sin resultados”), 2, -1)

en K3, como se muestra en Figura G.

Figura G

El argumento de la matriz de SORT () es la función FILTER () completa. El valor sort_index de 2 ordena por la columna Valor, la segunda columna de la matriz. El valor de sort_index es -1, que aplica una ordenación descendente. El resultado es un conjunto filtrado ordenado que muestra primero el valor más alto para la región suroeste.

Digno de mención

Hay algunas cosas que quizás desee incluir al aplicar FILTER () a su propio trabajo. Primero, la hoja de ejemplo incluye una celda de entrada para cada columna en el conjunto de datos de origen. Sin embargo, le recomiendo que incluya celdas de entrada solo para las columnas a las que hace referencia el argumento de inclusión de la función FILTER (). Ofrecer una celda de entrada que no actualice el conjunto filtrado es confuso para los usuarios.

La función de filtro incorporada convierte el conjunto de datos en un objeto de tabla. Las funciones FILTER () continúan haciendo referencia a la misma tabla fuente. Al ingresar las funciones, si usa el método de selección para insertar referencias, verá la estructura de la tabla de Excel haciendo referencia en el trabajo en lugar de las referencias de rango (matriz) reales que uso. No se preocupe, todo seguirá funcionando.

VER: Windows 10: listas de comandos vocales para reconocimiento de voz y dictado (PDF gratuito) (TechRepublic)

Dado que los datos de origen son un objeto Table, todo es dinámico. Si agrega, elimina o modifica un registro en los datos de origen, el conjunto de datos filtrados se actualizará en consecuencia.

En una situación real de generación de informes o tablero, considere usar la validación de datos para crear listas desplegables para las celdas de entrada. Si lo hace, evitará errores cuando el valor de entrada no sea válido.

Puede envolver una función FILTER () en una función agregada como SUM (), AVERAGE (), etc. Hacerlo devolverá solo un valor, el resultado del agregado en los resultados filtrados de FILTER ().

Manténganse al tanto

Usar FILTER () para hacer coincidir múltiples valores en múltiples celdas puede ser complicado. De hecho, la función de filtro incorporada es mucho más fácil de usar a este respecto. En un artículo futuro, abordaré ese requisito usando FILTER (). Continuaremos el estudio con un artículo sobre el filtrado por columnas de valor, como Valor y Comisión.

Ver también