Цель: Освоить расчет относительных и абсолютных показателей динамики, построение графиков динамики судимости и линии тренда, прогнозирование изучаемых показателей.
Задачи:
1. Произвести расчеты показателей динамики, используя средства приложения MS Office табличного процессора Excel.
2. Построить график динамики судимости и провести выравнивание ряда динамики с помощью уравнения линейной регрессии.
3. Построить линию тренда и сделать прогноз на 2 года вперед.
Исходные статистические данные:
Таблица 1
Осужденные по приговорам судов общей юрисдикции Российской Федерации, вступившим в законную силу
Годы Всего
1986 797286
1987 580074
1988 427039
1989 436988
1990 537643
1991 593823
1992 661392
1993 792410
1994 924754
1995 1035807
1996 1111097
1997 1013431

Порядок работы
1. Откроем новый файл MS Excel и сохраним файл, указав в имени файла свою фамилию. Создим в Excel табл. 2. Набрать общий заголовок к таблице (отвечающий на вопросы Что? Где? Когда?) в шапку (наименование столбцов) табл. 2 в графу 3 «Осужденные по приговорам судов общей юрисдикции, чел.». Занести в графу «Годы» два первых значения, далее «протянуть» автозаполнением согласно варианту. В следующую графу перенести абсолютные значения числа осужденных.
2. Расчеты темпов роста, абсолютных и относительных приростов цепным и базисным способами произведем с помощью занесения формул в первые свободные ячейки соответствующих граф.
Таблица 2
Динамика осужденных по приговорам судов общей юрисдикции Российской Федерации, вступившим в законную силу
№ Год Осужденные по приговорам судов общей юрисдикции, чел. Абс.прирост, чел. Темп роста, % Темп прироста, % Абсолютное значение в 1 % прироста, чел.
цепной базисный цепной базисный цепной базисный
1 2 3 4 5 6 7 8 9 10
1 1986 797286 - - - 100 - 0 -
2 1987 580074 -217212 -217212 72,76 72,76 -27,24 -27,24 7972,86
3 1988 427039 -153035 -370247 73,62 53,56 -26,38 -46,44 5800,74
4 1989 436988 9949 -360298 102,33 54,81 2,33 -45,19 4270,39
5 1990 537643 100655 -259643 123,03 67,43 23,03 -32,57 4369,88
6 1991 593823 56180 -203463 110,45 74,48 10,45 -25,52 5376,43
7 1992 661392 67569 -135894 111,38 82,96 11,38 -17,04 5938,23
8 1993 792410 131018 -4876 119,81 99,39 19,81 -0,61 6613,92
9 1994 924754 132344 127468 116,70 115,99 16,70 15,99 7924,1
10 1995 1035807 111053 238521 112,01 129,92 12,01 29,92 9247,54
11 1996 1111097 75290 313811 107,27 139,36 7,27 39,36 10358,07
12 1997 1013431 -97666 216145 91,21 127,11 -8,79 27,11 11110,97
Итого: 8911744

3. Построим график динамики судимости. Для этого:
а) выделим ячейки с значениями в столбце «годы» и абсолютное число осужденных;
б) в меню найдем Вставка – Диаграмма – «Точечная» и выберем внешний вид диаграммы в виде графика;
в) далее двигаемся по Мастеру диаграмм и его закладками;
г) в закладке «Заголовки» даем название диаграммы, соответствующее названию статистической таблицы, название оси Х и оси Y;
д) уберем легенду и поставим основные линии сетки для осей;
е) поместим диаграмму на отдельном листе и дадим листу соответствующее название.

4. Проведем выравнивание ряда динамики с помощью уравнения линейной регрессии, заполнив предварительно таблицу 3.
Уравнение линейной регрессии определяется формулой: .
Для нахождения параметров а и b линейной регрессии используется метод наименьших квадратов (МНК), согласно которому для a и b находятся из системы нормальных уравнений:

Необходимые данные берем из таблицы 3.

Решив систему, находим:
a=408805;
b= 51360.
Тогда уравнение линейного тренда будет иметь вид:
Yt= 408805 +51360×t
Дополнительно рассчитаем среднюю ошибку прогноза:
А = 270,89/12*100 = 18,4%

Таблица 3
№ год Осужденные по приговорам судов общей юрисдикции, чел. Y Показатели для расчета параметров уравнения линейной регрессии Прогнозный показатель, Yпр, чел. Ошибка прогноза, %
t t2 Y*t
1 2 3 4 5 6 7 8
1 1986 797 286 1 1 797286 460165 42,28
2 1987 580074 2 4 1160148 511525 11,82
3 1988 427039 3 9 1281117 562885 31,81
4 1989 436 988 4 16 1747952 614245 40,56
5 1990 537 643 5 25 2688215 665605 23,80
6 1991 593 823 6 36 3562938 716965 20,74
7 1992 661 392 7 49 4629744 768325 16,17
8 1993 792 410 8 64 6339280 819685 3,44
9 1994 924 754 9 81 8322786 871045 5,81
10 1995 1035807 10 100 10358070 922405 10,95
11 1996 1 111 097 11 121 12227067 973765 12,36
12 1997 1 013 431 12 144 12161172 1025125 1,15
Итого 8911744 78 650 65270775 8911740 270,89
Даты прогнозных лет 1998 13 1076485
1999 14 1127845

5. Построим график. Для этого в меню Диаграмма выберем Добавить линию тренда. Построим линию тренда.