„Excel“

FILTRUOTI viršų n reikšmių su kriterijais

Filter Top N Values With Criteria

„Excel“ formulė: FILTRUOKITE viršutines n reikšmes su kriterijaisBendroji formulė | _+_ | Santrauka

Jei norite filtruoti duomenis, kad būtų rodomos didžiausios n reikšmės, atitinkančios konkrečius kriterijus, galite naudoti FILTRO funkcija kartu su DIDELIS ir JEI funkcijas. Pateiktame pavyzdyje formulė F5 yra tokia:



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

kur duomenis (B5: D16), grupė (C5: C16) ir rezultatas (D5: D16) yra pavadinti diapazonai .

Paaiškinimas

Ši formulė naudoja FILTER funkciją, kad gautų duomenis pagal loginį testą, sukurtą naudojant DIDELIS ir JEI funkcijas. Rezultatas - 3 geriausi B grupės balai.





Funkcija FILTER taiko kriterijus su įtraukti argumentas. Šiame pavyzdyje sudaromi kriterijai loginė logika kaip šitas:

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

Kairioji išraiškos pusė nukreipta į balus, didesnius arba lygius trečiam aukščiausiam B grupės balui:



kaip apskaičiuoti valandas excel tarp dviejų kartų
 
(score>= LARGE ( IF (group='b',score),3))*(group='b')

IF funkcija naudojama siekiant įsitikinti, kad LARGE veikia tik su B grupės balais. Kadangi iš viso turime 12 balų, IF grąžina an masyvas su 12 tokių rezultatų:

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

Atkreipkite dėmesį, kad vieninteliai balai, išlikę po operacijos, yra iš B grupės. Visi kiti balai yra NETIESI. Šis masyvas grąžinamas tiesiai į LARGE kaip masyvas argumentas:

 
{FALSE65FALSE80FALSE88FALSE76FALSE86FALSE83}

LARGE ignoruoja FALSE reikšmes ir pateikia trečią aukščiausią balą - 83.

Dabar galime supaprastinti formulę taip:

 
 LARGE ({FALSE65FALSE80FALSE88FALSE76FALSE86FALSE83},3)

kuris išsprendžia:

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

Galiausiai FILTER grąžina Masono, Annie ir Cassidy įrašus, kurie patenka į diapazoną F5: H7.

Rūšiuokite rezultatus pagal balą

Pagal numatytuosius nustatymus FILTER grąžins atitinkamus įrašus ta pačia tvarka, kokia jie rodomi šaltinio duomenyse. Norėdami surūšiuoti rezultatus mažėjančia tvarka pagal balą , tu gali lizdas originali FILTER formulė SORT funkcija kaip šitas:

„Excel“ vidurkis, jei didesnis nei 0
 
= FILTER (data,{000001000101})

Čia FILTER grąžina rezultatus tiesiai į rūšiavimo funkciją kaip masyvas argumentas. Rūšiavimo indeksas nustatytas į 3 (balas), o rūšiavimo tvarka -į -1 mažėjantis įsakymas.

Dinaminio masyvo formulės yra prieinami „Excel 365“ tik. Autorius Dave'as Brunsas


^