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.
