Filtro avanzado en Excel

¿Qué es el filtro avanzado en Excel?

El filtro avanzado es diferente del filtro automático en Excel, esta función no es como un botón que se puede usar con un solo clic del mouse, para usar un filtro avanzado primero tenemos que definir un criterio para el filtro automático y luego hacer clic en la pestaña Datos y luego en la sección avanzada para el filtro avanzado donde completaremos nuestros criterios para los datos.

¿Cómo usar el filtro avanzado en Excel? (Con ejemplos)

Aprendamos el uso de esto con algunos ejemplos.

Puede descargar esta plantilla de Excel de filtro avanzado aquí - Plantilla de Excel de filtro avanzado

Ejemplo 1

Supongamos que tenemos datos siguientes para filtrar en función de diferentes criterios.

Necesitamos verificar la transacción de venta que fue realizada por 'Taran' y 'Suresh', luego podemos usar el operador OR que muestra los registros que cumplen cualquiera de las condiciones. Para obtener los resultados podemos seguir los pasos para aplicar estos filtros en Excel.

  • Paso 1: para aplicar un filtro avanzado, primero debemos seleccionar cualquiera de las celdas en el rango de datos

  • Paso 2: a continuación, haga clic en Datos tabuladores> Ordenar y filtrar grupo -> Avanzado de comandos

  • Paso 3: Al hacer clic en 'Avanzado' , se abrirá un cuadro de diálogo 'Filtro avanzado' para pedirle al Rango de lista que filtre, Rango de criterios para definir los criterios y Rango de extracción para copiar los datos filtrados (si lo desea).

  • Paso 4: Para Criterios, necesitamos copiar los encabezados de las columnas en la fila superior y definir los criterios debajo del encabezado del campo. Para especificar los criterios, podemos utilizar el operador de comparación, que son los siguientes:

  • Paso 5: Como queremos obtener todos los registros que tengan el nombre 'Suresh' o 'Taran'. El rango de criterios sería el siguiente:

Para las condiciones 'O' en las que queremos mostrar los registros que satisfacen cualquiera de las condiciones, necesitamos especificar los criterios en diferentes filas.

Hay dos acciones en un filtro avanzado.

  • Filtrar la lista, en su lugar : esta opción filtra la lista en el lugar original, es decir, en el rango de la lista en sí y después de analizar, podemos eliminar el filtro usando el comando 'Borrar' en el grupo 'Ordenar y filtrar' en 'Datos'

  • Copiar en otra ubicación : esta opción copia los datos deseados según los criterios en el rango especificado.

Podemos utilizar cualquiera de las opciones de acuerdo a nuestra necesidad pero usaremos la segunda opción más a menudo.

Ahora solo necesitamos

  • Abra el cuadro de diálogo 'Filtro avanzado'

  • Especificando el rango de lista como $ A $ 5: $ D $ 26, el rango de criterios como $ A $ 1: $ D $ 3 y el rango ' Copiar a' como $ F $ 5: $ I $ 26. Haga clic en 'Aceptar' .

Podemos ver que todos los registros que tienen Nombre como 'Suresh' o 'Taran' se filtran y se muestran por separado en un rango de celdas diferente.

Ejemplo # 2

Ahora queremos obtener todas las transacciones de ventas del primer trimestre y el sur de la India. El rango de criterios es el siguiente:

Como tenemos aquí la condición 'Y', es decir, queremos mostrar los registros donde se cumplen ambas condiciones, por eso hemos mencionado los criterios debajo de ambos encabezados de columna en la misma fila.

Ahora haremos clic en el comando 'Avanzado' en el grupo 'Ordenar y filtrar' en la pestaña 'Datos' .

Desde el cuadro de diálogo ' Filtro avanzado ', elegiremos 'Copiar a otra ubicación' y luego definiremos A5: D26 como Rango de lista , A1: D2 como Rango de criterios y F5: I26 como rango ' Copiar a' .

Ahora el resultado es el siguiente:

Ejemplo # 3

Ahora queremos encontrar ventas en el primer trimestre o realizadas en el norte de la India.

Necesitamos especificar tanto los criterios en diferentes filas como en diferentes columnas, ya que tenemos que mostrar los datos si se cumple alguna de las condiciones y ambas condiciones están relacionadas con diferentes columnas.

Pasos:

  • Necesita abrir el cuadro de diálogo 'Filtro avanzado' .

  • Especifique el rango de la lista como $ A $ 5: $ D $ 26

  • Especifique el rango de criterios como $ A $ 1: $ D $ 3

  • Especifique el rango 'Copiar a' como $ F $ 5: $ I $ 26

El resultado sería el siguiente:

Ejemplo # 4

Ahora queremos encontrar todas las ventas de Rs. 2000-4000 y Rs. 10000-13000.

Como tenemos cuatro condiciones como (Condición 1 Y Condición 2) O (Condición 3 Y Condición 4).

(> = 2000 Y = 10000 Y <= 13000)

Es por eso que hemos mencionado las condiciones con " Y" en la misma fila y Condiciones con "O" en diferentes filas.

Pasos:

  • Para abrir el cuadro de diálogo 'Filtro avanzado' , haremos clic en 'Avanzado' en el grupo 'Ordenar y filtrar' en 'Datos'

  • En el cuadro de diálogo 'Filtro avanzado' , especificaremos
  • Rango de lista como $ A $ 5: $ D $ 26

  • Rango de criterios como $ A $ 1: $ D $ 3

  • 'Copiar a' Rango como $ F $ 5: $ I $ 26

  • Después de hacer clic en 'Aceptar'. El resultado será:

Ejemplo # 5

Ahora queremos encontrar las ventas del trimestre 1 de Sunny o las del trimestre 3 de Mukesh.

Como tenemos Y y O , ambos tipos de relaciones en condiciones, es por eso que especificaremos las condiciones en el rango de criterios en diferentes filas (O) y diferentes columnas (Y).

Pasos:

  • Para abrir el cuadro de diálogo 'Filtro avanzado' , haremos clic en 'Avanzado' en el grupo 'Ordenar y filtrar' en 'Datos'

  • En el cuadro de diálogo 'Filtro avanzado' , especificaremos
  • Rango de lista como $ A $ 5: $ D $ 26

  • Rango de criterios como $ A $ 1: $ D $ 3

  • 'Copiar a' Rango como $ F $ 5: $ I $ 26

  • Después de hacer clic en Aceptar, el resultado sería

Ejemplo n. ° 6: uso de personajes WILDCARD

Ahora queremos encontrar todas las transacciones de ventas que tengan un nombre que termine con 'esh' o la primera palabra de la región que termine con 'st' y solo queremos recuperar el nombre, las ventas y la región solamente.

Aquí * denota más de un carácter y

'?' denota un solo carácter.

Como solo queremos algunas columnas, no todas, entonces necesitamos especificar las etiquetas de las columnas en Copiar al rango antes de implementar el filtro avanzado.

Ahora llamaremos al comando.

Pasos:

  • Para abrir el cuadro de diálogo 'Filtro avanzado' , haremos clic en 'Avanzado' en el grupo 'Ordenar y filtrar' en 'Datos'

  • En el cuadro de diálogo 'Filtro avanzado' , especificaremos
  • Rango de lista como $ A $ 5: $ D $ 26

  • Rango de criterios como $ A $ 1: $ D $ 3

  • 'Copiar a' Rango como $ F $ 5: $ H $ 26

  • Después de hacer clic en ' Aceptar'. El resultado sería:

Ejemplo # 7

Ahora queremos filtrar las cinco ventas principales (de una gran cantidad).

La celda de fórmula debe evaluarse como VERDADERO o FALSO . Como queremos obtener los 5 registros más grandes, es por eso que hemos utilizado la función de Excel GRANDE y comparamos el valor con el monto de ventas .

Como podemos ver, el encabezado de la columna de la celda de fórmula está en blanco. Podemos dejarlo en blanco o dar el nombre, que no coincide, con cualquiera de los encabezados de la columna en el rango de datos.

Ahora especificaremos los rangos en el cuadro de diálogo 'Filtro avanzado' . Los pasos son:

  • Para abrir el cuadro de diálogo 'Filtro avanzado' , haremos clic en 'Avanzado' en el grupo 'Ordenar y filtrar' en 'Datos'

  • En el cuadro de diálogo 'Filtro avanzado de Excel' , especificaremos
  • Rango de lista como $ A $ 5: $ D $ 26

  • Rango de criterios como $ A $ 1: $ E $ 2

  • 'Copiar a' Rango como $ F $ 5: $ I $ 26

  • Después de hacer clic en Aceptar. El resultado sería así:

Cosas para recordar

  • El rango al que se debe aplicar debe tener un encabezado único, ya que los encabezados duplicados causan un problema al ejecutar un filtro avanzado.
  • Debe haber al menos una fila en blanco entre Rango de lista y Rango de criterios.