Как удалить график но оставить линию тренда. Инструменты прогнозирования в Microsoft Excel
Линия тренда в Excel. Процесс построения
Линия тренда — это один из основных инструментов анализа данных
Чтобы сформировать линию тренда , необхдимо совершить три этапа, а именно:
1. Создать таблицу;
2.
3. Выбрать тип линии тренда.
После сбора всей необходимой информации, можно приступить непосредственно к выполнению шагов на пути к получению конечного результата.
Следующее действие построение самой линии тренда . Итак, для этого необходимо вновь выделить график и выбрать вкладку «Макет» на ленте задач. Следом в данном меню нужно нажать на кнопку «Линия тренда » и выбрать «линейное приближение» или же «экспоненциальное приближение».
Линейная аппроксимация . По характеру данная линия прямая, и стандартно применяется в элементарных случаях, когда функция увеличивается или же уменьшается в приблизительном постоянстве.
Логарифмическая аппроксимация. Если величина сначала верно и быстро растет или же наоборот — убывает, а вот затем, спустя значения, стабилизируется, то данная линия тренда подойдет как нельзя кстати.
Полиномиальная аппроксимация . Переменное возрастание и убывание – вот характеристики, что свойственны данной линии. Причем, степень самих полиномов (многочленов) определяется количеством максимумов и минимумом.
Степенная аппроксимация . Характеризует монотонное возрастание и убывание величины, но применение ее невозможно, если данные имеют отрицательные и нулевые значения.
Скользящее среднее . Используется чтобы наглядно показать прямую зависимость одного от другого, путем сглаживания всех точек колебания. Это достигается путем выделения среднего значения между двумя соседними точками. Таким образом, график усредняется, а количество точек сокращается до значения, что было выбрано в меню «Точки» пользователем.
Как используется? Д ля прогнозирования экономический вариантов используется именно полиноминальная линия, степень многочлена которой определяется на основе нескольких принципов: максимизации коэффициента детерминации, а также экономической динамики показателя в период, за который требуется прогноз.
Следуя всем этапам формирования и, разобравшись в особенностях, можно построить всего первичную линию тренда , которая лишь отдаленно соответствует реальным прогнозам. Но вот после настройки параметров можно уже говорить о более реальной картине прогноза.
Линия тренда в Excel. Настройка параметро в функциональной линии
Нажав на кнопку «Линия тренда », выбираем необходимое меню под названием «Дополнительные параметры». В появившемся окне следует нажать на «Формат линии тренда », а после поставить и отметку напротив значения «поместить на диаграмму величину достоверности аппроксимации R^2». После этого закрываем меню, нажав на соответственную кнопку. На самой же диаграмме появляется коэффициент R^2= 0,6442.
После этого отменяем вводимые изменения. Выделив график и нажав на вкладку «Макет», следом нажимаем на «Линию тренда » и наживаем на «Нет». Следом, перейдя в функцию «Формат линии тренда », нажимаем на полиноминальную линию и пытаемся добиться значения R^2= 0,8321, меняя степень.
Чтобы просмотреть формулы или составить другие, отличные от стандартных вариации прогнозов, достаточно не бояться экспериментировать со значениями, а особенно – с полиномами. Таким образом, используя лишь одну программу Excel, можно создать достаточно точный прогноз исходя из вводимых данных.
Глядя на любой набор данных распределенных во времени (динамический ряд), мы можем визуально определить падения и подъемы показателей, которые он содержит. Закономерность подъемов и падений называется трендом, который может говорить о том, увеличиваются или уменьшаются наши данные.
Пожалуй, цикл статей о прогнозировании я начну с самого простого — построении функции тренда. Для примера возьмем данные о продажах и построим модель, которая опишет зависимость продаж от времени.
Как удалить график но оставить линию тренда. Инструменты прогнозирования в Microsoft Excel
График вордстата показывает — спрос на наш продукт падает. Значит, чтобы планировать повышение продаж нам необходимо предпринимать меры — в обычных условиях клиенты будут уходить. Чтобы удержать поток клиентов, мы будем планировать маркетинговые акции, снижать стоимость продукции и отбивать аудиторию у конкурентов
Прогнозирование с помощью Microsoft Excel
Для прогнозирования с Microsoft Excel необходимо ввести следующие основные понятия.
Линия тренда – графическое представление трендов в рядах данных. Линии тренда могут быть добавлены к ряду данных плоской диаграммы, линейчатой, гистограмме, графику, точечному графику. Линию тренда можно отформатировать.
Метка линии тренда – текст для линии тренда, который формируется электронными таблицами и может содержать уравнение регрессии и (или) среднее квадратическое отклонение. Метку линии тренда можно форматировать и перемещать, но нельзя изменить по размеру.
Регрессионный анализ (экстраполяция) – форма статистического анализа, используемая при прогнозировании. Оценивается отношение между переменными, в результате чего одна переменная может быть предсказана через другие.
Среднее квадратическое отклонение – вычисляемое значение, которое в регрессионном анализе характеризует достоверность линии тренда для прогнозирования. Среднее квадратическое отклонение помогает определить наиболее подходящую линию тренда. Близость его к нулю означает низкую степень соответствия, близость к единице – высокую, вполне достоверную линию тренда.
MS EXCEL содержит ряд родственных статистических функций для экстраполяционного анализа. К ним относят функции «ТЕНДЕНЦИЯ», «РОСТ», «ЛИНЕЙН», «ЛГРФПРИБЛ». Подробнее рассмотрим первую из них.
ТЕНДЕНЦИЯ –возвращает значение в соответствии с линейным трендом, аппроксимирует прямой линией (по методу наименьших квадратов) массивы «известные значения У» и «известные значения X», возвращает значения У в соответствии с этой прямой для заданного массива «новые значения X». Синтаксис данной функции:
ТЕНДЕНЦИЯ(известные значения У, известные значения X, новые значения X, конст) где:
• известные значения у – это множество значений у, которые уже известны для соотношения у = мх + + в;
• известные значения х – это необязательное множество значений х, которые уже известны для соотношения у = м х + в;
• новые значения х – это новые значения х, для которых тренд возвращает соответствующие значения у;
• конст – это логическое значение, которое указывает, требуется ли, чтобы константа «в» была равна нулю.
РОСТ – подобна «ТЕНДЕНЦИЯ» и «ЛИНЕЙН», но аппроксимирует данные экспоненциальной прямой.
ЛИНЕЙН – вычисляет прямую, но возвращает параметры прямой, а не массив значений У.
ЛГРФПРИБЛ – подобна «ТЕНДЕНЦИЯ» и «ЛИНЕЙН», но аппроксимирует данные экспоненциальной прямой.
Получить информацию о том, как MS EXCEL аппроксимирует данные прямой можно с помощью справки: [F1] _ «Использование Microsoft Excel» _ «Решение задач путём анализа данных» _ «Статистический анализ данных» _ «Регрессия» _ «ЛИНЕЙН».
Команда «ЛИНИЯ ТРЕНДА» (меню «ВСТАВКА») содержит две вкладки:
вкладку «ТИП» и вкладку «ПАРАМЕТРЫ». Вкладка «ТИП» имеет функции:
• Добавление линии тренда или изменение типа линии тренда, связанного с рядом данных в диаграмме из типовой группы рядов, содержащей линейчатые и графические диаграммы, гистограммы, диаграммы с областями и точки диаграммы. Не может добавить линию тренда к объёмной диаграмме, круговой, кольцевой, диаграмме типа радар.
• Тип тренда / регрессии определяет тип линии тренда: линейный, полиномиальный (степень выражается как целое число от 2 до 6), логарифмический, экспоненциальный, степенной, скользящее среднее (указывается количество периодов, использованное для усреднения).
Вкладка «ПАРАМЕТРЫ» имеет функции модификации линии тренда:
«автоматическое». Microsoft Excel именует линию тренда, основываясь на выбранном типе и на ряде данных, с которым она ассоциирована. Например, если «линейный» тренд добавляется ко второму ряду данных в группе типа диаграмм, он выводит имя «Линейный (Ряд 2)».
«пользовательское». Позволяет ввести имя длиной до 256 символов (в версии 5.0).
• Прогноз. Доступен только для регрессий (для скользящего среднего не доступен).
«Вперёд на» –указывается количество периодов, на которое проектируется будущее в линии тренда, или от оси у.
«Назад на» –указывается количество периодов, на которое проектируется прошлое в линии тренда, или от оси у.
•. Точка, в которой линия тренда пересекает ось у. Доступен только для некоторых типов регрессии.
• Показывать уравнение на диаграмме. Выводит уравнение регрессии для линии тренда в метке линии тренда на диаграмме.
• Показывать значение R-квадрат на диаграмме. Выводит значение среднего квадрэтического отклонения для линии тренда в метке линии тренда на диаграмме.
Изменение установок линии тренда приемлемо для регрессионного анализа (экстраполяции), а не для скользящего среднего. Можно присвоить имя линии тренда или изменить тип её экстраполяции. Можно также вывести метки линии тренда, например, среднеквадратическое отклонение или уравнение экстраполяции, можно изменить У-пересечение.
Экспоненциальное сглаживание –предсказывает значение на основе прогноза для предыдущего периода, скорректированного с учетом ошибки в этом прогнозе. Использует константу сглаживания, по величине которой определяет, насколько сильно влияние на прогнозы ошибок в предыдущем прогнозе.
Экспоненциальное сглаживание можно использовать для прогноза сбыта или других тенденций. Другой метод, регрессия, также предсказывает значения на основе связей в существующих данных.
Входной интервал. Введите ссылку на интервал данных рабочего листа, подлежащих анализу. Входной интервал должен состоять из одного столбца или одной строки, содержащих четыре или более ячеек данных. Если данные во входном интервале не числовые, Microsoft Excel выведет сообщение.
Фактор затухания. Введите фактор затухания, который будет использоваться в качестве константы экспоненциального сглаживания. Значение по умолчанию для фактора затухания равно 0.3.
Вывод диаграммы. Выделите флажок «Вывод диаграммы», чтобы автоматически вместе с выходной таблицей была сгенерирована диаграмма для фактических и прогнозируемых значении. Диаграмма будет внедрена в тот же лист, что и выходная таблица. Если этот флажок не выделен, диаграмма не генерируется.
Метки. Если первая строка и первый столбец входного интервала содержат метки, выделите флажок «Метки». Если входной интервал не содержит меток, очистите флажок «Метки». Microsoft Excel сгенерирует подходящие метки данных для выходной таблицы.
Линию тренда можно отформатировать, задать её цвет, стиль, толщину. Если при этом доступна «Метка линии тренда», то можно и задавать числовой формат,цвет шрифта и прочие характеристики этой метки.
Алгоритм прогнозирования объёма продаж в MS Excel
Теперь мы решили, что в процессе есть и шум, и тренд (это определяется
обработкой даже в Экселе). Тогда строим линию тренда (при этом шум
автоматически фильтруется) и ее продолжение за текущий момент —
наилучший прогноз.
Прогнозирование методом среднего индекса и среднего прироста
Этот простой метод прогнозирования применяется для краткосрочного прогнозирования на 1-3 периода, при устойчивой динамике (рост или снижение) показателя и небольших колебаниях показателя в прошлом периоде относительно прямой, соединяющей первую и последнюю точки фактических данных.
Недостаток метода – учитываются только первое и последнее значения показателя за прошлый период, поэтому за базу прогноза обычно берут 3-5 последних значений показателя за прошлый период.
Для прогнозирования используется средний цепной Ic и средний цепной прирост Δc показателя за прошлый период от t = 1 до t = n.
Средний индекс динамики (Ic) определяется по формулам:
где Δt – изменение показателя на t-м интервале, для t = 1 значение Δ1 не определено.
Прогнозные значения показателя определяются по формулам:
где YIn+k – прогноз по среднему индексу, YΔn+k – прогноз по среднему приросту, Yсn+k – средний прогноз.
Если YΔn+k < 0, то прогнозными значениями будут YΔn+k = 0.
Точечным прогнозом будут значения Yсn+k. Интервальный прогноз и доверительная вероятность определяются экспертно.
Пример 1. По отчетным данным показателя дать прогноз на 2 периода методом среднего индекса и среднего прироста по данным таблицы 1.
Решение. Динамика показателя – устойчивый рост с малыми колебаниями (3) относительно линейного тренда (δ = 1,06%), значит точность прогноза высокая.
Применяя формулы (4-6), получаем: средний индекс Ic = (102,2 / 94,3)1/3 = 1,03, средний прирост Δс = (102,2 – 94,3) / 3 = 2,63. Прогнозные значения представлены в таблице 2.
Прогноз плана продаж: что это такое, задачи и формулы расчета — OKOCRM
- Сезонные (квартальные) колебания.
- Спекулятивные колебания цен на товарных биржах.
- Экономические циклы деловой активности.
- Случайные колебания, вызванные неучтенными факторами.
Глядя на любой набор данных распределенных во времени (динамический ряд), мы можем визуально определить падения и подъемы показателей, которые он содержит. Закономерность подъемов и падений называется трендом, который может говорить о том, увеличиваются или уменьшаются наши данные.
Алгоритм прогнозирования объёма продаж в MS Excel
Аддитивную модель прогнозирования можно представить в виде формулы:
где:
F — прогнозируемое значение;
Т — тренд;
S — сезонная компонента;
Е — ошибка прогноза.
Применение мультипликативных моделей обусловлено тем, что в некоторых временных рядах значение сезонной компоненты представляет собой определенную долю трендового значения. Эти модели можно представить формулой:
На практике отличить аддитивную модель от мультипликативной можно по величине сезонной вариации. Аддитивной модели присуща практически постоянная сезонная вариация, тогда как у мультипликативной она возрастает или убывает, графически это выражается в изменении амплитуды колебания сезонного фактора, как это показано на рисунке 1.
Рис. 1. Аддитивная и мультипликативные модели прогнозирования
Алгоритм построения прогнозной модели
Для прогнозирования объема продаж, имеющего сезонный характер, предлагается следующий алгоритм построения прогнозной модели:
1. Определяется тренд, наилучшим образом аппроксимирующий фактические данные. Существенным моментом при этом является предложение использовать полиномиальный тренд, что позволяет сократить ошибку прогнозной модели.
2 . Вычитая из фактических значений объёмов продаж значения тренда, определяют величины сезонной компоненты и корректируют таким образом, чтобы их сумма была равна нулю.
3. Рассчитываются ошибки модели как разности между фактическими значениями и значениями модели.
где:
F — прогнозируемое значение;
Т — тренд;
S — сезонная компонента;
Е — ошибка модели.
где:
F пр t — прогнозное значение объёма продаж;
F ф t-1 — фактическое значение объёма продаж в предыдущем году;
F м t — значение модели;
а — константа сглаживания
- для составления прогноза необходимо точно знать величину сезона. Исследования показывают, что множество продуктов имеют сезонный характер, величина сезона при этом может быть различной и колебаться от одной недели до десяти лет и более;
- применение полиномиального тренда вместо линейного позволяет значительно сократить ошибку модели;
- при наличии достаточного количества данных метод даёт хорошую аппроксимацию и может быть эффективно использован при прогнозировании объема продаж в инвестиционном проектировании.
Применение алгоритма рассмотрим на следующем примере.
Исходные данные: объёмы реализации продукции за два сезона. В качестве исходной информации для прогнозирования была использована информация об объёмах сбыта мороженого “Пломбир” одной из фирм в Нижнем Новгороде. Данная статистика характеризуется тем, что значения объёма продаж имеют выраженный сезонный характер с возрастающим трендом. Исходная информация представлена в табл. 1.
Задача: составить прогноз продаж продукции на следующий год по месяцам.
Реализуем алгоритм построения прогнозной модели, описанный выше. Решение данной задачи рекомендуется осуществлять в среде MS Excel, что позволит существенно сократить количество расчётов и время построения модели.
1. Определяем тренд , наилучшим образом аппроксимирующий фактические данные. Для этого рекомендуется использовать полиномиальный тренд, что позволяет сократить ошибку прогнозной модели).
Рис. 2. Сравнительный анализ полиномиального и линейного тренда
На рисунке показано, что полиномиальный тренд аппроксимирует фактические данные гораздо лучше, чем предлагаемый обычно в литературе линейный. Коэффициент детерминации полиномиального тренда (0,7435) гораздо выше, чем линейного (4E-05). Для расчёта тренда рекомендуется использовать опцию “Линия тренда” ППП Excel.
2. Вычитая из фактических значений объёмов продаж значения тренда , определим величины сезонной компоненты , используя при этом пакет прикладных программ MS Excel (рис. 4).
Рис. 4. Расчёт значений сезонной компоненты в ППП MS Excel
Скорректируем значения сезонной компоненты таким образом, чтобы их сумма была равна нулю.
3. Рассчитываем ошибки модели как разности между фактическими значениями и значениями модели.
Находим среднеквадратическую ошибку модели (Е) по формуле:
где:
Т — трендовое значение объёма продаж;
S — сезонная компонента;
О — отклонения модели от фактических значений
Величина полученной ошибки позволяет говорить, что построенная модель хорошо аппроксимирует фактические данные, т.е. она вполне отражает экономические тенденции, определяющие объём продаж, и является предпосылкой для построения прогнозов высокого качества.
Построенная модель представлена графически на рис. 5.
5. На основе модели строим окончательный прогноз объёма продаж. Для смягчения влияния прошлых тенденций на достоверность прогнозной модели, предлагается сочетать трендовый анализ с экспоненциальным сглаживанием. Это позволит нивелировать недостаток адаптивных моделей, т.е. учесть наметившиеся новые экономические тенденции:
где:
F пр t — прогнозное значение объёма продаж;
F ф t-1 — фактическое значение объёма продаж в предыдущем году;
F м t — значение модели;
а — константа сглаживания.
Константу сглаживания рекомендуется определять методом экспертных оценок, как вероятность сохранения существующей рыночной конъюнктуры, т.е. если основные характеристики изменяются / колеблются с той же скоростью / амплитудой что и прежде, значит предпосылок к изменению рыночной конъюнктуры нет, и следовательно а ® 1, если наоборот, то а ® 0.
Таким образом, прогноз на январь третьего сезона определяется следующим образом.
Фактическое значение объёма продаж в предыдущем году (F ф t-1 ) составило 2 361 руб. Принимаем коэффициент сглаживания 0.8. Получим прогнозное значение объёма продаж:
Для учёта новых экономических тенденций рекомендуется регулярно уточнять модель на основе мониторинга фактически полученных объёмов продаж, добавляя их или заменяя ими данные статистической базы, на основе которой строится модель.
Кроме того, для повышения надёжности прогноза рекомендуется строить все возможные сценарии прогноза и рассчитывать доверительный интервал прогноза.
-
Дмитриев Михаил Николаевич, заведующий кафедрой экономики и предпринимательства Нижегородского архитектурно-строительного университета (ННГАСУ), доктор экономических наук, профессор.
Юрий
Перед таблицой №3 написано: Скорректируем значения сезонной компоненты таким образом, чтобы их сумма была равна нулю.
Юра из Киева, drvlas@gmx.net
УФ! Я не сразу разобрался, куда что посылать. Хотел обратиться ко всем, а попал к Алексею. Поэтому повторяю свое вчерашнее сообщение.
Мои слова подтверждаются и замечанием Алексея.
И еще одно. Сама матоснова прогнозирования также должна опираться на предположения о характере процесса. Поэтому «универсального» алгоритма и не существует. Где-то можно и пренебречь случайной составляющей. Но не в приведенном примере, ИМХО.
Юра из Киева, drvlas@gmx.net
Коллеги, ко мне обращаются за моим таинственным методом прогнозирования. Отвечу здесь, чтобы было доступно всем.
Нужен пример. Пусть мы предполагаем в процесс просто шум. Т.е. модель
того, что мы прогнозируем: постоянная составляющая + шум (с нулевым
средним значением). Тогда алгоритм прогноза: по всем имеющимся данным
находим среднее и считаем это прогнозом.
Теперь мы решили, что в процессе есть и шум, и тренд (это определяется
обработкой даже в Экселе). Тогда строим линию тренда (при этом шум
автоматически фильтруется) и ее продолжение за текущий момент —
наилучший прогноз.
У меня есть примерчик на Экселе. ВОт его не знаю, как выложить. Пожалуй, снова подожду — если не разочаровал вас словами — обращайтесь, пришлю табличку.
С уважением и благодарностью к заинтересовавшимся и в надежде на конструктивную критику.
Алексей, iscariot@rambler.ru
Юра, скинь файлик с текстовкой и XLS посмотреть. О прогнозировании продаж
Сергей, ruskin@navigator.lv
Юра, Вы не могли бы прислать файлик примера продаж в Экселе! Заранее благодарю!
Елена, lenao@sitsy.tpi.ru
Юра, умоляю,скинь файлик с текстовкой и XLS посмотреть. О прогнозировании продаж. Мы как ни крутили, цифры получаются с большими отклонениями.
Прогнозирование с помощью Microsoft Excel — Мегаобучалка
Особенности. Повышает объективность прогноза с учетом прямых и косвенных рыночных факторов. Требует аудита всех учитываемых показателей и их связи с реакцией на спрос. Часто требует привлечения внешних экспертов и проведения глубокой аналитики.
