Excel-kaava: Keskiarvo viimeiset 3 numeerista arvoa

Yleinen kaava

(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)),ROW(data), data)))

Yhteenveto

Voit keskittää alueen viimeiset 3 numeerista arvoa käyttämällä funktioiden yhdistelmään perustuvaa taulukko-kaavaa syöttämään viimeiset n numeerista arvoa AVERAGE-funktioon. Esitetyssä esimerkissä kaava kohdassa D6 on:

(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)), ROW(data), data)))

missä "data" on nimetty alue B5: B13.

Huomaa: tämä on matriisikaava, ja se on syötettävä painamalla ohjainta + vaihto + enter.

Selitys

AVERAGE-funktio laskee matriisissa esitettyjen lukujen keskiarvon, joten melkein kaikki tämän kaavan tehtävät ovat ryhmän kolmen viimeisen numeerisen arvon taulukon luominen. Työskentelemällä sisältä ulospäin, IF-toimintoa käytetään "suodattamaan" numeerisia arvoja:

IF(ISNUMBER(data),ROW(data))

ISNUMBER-funktio palauttaa TOSI numeerisille arvoille ja FALSE muille arvoille (mukaan lukien tyhjät) ja ROW-funktio palauttaa rivinumerot, joten tämän toiminnon tulos on taulukon rivinumero, joka vastaa numeerisia merkintöjä:

(5;6;FALSE;8;9;10;FALSE;12;13)

Tämä taulukko menee LARGE-funktioon k: n matriisivakion (1,2,3) avulla. LARGE ohittaa FALSE-arvot automaattisesti ja palauttaa taulukon, jolla on kolme suurinta numeroa, jotka vastaavat 3 viimeistä riviä numeerisilla arvoilla:

(13,12,10)

Tämä matriisi menee LOOKUP-funktioon hakuarvona. Hakutaulukon tarjoaa ROW-toiminto, ja tulosryhmä on nimetty alue "data":

LOOKUP((13,12,10), ROW(data), data))

LOOKUP palauttaa sitten taulukon, joka sisältää vastaavat arvot "data" -kentässä, joka syötetään AVERAGE: iin:

=AVERAGE((100,92,90))

Käsittelemällä vähemmän arvoja

Jos numeeristen arvojen määrä putoaa alle 3, tämä kaava palauttaa #NUM -virheen, koska LARGE ei pysty palauttamaan 3 arvoa pyydettäessä. Yksi tapa hoitaa tämä on korvata kovakoodattu matriisivakio (1,2,3) dynaamisella matriisilla, joka on luotu käyttämällä epäsuoraa näin:

ROW(INDIRECT("1:"&MIN(3,COUNT(data))))

Tässä MIN: ää käytetään asettamaan matriisin ylärajaksi 3 tai numeeristen arvojen todellinen määrä sen mukaan, kumpi on pienempi.

Huomaa: törmäsin tähän älykkääseen lähestymistapaan chandoo.org -sivustolla Sajanin vastauksessa vastaavaan kysymykseen.

Mielenkiintoisia artikkeleita...