Организационная часть (5 минут)

1 2 3

Содержание занятия

Практического занятия (№ 15-4)

ПЛАН-КОНСПЕКТ

Обеспечения деятельности УИС

ФЕДЕРАЛЬНАЯ СЛУЖБА ИСПОЛНЕНИЯ НАКАЗАНИЙ

САМАРСКИЙ ЮРИДИЧЕСКИЙ ИНСТИТУТ

Кафедра управления и информационно-технического

по дисциплине «Информатика и информационные технологии

в профессиональной деятельности»

Тема № 15 «Анализ и прогнозирование в практической деятельности УИС»

для курсантов 1 курса

по направлению подготовки 031001 Правоохранительная деятельность

Разработал:

Ст. преподаватель кафедры

подполковник внутренней службы

______________О.Р. Шебец

Самара 2013

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

Длительность: 2 часа (90 мин.).

Место проведения: учебная аудитория (компьютерный класс).

Методы преподавания: объяснение, выполнение практических заданий на компьютере, демонстрация, частично-поисковый метод.

Материальное обеспечение: персональные компьютеры с выходом в Интернет, видеопроектор, экран.

Вопросы, рассматриваемые на занятии:

1. Методы, применяемые для получения прогноза.

2. Математическая модель прогнозирования, которая принятая в методе скользящего среднего.

3. Математическая модель прогнозирования, которая используется при линейном прогнозе.

4. Метод экспоненциального сглаживания.

Методические рекомендации к проведению занятия:

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

В Excel имеется несколько инструментов для прогнозирования, в основе которых применяются различные математические модели:

- скользящее среднее (в качестве прогноза принимается среднее значение наблюдаемой величины в нескольких последних измерениях) может быть вычислено с помощью функции с именем СРЗНАЧ или надстройки Скользящее среднее;

- линейный прогноз (к полученным значениям величины приближается прямая линия, на основании которой и рассчитывается прогноз) выполняется с помощью функции с именем ТЕНДЕНЦИЯ или надстройки Регрессия;

- нелинейный прогноз (принимается, что значение величины изменяется нелинейно) может быть получен с помощью функции с именем РОСТ;

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

Ко второму вопросу. Метод экспоненциального сглаживания

Предполагается, что наблюдения некоторой величины X, проводятся через равные промежутки времени. Результат наблюдения обозначим X(t), где – t номер наблюдения. Прогноз P(t+1) для следующего момента времени рассчитывается по формуле:

P(t+1) = P(t) + a*(X(t) – P(t))

(1)

где a – константа сглаживания, выбирается обычно от 0,2 до 0,3. Большие значения константы сглаживания ускоряют отклик прогноза на скачок наблюдаемого процесса, но могут привести к непредсказуемым выбросам.

Первый раз после начала наблюдений, располагая лишь одним результатом наблюдений X(1), когда прогноза P(1) нет и формулой (1) воспользоваться еще невозможно, в качестве прогноза P(2) следует взять X(1).

Формула (1) легко может быть переписана в ином виде:

P(t+1) = (1 – a)*P(t) + a*X(t). Теперь видно, что при увеличении константы сглаживания в прогнозе доля последнего наблюдения увеличивается, а доля предыдущих наблюдений убывает.

1. Решение задачи

Представьте, что Вы руководите агентством по прокату автомобилей. По мере приближения зимы Вы замечаете увеличение количества заявок клиентов на транспорт, снабженный багажником для перевозки лыж. Несколько дней спустя после начала проведения исследования в Вашей местности выпало очень много снега и, как следовало ожидать, количество вышеупомянутых заявок резко возросло. Итак, используя результаты выполненных на сегодняшний день наблюдений (в данном случае наблюдение – это количество заявок за день) нам нужно узнать, сколько автомобилей, оборудованных багажником для лыж, необходимо подготовить, чтобы полностью удовлетворить спрос в завтрашний день. Воспользуйтесь Excel для выполнения необходимых расчетов.

АЛГОРИТМ

1. Запустите Excel и щелкните на кнопке Сохранить.

2. С помощь кнопки Создать папку в появившемся окне Сохранение документа создайте на диске d свою рабочую папку и сохраните в ней файл Книга1 под именем Прогноз.xls.

3. Установите во всей таблице шрифт Times New Roman размером 12.

4. Введите в диапазоне А1:А11 заголовок и данные наблюдений, руководствуясь рис. 1.

5. Введите в ячейке B1 заголовок Прогноз.

6. Раскройте пункт меню Сервис. Если в выпавшем подменю нет команды Анализ данных, то выполните команду Сервис, Надстройки. В появившемся окне Надстройки (рис. 2) в списке надстроек установите флажок слева от строки Analysis ToolPak - VBA (функции VBA для работы пакета анализа) и щелкните на кнопке ОК.

Рис. 1. Прогноз по методу экспоненциального сглаживания (константа сглаживанияравна 0,2)

Рис. 2. Установка пакета Анализ данных

7. Выполните команду Сервис, Анализ данных. Появится окно Анализ данных (рис. 3). Пролистайте список инструментов анализа и обратите внимание на то, что их достаточно много.

8. В списке инструментов анализа выберите строку Экспоненциальное сглаживание и щелкните на кнопке ОК. Появится окно Экспоненциальное сглаживание (рис. 4), которое следует заполнить.

9. Установите курсор в поле Входной интервал. Выделите интервал входных данных A1:A12. В поле Входной интервал появится строка $A$1:$A$12.

Рис. 3. Выбор инструмента анализа

10. Проведите расчет при значении константы сглаживания a, равном 0,2. Для этого введите в поле Фактор затухания значение, равное1 – а, которое в данном случае равно 0,8.

11. Установите флажок в поле Метки, означающий, что первая ячейка входного интервала является заголовком.

Рис. 4. Заполнение окна Экспоненциальное сглаживание

12. Установите курсор в поле Выходной интервал. Выделите ячейку B2 – первую ячейку выходного интервала. В поле Выходной интервал появится строка $B$2.

13. Установите флажок в поле Вывод графика и щелкните на кнопке ОК. На рабочем листе (см. рис. 1) будет выведен прогноз и диаграмма, позволяющая сравнить прогноз с фактическими данными.

14. Установите в диапазоне ячеек B3:B12 числовой формат с двумя разрядами дробной части.

15. Проанализируйте полученные результаты. В ячейке A11 записано количество заявок, сделанных за десятый день наблюдений.В ячейке B11 записан прогноз на десятый день, полученный сглаживанием на основании данных предыдущих девяти дней наблюдений. В ячейке B12 записан прогноз количества ожидающихся заявок в следующий день. А сколько их будет сделано на самом деле, станет известно только в следующий день. Запись в ячейке B2 означает недостаток данных.

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

Рис. 5. Предупреждение Excel о предстоящей перезаписи данных

16. Повторите получение прогноза, заменив значение 0,2 константы сглаживания наибольшим рекомендуемым значением 0,3. Появившееся окно (рис. 5) с предложением перезаписать данные закройте щелчком на кнопке ОК.

17. Рабочий лист Вашей таблицы должен соответствовать изображению на рис. 6. Можно заметить, что теперь прогноз быстрее отслеживает скачок фактической функции.

Рис. 6. Прогноз по методу экспоненциального сглаживания (константа сглаживания равна 0,3)

18. Снова повторите получение прогноза, заменив значение 0,3 константы сглаживания на 0,1. Анализируя рабочий лист (рис. 7) Вы убедитесь, что в последнем случае качество прогноза заметно возросло, а устойчивость прогноза сохранилась.

Рис. 7. Прогноз по методу экспоненциального сглаживания (константа сглаживания равна 0,9)

Рис. 8. Обращение к справке

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

19. Для обращения к справке нажмите клавишу F1. Если использование Помощника не предусмотрено, то на вкладке мастера ответов (рис. 8) в поле Выберите действие введите название инструмента анализа, например Экспоненциальное сглаживание и щелкните на кнопке Найти. Если Помощник используется, то введите свой вопрос в аналогичном окне Помощника. Затем щелкните на кнопке Найти. В появившемся списке разделов, связанных с интересующим Вас инструментом анализа, выберите нужный Вам раздел, и соответствующая справка Excel будет выведена на экран (рис. 9).