Kako uporabljati Excelovo funkcijo FILTER

Office 365 prinaša nekaj odličnih funkcij, kot so XLOOKUP, SORT in FILTER.

Pri filtriranju podatkov v Excelu smo se v svetu pred Office 365 večinoma zanašali na vgrajene filtre v Excelu ali na najbolj napredne filtre ali zapletene formule SUMPRODUCT.To je pogosto zapletena rešitev, če morate filtrirati del nabora podatkov.

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

V tej vadnici vam bom pokazal, kako odlična je nova funkcija FILTER in nekaj uporabnih stvari, ki jih lahko naredite z njo.

Toda preden se lotim primera, si oglejmo na hitro sintakso funkcije FILTER.

Če želite pridobiti te nove funkcije v Excelu, lahkoNadgradite na Office 365(Pridružite se notranjemu programu za dostop do vseh funkcij/formul)

Funkcija Excelovega filtra – sintaksa

Sledi sintaksa funkcije FILTER:

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

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

Zdaj pa poglejmo nekaj neverjetnih primerov funkcij filtra in kaj lahko naredi, kar je bilo včasih zelo zapleteno brez njega.

Primer 1: Filtrirajte podatke na podlagi enega pogoja (regija)

Recimo, da imate nabor podatkov, kot je spodnji, in želite filtrirati samo vse zapise v Združenih državah.

Tukaj je formula FILTER, ki to naredi:

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

Zgornja formula vzame nabor podatkov kot matriko, pogoj pa je $B$2:$B$11=”ZDA”

Ta pogoj bo povzročil, da bo funkcija FILTER pregledala vsako celico v stolpcu B (celice, ki imajo obseg) in filtrirala samo tiste zapise, ki se ujemajo s tem pogojem.

Tudi v tem primeru sem dal izvirne in filtrirane podatke na isti list, lahko pa jih tudi v ločene liste ali celo delovne zvezke.

Funkcija filtra vrne dinamično matriko rezultatov (kar pomeni, da namesto vrnitve vrednosti vrne matriko, ki se preliva v druge celice).

Za to morate imeti območje, kjer je rezultat prazen.V kateri koli celici v obsegu je že nekaj (E2:G5 v tem primeru) in funkcija vam bo dala napako #SPILL.

Ker je to dinamična matrika, dela rezultata ne morete spremeniti.Celoten obseg lahko izbrišete z rezultatom ali celico E2 (kjer je vnesena formula).Oboje bo izbrisalo celotno niz rezultatov.Vendar ne morete spremeniti nobene posamezne celice (ali je izbrisati).

V zgornji formuli sem trdo kodiral vrednost obsega, lahko pa jo vstavite tudi v celico in se sklicujete na to celico z vrednostjo obsega.

Na primer, v spodnjem primeru imam vrednost obsega v celici I2 in jo nato sklicujem v formuli:

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

Zaradi tega je formula še bolj uporabna, zdaj morate samo spremeniti vrednost obsega v celici I2 in filter se bo samodejno spremenil.

Prav tako imate lahko spustni meni v celici I2, kjer lahko preprosto izberete in bo takoj posodobil filtrirane podatke.

Primer 2: Filtrirajte podatke na podlagi enega merila (večje ali manjše od)

Uporabite lahko tudi primerjalne operatorje v funkcijah filtra in izvlečete vse zapise, večje ali manjše od določene vrednosti.

Recimo, da imate nabor podatkov, prikazan spodaj, in želite filtrirati vse zapise s prodajo nad 10000.

To lahko naredi naslednja formula:

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

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

Formula preveri vsak zapis za vrednost v stolpcu C.Če je vrednost večja od 10000, se filtrira, v nasprotnem primeru se prezre.

Če želite dobiti vse zapise manj kot 10000, lahko uporabite naslednjo formulo:

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

S formulo FILTER lahko postanete tudi bolj ustvarjalni.Če bi na primer želeli filtrirati prve tri zapise glede na prodajo, lahko uporabite naslednjo formulo:

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

Zgornja formula uporablja funkcijo LARGE za pridobitev tretje največje vrednosti v naboru podatkov.Nato uporabite to vrednost v pogoju funkcije FILTER, da dobite vse zapise s prodajo, ki je večja ali enaka tretji največji vrednosti.

3. primer: filtriranje podatkov z več pogoji (IN)

Recimo, da imate naslednji nabor podatkov in želite filtrirati vse zapise v Združenih državah s prodajno vrednostjo, večjo od 10000.

To je pogoj IN, preveriti morate dve stvari - regija mora biti v ZDA in prodaja mora biti več kot 10000.Če je izpolnjen samo en pogoj, rezultatov ni treba filtrirati.

Tukaj je formula za filtriranje, ki bo filtrirala zapise z ZDA kot regijo in z več kot 10000 prodajami:

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

請注意，標準（稱為包含參數）是 ($B$2:$B$11=”US”)*($C$2:$C$11>10000)

Ker uporabljam dva pogoja in moram, da sta oba resnična, sem uporabil operator množenja, da združim oba pogoja.To vrne matriko 0s in 1s, kjer je 1 vrnjena le, če sta izpolnjena oba pogoja.

Če ni ustreznih zapisov, bo funkcija vrnila #CALC!Napaka.

Če želite vrniti nekaj smiselnega (ne napake), lahko uporabite formulo, kot je ta:

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

Tukaj sem uporabil "ni najdeno" kot tretji parameter, ki se uporablja, ko ni najdenega ujemajočega zapisa.

Primer 4: Filtriranje podatkov z več merili (ALI)

Prav tako lahko spremenite parameter "vsebuje" v funkciji FILTER, da preverite pogoje ALI (kjer je kateri koli dani pogoj lahko resničen).

Recimo, da imate nabor podatkov, prikazan spodaj, in želite filtrirati zapise, kjer je država Združene države ali Kanada.

Tukaj je formula za to:

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

Upoštevajte, da v zgornji formuli samo dodam dva pogoja z operatorjem seštevanja.Ker vsak od teh pogojev vrne matriko TRUE in FALSE, lahko dodam kombinirano matriko, ki bo TRUE, če bo izpolnjen kateri koli pogoj.

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

To bo naredila naslednja formula:

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

OPOMBA: Pri uporabi pogoja IN v funkciji FILTER uporabite operator množenja (*), pri uporabi pogoja ALI pa operator seštevanja (+).

Primer 5: Filtrirajte podatke za zapise nad/pod povprečjem

Formule v funkciji FILTER lahko uporabite za filtriranje in ekstrahiranje zapisov z vrednostmi nad ali pod povprečjem.

Recimo, da imate nabor podatkov, prikazan spodaj, in želite filtrirati vse zapise z vrednostjo prodaje nad povprečjem.

To lahko storite z naslednjo formulo:

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

Še enkrat, za podpovprečje lahko uporabite naslednjo formulo:

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

Primer 6: Filtrirajte samo sode zapise (ali lihe zapise)

Če morate hitro filtrirati in ekstrahirati vse zapise v sodih ali lihih vrsticah, lahko to storite s funkcijo FILTER.

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

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

Tukaj je formula za to:

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

Zgornja formula uporablja funkcijo MOD za preverjanje številke vrstice (ki jo poda funkcija ROW) vsakega zapisa.

Formula MOD(ROW(A2:A11)-1,2)=0 vrne TRUE, ko je številka vrstice soda, in FALSE, če je liha.Upoštevajte, da sem od dela ROW(A2:A11) odštel 1, ker je prvi zapis v drugi vrstici, ki prilagodi številke vrstic tako, da se druga vrstica obravnava kot prvi zapis.

Prav tako lahko filtrirate vse nenavadne zapise z naslednjo formulo:

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

Primer 7: Razvrščanje filtriranih podatkov s formulo

Uporaba funkcije FILTER z drugimi funkcijami nam omogoča več.

Če na primer uporabite funkcijo FILTER za filtriranje nabora podatkov, lahko uporabite funkcijo SORT, da dobite razvrščene rezultate.

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite filtrirati vse zapise s prodajo nad 10000.S to funkcijo lahko uporabite funkcijo SORT, da zagotovite, da so nastali podatki razvrščeni glede na prodajo.

To bo naredila naslednja formula:

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

Zgornja funkcija uporablja funkcijo FILTER za pridobitev podatkov v stolpcu C s prodajo, večjo od 10000.Nato uporabite matriko, ki jo vrne funkcija FILTER v funkciji SORT, da razvrstite podatke glede na prodajo.

Drugi parameter v funkciji SORT je 3, ki naj bi bil razvrščen po tretjem stolpcu.Četrti parameter je -1, ki razvrsti podatke v padajočem vrstnem redu.

To je torej 7 primerov uporabe funkcije FILTER v Excelu.

Upam, da se vam je ta vadnica zdela koristna.