Задание 5.1. Используя статистические данные по численности населения России (таблица 5.1), построить линейный график ЧислСтат (Год). Выделив линию графика, построить различные линии тренда, выражающие зависимость численности населения от времени: Вставка | Линия тренда (или, наведя курсор на линию графика, щелкнуть правой клавишей мыши; в появившемся контекстно-зависимом меню выбрать Добавить линию тренда).
Таблица 5.1. Население России
| Год, t | Численность статист., млн. чел. | Теория y=k*t+m | C-T | Теория y=a*t^2+d*t+c | C-T | Теория y=a* exp (b*t) | С-Т | Теория y=c*t^n | C-T |
| 117,5 | |||||||||
| 130,1 | |||||||||
| 137,6 | |||||||||
| 147,4 | |||||||||
| 148,5 | |||||||||
| 147,7 | |||||||||
| 148,7 | |||||||||
| 148,4 | |||||||||
| 148,3 | |||||||||
| ? | |||||||||
| S 1 = | S 2 = | S 3 = | S 4 = |
Проверить линейную, полиномиальную (n =2), экспоненциальную, степенную линии: Тип | Построение линии тренда (рис. 5.1).
Для каждого тренда:
а) выдать аналитическую зависимость Численность (Год): Параметры | Показывать уравнение на диаграмме (рис. 5.2);
б) найти погрешность С–Т (разницу между статистической и трендовой численностью);
в) рассчитать квадратичное отклонение Si, используя функцию СУММКВ.
Сравнить эти отклонения и по минимальному Si оценить численность населения в 2000 году.
Рис. 5.1. Диалоговое окно Линия тренда/Тип
Рис. 5.2. Диалоговое окно Линия тренда/Параметры
Задание 5.2. Используя новое значение численности России в 1998 году – 146,2 млн. чел., уточнить экстраполяцию, используя только данные 90-х годов. Проанализировать полученные результаты.
Задание 5.3. По заданной таблице 5.2 построить линии полиномиального тренда, наилучшим образом (по максимальному значению критерия детерминации R2) описывающие дневную температуру в г. Томске в разные месяцы 2007-2009 гг.
Определить среднюю температуру месяца и отклонение от нее максимальной и минимальной температуры в процентах.
Вычислить коэффициент корреляции температуры для одних и тех же месяцев двух разных лет. Сделать выводы.
Коэффициент корреляции используется для определения наличия взаимосвязи между двумя различными рядами данных Xi, Yi, i = 1... n и имеет вид:
О хорошей корреляции говорят значения К, по модулю близкие к единице. Знак «+» соответствует прямой взаимосвязи, знак «-» - обратной. Вычисление этой формулы встроено в Excel (функция КОРРЕЛ).
Таблица 5.2. Дневная температура в г. Томске в 2007-2009 гг.
| 2007 г. | Месяц | |||||||||||
| День | Январь | Февраль | Март | Апрель | Май | Июнь | Июль | Август | Сентябрь | Октябрь | Ноябрь | Декабрь |
| –19 –17 –12 –3 –2 –15 –9 –18 –22 –25 –20 –13 –12 –20 –20 –5 –2 –3 –15 –20 –6 –4 –4 –14 –6 –11 –14 –10 –10 –4 | –6 –15 –20 –12 –9 –8 –6 –12 –6 –6 –5 –4 –11 –7 –4 –1 –1 –2 –3 –11 –20 –16 –16 –16 –10 –6 –1 | –4 –1 –1 –1 –1 –2 –3 –1 | –3 –4 –3 –1 | –4 –5 –5 –1 –14 –15 –7 –5 –2 –4 –21 –24 –15 –9 –25 –30 –23 –10 –15 –31 –30 –26 –17 | –14 –9 –19 –28 –26 –9 –19 –22 –12 –17 –19 –13 –9 –22 –23 –25 –31 –20 –10 –11 –17 –9 –20 –29 –23 –12 –18 –15 –6 –19 –22 | |||||||
| 2008 г. | Месяц | |||||||||||
| День | Январь | Февраль | Март | Апрель | Май | Июнь | Июль | Август | Сентябрь | Октябрь | Ноябрь | Декабрь |
| –32 –23 –13 –12 –13 –20 –20 –33 –30 –28 –27 –32 –29 –25 –27 –31 –24 –18 –16 –13 –19 –18 –15 –14 –15 –10 –12 –16 –13 –14 –17 | –15 –12 –3 –10 –20 –14 –15 –16 –20 –29 –21 –10 –6 –3 –11 –16 –17 –12 –7 –5 –2 –10 –5 –4 –2 –4 –1 | –6 –5 –3 –1 –1 –10 –15 –22 –1 –1 –2 –2 –8 –3 –4 | –2 –3 –2 –1 –1 –2 –2 | –2 –8 –1 –7 –10 –5 | –7 –1 –1 –7 –12 –10 –15 –13 –12 –15 –9 –16 –17 –9 –17 –22 –20 –22 –25 –31 –34 –30 –23 | –19 –22 –21 –16 –15 –4 –8 –13 –7 –9 –6 –10 –5 –2 –8 –10 –12 –5 –7 –6 –4 –3 –4 –1 –8 –20 –17 –13 –2 –5 |
Продолжение таблицы 5.2
| 2009 г. | Месяц | |||||||||||
| День | Январь | Февраль | Март | Апрель | Май | Июнь | Июль | Август | Сентябрь | Октябрь | Ноябрь | Декабрь |
| –4 –17 –25 –23 –25 –17 –14 –19 –14 –6 –12 –15 –17 –6 –9 –16 –20 –17 –8 –5 –8 –8 –31 –30 –36 –27 –21 –15 –9 –17 –18 | –6 –8 –5 –2 –12 –9 –5 –2 –4 –4 –12 –16 –8 –11 –5 –6 –10 –7 –6 –3 –1 –7 –4 –2 | –5 –10 –15 –23 –20 –22 –20 –15 –20 –13 –10 –13 –15 –13 –15 –10 –16 –13 –15 –5 –2 –11 –17 –12 –5 | –1 –2 –2 –12 –6 –4 | –2 –5 –7 –5 –13 –4 –2 –5 –8 –19 –25 –27 –25 –22 –19 –19 –17 –14 –12 –6 | –7 –5 –4 –1 –6 –4 –5 –3 –13 –4 –2 –5 –4 –2 –1 –3 –12 –14 –17 –7 –1 –1 –5 –11 –10 –6 –19 –26 –36 –39 |
Контрольные вопросы
1. Что такое линия тренда?
2. Для каких типов задач используются линии тренда?
3. Перечислите типы линий тренда.
4. Каковы критерии оценки линии тренда?
5. Что такое коэффициент детерминации?
6. Что такое квадратичное отклонение?
7. Опишите параметры диалогового окна Линия тренда/Параметры.
8. Как построить аналитическую зависимость Численность /Год?
9. Как рассчитать квадратичное отклонение Si, используя функцию СУММКВ?
10. Что представляет собой процесс экстраполяции?
11. Что такое коэффициент корреляции?
Лабораторная работа №6
