Прогнозирование роста числа правонарушений

Статистические данные роста тяжких преступлений по России приведены в таблице:

Средствами Excel по этим экспериментальным значениям построить теоретическую функцию роста тяжких преступлений и вычислить прогнозируемую численность тяжких преступлений в России в начале 2004 года. Результаты расчета и сравнения теории и эксперимента представить в виде диаграммы Excel.

Указания:

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

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

1. В любых двух свободных ячейках (например, A1, B1) заносятся произвольные допустимые значения параметров .

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

3. Составляется столбец отклонений теории от эксперимента.

4. Составляется столбец квадратов этих отклонений.

5. Квадраты отклонений суммируются в свободную нижнюю ячейку, в которой образуется величина, зависящая от .

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

Эта программа находит значения , при которых сумма квадратов отклонений будет наименьшей и вычисляет число тяжких правонарушений в 2004 году.

Примечание: В исходной таблице размещаются достаточно большие числа (годы и численность преступлений). Для того чтобы не оперировать большими числами следует добавить в таблицу столбец, в котором значения (Год) уменьшены в 100 раз, и столбцы, в которых значения (ЧП Эксперимент) и (ЧП Теория) уменьшены в 1000 раз. Пункты статистического анализа 2 – 6 следует проводить именно для этих столбцов и по ним же следует строить диаграмму. Диаграмму следует строить по столбцам (ЧП Эксперимент) и (ЧП Теория), а в качестве подписи оси абсцисс использовать столбец (Год).

Образец таблицы

Сравнительная диаграмма теории и эксперимента должна иметь вид:

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

ЛИТЕРАТУРА

1.Информатика: Базовый курс / С.В. Симонович и др. - СПб.: Питер, 2001. – 640 с.: ил.

2.Информатика: Учебник / Под ред. Проф. Н.В. Макаровой. – М.: ФиС, 1997, 768 с.

3.Шафрин Ю. Информационные технологии. – М.: Лаборатория базовых знаний, 1999, Т. 1,2.

4.Андрияшин Х.А., Казанцев С.Я. и др. Информатика и математика для юристов// М. ЮНИТИ, 2002.

5.Гельман В.Я. Решение математических задач средствами Excel/ М:, Питер, 2003.

Роганов Е.А. Инфыорматик