Прогнозирование с помощью 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 выведет сообщение.

Выходной интервал. Введите ссылку для верхней левой ячейки интервала, в который хотите вывести выходную таблицу. Если выделен флажок «Стандартные ошибки», Microsoft Excel сгенерирует двухколоночную таблицу со значениями стандартных ошибок в правом столбце. Если недостаточно прошлых значений для построения прогноза или для вычисления стандартной ошибки, Microsoft Excel возвратит ошибочное значение «#N/A».

Фактор затухания. Введите фактор затухания, который будет использоваться в качестве константы экспоненциального сглаживания. Значение по умолчанию для фактора затухания равно 0.3.

Стандартные ошибки. Выделите флажок «Стандартные ошибки», если хотите включить значения стандартных ошибок в выходную таблицу. Если выделен флажок «Стандартные ошибки», Microsoft Excel сгенерирует двухколоночную таблицу со значениями стандартных ошибок в правом столбце. Если флажок «Стандартные ошибки» не выделен, Microsoft Excel сгенерирует одноколоночную таблицу без значений стандартных ошибок.

Вывод диаграммы. Выделите флажок «Вывод диаграммы», чтобы автоматически вместе с выходной таблицей была сгенерирована диаграмма для фактических и прогнозируемых значении. Диаграмма будет внедрена в тот же лист, что и выходная таблица. Если этот флажок не выделен, диаграмма не генерируется.

Метки. Если первая строка и первый столбец входного интервала содержат метки, выделите флажок «Метки». Если входной интервал не содержит меток, очистите флажок «Метки». Microsoft Excel сгенерирует подходящие метки данных для выходной таблицы.

Линию тренда можно отформатировать, задать её цвет, стиль, толщину. Если при этом доступна «Метка линии тренда», то можно и задавать числовой формат,цвет шрифта и прочие характеристики этой метки.