Napredni filter Excel - potpuni vodič s primjerima

Gledajte video - Excel napredni filtar

Napredni filter Excel jedna je od najcjenjenijih i nedovoljno korištenih značajki na koje sam naišao.

Ako radite s Excelom, siguran sam da ste koristili (ili ste barem čuli za regularni excel filter). Brzo filtrira skup podataka na temelju odabira, navedenog teksta, broja ili drugih takvih kriterija.

U ovom vodiču pokazat ću vam neke zanimljive stvari koje možete učiniti pomoću naprednog filtra Excel.

Ali prvo … Što je Excel napredni filtar?

Excel napredni filtar - kako naziv govori - napredna je verzija uobičajenog filtra. To možete upotrijebiti kada trebate koristiti složenije kriterije za filtriranje vašeg skupa podataka.

Evo nekoliko razlika između običnog i naprednog filtra:

• Dok će uobičajeni filtar podataka filtrirati postojeći skup podataka, možete upotrijebiti napredni filtar Excel za ekstrahiranje skupa podataka i na neko drugo mjesto.
• Napredni filter Excel omogućuje vam korištenje složenih kriterija. Na primjer, ako imate podatke o prodaji, možete filtrirati podatke prema kriteriju gdje je prodajni predstavnik Bob, a regija sjever ili jug (vidjet ćemo kako to učiniti u primjerima). Uredska podrška ima dobro objašnjenje za to.
• Napredni filtar Excel možete koristiti za izdvajanje jedinstvenih zapisa iz vaših podataka (više o tome u sekundi).

EXCEL NAPREDNI FILTER (Primjeri)

Pogledajmo sada neki primjer korištenja Naprednog filtra u Excelu.

Primjer 1 - Izdvajanje jedinstvenog popisa

Napredni filtar Excel možete koristiti za brzo izdvajanje jedinstvenih zapisa iz skupa podataka (ili drugim riječima uklanjanje duplikata).

U Excelu 2007 i novijim verzijama postoji mogućnost uklanjanja duplikata iz skupa podataka. No to mijenja vaš postojeći skup podataka. Da bi izvorni podaci ostali netaknuti, morate stvoriti kopiju podataka, a zatim upotrijebiti opciju Ukloni duplikate. Napredni filter Excel omogućio bi vam odabir lokacije za dobivanje jedinstvenog popisa.

Pogledajmo kako koristiti napredne filtre za dobivanje jedinstvenog popisa.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku:

Kao što vidite, u ovom skupu podataka postoje duplicirani zapisi (označeni narančastom bojom). To bi moglo biti posljedica pogreške pri unosu podataka ili rezultata kompilacije podataka.

U tom slučaju možete upotrijebiti alat Excel Advanced Filter za brzo dobivanje popisa svih jedinstvenih zapisa na drugom mjestu (tako da izvorni podaci ostanu netaknuti).

Evo koraka za dobivanje svih jedinstvenih zapisa:

• Odaberite cijeli skup podataka (uključujući zaglavlja).
• Idite na karticu Podaci -> Poredaj i filtriraj -> Napredno. (Također možete koristiti tipkovnički prečac - Alt + A + Q). Ovo će otvoriti dijaloški okvir Napredni filtar.
• U dijaloškom okviru Napredni filtar upotrijebite sljedeće detalje:
  • Akcijski: Odaberite opciju "Kopiraj na drugo mjesto". To će vam omogućiti da odredite mjesto na kojem možete dobiti popis jedinstvenih zapisa.
  • Raspon popisa: Pobrinite se da se odnosi na skup podataka iz kojeg želite pronaći jedinstvene zapise. Također provjerite jesu li zaglavlja u skupu podataka uključena.
  • Raspon kriterija: Ostavite ovo prazno.
  • Kopiraj u: Navedite adresu ćelije na koju želite dobiti popis jedinstvenih zapisa.
  • Kopiraj samo jedinstvene zapise: Označite ovu opciju.
• Pritisnite U redu.

Ovo će vam odmah dati popis svih jedinstvenih zapisa.

Oprez: Kad koristite Napredni filtar za dobivanje jedinstvenog popisa, provjerite jeste li odabrali i zaglavlje. Ako to ne učinite, prva će ćelija biti zaglavlje.

Primjer 2 - Korištenje kriterija u Excel naprednom filtru

Dobivanje jedinstvenih zapisa jedna je od mnogih stvari koje možete učiniti s naprednim filtrom programa Excel.

Njegova primarna korisnost leži u sposobnosti dopuštanja korištenja složenih kriterija za filtriranje podataka.

Evo što mislim pod složenim kriterijima. Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite brzo dobiti sve zapise u kojima je prodaja veća od 5000, a regija je SAD.

Evo kako pomoću naprednog filtra Excel možete filtrirati zapise na temelju navedenih kriterija:

• Prvi korak pri korištenju programa Excel Advanced Filter sa složenim kriterijima je navođenje kriterija. Da biste to učinili, kopirajte zaglavlja i zalijepite ih negdje na radnom listu.
• Navedite kriterije za koje želite filtrirati podatke. U ovom primjeru, budući da želimo dobiti sve zapise za SAD s prodajom većom od 5000, unesite "US" u ćeliju ispod Regije i> 5000 u ćeliju ispod Prodaja. To bi se sada koristilo kao ulaz u Napredni filtar za dobivanje filtriranih podataka (kao što je prikazano u sljedećim koracima).
• Odaberite cijeli skup podataka (uključujući zaglavlja).
• Idite na karticu Podaci -> Poredaj i filtriraj -> Napredno. Ovo će otvoriti dijaloški okvir Napredni filtar.
• U dijaloškom okviru Napredni filtar upotrijebite sljedeće detalje:
  • Akcijski: Odaberite opciju "Kopiraj na drugo mjesto". To će vam omogućiti da odredite mjesto na kojem možete dobiti popis jedinstvenih zapisa.
  • Raspon popisa: Pobrinite se da se odnosi na skup podataka iz kojeg želite pronaći jedinstvene zapise. Također provjerite jesu li zaglavlja u skupu podataka uključena.
  • Raspon kriterija: Navedite kriterije koje smo izgradili u prethodnim koracima. U ovom primjeru to bi bilo F1: I3.
  • Kopiraj u: Navedite adresu ćelije na koju želite dobiti popis jedinstvenih zapisa.
  • Kopiraj samo jedinstvene zapise: Označite ovu opciju.
• Pritisnite U redu.

To bi vam odmah dalo sve rekorde u kojima je regija SAD, a prodaja veća od 5000.

Gornji primjer je slučaj kada se filtriranje vrši na temelju dva kriterija (SAD i prodaja veća od 5000).

Napredni filter Excel omogućuje vam stvaranje mnogo različitih kombinacija kriterija.

Evo nekoliko primjera kako možete izgraditi te filtre.

Korištenje kriterija AND

Ako želite koristiti kriterije AND, morate ih navesti ispod zaglavlja.

Na primjer:

• Za filtriranje zapisa kada je regija SAD, a prodajni predstavnik je Joe.
• Za filtriranje zapisa kada je regija SAD I vrijednost prodaje veća je od 5000.
• Kada je regija SAD I prodaja se bilježi nakon 31. 03. 2017.

Korištenjem kriterija OR

Kada želite koristiti kriterije ILI, morate navesti kriterije u istom stupcu.

Na primjer:

• Za filtriranje zapisa kada je regija SAD ILI regija je Azija.
• Za filtriranje zapisa kada je prodajni predstavnik Bob ILI Martha.

Dosad ste već morali shvatiti da kada imamo kriterije u istom retku, to je I kriterijima, a kada ga imamo u različitim redovima, to je an ILI kriterijima.

Primjer 3 - Korištenje WILDCARD znakova u Naprednom filtru u Excelu

Napredni filtar Excel također dopušta korištenje zamjenskih znakova prilikom izrade kriterija.

U Excelu postoje tri zamjenska znaka:

1. * (zvjezdica) - Predstavlja neograničeni broj znakova. Na primjer, ex* može značiti excel, excels, primjer, stručnjak itd.
2. ? (upitnik) - Predstavlja jedan jedini znak. Na primjer, Tr? Mp bi mogao značiti Trump ili Tramp.
3. ~ (tilda) - Koristi se za identifikaciju zamjenskog znaka (~, *,?) U tekstu.

Pogledajmo sada kako možemo koristiti ove zamjenske znakove za napredno filtriranje u Excelu.

• Za filtriranje zapisa gdje naziv prodajnog predstavnika počinje od J.

Imajte na umu da * predstavlja bilo koji broj znakova. Dakle, svaki predstavnik s imenom koji počinje s J bio bi filtriran prema ovim kriterijima.

Slično, možete koristiti i druga dva zamjenska znaka.

Napomena: U slučaju da koristite Office 365, trebali biste provjeriti funkciju FILTER. Može učiniti mnogo toga što napredni filter može učiniti s jednostavnom formulom.

BILJEŠKA:

1. Zapamtite, zaglavlja u kriterijima trebala bi biti potpuno ista kao ona u skupu podataka.
2. Napredno filtriranje ne može se poništiti ako se kopira na druga mjesta.