Прогнозирование данных. Выделение тренда: скользящее среднее, функции регрессионного анализа

Прогнозирование данны х. Вариативны й анализ " Что Если" и Оптимизация. Вы деление тренда: скользящее среднее, функции регрессионного анализа: ПРЕДСКАЗ, ТЕНДЕНЦИЯ, РОСТ. Построение линий тренда. 1 Пакет анализа 2 Подключение надстройки 3 Анализ данны х 4 Скользящее среднее 5 Скользящее среднее Метод скользящей средней – один из эмпирических методов для сглаживания и прогнозирования временных рядов. Суть: абсолютные значения ряда динамики меняются на средние арифметические значения в определенные интервалы. Выбор интервалов осуществляется способом скольжения: первые уровни постепенно убираются, последующие – включаются. В результате получается сглаженный динамический ряд значений, позволяющий четко проследить тенденцию изменений исследуемого параметра. Временной ряд – это множество значений X и Y, связанных между собой. Х – интервалы времени, постоянная переменная. Y – характеристика исследуемого явления (цена, например, действующая в определенный период времени), зависимая переменная. С помощью скользящего среднего можно выявить характер изменений значения Y во времени и спрогнозировать данный параметр в будущем. Метод действует тогда, когда для значений четко прослеживается тенденция в динамике. 6 Использование СРЗНАЧ По значениям исходного временного ряда строим сглаженный временный ряд методом скользящего среднего по данным за 2 предыдущих месяца. 7 Использование СРЗНАЧ Рассчитаны значения по 3 и 4 предыдущим месяцам. На графике видно, что линии тренда скользящего среднего сдвинуты относительно линии исходного временного ряда. Это объясняется тем, что рассчитанные значения сглаженных временных рядов запаздывают по сравнению с соответствующими значениями заданного ряда. 8 Использование СРЗНАЧ Абсолютные отклонения: =ABS(C7-B7) Относительные отклонения: =ABS(C7-B7)/B7 Средние квадратичные отклонения: =КОРЕНЬ( СУММКВРАЗН(B7:B13;C7:C13)/СЧЁТ(B7:B13)) 9 Использование СРЗНАЧ После сопоставления таблиц с отклонениями стало видно, что для составления прогноза по методу скользящей средней в Excel о тенденции изменения выручки предприятия предпочтительнее модель двухмесячного скользящего среднего. У нее минимальные ошибки прогнозирования (в сравнении с трех- и четырехмесячной). Прогнозное значение выручки на 12 месяц – 9 430 млн.руб 10 Скользящее среднее Инструмент анализа Скользящее среднее применяется для расчета значений в прогнозируемом периоде на основе среднего значения переменной для указанного числа предшествующих периодов. Скользящее среднее, в отличие от простого среднего для всей выборки, содержит сведения о тенденциях изменения данных. Этот метод может использоваться для прогноза сбыта, запасов и других тенденций. Расчет прогнозируемых значений выполняется по следующей формуле: 𝑁 1 𝐹(𝑗+1) = 𝑁 ෍ 𝐴 𝑡 − 𝑗 +1 𝑗 =1 где N — число предшествующих периодов, входящих в скользящее среднее; A j — фактическое значение в момент времени j; F j — прогнозируемое значение в момент времени j. 11 Скользящее среднее Входной интервал – исходные значения временного ряда (одномерный массив). Интервал – число месяцев (периодов), включаемое в подсчет скользящего среднего. Выходной интервал – диапазон ячеек для выведения полученных результатов. 12 Скользящее среднее Сравнив стандартные погрешности, убеждаемся в том, что модель двухмесячного скользящего среднего больше подходит для сглаживания и прогнозирования. Она имеет меньшие стандартные погрешности. Прогнозное значение выручки на1312 месяц – 9 430 млн.руб Функции регрессионного анализа: ПРЕДСКАЗ, ТЕНДЕНЦИЯ, РОСТ 14 Формулы массивов 1. Если формула массив вернет n элементов, и вы хотите отобразить n элементов в n ячейках, вы должны выбрать n ячеек до ввода формулы. Кроме того, необходимо учитывать, является ли массив одно- или двумерным. В первом случае вы выберите ряд или столбец ячеек, во втором – прямоугольную область. 2. Создайте свою формулу массива в активной ячейке. 3. Введите формулу массива нажатием Ctrl+Shift+Enter. 15 Функции EXCEL, связанны е с инструментом Регрессия Во многих случаях нет необходимости в полном регрессионном анализе. Тогда возможно использование некоторых функций программы MS EXCEL, в той или иной мере связанных с регрессионным анализом: • НАКЛОН, • ОТРЕЗОК, • СТОШУХ, • ЛИНЕЙН, • ПРЕДСКАЗ, • ТЕНДЕНЦИЯ, • ЛГРФПРИБЛ, • РОСТ. 16 Функция НАКЛОН Устанавливает наклон линии линейной регрессии для точек данных, образованных известными значениями y i и известными значениями x i. Наклон определяется как частное от деления расстояния по вертикали на расстояние по горизонтали между двумя точками прямой, т.е. наклон - это скорость изменения значений вдоль прямой. Иначе говоря, результат вычисления функции НАКЛОН представляет собой коэффициент a1 в уравнении регрессии 𝑦 = 𝑎 0 + 𝑎 1𝑥 18 Функция ОТРЕЗОК Имеет те же аргументы (известные значения x и известные значения y), что и функция НАКЛОН, вычисляет точку пересечения линии парной линейной регрессии с осью y, т.е. при x = 0. Иначе говоря, результат вычисления функции ОТРЕЗОК представляет собой коэффициент a0 в уравнении регрессии 𝑦 = 𝑎 0 + 𝑎 1𝑥 19 Функция СТОШУХ Имеет те же аргументы, что и функции НАКЛОН, ОТРЕЗОК, принципиально отличается от них - она определяет не усреднённые результаты расчёта регрессионной модели, а стандартную ошибку предсказанных значений y в этой модели. Стандартная ошибка - это мера среднего рассеивания наблюдаемых значений (точек) вокруг подобранной линии регрессии, дающая некоторое представление о надежности уравнения регрессии для производства прогнозных расчетов. 20 Функция ЛИНЕЙН Рассчитывает статистику для ряда с применением метода наименьших квадратов путём вычисления линейной зависимости, которая наилучшим образом аппроксимирует имеющиеся данные. Функция ЛИНЕЙН может использоваться для расчёта множественной регрессии (в случае задания нескольких диапазонов значений x), имеющей вид 𝑦 = 𝑚1𝑥1 + 𝑚 2 𝑥 2 + ⋯ + 𝑏 Если массив «известные значения y» и в других функциях задан столбцом, то каждый столбец массива «известные значения x» интерпретируется как отдельная переменная. Наоборот, если массив «известные значения y» задан строкой, то каждая строка массива «известные значения x» интерпретируется как отдельная переменная. При вводе «вручную» массива констант в качестве, например аргумента «известные значения x», следует использовать точку с запятой для разделения значений в одной строке и двоеточие для разделения строк. Аргумент «Конст» - логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. Если «Конст» имеет значение ИСТИНА или отсутствует, то b вычисляется обычным образом. Если «Конст» имеет значение ЛОЖЬ, то b=0 и значения m подбираются так, чтобы выполнялось соотношение 𝑦 = 𝑚1𝑥1 + 𝑚 2 𝑥 2 + ⋯ + 𝑚 𝑛 𝑥 𝑛 Статистика - логическое значение, которое указывает, требуются ли дополнительные статистические данные по регрессии. Если аргумент «Статистика» имеет значение 0 («ЛОЖЬ») или отсутствует, то функция ЛИНЕЙН подсчитает только 21 аргумента «Статистика» представлено коэффициенты регрессии. Если в качестве любое другое число («ИСТИНА»), то производится расчёт дополнительной Функция ЛИНЕЙН Если для вывода результатов расчёта в MS EXCEL требуется не одна ячейка, необходимо предварительно выделить массив ячеек. Для функции ЛИНЕЙН необходимый массив состоит из пяти строк и числа столбцов, равного n +1. После введения всех аргументов необходимо набрать комбинацию клавиш CTRL+SHIFT+ENTER.(При нажатии «ОК» будет подсчитан только коэффициент при x.) В дополнительную регрессионную статистику входят: - стандартные значения ошибок коэффициентов m1, m2,... и постоянной b; - коэффициент детерминации; - стандартная ошибка для оценки y; - F-статистика (F-наблюдаемое значение); - степени свободы для нахождения F-критических значений; - регрессионная сумма квадратов (sstotal); - сумма квадратов остатков (ssresid). 21 Функция ЛИНЕЙН. Пример 1 Для вставки функции, которая возвращает несколько значений, необходимо выделить несколько ячеек. • Выделены ячейки A15 и B15 • Добавлена функция =ЛИНЕЙН(B2:B13;A2:A13;ИСТИНА;ИСТ ИНА) • Для расчета массива нажато CTRL+SHIFT+ENTER Получен результат: • A15 – наклон • B15 - отрезок 22 Функция ЛИНЕЙН. Пример 2 Для вставки функции, которая возвращает несколько значений, необходимо выделить несколько ячеек. • Выделен диапазон A15:B19 • Добавлена функция =ЛИНЕЙН(B2:B13;A2:A13;ИСТИНА;ИСТ ИНА) • Для расчета массива нажато CTRL+SHIFT+ENTER Получен результат: • A15 – Наклон • A16 – Стандартная ошибка для m • A17 – Коэффициент детерминированности R2 • A18 – F-статистика • A19 – Регрессионная сумма квадратов • B15 – Отрезок • B16 – Стандартная ошибка для b 24 • B17 – Стандартная ошибка для y Функция ТЕНДЕНЦИЯ Возвращает значения в соответствии с линейным трендом. Аппроксимирует прямой линией (по методу наименьших квадратов) массивы "известные_значения_y" и "известные_значения_x". Возвращает значения y, соответствующие этой прямой для заданного массива "новые_значения_x". Синтаксис ТЕНДЕНЦИЯ(известные_значения_y; [известные_значения_x]; [новые_значения_x]; [конст]) Известны е_значения_y Множество значений y, которые уже известны для соотношения y = mx + b. Если массив "известные_значения_y" имеет один столбец, то каждый столбец массива "известные_значения_x" интерпретируется как отдельная переменная. Если массив "известные_значения_y" содержит одну строку, каждая строка массива "известные_значения_x" интерпретируется как отдельная переменная. Известны е_значения_x Множество значений x, которые могут быть уже известны для соотношения y = mx + b. Массив "известные_значения_x" может содержать одно или несколько множеств переменных. Если используется только одна переменная, то аргументы "известные_значения_y" и "известные_значения_x" могут быть диапазонами любой формы при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то аргумент "известные_значения_y" должен быть вектором (то есть диапазоном высотой в одну строку или шириной в один столбец). Если аргумент "известные_значения_x" опущен, то предполагается, что это массив {1;2;3;...} того же размера, 24 что и "известные_значения_y". Функция ТЕНДЕНЦИЯ Новы е_значения_x . Новые значения x, для которых функция ТЕНДЕНЦИЯ возвращает соответствующие значения y. • Аргумент "новые_значения_x", так же как и аргумент "известные_значения_x", должен содержать по одному столбцу (или строке) для каждой независимой переменной. Таким образом, если "известные_значения_y" — это один столбец, то "известные_значения_x" и "новые_значения_x" должны иметь одинаковое количество столбцов. Если "известные_значения_y" — это одна строка, то аргументы "известные_значения_x" и "новые_значения_x" должны иметь одинаковое количество строк. • Если аргумент "новые_значения_x" опущен, то предполагается, что он совпадает с аргументом "известные_значения_x". • Если опущены оба аргумента — "известные_значения_x" и "новые_значения_x", — то предполагается, что это массивы {1;2;3;...} того же размера, что и "известные_значения_y". Конст Необязательный. Логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0. • Если аргумент "конст" имеет значение ИСТИНА или опущен, то b вычисляется обычным образом. • Если аргумент "конст" имеет значение ЛОЖЬ, то b полагается равным 0 и значения m подбираются таким образом, чтобы выполнялось условие y = mx. 25 Функция ТЕНДЕНЦИЯ Получено иное значение, чем при расчете среднего скользящего за счет того, что используется весь массив данных, а не часть его 26 Функция ПРЕДСКАЗ В Excel 2016 эта функция был заменена на ПРЕДСКАЗ.ЛИНЕЙН. Попрежнему доступен для обеспечения обратной совместимости, но рекомендуется использовать новые функции в Excel 2016. Вычисляет или предсказывает будущее значение по существующим значениям. Предсказываемое значение — это значение y, соответствующее заданному значению x. Значения x и y известны; новое значение предсказывается с использованием линейной регрессии. ПРЕДСКАЗ.ЛИНЕЙН(x;известные_значения_y;известные_значения_x) x — обязательный аргумент. Точка данных, для которой предсказывается значение. Известны е_значения_y — обязательный аргумент. Зависимый массив или интервал данных. Известны е_значения_x — обязательный аргумент. Независимый массив или интервал данных. 27 Функция ПРЕДСКАЗ.ЛИНЕЙН Получено иное значение, чем при расчете среднего скользящего за счет того, что используется весь массив данных, а не часть его 28 Функция РОСТ Рассчитывает прогнозируемый экспоненциальный рост на основе имеющихся данных. Функция РОСТ возвращает значения y для последовательности новых значений x, задаваемых с помощью существующих значений x и y. Функцию РОСТ также можно использовать для аппроксимации существующих значений x и y экспоненциальной кривой. РОСТ(известные_значения_y;[известные_значения_x];[новые_значения_x]; [конст]) Известны е_значения_y — обязательный аргумент. Множество значений y в уравнении y = b*m^x, которые уже известны. • Если массив "известные_значения_y" содержит один столбец, каждый столбец массива "известные_значения_x" интерпретируется как отдельная переменная. • Если массив "известные_значения_y" содержит одну строку, каждая строка массива "известные_значения_x" интерпретируется как отдельная переменная. • Если какие-либо числа в массиве "известные_значения_y" равны 0 или имеют отрицательное значение, функция РОСТ возвращает значение ошибки #ЧИСЛО!. Известны е_значения_x — необязательный аргумент. Множество значений x в уравнении y = b*m^x, которые уже известны. • Массив "известные_значения_x" может содержать одно или несколько множеств переменных. Если используется только одна переменная, множества "известные_значения_y" и "известные_значения_x" могут иметь любую длину, но их размерности должны совпадать. Если используется более одной переменной, аргумент "известные_значения_y" должен быть вектором (т. е. интервалом высотой 30 в одну строку или шириной в один столбец). Функция РОСТ Новы е_значения_x — необязательный аргумент. Новые значения x, для которых функция РОСТ возвращает соответствующие значения y. • Аргумент "новые_значения_x" должен содержать столбец (или строку) для каждой независимой переменной, так же как и "известные_значения_x". Таким образом, если массив "известные_значения_y" состоит из одного столбца, то столько же столбцов должны иметь массивы "известные_значения_x" и "новые_значения_x". Если массив "известные_значения_y" состоит из одной строки, столько же строк должно содержаться в массивах "известные_значения_x" и "новые_значения_x". • Если аргумент "новые_значения_x" опущен, предполагается, что он совпадает с аргументом "известные_значения_x". • Если опущены аргументы "известные_значения_x" и "новые_значения_x", предполагается, что каждый из них представляет собой массив {1;2;3;...} того же размера, что и "известные_значения_y". Конст — необязательный аргумент. Логическое значение, которое указывает, должна ли константа b равняться 1. • Если аргумент "конст" имеет значение ИСТИНА или опущен, b вычисляется обычным образом. • Если аргумент "конст" имеет значение ЛОЖЬ, то предполагается, что b = 1, а значения m подбираются таким образом, чтобы выполнялось равенство y = m^x. 30 Функция РОСТ Данная функция принадлежит к классу формул массивов, поэтому при ее использовании необходимо выделить соответствующее количество ячеек, а после ввода всех требуемых аргументов следует нажать сочетание клавиш Ctrl+Shift+Enter для корректного отображения результатов. 31 Анализ данны х Графические методы . Линии тренда 32 Прогнозирование. Вы бор источника данны х http://cbr.ru/currency_base/dynamics.aspx?VAL_NM_RQ=R01235&date_r eq1=01.10.2014&date_req2=01.12.2014&rt=1&mode=1 34 Построение точечной диаграммы 1. Выделить диапазон 2. Вставка – Точечная 3. Точечная диаграмма 35 Точечная диаграмма 36 Добавление линии тренда 37 Линейная линия тренда • уравнение на диаграмме • величина достоверности 𝑦 = 𝑎𝑥 + 𝑏 38 Экспоненциальная линия тренда y = 𝑎𝑒 𝑏 𝑥 39 Логарифмическая линия тренда y = 𝑎 𝑙𝑛(x) + b 40 Полиномиальная линия тренда 𝑦 = 𝑎𝑥 2 + 𝑏𝑥 + 𝑐 41 Полиномиальная линия тренда 𝑦 = 𝑎1 𝑥 6 + 𝑎2 𝑥 5 + 𝑎3 𝑥 4 + 𝑎4 𝑥 3 + 𝑎5 𝑥 2 + 𝑎6 𝑥 + 𝑎7 42 Прогноз • Построение прогноза вперед и назад • При работе с датами надо учитывать, что дата в Excel представлена в виде целого числа 43 Прогноз Неточность полинома большой степени 44 Прогноз • Неточность полинома большой степени • Линейная функция точно описывает общую тенденцию • Полином точно "попадает" в каждую точку, но недостоверен в промежутках и прогнозах 45