Exceli filtri funktsioon - selgitatud näidetega + video

Vaadake videot - Exceli filtri funktsiooninäited

Office 365 pakub suurepäraseid funktsioone, näiteks XLOOKUP, SORT ja FILTER.

Mis puudutab andmete filtreerimist Excelis, siis Office 365-eelses maailmas sõltusime peamiselt Exceli sisseehitatud filtrist või maksimaalselt täpsemast filtrist või keerukatest SUMPRODUCT valemitest. Kui peaksite andmekogumi osa filtreerima, oli see tavaliselt keeruline lahendus (mida ma siin käsitlen).

Kuid uue FILTER -funktsiooni abil on nüüd tõesti lihtne osa andmekogumit tingimuse alusel kiiresti filtreerida.

Ja selles õpetuses näitan teile, kui fantastiline on uus FILTER -funktsioon ja mõned kasulikud asjad, mida saate sellega teha.

Kuid enne, kui näitedesse asun, tutvume kiiresti funktsiooni FILTER süntaksiga.

Kui soovite neid uusi funktsioone Excelis hankida, saate seda teha uuendada Office 365 -le (liituge siseringi programmiga, et saada juurdepääs kõigile funktsioonidele/valemitele)

Exceli filtri funktsioon - süntaks

Allpool on funktsiooni FILTER süntaks:

= FILTER (massiiv, kaasake, [if_empty])

• massiiv - see on lahtrite vahemik, kus teil on andmed ja soovite mõningaid andmeid sealt filtreerida
• kaasata - see on tingimus, mis ütleb funktsioonile, milliseid kirjeid filtreerida
• [kui_tühi] - see on valikuline argument, kus saate määrata, mida tagastada, kui funktsioon FILTER ei leia tulemusi. Vaikimisi (kui pole määratud) tagastab see #CALC! viga

Nüüd vaatame mõningaid hämmastavaid filtrite funktsioonide näiteid ja asju, mida see saab teha, mis oli selle puudumisel üsna keeruline.

Näidisfaili allalaadimiseks klõpsake siin ja järgige

Näide 1: andmete filtreerimine ühe kriteeriumi (piirkonna) alusel

Oletame, et teil on allpool näidatud andmekogum ja soovite filtreerida kõik kirjed ainult USA jaoks.

Allpool on FILTER valem, mis seda teeb:

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

Ülaltoodud valem kasutab massiivina andmestikku ja tingimus on $ B $ 2: $ B $ 11 = ”US”

See tingimus paneks FILTER -funktsiooni kontrollima kõiki veeru B lahtreid (seda, millel on piirkond) ja filtreeritakse ainult need kirjed, mis sellele kriteeriumile vastavad.

Ka selles näites on mul algandmed ja filtreeritud andmed samal lehel, kuid need võivad olla ka eraldi lehtedel või isegi töövihikutes.

Filtri funktsioon tagastab tulemuse, mis on dünaamiline massiiv (see tähendab, et ühe väärtuse tagastamise asemel tagastab see massiivi, mis voolab teistesse lahtritesse).

Selle toimimiseks peab teil olema ala, kus tulemus oleks tühi. Selle piirkonna lahtrites (selles näites E2: G5) on juba midagi sees, annab funktsioon teile vea #SPILL.

Kuna see on dünaamiline massiiv, ei saa te tulemuse osa muuta. Saate kustutada kogu tulemusvahemiku või lahtri E2 (kuhu valem sisestati). Mõlemad kustutaksid kogu saadud massiivi. Kuid te ei saa ühtegi lahtrit muuta (või kustutada).

Ülaltoodud valemis olen piirkonna väärtuse kõvasti kodeerinud, kuid võite selle ka lahtrisse paigutada ja seejärel sellele piirkonna väärtusega lahtrile viidata.

Näiteks allolevas näites on mul piirkonna väärtus lahtris I2 ja sellele viidatakse seejärel valemis:

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

See muudab valemi veelgi kasulikumaks ja nüüd saate lihtsalt muuta lahtri I2 piirkonna väärtust ja filter muutub automaatselt.

Teil võib olla ka rippmenüü lahtris I2, kus saate lihtsalt valiku teha ja see värskendaks koheselt filtreeritud andmeid.

Näide 2: andmete filtreerimine ühe kriteeriumi alusel (rohkem kui vähem)

Samuti saate filtreerimisfunktsioonis kasutada võrdlevaid operaatoreid ja eraldada kõik kirjed, mis on rohkem või vähem kui konkreetne väärtus.

Oletame näiteks, et teil on allpool näidatud andmekogum ja soovite filtreerida kõik kirjed, mille müügiväärtus on üle 10000.

Seda saab teha järgmise valemiga:

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

Massiivi argument viitab kogu andmestikule ja tingimus on sel juhul ($ C $ 2: $ C $ 11> 10000).

Valem kontrollib iga kirje veeru C väärtust. Kui väärtus on üle 10 000, filtreeritakse see, vastasel juhul ignoreeritakse seda.

Kui soovite saada kõik kirjed alla 10000, võite kasutada järgmist valemit:

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

FILTER valemiga saate ka loovamaks muutuda. Näiteks kui soovite filtreerida müügiväärtuse põhjal kolme esimest kirjet, võite kasutada järgmist valemit:

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

Ülaltoodud valem kasutab andmestiku suuruselt kolmanda väärtuse saamiseks funktsiooni LARGE. Seda väärtust kasutatakse seejärel funktsiooni FILTER kriteeriumides, et saada kõik kirjed, mille müügiväärtus on suurem või võrdne suuruselt kolmandast väärtusest.

Näidisfaili allalaadimiseks klõpsake siin ja järgige

Näide 3: andmete filtreerimine mitme kriteeriumiga (AND)

Oletame, et teil on allpool olev andmekogum ja soovite filtreerida kõik USA kirjed, kus müügi väärtus on üle 10000.

See on JA -tingimus, kus peate kontrollima kahte asja - piirkond vajab USA -d ja müük peab olema suurem kui 10 000. Kui täidetud on ainult üks tingimus, ei tohiks tulemusi filtreerida.

Allpool on FILTER -valem, mis filtreerib kirjed, kus USA on piirkond ja müük üle 10000:

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

Pange tähele, et kriteerium (mida nimetatakse kaasamisargumendiks) on ($ B $ 2: $ B $ 11 = ”US”)*($ C $ 2: $ C $ 11> 10000)

Kuna kasutan kahte tingimust ja mul on vaja, et mõlemad oleksid tõesed, kasutasin nende kahe kriteeriumi ühendamiseks korrutusoperaatorit. See tagastab massiivi 0 ja 1, kus 1 tagastatakse ainult siis, kui mõlemad tingimused on täidetud.

Kui kriteeriumidele vastavaid kirjeid pole, tagastab funktsioon #CALC! viga.

Ja kui soovite tagastada midagi tähendust (vea asemel), võite kasutada allpool näidatud valemit:

= FILTER ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "USA")**($ C $ 2: $ C $ 11> 10000), "Midagi ei leitud")

Siin olen kolmanda argumendina kasutanud “Not Found”, mida kasutatakse siis, kui kriteeriumidele vastavaid kirjeid ei leita.

Näide 4: andmete filtreerimine mitme kriteeriumiga (OR)

Funktsioonis FILTER saate muuta ka argumenti „kaasata“, et kontrollida VÕI kriteeriume (kus mõni antud tingimustest võib tõene olla).

Oletame näiteks, et teil on allpool näidatud andmekogum ja soovite filtreerida kirjeid, kus riik on kas USA või Kanada.

Allpool on valem, mis seda teeb:

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

Pange tähele, et ülaltoodud valemis olen lisanud need kaks tingimust lisamisoperaatori abil. Kuna kõik need tingimused tagastavad massiivi tõdesid ja valesid, võin lisada, et saada kombineeritud massiiv, kus see on tõene, kui mõni tingimustest on täidetud.

Teine näide võib olla see, kui soovite filtreerida kõik kirjed, kus riik on USA või müügiväärtus on üle 10 000.

Seda teeb järgmine valem:

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

Märkus. Kui kasutate FILTER -funktsioonis AND -kriteeriume, kasutage korrutusoperaatorit (*) ja VÕI -kriteeriumide kasutamisel liitmisoperaatorit (+).

Näide 5: andmete filtreerimine, et jõuda üle/alla andmete

Funktsiooni FILTER valemite abil saate filtreerida ja ekstraheerida kirjeid, mille väärtus on üle keskmise või alla selle.

Oletame näiteks, et teil on allpool näidatud andmekogum ja soovite filtreerida kõik kirjed, mille müügiväärtus on üle keskmise.

Seda saate teha järgmise valemi abil:

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

Sarnaselt saate keskmise väärtuse korral kasutada järgmist valemit:

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

Näidisfaili allalaadimiseks klõpsake siin ja järgige

Näide 6: ainult EVEN -numbrikirjete (või ODD -numbrikirjete) filtreerimine

Kui teil on vaja kiiresti filtreerida ja ekstraheerida kõik kirjed paarisarvude või paaritute arvude ridadest, saate seda teha FILTER -funktsiooniga.

Selleks peate funktsiooni FILTER raames kontrollima rea ​​numbrit ja filtreerima ainult reanumbreid, mis vastavad reanumbri kriteeriumidele.

Oletame, et teil on allpool näidatud andmekogum ja ma tahan sellest andmestikust välja võtta ainult paarisarvulised kirjed.

Allpool on valem, mis seda teeb:

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

Ülaltoodud valem kasutab funktsiooni MOD iga kirje rea numbri kontrollimiseks (mille annab funktsioon ROW).

Valem MOD (RIDA (A2: A11) -1,2) = 0 tagastab tõe, kui rea number on paaris ja vale, kui see on paaritu. Pange tähele, et olen ROW (A2: A11) osast lahutanud 1, kuna esimene kirje on teisel real ja see kohandab rea numbrit, et lugeda teist rida esimeseks kirjeks.

Samamoodi saate filtreerida kõik paaritu numbriga kirjed, kasutades järgmist valemit:

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

Näide 7: Sorteerige filtreeritud andmed valemiga

FILTER -funktsiooni kasutamine koos muude funktsioonidega võimaldab meil palju rohkem ära teha.

Näiteks kui filtreerite andmekogumit funktsiooni FILTER abil, saate koos funktsiooniga SORT kasutada juba sorteeritud tulemuse saamiseks.

Oletame, et teil on allpool näidatud andmekogum ja soovite filtreerida kõik kirjed, mille müügiväärtus on suurem kui 10000. Funktsiooni SORT abil saate koos funktsiooniga veenduda, et saadud andmed sorteeritakse müügiväärtuse alusel.

Seda teeb järgmine valem:

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

Ülaltoodud funktsioon kasutab funktsiooni FILTER andmete saamiseks, kui veeru C müügiväärtus on suurem kui 10000. Funktsiooni FILTER tagastatud massiivi kasutatakse seejärel funktsioonis SORT nende andmete sortimiseks müügiväärtuse alusel.

Funktsiooni SORT teine ​​argument on 3, mida tuleb sortida kolmanda veeru alusel. Ja neljas argument on -1, mis on nende andmete sortimine kahanevas järjekorras.

Näidisfaili allalaadimiseks klõpsake siin

Nii et need on 7 näidet funktsiooni FILTER kasutamiseks Excelis.

Loodan, et leidsite selle õpetuse kasulikuks!

Samuti võivad teile meeldida järgmised Exceli õpetused:

1. Kuidas filtreerida lahtreid rasvaste fontide vormindamisega Excelis
2. Dünaamiline Exceli filtri otsingukast
3. Kuidas Excelis liigendtabelis andmeid filtreerida