Yhteenveto
Voit laskea liikkuvan tai liikkuvan keskiarvon käyttämällä keskimääräiseen funktioon perustuvaa yksinkertaista kaavaa suhteellisilla viitteillä. Esitetyssä esimerkissä kaava kohdassa E7 on:
=AVERAGE(C5:C7)
Kun kaava kopioidaan alaspäin, se laskee kolmen päivän liukuvan keskiarvon kuluvan päivän ja kahden edellisen päivän myyntiarvon perusteella.
Alla on joustavampi vaihtoehto, joka perustuu OFFSET-toimintoon, joka käsittelee vaihtelevia jaksoja.
Tietoja liukuvista keskiarvoista
Liukuva keskiarvo (jota kutsutaan myös liukuvaksi keskiarvoksi) on keskiarvo, joka perustuu tietyin väliajoin tapahtuvaan tietoryhmään. Keskiarvon laskeminen tietyin väliajoin tasoittaa tietoja vähentämällä satunnaisvaihteluiden vaikutusta. Näin on helpompi nähdä yleiset trendit, erityisesti kaaviossa. Mitä suurempi väli käytetään liikkuvan keskiarvon laskemiseen, sitä tasaisempaa tapahtuu, koska kuhunkin laskettuun keskiarvoon sisältyy enemmän datapisteitä.
Selitys
Kaikki esimerkissä esitetyt kaavat käyttävät AVERAGE-funktiota suhteellisen viitteen kanssa, joka on määritetty kullekin tietylle aikavälille. E7: n kolmen päivän liukuva keskiarvo lasketaan syöttämällä AVERAGE alueelle, joka sisältää kuluvan päivän ja kaksi edellistä päivää näin:
=AVERAGE(C5:C7) // 3-day average
Viiden ja 7 päivän keskiarvot lasketaan samalla tavalla. Kummassakin tapauksessa AVERAGE-arvoon toimitettua aluetta laajennetaan sisältämään vaadittu päivien määrä:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Kaikissa kaavoissa käytetään suhteellista viittausta AVERAGE-toiminnolle toimitetulle alueelle. Kun kaavat kopioidaan sarakkeesta alaspäin, alue muuttuu kullakin rivillä sisällyttää jokaiselle keskiarvolle tarvittavat arvot.
Kun arvot piirretään viivakaavioon, tasoitusvaikutus on selvä:
Riittämätön data
Jos aloitat kaavat taulukon ensimmäisellä rivillä, ensimmäisillä kaavoilla ei ole tarpeeksi tietoja täydellisen keskiarvon laskemiseksi, koska alue ulottuu ensimmäisen tietorivin yläpuolelle:
Tämä voi olla ongelma tai ei, riippuen laskentataulukon rakenteesta ja siitä, onko tärkeää, että kaikki keskiarvot perustuvat samaan määrään arvoja. AVERAGE-toiminto jättää automaattisesti huomiotta tekstiarvot ja tyhjät solut, joten se jatkaa keskiarvon laskemista vähemmän arvoilla. Siksi se "toimii" malleissa E5 ja E6.
Yksi tapa osoittaa riittämättömät tiedot on tarkistaa nykyisen rivin numero ja keskeyttää #NA, kun arvoja on alle n. Esimerkiksi kolmen päivän keskiarvoksi voit käyttää:
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
Kaavan ensimmäinen osa muodostaa yksinkertaisesti "normalisoidun" rivinumeron alkaen 1: stä:
ROW()-ROW($C$5)+1 // relative row number
Rivillä 5 tulos on 1, rivillä 6 tulos 2 ja niin edelleen.
Kun nykyinen rivinumero on alle 3, kaava palauttaa # N / A. Muussa tapauksessa kaava palauttaa liikkuvan keskiarvon kuten aiemmin. Tämä jäljittelee liukuvan keskiarvon Analysis Toolpak -version käyttäytymistä, joka tuottaa # N / A, kunnes ensimmäinen täydellinen jakso on saavutettu.
Kuitenkin ajanjaksojen lukumäärän kasvaessa tietojen yläpuolella olevat rivit loppuvat lopulta, etkä pysty syöttämään vaadittua aluetta keskiarvon sisällä. Et esimerkiksi voi asettaa liikkuvaa 7 päivän keskiarvoa laskentataulukolla kuvan osoittamalla tavalla, koska et voi syöttää aluetta, joka ulottuu 6 riviä C5: n yläpuolelle.
Vaihtelevat jaksot OFFSET-toiminnolla
Joustavampi tapa laskea liukuva keskiarvo on OFFSET-toiminto. OFFSET voi luoda dynaamisen alueen, mikä tarkoittaa, että voimme asettaa kaavan, jossa jaksojen lukumäärä vaihtelee. Yleinen muoto on:
=AVERAGE(OFFSET(A1,0,0,-n,1))
missä n on kuhunkin keskiarvoon sisällytettävien jaksojen lukumäärä. Kuten edellä, OFFSET palauttaa alueen, joka siirretään AVERAGE-toimintoon. Alla näet tämän kaavan toiminnassa, jossa "n" on nimetty alue E2. Solusta C5 alkaen OFFSET muodostaa alueen, joka ulottuu takaisin edellisiin riveihin. Tämä saavutetaan käyttämällä korkeutta, joka on yhtä suuri kuin negatiivinen n. Kun E5 vaihdetaan toiseen numeroon, liikkuva keskiarvo laskee uudelleen kaikilla riveillä:
E5: n kaava kopioituna alaspäin on:
=AVERAGE(OFFSET(C5,0,0,-n,1))
Kuten yllä olevassa alkuperäisessä kaavassa, OFFSET-versiolla on myös ongelma riittämättömistä tiedoista ensimmäisillä riveillä, riippuen siitä, kuinka monta jaksoa E5: ssä on annettu.
Esitetyssä esimerkissä keskiarvot lasketaan onnistuneesti, koska AVERAGE-funktio jättää automaattisesti huomiotta tekstiarvot ja tyhjät solut, eikä C5: n yläpuolella ole muita numeerisia arvoja. Joten vaikka keskiarvoon siirretty alue E5: ssä on C1: C5, keskiarvoon on vain yksi arvo, 100. Ajanjaksojen kasvaessa OFFSET jatkaa kuitenkin alueen luomista, joka ulottuu datan alun yläpuolelle ja joutuu lopulta laskentataulukon yläosassa ja palauttaa #REF-virheen.
Yksi ratkaisu on "peittää" alueen koko käytettävissä olevien datapisteiden määrään. Tämä voidaan tehdä MIN-toiminnolla rajoittaaksesi korkeudelle käytettyä lukumäärää alla esitetyllä tavalla:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
Tämä näyttää melko pelottavalta, mutta on itse asiassa melko yksinkertainen. Rajoitamme OFFSETiin siirrettyä korkeutta MIN-toiminnolla:
MIN(ROW()-ROW($C$5)+1,n)
MIN: n sisällä ensimmäinen arvo on suhteellinen rivinumero, joka lasketaan seuraavasti:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
Toinen MIN: lle annettu arvo on jaksojen lukumäärä n. Kun suhteellinen rivinumero on alle n, MIN palauttaa nykyisen rivinumeron OFFSET-korkeudeksi. Kun rivin numero on suurempi kuin n, MIN palauttaa n. Toisin sanoen MIN palauttaa yksinkertaisesti pienemmän kahdesta arvosta.
OFFSET-vaihtoehdon mukava piirre on, että n: ää voidaan muuttaa helposti. Jos muutamme n arvoksi 7 ja piirrämme tulokset, saat seuraavan kaavion:
Huomaa: Yllä olevien OFFSET-kaavojen mieliala on, että ne eivät toimi Google Sheetsissa, koska Sheetsin OFFSET-toiminto ei salli negatiivista korkeuden tai leveyden arvoa. Liitetyssä laskentataulukossa on kiertotavat kaavat Google-taulukoille.
