Обобщение
За да изчислите подвижна или подвижна средна стойност, можете да използвате проста формула, базирана на функцията AVERAGE с относителни препратки. В показания пример формулата в E7 е:
=AVERAGE(C5:C7)
Докато формулата се копира надолу, тя изчислява тридневна пълзяща средна стойност въз основа на стойността на продажбите за текущия ден и двата предходни дни.
По-долу има по-гъвкава опция, базирана на функцията OFFSET, която обработва променливи периоди.
Относно пълзящите средни
Плъзгаща средна стойност (наричана още подвижна средна стойност) е средна стойност, базирана на подмножества от данни на дадени интервали. Изчисляването на средна стойност на определени интервали изглажда данните чрез намаляване на въздействието на случайни колебания. Това улеснява виждането на общите тенденции, особено в диаграма. Колкото по-голям е интервалът, използван за изчисляване на подвижна средна стойност, толкова по-изглаждане настъпва, тъй като във всяка изчислена средна стойност са включени повече точки от данни.
Обяснение
Формулите, показани в примера, използват функцията AVERAGE с относителна препратка, настроена за всеки специфичен интервал. Тридневната подвижна средна стойност в E7 се изчислява чрез подаване на СРЕДЕН диапазон, който включва текущия ден и двата предходни дни по следния начин:
=AVERAGE(C5:C7) // 3-day average
5-дневните и 7-дневните средни стойности се изчисляват по същия начин. Във всеки случай диапазонът, предоставен на AVERAGE, се увеличава, за да включи необходимия брой дни:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Всички формули използват относителна препратка за диапазона, предоставен на функцията AVERAGE. Докато формулите се копират надолу по колоната, диапазонът се променя на всеки ред, за да включва стойностите, необходими за всяка средна стойност.
Когато стойностите са нанесени в линейна диаграма, изглаждащият ефект е ясен:
Недостатъчно данни
Ако стартирате формулите в първия ред на таблицата, първите няколко формули няма да имат достатъчно данни за изчисляване на пълна средна стойност, тъй като диапазонът ще се простира над първия ред с данни:
Това може да е проблем или не, в зависимост от структурата на работния лист и дали е важно всички средни стойности да се базират на един и същ брой стойности. Функцията AVERAGE автоматично ще игнорира текстови стойности и празни клетки, така че ще продължи да изчислява средна стойност с по-малко стойности. Ето защо тя "работи" в E5 и E6.
Един от начините да се посочи ясно недостатъчно данни е да се провери номерът на текущия ред и да се прекъсне с #NA, когато има по-малко от n стойности. Например, за средата за 3 дни можете да използвате:
=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, формулата връща # N / A. В противен случай формулата връща пълзяща средна стойност, както преди. Това имитира поведението на версията на Moving Average на Analysis Toolpak, която извежда # N / A до достигане на първия пълен период.
Тъй като обаче броят на периодите се увеличава, в крайна сметка ще свършите редовете над данните и няма да можете да въведете необходимия диапазон вътре СРЕДНО. Например не можете да настроите подвижна 7-дневна средна стойност с работния лист, както е показано, тъй като не можете да въведете диапазон, който се простира на 6 реда над C5.
Променливи периоди с OFFSET
По-гъвкав начин за изчисляване на подвижна средна стойност е функцията OFFSET. OFFSET може да създаде динамичен диапазон, което означава, че можем да настроим формула, където броят на периодите е променлив. Общата форма е:
=AVERAGE(OFFSET(A1,0,0,-n,1))
където n е броят на периодите, които трябва да бъдат включени във всяка средна стойност. Както по-горе, OFFSET връща диапазон, който се предава във функцията AVERAGE. По-долу можете да видите тази формула в действие, където "n" е наименуваният диапазон E2. Започвайки от клетка C5, OFFSET конструира диапазон, който се простира обратно до предишните редове. Това се постига чрез използване на височина, равна на отрицателно n. Когато E5 се промени на друго число, пълзящата средна стойност се преизчислява на всички редове:
Формулата в E5, копирана надолу, е:
=AVERAGE(OFFSET(C5,0,0,-n,1))
Подобно на оригиналната формула по-горе, версията с OFFSET също ще има проблема с недостатъчно данни в първите няколко реда, в зависимост от това колко периоди са дадени в E5.
В показания пример средните стойности се изчисляват успешно, защото функцията AVERAGE автоматично игнорира текстови стойности и празни клетки и няма други цифрови стойности над C5. И така, докато диапазонът, прехвърлен в СРЕДНО в E5, е C1: C5, има само една стойност за осредняване, 100. Въпреки това, с увеличаване на периодите, OFFSET ще продължи да създава диапазон, който се простира над началото на данните, в крайна сметка се сблъсква в горната част на работния лист и връщане на грешка #REF.
Едно от решенията е да ограничите размера на диапазона до броя на наличните точки. Това може да се направи с помощта на функцията MIN, за да се ограничи броят, използван за височина, както е показано по-долу:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
Това изглежда доста страшно, но всъщност е доста просто. Ограничаваме височината, предадена в OFFSET с функцията MIN:
MIN(ROW()-ROW($C$5)+1,n)
Вътре в MIN първата стойност е относително число на ред, изчислено с:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
Втората стойност, дадена на MIN, е броят на периодите, n. Когато относителният номер на реда е по-малък от n, MIN връща номера на текущия ред в OFFSET за височина. Когато номерът на реда е по-голям от n, MIN връща n. С други думи, MIN просто връща по-малката от двете стойности.
Хубава характеристика на опцията OFFSET е, че n може лесно да бъде променена. Ако променим n на 7 и начертаем резултатите, ще получим диаграма като тази:
Забележка: Странността с формулите OFFSET по-горе е, че те няма да работят в Google Sheets, тъй като функцията OFFSET в Sheets няма да позволи отрицателна стойност за височина или ширина. В приложената електронна таблица има формули за заобикаляне на листове на Google.
