Пример выполнения лабораторной работы.. =ЛИНЕЙН(D3:D11;E3:E11;1;1).

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

1. Найти уравнение регрессии.

2. Найти общую сумму квадратов отклонений и остаточную сумму квадратов отклонений.

3. Найти коэффициент детерминации.

4. Найти параметры регрессии с помощью статистической функции ЛИНЕЙН.

 

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

Создадим новую рабочую книгу с семью листами.

Название листа	Назначение
Линейная	Для анализа линейной модели
Степенная	Для анализа степенной модели
Показательная	Для анализа показательной модели
Обратная	Для анализа обратной модели
Полулогарифмическая	Для анализа полулогарифмической модели
Гиперболическая	Для анализа гиперболической модели
Экспоненциальная	Для анализа экспоненциальной модели

 

Будем использовать данные из лабораторной работы №1.

 

1. Лист Линейная оформим, как показано на рис.1.3:

Рис. 1.3. Лист Линейная

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

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

 

Ячейка	Формула	Примечание
E3	=$A$16+$B$16*B3	Расчет теоретических значений результата . Копируем диапазон E3:E11
F3	=(C3–$C$12)^2	Копируем диапазон F3:F11
G3	=(C3–E3)^2	Копируем диапазон G3:G11
H3	=(E3-$C$12)^2	Копируем диапазон H3:H11
F12	=СУММ(F3:F11)	Копируем диапазон F12:H12

Замечание. В приведенных формулах неоднократно используется абсолютная адресация, содержащая знак «$». Это необходимо для того, чтобы при копировании формул данный адрес не изменялся. Для того чтобы превратить относительный адрес А16 в абсолютный ($A$16), достаточно нажать клавишу F4 в то время, когда курсор находится на ячейке А16.

Для вычисления коэффициента детерминации в ячейку Н15 введем формулу:

=1-G12/F12.

2. Регрессия в виде степенной функции имеет вид: .

Для нахождения параметров регрессии  необходимо провести ее линеаризацию:

Y=A+bX,

где Y=ln y, X=ln x, A=ln a.

Составляем вспомогательную таблицу для преобразованных данных (рис. 2.3):

Рис. 2.3 Лист Степенная

Вводим формулы:

 

Ячейка	Формула	Примечание
D3	=LN(C3)	Y=ln y Копируем диапазон D3:D11
E3	=LN(B3)	X=ln x Копируем диапазон E3:E11
F3	=D3*E3	Копируем диапазон F3:F11
G3	=E3^2	Копируем диапазон G3:G11
D12	=СРЗНАЧ(D3:D11)	Копируем диапазон D12:G12

 

Для вычисления коэффициентов регрессии введем следующие формулы:

 

Ячейка	Формула	Примечание
B16	=(F12-E12*D12)/(G12-E12^2)	b
A16	=D12-B16*E12	A

 

После потенцирования находим искомые коэффициенты регрессии:

 

Ячейка	Формула	Примечание
A19	=EXP(A16)	a
B19	= B16	b

Тогда уравнение регрессии будет иметь вид: .

 

Для расчета сумм введем формулы:

 

Ячейка	Формула	Примечание
I3	=$A$19*B3^$B$19	Расчет теоретических значений результата . Копируем диапазон I3:I11
J3	=(C3–$C$12)^2	Копируем диапазон J3:J11
K3	=(C3–E3)^2	Копируем диапазон K3:K11
L3	=(E3-$C$12)^2	Копируем диапазон L3:L11
J12	=СУММ(J3:J11)	Копируем диапазон J12:L12

Для вычисления коэффициента детерминации в ячейку L15 введем формулу:

=1-K12/J12.

Проведем расчеты параметров регрессии с помощью статистической функции ЛИНЕЙН.

Выделим диапазон А22:В26. введем формулу

=ЛИНЕЙН(D3:D11;E3:E11;1;1).

В левой верхней ячейке выделенной области появится первый элемент итоговой таблицы. Чтобы раскрыть всю таблицу, нажмите на клавишу F2, а затем – на комбинацию клавиш CTRL+SHIFT+ENTER.

 

3. Расчеты на остальных листах во многом повторяют расчеты, произведенные на листе Степенная, поэтому остальные листы лучше всего получить копированием листа Степенная.

Для этого необходимо:

· находясь на листе Степенная, выделить его полностью, щелкнув мышью на пересечении названий столбцов и строк; с помощью кнопки (Копировать) скопировать лист в Буфер обмена;

· перейти на следующий лист и выделив ячейку А1,щелкнуть мышью по кнопке (Вставить).

 

Получим следующие результаты (рис. 3.3-7.3):

Рис. 3.3. Лист Показательная

Рис. 4.3. Лист Обратная

 

Рис. 5.3. Лист Полулогарифмическая

 

Рис. 6.3. Лист Гиперболическая

 

Рис. 7.3. Лист Экспоненциальная

 

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

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

Название листа	Уравнение регрессии	Коэффициент детерминации
Линейная		0,9335
Степенная		0,9283
Показательная		0,9353
Обратная		0,9145
Полулогарифмическая		0,9060
Гиперболическая		0,8528
Экспоненциальная		0,9353

 

⇐ Предыдущая12345678Следующая ⇒

Поделиться: