Excel

FILTER na vrhu n vrednosti s kriteriji

Filter Top N Values With Criteria

Formula Excel: FILTER na vrhu n vrednosti s kriterijiSplošna formula | _+_ | Povzetek

Če želite filtrirati podatke in prikazati zgornjih n vrednosti, ki izpolnjujejo določena merila, lahko uporabite datoteko Funkcija FILTER skupaj z VELIKO in ČE funkcije. V prikazanem primeru je formula v F5:



= FILTER (data,(range>= LARGE ( IF (criteria),n))*(criteria))

kje podatkov (B5: D16), skupina (C5: C16) in rezultat (D5: D16) so imenovanih obsegov .

Pojasnilo

Ta formula uporablja funkcijo FILTER za pridobivanje podatkov na podlagi logičnega testa, izdelanega z VELIKO in ČE funkcije. Rezultat so tri najboljše ocene v skupini B.





Funkcija FILTER uporablja merila z vključujejo prepir. V tem primeru so merila zgrajena z logična logika Všečkaj to:

 
= FILTER (data,(score>= LARGE ( IF (group='b',score),3))*(group='b'))

Leva stran izraza cilja na ocene, ki so večje ali enake tretji najvišji oceni v skupini B:



izbriši prazne celice v
 
(score>= LARGE ( IF (group='b',score),3))*(group='b')

Funkcija IF se uporablja za zagotovitev, da LARGE deluje samo z ocenami skupine B. Ker imamo skupaj 12 točk, IF vrne an matriko z 12 takšnimi rezultati:

 
score>= LARGE ( IF (group='b',score),3)

Upoštevajte, da so edini rezultati, ki preživijo operacijo, iz skupine B. Vse druge ocene so LAŽNE. Ta niz se vrne neposredno v LARGE kot matriko prepir:

 
{FALSE65FALSE80FALSE88FALSE76FALSE86FALSE83}

LARGE prezre vrednosti FALSE in vrne tretjo najvišjo oceno, 83.

Formulo lahko zdaj poenostavimo na:

 
 LARGE ({FALSE65FALSE80FALSE88FALSE76FALSE86FALSE83},3)

ki se reši na:

 
= FILTER (data,(score>=83)*(group='b'))

Nazadnje FILTER vrne zapise za Mason, Annie in Cassidy, ki se razlijejo v obseg F5: H7.

Razvrstite rezultate po rezultatih

FILTER privzeto vrne ujemajoče se zapise v istem vrstnem redu, kot so prikazani v izvornih podatkih. Če želite razvrstiti rezultate padajoči vrstni red po rezultatih , ti lahko gnezdo izvirna formula FILTER v SORT funkcija Všečkaj to:

kako najdem krožne reference v
 
= FILTER (data,{000001000101})

Tu FILTER vrne rezultate neposredno v funkcijo SORT kot matriko prepir. Indeks razvrščanja je nastavljen na 3 (rezultat), vrstni red pa na -1, za padajoče naročilo.

Formule dinamičnega niza so na voljo v Excel 365 samo. Avtor Dave Bruns


^