Kokkuvõte
Liikuva või jooksva keskmise arvutamiseks võite kasutada lihtsa valemi põhjal funktsiooni AVERAGE suhteliste viidetega. Näidatud näites on valem E7:
=AVERAGE(C5:C7)
Kui valemit kopeeritakse alla, arvutab see 3 päeva liikuva keskmise praeguse ja kahe eelmise päeva müügiväärtuse põhjal.
Allpool on paindlikum valik, mis põhineb funktsioonil OFFSET, mis tegeleb muutuvate perioodidega.
Liiguvate keskmiste kohta
Liikuv keskmine (nimetatakse ka jooksvaks keskmiseks) on keskmine, mis põhineb andmete alamhulkadel teatud intervallidega. Keskmise arvutamine kindlate ajavahemike tagant silub andmeid, vähendades juhuslike kõikumiste mõju. See hõlbustab üldiste suundumuste nägemist, eriti graafikul. Mida suuremat intervalli kasutatakse liikuva keskmise arvutamiseks, seda suurem on silumine, kuna igasse arvutatud keskmisse lisatakse rohkem andmepunkte.
Selgitus
Näites näidatud valemid kasutavad kõik funktsiooni AVERAGE, kusjuures iga konkreetse intervalli jaoks on määratud suhteline võrdlus. E7 kolme päeva liikuv keskmine arvutatakse, toites AVERAGE vahemiku, mis sisaldab praegust päeva ja kahte eelmist päeva:
=AVERAGE(C5:C7) // 3-day average
5- ja 7-päevased keskmised arvutatakse samal viisil. Mõlemal juhul laiendatakse AVERAGE-le antud vahemikku, et see hõlmaks vajalikku päevade arvu:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Kõikides valemites kasutatakse funktsiooni AVERAGE jaoks antud vahemiku suhtelist viidet. Kui valemeid veerust alla kopeeritakse, muutub vahemik igas reas, lisades iga keskmise jaoks vajalikud väärtused.
Kui väärtused joonestatakse joondiagrammile, on silumisefekt selge:
Andmeid pole piisavalt
Kui alustate valemeid tabeli esimeses reas, pole esimestel valemitel täieliku keskmise arvutamiseks piisavalt andmeid, kuna vahemik laieneb esimese andmerea kohal:
See võib olla või mitte olla probleem, sõltuvalt töölehe struktuurist ja sellest, kas on oluline, et kõik keskmised põhineksid samal arvul väärtustel. Funktsioon AVERAGE ignoreerib automaatselt tekstiväärtusi ja tühje lahtreid, nii et see jätkab vähemate väärtustega keskmise arvutamist. Sellepärast see "töötab" E5 ja E6.
Üks võimalus ebapiisavate andmete selgelt näitamiseks on praeguse rea numbri kontrollimine ja katkestamine #NA-ga, kui väärtusi on vähem kui n. Näiteks võite kolme päeva keskmise jaoks kasutada järgmist.
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
Valemi esimene osa genereerib lihtsalt "normaliseeritud" rea numbri, algusega 1:
ROW()-ROW($C$5)+1 // relative row number
5. reas on tulemus 1, 6. reas on tulemus 2 jne.
Kui praeguse rea number on väiksem kui 3, tagastab valem # N / A. Vastasel juhul tagastab valem liikuva keskmise nagu varem. See jäljendab liikuva keskmise Analysis Toolpaki versiooni käitumist, mis väljastab # täieliku esimese perioodi saavutamiseni.
Perioodide arvu suurenemisel saavad andmete kohal olevad read lõpuks otsa ja te ei saa AVERAGE sisestada nõutavat vahemikku. Näiteks ei saa te töölehega seadistada liikuvat 7 päeva keskmist, nagu näidatud, kuna te ei saa sisestada vahemikku, mis ulatub 6 rida üle C5.
Muutuvad perioodid OFFSETiga
Paindlikum viis liikuva keskmise arvutamiseks on funktsioon OFFSET. OFFSET võib luua dünaamilise vahemiku, mis tähendab, et saame seadistada valemi, kus perioodide arv on muutuv. Üldine vorm on:
=AVERAGE(OFFSET(A1,0,0,-n,1))
kus n on igasse keskmisse lisatavate perioodide arv. Nagu ülalpool, tagastab OFFSET vahemiku, mis edastatakse funktsiooni KESKMINE. Allpool näete seda valemit toimimas, kus "n" on nimega vahemik E2. Alates lahtrist C5 konstrueerib OFFSET vahemiku, mis ulatub tagasi eelmistesse ridadesse. Selle saavutamiseks kasutatakse negatiivse n-ga võrdset kõrgust. Kui E5 muudetakse teiseks numbriks, arvutab liikuv keskmine kõigil ridadel ümber:
E5 valem, mis on kopeeritud, on järgmine:
=AVERAGE(OFFSET(C5,0,0,-n,1))
Sarnaselt ülaltoodud algsele valemile on ka OFFSET-iga versioonil probleem esimeste ridade ebapiisavate andmete osas, sõltuvalt sellest, mitu perioodi on E5-s antud.
Näidatud näites arvutatakse keskmised edukalt, kuna funktsioon AVERAGE eirab automaatselt tekstiväärtusi ja tühje lahtreid ning C5 kohal pole muid arvväärtusi. Niisiis, kuigi E5-s KESKMISEKS kantud vahemik on C1: C5, on keskmiseks vaid üks väärtus, 100. Perioodide suurenedes loob OFFSET aga vahemiku, mis ulatub üle andmete alguse, sattudes lõpuks töölehe ülaosas ja tagastatakse viga #REF.
Üks lahendus on "piirata" vahemiku suurus olemasolevate andmepunktide arvuga. Seda saab teha funktsiooni MIN abil, et piirata kõrguse jaoks kasutatavat arvu, nagu allpool näha:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
See tundub üsna hirmutav, kuid on tegelikult üsna lihtne. Funktsiooniga MIN piirame OFFSETi kantud kõrgust:
MIN(ROW()-ROW($C$5)+1,n)
MIN-i sees on esimene väärtus suhteline rea number, mis arvutatakse järgmise valemiga:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
MIN-ile antud teine väärtus on perioodide arv, n. Kui suhteline rea number on väiksem kui n, tagastab MIN praeguse rea numbri kõrguseks OFFSET. Kui rea number on suurem kui n, tagastab MIN n. Teisisõnu tagastab MIN kahest väärtusest väiksema.
OFFSET-valiku kena omadus on see, et n saab hõlpsasti muuta. Kui muudame n väärtuseks 7 ja joonistame tulemused, saame sellise diagrammi:
Märkus. Ülaltoodud valemite OFFSET mõte on see, et need ei tööta Google'i arvutustabelites, kuna arvutustabelite funktsioon OFFSET ei võimalda negatiivset kõrguse ega laiuse väärtust. Manustatud arvutustabelil on Google'i arvutustabelite lahendused.
