Лабораторная работа Прогнозирование событий с использованием MS Excel

ИНСТИТУТ ГОСУДАРСТВЕННОГО И МУНИЦИПАЛЬНОГО УПРАВЛЕНИЯ

КАФЕДРА ГОСУДАРСТВЕННОГО И МУНИЦИПАЛЬНОГО УПРАВЛЕНИЯ

Предмет: информационно-аналитические системы

ЛАБОРАТОРНАЯ РАБОТА

Прогнозирование событий с использованием MS EXCEL

№ 1

Продолжительность работы 2часа.

Составил лабораторную работу доцент кафедры, к. т.н.

2010 год

Прогнозирование событий

Цель лабораторной работы: Освоить способы прогнозирования событий методом построения линии тренда

1. Задание для лабораторной работы:

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

2. Порядок выполнения лабораторной работы

1.  Получите у преподавателя индивидуальное задание для выполнения работы.

2.  Изучите разделы методического указания.

3.  Постройте точечную диаграмму для заданного ряда данных и добавьте линию тренда, характеризуемую линейным и нелинейным уравнениями (на отдельных диаграммах). Интерпретируйте полученные результаты. Определите прогнозируемые значения для нескольких точек независимой переменной. Сравните прогнозируемые данные.

3. Методические указания

3.1 Введение

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

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

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

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

Мера связи двух параметров определяется коэффициентом корреляции. Для его расчета имеется соответствующая методика, а в MS Excel имеется соответствующая функция. Коэффициент корреляции принимает значения в интервале -1<R<1. Если коэффициент корреляции близкий к -1 или 1, то это говорит о том, что связь переменных сильная. Если R>0, то связь между переменными положительная (прямая). Иначе говоря, можно утверждать, что оба параметра (с точностью до случайных погрешностей) одновременно возрастают или убывают. При R<0 связь отрицательная (обратная) - с возрастанием одной величины другая имеет устойчивую тенденцию к убыванию.

Регрессионный анализ представляет собой следующий этап статистического анализа и позволяет предсказать значения случайной величины на основании значений одной или нескольких независимых случайных величин. Поскольку здесь фигурируют взаимосвязи величин, то логично, что эти связи хорошо описываются аналитическими уравнениями и графически их можно отобразить в виде линий. Эти уравнения в регрессионном анализе называются уравнениями регрессии, а линии – линиями регрессии. Линия регрессии переменной Y, зависящей от независимой переменной X, является статистическим построением, которое представляет линию наибольшего "соответствия" данным. Для нахождения оптимального прохождения линии на графикеГРАФИК
-1) расписание, определяющее последовательность выполнения действий, протекания событий во време...  регрессионного анализа в MS Excel используется методМЕТОД
- способ исследования явлений природы и общественной жизни, а в узком смысле - прием, способ или...  наименьших квадратов1.

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

Наиболее важным параметром регрессионного анализа является коэффициент регрессии (коэффициент детерминации) - R2. Для линейной зависимости он равен квадрату коэффициента корреляции. Этот коэффициент дает количественную оценку меры анализируемой связи и изменяется от 0 до 1. Коэффициент детерминации показывает, насколько точно найденная функция регрессии описывает связь между исходными значениями факторов X и Y. Чем ближе R2 к 1, тем в большей степени уравнение регрессии объясняет изучаемый фактор.

Для проведения статистического анализа разработано ряд программных продуктов, среди которых, например, широко известны программные пакеты Deductor, Statistica и Stadia. Но на практике они, к сожалению, не всегда доступны рядовому пользователю, а в то же время многие из задач статистического анализа можно достаточно успешно и просто решать, используя широко известный и распространённый MS Excel.

В MS Excel могут использовать следующие формы уравнений для описания взаимодействия параметров (факторов) исследуемого процесса:

Линейный - уравнение имеет вид: Y = a + bX;

логарифмический - Y = a + Ln(X);

степенной (показательный) - Y = a * Xb;

полиноминальный - Y = a + b1 X + b2 X2 + …+ bn Xn;

экспоненциальный - Y = ebx

В данной лабораторной работе проведем исследование закономерностей взаимодействия параметров используя линию тренда с аппроксимирующим её уравнением.

Для целей прогнозирования используем следующую технологию:

1.  Формируется массив статистических данных исследуемых параметров по определенным интервалам.

_____________________________________________________________________________________________

1 Метод наименьших квадратов - статистический метод определения параметров совокупности путем минимизации суммы квадратов (иногда средней суммы квадратов) отклонений между фактическими и расчетными данными.

å (Yi—Y(Xі))2=min.

Yi - действительно наблюдаемые значения,

Y(Xі) - значения, которые получаются из уравнения регрессии.

2. Дисперсия - мера рассеивания (отклонения от среднего) - средний квадрат отклонений индивидуальных значений признака от его средней величины. Дисперсия фактических значений результативного признака от вычисленных по уравнению определяется как

s 2 = å (Yi—Y(Xі))2/n, где: Yi - действительно наблюдаемые значения,

Y(Xі) - значения из уравнения регрессии, n-количество наблюдений.

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

3.2 Прогнозирование с использованием

линии тренда

Рассмотрим технологию проведения регрессионного анализа с помощью MS Ехсеl на конкретных примерах.

Для получения прогноза наиболее наглядный способ исследования связи между двумя переменными базируется на использовании линии тренда. В качестве примера проведем анализ связи стоимости и площади объектов недвижимости. Исходные данные приведены в таблице 1.

Таблица 1

Площадь, м2	Цена, у. е.
52	26
66	31
69	37
74	34
78	39
82	38
88	39
92	31
96	37
101	38
104	43
106	44

Первым шагом является построение точечной диаграммы. Здесь и в дальнейшей работе принимается во внимание, что в MS Excel зависимую переменную называют Y-переменной, а независимую переменную называют Х-переменной.

Введите данные таблицы 1 на листе MS Excel. Выделите область данных без меток (заголовков), войдите в пункт меню «Вставка», затем «Диаграмма». Выберите – «Точечная диаграмма». Далее можно уточнить диапазон исходных данных для диаграммы и в пункте меню «Ряд» можно указать распределение столбцов (строк) по осям ординат. Затем укажите название диаграммы, двух её осей, место расположения диаграммы и легенду (название). При желании имеется возможность установить подписи для каждой точки диаграммы.

Следующий шаг - добавление линии тренда к точечной диаграмме и форматирование результатов:

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

2.  В появившемся окне «Линия тренда» выберите тип линии, который будет соответствовать точечной диаграмме.

Для прогнозирования данных в нашем примере можем использовать различные типы линий тренда.

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

Линейный тренд. После построения точечной диаграммы (рис. 2) можно предположить, что линия тренда может быть линейная. Исследуем этот тип.

В окне «Линия тренда» выделите «Линейная». Щелкните по вкладке «Параметры» и включите опции: «Показывать уравнение на диаграмме» и «Поместить на диаграмму величину достоверности аппроксимации (R^2)». Убедитесь, что пункт «Пересечение кривой с осью Y в точке:» не отмечен. Нажмите OK. Результат представлен на рис. 1.1.

Имеется ряд возможностей редактирования диаграммы:

- изменить шрифт текста и получить большее количество десятичных знаков в формуле (дважды щелкните по области, где расположено уравнение, и в окне «Формат подписей данных» выполните необходимые действия).

- пере­местить и ввести дополнительный текст в область, где расположено уравнение (выделите эту область).

- установить параметры шкал осей, их вид и шрифт цифр (щелкните по оси графика, затем щелкните правой кнопкой на маркере и воспользуйтесь пунктами контекстного меню).

Рис.2

Интерпретация полученных результатов

Уравнение аппроксимации Y= 0,2092х + 18,67 можно переписать в виде: Предсказанная цена = 18,67 + 0,2092* Площадь.

Величина смещения по Y или постоянного члена в уравнении равна 18,67 и измеряется в тех же единицах, что и переменная Y. Величина 0,2092 является мерой наклона линии регрессии. Она показывает среднее изменение переменной Y при единичном изменении переменной X. Одним из самых распространенных способов ответить на вопрос «Насколько хорошо приближение» является исследование значения коэффициента детерминации (R2). Здесь значение R2 равно 0,679 и показывает, что примерно 68% колебаний стои­мости от жилой площади может быть выражено линейной моделью. Возможно, остальные 32% колебаний могут быть выражены через другие параметры объектов в регрессионной модели с многими параметрами.

Для определения величины прогнозируемой цены «вперед» или «назад» выполните следующее.

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

Далее рассмотрим нелинейный тип линии тренда.

По изложенной выше методике постройте точечную диаграмму по данным таблицы 1. Затем при добавлении линии тренда в окне «Линия тренда» выберите, допустим, тип - «Логарифмическая». При этом следует иметь в виду, что свойства алгоритма предписывают, чтобы значения независимой переменной были положительными. Если же среди значений X имеются нулевые или отрицательные значения, то при добавлении линии тренда пиктограмма «Логарифмическая» на вкладке «Линия тренда» будет недоступна. Чтобы обойти это ограничение, можно добавить какое-либо число ко всем X.

Результаты построения точечной диаграммы и добавления логарифмиче­ской линии тренда приведены на рис. 3.

Рис. 3

В данном примере значение R2 равно 0.7019, это лучше результатов линейного моделирования.

Далее используйте следующие формы линий тренда для описания взаимодействия параметров: степенной (показательный), полиноминальный и экспоненциальный (рис.1).