Добавлен: 30.10.2018
Просмотров: 641
Скачиваний: 15
Лабораторная работа 3. Трендовый анализ
Excel имеет специальный аппарат для графического анализа моделей. в том числе построения аппроксимационных зависимостей (линий тренда P(x)) по данной таблице {xi .yi}. которые приближенно отражают функциональную связь y=f(x).
Линии тренда обычно используются в задачах прогнозирования. Такие задачи решают с помощью методов регрессионного анализа. С помощью регрессионного анализа можно показать тенденцию изменения рядов данных. экстраполировать их (то есть продолжить линию тренда вперед или назад за пределы известных данных). Можно также построить линию скользящего среднего. которая сглаживает случайные флуктуации. яснее демонстрирует модель и прослеживает тенденцию изменения данных.
Линиями тренда можно дополнить ряды данных. представленные на линейчатых диаграммах. гистограммах. графиках. биржевых. точечных и пузырьковых диаграммах. Нельзя дополнить линиями тренда ряды данных на объемных. лепестковых. круговых и кольцевых диаграммах.
Excel позволяет выбрать один из пяти типов линии тренда – линейный. логарифмический. экспоненциальный. степенной или полиномиальный (2...6 степени) и проверить (по различным критериям). какой из типов лучше всего подходит в данной ситуации. Критерием может служить или критерий R2 (коэффициент детерминации). автоматическое вычисление которого встроено в диалоговое окно Линия тренда. или квадратичное отклонение
.
обычно используемое в методе наименьших квадратов при аппроксимации табличных функций.
Чем меньше квадратичное отклонение. тем лучше линия тренда аппроксимирует ряд данных. Или. чем ближе коэффициент детерминации к единице. тем лучше тренд.
Задания
Задание 1. Используя статистические данные по численности населения России (таблица 3.1). построить линейный график ЧислСтат (Год). Выделив линию графика. построить различные линии тренда. выражающие зависимость численности населения от времени: Вставка | Линия тренда (или. наведя курсор на линию графика. щелкнуть правой клавишей мыши; в появившемся контекстно-зависимом меню выбрать Добавить линию тренда).
Таблица 3.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 |
|
1960 |
117.5 |
|
|
|
|
|
|
|
|
|
1970 |
130.1 |
|
|
|
|
|
|
|
|
|
1980 |
137.6 |
|
|
|
|
|
|
|
|
|
1990 |
147.4 |
|
|
|
|
|
|
|
|
|
1991 |
148.5 |
|
|
|
|
|
|
|
|
|
1992 |
147.7 |
|
|
|
|
|
|
|
|
|
1993 |
148.7 |
|
|
|
|
|
|
|
|
|
1994 |
148.4 |
|
|
|
|
|
|
|
|
|
1995 |
148.3 |
|
|
|
|
|
|
|
|
|
2000 |
? |
|
|
|
|
|
|
|
|
|
|
|
S1= |
|
S2= |
|
S3= |
|
S4= |
Проверить линейную. полиномиальную (n=2). экспоненциальную. степенную линии: Тип | Построение линии тренда (рис. 3.1).
Для каждого тренда:
а) выдать аналитическую зависимость Численность (Год): Параметры | Показывать уравнение на диаграмме (рис. 3.2);
б) найти погрешность С–Т (разницу между статистической и трендовой численностью);
в) рассчитать квадратичное отклонение Si . используя функцию СУММКВ.
Сравнить эти отклонения и по минимальному Si оценить численность населения в 2000 году.
|
|
|
|
|
|
Задание 2. Используя новое значение численности России в 1998 году – 146.2 млн. чел.. уточнить экстраполяцию. используя только данные 90-х годов. Проанализировать полученные результаты.
Задание 3. По заданной таблице 3.2 построить линии полиномиального тренда. наилучшим образом (по максимальному значению критерия детерминации R2см. в студопедии: studopedia.ru) описывающие дневную температуру в г. Томске в разные месяцы 19971999 гг.
Определить среднюю температуру месяца и отклонение от нее максимальной и минимальной температуры в процентах.
Вычислить коэффициент корреляции температуры для одних и тех же месяцев двух разных лет. Сделать выводы.
Коэффициент корреляции используется для определения наличия взаимосвязи между двумя различными рядами данных Xi . Yi . i = 1... n и имеет вид:
.
О хорошей корреляции говорят значения К. по модулю близкие к единице. Знак «+» соответствует прямой взаимосвязи. знак «» ― обратной. Вычисление этой формулы встроено в Excel(функция КОРРЕЛ).
Таблица 3.2 – Дневная температура в г. Томске в 1997–1999 гг.
|
1997 г. |
Месяц |
|||||||||||
|
День |
Январь |
Февраль |
Март |
Апрель |
Май |
Июнь |
Июль |
Август |
Сентябрь |
Октябрь |
Ноябрь |
Декабрь |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
–19 –17 –12 –3 –2 –15 –9 2 –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 0 –1 –2 –3 –11 –20 –16 –16 –16 –10 –6 –1
|
0 4 0 –4 –1 2 1 –1 –1 –1 –2 –3 0 2 –1 2 2 3 5 6 8 9 11 9 10 7 11 12 3 3 5 |
7 6 5 6 4 10 13 15 15 16 18 17 16 9 20 14 9 12 15 16 22 22 21 20 12 10 18 16 12 13 |
19 12 15 10 11 12 24 14 10 11 4 3 5 7 10 14 16 18 23 28 17 19 20 17 19 27 30 22 19 29 27 |
20 11 12 10 22 25 29 30 13 10 19 10 16 19 22 22 15 11 18 22 18 26 30 28 30 19 29 33 31 19 |
20 22 21 22 25 19 18 15 14 20 25 28 27 23 20 25 23 15 11 12 21 19 22 18 16 19 21 26 28 27 29 |
31 30 26 27 21 26 29 30 17 15 13 10 13 18 22 27 28 23 11 12 15 16 11 11 10 11 14 10 13 9 7 |
12 10 15 13 10 8 13 10 14 17 16 18 16 20 19 23 25 24 19 14 16 23 23 23 21 18 15 11 5 6
|
8 12 16 15 18 17 14 10 11 7 8 7 4 3 7 13 12 10 –3 –4 –3 2 4 5 5 –1 8 6 7 4 3 |
1 3 0 3 6 6 2 –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 |
Продолжениетаблицы 3.2
|
1998 г. |
Месяц |
|||||||||||
|
День |
Январь |
Февраль |
Март |
Апрель |
Май |
Июнь |
Июль |
Август |
Сентябрь |
Октябрь |
Ноябрь |
Декабрь |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
–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 1 –11 –16 –17 –12 –7 –5 –2 –10 –5 –4 –2 –4 –1
|
–6 –5 –3 –1 0 0 1 3 1 2 1 –1 1 –10 –15 –22 –1 0 1 0 1 3 5 2 –1 –2 –2 –8 –3 –4 0 |
6 4 5 –2 3 0 –3 1 1 3 5 0 –2 –1 0 2 –1 2 0 –2 0 –2 4 5 4 12 7 6 5 2 |
4 7 5 2 7 9 13 22 18 20 17 19 9 12 1 0 9 11 5 4 9 22 28 24 29 28 30 25 21 23 28 |
29 28 20 14 18 20 28 29 23 27 25 23 28 23 25 24 27 30 17 15 10 12 15 12 17 19 24 23 32 22
|
20 23 30 28 33 34 32 30 32 30 27 25 23 28 32 29 27 30 29 30 26 25 29 31 30 31 28 25 29 27 30 |
26 25 30 27 26 29 30 31 30 31 31 32 32 31 32 30 20 18 21 18 16 14 15 15 15 20 22 23 21 20 18 |
15 13 12 13 10 9 11 12 9 15 17 14 9 10 8 6 9 17 18 10 4 2 1 5 8 5 3 4 1 5
|
5 3 4 1 3 1 3 4 2 0 1 1 5 –2 –8 6 4 11 7 4 8 9 7 4 2 –1 4 –7 –10 –5 4 |
5 3 0 –7 –1 2 4 3 –1 7 –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 0 |
Продолжениетаблицы 3.2
|
1999 г. |
Месяц |
|||||||||||
|
День |
Январь |
Февраль |
Март |
Апрель |
Май |
Июнь |
Июль |
Август |
Сентябрь |
Октябрь |
Ноябрь |
Декабрь |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
–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 2 –12 –9 –5 –2 0 3 0 –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 0 –11 –17 –12 –5 2 1 2 0 2 |
2 3 2 5 7 5 2 3 5 6 7 6 7 10 5 4 3 4 1 3 2 5 1 0 10 7 4 2 10 15
|
16 22 24 20 26 25 17 13 4 14 18 10 12 17 26 19 22 19 24 27 30 31 33 31 17 23 24 23 25 30 31 |
24 20 23 22 25 23 28 26 14 10 8 8 10 20 18 9 14 15 16 15 18 22 24 26 25 19 20 24 16 20
|
25 27 24 26 28 30 32 34 36 34 35 36 34 37 31 32 34 35 32 30 30 28 27 27 26 23 26 23 26 30 33 |
32 28 25 23 24 20 18 22 21 22 19 20 22 19 20 20 19 20 22 25 28 24 23 23 23 22 23 24 22 20 21 |
16 15 18 16 13 10 15 16 9 8 10 13 16 17 15 17 14 18 20 15 14 8 5 8 7 2 2 1 0 1 |
6 12 13 10 14 15 10 5 2 0 0 0 3 5 8 11 13 7 6 5 1 0 –1 –2 –2 –12 –6 –4 1 3 4 |
6 3 2 4 1 3 2 –2 –5 –7 –5 –13 –4 1 –2 –5 0 2 –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 1 –5 –11 –10 –6 –19 –26 –36 –39 |