„Excel“ filtro funkcija - paaiškinta pavyzdžiais ir vaizdo įrašu

Žiūrėti vaizdo įrašą - „Excel FILTER“ funkcijų pavyzdžiai

„Office 365“ siūlo keletą nuostabių funkcijų, tokių kaip „XLOOKUP“, „RŪŠYTI“ ir „FILTER“.

Kalbant apie duomenų filtravimą „Excel“, „Office 365“ pasaulyje mes daugiausia priklausėme nuo „Excel“ integruoto filtro arba ne daugiau kaip išplėstinio filtro arba sudėtingų SUMPRODUCT formulių. Jei turėjote filtruoti dalį duomenų rinkinio, tai paprastai buvo sudėtingas sprendimas (ką aš čia aprašiau).

Tačiau naudojant naują FILTER funkciją dabar labai lengva greitai filtruoti dalį duomenų rinkinio pagal sąlygą.

Šioje pamokoje aš jums parodysiu, kokia nuostabi yra naujoji FILTRO funkcija ir kai kurie naudingi dalykai, kuriuos galite padaryti naudodami šią funkciją.

Tačiau prieš pradėdamas nagrinėti pavyzdžius, greitai sužinokime apie funkcijos FILTRAS sintaksę.

Jei norite gauti šias naujas „Excel“ funkcijas, galite atnaujinti į „Office 365“ (prisijunkite prie viešai neatskleistos programos, kad gautumėte prieigą prie visų funkcijų/formulių)

„Excel“ filtro funkcija - sintaksė

Žemiau yra funkcijos FILTER sintaksė:

= FILTRAS (masyvas, įtraukite, [if_empty])

• masyvas - tai yra ląstelių diapazonas, kuriame turite duomenis ir norite iš jo filtruoti kai kuriuos duomenis
• įtraukti - tai sąlyga, nurodanti funkcijai, kokius įrašus filtruoti
• [if_empty] - tai neprivalomas argumentas, kuriame galite nurodyti, ką grąžinti, jei funkcija FILTER neranda rezultatų. Pagal numatytuosius nustatymus (kai nenurodyta), jis grąžina #CALC! klaida

Dabar pažvelkime į keletą nuostabių filtrų funkcijų pavyzdžių ir dalykų, kuriuos jis gali padaryti, o anksčiau tai buvo gana sudėtinga.

Spustelėkite čia, norėdami atsisiųsti pavyzdinį failą ir sekti toliau

1 pavyzdys: duomenų filtravimas pagal vieną kriterijų (regioną)

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite filtruoti visus tik JAV įrašus.

Žemiau yra FILTER formulė, kuri tai padarys:

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

Aukščiau pateikta formulė naudoja duomenų rinkinį kaip masyvą, o sąlyga yra $ B $ 2: $ B $ 11 = ”US”

Ši sąlyga priverstų FILTER funkciją patikrinti kiekvieną B stulpelio langelį (tą, kuriame yra regionas) ir būtų filtruojami tik tie įrašai, kurie atitinka šį kriterijų.

Be to, šiame pavyzdyje aš turiu pirminius duomenis ir filtruotus duomenis tame pačiame lape, tačiau juos taip pat galite turėti atskiruose lapuose ar net darbaknygėse.

Filtro funkcija grąžina dinaminio masyvo rezultatą (tai reiškia, kad užuot grąžinusi vieną reikšmę, ji grąžina masyvą, kuris išsilieja į kitas ląsteles).

Kad tai veiktų, turite turėti sritį, kurioje rezultatas būtų tuščias. Bet kurioje šios srities ląstelėje (šiame pavyzdyje E2: G5) jau yra kažkas, funkcija pateiks #SPILL klaidą.

Be to, kadangi tai yra dinaminis masyvas, negalite pakeisti dalies rezultato. Galite ištrinti visą diapazoną, kuriame yra rezultatas, arba langelį E2 (kur buvo įvesta formulė). Abu jie ištrins visą gautą masyvą. Bet jūs negalite pakeisti jokios atskiros ląstelės (arba jos ištrinti).

Anksčiau pateiktoje formulėje aš sunkiai užkoduoju regiono vertę, tačiau ją taip pat galite turėti langelyje ir tada nurodyti tą langelį, kuris turi regiono reikšmę.

Pavyzdžiui, žemiau esančiame pavyzdyje aš turiu regiono reikšmę ląstelėje I2, ir tai nurodoma formulėje:

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

Tai daro formulę dar naudingesnę ir dabar galite tiesiog pakeisti regiono reikšmę ląstelėje I2 ir filtras automatiškai pasikeis.

Taip pat galite turėti išskleidžiamąjį meniu langelyje I2, kur galite tiesiog pasirinkti ir jis iškart atnaujins filtruotus duomenis.

2 pavyzdys: duomenų filtravimas pagal vieną kriterijų (daugiau nei mažiau)

Filtro funkcijoje taip pat galite naudoti lyginamuosius operatorius ir išgauti visus įrašus, kurie yra didesni ar mažesni už konkrečią vertę.

Pvz., Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite filtruoti visus įrašus, kurių pardavimo vertė yra didesnė nei 10000.

Žemiau pateikta formulė gali tai padaryti:

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

Masyvo argumentas nurodo visą duomenų rinkinį, o sąlyga šiuo atveju yra ($ C $ 2: $ C $ 11> 10000).

Formulė tikrina kiekvieną stulpelio C įrašo vertę. Jei vertė didesnė nei 10000, ji filtruojama, kitu atveju ji nepaisoma.

Jei norite, kad visi įrašai būtų mažesni nei 10000, galite naudoti šią formulę:

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

Taip pat galite būti kūrybiškesni naudodami FILTER formulę. Pavyzdžiui, jei norite filtruoti tris populiariausius įrašus pagal pardavimo vertę, galite naudoti šią formulę:

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

Aukščiau pateikta formulė naudoja LARGE funkciją, kad gautų trečią pagal dydį duomenų rinkinio vertę. Tada ši vertė naudojama FILTRO funkcijos kriterijuose, kad būtų gauti visi įrašai, kuriuose pardavimo vertė yra didesnė arba lygi trečiai pagal dydį vertei.

Spustelėkite čia, norėdami atsisiųsti pavyzdinį failą ir sekti toliau

3 pavyzdys: duomenų filtravimas pagal kelis kriterijus (AND)

Tarkime, kad turite toliau pateiktą duomenų rinkinį ir norite filtruoti visus JAV įrašus, kurių pardavimo vertė yra didesnė nei 10000.

Tai yra AND sąlyga, kai reikia patikrinti du dalykus - regionas turi būti JAV, o pardavimas turi būti didesnis nei 10000. Jei įvykdyta tik viena sąlyga, rezultatai neturėtų būti filtruojami.

Žemiau yra FILTER formulė, kuri filtruos įrašus su JAV kaip regionu ir pardavimais daugiau nei 10000:

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

Atminkite, kad kriterijus (vadinamas įtraukimo argumentu) yra ($ B $ 2: $ B $ 11 = ”US”)*($ C $ 2: $ C $ 11> 10000)

Kadangi naudoju dvi sąlygas ir man reikia, kad abi būtų teisingos, aš panaudojau daugybos operatorių, kad sujungčiau šiuos du kriterijus. Tai grąžina 0 ir 1 masyvą, kur 1 grąžinamas tik tada, kai įvykdomos abi sąlygos.

Jei nėra kriterijus atitinkančių įrašų, funkcija grąžins #CALC! klaida.

Ir jei norite grąžinti kažką reikšmingo (vietoj klaidos), galite naudoti toliau pateiktą formulę:

= FILTRAS ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "USA")**($ C $ 2: $ C $ 11> 10000), "Nieko nerasta")

Čia aš naudoju „Not Found“ kaip trečiąjį argumentą, kuris naudojamas, kai nerandama kriterijus atitinkančių įrašų.

4 pavyzdys: duomenų filtravimas pagal kelis kriterijus (OR)

Funkcijoje FILTER taip pat galite pakeisti argumentą „įtraukti“, kad patikrintumėte ARBA kriterijus (kai bet kuri iš nurodytų sąlygų gali būti teisinga).

Pvz., Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite filtruoti įrašus, kuriuose šalis yra JAV arba Kanada.

Žemiau yra formulė, kuri tai padarys:

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

Atkreipkite dėmesį, kad aukščiau pateiktoje formulėje aš tiesiog pridėjau abi sąlygas naudodamas pridėjimo operatorių. Kadangi kiekviena iš šių sąlygų pateikia TRUE ir FALSE masyvą, galiu pridėti, kad gautumėte kombinuotą masyvą, kuriame jis yra TRUE, jei įvykdoma kuri nors iš sąlygų.

Kitas pavyzdys galėtų būti tada, kai norite filtruoti visus įrašus, kuriuose šalis yra JAV arba pardavimo vertė yra didesnė nei 10000.

Žemiau pateikta formulė tai padarys:

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

Pastaba: Funkcijoje FILTER naudodami AND kriterijus, naudokite daugybos operatorių (*), o OR kriterijus - pridėjimo operatorių (+).

5 pavyzdys: duomenų filtravimas, kad įrašai būtų aukštesni arba žemesni už vidutinius

Filtravimo funkcijos formulėse galite filtruoti ir išgauti įrašus, kurių vertė yra didesnė arba mažesnė už vidurkį.

Pvz., Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite filtruoti visus įrašus, kuriuose pardavimo vertė yra didesnė nei vidutinė.

Tai galite padaryti naudodami šią formulę:

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

Panašiai, jei rodiklis yra mažesnis nei vidutinis, galite naudoti šią formulę:

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

Spustelėkite čia, kad atsisiųstumėte pavyzdinį failą ir sekite toliau

6 pavyzdys: tik EVEN skaičių įrašų (arba ODD numerių įrašų) filtravimas

Jei jums reikia greitai filtruoti ir išgauti visus įrašus iš lyginių ar nelyginių skaičių eilučių, tai galite padaryti naudodami funkciją FILTER.

Norėdami tai padaryti, turite patikrinti FILTER funkcijos eilutės numerį ir filtruoti tik tuos eilutės numerius, kurie atitinka eilutės numerio kriterijus.

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir aš noriu iš šio duomenų rinkinio ištraukti tik lyginius įrašus.

Žemiau yra formulė, kuri tai padarys:

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

Aukščiau pateikta formulė naudoja MOD funkciją, kad patikrintų kiekvieno įrašo eilutės numerį (kurį nurodo funkcija ROW).

Formulė MOD (ROW (A2: A11) -1,2) = 0 grąžina TRUE, kai eilutės numeris yra lyginis, o FALSE, kai yra nelyginis. Atminkite, kad aš atėmiau 1 iš ROW (A2: A11) dalies, nes pirmasis įrašas yra antroje eilutėje, ir tai koreguoja eilutės numerį, kad antroji eilutė būtų laikoma pirmuoju.

Panašiai galite filtruoti visus nelyginius įrašus naudodami šią formulę:

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

7 pavyzdys: surūšiuokite filtruotus duomenis pagal formulę

FILTRO funkcijos naudojimas su kitomis funkcijomis leidžia mums nuveikti daug daugiau.

Pvz., Jei filtruojate duomenų rinkinį naudodami funkciją FILTER, galite naudoti funkciją RŪŠYTI, kad gautumėte jau surūšiuotą rezultatą.

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite filtruoti visus įrašus, kurių pardavimo vertė yra didesnė nei 10000. Galite naudoti funkciją RŪŠYTI su funkcija, kad įsitikintumėte, jog gauti duomenys surūšiuoti pagal pardavimo vertę.

Žemiau pateikta formulė tai padarys:

= RŪŠIUOTI (FILTRAS ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> 10000)), 3, -1)

Aukščiau pateikta funkcija naudoja funkciją FILTER, kad gautų duomenis, kai pardavimo stulpelio C vertė yra didesnė nei 10000. Šis FILTER funkcijos grąžintas masyvas vėliau naudojamas funkcijoje RŪŠIUO, siekiant surūšiuoti šiuos duomenis pagal pardavimo vertę.

Antrasis funkcijos RŪŠYMAS argumentas yra 3, kuris turi būti rūšiuojamas pagal trečiąjį stulpelį. Ketvirtasis argumentas yra -1, ty surūšiuoti šiuos duomenis mažėjančia tvarka.

Spustelėkite čia, jei norite atsisiųsti pavyzdinį failą

Taigi, tai yra 7 pavyzdžiai, kaip naudoti funkciją FILTER programoje „Excel“.

Tikimės, kad ši pamoka jums buvo naudinga!

Jums taip pat gali patikti šios „Excel“ pamokos:

1. Kaip filtruoti langelius su paryškintu šriftu „Excel“
2. Dinaminio „Excel“ filtro paieškos laukelis
3. Kaip filtruoti duomenis „Pivot“ lentelėje „Excel“