Задание 2. С помощью MS Excel провести регрессионный анализ заданных данных

С помощью MS Excel провести регрессионный анализ заданных данных.

Численность населения мира, млн. чел.

Вариант	Страна
	США	76,4	97,6	122,2	130,5			200,5
	Германия	45,7	54,7	58,7	62,3				78,5
	Франция	40,8	41,8					50,5		56,5
	Япония		51,6	63,2	71,8				116,8	123,5
	СССР			171,5	186,5	205,5	226,5		258,5

Численность населения занятого в мировой экономике, млн. чел.

Вариант	Страна
	Германия	18,5	23,5		26,5						38,5
	Франция				19,5					26,5	27,5
	Англия	16,5	18,5		20,5	22,5			25,5		26,5
	Италия		16,5			18,5				24,5

Промышленное производство: добавленная стоимость, в ценах 2000 г., млрд. долл.

Вариант	Страна
	Германия
	Франция
	Англия
	СССР

Мировое сельскохозяйственное производство: добавленная стоимость в ценах 2000 г., млрд. долл.

Вариант	Страна
	США				76,5	93,5		128,5		157,5
	Германия				21,5				40,5	46,5	52,6
	Франция	21,5		22,5		23,5	29,5				76,5
	Италия	13,5	14,5			18,5	30,5		44,5
	СССР		50,5	58,8			81,5	87,5

Мировой товарный экспорт, в ценах 2000 г., млрд. долл.

Вариант	Страна
	Германия	21,5			64,1	36,5	87,5
	Франция		28,5	40,4		31,5	62,3
	Англия	38,5	54,5
	Бельгия	12,2	15,5	18,4	16,8	12,3	27,9

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

Тип	Описание
1. Линейная	Аппроксимирующая прямая: Y = b X + a, где b − тангенс угла наклона, а − точка пересечения прямой с осью Y
2. Логарифмическая	Логарифмическая аппроксимация: Y = b * ln (X) + a, где a и b − константы, ln − натуральный логарифм
3. Полиномиальная	Полиномиальная аппроксимация: Y = b 1X6 + b 2X5 + b 3X4 + b 4X3 + b 5X2 + b 6X + a, где b i, 1,2, …,6, и а − константа. Максимальная степень полинома 6
4. Степенная	Степенная аппроксимация: Y = b *X a, где a и b − константы
5. Экспоненциальная	Экспоненциальная аппроксимация: Y = b *e a X, где a и b − константы, е − основание натурального логарифма.

Теория

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

В таких случаях математическая формулировка задачи ставится следующим образом.

Имеются две наблюдаемые величины х и у, причем у зависит от х некоторым образом. Необходимо построить математическую модель , где f(x) − некоторая функция от х наилучшим образом описывающую наблюдаемые значения у.

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

где n − число наблюдений.

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

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

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

Удобной математической моделью экспериментальных зависимостей является уравнение вида Y(X) = f (X) + e, где e − случайная переменная (остатки). Это уравнение называется уравнением регрессии; функция f (X) − функцией регрессии. Относительно случайной величины e обычно делается предположение, что она имеет нормальное распределение с нулевым средним значением.

Выбор функции f (X) методом наименьших квадратов составляет задачу регрессионного анализа. Тип функции регрессии в значительной мере зависит от экспериментальных данных, однако наиболее часто используют многочлен вида Y = a + b ₁X + b ₂X² + … + b _mX^m (коэффициенты a и b_i определяется на основе экспериментальных данных). Такая функция линейной регрессии называется полиномиальной.