Лабораторная работа. Прогнозирование значений выходных параметров средствами MS Excel

Прогнозирование значений выходных параметров средствами MS Excel

Цель работы: Ознакомиться с возможностями прогнозирования значений выходных параметров в пакете MS Excel.

Очень часто во многих сферах деятельности приходится сталкиваться с необходимостью прогнозирования тех или иных данных. Например, в торговых фирмах необходимо прогнозировать продажи товаров (выходной параметр) с целью того, чтобы знать, сколько нужно заказать или произвести того или иного товара, избежав убытков.

Крайне редки те случаи, когда известна явная зависимость между выходными параметрами и входными. Поэтому для прогнозирования необходимо сначала найти скрытую закономерность изменения выходного параметра от входных параметров. Одним из способов отыскания этой закономерности является построение регрессионных моделей.

Регрессионная модель – это уравнение зависимости между выходным параметром, который является случайной величиной и входным (ми) параметрами – неслучайными величинами

Вид уравнения регрессии выбирается из особенностей изучаемой системы случайных величин. Наиболее распространенны следующие виды регрессий:

· Одномерная линейная регрессия, зависимость Y от X имеет вид

Y = aX + b, (1)

где a – коэффициент при X, а b – постоянная. Коэффициент a имеет тот же знак, что и коэффициент корреляции.

· Многомерная линейная регрессия, имеющая вид

Y = a₁X₁ + a₂X₂ +... + a₀, (2)

где зависимое значение Y является функцией независимых значений X_i. Значения a_i – это коэффициенты, соответствующие каждой независимой переменной X_i, a₀ – это постоянная.

· Одномерная нелинейная регрессия

, (3)

где b – коэффициент при X, с – коэффициент при X², а – постоянная.

· Степенная регрессия с уравнением вида

, (4)

где a – коэффициент при X, а b – степенной коэффициент.

· Экспоненциальная регрессия с уравнением вида

, (5)

где a – коэффициент при экспоненте, а b – коэффициент при X.

При выборе необходимо, чтобы вид функции был по возможности простым, но в то же время достаточно хорошо выражал реальную зависимость. Наиболее простым видом регрессионной модели является линейная модель, которая и будет рассматриваться в данной работе.

Получить линейное регрессионное уравнение в MS Excel наиболее просто можно двумя способами:

1. Построение на диаграмме линии тренда (идеально подходит для одномерных регрессионных моделей)

2. Использования функции ЛИНЕЙН (эта функция используется для получения как одномерной, так и многомерной регрессионной модели).

Для использования первого метода необходимо построить точечный график, на котором отображается величина выходного параметра в зависимости от входного. А затем можно воспользоваться свойством диаграмм создавать линии тренда. Для этого необходимо выделить ряд данных на диаграмме и выбрать в меню Диаграмма команду Добавить линию тренда. Откроется диалоговое окно «Линия тренда» (рис. 1), позволяющее выбрать тип и указать параметры добавляемой линии.

Рис. 1. Диалоговое окно «Линия тренда»

Excel предлагает следующие типы линий для сглаживания и аппроксимации: Линейная, Степенная, Логарифмическая, Экспоненциальная, Полиномиальная и Скользящее среднее. При полиномиальнойаппроксимациинеобходимо указать степень полинома (от 2 до 6). При сглаживании скользящим средним необходимо указать количество точек, по которым будет вычисляться среднее.

Рассмотрим теперь параметры добавляемой линии тренда (рис. 2).

Рис. 2. Диалоговое окно «Параметры линии тренда»

Название аппроксимирующей (сглаженной кривой) –устанавливается один из двух параметров: автоматическое – Excel сам определяет название линии тренда с учетом выбранного типа и ряда данных, к которому она относится. Чтобы задать нестандартное название линии необходимо установить положение переключателя в положение другое, а затем ввести название;

Прогноз – чтобы определить, как будет продолжена линия тренда. Этот параметр недоступен для линий тренда со скользящим средним;

Пересечение кривой с осью Y в точке – задается точка пересечения кривой с осью Y – в данное поле необходимо ввести значение. Этот параметр неприменим к логарифмическим, степенным и линиям со скользящим средним;

Показывать уравнение на диаграмме – необходимо активировать параметр, если нужно отобразить уравнение тренда на диаграмме;

Поместить на диаграмму величину достоверности аппроксимации – на диаграмме будет указан коэффициент детерминированности.

После нажатия кнопки ОК, на диаграмме будет отображена выбранная линия тренда и указанные параметры.

Коэффициент детерминированности – один из параметров, который показывает, насколько хорошо полученное регрессионное уравнение описывает зависимость между выходным и входным параметрами. Для линейной регрессии коэффициент детерминированности совпадает с квадратом коэффициента корреляции.

Рассмотрим второй способ получения регрессионного уравнения при помощи функции ЛИНЕЙН. Функция ЛИНЕЙН находится в подгруппе статистических функций. Функция ЛИНЕЙН имеет следующий синтаксис:

ЛИНЕЙН (у-массив; x-массив; конст; статистика),

где у-массив является ссылкой на массив данных Y;

х-массив является ссылкой на один или несколько массивов данных X;

конст – это логическое значение, определяющее нулевой коэффициент в уравнении;

статистика – это логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии.

Если член конст пропущен или ИСТИНА, нулевой коэффициент вычисляется обычным способом. Если член конст равен ЛОЖЬ, константа сдвига полагается равной 0. Если член статистика равен ИСТИНА, вместе с коэффициентами уравнения регрессии возвращается таблица из восьми или более статистических значений.

Табл. 1.

Регрессионная статистика

В приведенной ниже таблице 2 показано, в каком порядке возвращается дополнительная регрессионная статистика.

Табл. 2.

Порядок значений регрессионной статистики

После получения регрессионной модели можно прогнозировать значение выходного параметра, подставляя значения входных параметров в уравнение. Можно также использовать статистическую функцию ТЕНДЕНЦИЯ, которая имеет следующий синтаксис:

ТЕНДЕНЦИЯ (известные значения y; известные значения x; новые значения x; конст),

где:

известные значения y – имеющиеся значения выходного параметра

известные значения x – имеющиеся значения входного параметра либо массив значений входных параметров

новые значения x – значения входного параметра или массив значений входных параметров, для которых нужно получить значение выходного параметра

конст – если член конст пропущен или ИСТИНА, функция ТЕНДЕНЦИЯ выполняет прогноз выходного параметра на основе линейного уравнения вида Y = aX + b. Если член конст равен ЛОЖЬ, то используется модель вида Y = aX.

Варианты заданий

Вариант №1

Менеджер фирмы, торгующей музыкальными инструментами, установил, что ежемесячный спрос на турецкие барабаны зависит от числа показов на телеэкране рок-групп в предыдущем месяце. Менеджер составил следующую таблицу.

1. Построить точечный график, на котором показан спрос на барабаны и число показов рок групп.

2. Получить линейное регрессионное уравнение зависимости между числом показов рок-групп и спросом на барабаны.

3. Спрогнозировать спрос на барабаны при условии, что в прошлом месяце было показано 10 выступлений рок-групп.

4. Чему равен коэффициент детерминированности для регрессионной модели

Вариант №2

В нижеследующей таблице приведены величины спроса на электрические генераторы за период 1991-1997 гг.

1. Построить точечный график, на котором показана продажа генераторов в определенный период времени.

2. Получить линейное регрессионное уравнение зависимости между периодом времени и продажей генераторов.

3. Спрогнозировать продажу генераторов в 1998 году.

4. Чему равен коэффициент детерминированности для регрессионной модели

Вариант №3

Менеджер фирмы – оптового торговца углем – установил, что объем продаж угля связан с индексом погоды, ежегодно определяемым Бюро погоды. Чем холоднее погода (соответственно – выше индекс погоды), тем выше объем продаж угля. Менеджер полагает, что на основании регрессионного уравнения, устанавливающего взаимосвязь спроса на уголь и индекса погоды, можно получить хороший прогноз спроса на уголь.

На основании данных указанных в таблице

1. Построить точечный график, на котором показана продажа угля в зависимости от индекса погоды

2. Получить линейное регрессионное уравнение зависимости продажи угля от индекса погоды

3. Какова будет продажа угля, если индекс погоды составит 7?

4. Чему равен коэффициент детерминированности для регрессионной модели

Вариант №4

Число пассажиров городского транспорта (автобус и метро) возрастает в Киеве с увеличением числа приезжих в летние месяцы. В таблице показаны данные, связывающие число туристов и пассажиров в последние 12 лет.

1. Построить точечный график, на котором показано число пассажиров общественного транспорта в зависимости от числа приезжих.

2. Построить регрессионную зависимость числа пассажиров от числа приезжих.

3. Сколько можно ожидать пассажиров (млн.), если город посетят 10 млн. приезжих?

4. Сколько будет пассажиров, если туристов не будет совсем?

5. Каков коэффициент детерминированности для этой модели?

Вариант №5

Доктор М. – психолог, занимающийся пациентами, испытывающими страх перед необходимостью покидать свой дом и выходить на улицу. В следующей таблице показано количество новых пациентов, обратившихся к нему за помощью в каждом из последних 10 лет, а также приведен индекс преступности за эти годы.

1. Построить точечный график, на котором показано число пациентов в зависимости от года наблюдения

2. Получить линейную регрессионную модель зависимости числа пациентов от года наблюдения. (Насколько хорошо эта модель отражает данные?)

3. Сколько пациентов будет в году 11?

4. Сколько пациентов будет в году 13?

5. Построить точечный график, на котором показано число пациентов в зависимости от индекса преступности

6. Получить линейную регрессионную модель зависимости числа пациентов от индекса преступности

7. Сколько пациентов будет в году 11, если индекс преступности возрастет до 131, 2?

8. Сколько пациентов будет в году 11, если индекс преступности снизится до 90, 6?

Вариант №6

Компания " Билд" ремонтирует старые дома в Харькове. Со временем компания обнаружила, что объем ремонтных работ зависит от уровня доходов в городе. Следующая таблица содержит данные о доходе компании и денежных доходах наемных рабочих в Харькове за период 1997-2002гг.

На основании вышеприведенной таблицы

1. Построить точечный график, на котором показаны доходы компании в зависимости от дохода рабочих

2. Получить линейную регрессионную модель зависимости доходов компании от доходов рабочих

3. Каковы будут доходы компании, если доходы рабочих за 2003 год составили 50 млн. грн.

4. Чему равен коэффициент детерминированности для регрессионной модели

Задание для вариантов 7-12.

В приведенных выше таблицах находятся значения входных параметров X1 и X2 и выходного параметра Y.

1. Проверить существует ли зависимость между параметрами X1 и X2. (Найти при помощи функции КОРРЕЛ из подгруппы статистических функций коэффициент корреляции)

2. Если между X1 и X2 существует сильная зависимость (коэффициент корреляции близок к 1) один из коэффициентов отбросить.

3. Получит линейное регрессионное уравнение зависимости между входными и выходным параметрами.

4. Качество аппроксимации оценить по значению коэффициента детерминированности

5. Спрогнозировать значение выходного параметра Y (указан знак вопроса)