Santrauka
Norėdami apskaičiuoti slenkantį arba slenkantį vidurkį, galite naudoti paprastą formulę, pagrįstą funkcija AVERAGE su santykinėmis nuorodomis. Parodytame pavyzdyje formulė E7 yra:
=AVERAGE(C5:C7)
Kai formulė nukopijuojama žemyn, ji apskaičiuoja 3 dienų slenkantį vidurkį pagal dabartinės dienos ir dviejų ankstesnių dienų pardavimo vertę.
Žemiau pateikiama lankstesnė parinktis, pagrįsta funkcija OFFSET, kuri valdo kintamus laikotarpius.
Apie slenkamuosius vidurkius
Slankusis vidurkis (dar vadinamas slenkamuoju vidurkiu) yra vidurkis, pagrįstas duomenų pogrupiais tam tikrais intervalais. Apskaičiuojant vidurkį tam tikrais intervalais, duomenys išlyginami sumažinant atsitiktinių svyravimų poveikį. Tai leidžia lengviau pamatyti bendras tendencijas, ypač diagramoje. Kuo didesnis intervalas naudojamas slenkamajam vidurkiui apskaičiuoti, tuo labiau išlyginama, nes į kiekvieną apskaičiuotą vidurkį įtraukiama daugiau duomenų taškų.
Paaiškinimas
Visose pavyzdyje pateiktose formulėse naudojama funkcija AVERAGE su santykine nuoroda, nustatyta kiekvienam konkrečiam intervalui. 3 dienų slenkamasis E7 vidurkis apskaičiuojamas apskaičiuojant AVERAGE diapazoną, kuris apima dabartinę dieną ir dvi ankstesnes dienas:
=AVERAGE(C5:C7) // 3-day average
5 ir 7 dienų vidurkiai apskaičiuojami tuo pačiu būdu. Kiekvienu atveju AVERAGE pateiktas diapazonas padidinamas, kad būtų įtrauktas reikiamas dienų skaičius:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Visose formulėse naudojama santykinė nuoroda į diapazoną, pateiktą funkcijai AVERAGE. Formulėms nukopijavus stulpelį, diapazonas keičiasi kiekvienoje eilutėje, įtraukiant kiekvienam vidurkiui reikalingas vertes.
Kai vertės pateikiamos linijinėje diagramoje, išlyginamasis poveikis yra aiškus:
Nepakanka duomenų
Jei pradėsite formules pirmoje lentelės eilutėje, pirmosiose formulėse nebus pakankamai duomenų, kad būtų galima apskaičiuoti visą vidurkį, nes diapazonas bus didesnis nei pirmoji duomenų eilutė:
Tai gali būti ar ne problema, atsižvelgiant į darbalapio struktūrą ir į tai, ar svarbu, kad visi vidurkiai būtų pagrįsti tuo pačiu verčių skaičiumi. Funkcija AVERAGE automatiškai nepaisys teksto reikšmių ir tuščių langelių, todėl ji ir toliau skaičiuos vidurkį su mažiau reikšmių. Štai kodėl jis „veikia“ E5 ir E6.
Vienas iš būdų aiškiai nurodyti nepakankamą duomenų kiekį yra tikrinti dabartinį eilutės numerį ir nutraukti naudojant #NA, kai yra mažiau nei n reikšmių. Pavyzdžiui, 3 dienų vidurkiui galite naudoti:
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
Pirmoji formulės dalis tiesiog sukuria „normalizuotą“ eilutės numerį, pradedant 1:
ROW()-ROW($C$5)+1 // relative row number
5 eilutėje rezultatas yra 1, 6 eilutėje rezultatas yra 2 ir kt.
Kai dabartinis eilutės numeris yra mažesnis nei 3, formulė grąžina # N / A. Kitu atveju formulė pateikia slankųjį vidurkį, kaip ir anksčiau. Tai imituoja „Moving Average“ analizės įrankių paketo versijos elgseną, kuri pateikia # N / A, kol bus pasiektas pirmasis visas laikotarpis.
Tačiau didėjant laikotarpių skaičiui, galų gale baigsis eilutės virš duomenų ir negalėsite įvesti reikiamo diapazono AVERAGE. Pavyzdžiui, naudodami darbalapį negalite nustatyti judančio 7 dienų vidurkio, kaip parodyta, nes negalite įvesti diapazono, kuris tęsiasi 6 eilutes virš C5.
Kintantys laikotarpiai naudojant OFFSET
Lankstesnis būdas apskaičiuoti slenkantį vidurkį yra funkcija OFFSET. OFFSET gali sukurti dinaminį diapazoną, o tai reiškia, kad mes galime nustatyti formulę, kurioje periodų skaičius yra kintamas. Bendra forma yra:
=AVERAGE(OFFSET(A1,0,0,-n,1))
kur n yra į kiekvieną vidurkį įtraukiamų laikotarpių skaičius. Kaip ir aukščiau, OFFSET pateikia diapazoną, kuris perduodamas funkcijai AVERAGE. Žemiau galite pamatyti šią formulę veikiant, kur "n" yra pavadintas diapazonas E2. Pradedant langeliu C5, OFFSET sukuria diapazoną, kuris tęsiasi iki ankstesnių eilučių. Tai pasiekiama naudojant aukštį, lygų neigiamam n. Kai E5 pakeičiamas kitu skaičiumi, slenkamasis vidurkis perskaičiuoja visas eilutes:
E5 formulė, nukopijuota žemyn, yra:
=AVERAGE(OFFSET(C5,0,0,-n,1))
Kaip ir pirmiau pateiktoje formulėje, versijoje su OFFSET taip pat kils problemų dėl nepakankamų duomenų pirmose eilutėse, atsižvelgiant į tai, kiek laikotarpių yra E5.
Parodytame pavyzdyje vidurkiai sėkmingai apskaičiuojami, nes funkcija AVERAGE automatiškai nepaiso teksto reikšmių ir tuščių langelių, o virš C5 nėra jokių kitų skaitinių verčių. Taigi, kai E5 vidurkis perduodamas į C1 vidurkį, C1: C5, vidutinė vertė yra tik viena - 100. Tačiau didėjant laikotarpiams, OFFSET ir toliau kurs diapazoną, kuris tęsiasi virš duomenų pradžios, galų gale pateks į darbalapio viršuje ir pateikiama #REF klaida.
Vienas iš sprendimų yra „apriboti“ diapazono dydį prie turimų duomenų taškų skaičiaus. Tai galima padaryti naudojant MIN funkciją, kad apribotumėte aukščiui naudojamą skaičių, kaip parodyta žemiau:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
Tai atrodo gana baisu, bet iš tikrųjų yra gana paprasta. Ribojame aukštį, perkeltą į OFFSET, naudodami MIN funkciją:
MIN(ROW()-ROW($C$5)+1,n)
MIN viduje pirmoji vertė yra santykinis eilutės skaičius, apskaičiuotas pagal:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
Antroji vertė, suteikta MIN, yra periodų skaičius, n. Kai santykinis eilutės skaičius yra mažesnis nei n, MIN grąžina dabartinį eilutės numerį į OFFSET aukštį. Kai eilutės numeris yra didesnis nei n, MIN grąžina n. Kitaip tariant, MIN paprasčiausiai pateikia mažesnę iš dviejų reikšmių.
Maloni OFFSET parinkties ypatybė yra ta, kad n galima lengvai pakeisti. Jei pakeisime n į 7 ir apskaičiuosime rezultatus, gausime tokią diagramą:
Pastaba: aukščiau pateiktų OFFSET formulių keitimas yra tas, kad jie neveiks „Google“ skaičiuoklėse, nes „Sheets“ funkcija OFFSET neleis neigiamos aukščio ar pločio vertės. Pridėtoje skaičiuoklėje yra „Google“ lapų apėjimo formulės.
