Filtrar datos por condiciones de lista. Filtrar datos en la lista. Filtrar usando formulario de datos

Objetivo del trabajo: realizar clasificación de datos, familiarizarse con el método de filtrado de entradas de listas, filtrado automático y trabajar con formularios de datos.

Ejercicio 1.

Ordene los datos de la Tabla 5.5 varias veces de acuerdo con los siguientes criterios: en orden alfabético de los apellidos de los compradores, en orden descendente del monto de la transacción, en orden ascendente de la fecha de la transacción, en combinación de características (apellido, fecha, monto).

Método de hacer el trabajo.

1. Abra un nuevo libro y guárdelo como "Ordenar" en su carpeta de trabajo. .

2. Cree la tabla que se muestra en la Figura 5.56.

Figura 5.56 – Tabla inicial con datos

3. Establezca las opciones de formato para la tabla.

Fuente Times New Roman, tamaño de fuente 12 pt., para títulos, estilo en negrita y alineación central, ajuste de texto, relleno gris; para la parte principal. Como recordatorio, los comandos de formato están disponibles en la cinta Inicio Þ Celdas .

4. Para ordenar por el campo de apellido del comprador, coloque el cursor en cualquier lugar de esta columna y ejecute el comando Datos Þ Clasificación (Figura 5.51) .

En el cuadro de diálogo que se abre, en el campo Ordenar por Seleccione el apellido del comprador. Ascendente.

5. Repita todos los pasos del paso 4 y establezca la clasificación por "Monto de la transacción", en orden descendente.

6. Vuelva a ordenar por el campo "Fecha de transacción", en orden ascendente.

7. Copie la tabla a nueva hoja y ordenarlo por un conjunto de características. Para hacer esto, llame al comando Datos Þ Clasificación. Instalar Ordenar por apellidos en orden ascendente, Entonces por fecha en orden ascendente, Por último, por importe en orden descendente.

8. Usando un comando Rebautizar Dé nombres a estas dos hojas.

Tarea 2. Seleccione información de la lista según el comando Autofiltro.

Método de hacer el trabajo..

1. En la hoja 4, cree una tabla y rellénela con información de la tabla 5.5.

2. Cambie el nombre de la Hoja 4, dándole el nombre “Autofiltro n.° 1”.

3. Para aplicar AutoFiltering, coloque el cursor en el área de la lista y ejecute el comando Datos ÞFiltro. Aparecerán flechas hacia abajo junto a los nombres de las columnas de la tabla, revelando una lista de valores posibles. En la columna "Sexo", seleccione "M", copie la tabla en la hoja 5 y cámbiele el nombre a "Autofiltro No. 2".

4. En la hoja “Autofiltro No. 1”, en la columna “Género”, abra la lista de filtrado y seleccione “Todos”. Luego, en la columna "Fecha de nacimiento", seleccione "Condición" de la lista de filtrado y establezca la condición (Fig. 5.57):

Tabla 5.5

Apellido	Nombre	fecha empleo	Fecha de nacimiento	Piso	Salario	Edad
Pashkov	Ígor	16.05.74	15.03.49	METRO
andreeva	ana	16.01.93	19.10.66	Y
Erokhin	Vladímir	23.10.81	24.04.51	METRO
popov	alexei	02.05.84	07.10.56	METRO
Tyunkov	Vladímir	03.11.88	19.07.41	METRO
Notkin	Eugenio	27.08.85	17.08.60	METRO
kubrina	Puerto pequeño	20.04.93	26.06.61	Y
Gudkov	nikita	18.03.98	05.04.58	METRO
Gorbátov	Miguel	09.08.99	15.09.52	METRO
Bystrov	alexei	06.12.00	08.10.47	METRO
krylova	tatiana	28.12.93	22.03.68	Y
Bersheva	Olga	14.12.01	22.12.74	Y
Rusanova	Esperanza	24.05.87	22.01.54	Y

Figura 5.57 – Configuración de las condiciones de filtrado

5. Copie la tabla filtrada a la hoja 6 y cámbiele el nombre a “Autofiltro No. 3. En la hoja Autofiltro n.º 1, anule la selección.

Figura 5.58 – Filtro personalizado

6. En la columna "Apellido", seleccione "Condición" en la lista de filtrado y establezca una condición para seleccionar todos los empleados cuyo apellido comience con "B" (Fig. 5.58).

7. Copie la lista filtrada en la hoja 7 y cámbiele el nombre a "Autofiltro No. 4".

8. En la hoja “Autofiltro No. 1” para la columna “Apellido” establezca “Todos”, y en la columna “Salario” establezca “Primeros 10...” donde en el cuadro de diálogo ingrese “Mostrar los 5 elementos más grandes de la lista”.

9. Guarde el archivo.

Tarea 3. Seleccione registros de la lista usando el comando de filtro avanzado.

Metodología de realización del trabajo.

1. Vaya a la Hoja 8 y cámbiele el nombre a "Filtro avanzado".

2. Copie la tabla de la tarea anterior (Tabla 5.5) en esta hoja, péguela comenzando desde la línea 7. Las primeras 6 líneas están reservadas para establecer condiciones.

3. Creemos una variedad de condiciones. Supongamos que necesitamos seleccionar los nombres de los empleados que ganan más de 5.000 rublos. O cuya edad supere los 50 años. Complete las condiciones como se muestra en la Figura 5.59.

Figura 5.59 – Condiciones para un filtro avanzado

4. Ejecute el comando Datos Þ Adicionales . Complete el cuadro de diálogo de la siguiente manera (Fig. 5.60):

Figura 5.60 – Ventana de parámetros de filtro avanzado

Ver los resultados de la selección. Cuando se escriben condiciones en una línea, se implementa un Y lógico. Cuando se escriben condiciones en diferentes líneas, se consideran conectadas mediante un O lógico. Hemos considerado la primera opción, ahora consideraremos la segunda.

5. Supongamos que necesitamos mostrar solo aquellos empleados cuyos apellidos comiencen con las letras A, G o N. Complete el rango de condiciones (Figura 5.61).

Figura 5.61 – Condiciones para un filtro avanzado

6. Ejecute el comando DatosÞAdicional y complete el cuadro de diálogo (Figura 5.62).

Figura 5.62 – Ventana de parámetros de filtro avanzado

Ver los resultados de la selección de registros.

1. Enumere todos los empleados salario que son más que el promedio. Antes de crear este filtro, ingrese la fórmula =PROMEDIO(F8:F20) en la celda H2 para calcular el salario promedio.

2. Luego, en la celda A2 ingresamos la condición calculada =F8>$H$2, que se refiere a la celda H2 (Figuras 5.63 y 5.64).

Figura 5.63 – Condiciones para un filtro avanzado

Figura 5.64 – Parámetros de filtro avanzados

Un filtro es una forma rápida y sencilla de encontrar un subconjunto de datos y trabajar con él en una lista. La lista filtrada muestra solo las filas que cumplen los criterios. A diferencia de la ordenación, un filtro no cambia el orden de las entradas en la lista. El filtrado oculta temporalmente las filas que no desea mostrar.

Las filas seleccionadas mediante filtrado se pueden editar, formatear, crear gráficos e imprimir sin cambiar el orden de las filas ni moverlas.

El filtrado selecciona solo los datos necesarios y oculta los datos restantes. De esta forma solo se muestra lo que quieres ver y puedes hacerlo con un solo clic.

Al filtrar, los datos no cambian de ninguna manera. Una vez que se elimina el filtro, todos los datos vuelven a aparecer en el mismo formato que tenían antes de aplicar el filtro.

Hay dos comandos disponibles en Excel para filtrar listas:

• Autofiltro, incluido el filtro por selección, para condiciones simples selección.
• Filtro avanzado para más condiciones difíciles selección.

Autofiltro

Para permitir Autofiltro debe seleccionar cualquier celda de la tabla y luego en la pestaña Datos en grupo Clasificación Y filtrar presione el botón grande :

Después de esto, aparecerá un botón de flecha hacia abajo en el encabezado de la tabla a la derecha del encabezado de cada columna:

Al hacer clic en una flecha se abre un menú de lista para la columna correspondiente. La lista contiene todos los elementos de una columna en orden alfabético o numérico (según el tipo de datos), para que puedas encontrar rápidamente el elemento que necesitas:

Si necesitamos un filtro para una sola columna, entonces no tenemos que mostrar botones de flecha para las columnas restantes. Para ello, antes de pulsar el botón seleccione varias celdas de la columna deseada junto con el encabezado.

Filtrar por valor exacto

Encender Autofiltro, haga clic en el botón de flecha y seleccione un valor de la lista desplegable. Para seleccionar rápidamente todos los elementos de una columna o deseleccionar todos los elementos, haga clic en el elemento (Seleccionar todo) :

En este caso, se ocultan todas las filas cuyo campo no contenga el valor seleccionado.

Haciendo trabajo de laboratorio, seleccione el resultado del filtrado, cópielo en otro lugar de la hoja y fírmelo.

Apagar Autofiltro necesitas presionar el botón nuevamente .

Para cancelar la acción de filtrado sin salir del modo de filtrado, haga clic en el botón y seleccione el elemento de la lista desplegable (Seleccionar todo) . En este caso, aparecen las filas de la tabla ocultas por el filtro.

Señales de filtrado de datos

Los filtros ocultan datos. Esto es exactamente para lo que están diseñados. Sin embargo, si no se conoce el filtrado de datos, puede parecer que faltan algunos datos. Podrías, por ejemplo, abrir la hoja filtrada de otra persona o incluso olvidar que tú mismo aplicaste previamente un filtro. Entonces, cuando tienes filtros en una hoja, puedes encontrar diferentes señales visuales y mensajes.

(ubicado en la parte inferior izquierda de la ventana). El estado inicial:

Inmediatamente después de filtrar los datos, el resultado de aplicar el filtro se muestra en la esquina inferior izquierda de la barra de estado. Por ejemplo, " Registros encontrados: 2 de 11”:

Línea de números . Los números de línea discontinua indican que algunas líneas están ocultas y el color cambiado de los números líneas visibles indica que las filas seleccionadas son el resultado de una selección de filtro.

tipo de flecha . Cuando la flecha de Autofiltro en una columna filtrada cambia a, indica que la columna está filtrada.

”

“” es otro filtro universal que se puede aplicar a columnas con números o fechas.

“” es un nombre muy convencional. De hecho, las capacidades de este filtro son mucho más amplias. Con este filtro, puede encontrar los primeros elementos o los últimos elementos (números o fechas más pequeños o más grandes). Y, contrariamente al nombre del filtro, los resultados obtenidos no se limitan a los 10 primeros elementos ni a los 10 últimos elementos. El número de elementos mostrados se puede seleccionar entre 1 y 500.

”también le permite filtrar datos por porcentaje del número total de filas en la columna. Si una columna contiene 100 números y desea ver los quince más grandes, seleccione 15 por ciento.

Puede utilizar el filtro para buscar productos con los precios más altos o más bajos, para determinar la lista de empleados contratados más recientemente o para ver una lista de estudiantes con las mejores o peores calificaciones. Para aplicar el filtro “” a una columna de datos ( solo numeros o fechas!!!), haga clic en la flecha de la columna y seleccione el elemento Filtros numéricos Más :

Después de esto, se abrirá un cuadro de diálogo. Cubrir condiciones Por lista :

En el cuadro de diálogo seleccione número(filas o porcentajes), más grande o pequeñísimo, elementos de la lista o % del número de elementos.

Crea tus propios filtros personalizados

Por ejemplo, necesitamos generar solo filas con posiciones que comiencen con la letra " D'. Para hacer esto, haga clic en la flecha de filtro automático en la primera columna y seleccione Filtros de texto , luego señala empezar con… :

Un cuadro de diálogo aparecerá (Cualquiera que sea el elemento de la derecha que seleccione, seguirá apareciendo el mismo cuadro de diálogo):

en el campo Título profesional elegir - empezar con , a la derecha entramos d:

En la ventana hay una pista:

Signo de interrogación " ? ”significa cualquier carácter.

Firmar " * ”denota una secuencia de cualquier carácter.

Puede seleccionar los datos necesarios de la lista mediante filtrado, es decir, ocultando todas las filas de la lista excepto aquellas que cumplen con los criterios especificados. Para utilizar la función de filtrado, debe colocar el cursor de la tabla en una de las celdas del encabezado de la lista (en nuestra tabla, este es el rango A1:U11) y llamar al comando Datos/Filtro/Autofiltro. Una vez activado, aparecerá un pequeño cuadrado con una flecha desplegable en la esquina inferior derecha de cada celda del encabezado.

Veamos cómo trabajar con un filtro automático usando el siguiente ejemplo. Determinemos cuántos representantes del sexo más fuerte trabajan en la empresa. Haga clic en el botón de filtro ubicado en la celda con el encabezado Género y seleccione la letra M (masculino) de la lista que se abre. En la barra de estado aparecerá el mensaje Filtro: selección (Fig. 4.20). Se ocultarán todas las filas que no cumplan con los criterios especificados. La flecha en el botón de lista se volverá azul para indicar que de este campo El filtro automático está habilitado.

Arroz. 4.20. Usar un filtro automático para seleccionar registros según "M" (masculino)

Si desea aclarar cuántos de estos hombres son jefes, haga clic también en el botón de filtro automático en la celda Posición y seleccione la palabra Jefe en la lista correspondiente. Aparecerá un mensaje en la barra de estado indicando cuántas filas satisfacen el criterio especificado: Registros encontrados: 2 de 10 (es decir, la respuesta se dará inmediatamente). El resultado se muestra en la figura. 4.21.

Para cancelar el filtrado por una columna específica, simplemente abra la lista Autofiltro en esa columna y seleccione Todo. Sin embargo, si la función de filtrado está configurada en varias columnas, deberá repetir esta operación varias veces. En este caso es mejor usar el comando Datos/Filtro/Mostrar todo.

Arroz. 4.21. Hoja de trabajo después de filtrar la lista de empleados por el criterio “jefe masculino”

La función de filtrado funcionará correctamente si tiene cuidado al ingresar datos. En particular, debe asegurarse de que no haya espacios adicionales al principio y al final de los datos del texto. No se notan en la pantalla, pero pueden dar lugar a resultados erróneos y se dedica mucho tiempo a identificarlos.

El filtrado selecciona datos que cumplen exactamente un criterio determinado. Por lo tanto, si en lugar de la palabra “Head” aparece en una columna la palabra “Head_”, es decir con un espacio al final, Excel trata estos valores como diferentes. Para deshacerse de inconsistencias de este tipo, copie la celda con la palabra “Boss” al portapapeles, active el filtro de selección por “Boss_” y reemplace los valores incorrectos con el contenido del buffer.

Puede mostrar información sobre uno o varios parámetros utilizando el filtrado de datos en Excel.

Existen dos herramientas para este propósito: Autofiltro y Filtro avanzado. No eliminan, sino que ocultan los datos que no cumplen las condiciones. Autofilter realiza las operaciones más simples. El filtro avanzado tiene muchas más opciones.

Autofiltro y filtro avanzado en Excel

Tengo una tabla simple que no está formateada ni declarada como una lista. Puedes habilitar el filtro automático a través del menú principal.

Si formatea el rango de datos como una tabla o lo declara como una lista, el filtro automático se agregará inmediatamente.

Usar un filtro automático es simple: debe seleccionar la entrada con el valor deseado. Por ejemplo, muestre las entregas a la tienda número 4. Coloque una marca de verificación junto a la condición de filtrado correspondiente:

Inmediatamente vemos el resultado:

Características de la herramienta:

1. El autofiltro sólo funciona en un rango sin ruptura. No se filtran diferentes tablas en la misma hoja. Incluso si tienen el mismo tipo de datos.
2. La herramienta trata la línea superior como encabezados de columna; estos valores no están incluidos en el filtro.
3. Está permitido aplicar varias condiciones de filtrado a la vez. Pero cada resultado anterior puede ocultar los registros necesarios para el siguiente filtro.

El filtro avanzado tiene muchas más opciones:

1. Puede establecer tantas condiciones de filtrado como sea necesario.
2. Los criterios para seleccionar datos son visibles.
3. Usando el filtro avanzado, el usuario puede encontrar fácilmente valores únicos en una matriz de varias líneas.



Cómo hacer un filtro avanzado en Excel

Un ejemplo listo para usar: cómo usar un filtro avanzado en Excel:

En la tabla original sólo quedaron las filas que contienen el valor "Moscú". Para cancelar el filtrado, debe hacer clic en el botón "Borrar" en la sección "Ordenar y filtrar".

Cómo utilizar el filtro avanzado en Excel

Consideremos usar un filtro avanzado en Excel para seleccionar filas que contengan las palabras "Moscú" o "Ryazan". Las condiciones de filtrado deben estar en la misma columna. En nuestro ejemplo, uno debajo del otro.

Completando el menú de filtro avanzado:

Obtenemos una tabla con filas seleccionadas según un criterio dado:

Seleccionemos filas que contengan el valor "Nº 1" en la columna "Tienda" y ">1.000.000 de rublos" en la columna de coste. Los criterios de filtrado deben estar en las columnas correspondientes de la tabla de condiciones. En una línea.

Complete los parámetros de filtrado. Haga clic en Aceptar.

Dejemos en la tabla solo aquellas filas que contienen la palabra "Ryazan" en la columna "Región" o el valor ">10.000.000 de rublos" en la columna "Costo". Dado que los criterios de selección pertenecen a columnas diferentes, los ubicamos en líneas diferentes debajo de los títulos correspondientes.

Usemos la herramienta “Filtro avanzado”:

Esta herramienta puede trabajar con fórmulas, lo que permite al usuario resolver casi cualquier problema al seleccionar valores de matrices.

Reglas básicas:

1. El resultado de la fórmula es el criterio de selección.
2. La fórmula escrita devuelve VERDADERO o FALSO.
3. El rango inicial se especifica mediante referencias absolutas y el criterio de selección (en forma de fórmula) se especifica mediante referencias relativas.
4. Si se devuelve VERDADERO, la fila se mostrará después de aplicar el filtro. FALSO - no.

Mostremos filas que contienen cantidades superiores al promedio. Para ello, además de la placa con los criterios (en la celda I1), ingrese el nombre “Mayor cantidad”. A continuación se muestra la fórmula. Usamos la función PROMEDIO.

Seleccione cualquier celda en el rango de origen y llame a "Filtro avanzado". Indicamos I1:I2 como criterio de selección (¡enlaces relativos!).

Solo permanecen en la tabla aquellas filas donde los valores de la columna "Cantidad" están por encima del promedio.

Para dejar solo filas no repetidas en la tabla, en la ventana "Filtro avanzado", marque la casilla junto a "Solo registros únicos".

Haga clic en Aceptar. Se ocultarán las líneas duplicadas. Sólo las entradas únicas permanecerán en la hoja.

Filtrar datos en una lista es seleccionar datos según un criterio determinado, es decir Esta es una operación que le permite seleccionar los datos necesarios entre los disponibles.

Al utilizar filtros, puede mostrar y ver solo datos que cumplan ciertas condiciones. Excel le permite ver rápida y cómodamente los datos requeridos de la lista usando un comando simple: "Autofiltrado". Más consultas complejas a la base de datos se puede implementar usando el comando "Filtro avanzado".

Autofiltrado

Para realizar el autofiltrado, inicialmente debe copiar la base de datos de origen de la hoja "Cálculo de datos por fórmulas" a una nueva hoja de "Autofiltrado". Luego coloque el cursor en el área de la lista y ejecute el comando "Datos" - "Filtro" - "Autofiltro". Por esto equipo excel coloca listas desplegables directamente en los nombres de las columnas de la lista. Al hacer clic en la flecha, puede ver una lista de posibles criterios de selección. Si el botón se usó para asignar un filtro, la flecha se vuelve azul. Están disponibles las siguientes opciones de lista de criterios:

· “Todos”: se seleccionan todos los registros;

· “Top 10” - en el cuadro de diálogo “Imponer una condición a una lista”, seleccione un cierto número de los elementos más pequeños o más grandes de la lista que desea mostrar;

· “Valores”: solo se seleccionarán aquellos registros que creen el valor especificado en esta columna;

· “Condición”: los registros se seleccionan según una condición generada por el usuario en el cuadro de diálogo “Autofiltro personalizado”;

· “Vacío”: se presentan filas que no contienen datos en la columna;

· “No vacío”: solo se presentan aquellos registros que contienen líneas no vacías en la columna.

En este caso, es necesario crear las siguientes condiciones para la operación "Autofiltrado": para el campo "Beneficios", debe establecer el valor "Veterano o discapacitado", y para el campo "Número de familiares", debe Es necesario establecer la condición: "Mayor o igual a 3". De acuerdo con el hecho de que los filtros se instalan en dos columnas al mismo tiempo, el filtrado de registros se realizará según dos condiciones simultáneamente, es decir, como resultado se seleccionarán los beneficios para Veteranos y Discapacitados, el número de miembros de la familia. de los cuales es mayor o igual a 3. Como resultado se encontraron inquilinos que cumplen con las condiciones anteriores. Este resultado se presenta en la Figura Tabla 4 “Autofiltrado”.

Filtro avanzado

El filtrado mediante un filtro avanzado se realiza mediante el comando: “Datos” - “Filtro” - “Filtro avanzado”.

Para utilizar el comando "Filtro avanzado", primero debe crear una tabla de criterios, que luego colocaremos en la misma hoja de trabajo "Filtro avanzado" que la tabla original "Cálculo de datos por fórmulas", pero para no ocultar la hoja durante filtración.

En el “Filtro Avanzado”, así como en el “Autofiltro”, existen varias opciones para los tipos de criterios, tales como:

El criterio de comparación incluye operaciones del siguiente tipo:

· valor exacto;

· valores formados mediante operadores relacionales;

un patrón de valores que incluye caracteres o

Criterio múltiple: criterio formado en varias columnas.

· Si los criterios se indican en cada columna en una línea, entonces se considera que están sujetos a la condición AND.

· Si los criterios se escriben en varias líneas, se considera que están conectados por una condición OR.

Criterio calculado: es una fórmula escrita en una línea en el área de condiciones que devuelve el valor lógico "VERDADERO" o "FALSO".