4.4. Построение линии гиперболической регрессии

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

Животные	Время удвоения массы новорождённых, дни	Содержание белков в молоке матери, %
Лошадь	75	2,4
Корова	60	2,6
Коза	43	3,3
Овца	27	4,8
Мул	15	5,6
Свинья	14	7,5

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

Для выполнения этого задания проделайте следующие пункты.

1. Перейдите на следующий рабочий лист.

2. Поскольку в Excel нет возможности быстрого построения с помощью стандартных процедур линии гиперболической регрессии, то для нахождения ее коэффициентов придется использовать формулы (4.16-4.17). Содержащиеся в этих формулах суммы удобнее всего вычислить с помощью вспомогательной расчетной таблицы.

3. Наберите в заголовки столбцов расчетной таблицы: в ячейку А1 – ; в ячейку В1 – ; в ячейку С1 – 1/ ; в ячейку D1 – / ; в ячейку Е1 – .

4. В интервал ячеек А2:А7 наберите данные о времени удвоения массы новорожденных; а в интервал ячеек В2:В7 наберите данные о содержании белков в молоке матерей.

5. В ячейку С2 наберите формулу: =1/А2 и распространите ее с помощью Маркера заполнения на интервал ячеек С2:С7.

6. В ячейку D2 наберите формулу: =В2*С2 (или =В2/А2, что тоже самое) и распространите ее на интервал ячеек D2:D7 с помощью Маркера заполнения.

7. В ячейку Е2 наберите формулу: =С2^2 (или 1/A2^2, что тоже самое) и распространите ее с помощью Маркера заполнения на интервал ячеек Е2:Е7.

8. Выделите ячейку А8 и нажмите на кнопку Автосумма на панели инструментов. Тогда в этой ячейке появится сумма чисел диапазона А2:А7, т.е. .

9. Распространите полученную формулу Маркером заполнения на ячейки диапазона А8:Е8. Тогда:

   • в ячейке В8 будет сумма ;

   • в ячейке С8  ;

   • в ячейке D8  ;

   • в ячейке Е8  .

10. Поскольку именно эти суммы входят в формулы (4.17), то по ним легко найти величины _а и _b. Для этого в ячейку А9 наберите:  =; в ячейку А10 наберите: «_а=»; в ячейку А11: «_b=». (Греческую заглавную букву   «дельта» можно набрать, если выбрать шрифт Symbol и нажать одновременно клавиши Shift и D.)

11. Теперь в ячейку В9 наберите формулу: =С8^2-6*Е8; в ячейку В10 наберите формулу: =С8*В8-6*D8; в ячейку В11 наберите формулу: =С8*D8-В8*Е8.

12. Теперь по формулам (4.16) осталось найти коэффициенты гиперболической регрессии. Для этого в ячейку С9 наберите: «а=»; в ячейку С10: «b=». Оформите содержание этих ячеек курсивом и выровняйте их по правому краю.

13. Далее, в ячейку D9 наберите формулу: =В10/В9; в ячейку D10 наберите формулу: =В11/В9. Должно получиться, что а=75,008;

b=1,512. Это означает, что уравнение гиперболической регрессии имеет вид: .

14. Чтобы построить график линии регрессии следует протабулировать полученную зависимость. Для этого в ячейку F1 наберите заголовок нового столбца: «Y_i». Он будет содержать вычисленные по полученной формуле гиперболической регрессии значения Y для каждого Х.

15. В ячейку F2 наберите формулу: =$D$9/A2+$D$10 и распространите ее с помощью Маркера заполнения на диапазон ячеек F2:F7.

16. Окончательный вид полученной расчетной таблицы:

    xi	yi	1/xi	yi/xi	1/xi^2	Yi
    75	2,4	0,013	0,032	0,0002	2,512
    60	2,6	0,017	0,043	0,0003	2,762
    43	3,3	0,023	0,077	0,0005	3,256
    27	4,8	0,037	0,178	0,0014	4,29
    15	5,6	0,067	0,373	0,0044	6,512
    14	7,5	0,071	0,536	0,0051	6,869
    234	26,2	0,228	1,239	0,0119
    =	-0,019	a=	75,008
    a=	-1,450	b=	1,512
    b=	-0,029

17. Выделите мышью интервал ячеек А2:В7 и F2:F7, прижимая Ctrl. Затем вызовите Мастер диаграмм, в котором выберите Тип диаграммы – Точечная и нажмите Готово.

18. Отредактируйте диаграмму: удалите легенду, линии сетки, сделайте фон белым.

19. Щелкните мышью по любому розовому маркеру (они отмечают предсказанные значения Y_i). В появившемся диалоговом окне Формат ряда данных:

• в группе Линия активизируйте переключатели Обычная и Сглаженная линия;

• в группе Маркер активизируйте переключатель Отсутствует и нажмите ОК.

20. Самостоятельно добавьте надписи на осях и графике и приведите его к следующему виду: