Vizionați videoclipuri - Filtru avansat Excel
Excel Advanced Filter este una dintre cele mai subevaluate și subutilizate caracteristici pe care le-am întâlnit.
Dacă lucrați cu Excel, sunt sigur că ați folosit (sau cel puțin ați auzit de filtrul excel obișnuit). Filtrează rapid un set de date pe baza selecției, a textului specificat, a numărului sau a altor astfel de criterii.
În acest ghid, vă voi arăta câteva lucruri interesante pe care le puteți face folosind filtrul avansat Excel.
Dar mai întâi … Ce este Excel Advanced Filter?
Excel Advanced Filter - așa cum sugerează și numele - este versiunea avansată a filtrului obișnuit. Puteți utiliza acest lucru atunci când trebuie să utilizați criterii mai complexe pentru a filtra setul de date.
Iată câteva diferențe între filtrul obișnuit și filtrul avansat:
- În timp ce filtrul de date obișnuit va filtra setul de date existent, puteți utiliza filtrul avansat Excel pentru a extrage setul de date și în altă locație.
- Excel Advanced Filter vă permite să utilizați criterii complexe. De exemplu, dacă aveți date despre vânzări, puteți filtra datele pe un criteriu în care reprezentantul vânzărilor este Bob și regiunea este fie Nord, fie Sud (vom vedea cum să facem acest lucru în exemple). Asistența Office are câteva explicații bune în acest sens.
- Puteți utiliza filtrul avansat Excel pentru a extrage înregistrări unice din datele dvs. (mai multe despre aceasta într-o secundă).
EXCEL FILTRU AVANSAT (Exemple)
Acum să aruncăm o privire la un exemplu de utilizare a filtrului avansat în Excel.
Exemplul 1 - Extragerea unei liste unice
Puteți utiliza filtrul avansat Excel pentru a extrage rapid înregistrări unice dintr-un set de date (sau, cu alte cuvinte, pentru a elimina duplicatele).
În Excel 2007 și versiunile ulterioare, există o opțiune pentru a elimina duplicatele dintr-un set de date. Dar asta vă modifică setul de date existente. Pentru a păstra intacte datele originale, trebuie să creați o copie a datelor și apoi să utilizați opțiunea Eliminare duplicate. Filtrul Excel Advanced vă va permite să selectați o locație pentru a obține o listă unică.Să vedem cum se utilizează filtre avansate pentru a obține o listă unică.
Să presupunem că aveți un set de date așa cum se arată mai jos:
După cum puteți vedea, există înregistrări duplicate în acest set de date (evidențiat în portocaliu). Acestea se pot datora unei erori la introducerea datelor sau rezultatului compilării datelor.
Într-un astfel de caz, puteți utiliza instrumentul de filtrare avansată Excel pentru a obține rapid o listă a tuturor înregistrărilor unice într-o locație diferită (astfel încât datele dvs. originale să rămână intacte).
Iată pașii pentru a obține toate înregistrările unice:
- Selectați întregul set de date (inclusiv antetele).
- Accesați fila Date -> Sortare și filtrare -> Avansat. (Puteți utiliza și comanda rapidă de la tastatură - Alt + A + Q). Aceasta va deschide caseta de dialog Filtru avansat.
- În caseta de dialog Filtru avansat, utilizați următoarele detalii:
- Acțiune: Selectați opțiunea „Copiați într-o altă locație”. Acest lucru vă va permite să specificați locația în care puteți obține lista înregistrărilor unice.
- Gama de liste: Asigurați-vă că se referă la setul de date din care doriți să găsiți înregistrări unice. De asemenea, asigurați-vă că sunt incluse anteturile din setul de date.
- Gama de criterii: Lăsați acest lucru gol.
- Copiaza in: Specificați adresa celulei unde doriți să obțineți lista înregistrărilor unice.
- Copiați numai înregistrări unice: Bifați această opțiune.
- Faceți clic pe OK.
Acest lucru vă va oferi instantaneu o listă cu toate înregistrările unice.
Prudență: Când utilizați Filtru avansat pentru a obține lista unică, asigurați-vă că ați selectat și antetul. În caz contrar, ar considera prima celulă ca antet.
Exemplul 2 - Utilizarea criteriilor în Excel Advanced Filter
Obținerea de înregistrări unice este unul dintre multele lucruri pe care le puteți face cu filtrul avansat Excel.
Utilitatea sa principală constă în capacitatea sa de a permite utilizarea unor criterii complexe pentru filtrarea datelor.
Iată ce vreau să spun prin criterii complexe. Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să obțineți rapid toate înregistrările în care vânzările sunt mai mari de 5000 și regiunea este SUA.
Iată cum puteți utiliza Excel Advanced Filter pentru a filtra înregistrările pe baza criteriilor specificate:
- Primul pas atunci când utilizați filtru avansat Excel cu criterii complexe este specificarea criteriilor. Pentru aceasta, copiați anteturile și lipiți-le undeva în foaia de lucru.
- Specificați criteriile pentru care doriți să filtrați datele. În acest exemplu, deoarece vrem să obținem toate înregistrările pentru SUA cu vânzări mai mari de 5000, introduceți „SUA” în celula de sub Regiune și> 5000 în celula de sub Vânzări. Acest lucru va fi folosit acum ca intrare în Filtru avansat pentru a obține datele filtrate (așa cum se arată în pașii următori).
- Selectați întregul set de date (inclusiv antetele).
- Accesați fila Date -> Sortare și filtrare -> Avansat. Aceasta va deschide caseta de dialog Filtru avansat.
- În caseta de dialog Filtru avansat, utilizați următoarele detalii:
- Acțiune: Selectați opțiunea „Copiați într-o altă locație”. Acest lucru vă va permite să specificați locația în care puteți obține lista înregistrărilor unice.
- Gama de liste: Asigurați-vă că se referă la setul de date din care doriți să găsiți înregistrări unice. De asemenea, asigurați-vă că sunt incluse anteturile din setul de date.
- Gama de criterii: Specificați criteriile pe care le-am construit în pașii de mai sus. În acest exemplu, ar fi F1: I3.
- Copiaza in: Specificați adresa celulei unde doriți să obțineți lista înregistrărilor unice.
- Copiați numai înregistrări unice: Bifați această opțiune.
- Faceți clic pe OK.
Acest lucru vă va oferi instantaneu toate înregistrările în care regiunea este SUA și vânzările sunt mai mari de 5000.
Exemplul de mai sus este un caz în care filtrarea se face pe baza a două criterii (SUA și vânzări mai mari de 5000).
Filtrul avansat Excel vă permite să creați mai multe combinații diferite de criterii.
Iată câteva exemple despre cum puteți construi aceste filtre.
Utilizarea criteriilor ȘI
Când doriți să utilizați criteriile AND, trebuie să le specificați sub antet.
De exemplu:
- Pentru a filtra înregistrările când regiunea este SUA ȘI reprezentantul de vânzări este Joe.
- Pentru a filtra înregistrările când regiunea este SUA ȘI valoarea vânzărilor este mai mare de 5000.
- Când regiunea este SUA ȘI vânzările sunt înregistrate după 31-03-2017.
Utilizarea criteriilor SAU
Când doriți să utilizați criterii SAU, trebuie să specificați criteriile în aceeași coloană.
De exemplu:
- Pentru a filtra înregistrările când regiunea este SUA SAU regiunea este Asia.
- Pentru a filtra înregistrările când reprezentantul de vânzări este Bob SAU Martha.
Exemplul 3 - Utilizarea caracterelor WILDCARD în filtru avansat în Excel
Filtrul avansat Excel permite, de asemenea, utilizarea de caractere wildcard în timp ce construiți criteriile.
Există trei caractere wildcard în Excel:
- * (asterisc) - Reprezintă orice număr de caractere. De exemplu, ex * ar putea însemna excel, excelează, exemplu, expert etc.
- ? (semnul întrebării) - Reprezintă un singur personaj. De exemplu, Tr? Mp ar putea însemna Trump sau Tramp.
- ~ (tilde) - Este folosit pentru a identifica un caracter wildcard (~, *,?) În text.
Acum să vedem cum putem folosi aceste caractere wildcard pentru a efectua o filtrare avansată în Excel.
- Pentru a filtra înregistrările de unde începe numele reprezentantului de vânzări de la J.
Rețineți că * reprezintă orice număr de caractere. Deci, orice reprezentant cu numele care începe cu J ar fi filtrat cu aceste criterii.
În mod similar, puteți utiliza și celelalte două caractere wildcard.
Notă: În cazul în care utilizați Office 365, ar trebui să verificați funcția FILTER. Poate face o mulțime de lucruri pe care filtrul avansat le poate face cu o formulă simplă.
NOTĂ:
- Nu uitați, anteturile din criterii ar trebui să fie exact aceleași cu cele din setul de date.
- Filtrarea avansată nu poate fi anulată atunci când este copiată în alte locații.
