Filtra i dati in base alle condizioni dell'elenco. Filtraggio dei dati nell'elenco. Filtraggio con un modulo dati

Obiettivo del lavoro: eseguire l'ordinamento dei dati, familiarizzare con il metodo di filtraggio delle voci dell'elenco, filtraggio automatico, utilizzo di un modulo dati.

Esercizio 1.

Ordinare più volte i dati nella Tabella 5.5 secondo i seguenti criteri: in ordine alfabetico dei nomi degli acquirenti, in ordine decrescente dell'importo della transazione, in ordine crescente della data della transazione, secondo la totalità delle caratteristiche (cognome, data , quantità).

Metodologia per eseguire il lavoro

1. Apri una nuova cartella di lavoro e salvala come "Ordina" nella cartella di lavoro .

2. Creare la tabella mostrata nella Figura 5.56.

Figura 5.56 - Tabella iniziale con dati

3. Imposta le opzioni di formattazione per la tabella.

Carattere Times New Roman, dimensione carattere 12 pt., grassetto e allineamento al centro dei titoli, ritorno a capo automatico, riempimento grigio; per la parte principale. Come promemoria, i comandi di formattazione sono disponibili sulla barra multifunzione Home Þ Celle .

4. Per ordinare in base al campo del cognome del cliente, posizionare il cursore in un punto qualsiasi di questa colonna ed eseguire il comando Dati Þ Ordina (Fig. 5.51) .

Nella finestra di dialogo che si apre, nel campo Ordina per selezionare "Cognome cliente". Ascendente.

5. Ripetere tutti i passaggi del paragrafo 4 e impostare l'ordinamento per "Importo della transazione", in ordine decrescente.

6. Riordinare in base al campo Data di negoziazione, in ordine crescente.

7. Copia la tabella in nuova foglia e ordinarlo in base all'insieme di funzionalità. Per fare ciò, chiama il comando Dati Þ Ordina. Installare Ordina per cognomi in ordine crescente, Poi di data in ordine crescente Infine, di somma in ordine decrescente.

8. Con comando Rinominare dai un nome a questi due fogli.

Compito 2. Selezionare le informazioni dall'elenco in base al comando Filtro automatico.

Metodologia per eseguire il lavoro.

1. Sul foglio 4, crea una tabella e riempila con le informazioni della tabella 5.5.

2. Rinominare Foglio4 come Filtro automatico n. 1.

3. Per applicare il filtro automatico, posizionare il cursore nell'area dell'elenco ed eseguire il comando Dati ÞFiltro. Accanto ai nomi delle colonne della tabella appariranno delle frecce verso il basso, che rivelano un elenco di possibili valori. Nella colonna "Sesso" seleziona "M". Copia la tabella sul foglio 5 e rinominala in "Filtro automatico n. 2".

4. Nella scheda "Filtro automatico n. 1", nella colonna "Sesso", aprire l'elenco dei filtri e selezionare "Tutti". Quindi, nella colonna "Data di nascita", seleziona "Condizione" nell'elenco dei filtri e imposta la condizione (Fig. 5.57):

Tabella 5.5

Cognome	Nome	data di assunzione	Data di nascita	Pavimento	Stipendio	Età
Pashkov	Igor	16.05.74	15.03.49	M
Andreeva	Anna	16.01.93	19.10.66	E
Erokhin	Vladimir	23.10.81	24.04.51	M
Popov	Alessio	02.05.84	07.10.56	M
Tyunkov	Vladimir	03.11.88	19.07.41	M
Notkin	Eugenio	27.08.85	17.08.60	M
Kubrina	Marina	20.04.93	26.06.61	E
Gudkov	Nikita	18.03.98	05.04.58	M
Gorbatov	Michael	09.08.99	15.09.52	M
Bystrov	Alessio	06.12.00	08.10.47	M
Krylova	Tatiana	28.12.93	22.03.68	E
Bersheva	Olga	14.12.01	22.12.74	E
Rusanova	Speranza	24.05.87	22.01.54	E

Figura 5.57 – Impostazione delle condizioni di filtraggio

5. Copia la tabella filtrata sul foglio 6 e rinominala in "Filtro automatico #3. Nel foglio Filtro automatico n. 1, deseleziona.

Figura 5.58 - Filtro utente

6. Nella colonna "Cognome", selezionare "Condizione" nell'elenco dei filtri e impostare la condizione per selezionare tutti i dipendenti il ​​cui cognome inizia con "B" (Fig. 5.58).

7. Copia l'elenco filtrato sul foglio 7, rinominalo "AutoFilter #4".

8. Nel foglio "Filtro automatico n. 1", per la colonna "Cognome", impostare "Tutti", e nella colonna "Stipendio", impostare "Primi 10..." dove nella finestra di dialogo inserire "Mostra le 5 voci dell'elenco più grandi".

9. Salvare il file.

Compito 3. Filtra i record dall'elenco utilizzando il comando Filtro avanzato.

La metodologia per svolgere il lavoro.

1. Vai al foglio 8 e rinominalo "Filtro Avanzato".

2. Copia la tabella dell'attività precedente su questo foglio (Tabella 5.5), incollala a partire dalla riga 7. Le prime 6 righe sono riservate all'impostazione delle condizioni.

3. Creiamo una serie di condizioni. Supponiamo di dover selezionare i nomi dei dipendenti che ricevono più di 5.000 rubli. O qualcuno di età superiore ai 50 anni. Compilare le condizioni come mostrato nella Figura 5.59.

Figura 5.59 - Condizioni per il filtro avanzato

4. Eseguire il comando Dati Þ Avanzato . Compilare la finestra di dialogo come segue (Fig. 5.60):

Figura 5.60 - Finestra parametri filtro avanzati

Visualizza i risultati della selezione. Quando le condizioni vengono scritte su una riga viene implementato un AND logico, mentre quando le condizioni vengono scritte su righe diverse vengono considerate collegate tramite un OR logico. Abbiamo considerato la prima opzione, ora consideriamo la seconda.

5. Supponiamo di voler visualizzare solo i dipendenti i cui cognomi iniziano con le lettere A, G o N. Compila l'intervallo di condizioni (Figura 5.61).

Figura 5.61 - Condizioni per il filtro avanzato

6. Eseguire il comando DatiÞAvanzate e compilare la finestra di dialogo (figura 5.62).

Figura 5.62 - Finestra parametri filtro avanzati

Visualizza i risultati della selezione dei record.

1. Visualizza un elenco di tutti i dipendenti, salario che sono sopra la media. Prima di creare questo filtro, inserisci la formula =MEDIA(F8:F20) nella cella H2 per calcolare lo stipendio medio.

2. Successivamente nella cella A2 inseriamo la condizione calcolata =F8>$H$2, riferita alla cella H2 (Figure 5.63 e 5.64).

Figura 5.63 - Condizioni per il filtro avanzato

Figura 5.64 Opzioni di filtro avanzate

Un filtro è un modo semplice e veloce per trovare e lavorare con un sottoinsieme di dati in un elenco. L'elenco filtrato mostra solo le righe che corrispondono ai criteri. A differenza dell'ordinamento, un filtro non modifica l'ordine delle voci nell'elenco. Il filtro nasconde temporaneamente le righe che non desideri visualizzare .

Le linee selezionate durante il filtraggio possono essere modificate, formattate, creati diagrammi basati su di esse, stampati senza modificare l'ordine delle linee e senza spostarle.

Durante il filtraggio, vengono selezionati solo i dati necessari e il resto dei dati viene nascosto. In questo modo viene mostrato solo quello che vuoi vedere e puoi farlo con un solo clic.

Il filtraggio non modifica in alcun modo i dati. Una volta rimosso il filtro, tutti i dati riappaiono com'erano prima dell'applicazione del filtro.

In Excel sono disponibili due comandi per filtrare gli elenchi:

• Filtro automatico, incluso il filtro per selezione, per termini semplici selezione.
• Filtro avanzato per più condizioni difficili selezione.

Filtro automatico

Abilitare Filtro automaticoè necessario selezionare qualsiasi cella nella tabella, quindi nella scheda Dati in gruppo Ordinamento E filtro premi il pulsante grande :

Successivamente, nell'intestazione della tabella a destra di ciascuna intestazione di colonna verrà visualizzato un pulsante con una freccia rivolta verso il basso:

Facendo clic su una freccia si apre un menu di elenco per la colonna corrispondente. L'elenco contiene tutti gli elementi della colonna in ordine alfabetico o numerico (a seconda del tipo di dati), in modo da poter trovare rapidamente l'elemento richiesto:

Se abbiamo bisogno di un filtro solo per una colonna, non possiamo visualizzare i pulsanti freccia per le restanti colonne. Per fare ciò, prima di premere il pulsante seleziona diverse celle della colonna desiderata insieme all'intestazione.

Filtraggio per valore esatto

Accendere Filtro automatico, fare clic sul pulsante con una freccia e selezionare un valore dall'elenco a discesa. Per selezionare rapidamente tutti gli elementi in una colonna o deselezionare tutti gli elementi, fare clic su (Seleziona tutto) :

In questo caso tutte le righe che non contengono il valore selezionato nel campo verranno nascoste.

Facendo lavoro di laboratorio, seleziona il risultato del filtraggio, copialo in un altro punto del foglio e firmalo.

Per disattivare Filtro automaticoè necessario premere nuovamente il pulsante .

Per annullare l'azione di filtro, senza uscire dalla modalità di filtraggio, fare clic sul pulsante e selezionare la voce dall'elenco a discesa. (Seleziona tutto) . Contemporaneamente compaiono le righe della tabella nascoste dal filtro.

Funzionalità di filtraggio dei dati

I filtri nascondono i dati. Questo è lo scopo per cui sono progettati. Tuttavia, se non conosci il filtraggio dei dati, potresti avere l'impressione che manchino alcuni dati. Potresti, ad esempio, aprire il foglio filtrato di qualcun altro o addirittura dimenticare di aver applicato tu stesso il filtro in precedenza. Pertanto, quando sono presenti filtri su un foglio, è possibile trovare vari segnali visivi e messaggi.

(situato in basso a sinistra della finestra). Lo stato iniziale:

Immediatamente dopo aver filtrato i dati, il risultato dell'applicazione del filtro viene visualizzato nell'angolo inferiore sinistro della barra di stato. Per esempio, " Documenti trovati: 2 su 11”:

Numeri di riga . Dai numeri delle linee spezzate puoi capire che alcune linee sono nascoste e dal colore cambiato dei numeri linee visibili indica che le righe selezionate sono il risultato della selezione del filtro.

Tipo di frecce . La modifica della freccia del filtro automatico in una colonna filtrata in indica che la colonna è filtrata.

”

“” è un altro filtro universale che può essere applicato alle colonne con numeri o date.

“” è un nome molto condizionale. In effetti, le possibilità di questo filtro sono molto più ampie. Con questo filtro puoi trovare i primi elementi o gli ultimi elementi (numeri o date più piccoli o più grandi). E, contrariamente al nome del filtro, i risultati non si limitano ai primi 10 elementi o agli ultimi 10 elementi. Il numero di elementi visualizzati può essere selezionato da 1 a 500.

" consente inoltre di filtrare i dati in base alla percentuale del numero totale di righe nella colonna. Se la colonna contiene 100 numeri e desideri visualizzare i primi quindici, scegli 15%.

Il filtro può essere utilizzato per trovare i prodotti con i prezzi più alti o più bassi, per determinare l'elenco dei dipendenti assunti più recentemente o per visualizzare l'elenco degli studenti con i voti migliori o peggiori. Per applicare il filtro “” a una colonna di dati ( solo numeri o date!!!), fare clic sulla freccia nella colonna e selezionare l'elemento Filtri numerici Ulteriore :

Questo aprirà una finestra di dialogo. sovrapposizione condizioni Di elenco :

Nella finestra di dialogo, seleziona numero(linee o percentuali), maggiore O meno, Elementi della lista O % del numero di elementi.

Crea i tuoi filtri personalizzati

Ad esempio, vogliamo visualizzare solo le righe con posizioni che iniziano con la lettera " D'. Per fare ciò, fare clic sulla freccia del filtro automatico nella prima colonna e selezionare Filtri di testo , quindi puntare iniziare con… :

Questo farà apparire una finestra di dialogo. (qualunque sia l'opzione scelta a destra, verrà comunque visualizzata la stessa finestra di dialogo.):

Nel campo Titolo di lavoro scegliere - iniziare con , a destra entriamo D:

Nella finestra c'è un suggerimento:

Punto interrogativo " ? " sta per qualsiasi carattere.

Cartello " * " denota una sequenza di caratteri qualsiasi.

È possibile selezionare i dati necessari dall'elenco utilizzando il filtro, ovvero nascondendo tutte le righe dell'elenco, ad eccezione di quelle che soddisfano i criteri specificati. Per utilizzare la funzione di filtro, è necessario posizionare il cursore della tabella su una delle celle dell'intestazione della lista (nella nostra tabella questo è l'intervallo A1:U11) e chiamare il comando Dati/Filtro/Filtro automatico. Una volta attivato, nell'angolo in basso a destra di ciascuna cella di intestazione verrà visualizzata una piccola casella con una freccia a discesa.

Considera i metodi di lavoro con il filtro automatico utilizzando il seguente esempio. Determiniamo quanti rappresentanti del sesso più forte lavorano nell'impresa. Clicca sul pulsante del filtro situato nella cella con la voce Sesso e seleziona la lettera M (maschio) dall'elenco che si apre. Nella barra di stato apparirà il messaggio Filtro: selezione (Fig. 4.20). Tutte le righe che non soddisfano i criteri specificati verranno nascoste. La freccia sul pulsante dell'elenco diventerà blu, indicando che il file dato campo filtro automatico abilitato.

Riso. 4.20. Utilizzo di un filtro automatico per selezionare i record in base a "M" (maschio)

Se devi specificare quanti boss ci sono tra questi uomini, clicca anche sul pulsante del filtro automatico nella cella Posizione e seleziona la parola Boss nell'elenco corrispondente. Nella barra di stato apparirà un messaggio che indica quante righe soddisfano i criteri specificati: Records trovati: 2 su 10 (ovvero, la risposta verrà data immediatamente). Il risultato è mostrato in fig. 4.21.

Per annullare il filtro su una colonna specifica, basta aprire l'elenco dei filtri automatici in quella colonna e selezionare Tutto. Se però la funzione di filtraggio è definita per più colonne, sarà necessario ripetere l'operazione più volte. In questo caso è meglio usare il comando Dati/Filtra/Mostra tutto.

Riso. 4.21. Foglio di lavoro dopo aver filtrato l'elenco dei dipendenti in base al criterio "maschio - capo"

La funzione di filtro funzionerà correttamente se si presta attenzione durante l'immissione dei dati. In particolare è necessario assicurarsi che non vi siano spazi aggiuntivi all'inizio e alla fine dei dati di testo. Non sono visibili sullo schermo, ma possono portare a risultati errati e occorre molto tempo per identificarli.

Il filtraggio seleziona i dati che soddisfano esattamente i criteri specificati. Pertanto, se al posto della parola "Head" nella colonna compare la parola "Head_", cioè con uno spazio alla fine, Excel tratta questi valori come diversi. Per eliminare questo tipo di incongruenze, copiare negli appunti la cella con la parola "Head", attivare il filtro per la selezione in base "Head_" e sostituire i valori errati con il contenuto del buffer.

È possibile visualizzare informazioni su uno/più parametri filtrando i dati in Excel.

Esistono due strumenti a questo scopo: AutoFilter e Advanced Filter. Non cancellano, ma nascondono i dati che non soddisfano la condizione. Il filtro automatico esegue le operazioni più semplici. Il filtro avanzato ha molte più opzioni.

Filtro automatico e filtro avanzato in Excel

C'è una tabella semplice, non formattata e non dichiarata come elenco. È possibile attivare il filtro automatico tramite il menu principale.

Se formatti un intervallo di dati come tabella o lo dichiari come elenco, verrà aggiunto immediatamente un filtro automatico.

Utilizzare il filtro automatico è semplice: è necessario selezionare la voce con il valore desiderato. Ad esempio, visualizza le consegne al negozio n. 4. Metti un segno di spunta accanto alla condizione di filtro corrispondente:

Vediamo subito il risultato:

Caratteristiche dello strumento:

1. Il filtro automatico funziona solo in un intervallo unificato. Tabelle diverse sullo stesso foglio non vengono filtrate. Anche se hanno lo stesso tipo di dati.
2. Lo strumento tratta la riga superiore come intestazioni di colonna: questi valori non sono inclusi nel filtro.
3. È possibile applicare più condizioni di filtro contemporaneamente. Ma ogni risultato precedente può nascondere i record necessari per il filtro successivo.

Il filtro avanzato ha molte più opzioni:

1. Puoi impostare tutte le condizioni di filtraggio di cui hai bisogno.
2. I criteri di selezione dei dati sono sotto gli occhi di tutti.
3. Con il filtro avanzato, l'utente può facilmente trovare valori univoci in un array multilinea.



Come creare un filtro avanzato in Excel

Un esempio già pronto è come utilizzare un filtro avanzato in Excel:

Nella tabella originale rimanevano solo le righe contenenti il ​​valore "Mosca". Per annullare il filtraggio, è necessario fare clic sul pulsante "Cancella" nella sezione "Ordina e filtra".

Come utilizzare il filtro avanzato in Excel

Prendi in considerazione l'utilizzo di un filtro avanzato in Excel per selezionare le righe contenenti le parole "Mosca" o "Ryazan". Le condizioni per il filtraggio devono essere nella stessa colonna. Nel nostro esempio, uno sotto l'altro.

Compila il menu dei filtri avanzati:

Otteniamo una tabella con le righe selezionate secondo un dato criterio:

Selezioniamo le righe che contengono il valore "N. 1" nella colonna "Negozio" e "> 1.000.000 di rubli" nella colonna costo. I criteri per il filtraggio devono trovarsi nelle colonne appropriate dell'etichetta della condizione. Su una riga.

Compila i parametri di filtraggio. Premiamo OK.

Lasciamo nella tabella solo quelle righe che contengono la parola "Ryazan" nella colonna "Regione" o il valore "> 10.000.000 di rubli" nella colonna "Costo". Poiché i criteri di selezione si riferiscono a colonne diverse, li posizioniamo su righe diverse sotto le intestazioni corrispondenti.

Applica lo strumento Filtro avanzato:

Questo strumento può funzionare con formule, che consentono all'utente di risolvere quasi tutte le attività quando seleziona valori dagli array.

Regole di base:

1. Il risultato della formula è il criterio di selezione.
2. La formula scritta restituisce VERO o FALSO.
3. L'intervallo di origine viene specificato utilizzando riferimenti assoluti e il criterio di selezione (sotto forma di formula) viene specificato utilizzando riferimenti relativi.
4. Se viene restituito TRUE, la riga verrà visualizzata dopo l'applicazione del filtro. FALSO – no.

Visualizziamo le righe contenenti la quantità superiore alla media. Per fare ciò, oltre alla targa con i criteri (nella cella I1), inseriremo il nome “Il numero più grande”. Di seguito è riportata la formula. Usiamo la funzione MEDIA.

Seleziona una cella qualsiasi nell'intervallo di origine e chiama il "Filtro avanzato". Indichiamo I1:I2 come criterio di selezione (link relativi!).

Nella tabella sono rimaste solo quelle righe in cui i valori nella colonna "Importo" sono superiori alla media.

Per lasciare solo le righe non ripetute nella tabella, nella finestra "Filtro avanzato", seleziona la casella accanto a "Solo record univoci".

Fare clic su OK. Le righe duplicate verranno nascoste. Sul foglio rimarranno solo i record univoci.

Il filtraggio dei dati nell'elenco consiste nella selezione dei dati in base a un determinato criterio, ad es. si tratta di un'operazione che permette di selezionare il dato desiderato tra quelli disponibili.

I filtri consentono di visualizzare e visualizzare solo i dati che soddisfano determinate condizioni. Excel ti consente di visualizzare rapidamente e comodamente i dati richiesti dall'elenco utilizzando un semplice comando: "Filtro automatico". Di più interrogazioni complesse al database può essere implementato utilizzando il comando "Filtro Avanzato".

Filtro automatico

Per eseguire il filtro automatico, è necessario inizialmente copiare il database di origine dal foglio "Calcolo dei dati tramite formule" nel nuovo foglio "Filtro automatico". Posizionare quindi il cursore nell'area dell'elenco ed eseguire il comando "Dati" - "Filtro" - "Filtro automatico". Da questo Squadra eccellente inserisce gli elenchi a discesa direttamente nei nomi delle colonne dell'elenco. Cliccando sulla freccia è possibile visualizzare l'elenco dei possibili criteri di selezione. Se il pulsante è stato utilizzato per assegnare un filtro, la freccia diventa blu. Sono disponibili le seguenti opzioni dell'elenco dei criteri:

«Tutti» - tutti i record sono selezionati;

· "Top 10" - nella finestra di dialogo "Imposizione di condizioni sulla lista" seleziona un certo numero degli elementi più piccoli o più grandi della lista che desideri visualizzare;

· "Valori" - verranno selezionati solo i record che creano il valore specificato in questa colonna;

· "Condizione" - i record vengono selezionati in base alla condizione formulata dall'utente nella finestra di dialogo "Filtro automatico personalizzato";

· "Vuoto" - vengono presentate le righe che non contengono dati nella colonna;

· "Non vuoto" - vengono presentati solo i record che contengono righe non vuote nella colonna.

In questo caso è necessario creare le seguenti condizioni per l'operazione di "Filtro automatico": per il campo "Vantaggi" è necessario impostare il valore "Veterano o Disabile", e per il campo "Numero di familiari" , è necessario impostare la condizione: "Maggiore o uguale a 3". In base al fatto che i filtri sono impostati in due colonne contemporaneamente, il filtraggio dei record verrà eseguito in base a due condizioni contemporaneamente, cioè, di conseguenza, verranno selezionati i vantaggi per veterani e disabili, il numero di componenti della famiglia di cui maggiore o uguale a 3. Di conseguenza, sono stati trovati inquilini che soddisfano le condizioni di cui sopra. Questo risultato mostrato nella Figura Tabella 4 "Filtro automatico".

Filtro avanzato

Il filtraggio tramite filtro avanzato viene effettuato utilizzando il comando: "Dati" - "Filtro" - "Filtro avanzato".

Per utilizzare il comando "Filtro Avanzato", è necessario creare prima una tabella di criteri, che posizioneremo poi nello stesso foglio di lavoro "Filtro Avanzato" della tabella originale "Calcolo dei dati per formule", ma in modo tale che il foglio sia non nascosto durante il filtraggio.

Nel "Filtro avanzato" così come nel "Filtro automatico" ci sono diverse opzioni per i tipi di criteri, come ad esempio:

Il criterio di confronto comprende operazioni della seguente tipologia:

Il valore esatto

valore formato utilizzando operatori relazionali;

un modello di valore che include caratteri o

Criterio multiplo: un criterio formato in più colonne.

· Se i criteri sono specificati in ciascuna colonna su una riga, allora sono considerati collegati dalla condizione AND.

· Se i criteri sono scritti su più righe, si considerano collegati dalla condizione OR.

Criterio calcolato - è una formula scritta nella riga dell'area condizione, che restituisce il valore logico "VERO" o "FALSO".