Filtro avanzado de Excel: una guía completa con ejemplos

Ver video - Filtro avanzado de Excel

El filtro avanzado de Excel es una de las funciones más subestimadas y subutilizadas que he encontrado.

Si trabaja con Excel, estoy seguro de que lo ha utilizado (o al menos ha oído hablar del filtro de Excel normal). Filtra rápidamente un conjunto de datos según la selección, el texto especificado, el número u otros criterios similares.

En esta guía, le mostraré algunas cosas interesantes que puede hacer con el filtro avanzado de Excel.

Pero primero … ¿Qué es el filtro avanzado de Excel?

El filtro avanzado de Excel, como su nombre indica, es la versión avanzada del filtro normal. Puede usar esto cuando necesite usar criterios más complejos para filtrar su conjunto de datos.

A continuación, se muestran algunas diferencias entre el filtro normal y el filtro avanzado:

• Si bien el filtro de datos normal filtrará el conjunto de datos existente, también puede usar el filtro avanzado de Excel para extraer el conjunto de datos a otra ubicación.
• El filtro avanzado de Excel le permite utilizar criterios complejos. Por ejemplo, si tiene datos de ventas, puede filtrar los datos según un criterio en el que el representante de ventas sea Bob y la región sea Norte o Sur (veremos cómo hacer esto en los ejemplos). El soporte de Office tiene una buena explicación sobre esto.
• Puede usar el Filtro avanzado de Excel para extraer registros únicos de sus datos (más sobre esto en un segundo).

FILTRO EXCEL AVANZADO (Ejemplos)

Ahora echemos un vistazo a un ejemplo sobre el uso del filtro avanzado en Excel.

Ejemplo 1: extracción de una lista única

Puede utilizar el Filtro avanzado de Excel para extraer rápidamente registros únicos de un conjunto de datos (o en otras palabras, eliminar duplicados).

En Excel 2007 y versiones posteriores, existe una opción para eliminar duplicados de un conjunto de datos. Pero eso altera su conjunto de datos existente. Para mantener intactos los datos originales, debe crear una copia de los datos y luego usar la opción Eliminar duplicados. El filtro avanzado de Excel le permitiría seleccionar una ubicación para obtener una lista única.

Veamos cómo usar filtros avanzados para obtener una lista única.

Suponga que tiene un conjunto de datos como se muestra a continuación:

Como puede ver, hay registros duplicados en este conjunto de datos (resaltados en naranja). Estos podrían deberse a un error en la entrada de datos o al resultado de la recopilación de datos.

En tal caso, puede usar la herramienta Filtro avanzado de Excel para obtener rápidamente una lista de todos los registros únicos en una ubicación diferente (de modo que sus datos originales permanezcan intactos).

Estos son los pasos para obtener todos los registros únicos:

• Seleccione todo el conjunto de datos (incluidos los encabezados).
• Vaya a la pestaña Datos -> Ordenar y filtrar -> Avanzado. (También puede usar el atajo de teclado - Alt + A + Q). Esto abrirá el cuadro de diálogo Filtro avanzado.
• En el cuadro de diálogo Filtro avanzado, utilice los siguientes detalles:
  • Acción: Seleccione la opción "Copiar en otra ubicación". Esto le permitirá especificar la ubicación donde puede obtener la lista de registros únicos.
  • Rango de lista: Asegúrese de que se refiera al conjunto de datos del que desea buscar registros únicos. Además, asegúrese de que se incluyan los encabezados en el conjunto de datos.
  • Rango de criterio: Deje esto vacío.
  • Copiar a: Especifique la dirección de la celda donde desea obtener la lista de registros únicos.
  • Copiar solo registros únicos: Marque esta opción.
• Haga clic en Aceptar.

Esto le dará instantáneamente una lista de todos los registros únicos.

Precaución: Cuando esté utilizando Filtro avanzado para obtener la lista única, asegúrese de haber seleccionado también el encabezado. Si no lo hace, consideraría la primera celda como el encabezado.

Ejemplo 2: uso de criterios en el filtro avanzado de Excel

Obtener registros únicos es una de las muchas cosas que puede hacer con el filtro avanzado de Excel.

Su utilidad principal radica en su capacidad para permitir el uso de criterios complejos para el filtrado de datos.

Esto es lo que quiero decir con criterios complejos. Suponga que tiene un conjunto de datos como se muestra a continuación y desea obtener rápidamente todos los registros donde las ventas son superiores a 5000 y la región es EE. UU.

Así es como puede usar el Filtro avanzado de Excel para filtrar los registros según los criterios especificados:

• El primer paso al utilizar el filtro avanzado de Excel con criterios complejos es especificar los criterios. Para hacer esto, copie los encabezados y péguelos en algún lugar de la hoja de trabajo.
• Especifique los criterios por los que desea filtrar los datos. En este ejemplo, dado que queremos obtener todos los registros de EE. UU. Con ventas superiores a 5000, ingrese "EE. UU." En la celda debajo de Región y> 5000 en la celda debajo de Ventas. Esto ahora se usaría como entrada en el Filtro avanzado para obtener los datos filtrados (como se muestra en los siguientes pasos).
• Seleccione todo el conjunto de datos (incluidos los encabezados).
• Vaya a la pestaña Datos -> Ordenar y filtrar -> Avanzado. Esto abrirá el cuadro de diálogo Filtro avanzado.
• En el cuadro de diálogo Filtro avanzado, utilice los siguientes detalles:
  • Acción: Seleccione la opción "Copiar en otra ubicación". Esto le permitirá especificar la ubicación donde puede obtener la lista de registros únicos.
  • Rango de lista: Asegúrese de que se refiera al conjunto de datos del que desea buscar registros únicos. Además, asegúrese de que se incluyan los encabezados en el conjunto de datos.
  • Rango de criterio: Especifique los criterios que construimos en los pasos anteriores. En este ejemplo, sería F1: I3.
  • Copiar a: Especifique la dirección de la celda donde desea obtener la lista de registros únicos.
  • Copiar solo registros únicos: Marque esta opción.
• Haga clic en Aceptar.

Esto le daría instantáneamente todos los registros donde la región es EE. UU. Y las ventas son más de 5000.

El ejemplo anterior es un caso en el que el filtrado se realiza según dos criterios (EE. UU. Y ventas superiores a 5000).

El filtro avanzado de Excel le permite crear muchas combinaciones diferentes de criterios.

A continuación, se muestran algunos ejemplos de cómo puede construir estos filtros.

Uso de los criterios AND

Cuando desee utilizar los criterios AND, debe especificarlo debajo del encabezado.

Por ejemplo:

• Para filtrar registros cuando la región es EE. UU. Y el representante de ventas es Joe.
• Para filtrar registros cuando la región es EE. UU. Y el valor de ventas es superior a 5000.
• Cuando la región es EE. UU. Y las ventas se registran después del 31-03-2017.

Uso de los criterios OR

Cuando desee utilizar criterios OR, debe especificar los criterios en la misma columna.

Por ejemplo:

• Para filtrar registros cuando la región es EE. UU. O la región es Asia.
• Para filtrar registros cuando el representante de ventas es Bob O Martha.

A estas alturas, debe haberse dado cuenta de que cuando tenemos los criterios en la misma fila, es un Y criterios, y cuando lo tenemos en diferentes filas, es un O Criterios.

Ejemplo 3: uso de caracteres WILDCARD en el filtro avanzado en Excel

El filtro avanzado de Excel también permite el uso de caracteres comodín al construir los criterios.

Hay tres caracteres comodín en Excel:

1. * (asterisco) - Representa cualquier número de caracteres. Por ejemplo, ex * podría significar sobresalir, sobresalir, ejemplo, experto, etc.
2. ? (signo de interrogación) - Representa un solo carácter. Por ejemplo, Tr? Mp podría significar Trump o Tramp.
3. ~ (tilde) - Se utiliza para identificar un carácter comodín (~, *,?) En el texto.

Ahora veamos cómo podemos usar estos caracteres comodín para realizar un filtrado avanzado en Excel.

• Para filtrar registros donde el nombre del representante de ventas comienza por J.

Tenga en cuenta que * representa cualquier número de caracteres. Entonces, cualquier representante con el nombre que comience con J se filtraría con estos criterios.

Del mismo modo, también puede utilizar los otros dos caracteres comodín.

Nota: En caso de que esté utilizando Office 365, debe consultar la función FILTRO. Puede hacer muchas cosas que el filtro avanzado puede hacer con una fórmula simple.

NOTA:

1. Recuerde, los encabezados de los criterios deben ser exactamente los mismos que los del conjunto de datos.
2. El filtrado avanzado no se puede deshacer cuando se copia a otras ubicaciones.