Задача - Аппроксимация по методу наименьших квадратов (МНК) - образец решения

Определить, имеется ли взаимосвязь между живой массой коровы (в кг) и ее удоем (в кг), если известны результаты наблюдений за этими показателями. Аппроксимировать зависимость удоя от живой массы наиболее подходящим уравнением и определить предположительный удой коровы с живой массой 600 кг.

Наблюдаемые величины

Живая масса, кг	511	572	595	661	654	595	647	625	595	646	536	640
Удой, кг	8320	9982	10213	7859	8419	8457	6229	5955	6260	6832	9712	7573

Решение

Вводим экспериментальные значения в ячейки Эксель
Итак, исходные данные – в диапазоне А3:В14.
В любую ячейку, например, в В15 вводим формулу =коррел(А3:А14;В3:В14)
В этой ячейке получили коэффициент корреляции. Он равен –0,49. Это говорит о том, что связь между живой массой и удоем обратная, не тесная.
Для аппроксимации по МНК нужно построить точечную диаграмму, где в качестве независимой переменной х указываем значения живой массы из диапазона А3:А14, а в качестве зависимой переменной у – значения удоя из диапазона В3:В14.
Далее последовательно добавляем две линии тренда (линейную и полиномиальную 2-й степени) с параметрами.
Для этого (когда диаграмма активна) меню Диаграмма, Добавить линию тренда. На вкладке Тип вбираем Тип (линейный, затем полиномиальный 2-й степени), на вкладке Параметры – устанавливаем флажок на Показывать ур-ние на диаграмме и Поместить достоверность аппроксимации.
Получили уравнения аппроксимирующих линий на диаграмме и их достоверность.
Чтобы получить ожидаемый удой при живой массе в 600 кг, подставляем значение 600 в то уравнение, достоверность которого выше. Для этого в любой ячейке записываем (например, в В17) формулу =-0,0733*600*600+71,383*600-8188,3
Получили, что при живой массе коровы 600 кг ожидаемый удой составит 8253,5 кг молока с достоверностью 25%.