Excel filtra funkcija - izskaidrota ar piemēriem + video

Skatieties video - Excel FILTER funkciju piemēri

Office 365 piedāvā dažas lieliskas funkcijas, piemēram, XLOOKUP, SORT un FILTER.

Runājot par datu filtrēšanu programmā Excel, iepriekšējā Office 365 pasaulē mēs galvenokārt bijām atkarīgi no Excel iebūvētā filtra vai maksimāli no papildu filtra vai sarežģītām SUMPRODUCT formulām. Gadījumā, ja jums bija jāfiltrē kāda datu kopas daļa, tas parasti bija sarežģīts risinājums (kaut ko es šeit apskatīju).

Bet ar jauno FILTER funkciju tagad ir patiešām viegli ātri filtrēt daļu datu kopas, pamatojoties uz nosacījumu.

Un šajā apmācībā es jums parādīšu, cik lieliska ir jaunā FILTER funkcija un dažas noderīgas lietas, ko varat darīt ar to.

Bet pirms iedziļināties piemēros, ātri uzzināsim par funkcijas FILTER sintaksi.

Ja vēlaties iegūt šīs jaunās funkcijas programmā Excel, varat to izdarīt jauniniet uz Office 365 (pievienojieties iekšējās informācijas programmai, lai piekļūtu visām funkcijām/formulām)

Excel filtra funkcija - sintakse

Zemāk ir funkcijas FILTER sintakse:

= FILTER (masīvs, iekļaut, [if_empty])

• masīvs - tas ir šūnu diapazons, kurā jums ir dati un no kura vēlaties filtrēt dažus datus
• iekļaut - tas ir nosacījums, kas norāda funkcijai, kādus ierakstus filtrēt
• [if_empty] - šis ir fakultatīvs arguments, kurā varat norādīt, ko atdot, ja funkcija FILTER neatrod rezultātus. Pēc noklusējuma (ja nav norādīts), tas atgriež #CALC! kļūda

Tagad apskatīsim dažus pārsteidzošus filtra funkciju piemērus un lietas, ko tā var darīt, kas agrāk bija diezgan sarežģīta, ja tās nebija.

Noklikšķiniet šeit, lai lejupielādētu parauga failu un sekotu tam

1. piemērs. Datu filtrēšana, pamatojoties uz vienu kritēriju (reģionu)

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties filtrēt visus ierakstus tikai ASV.

Zemāk ir FILTER formula, kas to darīs:

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

Iepriekšminētā formula izmanto datu kopu kā masīvu, un nosacījums ir $ B $ 2: $ B $ 11 = ”US”

Šis nosacījums ļautu FILTER funkcijai pārbaudīt visas šūnas B kolonnā (tai, kurai ir reģions) un tiktu filtrēti tikai tie ieraksti, kas atbilst šim kritērijam.

Arī šajā piemērā man ir sākotnējie dati un filtrētie dati vienā lapā, taču tos var iegūt arī atsevišķās lapās vai pat darbgrāmatās.

Filtra funkcija atgriež rezultātu, kas ir dinamisks masīvs (tas nozīmē, ka tā vietā, lai atgrieztu vienu vērtību, tas atgriež masīvu, kas izplūst citās šūnās).

Lai tas darbotos, jums ir jābūt apgabalam, kur rezultāts būtu tukšs. Jebkurā šūnā šajā apgabalā (šajā piemērā E2: G5) jau ir kaut kas, funkcija sniegs jums kļūdu #SPILL.

Turklāt, tā kā šis ir dinamisks masīvs, jūs nevarat mainīt daļu no rezultāta. Varat dzēst visu diapazonu, kurā ir rezultāts, vai šūnu E2 (kur tika ievadīta formula). Abas šīs darbības dzēstu visu iegūto masīvu. Bet jūs nevarat mainīt nevienu atsevišķu šūnu (vai to izdzēst).

Iepriekšminētajā formulā man ir grūti kodēta reģiona vērtība, taču jūs varat to norādīt arī šūnā un pēc tam atsaukties uz šūnu, kurai ir reģiona vērtība.

Piemēram, zemāk redzamajā piemērā man ir reģiona vērtība šūnā I2, un pēc tam uz to atsaucas formulā:

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

Tas padara formulu vēl noderīgāku, un tagad jūs varat vienkārši mainīt reģiona vērtību šūnā I2, un filtrs automātiski mainīsies.

Šūnā I2 var būt arī nolaižamā izvēlne, kurā varat vienkārši veikt atlasi, un tas uzreiz atjauninās filtrētos datus.

2. piemērs. Datu filtrēšana, pamatojoties uz vienu kritēriju (vairāk vai mazāk)

Filtrēšanas funkcijā varat izmantot arī salīdzinošos operatorus un iegūt visus ierakstus, kas ir lielāki vai mazāki par noteiktu vērtību.

Piemēram, pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties filtrēt visus ierakstus, kuru pārdošanas vērtība ir lielāka par 10000.

To var izdarīt zemāk esošā formula:

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

Masīva arguments attiecas uz visu datu kopu, un nosacījums šajā gadījumā ir ($ C $ 2: $ C $ 11> 10000).

Formula pārbauda katru ierakstu attiecībā uz C slejas vērtību. Ja vērtība ir lielāka par 10000, tā tiek filtrēta, pretējā gadījumā tā tiek ignorēta.

Ja vēlaties, lai visi ieraksti būtu mazāki par 10000, varat izmantot šādu formulu:

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

Varat arī kļūt radošāks, izmantojot FILTER formulu. Piemēram, ja vēlaties filtrēt trīs populārākos ierakstus, pamatojoties uz pārdošanas vērtību, varat izmantot šādu formulu:

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

Iepriekšminētā formula izmanto funkciju LARGE, lai iegūtu trešo lielāko vērtību datu kopā. Pēc tam šī vērtība tiek izmantota funkcijas FILTER kritērijos, lai iegūtu visus ierakstus, kur pārdošanas vērtība ir lielāka vai vienāda ar trešo lielāko vērtību.

Noklikšķiniet šeit, lai lejupielādētu parauga failu un sekotu tam

3. piemērs. Datu filtrēšana ar vairākiem kritērijiem (AND)

Pieņemsim, ka jums ir zemāk esošā datu kopa un vēlaties filtrēt visus ASV ierakstus, kur pārdošanas vērtība ir lielāka par 10000.

Šis ir nosacījums UN, kurā jāpārbauda divas lietas - reģionam ir jābūt ASV, un pārdošanas apjomam jābūt lielākam par 10000. Ja ir izpildīts tikai viens nosacījums, rezultātus nevajadzētu filtrēt.

Zemāk ir FILTER formula, kas filtrēs ierakstus ar ASV kā reģionu un pārdošanas apjomu vairāk nekā 10000:

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

Ņemiet vērā, ka kritērijs (saukts par iekļaušanas argumentu) ir ($ B $ 2: $ B $ 11 = ”US”)*($ C $ 2: $ C $ 11> 10000)

Tā kā es izmantoju divus nosacījumus un man ir jābūt abiem, lai tie būtu patiesi, esmu izmantojis reizināšanas operatoru, lai apvienotu šos divus kritērijus. Tādējādi tiek atgriezts 0 un 1 masīvs, kur 1 tiek atgriezts tikai tad, ja ir izpildīti abi nosacījumi.

Gadījumā, ja nav ierakstu, kas atbilst kritērijiem, funkcija atgriež #CALC! kļūda.

Un, ja vēlaties atgriezt kaut ko nozīmīgu (kļūdas vietā), varat izmantot formulu, kā parādīts zemāk:

= FILTRS ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "USA")**($ C $ 2: $ C $ 11> 10000), "Nekas nav atrasts")

Šeit kā trešo argumentu esmu izmantojis “Not Found”, kas tiek izmantots, ja netiek atrasti ieraksti, kas atbilst kritērijiem.

4. piemērs. Datu filtrēšana ar vairākiem kritērijiem (VAI)

Funkcijā FILTER varat arī mainīt argumentu “iekļaut”, lai pārbaudītu VAI kritērijus (ja kāds no dotajiem nosacījumiem var būt patiess).

Piemēram, pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties filtrēt ierakstus, kuros valsts ir ASV vai Kanāda.

Zemāk ir formula, kas to darīs:

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

Ņemiet vērā, ka iepriekšminētajā formulā es vienkārši esmu pievienojis abus nosacījumus, izmantojot pievienošanas operatoru. Tā kā katrs no šiem nosacījumiem atgriež patiesu un nepatiesu masīvu, es varu pievienot, lai iegūtu kombinētu masīvu, kur tas ir PATIESS, ja ir izpildīts kāds no nosacījumiem.

Vēl viens piemērs varētu būt, ja vēlaties filtrēt visus ierakstus, kur valsts ir ASV vai pārdošanas vērtība ir lielāka par 10000.

Tālāk sniegtā formula to darīs:

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

Piezīme. Funkcijā FILTER izmantojot AND kritērijus, izmantojiet reizināšanas operatoru (*) un, lietojot VAI kritērijus, izmantojiet saskaitīšanas operatoru (+).

5. piemērs. Datu filtrēšana, lai iegūtu virs/zem vidējiem ierakstiem

Filtrēšanas funkcijā varat izmantot formulas, lai filtrētu un iegūtu ierakstus, kuru vērtība ir virs vai zem vidējās.

Piemēram, pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties filtrēt visus ierakstus, kur pārdošanas vērtība ir virs vidējās.

To var izdarīt, izmantojot šādu formulu:

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

Līdzīgi, ja rādītājs ir zemāks par vidējo, varat izmantot šādu formulu:

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

Noklikšķiniet šeit, lai lejupielādētu parauga failu un sekotu tam

6. piemērs. Filtrēt tikai EVEN numuru ierakstus (vai ODD numuru ierakstus)

Ja jums ir nepieciešams ātri filtrēt un izvilkt visus ierakstus no pāra vai nepāra skaitļu rindām, to var izdarīt, izmantojot funkciju FILTER.

Lai to izdarītu, jums jāpārbauda rindas numurs FILTRA funkcijā un jāfiltrē tikai rindu numuri, kas atbilst rindas numura kritērijiem.

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un es vēlos no šīs datu kopas iegūt tikai pāra numura ierakstus.

Zemāk ir formula, kas to darīs:

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

Iepriekš minētā formula izmanto MOD funkciju, lai pārbaudītu katra ieraksta rindas numuru (ko norāda ROW funkcija).

Formula MOD (ROW (A2: A11) -1,2) = 0 atgriež TRUE, ja rindas numurs ir pāra, un FALSE, ja tas ir nepāra. Ņemiet vērā, ka esmu atņēmis 1 no ROW (A2: A11) daļas, jo pirmais ieraksts atrodas otrajā rindā, un tas pielāgo rindas numuru, lai otro rindu uzskatītu par pirmo ierakstu.

Līdzīgi varat filtrēt visus nepāra numura ierakstus, izmantojot šādu formulu:

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

7. piemērs. Kārtojiet filtrētos datus ar formulu

FILTRA funkcijas izmantošana ar citām funkcijām ļauj mums paveikt daudz vairāk.

Piemēram, ja filtrējat datu kopu, izmantojot funkciju FILTER, varat izmantot funkciju SORT, lai iegūtu jau sakārtotu rezultātu.

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties filtrēt visus ierakstus, kuru pārdošanas vērtība ir lielāka par 10000. Jūs varat izmantot funkciju SORT ar funkciju, lai pārliecinātos, ka iegūtie dati ir sakārtoti, pamatojoties uz pārdošanas vērtību.

Tālāk sniegtā formula to darīs:

= Kārtot (FILTRS ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> 10000)), 3, -1)

Iepriekš minētā funkcija izmanto funkciju FILTER, lai iegūtu datus, kur pārdošanas vērtība C slejā ir lielāka par 10000. Šis FILTER funkcijas atgrieztais masīvs pēc tam tiek izmantots funkcijā SORT, lai kārtotu šos datus, pamatojoties uz pārdošanas vērtību.

Otrs arguments kārtošanas funkcijā ir 3, un tas ir jāšķiro, pamatojoties uz trešo kolonnu. Un ceturtais arguments ir -1, kas ir sakārtot šos datus dilstošā secībā.

Noklikšķiniet šeit, lai lejupielādētu parauga failu

Tātad šie ir 7 piemēri funkcijas FILTER izmantošanai programmā Excel.

Ceru, ka šī apmācība jums šķita noderīga!

Jums varētu patikt arī šādas Excel apmācības:

1. Kā filtrēt šūnas ar treknrakstu fontu formātā programmā Excel
2. Dinamiskā Excel filtra meklēšanas lodziņš
3. Kā filtrēt datus rakurstabulā programmā Excel