Filtrați datele după condițiile listei. Filtrarea datelor din listă. Filtrați folosind formularul de date

Scopul lucrării: efectuarea sortării datelor, familiarizarea cu metoda de filtrare a intrărilor în listă, filtrarea automată și lucrul cu formularele de date.

Exercitiul 1.

Sortați datele din Tabelul 5.5 de mai multe ori în conformitate cu următoarele criterii - în ordinea alfabetică a numelor cumpărătorilor, în ordinea descrescătoare a sumei tranzacției, în ordinea crescătoare a datei tranzacției, în combinație de caracteristici (nume, dată, sumă).

Metoda de realizare a muncii

1. Deschideți un nou registru de lucru și salvați-l ca „Sortați” în folderul de lucru .

2. Creați tabelul prezentat în Figura 5.56.

Figura 5.56 – Tabel inițial cu date

3. Setați opțiunile de formatare pentru tabel.

Font Times New Roman, dimensiunea fontului 12 pt., pentru titluri, stil îndrăzneț și aliniere la centru, împachetare cuvinte, umplere gri; pentru partea principală. Ca reamintire, comenzile de formatare sunt disponibile pe Panglică Acasă Þ Celulele .

4. Pentru a sorta după câmpul numelui de familie al cumpărătorului, plasați cursorul oriunde în această coloană și executați comanda Date Þ Sortare (Fig. 5.51) .

În caseta de dialog care se deschide, în câmp Filtrează după Selectați Numele cumpărătorului. Ascendent.

5. Repetați toți pașii de la pasul 4 și setați sortarea după „Suma tranzacției”, în ordine descrescătoare.

6. Resortați după câmpul „Data tranzacției”, crescător.

7. Copiați tabelul în frunză nouăși sortați după un set de caracteristici. Pentru a face acest lucru, apelați comanda Date Þ Sortare. Instalare Filtrează după nume de familie în ordine crescătoare, Apoi prin data in ordine crescatoare, În sfârșit, de către suma în ordine descrescătoare.

8. Folosind o comandă Redenumiți Dați nume acestor două foi.

Sarcina 2. Selectați informațiile din listă pe baza comenzii AutoFilter.

Metoda de realizare a muncii.

1. Pe foaia 4, creați un tabel și completați-l cu informațiile din tabelul 5.5.

2. Redenumiți Sheet4, dându-i numele „AutoFilter #1”.

3. Pentru a aplica AutoFiltering, plasați cursorul în zona de listă și executați comanda Date ÞFiltru. Lângă numele coloanelor din tabel vor apărea săgeți în jos, dezvăluind o listă de valori posibile. În coloana „Sex”, selectați „M”. Copiați tabelul în foaia 5 și redenumiți-l „Autofilter No. 2”.

4. Pe foaia „Autofilter No. 1”, în coloana „Gender”, deschideți lista de filtrare și selectați „Toate”. Apoi, în coloana „Data nașterii”, selectați „Condiție” din lista de filtrare și setați condiția (Fig. 5.57):

Tabelul 5.5

Nume de familie	Nume	data de angajare	Data nașterii	Podea	Salariu	Vârstă
Pașkov	Igor	16.05.74	15.03.49	M
Andreeva	Anna	16.01.93	19.10.66	ȘI
Erokhin	Vladimir	23.10.81	24.04.51	M
Popov	Alexei	02.05.84	07.10.56	M
Tyunkov	Vladimir	03.11.88	19.07.41	M
Notkin	Eugene	27.08.85	17.08.60	M
Kubrina	Marina	20.04.93	26.06.61	ȘI
Gudkov	Nikita	18.03.98	05.04.58	M
Gorbatov	Mihai	09.08.99	15.09.52	M
Bystrov	Alexei	06.12.00	08.10.47	M
Krylova	Tatiana	28.12.93	22.03.68	ȘI
Bersheva	Olga	14.12.01	22.12.74	ȘI
Rusanova	Speranţă	24.05.87	22.01.54	ȘI

Figura 5.57 – Setarea condițiilor de filtrare

5. Copiați tabelul filtrat în foaia 6 și redenumiți-l „Autofilter No. 3. Pe foaia Filtru automat nr. 1, deselectați.

Figura 5.58 – Filtru personalizat

6. În coloana „Nume”, selectați „Condiție” din lista de filtrare și setați o condiție pentru selectarea tuturor angajaților al căror nume de familie începe cu „B” (Fig. 5.58).

7. Copiați lista filtrată în foaia 7 și redenumiți-o „Autofilter No. 4”.

8. Pe foaia „Autofilter No. 1” pentru coloana „Nume” setați „Toate”, iar în coloana „Salariu” setați „Primii 10...” unde în caseta de dialog introduceți „Afișați cele 5 mai mari elemente a listei”.

9. Salvați fișierul.

Sarcina 3. Selectați înregistrările din listă utilizând comanda Advanced filter.

Metodologia de realizare a lucrării.

1. Accesați Foaia 8 și redenumiți-o „Filtru avansat”.

2. Copiați tabelul din sarcina anterioară (Tabelul 5.5) pe această foaie, lipiți-l începând de la rândul 7. Primele 6 rânduri sunt rezervate pentru stabilirea condițiilor.

3. Să creăm o serie de condiții. Să presupunem că trebuie să selectăm numele angajaților care câștigă mai mult de 5.000 de ruble. Sau a căror vârstă depășește 50 de ani. Completați condițiile așa cum se arată în Figura 5.59.

Figura 5.59 – Condiții pentru un filtru avansat

4. Rulați comanda Date Þ Suplimentare . Completați caseta de dialog după cum urmează (Fig. 5.60):

Figura 5.60 – Fereastra parametrii filtru avansati

Vedeți rezultatele selecției. La scrierea condițiilor pe o singură linie, se implementează ȘI logic.La scrierea condițiilor pe linii diferite, acestea sunt considerate conectate prin OR logic. Am luat în considerare prima opțiune, acum o vom lua în considerare pe a doua.

5. Să presupunem că trebuie să afișăm numai acei angajați ale căror nume de familie încep cu literele A, G sau N. Completați intervalul de condiții (Figura 5.61).

Figura 5.61 – Condiții pentru un filtru avansat

6. Rulați comanda DateÞSuplimentare și completați caseta de dialog (Figura 5.62).

Figura 5.62 – Fereastra parametrii filtru avansat

Vedeți rezultatele selecției înregistrărilor.

1. Listați toți angajații salariu care sunt mai mult decât media. Înainte de a crea acest filtru, introduceți formula =AVERAGE(F8:F20) în celula H2 pentru a calcula salariul mediu.

2. Apoi în celula A2 introducem condiția calculată =F8>$H$2, care se referă la celula H2 (Figurile 5.63 și 5.64).

Figura 5.63 – Condiții pentru un filtru avansat

Figura 5.64 – Parametrii filtrului avansat

Un filtru este o modalitate rapidă și ușoară de a găsi un subset de date și de a lucra cu el într-o listă. Lista filtrată afișează numai rândurile care îndeplinesc criteriile. Spre deosebire de sortare, un filtru nu modifică ordinea intrărilor din listă. Filtrarea ascunde temporar rândurile pe care nu doriți să le afișați.

Rândurile selectate prin filtrare pot fi editate, formatate, create în diagrame și tipărite fără a modifica ordinea rândurilor sau a le muta.

Filtrarea selectează numai datele necesare și ascunde datele rămase. În acest fel, este afișat doar ceea ce doriți să vedeți și se poate face cu un singur clic.

La filtrare, datele nu se modifică în niciun fel. Odată ce filtrul este eliminat, toate datele apar din nou în aceeași formă ca înainte de aplicarea filtrului.

Există două comenzi disponibile în Excel pentru filtrarea listelor:

• Filtru automat, inclusiv filtrarea după selecție, pentru conditii simple selecţie.
• Filtru avansat pentru mai mult conditii dificile selecţie.

Filtru automat

Pentru a permite Filtru automat trebuie să selectați orice celulă din tabel, apoi pe filă Date in grup Triere Și filtru apăsați butonul mare :

După aceasta, un buton săgeată în jos va apărea în antetul tabelului din dreapta fiecărui titlu de coloană:

Făcând clic pe o săgeată, se deschide un meniu cu listă pentru coloana corespunzătoare. Lista conține toate elementele unei coloane în ordine alfabetică sau numerică (în funcție de tipul de date), astfel încât să puteți găsi rapid elementul de care aveți nevoie:

Dacă avem nevoie de un filtru pentru o singură coloană, atunci nu trebuie să afișăm butoanele săgeți pentru coloanele rămase. Pentru a face acest lucru, înainte de a apăsa butonul selectați mai multe celule din coloana dorită împreună cu titlul.

Filtrați după valoarea exactă

Porniți Filtru automat, faceți clic pe butonul săgeată și selectați o valoare din lista verticală. Pentru a selecta rapid toate elementele unei coloane sau a deselecta toate elementele, faceți clic pe element (Selectează tot) :

În acest caz, toate rândurile al căror câmp nu conține valoarea selectată sunt ascunse.

Facand munca de laborator, selectați rezultatul de filtrare, copiați-l în alt loc de pe foaie și semnați-l.

A opri Filtru automat trebuie să apăsați din nou butonul .

Pentru a anula acțiunea de filtrare fără a părăsi modul de filtrare, faceți clic pe butonul și selectați elementul din lista derulantă (Selectează tot) . În acest caz, apar rânduri de tabel ascunse de filtru.

Semne de filtrare a datelor

Filtrele ascund datele. Exact pentru asta sunt concepute. Cu toate acestea, dacă filtrarea datelor nu este cunoscută, poate părea că unele date lipsesc. Ați putea, de exemplu, să deschideți foaia filtrată a altcuiva sau chiar să uitați că dvs. ați aplicat anterior un filtru. Deci, atunci când aveți filtre pe o foaie, puteți găsi diferite indici vizuale și mesaje.

(situat în partea stângă jos a ferestrei). Starea inițială:

Imediat după filtrarea datelor, rezultatul aplicării filtrului este afișat în colțul din stânga jos al barei de stare. De exemplu, " Înregistrări găsite: 2 din 11”:

Numerele liniilor . Numerele liniilor întrerupte indică faptul că unele linii sunt ascunse și culoarea schimbată a numerelor linii vizibile indică faptul că rândurile selectate sunt rezultatul unei selecții de filtru.

Tip săgeată . Când săgeata AutoFilter dintr-o coloană filtrată se schimbă în, aceasta indică faptul că coloana este filtrată.

”

„” este un alt filtru universal care poate fi aplicat coloanelor cu numere sau date.

„” este un nume foarte convențional. De fapt, capacitățile acestui filtru sunt mult mai largi. Folosind acest filtru, puteți găsi fie primele elemente, fie ultimele elemente (cele mai mici sau mai mari numere sau date). Și, spre deosebire de numele filtrului, rezultatele obținute nu se limitează la primele 10 elemente sau ultimele 10 elemente. Numărul de elemente afișate poate fi selectat de la 1 la 500.

” vă permite de asemenea să filtrați datele în funcție de procent din numărul total de rânduri dintr-o coloană. Dacă o coloană conține 100 de numere și doriți să vizualizați cele mai mari cincisprezece, atunci selectați 15 la sută.

Puteți folosi filtrul pentru a găsi produse cu cele mai mari sau mai mici prețuri, pentru a determina lista angajaților cei mai recent angajați sau pentru a vizualiza o listă a studenților cu cele mai bune sau mai proaste note. Pentru a aplica filtrul „” unei coloane de date ( doar numere sau date!!!), faceți clic pe săgeata din coloană și selectați elementul Filtre numerice Mai departe :

După aceasta, se va deschide o casetă de dialog Acoperire conditii De listă :

În caseta de dialog selectați număr(rânduri sau procente), cea mai mare sau cel mai mic, lista elementelor sau % din numărul de elemente.

Creează-ți propriile filtre personalizate

De exemplu, trebuie să scoatem numai rânduri cu poziții care încep cu litera „ D’. Pentru a face acest lucru, faceți clic pe săgeata de filtru automat din prima coloană și selectați Filtre de text , apoi punct începe cu… :

Va apărea o casetă de dialog (Orice element din dreapta selectați, aceeași casetă de dialog va apărea în continuare.):

În câmp Denumirea funcției alege - începe cu , in dreapta intram d:

La fereastră există un indiciu:

Semnul întrebării " ? ” înseamnă orice caracter.

Semn " * ” denotă o succesiune de caractere.

Puteți selecta datele necesare din listă utilizând filtrarea, adică ascunzând toate rândurile listei, cu excepția celor care îndeplinesc criteriile specificate. Pentru a utiliza funcția de filtrare, trebuie să plasați cursorul tabelului pe una dintre celulele antetului listei (în tabelul nostru acesta este intervalul A1:U11) și să apelați comanda Date/Filtru/Filtru automat. Odată activat, un pătrat mic cu o săgeată derulantă va apărea în colțul din dreapta jos al fiecărei celule antet.

Să ne uităm la cum să lucrați cu un filtru automat folosind următorul exemplu. Să stabilim câți reprezentanți ai sexului mai puternic lucrează la întreprindere. Faceți clic pe butonul de filtru aflat în celula cu titlul Sex și selectați litera M (bărbat) din lista care se deschide. Mesajul Filtru: selecție va apărea în bara de stare (Fig. 4.20). Toate rândurile care nu îndeplinesc criteriile specificate vor fi ascunse. Săgeata de pe butonul de listă va deveni albastră pentru a indica acest lucru a acestui domeniu Filtrul automat este activat.

Orez. 4.20. Utilizarea unui filtru automat pentru a selecta înregistrările pe baza „M” (masculin)

Dacă doriți să clarificați câți dintre acești bărbați sunt șefi, faceți clic și pe butonul de filtrare automată din celula Poziție și selectați cuvântul Șef din lista corespunzătoare. În bara de stare va apărea un mesaj care indică câte rânduri îndeplinesc criteriul specificat: Înregistrări găsite: 2 din 10 (adică răspunsul va fi dat imediat). Rezultatul este prezentat în Fig. 4.21.

Pentru a anula filtrarea după o anumită coloană, deschideți lista AutoFilter din acea coloană și selectați Toate. Cu toate acestea, dacă funcția de filtrare este setată pe mai multe coloane, va trebui să repetați această operațiune de mai multe ori. În acest caz, este mai bine să utilizați comanda Date/Filtrare/Afișează toate.

Orez. 4.21. Foaie de lucru după filtrarea listei de angajați după criteriul „șef masculin”

Funcția de filtrare va funcționa corect dacă aveți grijă când introduceți date. În special, trebuie să vă asigurați că nu există spații suplimentare la începutul și la sfârșitul datelor text. Ele nu sunt vizibile pe ecran, dar pot duce la rezultate eronate și se petrece mult timp identificându-le.

Filtrarea selectează datele care îndeplinesc exact un anumit criteriu. Prin urmare, dacă în loc de cuvântul „Head” apare cuvântul „Head_” într-o coloană, adică cu un spațiu la sfârșit, Excel tratează aceste valori ca fiind diferite. Pentru a scăpa de neconcordanțe de acest fel, copiați celula cu cuvântul „Boss” în clipboard, activați filtrul de selectare prin „Boss_” și înlocuiți valorile incorecte cu conținutul bufferului.

Puteți afișa informații despre unul/mai mulți parametri utilizând filtrarea datelor în Excel.

Există două instrumente în acest scop: AutoFilter și Advanced Filter. Ele nu șterg, ci ascund datele care nu îndeplinesc condițiile. Filtrul automat efectuează cele mai simple operații. Filtrul avansat are mult mai multe opțiuni.

Filtru automat și filtru avansat în Excel

Am un tabel simplu care nu este formatat sau declarat ca o listă. Puteți activa filtrul automat prin meniul principal.

Dacă formatați intervalul de date ca tabel sau îl declarați ca listă, filtrul automat va fi adăugat imediat.

Utilizarea unui autofiltru este simplă: trebuie să selectați intrarea cu valoarea dorită. De exemplu, afișați livrările către magazinul nr. 4. Puneți o bifă lângă condiția de filtrare corespunzătoare:

Vedem imediat rezultatul:

Caracteristicile instrumentului:

1. Autofiltrul funcționează numai într-un interval de neîncărcare. Diferitele tabele de pe aceeași foaie nu sunt filtrate. Chiar dacă au același tip de date.
2. Instrumentul tratează linia de sus ca titluri de coloană - aceste valori nu sunt incluse în filtru.
3. Este permisă aplicarea mai multor condiții de filtrare simultan. Dar fiecare rezultat anterior poate ascunde înregistrările necesare pentru următorul filtru.

Filtrul avansat are mult mai multe opțiuni:

1. Puteți seta oricâte condiții de filtrare este necesar.
2. Criteriile de selectare a datelor sunt vizibile.
3. Folosind filtrul avansat, utilizatorul poate găsi cu ușurință valori uniceîntr-o matrice cu mai multe linii.



Cum să faci un filtru avansat în Excel

Un exemplu gata făcut - cum să utilizați un filtru avansat în Excel:

Doar rândurile care conțin valoarea „Moscova” au rămas în tabelul original. Pentru a anula filtrarea, trebuie să faceți clic pe butonul „Șterge” din secțiunea „Sortare și filtrare”.

Cum se utilizează filtrul avansat în Excel

Să luăm în considerare utilizarea unui filtru avansat în Excel pentru a selecta rândurile care conțin cuvintele „Moscova” sau „Ryazan”. Condițiile de filtrare trebuie să fie în aceeași coloană. În exemplul nostru - unul sub celălalt.

Completarea meniului de filtru avansat:

Obținem un tabel cu rânduri selectate în funcție de un criteriu dat:

Să selectăm rândurile care conțin valoarea „Nr. 1” în coloana „Magazin” și „>1.000.000 de ruble” în coloana de cost. Criteriile de filtrare trebuie să fie în coloanele corespunzătoare din tabelul de condiții. Pe o linie.

Completați parametrii de filtrare. Faceți clic pe OK.

Să lăsăm în tabel doar acele rânduri care conțin cuvântul „Ryazan” în coloana „Regiune” sau valoarea „>10.000.000 de ruble” în coloana „Cost”. Deoarece criteriile de selecție aparțin unor coloane diferite, le plasăm pe linii diferite sub titlurile corespunzătoare.

Să folosim instrumentul „Filtru avansat”:

Acest instrument poate lucra cu formule, ceea ce permite utilizatorului să rezolve aproape orice problemă atunci când selectează valori din matrice.

Reguli de baza:

1. Rezultatul formulei este criteriul de selecție.
2. Formula scrisă returnează TRUE sau FALSE.
3. Intervalul inițial este specificat folosind referințe absolute, iar criteriul de selecție (sub formă de formulă) este specificat folosind referințe relative.
4. Dacă se returnează TRUE, rândul va fi afișat după aplicarea filtrului. FALS - nu.

Să afișăm rânduri care conțin cantități peste medie. Pentru a face acest lucru, în afară de placa cu criteriile (în celula I1), introduceți numele „Cea mai mare cantitate”. Mai jos este formula. Folosim funcția MEDIE.

Selectați orice celulă din intervalul sursă și apelați „Filtru avansat”. Indicăm I1:I2 drept criteriu de selecție (legături relative!).

Doar acele rânduri în care valorile din coloana „Cantitate” sunt peste medie rămân în tabel.

Pentru a lăsa în tabel numai rânduri care nu se repetă, în fereastra „Filtru avansat”, bifați caseta de lângă „Numai înregistrări unice”.

Faceți clic pe OK. Liniile duplicate vor fi ascunse. Doar intrările unice vor rămâne pe foaie.

Filtrarea datelor dintr-o listă înseamnă selectarea datelor în funcție de un criteriu dat, de ex. Aceasta este o operațiune care vă permite să selectați datele necesare dintre cele disponibile.

Folosind filtre, puteți afișa și vizualiza numai date care îndeplinesc anumite condiții. Excel vă permite să vizualizați rapid și convenabil datele necesare din listă folosind o comandă simplă - „Filtrare automată”. Mai mult interogări complexe la baza de date poate fi implementat folosind comanda „Filtru avansat”.

Autofiltrare

Pentru a efectua filtrarea automată, trebuie să copiați inițial baza de date sursă din foaia „Calcul de date prin formule” într-o nouă foaie „Filtrare automată”. Apoi plasați cursorul în zona listei și executați comanda „Date” - „Filter” - „Autofilter”. De aceasta Echipa Excel pune listele derulante direct în numele coloanelor listei. Făcând clic pe săgeată, puteți vizualiza o listă cu posibilele criterii de selecție. Dacă butonul a fost folosit pentru a atribui un filtru, săgeata devine albastră. Sunt disponibile următoarele opțiuni pentru lista de criterii:

· „Toate” - toate înregistrările sunt selectate;

· „Top 10” - în caseta de dialog „Impunerea unei condiții pe o listă”, selectați un anumit număr dintre cele mai mici sau mai mari elemente ale listei pe care doriți să le afișați;

· „Valori” - vor fi selectate doar acele înregistrări care creează valoarea specificată în această coloană;

· „Condiție” - înregistrările sunt selectate pe baza unei condiții generate de utilizator în caseta de dialog „Filtru automat personalizat”;

· „Gol” - sunt prezentate rânduri care nu conțin date în coloană;

· „Non-vide” - sunt prezentate doar acele înregistrări care conțin linii nevide în coloană.

În acest caz, este necesar să creați următoarele condiții pentru operațiunea „Autofiltrare”: pentru câmpul „Beneficii”, trebuie să setați valoarea „Veteran sau cu handicap”, iar pentru câmpul „Număr de membri ai familiei”, trebuie să setați condiția - „Mai mare sau egal cu 3”. În conformitate cu faptul că filtrele sunt instalate în două coloane în același timp, filtrarea înregistrărilor se va efectua în funcție de două condiții simultan, adică, ca urmare, se vor selecta beneficiile Veteran și Invalid, numărul de membri ai familiei. dintre care este mai mare sau egal cu 3. Ca urmare s-au constatat chiriași care îndeplinesc condițiile de mai sus. Acest rezultat este prezentat în Figura Tabelul 4 „Filtrare automată”.

Filtru avansat

Filtrarea folosind un filtru avansat se realizează cu ajutorul comenzii: „Date” - „Filtru” - „Filtru avansat”.

Pentru a utiliza comanda „Filtru avansat”, trebuie mai întâi să creați un tabel de criterii, pe care apoi îl vom plasa pe aceeași foaie de lucru „Filtru avansat” ca și tabelul original „Calcul de date prin formule”, dar pentru a nu ascunde foaia în timpul filtrare.

În „Filtrul avansat”, precum și în „Filtru automat”, există mai multe opțiuni pentru tipurile de criterii, cum ar fi:

Criteriul de comparare include operațiuni de următorul tip:

· valoare exacta;

· valori formate folosind operatori relaționali;

un model de valoare care include caractere sau

Criteriu multiplu - un criteriu format pe mai multe coloane.

· Dacă criteriile sunt indicate în fiecare coloană pe o singură linie, atunci ele sunt considerate a fi legate de condiția AND.

· Dacă criteriile sunt scrise pe mai multe rânduri, atunci ele sunt considerate a fi conectate printr-o condiție SAU.

Criteriu calculat – este o formulă scrisă într-o linie în zona de condiții care returnează valoarea logică „ADEVARAT” sau „FALSE”.