Bendroji formulė
=FILTER(data,(range>=LARGE(IF(criteria),n))*(criteria))
Santrauka
Norėdami filtruoti duomenis, kad būtų rodomos n geriausios vertės, atitinkančios konkrečius kriterijus, galite naudoti funkciją FILTRAS kartu su funkcijomis LARGE ir IF. Parodytame pavyzdyje formulė F5 yra:
=FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b"))
kur duomenys (B5: D16), grupė (C5: C16) ir rezultatas (D5: D16) yra įvardijami diapazonai.
Paaiškinimas
Ši formulė naudoja FILTER funkciją duomenims gauti pagal loginį testą, sukonstruotą naudojant LARGE ir IF funkcijas. Rezultatas yra 3 geriausi B grupės rezultatai.
Funkcija FILTRAS taiko kriterijus su argumentu „įtraukti“. Šiame pavyzdyje kriterijai sudaromi pagal tokią loginę logiką:
(score>=LARGE(IF(group="b",score),3))*(group="b")
Kairė išraiškos taikinių pusė yra didesnė arba lygi 3-iems aukščiausiems B grupės balams:
score>=LARGE(IF(group="b",score),3)
IF funkcija naudojama norint įsitikinti, kad LARGE dirba tik su B grupės balais. Kadangi iš viso turime 12 balų, IF pateikia masyvą su tokiais 12 rezultatų:
(FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83)
Atkreipkite dėmesį, kad vieninteliai balai, išlikę po operacijos, yra B grupė. Visi kiti balai yra NETIESA. Šis masyvas grąžinamas tiesiai į LARGE kaip masyvo argumentas:
LARGE((FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83),3)
LARGE nepaiso FALSE reikšmių ir pateikia 3 aukščiausią balą - 83.
Dabar formulę galime supaprastinti taip:
=FILTER(data,(score>=83)*(group="b"))
kuris nusprendžia:
=FILTER(data,(0;0;0;0;0;1;0;0;0;1;0;1))
Galiausiai, FILTER grąžina Mason, Annie ir Cassidy įrašus, kurie prasiskverbia į F5: H7 diapazoną.
Rūšiuoti rezultatus pagal balus
Pagal numatytuosius nustatymus FILTER grąžins atitinkamus įrašus ta pačia tvarka, kokia rodoma šaltinio duomenyse. Norėdami rūšiuoti rezultatus mažėjančia tvarka pagal balus, funkciją RŪŠIUOTI galite įdėti originalią FILTRO formulę taip:
=SORT(FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b")),3,-1)
Čia filtras grąžina rezultatus tiesiai į funkciją SORT kaip masyvo argumentą. Rūšiuoti indeksas yra nustatytas į 3 (balas), o rūšiavimo tvarka nustatyta į -1, mažėjančiai tvarkai.
