Построение полиномиальной зависимости от одной переменной с помощью диаграммы и линии тренда

Регрессионные зависимости и коэффициент корреляции.

 

1. Откройте файл MET72.xls

2. Создайте копию листа с данными («Правка» - «Переместить/скопировать лист») и назовите лист «Регрессия».

3. Удалите с этого листа все столбцы, кроме максимальной температуры воздуха (Тмакс), среднего балла облачности (Средн обл) и максимальной температуры почвы (ТпочвМакс). Столбец А должен остаться пустым.

	А	В	С	D
		Тмакс	СреднОбл	ТпочвМакс

 

Построение полиномиальной зависимости от одной переменной с помощью диаграммы и линии тренда

4. Построить диаграмму типа «ТОЧЕЧНАЯ», взяв в качестве значений Х максимальную температуру воздуха (Тмакс), а качестве значений Y - максимальную температуру почвы (ТпочвМакс). Подписать названия осей. Напечатать название диаграммы – «Зависимость температуры почвы от максимальной температуры воздуха».

5. Добавить линию тренда на диаграмме:
щелкнуть правой клавишей мыши по группе точек на диаграмме;
в появившемся меню выбрать «Добавить линию тренда»;
в открывшемся окне «Линия тренда» выбрать «Тип» - «Полиномиальная» 2 степени и задать параметры: «Показывать уравнение на диаграмме» и «Поместить на диаграмму величину достоверности аппроксимации (R^2)»

6. Переписать в тетрадь зависимость, полученную с помощью линии тренда, подставив вместо «Y» и «X» значения зависимой и независимой переменных (см. пункт 4). Выписать величину достоверности аппроксимации R². Написать название формулы – «Зависимость температуры почвы от максимальной температуры воздуха, полученная с помощью линии тренда».

7. Провести анализ полученной регрессионной зависимости:

· в ячейке Е1 напечатать заглавие столбца Е: «Тренд»,

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

8. Построить диаграмму типа «ТОЧЕЧНАЯ» (с маркерами, но без линий) для зависимости расчетных значений температуры почвы от наблюденных значений. В качестве значений Х брать данные ТпочвМакс (из столбца D), а в качестве значений Y – результаты расчетов по линии тренда (из столбца Е).

9. Ось Х назвать Тпочв экспериментальная, ось Y – Тпочв расчетная.

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

-30

и добавить на диаграмму ряд данных, указав эти две ячейки и для значений Х, и для значений Y. Выбрать для этого ряда линию без маркеров. Это будет биссектриса декартового угла.

11. Отформатировать диаграмму так, чтобы область построения диаграммы была квадратной, а максимальные и минимальные значения на осях Х и Y были бы одинаковыми. Диаграмму назвать «Зависимость по линии тренда».

 

Построение линейной зависимости от двух переменных с помощью функции =линейн()

 

12. С помощью функции =линейн рассчитать линейную регрессионную зависимость максимальной температуры почвы (Тпочвы Макс) от ДВУХ переменных -максимальной температуры воздуха (Тмакс) и среднего балла облачности (Средн обл).

· предварительно на свободном месте (не занимая столбцы E, F, G, H) выделить блок ячеек из 5 строк и 3 столбцов;

· значения параметров «константа» и «статистика» - «ИСТИНА»;

· после заполнения окошек для аргументов нажать «CTRL», «SHIFT» и «ENTER».

13. С помощью справки по функции =линейн() выяснить значения коэффициентов и свободного члена в уравнении регрессии и величину коэффициента детерминации («детерминированности»)

14. Переписать в тетрадь зависимость максимальной температуры почвы (Тпочвы Макс) от ДВУХ переменных -максимальной температуры воздуха (Тмакс) и среднего балла облачности (Средн обл), полученную с помощью функции =линейн(), использовав полученные коэффициенты при независимых переменных и свободный член. Обратить внимание, в каком порядке EXCEL выдает коэффициенты при независимых переменных (см. справку по функции =линейн()). Выписать коэффициент детерминации R². Написать название формулы – «Зависимость температуры почвы от максимальной температуры воздуха, полученная с помощью функции =линейн()».

15. Провести анализ полученной регрессионной зависимости:

· в ячейке F1 напечатать заглавие столбца F: «Линейная»,

· в столбце F вычислить расчетные значения температуры почвы, пользуясь коэффициентами линейной регрессионной зависимости, полученными с помощью функции =линейн();

16. Построить диаграмму типа «ТОЧЕЧНАЯ» (с маркерами, но без линий) для зависимости расчетных значений температуры почвы (из столбца F)от наблюденных значений ТпочвМакс (из столбца D), как описано в пунктах 8, 9, 10, 11. Диаграмму назвать «Линейная зависимость». Можно скопировать предыдущую диаграмму и заменить в ней данные из столбца Е на данные из столбца F и напечатать другое название.

Построение квадратичной зависимости от двух переменных с помощью функции =линейн()

17. Столбец А озаглавить «Т2» и заполнить квадратами величин максимальной температуры воздуха.

18. С помощью функции =линейн рассчитать линейную регрессионную зависимость максимальной температуры почвы (Тпочвы Макс) от ТРЕХ переменных - среднего балла облачности (Средн обл), максимальной температуры воздуха (Тмакс) и квадрата максимальной температуры воздуха (Т2).

· предварительно на свободном месте (не занимая столбцы E, F, G, H) выделить блок ячеек из 5 строк и 4 столбцов;

· значения параметров «константа» и «статистика» - «ИСТИНА»;

· после заполнения всех окошек для аргументов нажать «CTRL», «SHIFT» и «ENTER».

19. С помощью справки по функции =линейн() выяснить значения коэффициентов и свободного члена в уравнении регрессии и величину коэффициента детерминации.

20. Переписать в тетрадь зависимость, полученную с помощью функции =линейн(), использовав полученные коэффициенты при независимых переменных и свободный член. Обратить внимание, в каком порядке EXCEL выдает коэффициенты при независимых переменных (см. справку по функции =линейн()). Выписать коэффициент детерминации R². Написать название формулы – «Квадратичная зависимость, полученная с помощью функции =линейн()».

21. Провести анализ полученной регрессионной зависимости:

· в ячейке G1 напечатать заглавие столбца G: «Квадратичная»,

· в столбце G вычислить расчетные значения температуры почвы, пользуясь коэффициентами линейной регрессионной зависимости, полученными с помощью функции =линейн();

22. Построить диаграмму типа «ТОЧЕЧНАЯ» (с маркерами, но без линий) для зависимости расчетных значений температуры почвы (из столбца F)от наблюденных значений ТпочвМакс (из столбца D), как описано в пунктах 8, 9, 10, 11. Диаграмму назвать «Квадратичная зависимость с помощью функции Линейн». Можно скопировать предыдущую диаграмму и заменить в ней данные из столбца Е на данные из столбца G и напечатать другое название.