1. Вычисление показателей сезонности в ms Excel

В качестве примера создадим в Excel таблицу " Распределение острых кишечных инфекций (ОКИ) за год".

Рисунок 109. Пример формирования в Excel исходных данных сезонности

1. После запуска Excel сформируйте таблицу, содержащую названия колонок и исходные данные (Рисунок 109)

2. Для изменения ширины столбцов выделите все столбцы и воспользуйтесь командой <Столбец/Автоподбор ширины> из меню <Формат>. Эта команда установит оптимальную ширину для каждого столбца в зависимости от размера содержимого ячеек.

3. Для определения общего числа заболеваний за год введите в ячейку C15 формулу: =СУММ(C3:C14). В результате получите число 326.

4. Для определения среднедневного месячного числа заболеваний введите в ячейку D3 формулу: =C3/В3

5. Заполнение клеток D4:D15 выполните с помощью процедуры копирования формул. Для этого:

• установите указатель ячейки в ячейку D3, выберите команду <Копировать> из меню <Правка>,

• передвиньте указатель ячейки в ячейку D4 и маркируйте ячейки D4:D15,

• нажмите клавишу [Enter].

Рисунок 110. Пример расчета в Excel показателей сезонности

5. Заполните графу "Показатель сезонности в абсолютных числах". Для этого:

• введите в ячейку E3 формулу: =D3/$D$15. Чтобы распространить действие введенной формулы на весь столбец выполните копирование формулы по аналогии с пп.4.

6.Для заполнения графы "Показатель сезонности в процентах"

• в ячейку F3 введите формулу =E3*100;

• Скопируйте формулу в ячейки F4:F15.

6. Для задания в столбцах таблицы нового числового формата:

• выделите соответствующий блок ячеек. выполните команду <Ячейки> из меню <Формат>,

• в панели Число выберите из списка "Числовые форматы" категорию "Числовой" и установите необходимое число десятичных знаков,

• активизируйте кнопку [OK].

Рисунок 111. Пример итоговых показателей сезонности

3. Повышение наглядности динамических рядов. Прогноз динамики.

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

Если динамические ряды содержат значительную случайную ошибку (шум), то можно применить один из двух наиболее простых приемов сглаживания или выравнивания динамических рядов:

- укрупнение интервалов путем суммирования исходных уровней по нескольким интервалам. Например: суммируются числа рождений за 1980,1981 и 1982 годы (84+94+92=270), затем за 1985,1986 и 1987 годы и т.д. (Таблица 103);

- вычисление групповых средних, которые определяются на основе данных по укрупненным интервалам (270/3=90, 263/3=88 и т.д.);

Таблица 103

Сглаживание динамического ряда укрупнением интервалов и скользящим средним

Учетный год	Число рождений	Суммы по 3 годам	Средние по 3 годам	Скользящие средние
1982 1983 1984	84 94 92	270	90,0	- 90,0 89,7
1985 1986 1987	83 91 88	262	87,3	88,7 87,3 87,0
1988 1989 1990	82 90 77	249	83,0	86,7 83,0 82,3
1991 1992 1993	80 90 78	248	82,7	82,3 82,7 -

Укрупнение интервалов или расчет группового среднего внутри этих интервалов позволяет относительно легко повысить наглядность ряда, особенно если большинство «шумовых» составляющих находятся именно внутри этих интервалов. Но в случае если шум не согласуется с этой периодичностью, распределение уровней показателей становится грубым, что ограничивает возможности детального анализа изменения явления во времени. Более точные характеристики получаются, когда используются - скользящие средние. Этот метод - один из самых широко применяемых методов сглаживания показателей временного ряда.

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

Применение скользящего среднего особенно полезно при неясных тенденциях динамического ряда или в ситуациях, когда на показатели сильно воздействуют циклически повторяющиеся выбросы (резко выделяющиеся данные, так называемые интервенции).

Таблица 104

Вычисление скользящего среднего

Годы	Травм	Арифметические операции
1982	84	-
1983	94	(84+94+92)/3= 90,0
1984	92	(94+92+83)/3= 89,7
1985	83	(92+83+91)/3= 88,7
1986	91	(83+91+88)/3= 87,3
1987	88	(91+88+82)/3= 87,0
1988	82	(88+82+90)/3= 86,7
1989	90	(82+90+77)/3= 83,0
1990	77	(90+77+80)/3= 82,3
1991	80	(77+80+90)/3= 82,3
1992	90	(80+90+78)/3= 82,7
1993	78	-

Рисунок 112. Результаты сглаживания методом скользящего среднего

В приведенном примере временной интервал для вычисления скользящего среднего принят равным 3 годам. В результате проведенного сглаживания основная тенденция динамического ряда стала более наглядной. В частности, при оценке динамики травматизма можно утверждать, что наиболее интенсивно показатели травматизма снижались в период с 1984 по 1986 гг. и с 1988 по 1990 гг. Периоды с 1986 по 1988 гг. и с 1990 по 1993 гг. отличались относительной стабильностью, несмотря на размахи колебаний годовых показателей.

В целом, чем больше интервал сглаживания, тем более плавный вид имеет диаграмма скользящих средних. При выборе величины интервала сглаживания необходимо исходить из величины динамического ряда (числа отдельных временных точек) и содержательного смысла отражаемой динамики. Большая величина динамического ряда с большим числом точек наблюдения позволяет использовать более крупные временные интервалы сглаживания (5,7,10 и т.д.). Если процедура скользящего среднего используется для сглаживания не сезонного ряда, то чаще всего величину интервала сглаживания принимают равной 3.

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

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

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

где:- каждое новое сглаженное значение в момент времениt;- сглаженное значение в предыдущий момент времени t-1;- фактическое значение ряда в момент времениt; - параметр сглаживания. Еслиравно 1, то предыдущие наблюдения полностью игнорируются. Еслиравно0, то игнорируются текущие наблюдения. Значения между0, 1 дают промежуточные результаты. Изменяя значения этого параметра можно подобрать наиболее приемлемый вариант выравнивания. Выбор наиболее оптимального значения осуществляется путем анализа полученных графических изображений исходной и выровненной кривых, либо на основе учета суммы квадратов ошибок (погрешностей) вычисленных точек. Более полно практическое использование этого метода представлено далее, в разделе «Обработка динамических рядов и прогноз динамики в MS Excel.».

Одним из самых эффективных считается выравнивание по способу наименьших квадратов. Согласно ему из бесконечного числа линий, которые могли бы быть теоретически проведены между точками, изображающими исходный ряд, выбирается только одна прямая, которая имела бы наименьшую сумму квадратов отклонений исходных (эмпирических) точек от этой теоретической прямой. Практически выравнивание производят либо по уравнению прямой , либо по уравнению параболы. Уравнение параболы второго порядка выглядит следующим образом. В основе выбора параболы для выравнивания лежит предположение о том, что не скорость динамики, а ускорение является постоянной величиной. Гдеа.b иc- постоянные величины,t - .порядковый номер какого-либо периода или момента времени (года и т.п.). С помощью этого уравнения вычисляются необходимые для построения соответствующие данные

Показателем правильности выбора того или иного уравнения аппроксимации служит коэффициент R² . Чем больше его значение приближается к единице, том большее соответствие фактического и выровненного распределений. Максимальное значение, которое R² может принимать в предельном случае, равно 1.

Следует отметить, что при решении проблемы выбора типа прямой или кривой, нельзя исходить из формальных соображений: та линия лучше, которая дает меньшую сумму отклонений эмпирического ряда от теоретического распределения. Выбор кривой может быть обоснованным только на основе глубокого знания сути исследуемого явления.

Современные программы статистической обработки данных позволяют получать различные теоретические кривые в автоматическом режиме, без каких-либо усилий со стороны исследователя. Имея эти результаты можно проводить математическую экстраполяцию, то есть давать прогноз показателей в продолжение проанализированного периода, или проводить интерполяцию рядов, то есть определять утраченные или отсутствующие показатели в любой точке середины интервала анализируемого временного ряда.

Говорить о достоверности статистических прогнозов динамики каких-либо явлений можно лишь при сохранении общих тенденций, то есть при наличии определенной степени инерционности явлений. Здесь имеется в виду инерционность статистических взаимосвязей, которая обеспечивает сохранение в общих чертах механизма формирования явления, и инерционность характера динамики процесса (темп, направление, устойчивость) на протяжении достаточно длительных отрезков времени. При этом существует закономерность: чем на больший период времени вперед (или назад) производится экстраполяция данных, том ниже точность прогноза. Особенно резко снижается точность прогноза при значениях R² 0,6.