Резюме
Щоб розрахувати ковзне або ковзне середнє, ви можете використовувати просту формулу на основі функції AVERAGE із відносними посиланнями. У наведеному прикладі формула в E7 має вигляд:
=AVERAGE(C5:C7)
Коли формула копіюється вниз, вона обчислює триденну ковзну середню на основі вартості продажів за поточний день та два попередні дні.
Нижче наведено більш гнучку опцію на основі функції OFFSET, яка обробляє змінні періоди.
Про ковзні середні
Ковзне середнє (також зване ковзаючим середнім) - це середнє значення, яке базується на підмножинах даних через задані інтервали. Обчислення середнього значення через певні інтервали згладжує дані, зменшуючи вплив випадкових коливань. Це полегшує побачити загальні тенденції, особливо на графіку. Чим більший інтервал, використовуваний для обчислення ковзної середньої, тим більше згладжування відбувається, оскільки більше точок даних включається в кожну розрахункову середню.
Пояснення
Формули, показані в прикладі, використовують функцію AVERAGE із відносним посиланням, встановленим для кожного конкретного інтервалу. 3-денна ковзаюча середня в 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. В іншому випадку формула повертає ковзне середнє, як і раніше. Це імітує поведінку версії ковзаючого середнього, що міститься в інструменті аналізу, яка видає # N / A до досягнення першого повного періоду.
Однак із збільшенням кількості періодів у вас з часом закінчаться рядки над даними, і ви не зможете ввести необхідний діапазон усередині AVERAGE. Наприклад, ви не можете встановити ковзне середнє за 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 немає. Отже, хоча діапазон, що передається в AVERAGE в 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 Таблицях, оскільки функція OFFSET у Таблицях не допускає від’ємного значення висоти чи ширини. Додана електронна таблиця містить формули обхідних шляхів для аркушів Google.
