 |
Методические указания.
|
|
|
|
1. В MS Excel создайте рабочую книгу с листами: Прогнозирование, Линейная, Логарифмическая, Полиномиальная, Степенная, Экспоненциальная и оформите лист Прогнозирование как показано на рис. 1.
Рис. 1 – Экранная форма задания 2
Для правильности последующих вычислений в Excel необходимо, чтобы значения периодов были представлены их номерами, начиная с 1 (ячейки В9:В19).
2. Исходным пунктом моделирования трендов является построение диаграммы. На основе исходных данных, представленных в таблице, постройте точечную диаграмму с маркерами.
Для построения следует использовать Мастер диаграмм (Вставка / Диаграммы). Выберите подтип диаграммы «Точечная с маркерами». В качестве диапазонов значений для построения диаграммы выделите диапазон ячеек А8:В19. Обозначьте ось Х заголовком «Номер года», а ось У - заголовком «шт.». Расположите легенду снизу. Поместите диаграмму на имеющемся листе (рис. 2, 3).
Рис. 2 – Выбор подтипа диаграммы
Рис. 3 – Диаграмма исходных данных
3. Предварительно занесите график в буфер обмена и скопируйте его в начало других пяти листов (Линейная, Логарифмическая, Полиномиальная, Степенная, Экспоненциальная). Если у вас в книге недостает листов, выполните их вставку.
4. Постройте линейный тренд для диаграммы. Для этого необходимо:
1) установить указатель мыши на любой маркер диаграммы и щелкнуть правой кнопкой мыши так, чтобы все маркеры были выделены другим цветом и появилось контекстное меню;
2) в контекстном меню выполнить команду Добавить линию тренда;
3) в диалоговом окне Формат линии тренда выбрать с помощью селекторной кнопки Линейная (рис.4);
4) установить следующие параметры: название аппроксимирующей кривой: автоматическое, прогноз: вперед на 1 период;
|
|
|
5) показывать уравнение на диаграмме: установите флажок;
6) поместить на диаграмму величину достоверности аппроксимации: установите флажок;
7) подтвердить действия нажатием кнопки «Закрыть» (см. рис. 4).
Рис. 4 – Диалоговое окно «Формат линии тренда»
5. Перейдите на лист Логарифмическая. Постройте аналогичным образом логарифмический тренд для диаграммы (рис. 5).
Рис. 5 – Логарифмический тренд
6. Постройте полиномиальный тренд для диаграммы на листе Полиномиальный (рис 6). Не рекомендуется использование степени выше 4.
Рис. 6 – Полиномиальный тренд
7. Аналогичным образом построить степенной и экспоненциальный тренды для диаграммы на соответствующих листах книги Excel (рис. 7, рис.8).
Рис. 7 – Степенной тренд
Рис. 8 – Экспоненциальный тренд
Конечный результат моделирования должен оцениваться пользователем с точки зрения здравого смысла на основе неформального комплекса знаний об условиях развития процесса, о допустимых предельных значениях показателя и т.п. В Excel для анализа трендов автоматически выводится только коэффициент детерминации (R2). Статистики-практики применяют метод сверки контрольных сумм выровненного (сглаженного по тренду) ряда признака с суммой значений исходного ряда. Однако для подсчета этих сумм сначала необходимо построить ряды выровненных значений показателя по найденным уравнениям трендов.
8. Перейдите на лист Прогнозирование.
Скопируйте формулы трендов и коэффициенты аппроксимации с диаграмм и вставьте их в соответствующие ячейки (D4:H5) как показано на рис. 9.
9. Введите формулы для вычисления значений аппроксимирующих
функций в соответствующие ячейки D9, E9, F9, G9, H9 (см. рис. 9).
Скопируйте формулы вниз по столбцам.
10. Произведите подсчет контрольных сумм в ячейках С20:Н20 (см. рис. 9).
|
|
|
Рис. 9 – Экранная форма листа «Прогнозирование»
после копирования формул
В результате получили множество числовых рядов исходных данных, сглаженных по исследуемым трендам (D9:D19; E9:E19; F9:F19; G9:G19; H9:H19), множество вспомогательных контрольных сумм (D20:H20) для выявления наилучшего тренда путем сверки их с главной контрольной суммой (C20).
11. Поместите выводы из анализа полученных результатов
исследования динамики продаж с помощью аппроксимации на
листе Прогнозирование (рис. 10). Для создания текстовой области можно использовать команду Вставка / Надпись. Проанализировать построенные графики можно, например, следующим образом:
|
|
|
