Vispārēja formula
=FILTER(data,(range>=LARGE(IF(criteria),n))*(criteria))
Kopsavilkums
Lai filtrētu datus, lai parādītu n galvenās vērtības, kas atbilst noteiktiem kritērijiem, varat izmantot funkciju FILTER kopā ar funkcijām LARGE un IF. Parādītajā piemērā formula F5 ir:
=FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b"))
kur dati (B5: D16), grupa (C5: C16) un rezultāts (D5: D16) ir nosaukti diapazoni.
Paskaidrojums
Šī formula izmanto funkciju FILTER, lai izgūtu datus, pamatojoties uz loģisko testu, kas izveidots, izmantojot funkcijas LARGE un IF. Rezultāts ir B grupas 3 labākie rezultāti.
Funkcija FILTRS piemēro kritērijus ar argumentu include. Šajā piemērā kritēriji tiek veidoti, izmantojot šādu loģisko loģiku:
(score>=LARGE(IF(group="b",score),3))*(group="b")
Izteiksmes mērķa kreisajā pusē rādītāji ir lielāki vai vienādi ar 3. augstāko punktu skaitu B grupā:
score>=LARGE(IF(group="b",score),3)
IF funkcija tiek izmantota, lai pārliecinātos, ka LARGE darbojas tikai ar B grupas rādītājiem. Tā kā mums ir 12 rādītāji kopā, IF atgriež masīvu ar šādiem 12 rezultātiem:
(FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83)
Ievērojiet, ka vienīgie rādītāji, kas izdzīvo pēc operācijas, ir B grupa. Visi pārējie rādītāji ir FALSE. Šis masīvs tiek tieši atgriezts LARGE kā masīva arguments:
LARGE((FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83),3)
LARGE ignorē FALSE vērtības un atgriež 3. augstāko punktu skaitu 83.
Tagad mēs varam vienkāršot formulu:
=FILTER(data,(score>=83)*(group="b"))
kas nolemj:
=FILTER(data,(0;0;0;0;0;1;0;0;0;1;0;1))
Visbeidzot, FILTER atgriež Meisona, Annijas un Kasidija ierakstus, kas izplūst diapazonā F5: H7.
Kārtot rezultātus pēc rezultāta
Pēc noklusējuma FILTER atgriezīs atbilstošos ierakstus tādā pašā secībā, kādā tie tiek rādīti avota datos. Lai sakārtotu rezultātus dilstošā secībā pēc rezultātiem, sākotnējo FILTER formulu jūs varat ievietot SORT funkcijas iekšpusē šādi:
=SORT(FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b")),3,-1)
Šeit FILTER atgriež rezultātus tieši SORT funkcijai kā masīva argumentam. Kārtošanas indekss ir iestatīts uz 3 (punktu skaits) un kārtošanas secība ir iestatīta uz -1 dilstošā secībā.
