Funkcija Excelovega filtra - pojasnjeno s primeri + video

Oglejte si video - Primeri funkcij Excel FILTER

Office 365 prinaša nekaj čudovitih funkcij - na primer XLOOKUP, SORT in FILTER.

Ko gre za filtriranje podatkov v Excelu, smo bili v svetu pred Officeom 365 večinoma odvisni od vgrajenega filtra Excel ali največ od naprednega filtra ali kompleksnih formul SUMPRODUCT. Če ste morali filtrirati del nabora podatkov, je bilo to običajno zapleteno rešitev (nekaj, kar sem tukaj obravnaval).

Toda z novo funkcijo FILTER je zdaj zelo enostavno hitro filtrirati del nabora podatkov glede na pogoj.

V tej vadnici vam bom pokazal, kako super je nova funkcija FILTER in nekaj koristnih stvari, ki jih lahko naredite s tem.

Toda preden se spustim v primere, se hitro naučimo o sintaksi funkcije FILTER.

Če želite te nove funkcije dobiti v Excelu, lahko nadgradite na Office 365 (pridružite se notranjemu programu za dostop do vseh funkcij/formul)

Funkcija filtra Excel - Sintaksa

Spodaj je sintaksa funkcije FILTER:

= FILTER (matrika, vključi, [če_prazno])

• matriko - to je obseg celic, kjer imate podatke in želite iz njih filtrirati nekatere podatke
• vključujejo - to je pogoj, ki funkciji pove, katere zapise naj filtrira
• [če_prazno] - to je izbirni argument, kjer lahko določite, kaj naj se vrne, če funkcija FILTER ne najde rezultatov. Privzeto (če ni določeno) vrne #CALC! napaka

Zdaj pa poglejmo nekaj neverjetnih primerov funkcij filtra in stvari, ki jih lahko naredi, kar je bilo včasih precej zapleteno, če ni bilo.

Kliknite tukaj, če želite prenesti datoteko z primerom in nadaljevati

Primer 1: Filtriranje podatkov na podlagi enega merila (regija)

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite filtrirati vse zapise samo za ZDA.

Spodaj je formula FILTER, ki bo to naredila:

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

Zgornja formula uporablja niz kot niz in pogoj je $ B $ 2: $ B $ 11 = "US"

Zaradi tega pogoja bi funkcija FILTER preverila vsako celico v stolpcu B (eno z regijo) in filtrirali bi le tiste zapise, ki se ujemajo s tem merilom.

Tudi v tem primeru imam izvirne in filtrirane podatke na istem listu, lahko pa jih imate tudi v ločenih listih ali celo v delovnih zvezkih.

Funkcija filtra vrne rezultat, ki je dinamična matrika (kar pomeni, da namesto ene vrednosti vrne matriko, ki se razlije v druge celice).

Če želite, da to deluje, morate imeti območje, kjer bi bil rezultat prazen. V kateri koli celici na tem področju (E2: G5 v tem primeru) je že nekaj v njej, funkcija vam bo dala napako #SPILL.

Ker je to dinamična matrika, ne morete spremeniti dela rezultata. Izbrišete lahko celotno območje z rezultatom ali celico E2 (kjer je bila vnesena formula). Oboje bi izbrisalo celotno nastalo matriko. Ne morete pa spremeniti nobene posamezne celice (ali je izbrisati).

V zgornji formuli sem težko kodiral vrednost regije, lahko pa jo imate tudi v celici in se nato sklicujete na celico, ki ima vrednost regije.

Na primer, v spodnjem primeru imam vrednost regije v celici I2 in to se nato sklicuje v formuli:

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

Zaradi tega je formula še bolj uporabna in zdaj lahko preprosto spremenite vrednost regije v celici I2 in filter bi se samodejno spremenil.

Lahko imate tudi spustni meni v celici I2, kjer lahko preprosto naredite izbiro in bi takoj posodobil filtrirane podatke.

Primer 2: Filtriranje podatkov na podlagi enega merila (več ali manj kot)

V okviru funkcije filtriranja lahko uporabite tudi primerjalne operaterje in izvlečete vse zapise, ki so več ali manj kot določena vrednost.

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite filtrirati vse zapise, pri katerih je vrednost prodaje več kot 10000.

Spodnja formula lahko to naredi:

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

Argument niza se nanaša na celoten nabor podatkov, pogoj v tem primeru pa je ($ C $ 2: $ C $ 11> 10000).

Formula preveri vrednost vsakega zapisa v stolpcu C. Če je vrednost večja od 10000, se filtrira, sicer se ne upošteva.

Če želite, da so vsi zapisi manjši od 10000, lahko uporabite spodnjo formulo:

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

S formulo FILTER lahko postanete tudi bolj ustvarjalni. Na primer, če želite prve tri zapise filtrirati glede na prodajno vrednost, lahko uporabite spodnjo formulo:

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

Zgornja formula uporablja funkcijo LARGE, da dobi tretjo največjo vrednost v naboru podatkov. Ta vrednost se nato uporabi v merilih funkcije FILTER za pridobitev vseh zapisov, kjer je prodajna vrednost večja ali enaka tretji največji vrednosti.

Kliknite tukaj, če želite prenesti datoteko z primerom in nadaljevati

Primer 3: Filtriranje podatkov z več merili (AND)

Recimo, da imate spodnji nabor podatkov in želite filtrirati vse zapise za ZDA, kjer je vrednost prodaje več kot 10000.

To je pogoj AND, pri katerem morate preveriti dve stvari - regija mora v ZDA in prodaja mora biti več kot 10000. Če je izpolnjen le en pogoj, rezultatov ne bi smeli filtrirati.

Spodaj je formula FILTER, ki bo filtrirala zapise z ZDA kot regijo in prodajo več kot 10000:

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

Upoštevajte, da je kriterij (imenovan argument za vključitev) ($ B $ 2: $ B $ 11 = "US")*($ C $ 2: $ C $ 11> 10000)

Ker uporabljam dva pogoja in oba morata biti resnična, sem za združitev teh dveh meril uporabil operater množenja. To vrne niz 0 in 1, kjer se 1 vrne le, če sta izpolnjena oba pogoja.

Če ni zapisov, ki bi izpolnjevali merila, bi funkcija vrnila #CALC! napaka.

In če želite vrniti nekaj pomena (namesto napake), lahko uporabite formulo, kot je prikazano spodaj:

= FILTER ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "ZDA")*($ C $ 2: $ C $ 11> 10000), "Nič najdenega")

Tukaj sem kot tretji argument uporabil »Ni najdeno«, ki se uporabi, ko ni najdenih zapisov, ki bi ustrezali kriterijem.

Primer 4: Filtriranje podatkov po več merilih (ALI)

Argument "include" lahko spremenite tudi v funkciji FILTER, da preverite merila OR (kjer je kateri od danih pogojev lahko resničen).

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite filtrirati zapise, kjer je država ZDA ali Kanada.

Spodaj je formula, ki bo to naredila:

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

Upoštevajte, da sem v zgornji formuli preprosto dodal oba pogoja z uporabo operatorja seštevanja. Ker vsak od teh pogojev vrne niz TRUE in FALSEs, lahko dodam, da dobim kombinirano polje, kjer je TRUE, če je kateri od pogojev izpolnjen.

Drug primer je lahko, če želite filtrirati vse zapise, kjer je država ZDA ali pa je prodajna vrednost večja od 10000.

Spodnja formula bo to naredila:

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

Opomba: Pri uporabi meril AND v funkciji FILTER uporabite operator množenja (*), pri uporabi kriterijev OR pa operator seštevanja (+).

Primer 5: Filtriranje podatkov za zapise nad/pod povprečjem

S filtri s funkcijo FILTER lahko filtrirate in izvlečete zapise, kjer je vrednost nad ali pod povprečjem.

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite filtrirati vse zapise, kjer je vrednost prodaje nadpovprečna.

To lahko storite po naslednji formuli:

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

Podobno lahko za podpovprečno uporabite naslednjo formulo:

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

Kliknite tukaj, če želite prenesti datoteko z primerom in nadaljevati

Primer 6: Filtriranje samo EVEN številčnih zapisov (ali zapisov številk ODD)

V primeru, da morate hitro filtrirati in izvleči vse zapise iz vrstic s sodo ali neparno številko, lahko to storite s funkcijo FILTER.

Če želite to narediti, morate v funkciji FILTER preveriti številko vrstice in filtrirati samo številke vrstic, ki ustrezajo merilom številke vrstice.

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in iz tega nabora podatkov želim izvleči samo parne zapise.

Spodaj je formula, ki bo to naredila:

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

Zgornja formula uporablja funkcijo MOD za preverjanje številke vrstice vsakega zapisa (ki je podana s funkcijo ROW).

Formula MOD (ROW (A2: A11) -1,2) = 0 vrne TRUE, če je številka vrstice soda, in FALSE, če je liha. Upoštevajte, da sem od dela ROW (A2: A11) odštela 1, saj je prvi zapis v drugi vrstici, in to prilagodi številko vrstice, tako da drugo vrstico upoštevam kot prvi zapis.

Podobno lahko filtrirate vse lihe zapise z uporabo spodnje formule:

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

Primer 7: Filtrirane podatke razvrstite s formulo

Uporaba funkcije FILTER z drugimi funkcijami nam omogoča, da naredimo veliko več.

Če na primer filtrirate nabor podatkov s funkcijo FILTER, lahko s funkcijo SORT uporabite že razvrščeni rezultat.

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite filtrirati vse zapise, pri katerih je prodajna vrednost večja od 10000. S funkcijo SORT lahko s funkcijo preverite, ali so nastali podatki razvrščeni glede na prodajno vrednost.

Spodnja formula bo to naredila:

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

Zgornja funkcija uporablja funkcijo FILTER za pridobivanje podatkov, pri katerih je prodajna vrednost v stolpcu C večja od 10000. Ta matrika, ki jo vrne funkcija FILTER, se nato uporabi v funkciji SORT za razvrščanje teh podatkov glede na prodajno vrednost.

Drugi argument v funkciji SORT je 3, ki naj se razvrsti na podlagi tretjega stolpca. In četrti argument je -1, ki naj razvrsti te podatke po padajočem vrstnem redu.

Kliknite tukaj za prenos datoteke primera

Torej je to 7 primerov uporabe funkcije FILTER v Excelu.

Upam, da vam je bila ta vadnica koristna!

Morda vam bodo všeč tudi naslednje vaje za Excel:

1. Kako filtrirati celice s krepkim oblikovanjem pisave v Excelu
2. Iskalno polje za dinamični Excelov filter
3. Kako filtrirati podatke v vrtilni tabeli v Excelu