Аналитики данных используют множество инструментов для выявления закономерностей в бизнес-данных и прогнозирования того, что может произойти в будущем. Прогнозирование в Excel — это функция, которую многие профессионалы используют для изучения исторических данных и создания прогнозов, подкрепленных данными, на основе которых можно принимать бизнес-решения. Если вы хотите строить надежные прогнозы в Excel, вам будет полезно понять, как работает эта функция и какие функции доступны. В этой статье мы дадим определение прогнозирования в Excel, объясним три различные функции, которые вы можете использовать, и расскажем, почему они важны.
Что такое функция прогнозирования в Excel?
Функция прогнозирования — это инструмент анализа данных, который можно использовать в Excel для составления прогнозов будущего поведения на основе набора исторических данных. Существует три различных функции, которые можно использовать для прогнозирования результатов, и вы можете выбрать подходящую функцию в зависимости от целей вашего анализа и типа данных, которыми вы располагаете. Прогнозирование может быть ценным для аналитиков, которые пытаются быстро понять тенденции и закономерности в существующих данных. Он также может помочь вам определить вероятность успеха в будущем путем выявления и воспроизведения тенденций и закономерностей сезонности.
Прогнозирование в Excel с помощью функции =РОСТ( ). EXCEL ДЛЯ НАЧИНАЮЩИХ
Например, вы можете использовать прогнозирование в Excel для анализа данных о продажах и понимания покупательских привычек клиентов. Розничный магазин может захотеть использовать рекламные акции, чтобы стимулировать рост продаж в периоды спада. Чтобы определить подходящее время для проведения рекламных акций, аналитик может собрать данные за последние три года. Вы можете ввести эти данные в Excel и запустить функцию прогнозирования, чтобы узнать, когда продажи могут резко вырасти или упасть в следующем году. Затем вы можете визуализировать эти данные в Excel, чтобы легко анализировать их и делиться ими с другими.
Типы данных, которые можно использовать при прогнозировании в Excel
Прогнозы Excel опираются на временные данные, то есть они основаны на времени. Ваши данные могут содержать конкретные даты, месяцы или даже минуты, но некоторое измерение времени является жизненно важным, чтобы Excel мог понять, как ваши значения меняются со временем. Также полезно, если ваши данные используют четные интервалы. Если между каждой точкой данных проходит одинаковое количество времени, Excel легче сделать точные оценки. Если у вас есть равномерно распределенные временные данные с серией значений, ваши данные могут подойти для прогнозирования в Excel.
3 способа использования функции прогнозирования
Ниже приведены три различных способа использования функции прогнозирования:
Линейная регрессия
Линейная регрессия — это простой метод прогнозирования в Excel, который может помочь вам визуализировать общее направление развития ваших данных. Если у вас есть набор данных, в которых отсутствует сезонность, и вы хотите предсказать, как они будут развиваться в будущем, вам может подойти линейная регрессия. Эта формула рассматривает ваши данные, чтобы найти взаимосвязь между временем, которое охватывает ваш набор данных, и любыми изменениями в значениях, поэтому важно, чтобы ваши данные включали даты.
Прогноз в Excel
Чтобы применить линейную регрессию к данным, добавьте новый столбец справа от дат и значений. Определите диапазон дат, которые вы хотите учесть в своем прогнозе, и щелкните по ячейке непосредственно рядом с записью первой даты. Введите следующую формулу и скопируйте ее вниз, чтобы заполнить весь диапазон:
= прогноз.linear([ячейка, содержащая первую дату в вашем диапазоне],[ячейка, содержащая первое значение в вашем диапазоне]:[ячейка, содержащая последнее значение в вашем диапазоне])
Скользящие средние
Если в ваших данных есть отклонения, которые искажают результаты, вы можете использовать метод скользящих средних, чтобы удалить эти аномальные цифры и получить более четкое понимание общих тенденций. Хотя скользящие средние дают менее точные прогнозы, они помогают увидеть общие тенденции и могут быть полезны при прогнозировании долгосрочного поведения. Чтобы использовать эту технику, вы можете выбрать диапазон данных, которые вы хотите проанализировать, и применить функцию среднего значения. Вот формула, которую вы можете использовать:
среднее([первая точка данных]:[последняя точка данных])
Далее вы можете создать график, представляющий данные. Создайте новый график из выбранного диапазона и откройте окно Элементы диаграммы меню. Найти Линия тренда и попросить Excel открыть выпадающее меню, наведя на него курсор. Когда откроется окно, нажмите на Больше вариантов развернуть меню.
Возможно, вы увидите опцию под названием Скользящее среднее . Выберите этот график и настройте Период поле для отражения количества точек данных в вашем диапазоне. Затем Excel создает линейную визуализацию диапазона, сопровождаемую более плавной ломаной линией, которая представляет скользящую среднюю.
Экспоненциальное сглаживание
Чтобы лучше понять закономерности сезонности в больших массивах данных, можно использовать функцию экспоненциального сглаживания. Анализируя сезонные тенденции в большом наборе данных и взвешивая недавние данные более весомо, чем более исторические, эта функция отображает обоснованный прогноз потенциальных результатов. Экспоненциальное сглаживание может быть полезным инструментом для начинающих аналитиков, стремящихся расширить свои навыки, поскольку Excel автоматизирует большую часть анализа. Более продвинутые аналитики могут выбрать ручное управление процессом, чтобы настроить результат. Функция экспоненциального сглаживания приведена ниже:
прогноз.ets([первая точка данных]:[последняя точка данных])
Вы можете получить доступ к этой функции, выбрав свой диапазон значений и перейдя по ссылке Данные на вашей панели инструментов. После нажатия кнопки Данные , выберите Прогнозирование . Опция под названием Лист прогноза может отображаться с пиктограммой линейного графика. Нажмите на него и выберите тип визуализации, который вы предпочитаете. Многие аналитики любят использовать линейный график, чтобы легко понять, как их значения меняются с течением времени.
Затем Excel отобразит визуализацию данных с полезным ключом и доверительными интервалами. Доверительные интервалы — это границы, которые указывают на пределы погрешности для прогнозируемых значений. Хотя вы можете создать прогноз без ручного ввода каких-либо функций, вы можете щелкнуть по прогнозу, чтобы узнать, какие функции использует Excel. Экспоненциальное сглаживание доступно только в последних версиях Excel, поэтому если вы используете более старую версию программы, лучше воспользоваться одной из других функций.
Почему важна функция прогнозирования?
Вот некоторые причины, по которым функция прогнозирования может быть важной в анализе данных:
Упрощает анализ
Excel автоматизирует процесс прогнозирования и дает аналитикам возможность быстро делать прогнозы. Использование одной из этих полезных функций может сэкономить вам время, которое вы бы потратили на ручные расчеты. Это освобождает вас от необходимости тратить больше времени на интерпретацию полученных результатов или выполнение обязанностей более высокого уровня.
Предлагает индивидуальные решения
Три различных метода прогнозирования в Excel позволяют выбрать технику, которая лучше всего подходит для вашего набора данных и бизнес-целей. Например, компания, испытывающая циклические спады в доходах, может использовать экспоненциальное сглаживание, чтобы получить представление о сезонных закономерностях в своих данных. В качестве альтернативы, стартап, пытающийся показать инвесторам, что его продукт набирает популярность, может предпочесть использовать линейную регрессию, чтобы показать общую тенденцию роста продаж и предсказать возврат инвестиций (ROI).
Помогает обосновать бизнес-решения
Многие профессионалы считают крайне важным делать обоснованные предположения о будущей деловой активности, чтобы минимизировать риски и эффективно использовать свои ресурсы. Функции прогнозирования могут помочь бизнесу сформулировать свои планы на предстоящий год. Хотя поведение клиентов может быть непредсказуемым, изучение исторических данных может позволить командам оценить их потребности. Это может помочь в принятии решений о найме, инвестициях, бюджетах и партнерах.
Позволяет легко делиться данными
Использование одной из этих функций прогнозирования поможет вам легко поделиться своими выводами с коллегами. Это особенно ценно для аналитиков, которые работают вместе со специалистами из других отделов, которые могут не иметь опыта в анализе. Когда вы используете Excel для создания прогноза, вы превращаете исторические данные в удобную линейную или столбчатую диаграмму. Эти диаграммы полезны при представлении вашего анализа, поскольку большинство людей могут интерпретировать закономерности и тенденции с первого взгляда.
Обратите внимание, что ни одна из компаний, упомянутых в этой статье, не связана с Indeed.
ПоделитьсяTwitterLinkedInFacebookEmailКопировать в буфер обмена
Ключевые слова:
Источник: hr-portal.ru
Быстрое прогнозирование в Microsoft Excel
Особенно приятно, что вводить вручную эти функции и их многочисленные аргументы совершенно не требуется — в Microsoft Excel для этого есть гораздо более удобный инструмент, получивший название Лист прогноза (Forecast Sheet) . Давайте рассмотрим работу с ним на следующем примере.
В качестве исходных исторических данных возьмем с сайта AutoVercity реальную статистику по продажам автомобилей в России за 2019-2020 годы (все марки суммарно):
Представим на минуту, что сейчас конец 2020 года и мы хотим, используя эти данные, сделать помесячный прогноз продаж автомобилей на следующие полтора года. Выделим всю нашу таблицу и на вкладке Данные воспользуемся кнопкой Лист прогноза (Data — Forecast Sheet) .
В открывшемся окне зададим следующие настройки:
- Дату завершения прогноза
- Сезонность — почти никогда корректно не определяется автоматически, к сожалению, так что лучше задать её вручную. В большинстве бизнесов она годовая (т.е. «узор» колебаний похожим образом повторяется из года в год), так что установим её равной 12 месяцам.
- Вероятность, с которой мы требуем попадания будущих фактических значений в коридор доверительного интервала. Чем больше эта вероятность, тем шире интервал (т.е. более размыт прогноз). Обычно используют значения 90-95%.
- В правом нижнем углу окна можно дополнительно выбрать реакцию на пустые ячейки (их можно заполнить нулями или средним соседних значений — интерполяцией) и на дубликаты (обычно их усредняют). Однако же, по возможности, лучше заранее подготовить исходные исторические данные, чтобы таких пробелов или дублей в них не было.
После нажатия на кнопку Создать будет сформирован новый лист с прогнозной таблицей и диаграммой, которая по ней построена:
В верхней части таблицы будут идти строки с историческими данными (синяя линия), а в момент их окончания произойдет переключение на три новых столбца с прогнозом функцией ПРЕДСКАЗ.ETS и верхней и нижней границами доверительного интервала, вычисленного с помощью функции ПРЕДСКАЗ.ETS.ДОВИНТЕРВАЛ.
Ссылки по теме
- Моделирование и оценка вероятности выигрыша в лотерею
- Оптимизация доставки в Excel с помощью Поиска решения (Solver)
- Быстрое добавление новых данных в диаграмму
Источник: www.planetaexcel.ru
Создание прогноза в Excel для Windows
Если у вас есть статистические данные с зависимостью от времени, вы можете создать прогноз на их основе. При этом в Excel создается новый лист с таблицей, содержащей статистические и предсказанные значения, и диаграммой, на которой они отражены. С помощью прогноза вы можете предсказывать такие показатели, как будущий объем продаж, потребность в складских запасах или потребительские тенденции.
Создание прогноза
- На листе введите два ряда данных, которые соответствуют друг другу:
- ряд значений даты или времени для временной шкалы;
- ряд соответствующих значений показателя. Эти значения будут предсказаны для дат в будущем.
Примечание: Для временной шкалы требуются одинаковые интервалы между точками данных. Например, это могут быть месячные интервалы со значениями на первое число каждого месяца, годичные или числовые интервалы. Если на временной шкале не хватает до 30 % точек данных или есть несколько чисел с одной и той же меткой времени, это нормально. Прогноз все равно будет точным. Но для повышения точности прогноза желательно перед его созданием обобщить данные.
Совет: Если выделить ячейку в одном из рядов, Excel автоматически выделит остальные данные.
Настройка прогноза
Если вы хотите изменить дополнительные параметры прогноза, нажмите кнопку Параметры.
Сведения о каждом из вариантов можно найти в таблице ниже.
Параметры прогноза
Начало прогноза
Выберите дату, с которой должен начинаться прогноз. При выборе даты начала, которая наступает раньше, чем заканчиваются статистические данные, для построения прогноза используются только данные, предшествующие ей (это называется «ретроспективным прогнозированием»).
- Если вы начинаете прогноз перед последней точкой, вы сможете получить оценку точности прогноза, так как сможете сравнить прогнозируемый ряд с фактическими данными. Но если начать прогнозирование со слишком ранней даты, построенный прогноз может отличаться от созданного на основе всех статистических данных. При использовании всех статистических данных прогноз будет более точным.
- Если в ваших данных прослеживаются сезонные тенденции, то рекомендуется начинать прогнозирование с даты, предшествующей последней точке статистических данных.
Доверительный интервал
Установите или снимите флажок Доверительный интервал, чтобы показать или скрыть его. Доверительный интервал — это диапазон вокруг каждого предсказанного значения, в который в соответствии с прогнозом (при нормальном распределении) предположительно должны попасть 95 % точек, относящихся к будущему. Доверительный интервал помогает определить точность прогноза. Чем он меньше, тем выше достоверность прогноза для данной точки. Доверительный интервал по умолчанию определяется для 95 % точек, но это значение можно изменить с помощью стрелок вверх или вниз.
Сезонность — это число для длины (количества точек) сезонного шаблона и автоматически обнаруживается. Например, в ежегодном цикле продаж, каждый из которых представляет месяц, сезонность составляет 12. Автоматическое обнаружение можно переопрепредидить, выбрав установить вручную и выбрав число.
Примечание: Если вы хотите задать сезонность вручную, не используйте значения, которые меньше двух циклов статистических данных. При таких значениях этого параметра приложению Excel не удастся определить сезонные компоненты. Если же сезонные колебания недостаточно велики и алгоритму не удается их выявить, прогноз примет вид линейного тренда.
Диапазон временной шкалы
Здесь можно изменить диапазон, используемый для временной шкалы. Этот диапазон должен соответствовать параметру Диапазон значений.
Диапазон значений
Здесь можно изменить диапазон, используемый для рядов значений. Этот диапазон должен совпадать со значением параметра Диапазон временной шкалы.
Заполнить отсутствующие точки с помощью
Для обработки отсутствующих точек в Excel используется интерполяция, то есть отсутствующие точки будут заполнены в качестве взвешенного среднего значения соседних точек, если отсутствует менее 30 % точек. Чтобы нули в списке не были пропущены, выберите в списке пункт Нули.
Использование агрегатных дубликатов
Если данные содержат несколько значений с одной меткой времени, Excel находит их среднее. Чтобы использовать другой метод вычисления, например Медиана илиКоличество,выберите нужный способ вычисления из списка.
Включить статистические данные прогноза
Установите этот флажок, если хотите поместить на новом листе дополнительную статистическую информацию о прогнозе. При этом добавляется таблица статистики, созданная с помощью прогноза. Ets. Функция СТАТ и показатели, такие как коэффициенты сглаживания («Альфа», «Бета», «Гамма») и метрики ошибок (MASE, SMAPE, MAE, RMSE).
Формулы, используемые при прогнозировании
При использовании формулы для создания прогноза возвращаются таблица со статистическими и предсказанными данными и диаграмма. Прогноз предсказывает будущие значения на основе имеющихся данных, зависящих от времени, и алгоритма экспоненциального сглаживания (ETS) версии AAA.
Таблицы могут содержать следующие столбцы, три из которых являются вычисляемыми:
- столбец статистических значений времени (ваш ряд данных, содержащий значения времени);
- столбец статистических значений (ряд данных, содержащий соответствующие значения);
- столбец прогнозируемых значений (вычисленных с помощью функции ПРЕДСКАЗ.ЕTS);
- два столбца, представляющие доверительный интервал (вычисленные с помощью функции ПРЕДСКАЗ.ЕTS.ДОВИНТЕРВАЛ). Эти столбцы отображаются только при проверке доверительный интервал в разделе Параметры.
Скачивание образца книги
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Источник: support.microsoft.com