Funkcija Excel filtra - objašnjeno s primjerima + video

Gledajte video zapise - Primjeri funkcije Excel FILTER

Office 365 donosi neke sjajne funkcije - kao što su XLOOKUP, SORT i FILTER.

Što se tiče filtriranja podataka u Excelu, u svijetu prije Office 365, najviše smo ovisili o ugrađenom filteru programa Excel ili maksimalno o Naprednom filtru ili složenim formulama SUMPRODUCT. U slučaju da ste morali filtrirati dio skupa podataka, to je obično bilo složeno zaobilazno rješenje (nešto što sam ovdje opisao).

No, s novom funkcijom FILTER sada je vrlo lako brzo filtrirati dio skupa podataka na temelju uvjeta.

U ovom tutorialu pokazat ću vam koliko je nova funkcija FILTER izvrsna i neke korisne stvari koje možete učiniti s ovim.

No prije nego što prijeđem u primjere, naučimo brzo o sintaksi funkcije FILTER.

U slučaju da želite dobiti ove nove značajke u Excelu, možete nadogradite na Office 365 (pridružite se insajderskom programu za pristup svim značajkama/formulama)

Funkcija Excel filtra - Sintaksa

Ispod je sintaksa funkcije FILTER:

= FILTER (niz, uključi, [if_empty])

• nizu - ovo je raspon ćelija u kojima imate podatke i želite filtrirati neke podatke iz njih
• uključuju - ovo je uvjet koji govori funkciji koje zapise treba filtrirati
• [ako_prazan] - ovo je izborni argument u kojem možete navesti što vratiti u slučaju da funkcija FILTER ne pronađe rezultate. Prema zadanim postavkama (ako nije navedeno), vraća #CALC! pogreška

Pogledajmo sada neke nevjerojatne primjere funkcija filtera i stvari koje može učiniti, a koje su prije bile odsutne.

Kliknite ovdje za preuzimanje datoteke Primjer i slijedite je

Primjer 1: Filtriranje podataka na temelju jednog kriterija (regija)

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite filtrirati sve zapise samo za SAD.

Ispod je formula FILTER koja će to učiniti:

= FILTER ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = "US")

Gornja formula koristi skup podataka kao niz i uvjet je $ B $ 2: $ B $ 11 = ”US”

Zbog ovog uvjeta bi funkcija FILTER provjerila svaku ćeliju u stupcu B (onu koja ima regiju) i filtrirali bi se samo oni zapisi koji odgovaraju ovom kriteriju.

Također, u ovom primjeru imam izvorne podatke i filtrirane podatke na istom listu, ali ih možete imati i u zasebnim listovima ili čak radnim bilježnicama.

Funkcija filtra vraća rezultat koji je dinamički niz (što znači da umjesto da vraća jednu vrijednost, vraća niz koji se prelijeva u druge ćelije).

Da bi ovo funkcioniralo, morate imati područje u kojem će rezultat biti prazan. U bilo kojoj ćeliji u ovom području (E2: G5 u ovom primjeru) već postoji nešto u sebi, funkcija će vam dati grešku #SPILL.

Također, budući da je ovo dinamički niz, ne možete promijeniti dio rezultata. Možete izbrisati cijeli raspon koji ima rezultat ili ćeliju E2 (gdje je unesena formula). Oboje će izbrisati cijeli rezultirajući niz. Ali ne možete promijeniti nijednu pojedinačnu ćeliju (ili je izbrisati).

U gornjoj formuli, ja sam teško kodirao vrijednost regije, ali možete je imati i u ćeliji, a zatim referencirati tu ćeliju koja ima vrijednost regije.

Na primjer, u donjem primjeru imam vrijednost regije u ćeliji I2, a to se zatim poziva u formuli:

= FILTER ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = I1)

To čini formulu još korisnijom i sada možete jednostavno promijeniti vrijednost regije u ćeliji I2 i filter bi se automatski promijenio.

Također možete imati padajući izbornik u ćeliji I2 gdje jednostavno možete izvršiti odabir i on bi odmah ažurirao ažurirane filtrirane podatke.

Primjer 2: Filtriranje podataka na temelju jednog kriterija (više ili manje od)

Također možete koristiti usporedne operatore unutar funkcije filtriranja i izdvojiti sve zapise koji su više ili manje od određene vrijednosti.

Na primjer, pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite filtrirati sve zapise gdje je vrijednost prodaje veća od 10000.

Dolje navedena formula može to učiniti:

= FILTER ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> 10000))

Argument niza odnosi se na cijeli skup podataka, a uvjet u ovom slučaju je ($ C $ 2: $ C $ 11> 10000).

Formula provjerava ima li svaki zapis vrijednost u stupcu C. Ako je vrijednost veća od 10000, filtrira se, inače se zanemaruje.

U slučaju da želite da svi zapisi budu manji od 10000, možete upotrijebiti donju formulu:

= FILTER ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11 <10000))

Također možete postati kreativniji s formulom FILTER. Na primjer, ako želite filtrirati prva tri zapisa na temelju vrijednosti prodaje, možete upotrijebiti donju formulu:

= FILTER ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> = VELIKI (C2: C11,3)))

Gornja formula koristi funkciju LARGE za dobivanje treće najveće vrijednosti u skupu podataka. Ta se vrijednost zatim koristi u kriterijima funkcije FILTER za dobivanje svih zapisa u kojima je vrijednost prodaje veća ili jednaka trećoj najvećoj vrijednosti.

Kliknite ovdje za preuzimanje datoteke Primjer i slijedite je

Primjer 3: Filtriranje podataka s više kriterija (AND)

Pretpostavimo da imate niz podataka u nastavku i želite filtrirati sve zapise za SAD u kojima je vrijednost prodaje veća od 10000.

Ovo je uvjet AND u kojem morate provjeriti dvije stvari - regija treba u SAD -u, a prodaja mora biti veća od 10000. Ako je ispunjen samo jedan uvjet, rezultati se ne bi trebali filtrirati.

Ispod je formula FILTER koja će filtrirati zapise sa SAD -om kao regijom i prodajom većom od 10000:

= FILTER ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "US")*($ C $ 2: $ C $ 11> 10000))

Imajte na umu da je kriterij (koji se naziva argument uključivanja) ($ B $ 2: $ B $ 11 = ”US”)*($ C $ 2: $ C $ 11> 10000)

Budući da koristim dva uvjeta i da oba moraju biti točna, upotrijebio sam operator množenja za kombiniranje ova dva kriterija. Ovo vraća niz 0 i 1, gdje se 1 vraća samo kada su ispunjena oba uvjeta.

U slučaju da nema zapisa koji zadovoljavaju kriterije, funkcija bi vratila #CALC! pogreška.

A u slučaju da želite vratiti nešto što znači (umjesto pogreške), možete upotrijebiti formulu kao što je prikazano u nastavku:

= FILTER ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "USA")*($ C $ 2: $ C $ 11> 10000), "Ništa nije pronađeno")

Ovdje sam kao treći argument upotrijebio “Nije pronađeno”, koji se koristi kada se ne nađu zapisi koji odgovaraju kriterijima.

Primjer 4: Filtriranje podataka s više kriterija (ILI)

Također možete izmijeniti argument 'include' u funkciji FILTER kako biste provjerili ima li OR kriterija (gdje bilo koji od navedenih uvjeta može biti istinit).

Na primjer, pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite filtrirati zapise u kojima je zemlja SAD ili Kanada.

Ispod je formula koja će to učiniti:

= FILTER ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "US")+($ B $ 2: $ B $ 11 = "Kanada"))

Imajte na umu da sam u gornjoj formuli jednostavno dodao dva uvjeta pomoću operatora zbrajanja. Budući da svaki od ovih uvjeta vraća niz TRUE i FALSEs, mogu dodati da dobijem kombinirani niz u kojem je TRUE ako je bilo koji od uvjeta ispunjen.

Drugi primjer može biti kada želite filtrirati sve zapise u kojima je država SAD ili je vrijednost prodaje veća od 10000.

Formula u nastavku će to učiniti:

= FILTER ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "US")+(C2: C11> 10000))

Napomena: Kada koristite kriterij AND u funkciji FILTER, koristite operator množenja (*), a kada koristite kriterij ILI, koristite operator zbrajanja (+).

Primjer 5: Filtriranje podataka za zapise iznad/ispod prosjeka

Možete koristiti formule unutar funkcije FILTER za filtriranje i izdvajanje zapisa gdje je vrijednost iznad ili ispod prosjeka.

Na primjer, pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite filtrirati sve zapise u kojima je vrijednost prodaje iznad prosjeka.

To možete učiniti pomoću sljedeće formule:

= FILTER ($ A $ 2: $ C $ 11, C2: C11> PROSJEČNO (C2: C11))

Slično, za ispodprosječno možete koristiti donju formulu:

= FILTER ($ A $ 2: $ C $ 11, C2: C11<>

Kliknite ovdje za preuzimanje datoteke Primjer i slijedite je

Primjer 6: Filtriranje samo EVEN brojčanih zapisa (ili ODD brojeva)

U slučaju da trebate brzo filtrirati i izdvojiti sve zapise iz redaka s parnim brojevima ili neparnih brojeva, to možete učiniti pomoću funkcije FILTER.

Da biste to učinili, morate provjeriti broj retka unutar funkcije FILTER i filtrirati samo brojeve redaka koji zadovoljavaju kriterije broja redaka.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku, a ja samo želim izdvojiti parne zapise iz ovog skupa podataka.

Ispod je formula koja će to učiniti:

= FILTER ($ A $ 2: $ C $ 11, MOD (ROW (A2: A11) -1,2) = 0)

Gornja formula koristi funkciju MOD za provjeru broja retka svakog zapisa (koji je dat funkcijom ROW).

Formula MOD (ROW (A2: A11) -1,2) = 0 vraća TRUE kada je broj retka paran i FALSE kada je neparan. Imajte na umu da sam oduzeo 1 iz dijela ROW (A2: A11) jer je prvi zapis u drugom redu, a to prilagođava broj retka kako bi drugi red uzeo u obzir kao prvi zapis.

Slično, sve neparne zapise možete filtrirati pomoću formule u nastavku:

= FILTER ($ A $ 2: $ C $ 11, MOD (ROW (A2: A11) -1,2) = 1)

Primjer 7: Sortirajte filtrirane podatke formulom

Korištenje funkcije FILTER s drugim funkcijama omogućuje nam da učinimo puno više.

Na primjer, ako filtrirate skup podataka pomoću funkcije FILTER, možete upotrijebiti funkciju SORT s njom da biste dobili rezultat koji je već sortiran.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite filtrirati sve zapise gdje je vrijednost prodaje veća od 10000. Možete koristiti funkciju SORT s funkcijom kako biste bili sigurni da su rezultirajući podaci razvrstani na temelju prodajne vrijednosti.

Formula u nastavku će to učiniti:

= SORT (FILTER ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> 10000)), 3, -1)

Gornja funkcija koristi funkciju FILTER za dobivanje podataka gdje je vrijednost prodaje u stupcu C veća od 10000. Ovaj niz koji vraća funkcija FILTER tada se koristi unutar funkcije SORT za sortiranje ovih podataka na temelju prodajne vrijednosti.

Drugi argument u funkciji SORT je 3, koji se treba sortirati na temelju trećeg stupca. I četvrti argument je -1 koji treba sortirati ove podatke prema opadajućem redoslijedu.

Kliknite ovdje za preuzimanje datoteke Primjer

Dakle, ovo je 7 primjera za korištenje funkcije FILTER u Excelu.

Nadam se da vam je ovaj vodič bio koristan!

Možda će vam se svidjeti i sljedeći Excel vodiči:

1. Kako filtrirati ćelije podebljanim oblikovanjem fontova u Excelu
2. Okvir za pretraživanje dinamičkog Excel filtra
3. Kako filtrirati podatke u zaokretnoj tablici u Excelu