Бизнес-аналитика. Сводные таблицы. Часть 2 - [3]
Рис. 4.8. Зарисовка линии тренда
Задание. Сделайте зарисовку линии тренда и вставьте в отчёт.
Далее нам понадобится построить уравнение тренда для моделирования. Добавим в нашу табличку новую колонку — значения на линии тренда:
Начальное значение = 2
Конечное значение = 4.
Выделяем колонки дней и значений и строим график:
Insert — Charts — Insert Scatter (X, Y) or Bubble Chart — Scatter — Scatter with Straight Lines.
Всплывающая подсказка сообщает, что такой график используют, когда есть два набора данных (рис. 4.9). У нас как раз два набора — дни и значения.
Рис. 4.9. Вставка диаграммы
Задание. Постройте график тренда.
Появляется график (рис. 4.10). Рассмотрим его поподробней. Вид довольно странный. Это не совсем то, что мы хотели и что мы ожидали. Даже совсем не то. Мы хотели увидеть номера дней по оси «иксов», а значения по оси «игреков».
Вот для чего нужна зарисовка! И просто мысленное представление будущих результатов. Мы сразу заметили отличие от того, что должно быть.
В таблице данных для графика выделены две строки. То есть Excel решил, что у нас данные расположены по строкам, а не по столбцам.
Рис. 4.10. Первоначальный вид графика
Задание. Рассмотрите полученную диаграмму.
Исправим выбор данных для графика (рис. 4.11). Щёлкаем по графику и выбираем в контекстном меню
Select Data.
Рис. 4.11. Выбор данных для графика
Появляется диалоговое окно
Select Data Source.
Переходим к редактированию источника данных (рис. 4.12):
Select Data Source — Legend Entries (Series) — Edit.
Рис. 4.12. Редактирование источника данных
Появляется диалоговое окно
Edit Series.
Указываем, что наши данные расположены по столбцам (рис. 4.13):
Series X values;
Series Y values.
Нажимаем ОК и получаем более приемлемый вид графика.
Рис. 4.13. Выбор данных по столбцам
Задание. Установите расположение данных для графика по столбцам.
Ещё немного, и мы получим уравнение тренда. Графики позволяют быстро получить уравнение по точкам (рис. 4.14).
Щёлкаем по графику и нажимаем на кнопку [+] справа от графика. Выбираем в меню
Chart Elements — Trendline — More options.
По умолчанию линия строится в виде прямой линии и соответствующей линейной функции.
Рис. 4.14. Настройка линии тренда
В правой части окна Excel появляется раздел настройки графика тренда (рис. 4.15). Устанавливаем следующие настройки:
Format Trendline — Trendline Options — Linear — Display Equation on chart.
Заодно и название графика зададим. Чтобы было понятно читателю, что мы тут изобразили.
Рис. 4.15. Вывод уравнения тренда
Получаем уравнение тренда на графике. Можно его немного подвинуть. Перетащим уравнение мышкой на свободное место (рис. 4.16).
Итак, вот наше уравнение:
y = 0,0027 x — 116,25.
Рис. 4.16. Уравнение и линия тренда
Задание. Включите вывод уравнения тренда на график
Всё вроде бы хорошо, но коэффициент при «иксе» содержит всего два значащих разряда. Нули перед ними не дают особой точности. А вот свободный член уравнения даёт пять значащих разрядов. Исправить вид графика будет довольно сложно.
Мы пойдём другим путём. Вызовем надстройку «Анализ данных» (рис. 4.17) и попросим построить нам уравнение регрессии:
Data — Analysis — Data Analysis — Regression.
Что это такое и как это работает — мы разбирали в одной из предыдущих работ [2].
Появляется диалоговое окно
Regression.
Указываем диапазоны ячеек для «игреков» и для «иксов»:
— Input Y Range;
— Input X Range.
Чтобы всё запутать, вначале нас просят ввести «игреки». Но мы не поддаёмся на провокации и делаем всё правильно.
Ещё нужно указать, куда выводить результаты анализа. Указываем на свободное место.
Нажимаем ОК.
Рис. 4.17. Построение регрессии
Задание. Вызовите надстройку «Анализ данных» и выберите построение регрессии.
Рассмотрим результаты регрессионного анализа (рис. 4.18).
Среди большого количества цифр нас будет интересовать раздел с коэффициентами. Напомним, кто есть кто:
Intercept — свободный член уравнения
X Variable 1 — коэффициент регрессии, то есть коэффициент при «иксе». В нашем случае, при переменной t, которая обозначает время, номер дня.
Теперь можем записать наше уравнение тренда более точно. Оставляем по пять значащих разрядов в каждом коэффициенте:
y = —116,25 +0,0027435 t.
Последний разряд округляем.
Обратите внимание, как выглядят ЗНАЧАЩИЕ ЦИФРЫ. Перед ними и после них может быть много нулей, которые могут и не содержать полезной информации.
Кстати, это пример ситуации, когда ДАННЫЕ и ИНФОРМАЦИЯ — не одно и то же. Много данных в виде цифр — это не обязательно много полезной информации. Это просто цифры. А информация должна быть ПОЛЕЗНОЙ для дела.
Рис. 4.18. Результаты регрессионного анализа
Задание. Запишите уравнение тренда с точностью до пяти значащих цифр.
4.5. Сезонные колебания цен
Сезонные колебания — это изменения с периодом в один год. То есть двенадцать месяцев, или примерно 365 дней. Сезон — это времена года и всё, что с ними связано.
Причина сезонных колебаний цен — это изменение количества товаров, которое предлагается на рынке. В экономике это называется ПРЕДЛОЖЕНИЕ. Понятно, что сразу после сбора урожая сельскохозяйственной продукции много, и цены обычно снижаются. А вот когда запасы подходят к концу, цена может вырасти. Эта картина повторяется каждый год.
