Principalul dezavantaj standard ( ) este absența informațiilor vizuale despre ceea ce a fost folosit în în acest moment filtru: trebuie să mergeți la meniul de filtrare de fiecare dată pentru a vă aminti criteriile de selectare a înregistrărilor. Acest lucru este mai ales incomod atunci când sunt aplicate mai multe criterii. Filtrul avansat nu are acest dezavantaj - toate criteriile sunt plasate sub forma unei plăci separate deasupra înregistrărilor filtrate.

Algoritm de creare Filtru avansat simplu:

• Creați un tabel căruia i se va aplica filtrul (tabel sursă);
• Cream un tabel cu criterii (cu conditii de selectie);
• Hai să lansăm Filtru avansat.

Lasă în rază O 7:C 83 există un tabel sursă cu o listă de produse care conțin câmpuri (coloane) Produs, CantitateŞi Preţ(vezi exemplu de fișier). Tabelul nu trebuie să conțină rânduri și coloane goale, în caz contrar Filtru avansat(și chiar și cel obișnuit) nu va funcționa corect.

Sarcina 1 (începe...)

Să setăm un filtru pentru a selecta rândurile care conțin valori în numele produsului pornire din cuvânt Unghiile. Această condiție de selecție este îndeplinită de liniile de produse cuie 20 mm, Cuie 10 mm, Cuie 10 mmŞi Unghiile.

O 1 :A2 A2 indica cuvântul Unghiile.

Nota: Structura criteriilor Filtru avansat este clar definit și coincide cu structura criteriilor pentru funcțiile BDSUMM(), BCOUNT(), etc.

De obicei criteriile Filtru avansat sunt amplasate deasupra mesei pe care se aplică filtrul, dar pot fi amplasate și pe partea laterală a mesei. Evitați să plasați un semn cu criterii sub tabelul original, deși acest lucru nu este interzis, nu este întotdeauna convenabil, deoarece La tabelul original pot fi adăugate rânduri noi.

ATENŢIE!
Asigurați-vă că există cel puțin o linie goală între tabelul cu valorile condițiilor de selecție și tabelul sursă (acest lucru va face mai ușor să lucrați cu Filtru avansat).

Filtru avansat:

• apel Filtru avansat();
• în câmp Gama originală O 7:C 83 );
• în câmp Gama de condiții O1 :A2 .

Dacă doriți, puteți copia rândurile selectate într-un alt tabel setând comutatorul în poziția Copiați rezultatul într-o altă locație. Dar nu vom face asta aici.

Faceți clic pe OK și filtrul va fi aplicat - doar rândurile care conțin numele produsului în coloana Produs vor rămâne în tabel cuie 20 mm, Cuie 10 mm, Cuie 50 mmŞi Unghiile. Rândurile rămase vor fi ascunse.

Numerele liniilor selectate vor fi evidențiate cu albastru.

Pentru a anula un filtru, selectați orice celulă din tabel și faceți clic CTRL+SHIFT+L(se va aplica titlul și acțiunea Filtru avansat va fi anulat) sau apăsați butonul de meniu Clar ().

Problema 2 (exact aceeași)

Să setăm un filtru pentru a selecta rândurile care au în coloana Produs exact conţine cuvântul Unghiile. Această condiție de selecție este îndeplinită numai de rândurile cu mărfuri unghiileŞi Unghiile(neluat în considerare). Valori cuie 20 mm, Cuie 10 mm, Cuie 50 mm nu vor fi luate în considerare.

Vom plasa un semn cu condiția de selecție îl vom plasa în gamă B1:B2 . Tabelul ar trebui să conțină și numele antetului coloanei prin care se va face selecția. Ca criteriu într-o celulă B2 indicați formula == unghii".

Acum totul este gata de lucru Filtru avansat:

• selectați orice celulă de tabel (acest lucru nu este necesar, dar va grăbi completarea parametrilor de filtru);
• apel Filtru avansat ( Date/ Sortare și filtrare/ Avansat);
• în câmp Gama originală asigurați-vă că intervalul de celule din tabel este specificat împreună cu anteturile ( O 7:C 83 );
• în câmp Gama de condiții indicați celulele care conțin semnul cu criteriul, adică. gamă B1:B2 .
• Faceți clic pe OK

Aplicați Filtru avansat cu criterii atât de simple nu există o semnificație specială, pentru că face față cu ușurință acestor sarcini Filtru automat. Să ne uităm la sarcinile de filtrare mai complexe.

Dacă specificați nu ="=Nails" ca criteriu, ci pur și simplu Unghiile, apoi vor fi afișate toate înregistrările care conțin nume pornire din cuvintele Nails ( Cuie 80 mm, Unghiile2). Pentru a afișa linii de produse, conţinând pe cuvânt unghiile, De exemplu, Unghii noi, trebuie să specificați ="=*Unghiile" sau pur și simplu * ca criteriu Unghiile unde* este și înseamnă orice succesiune de caractere.

Problema 3 (condiția SAU pentru o coloană)

Să setăm un filtru pentru a selecta rândurile a căror coloană Produs conține o valoare care începe cu cuvântul UnghiileSAU Tapet.

În acest caz, criteriile de selecție ar trebui plasate sub titlul de coloană corespunzător ( Produs) și ar trebui să fie situate unul sub celălalt în aceeași coloană (vezi figura de mai jos). Plasați un semn cu criteriile din interval C1:C3 .

Fereastra cu parametrii filtru avansati și tabelul cu date filtrate vor arăta astfel.

După ce faceți clic pe OK, vor fi afișate toate înregistrările care conțin în coloană Produs produse Unghiile SAU Tapet.

Problema 4 (condiția I)

exact conţine în coloană Produs produse Unghiile, iar în coloană Cantitate valoare >40. Criteriile de selecție în acest caz ar trebui plasate sub rubricile corespunzătoare (Produs și Cantitate) și ar trebui să fie situate pe o singură linie. Condițiile de selecție trebuie scrise într-un format special: ="= unghii" și =">40" . Vom plasa un semn cu condiția de selecție îl vom plasa în gamă E1:F2 .

După ce faceți clic pe butonul OK, vor fi afișate toate înregistrările care conțin în coloană Produs produse Unghiile cu un număr >40.

SFAT: Când schimbați criteriile de selecție, este mai bine să creați un tabel cu criteriile de fiecare dată și, după apelarea filtrului, doar să schimbați linkul către acestea.

Nota: Dacă ar fi trebuit să ștergeți setările filtrului avansat ( Date/ Sortare și Filtrare/ Ștergere), apoi înainte de a apela filtrul, selectați orice celulă de tabel - EXCEL va insera automat o legătură către intervalul ocupat de tabel (dacă există rânduri goale în tabel, nu va fi inserat un link către întregul tabel, ci doar în sus la primul rând gol).

Problema 5 (condiția SAU pentru diferite coloane)

Problemele anterioare ar putea, dacă se dorește, să fie rezolvate în mod obișnuit. Aceeași problemă nu poate fi rezolvată cu un filtru convențional.

Să selectăm doar acele rânduri de tabel care exact conţine în coloană Produs produse Unghiile, SAU care sunt în coloană Cantitate conțin o valoare >40. Criteriile de selecție în acest caz ar trebui plasate sub rubricile corespunzătoare (Produs și Cantitate) și ar trebui să fie localizate pe linii diferite. Condițiile de selecție trebuie scrise într-un format special: =">40" și ="= unghii". Vom plasa un semn cu condiția de selecție îl vom plasa în gamă E4:F6 .

După ce faceți clic pe butonul OK, vor fi afișate înregistrările care conțin în coloană Produs produse Unghiile Valoare SAU >40 (pentru orice produs).

Sarcina 6 (Condiții de selecție create ca urmare a aplicării formulei)

Puterea reală Filtru avansat se manifestă atunci când se utilizează formule ca condiţii de selecţie.

Există două opțiuni pentru setarea condițiilor de selecție a rândurilor:

• introduceți direct valori pentru criteriu (vezi sarcinile de mai sus);
• formați un criteriu pe baza rezultatelor formulei.

Să luăm în considerare criteriile specificate de formulă. Formula specificată ca criteriu de selecție trebuie să returneze TRUE sau FALSE.

De exemplu, să afișăm rânduri care conțin un Produs care apare în tabel o singură dată. Pentru a face acest lucru, intrați în celulă H2 formula =COUNTIF(Foaie1!$A$8:$A$83,A8)=1, și în H1 în loc de titlu introduceți text explicativ, de exemplu, Valori care nu se repetă. Aplicabil Filtru avansat, specificând intervalul de condiții ale celulei ca H1:H2 .

Rețineți că intervalul de căutare pentru valori este introdus folosind , iar criteriul din funcția COUNTIF() este introdus cu o referință relativă. Acest lucru este necesar deoarece la utilizare Filtru avansat EXCEL va vedea asta A8 este o legătură relativă și se va deplasa în jos în coloana Produs câte o intrare și va returna fie TRUE, fie FALS. Dacă se returnează TRUE, va fi afișat rândul corespunzător al tabelului. Dacă este returnat FALSE, rândul nu va fi afișat după aplicarea filtrului.

Exemple de alte formule din fișierul exemplu:

• Afișați rânduri cu prețuri mai mari decât al treilea cel mai mare preț din tabel. =C8>CEL MAI MARE($С$8:$С$83;5) Acest exemplu arată clar caracterul insidios al funcției LARGE(). Dacă sortați coloana CU (prețuri), atunci obținem: 750; 700; 700 ; 700; 620, 620, 160, ... În înțelegerea umană, „al treilea cel mai mare preț” corespunde cu 620, iar în înțelegerea funcției HIGHEST() – 700 . Ca urmare, nu vor fi afișate 4 linii, ci doar una (750);
• Ieșirea șirurilor de caractere sensibile la majuscule și minuscule =COINCIDENT("unghii";A8). Doar acele rânduri în care produsul unghiile introdus folosind litere mici;
• Se afișează rânduri al căror preț este mai mare decât media =С8>MEDIE($С$8:$С$83);

ATENŢIE!
Aplicație Filtru avansat anulează filtrul aplicat tabelului ( Date/ Sortare și Filtrare/ Filtrare).

Sarcina 7 (Condițiile de selecție conțin formule și criterii uzuale)

Să luăm acum în considerare un alt tabel din exemplu de fișier pe foaia de lucru Sarcina 7.

În coloană Produs este dat denumirea produsului, iar în coloană Tip de produs- tipul lui.

Sarcina este de a afișa produse pentru un anumit tip de produs al cărui preț este sub medie. Adică avem 3 criterii: primul criteriu stabilește Produsul, al 2-lea - Tipul acestuia, iar al 3-lea criteriu (sub formă de formulă) stabilește prețul sub medie.

Vom plasa criteriile în rândurile 6 și 7. Introduceți produsul și tipul de produs necesar. Pentru un anumit tip de produs, calculăm media și o afișăm pentru claritate într-o celulă separată F7. În principiu, formula poate fi introdusă direct în formula criteriului din celula C7.

Vor fi afișate 2 produse din 4 (de tipul de produs dat).

Problema 7.1. (Cele 2 valori de pe aceeași linie sunt aceleași?)

Există un tabel care arată anul de fabricație și anul achiziției mașinii.

Doriți să afișați numai acele rânduri în care Anul de fabricație coincide cu Anul de achiziție. Acest lucru se poate face folosind formula elementară =B10=C10.

Problema 8 (simbolul este un număr?)

Să avem un tabel cu o listă diverse tipuri unghiile

Doriți să filtrați numai acele rânduri a căror coloană Produs conține unghii de 1 inch, unghii de 2 inch etc. bunuri Cuie din oțel inoxidabil, cuie cromate etc. nu trebuie filtrat.

Cel mai simplu mod de a face acest lucru este să setați condiția ca filtru ca după cuvântul Nails să existe un număr. Acest lucru se poate face folosind formula =ISNUMBER(--PSTR(A11,LENGTH($A$8)+2,1))

Formula decupează 1 caracter din numele produsului după cuvântul Nails (inclusiv spațiul). Dacă acest caracter este un număr (cifră), atunci formula returnează TRUE și șirul este tipărit, în caz contrar șirul nu este imprimat. Coloana F arată cum funcționează formula, adică poate fi testat înainte de lansare Filtru avansat.

Sarcina 9 (Rândurile de ieșire care NU CONTIN produsele specificate)

Trebuie să filtrați numai acele rânduri care NU conțin următoarele în coloana Produs: Unghii, Placă, Lipici, Tapet.

Pentru a face acest lucru, va trebui să utilizați o formulă simplă =SFÂRȘIT(CĂUTARE V (A15, 8 USD: 11 USD, 1,0))

Uneori, datele introduse într-un document Excel pot consta din mii de rânduri și este destul de dificil să găsești rândurile necesare printre ele. Dar dacă datele pe care le căutați îndeplinesc anumite criterii, datorită cărora puteți filtra informațiile inutile, atunci trebuie să utilizați un filtru în Excel.

Pentru toate tabelele în Excel create prin meniu "Masă" pe filă "Introduce" sau căreia i s-a aplicat formatarea, ca un tabel, are deja un filtru încorporat.

Dacă datele, formatate ca tabel, nu au un filtru, atunci acestea trebuie instalate. Pentru a face acest lucru, selectați antetul tabelului sau pur și simplu selectați o celulă din interiorul tabelului și accesați fila „Date”și selectați din meniu "Filtra". Același articol de meniu poate fi găsit în filă "Acasă"în meniu „Sortare și filtrare”. După conectarea filtrului, în antetul tabelului din partea dreaptă a titlului fiecărei coloane apare un semn corespunzător, un pătrat cu un triunghi inversat.

Cea mai simplă utilizare a unui filtru este ascunderea tuturor valorilor din listă, cu excepția celor selectate. Doar faceți clic pe pictograma filtrului și lăsați o bifă în lista de valori opusă celei necesare. După aplicarea unui filtru, se modifică și pictograma din coloana filtrată, ceea ce vă permite să determinați vizual dacă filtrul este aplicat datelor. Pentru a elimina un filtru, faceți clic pe pictograma filtrului și selectați „Eliminați filtrul din coloană...”.

De asemenea, puteți personaliza filtre mai complexe. Prima noastră coloană are doar date text, așa că atunci când facem clic pe pictograma filtrului, există un element activ în această coloană „Filtre de text”, unde puteți selecta anumite condiții. În cazul datelor numerice, ca în coloana a doua, opțiunea va fi disponibilă „Filtre numerice”.

Când selectați orice opțiune personalizată, se deschide o fereastră de filtru personalizat, în care puteți selecta două condiții simultan cu o combinație "ŞI"Şi "SAU".

De asemenea, este posibil să setați un filtru după culoarea textului și culoarea celulei. În acest caz, numai textul și culorile celulelor aplicate în această coloană vor fi afișate în listă.

ÎN ultimele versiuni Excel are capacitatea de a crea secțiuni de date pentru tabele. Pentru a face acest lucru, activați orice celulă din tabel și accesați fila care apare "Constructor"și selectați meniul „Inserează felia”. Apare o fereastră în care trebuie să selectați coloana la care va fi aplicată porțiunea de date. După selectarea unei coloane, apare o fereastră plutitoare cu controale, în care vor fi listate toate elementele feliei. Prin alegerea oricărei valori vom primi porțiunea de date corespunzătoare.

Ne-am cunoscut și am învățat cum să folosim filtrarea standard în Excel. Dar de foarte multe ori apar situații când instrumentele de filtrare de bază sunt neputincioase și nu pot oferi rezultatul de eșantionare necesar. În acest tutorial veți învăța cum să rezolvați această problemă în Excel folosind filtre avansate.

Dacă dintr-o dată este nevoie de evidențierea unor date specifice, atunci, de regulă, instrumentele de filtrare de bază nu mai pot face față acestei sarcini. Din fericire, Excel vine cu o varietate de filtre avansate, inclusiv căutare text, căutare prin dată și filtrare numerică, pentru a vă ajuta să restrângeți rezultatele și să vă ajute să găsiți exact ceea ce aveți nevoie.

Filtrați și căutați în Excel

Excel vă permite să căutați informații care conțin o expresie exactă, un număr, o dată și multe altele. În exemplul următor, vom folosi acest instrument pentru a păstra numai produsele de marcă în jurnalul de funcționare a echipamentului Saris.

Utilizați filtre de text avansate în Excel

Filtrele de text avansate sunt folosite pentru a afișa informații mai specifice, cum ar fi celulele care nu conțin un set specificat de caractere. Să presupunem că tabelul nostru este deja filtrat în așa fel încât în ​​coloană Tip doar afișat Alte produse. În plus, vom exclude toate pozițiile care conțin cuvântul „caz” din coloană Descrierea echipamentului.

Utilizați filtre de dată avansate în Excel

Filtrele avansate de dată vă permit să evidențiați informații pentru o anumită perioadă de timp, de exemplu, anul trecut, luna aceasta sau între două date. În exemplul următor, vom folosi filtrul avansat de dată pentru a vizualiza echipamentul care a fost trimis pentru inspecție astăzi.

Utilizați filtre numerice avansate în Excel

Filtrele numerice avansate vă permit să operați cu date din cele mai multe în diverse moduri. În exemplul următor, vom selecta doar acele echipamente care sunt incluse în intervalul dat de numere de identificare.

Filtrare date Excel include două filtre: filtru automat și filtru avansat. Să presupunem că aveți un set mare de date, dar din întreaga matrice trebuie să priviți sau să selectați date care se referă la o anumită dată, o anumită persoană etc. Există filtre pentru aceasta. Pentru cei care întâlnesc acest instrument pentru prima dată, filtrul nu șterge, ci ascunde înregistrările care nu îndeplinesc condițiile de filtrare pe care le-ați stabilit.

Primul este un autofiltru, conceput pentru cel mai mult operatii simple– evidențierea înregistrărilor cu o anumită valoare (de exemplu, evidențierea doar a înregistrărilor legate de LeBron James), a datelor aflate într-un anumit interval (sau peste medie sau în top zece) sau celule/fonturi de o anumită culoare (apropo, foarte convenabil ). Prin urmare, este foarte ușor de utilizat. Trebuie doar să selectați datele pe care doriți să le vedeți filtrate. Apoi comanda „Date” / „Filtru”. O casetă de listă va apărea pe fiecare celulă de sus a tabelului de sus, este deja ușor de înțeles fiecare comandă, este ușor de stăpânit și sper că nu este nevoie să explic mai multe, doar nuanțele utilizării filtrului automat:

1) Funcționează numai cu o gamă de frecvență. Nu mai este posibilă filtrarea a două liste diferite pe o singură foaie.

2) Linia de sus a tabelului este atribuită automat ca titlu și nu este implicată în filtrare.

3) Puteți aplica orice filtre în coloane diferite, dar rețineți că, în funcție de ordinea în care sunt aplicate filtrele, unele condiții pot să nu fie aplicate, deoarece filtrele anterioare au ascuns deja intrările necesare. Aici nu este nicio problemă, aceste intrări ar fi fost oricum ascunse, dar dacă doriți să folosiți mai multe seturi de filtre, atunci este mai bine să începeți cu acele condiții care au cea mai mică aplicație.

Aplicație practică la locul de muncă: de exemplu, parcurgeți această listă pentru a găsi o eroare sau a verifica datele. După aplicarea filtrului automat, puteți parcurge întregul tabel unul câte unul, marcând secvențial datele care au fost deja vizualizate. Butoanele „Șterge” și „Reaplicați” determină aspectul tabelului după aplicarea condițiilor. Apoi, după ce ați terminat de lucrat cu tabelul, puteți readuce fonturile la forma lor originală fără a modifica datele în sine. Apropo, unii oameni sunt confuzi de faptul că toate înregistrările din tabel dispar după aplicarea oricăror condiții. Ei bine, uitați-vă mai atent, ați stabilit condiții în care nu există înregistrări care să îndeplinească aceste condiții. Faptul că tabelul este filtrat este atunci când numerele rândurilor tabelului sunt evidențiate cu albastru.

Acum, să trecem la filtrul avansat. Diferă de autofiltru în mai mult reglaj fin, dar și o selecție mare la filtrarea datelor. În special:

1) Stabilește câte condiții este necesar.

2) Vă permite să selectați celule cu date unice (care nu se repetă). Acest lucru este adesea necesar atunci când lucrați cu date și opțiunea face față perfect problemei.

3) Vă permite să copiați rezultatul filtrului într-o locație separată fără a atinge matricea principală.

Deci, principala diferență în lucrul cu acest filtru este că mai întâi trebuie să pregătim un tabel de condiții. Este ușor de făcut. Anteturile tabelului principal sunt copiate și lipite într-un loc convenabil pentru noi (sugerez deasupra tabelului principal). Ar trebui să existe atât de multe rânduri în acest tabel încât, după definirea condițiilor, nu veți putea intra în tabelul principal.

Exemple de condiții:

1) „L*” – celule care încep cu L

2) „>5” - date mai mari de 5

Dacă ștergeți rânduri dintr-un tabel filtrat, acestea vor fi șterse fără a-și lua vecinii cu ei. Aceste. dacă tabelul este filtrat și arată rândurile 26-29 și 31-25, selectarea tuturor rândurilor și ștergerea lor nu va avea ca rezultat ștergerea rândului 30. Acest lucru este convenabil, personal, des când scriu macrocomenzi; Ce avantaj oferă acest lucru - adesea obținem tabele care trebuie aduse în formă de lucru, de ex. eliminați, de exemplu, liniile goale. Ceea ce facem este să aplicăm un filtru tabelului, arătând doar acele rânduri de care nu avem nevoie, apoi să ștergem întregul tabel, inclusiv antetul. Rândurile și antetul inutile sunt eliminate, în timp ce tabelul nu are spații și formează un singur interval. Și o linie de antet poate fi adăugată prin operații simple de copiere dintr-o zonă pregătită anterior. De ce este important acest lucru atunci când scrieți macrocomenzi? Nu se știe din ce rând încep datele nedorite și nu este clar din ce rând să începeți ștergerea întregului tabel ajută la rezolvarea rapidă a acestei probleme;