- •1. Основные термины и обозначения
- •1.1. Интерфейс программы
- •1.2. Рабочая книга
- •Контрольные вопросы
- •2. Ввод и форматирование данных
- •2.1. Общие положения
- •2.2. Ввод и форматирование текста
- •2.3. Ввод и форматирование чисел
- •2.4. Процедура автозаполнения
- •2.5. Формулы
- •2.6. Встроенные функции
- •2.7. Условное форматирование
- •2.8. Контрольные вопросы
- •3. Графическое отображение данных
- •3.1. Работа с диаграммами
- •3.2. Выбор формы зависимости временного ряда
- •3.3. Контрольные вопросы
- •4. Использование списков (базы данных)
- •4.1. Создание и фильтрация списка
- •4.2. Подведение итогов
- •4.3. Сводные таблицы
- •4.4. Контрольные вопросы
- •5. Анализ данных
- •5.1. Процедура Подбор параметра
- •5.2. Сценарии
- •5.3. Таблицы подстановки данных
- •5.4. Процедура Поиск решения
- •5.5. Контрольные вопросы
- •6. Основы программирования на vba
- •6.1. Общие положения
- •6.2. Макросы и редактор vba
- •6.3. Некоторые приемы программирования
- •6.4. Использование элементов управления форм в листах Excel
- •6.5. Контрольные вопросы
- •Библиографический список
- •Оглавление
3.2. Выбор формы зависимости временного ряда
Выбор формы зависимости временного ряда означает, что нужно подобрать такую функцию, которая наилучшим образом отображает основную тенденцию развития временного ряда. Эта функция называется уравнением тренда. Метод, встроенный в Excel, позволяет определить параметры только линейных уравнений или уравнений, приводимых к линейному виду путем преобразования переменных.
Всего таких функций пять: линейная, логарифмическая, степенная, экспоненциальная и полиномы разных порядков. Соответственно в Excel можно построить пять трендовых кривых, не считая скользящую среднюю. Для выбора наиболее адекватного вида уравнения тренда используется величина достоверности аппроксимации R2. Наиболее надежна та линия тренда, для которой значение R2 равно или близко к 1.
Линии тренда графически иллюстрируют тенденцию изменения рядов данных и используются для задач краткосрочного прогнозирования и экстраполяции. Их можно продолжать за пределы уже известных данных и показывать тенденцию их развития.
Р
Рис. 13. Динамика
заработной платы в России
Для графического представления временного ряда использованы данные второй и третьей строки. На вкладке Вставка выбран тип диаграммы Точечная в группе Диаграммы, а затем – вид графика (маркеры). В результате таких действий получен график, представленный на рис. 13 (легенда: Зарплата).
Добавление линии тренда можно осуществить, выбирая в контекстном меню диаграммы пункт Добавить линию тренда. В диалоговом окне Формат линии тренда из имеющихся вариантов выбираем наиболее две наиболее подходящие функции: полиномиальная и степенная. В полиномиальной зависимости рассмотрим полином второй степени.
Для определения параметров модели в диалоговом окне Формат линии тренда необходимо установить флажок Показывать уравнение на диаграмме, рис. 14. В этом же окне устанавливается параметр вперед на, который дает результат прогнозирования на один год вперед.
Д
Рис. 14. Диалоговое
окно Формат
линии тренда
Прогнозирование на основе построенной модели возможно только в том случае, если модель адекватна объекту-оригиналу. Лучшим способом проверки адекватности модели является сравнение прогнозного и фактического значений.
Рассмотрим расчёт точечного прогноза. В точечном прогнозе указывается единственное значение прогнозируемого показателя. Для этого в полученную нами модель y = 97,713x2 + 201,48x + 767,88 подставим временной период x, равный 10. Таким образом, получим, что в 2007 г. значение прогнозируемого показателя равно 12 553 руб.
Мы можем сравнить наш прогноз с фактическим значением показателя в 2007 г. Среднемесячная заработная плата в этом году составила 13 593 руб., что расходится с нашим прогнозом. Определим точность нашего прогноза. Прогноз составил 92,3 % от фактического уровня заработной платы. То есть ошибка прогноза составила 7,7 %.
Задание 10. 1. Выберите лист Тренд. Подберите функцию, которая наилучшим образом отображает основную тенденцию развития временного ряда (средние цены производителей сельскохозяйственной продукции) в соответствии с индивидуальным вариантом с краткосрочным результатом прогнозирования (источник информации http://statistika.ru/stat/Small-Busines):
1) зерновые культуры,
2) подсолнечник,
3) картофель,
4) овощи,
5) помидоры открытого грунта,
6) огурцы открытого грунта,
7) лук репчатый,
8) капуста,
9) свекла, морковь и другие корнеплоды,
10) плоды и ягоды,
11) виноград,
12) бахчевые культуры,
13) скот и птица (в живом весе),
14) крупный рогатый скот,
15) овцы и козы,
16) свиньи,
17) птица,
18) молоко.
2. Результаты оформите в виде отчета в документе Word с возможностью редактирования данных и диаграмм в Excel.