Регрессионные зависимости и коэффициент корреляции.
1. Откройте файл MET72.xls
2. Создайте копию листа с данными («Правка» - «Переместить/скопировать лист») и назовите лист «Регрессия».
3. Удалите с этого листа все столбцы, кроме максимальной температуры воздуха (Тмакс), среднего балла облачности (Средн обл) и максимальной температуры почвы (ТпочвМакс). Столбец А должен остаться пустым.
| А | В | С | D | |
| Тмакс | СреднОбл | ТпочвМакс |
Построение полиномиальной зависимости от одной переменной с помощью диаграммы и линии тренда
4. Построить диаграмму типа «ТОЧЕЧНАЯ», взяв в качестве значений Х максимальную температуру воздуха (Тмакс), а качестве значений Y - максимальную температуру почвы (ТпочвМакс). Подписать названия осей. Напечатать название диаграммы – «Зависимость температуры почвы от максимальной температуры воздуха».
5. Добавить линию тренда на диаграмме:
щелкнуть правой клавишей мыши по группе точек на диаграмме;
в появившемся меню выбрать «Добавить линию тренда»;
в открывшемся окне «Линия тренда» выбрать «Тип» - «Полиномиальная» 2 степени и задать параметры: «Показывать уравнение на диаграмме» и «Поместить на диаграмму величину достоверности аппроксимации (R^2)»
6. Переписать в тетрадь зависимость, полученную с помощью линии тренда, подставив вместо «Y» и «X» значения зависимой и независимой переменных (см. пункт 4). Выписать величину достоверности аппроксимации R2. Написать название формулы – «Зависимость температуры почвы от максимальной температуры воздуха, полученная с помощью линии тренда».
7. Провести анализ полученной регрессионной зависимости:
· в ячейке Е1 напечатать заглавие столбца Е: «Тренд»,
· в столбце Е вычислить расчетные значения температуры почвы, пользуясь полученными коэффициентами полиномиальной регрессионной зависимости, полученной по линии тренда на диаграмме;
8. Построить диаграмму типа «ТОЧЕЧНАЯ» (с маркерами, но без линий) для зависимости расчетных значений температуры почвы от наблюденных значений. В качестве значений Х брать данные ТпочвМакс (из столбца D), а в качестве значений Y – результаты расчетов по линии тренда (из столбца Е).
9. Ось Х назвать Тпочв экспериментальная, ось Y – Тпочв расчетная.
| 10. В двух свободных ячейках около диаграммы друг под другом напечатать числа |
|
и добавить на диаграмму ряд данных, указав эти две ячейки и для значений Х, и для значений Y. Выбрать для этого ряда линию без маркеров. Это будет биссектриса декартового угла.
11. Отформатировать диаграмму так, чтобы область построения диаграммы была квадратной, а максимальные и минимальные значения на осях Х и Y были бы одинаковыми. Диаграмму назвать «Зависимость по линии тренда».
Построение линейной зависимости от двух переменных с помощью функции =линейн()
12. С помощью функции =линейн рассчитать линейную регрессионную зависимость максимальной температуры почвы (Тпочвы Макс) от ДВУХ переменных -максимальной температуры воздуха (Тмакс) и среднего балла облачности (Средн обл).
· предварительно на свободном месте (не занимая столбцы E, F, G, H) выделить блок ячеек из 5 строк и 3 столбцов;
· значения параметров «константа» и «статистика» - «ИСТИНА»;
· после заполнения окошек для аргументов нажать «CTRL», «SHIFT» и «ENTER».
13. С помощью справки по функции =линейн() выяснить значения коэффициентов и свободного члена в уравнении регрессии и величину коэффициента детерминации («детерминированности»)
14. Переписать в тетрадь зависимость максимальной температуры почвы (Тпочвы Макс) от ДВУХ переменных -максимальной температуры воздуха (Тмакс) и среднего балла облачности (Средн обл), полученную с помощью функции =линейн(), использовав полученные коэффициенты при независимых переменных и свободный член. Обратить внимание, в каком порядке EXCEL выдает коэффициенты при независимых переменных (см. справку по функции =линейн()). Выписать коэффициент детерминации R2. Написать название формулы – «Зависимость температуры почвы от максимальной температуры воздуха, полученная с помощью функции =линейн()».
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 выдает коэффициенты при независимых переменных (см. справку по функции =линейн()). Выписать коэффициент детерминации R2. Написать название формулы – «Квадратичная зависимость, полученная с помощью функции =линейн()».
21. Провести анализ полученной регрессионной зависимости:
· в ячейке G1 напечатать заглавие столбца G: «Квадратичная»,
· в столбце G вычислить расчетные значения температуры почвы, пользуясь коэффициентами линейной регрессионной зависимости, полученными с помощью функции =линейн();
22. Построить диаграмму типа «ТОЧЕЧНАЯ» (с маркерами, но без линий) для зависимости расчетных значений температуры почвы (из столбца F)от наблюденных значений ТпочвМакс (из столбца D), как описано в пунктах 8, 9, 10, 11. Диаграмму назвать «Квадратичная зависимость с помощью функции Линейн». Можно скопировать предыдущую диаграмму и заменить в ней данные из столбца Е на данные из столбца G и напечатать другое название.
