Excel

FILTRIRAJ na vrh n vrednosti z merili

Filter Top N Values With Criteria

Excel formula: FILTRIRAJ na vrh n vrednosti z meriliSplošna formula
= FILTER (data,(range>= LARGE ( IF (criteria),n))*(criteria))
Povzetek

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

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

kje podatkov (B5: D16), skupini (C5: C16) in rezultat (D5: D16) so imenovani obsegi .



Pojasnilo

Ta formula uporablja funkcijo FILTER za pridobivanje podatkov na podlagi logičnega testa, zgrajenega z VELIKO in ČE funkcije. Rezultat so top 3 rezultati v skupini B.



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

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

Leva stran izraza cilja na rezultate, večje ali enake 3. najvišji oceni v skupini B:



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

Funkcija IF se uporablja za zagotovitev, da LARGE deluje samo z rezultati skupine B. Ker imamo skupaj 12 rezultatov, IF vrne matriko s 12 rezultati, kot je ta:

 
{FALSE65FALSE80FALSE88FALSE76FALSE86FALSE83}

Upoštevajte, da so edini rezultati, ki so preživeli operacijo, iz skupine B. Vsi ostali rezultati so LAŽNI. Ta matrika se vrne neposredno v LARGE kot matriko prepir:

 
 LARGE ({FALSE65FALSE80FALSE88FALSE76FALSE86FALSE83},3)

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



Zdaj lahko formulo poenostavimo na:

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

ki se razreši na:

 
= FILTER (data,{000001000101})

Končno FILTER vrne zapise za Masona, Annie in Cassidyja, ki se prelijejo v območje F5: H7.

Razvrsti rezultate po rezultatih

Privzeto bo FILTER vrnil ujemajoče se zapise v enakem vrstnem redu, kot so prikazani v izvornih podatkih. Razvrščanje rezultatov v padajoče po vrstnem redu , ti lahko gnezdo prvotna formula FILTER znotraj Funkcija SORT Všečkaj to:

kako najdem krožne reference v
 
= SORT ( FILTER (data,(score>= LARGE ( IF (group='b',score),3))*(group='b')),3,-1)

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

Dinamične matrične formule so na voljo v Excel 365 samo. Avtor Dave Bruns


^