Валентин Арьков - Анализ рядов динамики в электронных таблицах

Чтобы вызвать меню настройки элементов диаграммы, достаточно щёлкнуть по графику и нажать кнопку [+]справа от графика.

Второй вариант — щёлкнуть левой кнопкой по графику и выбрать в верхнем меню:

Design — Add Chart Elements — Chart Title — Above Chart

Design — Add Chart Elements — Legend — Bottom

Рис. 7.6. Заголовок и легенда

Задание. Добавьте заголовок и легенду.

Полученный график можно сравнить с зарисовкой, которую мы уже сделали в предыдущем разделе. Мы видим прямую линию. Значения возрастают от 7 до 22. Всё в порядке.

Задание. Сравните график тренда со своей зарисовкой.

Сформируем столбец для сезонных колебаний (рис. 7.7). Формула и коэффициент нам известны.

Рис. 7.7. Колебания

Задание. Сформируйте столбец для построения графика сезонных колебаний.

Строим график сезонных колебаний в отдельном окне (рис. 7.8). Сравниваем полученный график с нашей зарисовкой. Общая картина должна быть похожа: период колебаний 12, амплитуда колебаний 3.

Рис. 7.8. График колебаний

Задание. Постройте график сезонной составляющей и сравните со своей зарисовкой.

Теперь построим границы размаха колебаний в аддитивной модели. У нас уже есть линия тренда к ней прибавится размах колебаний — амплитуда синусоиды. Она определяется значением коэффициента с в нашем задании (рис. 7.9). Получаем два столбца:

Т — с

Т + с

Рис. 7.9. Размах колебаний

Задание. Постройте столбцы для оценки границ колебаний.

Наносим полученные границы колебаний на график тренда. Настраиваем тип линий, как для линии тренда (рис. 7.10). Можно даже сделать линии чуть потоньше. Сравниваем график с зарисовкой.

Рис. 7.10. Границы размаха колебаний

Задание. Постройте границы колебаний. Сравните полученный график с зарисовкой.

Добавляем колебания к линии тренда — в новой колонке таблицы. Наносим линию на график. Настраиваем тип и цвет линии (рис. 7.11). Колебания вокруг линии тренда должны иметь постоянную амплитуду. Сравниваем с зарисовкой.

Рис. 7.11. Сумма тренда и колебаний

Задание. Постройте график колебаний вокруг тренда. Сравните с зарисовкой.

Смоделируем случайную составляющую с помощью генератора случайных чисел. Вначале построим столбец случайных чисел со стандартным нормальным распределением (нулевое среднее и единичная дисперсия). Озаглавим столбец буквой N.

Вызываем надстройку «Анализ данных»:

Data — Analysis — Data Analysis — Random Number Generation.

Настраиваем генератор случайных чисел (рис. 7.12).

Число случайных переменных (столбцов в таблице):

Number of Variables — 1

Количество случайных чисел (для моментов времени от 0 до 50):

Number of Random Numbers — 51

Выбираем стандартное нормальное распределение:

Distribution — Normal

Parameters:

Mean = 0

Standard deviation = 1

Начальное состояние генератора (любые четыре цифры):

Random Seed — 1234

Указываем первую ячейку столбца, начиная с которой будем располагать случайные числа:

Output options:

Output Range

Рис. 7.12. Настройки генератора

Задание. Сгенерируйте столбец случайных чисел со стандартным нормальным распределением.

Чтобы получить случайную составляющую с нужной сигмой, умножим случайные числа на наш коэффициент d. Результаты разместим в колонке E. Строим график случайной составляющей — на отдельном графике (рис. 7.13). Общая форма графика должна соответствовать нашей зарисовке — размах приблизительно плюс-минус три сигмы.

Рис. 7.13. Случайная составляющая

Задание. Сформируйте столбец значений случайной составляющей. Постройте график. Сравните с зарисовкой.

Для начала оценим размах случайного разброса в модели.

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

Построим два вспомогательных столбца:

T + S — 3d

T + S +3d

Наносим линии на график. Настраиваем цвет и тип линий (рис. 7.14). Сравниваем с зарисовкой.

Рис. 7.14. Границы случайного разброса

Задание. Сформируйте столбцы для оценки границ случайного разброса. Нанесите линии на график. Сравните с зарисовкой.

Наконец мы складываем все три компонента и размещаем их в новой колонке y. Наносим наш ряд динамики на график. Настраиваем цвет и тип линий (рис. 7.15). Внешний вид должен соответствовать зарисовке.