Краткие теоретические сведения

Перепишите в тетрадь «Краткие теоретические сведения»

1. Выполните Задания по Инструкции (со с.3), рассмотрев сначала Типовой пример. Если нет возможности на компьютере – перепишите в тетрадь выполнение работы (как делать, правая сторона инструкции)

Запишите неясные вопросы

Сделайте скриншот выполненной работы. Сохраняйте

4. Покажите работу преподавателю (почта lagutina_6464@.mail.ru или в ВК,  не забудьте группу и ФИО)

Краткие теоретические сведения

РЕГРЕССИОННЫЙ АНАЛИЗ В EXCEL

Регрессионный анализ в Excel - показывает влияние одних значений (самостоятельных, независимых) на зависимую переменную. К примеру, как зависит количество экономически активного населения от числа предприятий, величины заработной платы и др. параметров. Или: как влияют иностранные инвестиции, цены на энергоресурсы и др. на уровень ВВП.

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

Регрессия бывает:

· линейной (у = а + bx);

· параболической (y = a + bx + cx²);

· экспоненциальной (y = a * exp(bx));

· степенной (y = a*x^b);

· гиперболической (y = b/x + a);

· логарифмической (y = b * 1n(x) + a);

· показательной (y = a * b^x).

ПРОГНОЗИРОВАНИЕ В MS EXCEL

Краткие теоретические сведения

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

Одним из самых популярных видов графического прогнозирования в Экселе является экстраполяция выполненная построением линии тренда.

Получение регрессионной модели происходит в два этапа:

- подбор вида функции;

- вычисление параметров функции.

Чаще всего выбор производится среди следующих функций:

 - линейная функция;

- квадратичная функция;

- логарифмическая функция;

- экспоненциальная функция;

- степенная функция.

Во всех этих формулах x – аргумент, y – значение функции, a, b, c – параметры функций.

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

Суть – искомая функция должна быть построена так, чтобы сумма квадратов отклонений y-координат всех экспериментальных точек от y-координат графика функции была бы минимальна.

ПОСЛЕДОВАТЕЛЬНОСТЬ ВЫПОЛНЕНИЯ РАБОТЫ

№ п/п	№ п/п	Что делать?	Как делать?
1	ПОЛУЧЕНИЕ РЕГРЕССИОННЫХ МОДЕЛЕЙ В MS EXCEL
	Типовой пример
	Задача. На 6 предприятиях была проанализирована среднемесячная заработная плата и количество уволившихся сотрудников. Необходимо определить зависимость числа уволившихся сотрудников от средней зарплаты.		Модель линейной регрессии имеет следующий вид: У = а0 + а1х1 +…+акхк. Где а – коэффициенты регрессии, х – влияющие переменные, к – число факторов. В нашем примере в качестве У выступает показатель уволившихся работников. Влияющий фактор – заработная плата (х). В Excel существуют встроенные функции, с помощью которых можно рассчитать параметры модели линейной регрессии. Но быстрее это сделает надстройка «Пакет анализа».
	1	Активируем мощный аналитический инструмент	1. 1.Нажимаем кнопку «Офис» и переходим на вкладку «Параметры Excel». «Надстройки». 2. 2.Внизу, под выпадающим списком, в поле «Управление» будет надпись «Надстройки Excel» (если ее нет, нажмите на флажок справа и выберите). И кнопка «Перейти». Жмем. 3. 3.Открывается список доступных надстроек. Выбираем «Пакет анализа» и нажимаем ОК. После активации надстройка будет доступна на вкладке «Данные».
	2.	Теперь займемся непосредственно регрессионным анализом.	1. Открываем меню инструмента «Анализ данных». Выбираем «Регрессия». Откроется меню для выбора входных значений и параметров вывода (где отобразить результат). В полях для исходных данных указываем диапазон описываемого параметра (У) и влияющего на него фактора (Х). Остальное можно и не заполнять. После нажатия ОК, программа отобразит расчеты на новом листе (можно выбрать интервал для отображения на текущем листе или назначить вывод в новую книгу).
	3.		В первую очередь обращаем внимание на R-квадрат и коэффициенты. R-квадрат – коэффициент детерминации. В нашем примере – 0,755, или 75,5%. Это означает, что расчетные параметры модели на 75,5% объясняют зависимость между изучаемыми параметрами. Чем выше коэффициент детерминации, тем качественнее модель. Хорошо – выше 0,8. Плохо – меньше 0,5 (такой анализ вряд ли можно считать резонным). В нашем примере – «неплохо». Коэффициент 64,1428 показывает, каким будет Y, если все переменные в рассматриваемой модели будут равны 0. То есть на значение анализируемого параметра влияют и другие факторы, не описанные в модели. Коэффициент -0,16285 показывает весомость переменной Х на Y. То есть среднемесячная заработная плата в пределах данной модели влияет на количество уволившихся с весом -0,16285 (это небольшая степень влияния). Знак «-» указывает на отрицательное влияние: чем больше зарплата, тем меньше уволившихся. Что справедливо.
	ХОД РАБОТЫ (1)
		Задание 1 1. Ввести табличные данные зависимости заболеваемости бронхиальной астмой от концентрации угарного газа в атмосфере	Представить зависимость в виде точечной диаграммы
		Задание 2 Требуется получить три варианта регрессионных моделей (три графических тренда) зависимости заболеваемости бронхиальной астмой от концентрации угарного газа в атмосфере.	1. Для получения линейного тренда выполнить следующий алгоритм: => щелкнуть на поле диаграммы «Заболеваемость астмой», построенной в предыдущем задании; => выполнить команду Диаграмма —> Добавить линию тренда; => в открывшемся окне на вкладке Тип выбрать Линейный тренд; => перейти на вкладку Параметры; установить галочки на флажках: показывать уравнения на диаграмме и поместить на диаграмму величину достоверности аппроксимации R^2; =< щелкнуть на кнопке ОК. Полученная диаграмма представлена на рисунке.
		2. Получить экспоненциальный тренд	Алгоритм аналогичен предыдущему. На закладке Тип выбрать Экспоненциальный тренд. Результат представлен на рисунке.
		3. Получить квадратичный тренд	Алгоритм аналогичен предыдущему. На закладке Тип выбрать Полиномиальный тренд с указанием степени 2. Результат представлен на рисунке.
	2.ПРОГНОЗИРОВАНИЕ В MS EXCEL Типовой пример
		Попробуем предсказать сумму прибыли предприятия через 3 года на основе данных по этому показателю за предыдущие 12 лет.
		Строим график зависимости на основе табличных данных, состоящих из аргументов и значений функции.	Для этого выделяем табличную область, а затем, находясь во вкладке «Вставка», кликаем по значку нужного вида диаграммы, который находится в блоке «Диаграммы». Затем выбираем подходящий для конкретной ситуации тип. Лучше всего выбрать точечную диаграмму. Можно выбрать и другой вид, но тогда, чтобы данные отображались корректно, придется выполнить редактирование, в частности убрать линию аргумента и выбрать другую шкалу горизонтальной оси.
		Теперь нам нужно построить линию тренда	Делаем щелчок правой кнопкой мыши по любой из точек диаграммы. В активировавшемся контекстном меню останавливаем выбор на пункте «Добавить линию тренда».  Открывается окно форматирования линии тренда. В нем можно выбрать один из шести видов аппроксимации: Линейная; Логарифмическая; Экспоненциальная; Степенная; Полиномиальная; Линейная фильтрация. Давайте для начала выберем линейную аппроксимацию. В блоке настроек «Прогноз» в поле «Вперед на» устанавливаем число «3,0», так как нам нужно составить прогноз на три года вперед. Кроме того, можно установить галочки около настроек «Показывать уравнение на диаграмме» и «Поместить на диаграмме величину достоверности аппроксимации (R^2)». Последний показатель отображает качество линии тренда. После того, как настройки произведены, жмем на кнопку «Закрыть».  Линия тренда построена и по ней мы можем определить примерную величину прибыли через три года. Как видим, к тому времени она должна перевалить за 4500 тыс. рублей. Коэффициент R2, как уже было сказано выше, отображает качество линии тренда. В нашем случае величина R2 составляет 0,89. Чем выше коэффициент, тем выше достоверность линии. Максимальная величина его может быть равной 1. Принято считать, что при коэффициенте свыше 0,85 линия тренда является достоверной. Если же вас не устраивает уровень достоверности, то можно вернуться в окно формата линии тренда и выбрать любой другой тип аппроксимации. Можно перепробовать все доступные варианты, чтобы найти наиболее точный.
	ХОД РАБОТЫ (2)
	Задание 1. Требуется выполнить прогнозирование заболеваемости бронхиальной астмой при концентрации угарного газа равной 3 мг/куб. м. методом восстановления значения, воспользовавшись квадратичной зависимостью, полученной в предыдущей работе.
	1.	Построить следующую электронную таблицу:
	2.	Подставить в ячейку А2 значение концентрации угарного газа, равного 3 мг/куб. м	В результате получим:
	Задание 2 Требуется выполнить прогнозирование заболеваемости бронхиальной астмой при концентрации угарного газа равной б мг/куб. м. методом графической экстраполяции у воспользовавшись квадратичной зависимостью, полученной в предыдущей работе.
		Выполнить построение квадратичного тренда по алгоритму, описанному в предыдущей работе, добавив в него следующее действие	=> на вкладке Параметры в области Прогноз в строке вперед на установить 2 единицы. Здесь имеются в виду единицы используемого масштаба по горизонтальной оси. . Оценить приблизительно на полученном графике значение функции при значении аргумента, равном 6.

Задание для самостоятельного выполнения на получение регрессионных зависимостей:

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

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