Excel-kaava: SUODATIN n: n ylimmän arvon perusteella -

Yleinen kaava

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

Yhteenveto

Voit suodattaa tietoja näyttämään ylemmät n arvoa, jotka täyttävät tietyt ehdot, käyttämällä FILTER-funktiota yhdessä LARGE- ja IF-toimintojen kanssa. Esitetyssä esimerkissä kaava kohdassa F5 on:

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

missä tiedot (B5: D16), ryhmä (C5: C16) ja pisteet (D5: D16) on nimetty alueiksi.

Selitys

Tämä kaava käyttää FILTER-toimintoa tietojen noutamiseen LARGE- ja IF-funktioilla muodostetun loogisen testin perusteella. Tuloksena on ryhmän B kolme parasta pistettä.

SUODATIN-funktio käyttää ehtoja sisällyttämällä argumentin. Tässä esimerkissä ehdot on rakennettu loogisella logiikalla seuraavasti:

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

Lausekohteiden vasen puoli saavuttaa vähintään B-ryhmän 3. korkeimman pistemäärän:

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

IF-toimintoa käytetään varmistamaan, että LARGE toimii vain ryhmän B pisteiden kanssa. Koska meillä on yhteensä 12 pistettä, IF palauttaa taulukon, jolla on 12 tällaista tulosta:

(FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83)

Huomaa, että ainoat toiminnasta selvinneet pisteet ovat ryhmästä B. Kaikki muut pisteet ovat EPÄTOSI. Tämä taulukko palautetaan suoraan LARGE-ryhmään taulukon argumenttina:

LARGE((FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83),3)

LARGE jättää FALSE-arvot huomiotta ja palauttaa kolmanneksi korkeimman pistemäärän, 83.

Voimme nyt yksinkertaistaa kaavaa seuraavasti:

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

joka päättää:

=FILTER(data,(0;0;0;0;0;1;0;0;0;1;0;1))

Lopuksi FILTER palauttaa Mason-, Annie- ja Cassidy-tietueet, jotka valuvat alueelle F5: H7.

Lajittele tulokset pisteiden mukaan

Oletusarvon mukaan FILTER palauttaa vastaavat tietueet samassa järjestyksessä kuin ne näkyvät lähdetiedoissa. Jos haluat lajitella tulokset laskevassa järjestyksessä pisteiden mukaan, voit sijoittaa alkuperäisen FILTER-kaavan SORT-toimintoon seuraavasti:

=SORT(FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b")),3,-1)

Tässä FILTER palauttaa tulokset suoraan SORT-funktioon taulukon argumenttina. Lajitteluhakemisto on asetettu arvoon 3 (pisteet) ja lajittelujärjestys arvoon -1 laskevaan järjestykseen.

Mielenkiintoisia artikkeleita...