Розглянемо використання формул "СУММЕСЛИ" і "СЧЕТЕСЛІ"
на прикладі розрахунку прогнозу за допомогою моделі "ковзної середньої до 3-х місяців з адитивною сезонністю".
Розрахуємо:
- Ковзаючу середню до 3-х місяців;
- Різниця між значеннями ряду і середніми значеннями до 3-х місяців (пункт 1);
- Усереднити різницю ряду і середньої для кожного місяця отримаємо сезонність в абсолютній величання - для цього скористаємося функціями СУММЕСЛИ і СЧЕТЕСЛІ;
- Продовжимо значення ряду за допомогою ковзної середньої до 3-х місяців і скорегуємо її адитивної сезонністю.
1. ковзних середню до 3-х місяців для кожного моменту часу в часі ряду починаючи з 4-го періоду:
2. Різниця між значеннями ряду і середніми значеннями до 3-х місяців для кожного моменту часу t (пункт 1):
Завантажити файл з прикладом в Excel
3. усереднити різницю ряду і середньої (пункт 2) для 12 місяців - отримаємо сезонність в абсолютній величання - аддитивную сезонність.
Для цього спочатку виділимо номера місяців за допомогою функції Excel = місяць (дата).
Отримуємо ряд з пронумерованими місяцями від 1 до 12 для кожного моменту часу t:
Далі за допомогою функції Excel = СУММЕСЛИ підсумовуємо для кожного місяця від 1 до 12 відхилення, які ми вважали в пункті 2.
Для цього в формулу СУММЕСЛИ передаємо наступні значення:
- діапазон - $ G $ 8: $ AY $ 8 - діапазон з номера місяців за весь період;
- критерій - D9 - номер місяця для якого підсумовуємо відхилення і розраховуємо сезону;
- діапазон для підсумовування - $ G $ 7: $ AY $ 7 - діапазон з відхиленнями розрахованими в пункті 2).
Фіксуємо посилання на діапазон з номерами місяців і діапазон з відхиленнями (як зафіксувати посилання описано в статті "Як зафіксувати посилання в Excel?"), І простягаємо формулу для 12 місяців від 1 до 12.
Отримуємо суму відхилень для кожного місяця.
Далі за допомогою формули Excel = СЧЁТЕСЛІ () вважаємо кількість місяців в часі ряду, на яке ми розділимо суму відхилень і отримаємо середнє абсолютне відхилення для кожного місяця - аддитивную сезонність.
І так передаємо в формулу = СЧЁТЕСЛІ (
- діапазон - $ G $ 8: $ AY $ 8 - фіксуємо посилання - номери місяців в часі ряду;
- критерій - D9 - номери місяців для сезонності, кол-во яких нам треба розрахувати в часі ряду для того щоб розрахувати середнє відхилення.
Завантажити файл з прикладом в Excel
Простягаємо формулу Excel = СЧЁТЕСЛІ від 1 до 12 і отримуємо ряд з кількістю місяців для кожного місяця сезонності:
Далі суму відхилень ділимо на кількість місяців в розрахунку сезонності, отримуємо абсолютну (адитивна) сезонність для кожного місяця в році:
Даний розрахунок ми виконали в 3 кроки
- Підсумували відхилення для кожного місяця сезонності за допомогою СУММЕСЛИ,
- Розрахували кількість періодів для кожного місяця сезонності за допомогою СЧЁТЕСЛІ,
- І розділили пункт 1 на пункт 2, тобто суму відхилень на кількість періодів, тобто знайшли середнє відхилення для кожного місяця.
Але ці 3 кроки можна поєднати в один і відразу отримати 12 значень адитивної сезонності.
Для цього СУММЕСЛИ розділимо на СЧЁТЕСЛІ, як на зображенні:
Формула виходить наступного виду:
Вводимо формулу, простягаємо на 12 місяців, отримуємо сезонність:
Ще раз її розберемо = СУММЕСЛИ ($ G $ 8: $ AY $ 8; D9; $ G $ 7: $ AY $ 7) / СЧЁТЕСЛІ ($ G $ 8: $ AY $ 8; D9)
СУММЕСЛИ ($ G $ 8: $ AY $ 8 (зафіксований діапазон з номерами місяців); D9 (номер місяця, для якого ми розраховуємо сезонність); $ G $ 7: $ AY $ 7 (зафіксований діапазон зі значеннями різниці між рядом і середньої - пункт 2) )
СЧЁТЕСЛІ ($ G $ 8: $ AY $ 8 (зафіксований діапазон з номерами місяців); D9 (номер місяця, для якого ми розраховуємо сезонність))
Тепер давайте розрахуємо прогноз.
Для цього продовжимо значення ряду за допомогою ковзної середньої до 3-х місяців і скорегуємо її адитивної сезонністю.
Продовжимо ковзаючу среднею до 3-х місяців в майбутнє на 12 місяців:
Скорегуємо ковзаючу сезонністю.
Для цього до ковзної середньої додамо аддитивную сезонність. Сезонність для кожного місяця підтягнемо за допомогою функції Excel ГПР.
Прогноз = середні продажу за останні 3 місяці + сезонність:
Завантажити файл з прикладом в Excel
Для того щоб цієї помилки свідомо уникнути і змусити думати машину використовуйте формули Excel = СУММЕСЛИ і СЧЁТЕСЛІ. Комбінація даних формул допоможе вам розрахувати середнє значення по заданому критерію.
А якщо хочете на 100% уникнути помилок в розрахунках прогнозів, та ще й автоматично підібрати потрібну модель для кожного часового ряду працюйте з Forecast4AC PRO.
Forecast4AC PRO заощадить вам купу часу дозволить робити точніші прогнози.
Точних вам прогнозів!
Рішення для бізнес-аналіз і прогнозування
Приєднуючись до нас Ви отримуєте:
