7 Лабораторная работа 3. Трендовый анализ

Цель работы: освоить элементы трендового анализа временного ряда, познакомиться со статистическими функциями Excel.

Excel имеет специальный аппарат для графического анализа моделей, в том числе построения по заданному в виде таблицы временному ряду {t_i , y_i} аппроксимационных зависимостей (линий тренда) P(t), которые приближенно отражают функциональную связь y=f(t).

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

Линиями тренда можно дополнить ряды данных, представленные на линейчатых диаграммах, гистограммах, графиках, биржевых, точечных и пузырьковых диаграммах. Нельзя дополнить линиями тренда ряды данных на объемных, лепестковых, круговых и кольцевых диаграммах.

Excel позволяет выбрать один из пяти типов линии тренда P(t) – линейный, логарифмический, экспоненциальный, степенной или полиномиальный (2...6 степени) и проверить (по различным критериям), какой из типов лучше всего подходит в данной ситуации.

Критерием может служить или критерий R² (коэффициент детерминации, или достоверность аппроксимации), автоматическое вычисление которого встроено в диалоговое окно Линия тренда,

или квадратичное отклонение

,

обычно используемое в методе наименьших квадратов при аппроксимации табличных функций.

Чем меньше квадратичное отклонение, тем лучше линия тренда аппроксимирует ряд данных. Или, чем ближе коэффициент детерминации к единице, тем лучше тренд.

Задания

Задание 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);

б) подсчитать по этим зависимостям соответствующую теоретическую (трендовую) численность, заполнив столбцы Теория;

в) найти погрешность С–Т (разницу между статистической и трендовой численностью);

г) рассчитать квадратичные отклонения S_i (i=1…4), используя функцию СУММКВ.

Сравнив эти отклонения, выбрать лучший тренд и по нему оценить численность населения в 2000 году.

Примечание. Коэффициенты трендов выдаются с недостаточной точностью, и погрешность проведенных вычислений может оказаться слишком высокой. Поэтому рекомендуется увеличить число знаков после запятой в этих коэффициентах. Для этого, выделив уравнение тренда, следует щелкнуть по «горячей» клавише (Увеличить разрядность) на панели инструментов Форматирование. Необходимое число знаков подбирается опытным путем: если расчет S по трендовым формулам с коэффициентами, имеющими n+1 знак после запятой, даст значение, отличающееся на 1% от расчета S с n знаками после запятой, то найти S по трендовым формулам с коэффициентами, имеющими n+2 знака после запятой и т.д.

Задание 2. Введя дополнительное данное: значение численности России в 1998 году – 146,2 млн чел., уточнить экстраполяцию, используя только данные 90-х годов. Сравнить полученный результат с решением, полученным в задании 1, и сделать выводы.

Задание 3. По таблице 3.2 построить линию полиномиального тренда, наилучшим образом (по максимальному значению критерия детерминации R²) описывающую дневную температуру в г. Томске для одних и тех же месяцев двух разных лет. Просмотреть полиномы всех возможных степеней, предлагаемых Excel. Построить график изменения значения R² в зависимости от степени полинома.

Определить максимальную (функция МАКС), минимальную (функция МИН) и среднюю (функция СРЗНАЧ) температуру месяца.

С помощью функции СЧЕТЕСЛИ определить, сколько дней в месяце держалась температура ниже средней.

Синтаксис этой функции: