Резиме
Да бисте израчунали покретни или покретни просек, можете да користите једноставну формулу засновану на АВЕРАГЕ функцији са релативним референцама. У приказаном примеру, формула у Е7 је:
=AVERAGE(C5:C7)
Како се формула копира, израчунава тродневни покретни просек на основу вредности продаје за текући дан и два претходна дана.
Испод је флексибилнија опција заснована на функцији ОФФСЕТ која обрађује променљиве периоде.
О покретним просецима
Покретни просек (који се назива и покретни просек) је просек заснован на подскуповима података у датим интервалима. Израчунавање просека у одређеним интервалима изглађује податке смањењем утицаја случајних колебања. То олакшава сагледавање укупних трендова, посебно на графикону. Што је већи интервал који се користи за израчунавање покретног просека, то се више постиже заглађивање, јер је у сваки израчунати просек укључено више тачака података.
Објашњење
Све формуле приказане у примеру користе функцију АВЕРАГЕ са релативном референцом постављеном за сваки одређени интервал. Тродневни покретни просек у Е7 израчунава се храњењем ПРОСЕЧНОГ опсега који укључује тренутни дан и два претходна дана попут овог:
=AVERAGE(C5:C7) // 3-day average
Просеци за 5 дана и 7 дана израчунавају се на исти начин. У сваком случају, опсег који се пружа АВЕРАГЕ је увећан да би обухватио потребан број дана:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Све формуле користе релативну референцу за опсег испоручен функцији АВЕРАГЕ. Како се формуле копирају у колону, опсег се мења у сваком реду тако да укључује вредности потребне за сваки просек.
Када су вредности уцртане у линијски графикон, ефекат заглађивања је јасан:
Недовољно података
Ако формуле започнете у првом реду табеле, првих неколико формула неће имати довољно података за израчунавање потпуног просека, јер ће се опсег проширити изнад првог реда података:
Ово може бити проблем или не, у зависности од структуре радног листа и од тога да ли је важно да се сви просеци заснивају на истом броју вредности. Функција АВЕРАГЕ аутоматски ће занемарити текстуалне вредности и празне ћелије, па ће и даље израчунавати просек са мање вредности. Због тога то „функционише“ у Е5 и Е6.
Један од начина да се јасно укаже на недовољно података је провера тренутног броја реда и прекид са #НА када постоји мање од н вредности. На пример, за тродневни просек можете да користите:
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
Први део формуле једноставно генерише "нормализовани" број реда, почев од 1:
ROW()-ROW($C$5)+1 // relative row number
У реду 5 резултат је 1, у реду 6 резултат 2 и тако даље.
Када је број тренутног реда мањи од 3, формула враћа # Н / А. У супротном, формула враћа покретни просек као и раније. Ово опонаша понашање верзије Мовинг Авераге (Пакет алата за анализу) која даје # Н / А док се не достигне први потпуни период.
Међутим, како се број периода повећава, на крају ћете остати без редова изнад података и нећете моћи да унесете тражени опсег унутар АВЕРАГЕ. На пример, не можете да подесите покретни просек од 7 дана помоћу радног листа, као што је приказано, јер не можете да унесете опсег који се протеже за 6 редова изнад Ц5.
Варијабилни периоди са ОФФСЕТ
Флексибилнији начин израчунавања покретног просека је помоћу функције ОФФСЕТ. ОФФСЕТ може створити динамички опсег, што значи да можемо поставити формулу у којој је број периода променљив. Општи облик је:
=AVERAGE(OFFSET(A1,0,0,-n,1))
где је н број периода који треба укључити у сваки просек. Као и горе, ОФФСЕТ враћа опсег који се прослеђује у функцију АВЕРАГЕ. Испод можете видети ову формулу на делу, где је „н“ именовани опсег Е2. Почев од ћелије Ц5, ОФФСЕТ конструише опсег који се протеже уназад до претходних редова. То се постиже коришћењем висине једнаке негативном н. Када се Е5 промени у други број, покретни просек се поново израчунава у свим редовима:
Копирана формула у Е5 је:
=AVERAGE(OFFSET(C5,0,0,-n,1))
Као и горња оригинална формула, верзија са ОФФСЕТ такође ће имати проблем са недостатком података у првих неколико редова, у зависности од тога колико је тачака дато у Е5.
У приказаном примеру, просеци се успешно израчунавају, јер функција АВЕРАГЕ аутоматски игнорише вредности текста и празне ћелије, а изнад Ц5 не постоје друге нумеричке вредности. Дакле, док је опсег прослеђен у Просек у Е5 Ц1: Ц5, постоји само једна вредност за просек, 100. Међутим, како се периоди повећавају, ОФФСЕТ ће наставити да ствара опсег који се протеже изнад почетка података, на крају наилазећи на врх радног листа и враћање грешке #РЕФ.
Једно решење је да се величина опсега „ограничи“ на број доступних тачака података. То се може урадити употребом функције МИН за ограничавање броја који се користи за висину као што је приказано доле:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
Ово изгледа прилично застрашујуће, али је заправо прилично једноставно. Ограничавамо висину прослеђену у ОФФСЕТ функцијом МИН:
MIN(ROW()-ROW($C$5)+1,n)
Унутар МИН, прва вредност је релативни број реда, израчунат са:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
Друга вредност дата МИН-у је број тачака, н. Када је релативни број реда мањи од н, МИН враћа тренутни број реда у ОФФСЕТ за висину. Када је број реда већи од н, МИН враћа н. Другим речима, МИН једноставно враћа мању од две вредности.
Лепа карактеристика опције ОФФСЕТ је да се н може лако променити. Ако променимо н у 7 и зацртамо резултате, добићемо овакав графикон:
Напомена: Чудност са горе наведеним формулама ОФФСЕТ је да они неће радити у Гоогле табелама, јер функција ОФФСЕТ у табелама неће дозволити негативну вредност за висину или ширину. Приложена прорачунска табела садржи формуле за заобилажење Гоогле листова.
