Как сделать прогнозирование в excel
Прогнозирование значений в рядах
Если вам нужно спрогнозировать расходы на следующий год или проецировать ожидаемые результаты для ряда в научном эксперименте, вы можете использовать Microsoft Office Excel для автоматического создания будущих значений, основанных на существующих данных, или для автоматического получения экстраполированных значений, основанных на вычислениях линейного тренда или тренда роста.
Вы можете заполнить ряд значений, которые соответствуют простому линейному или экспоненциальному тренду роста, с помощью маркер заполнения или последовательности. Для расширения сложных и нелинейных данных можно использовать функции или регрессионный анализ в надстройке «Надстройка «Надстройка анализа».
В линейном ряду шаг или разница между первым и следующим значением добавляется к начальному значению, а затем добавляется к каждому последующему значению.
Расширенный линейный ряд
Чтобы заполнить ряд для линейного тренда, сделайте следующее:
Выделите не менее двух ячеек, содержащих начальные значения для тренда.
Чтобы повысить точность ряда трендов, выберите дополнительные начальные значения.
Перетащите его в нужном направлении.
Например, если в ячейках C1:E1 выбраны начальные значения 3, 5 и 8, перетащите его вправо, чтобы заполнить значениями тенденций, или перетащите его влево, чтобы заполнить значениями убывания.
Совет: Чтобы вручную управлять тем, как создается ряд, или заполнить ряд с помощью клавиатуры, выберите команду Ряд (вкладка Главная, группа Редактирование, кнопка Заполнить).
В рядах роста начальное значение умножается на шаг, чтобы получить следующее значение в ряду. Результат и каждый последующий результат умножаются на шаг.
Расширенный ряд роста
Чтобы заполнить ряд для тенденции роста, сделайте следующее:
Выделите не менее двух ячеек, содержащих начальные значения для тренда.
Чтобы повысить точность ряда трендов, выберите дополнительные начальные значения.
Удерживая нажатой правую кнопку мыши, перетащите указатель заполнения в нужном направлении, отпустите кнопку мыши, а затем на ленте нажмите кнопку контекстное меню.
Например, если в ячейках C1:E1 выбраны начальные значения 3, 5 и 8, перетащите его вправо, чтобы заполнить значениями тенденций, или перетащите его влево, чтобы заполнить значениями убывания.
Совет: Чтобы вручную управлять тем, как создается ряд, или заполнить ряд с помощью клавиатуры, выберите команду Ряд (вкладка Главная, группа Редактирование, кнопка Заполнить).
При нажатии кнопки Ряд можно вручную управлять тем, как создается линейный тренд или тенденция роста, а затем заполнять значения с помощью клавиатуры.
В линейном ряду начальные значения применяются к алгоритму наименьших квадратов (y=mx+b), который создает ряд.
В рядах роста начальные значения применяются к алгоритму экспоненциальной кривой (y=b*m^x), который создает ряд.
В обоих случаях шаг игнорируется. Созданный ряд эквивалентен значениям, возвращенным функцией ТЕНДЕНЦИЯ или функцией РОСТ.
Чтобы заполнить значения вручную, сделайте следующее:
Вы выберите ячейку, в которой нужно начать ряд. Ячейка должна содержать первое значение ряда.
При выборе команды Ряд итоговые ряды заменяют исходные выбранные значения. Если вы хотите сохранить исходные значения, скопируйте их в другую строку или столбец, а затем создайте ряд, выбирая скопированные значения.
На вкладке Главная в группе Редактирование нажмите кнопку Заполнить и выберите пункт Прогрессия.
Выполните одно из указанных ниже действий.
Чтобы заполнить ряд вниз по worksheet, щелкните Столбцы.
Чтобы заполнить ряд по всему ряду, щелкните Строки.
В поле Шаг введите значение, на которое вы хотите увеличить ряд.
Значение шага добавляется к первому начальному значению, а затем к каждому последующему значению.
Первое начальное значение умножается на шаг. Результат и каждый последующий результат умножаются на шаг.
В области Типвыберите линейный или Рост.
В поле Остановить значение введите значение, на которое нужно остановить ряд.
Примечание: Если ряд имеет несколько начальных значений и Excel создать тенденцию, выберите значение Тренд.
Если у вас есть данные, для которых вы хотите спрогнозировать тенденцию, можно создать линия тренда на диаграмме. Например, если в Excel есть диаграмма с данными о продажах за первые несколько месяцев года, вы можете добавить на нее линию тренда, которая отображает общий тренд продаж (увеличение или уменьшение или снижение), а также прогнозируемый тренд на месяцы вперед.
Предполагается, что вы уже создали диаграмму, основанную на существующих данных. Если это не так, см. раздел Создание диаграммы.
Щелкните ряд данных, в который вы хотите добавить линия тренда или скользящее среднее.
На вкладке Макет в группе Анализ нажмите кнопку Линия тренда ивыберите нужный тип линии тренда или скользящего среднего.
Чтобы настроить параметры и отформатирование линии тренда или скользящего среднего, щелкните линию тренда правой кнопкой мыши и выберите в меню пункт Формат линии тренда.
Выберите нужные параметры линии тренда, линии и эффекты.
При выборе параметра Полиномиальная, введите в поле Порядок наивысшую мощность для независимой переменной.
В поле На основе ряда перечислены все ряды данных на диаграмме, которые поддерживают линии тренда. Чтобы добавить линию тренда к другому ряду, щелкните имя в поле и выберите нужные параметры.
При добавлении скользящего среднего на точечная диаграмма скользящие средние значения основаны на порядке, за исключением значений X, относящегося к диаграмме. Чтобы получить нужный результат, перед добавлением скользящего среднего может потребоваться отсортировать значения x.
Важно: Начиная с Excel 2005 г., Excel способ вычисления значения R2 для линейных линий тренда на диаграммах, где для перехваченной линии тренда установлено значение нуля (0). Эта корректировка исправит вычисления, которые дают неправильные значения R 2, и выровняет вычисление R2 с функцией LINEST. В результате на диаграммах, созданных в предыдущих версиях Excel, могут отображаться разные значения R2. Дополнительные сведения см. в таблице Изменения внутренних вычислений линейных линий тренда на диаграмме.
Если вам нужно выполнить более сложный регрессионный анализ, в том числе вычислить и отсчитывать остаточные данные, используйте средство регрессионного анализа в надстройке «Надстройка «Надстройка анализа». Дополнительные сведения см. в окне Загрузка средства анализа.
В Excel в Интернете, вы можете проецировать значения в ряду с помощью функций или щелкнуть и перетащить его, чтобы создать линейный тренд чисел. Однако создать тенденцию роста с помощью ручки заполнения нельзя.
Вот как можно использовать его для создания линейного тренда чисел в Excel в Интернете:
Выделите не менее двух ячеек, содержащих начальные значения для тренда.
Чтобы повысить точность ряда трендов, выберите дополнительные начальные значения.
Перетащите его в нужном направлении.
Использование функции ПРОГНОЗ Функция ПРЕДСПРОС вычисляет или предсказывает будущее значение с использованием существующих значений. Предсказываемое значение — это значение y, соответствующее заданному значению x. Значения x и y известны; новое значение предсказывается с использованием линейной регрессии. Эта функция используется для предсказания будущих продаж, требований к запасам и потребительских тенденций.
Использование функции ТЕНДЕНЦИЯ или ФУНКЦИИ РОСТ Функции ТЕНДЕНЦИЯ и РОСТ могут выполнять экстраполяцию будущих значений y,которые расширяют прямую или экспоненциальный кривую, наилучшим образом описывающую существующие данные. Они также могут возвращать только значения yна основе известных значений x-длянаиболее подходящих строк или кривой. Для отстройки линии или кривой, описывающую существующие данные, используйте существующие значения x-value и y-value,возвращаемые функцией ТЕНДЕНЦИЯ или РОСТ.
Использование функции ЛИНИИСТОЛ или ФУНКЦИИ ЛОГЕСТ Функцию ЛИННЕФ или LOGEST можно использовать для вычисления прямой или экспоненциальной кривой из существующих данных. Функции LINEST и LOGEST возвращают различные статистические данные о регрессии, включая наклон и отступ линии, которая лучше всего подходит.
В следующей таблице содержатся ссылки на дополнительные сведения об этих функциях.
Project значения, которые соответствуют прямой линии тренда
Project, которые соответствуют экспоненциальной кривой
Расчет прямой линии из существующих данных
Расчет экспоненциальной кривой из существующих данных
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Функции предсказания в Excel
Excel, как универсальный табличный редактор, давно и неплохо справляется с большинством задач прогнозирования (см. список литературы в конце заметки). Однако, не всегда вычисления в Excel являются простыми и понятными. И вот в версии 2016 года разработчики Microsoft добавили семейство функций ПРЕДСКАЗ (FORECAST), которые позволяют в несколько кликов решать большой круг задач прогнозирования на основе экспоненциального сглаживания.
Рис. 1. Прогнозирование продаж в Excel с помощью семейства функций ПРЕДСКАЗ
Скачать заметку в формате Word или pdf, примеры в формате Excel
Об экспоненциальном сглаживании
Экспоненциальное сглаживание также известно, как метод ETS: ошибки (Errors), тренд (Trend), сезонный фактор (Seasonal). Для составления прогноза используются все исторические данные, но коэффициенты, определяющие вклад, убывают в прошлое по экспоненте (отсюда и название). Это позволяет, с одной стороны, чутко реагировать на свежие данных, с другой стороны, сохранять информацию об историческом поведении всего временного ряда. Если данным присущ тренд, он вычисляется в каждой точке данных (а не на основе регрессии всего временного ряда). Наконец, с помощью автокорреляции в данных выявляется сезонность.
Преимущество модели в том, что она не использует никаких предположений относительно характера тренда (или его отсутствия) и периодичности сезонных колебаний (или их отсутствия). Все коэффициенты в модели подбираются на основе минимизации суммы квадратов ошибок, то есть, разности между прогнозом на исторических данных и самих данных. Если вас интересует, как это происходит, рекомендую работу Формана (см. список литературы).
Собственно, оптимизируются три коэффициента:
α – разброс относительно среднего
Разработчики Microsoft не предоставили пользователям возможность влиять на выбор коэффициентов, за исключением периода сезонности (об этом ниже).
Обзор функций семейства ПРЕДСКАЗ
В Excel представлено 5 функций:
Рис. 2. Семейство функций ПРЕДСКАЗ в Excel
ПРЕДСКАЗ.ETS рассчитывает будущее значение на основе существующих (ретроспективных) данных методом экспоненциального сглаживания. Т.е., дает прогноз одним числом.
ПРЕДСКАЗ.ЕTS.ДОВИНТЕРВАЛ возвращает доверительный интервал для прогнозной величины. Доверительный интервал следует отложить по обе стороны от среднего значения. Вместе с ПРЕДСКАЗ.ETS позволяет построить «коридор» прогноза.
ПРЕДСКАЗ.ETS.СЕЗОННОСТЬ возвращает длину повторяющегося фрагмента, обнаруженного в заданном временном ряду. Например, 12, если исторические данные представляют из себя продажи за месяц.
ПРЕДСКАЗ.ETS.СТАТ возвращает восемь статистических значений, являющихся результатом прогнозирования временного ряда. Вряд ли вы будете использовать эту функцию. Она нужна для более тонкого исследования параметров прогнозной модели.
ПРЕДСКАЗ.ЛИНЕЙН вычисляет будущее значение с помощью линейной регрессии исторических данных. До версии 2016 в Excel вместо семейства функций была единственная функция ПРЕДСКАЗ, которая работала также, как и ПРЕДСКАЗ.ЛИНЕЙН. Функция ПРЕДСКАЗ оставлена для обратной совместимости, но скоро перестанет поддерживаться. Далее в заметке ПРЕДСКАЗ.ЛИНЕЙН не рассматривается, так как не относится к функциям, использующим алгоритм экспоненциального сглаживания.
ПРЕДСКАЗ.ETS
В качестве примера рассмотрим месячный пассажиропоток в аэропорту (пример от MS). Исторические данные были собраны за период с января 2009 по декабрь 2912 г.
Рис. 3. Исторические данные
Продолжим временную шкалу еще на год, и создадим столбец для прогноза. Обычно прогноз располагают в отдельном столбце для того, чтобы при построении графика представить исторические и прогнозные значения разными линиями.
Рис. 4. Прогнозные значения на основе функции ПРЕДСКАЗ.ETS
Подробнее о формуле в ячейке С50:
Первый аргумент – целевая_дата = А50 – янв.13, т.е., в ячейке С50 ищется прогноз пассажиропотока для января 2013 г. Ссылка относительная, что позволит при протягивании функции вниз по столбцу ссылаться на новое значение: в С51 – на А51, в С52 – на А52 и т.д.
Четвертый аргумент – [сезонность] = 1. Это необязательный аргумент. Значение по умолчанию равно 1. Для него Excel автоматически определяет сезонность и использует положительные целые числа в качестве длины сезонного шаблона. Значение 0 предписывает не использовать фактор сезонности, в результате чего прогноз будет линейным. Если для этого параметра задано положительное целое число, алгоритм использует его в качестве длины шаблона сезонности. Например, вы знаете, что сезонность равна 4 (квартальная периодичность), но предполагаете, что она слабая, и автоматический алгоритм Excel может ее не выявить, и будет считать, что сезонности нет. Для начала я рекомендовал бы использовать значение по умолчанию.
Пятый аргумент – [заполнение_данных] = 1. Это необязательный аргумент. Хотя временная шкала требует постоянный шаг между точками данных, FORECAST.ETS поддерживает до 30% отсутствующих данных и автоматически настраивает их. 0 указывает, что алгоритм учитывает отсутствующие точки в качестве нулей. Если задано значение 1 (вариант по умолчанию), функция определяет отсутствующие значения как среднее между соседними точками.
Шестой аргумент – [агрегирование] – в нашем примере опущен. Это необязательный аргумент. Он нужен, если даты временной шкалы или номера периодов содержат дубли. Функция ПРЕДСКАЗ.ETS выполнит агрегирование точек с одинаковой меткой времени. Параметр агрегирования — это числовое значение, определяющее способ агрегирования нескольких значений с одинаковой меткой времени. Для значения по умолчанию 0 используется метод СРЗНАЧ; также доступны варианты СУММ, СЧЁТ, СЧЁТЗ, МИН, МАКС и МЕДИАНА.
Прогнозирование продаж в Excel и алгоритм анализа временного ряда
Прогнозирование продаж в Excel не сложно составить при наличии всех необходимых финансовых показателей.
В данном примере будем использовать линейный тренд для составления прогноза по продажам на бушующие периоды с учетом сезонности.
Линейный тренд хорошо подходит для формирования плана по продажам для развивающегося предприятия.
Excel – это лучший в мире универсальный аналитический инструмент, который позволяет не только обрабатывать статистические данные, но и составлять прогнозы с высокой точностью. Для того чтобы оценить некоторые возможности Excel в области прогнозирования продаж, разберем практический пример.
Пример прогнозирования продаж в Excel
Рассчитаем прогноз по продажам с учетом роста и сезонности. Проанализируем продажи за 12 месяцев предыдущего года и построим прогноз на 3 месяца следующего года с помощью линейного тренда. Каждый месяц это для нашего прогноза 1 период (y).
Уравнение линейного тренда:
Допустим у нас имеются следующие статистические данные по продажам за прошлый год.
Общая картина составленного прогноза выглядит следующим образом:
График прогноза продаж:
Алгоритм анализа временного ряда и прогнозирования
Алгоритм анализа временного ряда для прогнозирования продаж в Excel можно построить в три шага:
Нужно понимать, что точный прогноз возможен только при индивидуализации модели прогнозирования. Ведь разные временные ряды имеют разные характеристики.
Чтобы посмотреть общую картину с графиками выше описанного прогноза рекомендуем скачать данный пример:
Прогнозирование тенденций в данных
Вы можете продлить ряд значений, который описывается простым линейным или экспоненциальным приближением, используя маркер заполнения.
Ниже приведены инструкции по отображению и распознаванию трендов, а также по составлению прогноза.
Прогнозирование трендов на базе имеющихся данных
Создание линейного приближения
В линейных последовательностях шаг (разница между начальным и следующим значением последовательности) добавляется к начальному и к каждому последующему значению.
Выделите не менее двух ячеек, содержащих начальные значения для тренда.
Чтобы повысить точность значений последовательности, укажите дополнительные начальные значения.
Перетащите маркер заполнения в сторону увеличения или уменьшения значений.
Например, если вы выбрали ячейки C1:E1, содержащие начальные значения 3, 5 и 8, то при перетаскивании маркера заполнения вправо значения будут возрастать, а влево — убывать.
Совет: Чтобы вручную настроить создаваемые последовательности, в меню Правка выберите пункт Заполнить и команду Ряд.
Создание экспоненциального приближения
В экспоненциальных последовательностях начальное значение умножается на шаг для получения следующего значения. Получившийся результат и каждый последующий результат умножаются на шаг.
Выделите не менее двух ячеек, содержащих начальные значения для тренда.
Чтобы повысить точность значений последовательности, укажите дополнительные начальные значения.
Удерживая нажатой клавишу CONTROL, перетащите маркер заполнения в нужном направлении, чтобы заполнить ячейки возрастающими или убывающими значениями.
Например, если вы выбрали ячейки C1:E1, содержащие начальные значения 3, 5 и 8, то при перетаскивании маркера заполнения вправо значения будут возрастать, а влево — убывать.
Отпустите клавишу CONTROL и кнопку мыши, а затем в контекстном меню выберите команду Экспоненциальное приближение.
Excel автоматически рассчитывает экспоненциальное приближение и продолжает ряд, заполняя значениями выделенные ячейки.
Совет: Чтобы вручную настроить создаваемые последовательности, в меню Правка выберите пункт Заполнить и команду Ряд.
Отображение ряда на диаграмме с помощью линии тренда
С помощью линии тренда и скользящего среднего на двумерных диаграммах можно отобразить тренды и аналитически решить задачу прогнозирования (выполнить регрессионный анализ). Использование скользящего среднего позволяет сгладить колебания данных и более наглядно показать тенденцию. С помощью регрессионного анализа можно получить представление о зависимости между переменными и продлить линию тренда на диаграмме за пределы фактических данных, чтобы предсказать будущие значения.
На диаграмме выберите ряд данных, для которого требуется добавить линию тренда или скользящее среднее.
На вкладке Конструктор нажмите кнопку Добавить элемент диаграммы и выберите пункт Линия тренда.
Примечание: Линии тренда можно добавлять только на диаграммы без накопления, плоские диаграммы, диаграммы с областями, линейчатые диаграммы, гистограммы, графики, биржевые, точечные и пузырьковые диаграммы.
Выберите нужный тип линии тренда или скользящего среднего.
Примечание: При добавлении скользящего среднего на точечная диаграмма скользящие средние значения основаны на порядке, за исключением значений X, относящегося к диаграмме. Чтобы получить нужный результат, перед добавлением скользящего среднего может потребоваться отсортировать значения X.
С помощью линии тренда и скользящего среднего на двумерных диаграммах можно отобразить тренды и аналитически решить задачу прогнозирования (выполнить регрессионный анализ). Использование скользящего среднего позволяет сгладить колебания данных и более наглядно показать тенденцию. С помощью регрессионного анализа можно получить представление о зависимости между переменными и продлить линию тренда на диаграмме за пределы фактических данных, чтобы предсказать будущие значения.
На диаграмме выберите ряд данных, для которого требуется добавить линию тренда или скользящее среднее.
В меню Диаграмма выберите команду Добавить линию тренда, а затем — пункт Тип.
Примечание: Линии тренда можно добавлять только на диаграммы без накопления, плоские диаграммы, диаграммы с областями, линейчатые диаграммы, гистограммы, графики, биржевые, точечные и пузырьковые диаграммы.
Выберите нужный тип линии тренда или скользящего среднего.
Выполните одно из указанных ниже действий.
В поле Степень укажите наибольшую степень для независимой переменной.
В поле Период укажите число периодов, которые нужно использовать для расчета скользящего среднего.
Примечание: При добавлении скользящего среднего на точечная диаграмма скользящие средние значения основаны на порядке, за исключением значений X, относящегося к диаграмме. Чтобы получить нужный результат, перед добавлением скользящего среднего может потребоваться отсортировать значения X.
Анализ временных рядов и прогнозирование в Excel на примере
Анализ временных рядов позволяет изучить показатели во времени. Временной ряд – это числовые значения статистического показателя, расположенные в хронологическом порядке.
Подобные данные распространены в самых разных сферах человеческой деятельности: ежедневные цены акций, курсов валют, ежеквартальные, годовые объемы продаж, производства и т.д. Типичный временной ряд в метеорологии, например, ежемесячный объем осадков.
Временные ряды в Excel
Если фиксировать значения какого-то процесса через определенные промежутки времени, то получатся элементы временного ряда. Их изменчивость пытаются разделить на закономерную и случайную составляющие. Закономерные изменения членов ряда, как правило, предсказуемы.
Сделаем анализ временных рядов в Excel. Пример: торговая сеть анализирует данные о продажах товаров магазинами, находящимися в городах с населением менее 50 000 человек. Период – 2012-2015 гг. Задача – выявить основную тенденцию развития.
Внесем данные о реализации в таблицу Excel:
Подключение настройки «Анализ данных» детально описано здесь.
Нужная кнопка появится на ленте.
Из предлагаемого списка инструментов для статистического анализа выбираем «Экспоненциальное сглаживание». Этот метод выравнивания подходит для нашего динамического ряда, значения которого сильно колеблются.
Заполняем диалоговое окно. Входной интервал – диапазон со значениями продаж. Фактор затухания – коэффициент экспоненциального сглаживания (по умолчанию – 0,3). Выходной интервал – ссылка на верхнюю левую ячейку выходного диапазона. Сюда программа поместит сглаженные уровни и размер определит самостоятельно. Ставим галочки «Вывод графика», «Стандартные погрешности».
Закрываем диалоговое окно нажатием ОК. Результаты анализа:
Для расчета стандартных погрешностей Excel использует формулу: =КОРЕНЬ(СУММКВРАЗН(‘диапазон фактических значений’; ‘диапазон прогнозных значений’)/ ‘размер окна сглаживания’). Например, =КОРЕНЬ(СУММКВРАЗН(C3:C5;D3:D5)/3).
Прогнозирование временного ряда в Excel
Составим прогноз продаж, используя данные из предыдущего примера.
На график, отображающий фактические объемы реализации продукции, добавим линию тренда (правая кнопка по графику – «Добавить линию тренда»).
Настраиваем параметры линии тренда:
Выбираем полиномиальный тренд, что максимально сократить ошибку прогнозной модели.
R2 = 0,9567, что означает: данное отношение объясняет 95,67% изменений объемов продаж с течением времени.
Уравнение тренда – это модель формулы для расчета прогнозных значений.
Большинство авторов для прогнозирования продаж советуют использовать линейную линию тренда. Чтобы на графике увидеть прогноз, в параметрах необходимо установить количество периодов.
Получаем достаточно оптимистичный результат:
В нашем примере все-таки экспоненциальная зависимость. Поэтому при построении линейного тренда больше ошибок и неточностей.
Для прогнозирования экспоненциальной зависимости в Excel можно использовать также функцию РОСТ.
Для линейной зависимости – ТЕНДЕНЦИЯ.
При составлении прогнозов нельзя использовать какой-то один метод: велика вероятность больших отклонений и неточностей.