2.3. Анализ временных рядов с помощью инструмента Excel - мастер диаграмм

При анализе временных рядов широко применяются графические методы. Это объясняется тем, что табличное представление временного ряда и описательные характеристики чаще всего не позволяют понять характер процесса, а по графику временного ряда можно сделать определенные выводы, которые потом могут быть проверены с помощью расчетов.

Визуальный анализ графика временного ряда позволяет сделать выводы о следующем:

• наличии тренда и его характере;

• наличии сезонных и циклических компонент;

• степени плавности или прерывистости изменений последовательных значений ряда после устранения тренда.

Так графический анализ ряда обычно задает направление его дальнейшего анализа.

В Excel для анализа временных рядов можно использовать средство Мастер диаграмм.

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

В результате визуального анализа данных можно попытаться построить математическую модель, описывающую характер зависимости анализируемых величин. В регрессионных моделях зависимая (объясняемая) переменная Y может быть представлена в виде функции f(X₁, Х₂, Х₃, ..., Хт), где Х₁ Х₂, ., Хт -независимые (объясняющие) переменные, или факторы. В зависимости от вида функции f модели делятся на линейные и нелинейные. В зависимости от количества включенных в модель факторов X модели делятся на однофакторные (парная модель регрессии) и многофакторные.

Решение типового варианта лабораторной работы №2

Известна табличная зависимость G(L). Построить линию регрессии и вычислить ожидаемое значение в точках 0, 0.75, 1.75, 2.8, 4.5.

L	0	0,5	1	1,5	2	2,5	3	3,5	4
G	1	2,39	2,81	3,25	3,75	4,11	4,45	4,85	5,25

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

Рис. 2.1

Для того, чтобы рассчитать значения коэффициентов регрессии а и b выделим ячейки К2:L2, обратимся к мастеру функций и в категории Статистические выберем функцию ЛИНЕЙН. Заполним появившееся диалоговое окно так, как показано на рис. 2.2 и нажмем Ок.

Рис. 2.2

В результате вычисленное значение появится только в ячейке К2 (см. рис.2.3). Для того чтобы вычисленное значение появилось и в ячейке L2 необходимо войти в режим редактирования, нажав клавишу F2, а затем нажать комбинацию клавиш CTRL+SHIFT+ENTER.

Для расчета значения коэффициента корреляции в ячейку M2 была введена следующая формула: М2 = КОРРЕЛ(B1:J1;B2:J2) (см. рис. 2.4).

Рис.2.4

Для вычисления ожидаемого значения в точках 0, 0.75, 1.75, 2.8, 4.5 занесем их в ячейки L9:L13. Затем выделим диапазон ячеек M10:M13 и введем формулу:

= ТЕНДЕНЦИЯ(B2:J2;B1:J1;L9:L13).

Для того чтобы вычисленные значения появились и в ячейках M10:M13 необходимо нажать комбинацию клавиш CTRL+SHIFT+ENTER.

Рис. 2.5

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

В качестве имени введем Линия регрессии, в качестве Значения Х: L9:L13, в качестве Значения Y: M9:M13. Далее выделяем линию регрессии, для изменения ее типа щелкаем правой кнопкой мыши и выбираем команду Тип диаграммы (см. рис. 2.6). Для форматирования линии регрессии (можно изменить толщину линии, цвет, тип маркера и т.п.) дважды щелкаем по ней (см. рис. 2.7).

Рис. 2.6	Рис. 2.7

После форматирования графика рабочий лист примет вид, изображенный на рис. 2.8.

Рис. 2.8

Задание на самостоятельную работу: Известна табличная зависимость F(x). Построить линию регрессии и вычислить ожидаемое значение в точках 0.1, 0.75, 1.75, 2.8, 4.5.

x	0	0,5	1	1,5	2	2,5	3	3,5	4
F	2	2,39	2,81	3,25	3,75	4,11	4,45	4,85	5,25