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

1. Запустить пакет Ms Excel и проверить наличие кнопки Анализ данных на вкладке Данные в группе Анализ. Если кнопка Анализ данных недоступна, необходимо загрузить надстройку «Пакет анализа». Для этого необходимо:

   1. щелкнуть значок Кнопка Microsoft Office , а затем Параметры Excel;

   2. перейти на вкладку Надстройки, а затем в поле Управление выбрать Надстройки Excel;

   3. нажать кнопку Перейти и в поле Доступные надстройки установить флажок Пакет анализа, а затем нажать кнопку ОК.

Рис. 2 – Панель анализа данных

2. На листе в Ms Excel создать макет табл. 3 «Вспомогательные вычисления» и ввести в нее исходные данные в соответствии с индивидуальным заданием.

3. Провести вычисления , , рассчитать суммы для , , , и внести эти данные в табл. 3.

4. По формуле (1.5) рассчитать коэффициенты и построить уравнение линейной регрессии , подставив найденные .

5. Вычислить среднее значение , рассчитать значения , , и заполнить табл. 3 найденными значениями.

6. Вычислить коэффициент детерминации по формуле (1.6). Сделать вывод о построенной модели регрессии.

7. Исследовать исходные данные с помощью режима Регрессия в MS Excel и сравнить с результатами, рассчитанными «вручную».

8. Сделать прогноз расчетного значения функции для , заданного в индивидуальном задании.

Лабораторная работа № 2. Метод наименьших квадратов. Нелинейная регрессия

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

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

Экспонента. Для приведения к линейному виду уравнения экспоненты проведем логарифмирование:

Введем переменные и , тогда получим линейное уравнение .

Тогда система уравнений (1.4) будет иметь вид (2.1).

(2.1)

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

,

Степенная функция. Для нахождения коэффициентов уравнения степенной функции необходимо также провести логарифмирование.

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

(2.2)

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

,

Парабола. Для нахождения коэффициентов уравнения параболы необходимо записать выражение (2.3) и устремить его к минимуму.

(2.3)

Минимум функции можно получить, взяв частные производные по и приравняв их к нулю:

П роведя преобразования, получим систему уравнений (2.4), решив которую найдем коэффициенты регрессии :

(2.4)

Сила регрессионной связи для параболы определяется непосредственно по той же формуле что и для линейной модели (1.6).

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

;

,

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

Задача:

Используем исходные данные (табл. 1.1) представленные в лабораторной работе № 1 для подбора нелинейных функций регрессии: экспоненты, степенной функции и параболы.

Найти:

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

2. Проанализировать силу регрессионной связи для каждой функции.

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

4. На каждом листе исследуемых функций построить точечные диаграммы исходных данных задачи. Добавить линии тренда и вывести их параметры (уравнение регрессии и коэффициент детерминации ) на диаграммы в соответствии с исследуемой функцией. Сравнить правильность результатов вычисленных «вручную» с параметрами линии тренда на диаграмме.

5. Среди исследованных функций: линейной, экспоненты, степенной функции и параболы выбрать наиболее точно описывающую данные эксперимента.

Решение задачи «вручную»:

Экспонента.

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

Таблица 2.1 – Вспомогательные вычисления для определения уравнения регрессии

№
1	3,5	16	12,25	2,7726	9,7041	2,6995	0,0157	0,0393
2	2,4	13	5,76	2,5649	6,1559	2,4738	0,0101	0,0001
3	4,9	19	24,01	2,9444	14,4278	2,9868	0,1701	0,1370
4	4,2	18	17,64	2,8904	12,1396	2,8432	0,0723	0,0999
5	3	12	9	2,4849	7,4547	2,5969	0,0005	0,0080
6	1,3	11	1,69	2,3979	3,1173	2,2480	0,1065	0,0311
7	1	8	1	2,0794	2,0794	2,1865	0,1504	0,2449
8	3	14	9	2,6391	7,9172	2,5969	0,0005	0,0042
9	1,5	9	2,25	2,1972	3,2958	2,2891	0,0814	0,1422
10	4,1	16	16,81	2,7726	11,3676	2,8227	0,0617	0,0393
	28,9	136	99,41	25,7435	77,6593	25,7434	0,6692	0,7460
Среднее:	2,89	13,6	9,941	2,5743	7,7659	2,6995	0,0157	0,0393

Подставив результаты расчетов в систему уравнений (2.1) получим:

Для решения системы нормальных уравнений используем надстройку «Поиск решения» пакета Excel (Данные-Поиск решения). Составим исходную табличную модель (рис. 2.1).

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

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

Переменные
b0	b1
0	1
Матрица исходной системы			Значения левых частей уравнений		Свободные члены исходной системы
10	28,9		28,9	=	25,7434
28,9	99,41		99,41	=	77,6592

Рис. 2.1. Табличная модель решения системы линейных уравнений средствами Excel

«Значения левых частей уравнений» в верхнюю ячейку вводим формулу:

=СУММПРОИЗВ(«диапазон числовых значений переменных (0 и 1)»;«диапазон первой строки матрицы исходной системы (10 и 28,9)»). Автоматически заполняем весь столбец «Значения левых частей уравнений».

В блок «Свободные члены исходной системы» в столбец записываем значения правой части исходной системы и вызываем форму «Поиск решения» рис. 2.2.

Рис. 2.2. Окно «Поиск решения»

Необходимо заполнить форму «Поиск решения» следующими данными:

- установить целевую ячейку – ничего не ставить;

- равной – максимальному значению;

- изменяя ячейки – диапазон строки значений переменных (в нашем случае выделить значения 0 и 1);

- ограничения – диапазон «Значения левых частей уравнений» = диапазон «Свободные члены исходной системы»;

- нажать «Выполнить»;

- в форме «Результаты поиска решения» (рис. 2.3) выбрать «Сохранить найденное решение»;

- нажать ОК.

Рис. 2.3. Окно «Результаты поиска решения»

Результат выполнения команды «Поиск решения» для коэффициентов представлен на рис. 2.4.

Переменные
b0	b1
1,981248	0,205222
Матрица коэффициентов исходной системы			Значения левых частей уравнений		Свободные члены исходной системы
10	28,9		25,74339973		25,7434
28,9	99,41		77,659199		77,6592

Рис. 2.4. Результаты выполнения команды «Поиск решения» для экспоненциального распределения

Для определения экспоненциального распределения необходимо вычислить коэффициенты и .

Тогда уравнение регрессии примет вид

Определим коэффициент детерминации . Для этого необходимо рассчитать и заполнить пустые столбцы табл. 2.1 данными. При расчетах необходимо использовать формулы: , , , тогда