Формула покретног просека

Да бисте израчунали покретни или покретни просек, можете користити једноставну формулу засновану на АВЕРАГЕ функција са релативним референцама. У приказаном примеру, формула у Е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)+1<3, NA (), AVERAGE (C3:C5))

Први део формуле једноставно генерише „нормализован“ број реда, почевши од 1:

 
 ROW ()- ROW ($C)+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)+1,n)),1))

Ово изгледа прилично застрашујуће, али је заправо прилично једноставно. Ограничавамо висину која се преноси у ОФФСЕТ помоћу МИН функција :

 
 MIN ( ROW ()- ROW ($C)+1,n)

Унутар МИН -а, прва вредност је а релативни број реда , израчунато са:

 
 ROW ()- ROW ($C)+1 // relative row number..1,2,3, etc.

Друга вредност дата МИН -у је број периода, н. Када је релативни број реда мањи од н, МИН враћа тренутни број реда у ОФФСЕТ за висину. Када је број реда већи од н, МИН враћа н. Другим речима, МИН једноставно враћа мању од две вредности.

Лепа карактеристика опције ОФФСЕТ је та што се н може лако променити. Ако променимо н у 7 и исцртамо резултате, добићемо графикон овако:

Напомена: Занимљивост са горњим формулама ОФФСЕТ је да неће радити у Гоогле табелама, јер функција ОФФСЕТ у табелама неће дозволити негативну вредност за висину или ширину. Приложена табела садржи формуле за заобилажење за Гоогле листове.

екцељет покретни просек.клск