Üldine valem
=FILTER(data,(range>=LARGE(IF(criteria),n))*(criteria))
Kokkuvõte
Andmete filtreerimiseks, et näidata n kõige paremat väärtust, mis vastavad konkreetsetele kriteeriumidele, saate kasutada funktsiooni FILTER koos funktsioonidega LARGE ja IF. Näidatud näites on valem F5-s järgmine:
=FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b"))
kus andmed (B5: D16), rühm (C5: C16) ja skoor (D5: D16) on nimetatud vahemikeks.
Selgitus
See valem kasutab funktsiooni FILTER andmete hankimiseks funktsioonide LARGE ja IF abil loodud loogilise testi põhjal. Tulemuseks on B-grupi 3 parimat skoori.
Funktsioon FILTER rakendab kriteeriume koos argumendiga include. Selles näites on kriteeriumid koostatud loogika loogikaga järgmiselt:
(score>=LARGE(IF(group="b",score),3))*(group="b")
Avaldise sihtmärkide vasakpoolne skoor saavutab B-grupi 3. kõrgeima punktisumma või on sellega võrdne:
score>=LARGE(IF(group="b",score),3)
Funktsiooni IF kasutatakse veendumaks, et LARGE töötab ainult B-grupi skooridega. Kuna meil on kokku 12 skoori, tagastab IF massiivi 12 sellise tulemusega:
(FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83)
Pange tähele, et ainsad skoorid, mis operatsiooni üle elavad, kuuluvad rühmast B. Kõik muud hinded on VALE. See massiiv tagastatakse otse massiivi argumendiks LARGE:
LARGE((FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83),3)
LARGE ignoreerib FALSE väärtusi ja tagastab 3. kõrgeima punktisumma, 83.
Nüüd saame valemit lihtsustada:
=FILTER(data,(score>=83)*(group="b"))
mis otsustab:
=FILTER(data,(0;0;0;0;0;1;0;0;0;1;0;1))
Lõpuks tagastab FILTER Masoni, Annie ja Cassidy rekordid, mis levivad vahemikku F5: H7.
Sorteeri tulemused skoori järgi
Vaikimisi tagastab FILTER sobivad kirjed samas järjekorras, nagu need kuvatakse lähteandmetes. Tulemuste sortimiseks kahanevas järjekorras skooride järgi saate funktsiooni SORT (SORT) sisse asetada algse FILTER-valemi:
=SORT(FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b")),3,-1)
Siin tagastab FILTER tulemused massiivi argumendina otse funktsioonile SORT. Sorteerimise indeks on seatud 3 (skoor) ja sortimisjärjestus on -1, kahanevas järjekorras.
