Читать книгу Сводные таблицы Excel. Часть 2. Учебное пособие - Валентин Юльевич Арьков - Страница 10

Далее сформируем ТРЕНД. Это долгосрочная тенденция. Общее направление изменений. В нашей модели тренд – это количество товара в одном чеке, в одной покупке. Будем моделировать постепенный рост покупок в течение нескольких лет.

Для нашей модели тренда нам понадобится два значения из параметров задания:

– Начало – начальное значение на линии тренда;

– Конец – последнее значение на линии тренда;

– Период – интервал времени в годах.

В нулевом варианте мы получили такие параметры:

– Начало = 2;

– Конец = 4;

– Период = 2.

Мы будем моделировать данные за два последних года. На момент написания пособия текущий год 2020. Так что возьмём данные за 2018—2019 годы. То есть наш интервал времени такой:

01.01.2018 – 31.12.2019.

Задание. Определите интервал дат для моделирования.

Напомним, что дата в пакете Excel хранится как порядковый номер дня. Мы будем моделировать даты как целые случайные числа. Но для этого нам нужно определить, какие номера дней соответствуют нашим датам.

Запишем даты начала и конца интервала моделирования. Будем вводить даты так, что Excel догадался, что это даты, а не просто какой-то текст:

2018-01-01

2019-12-31.

Даты распознали как даты, и они выводятся в формате даты.

Скопируем даты в соседние ячейки и установим общий формат (рис. 4.7):

Format Cells – Number – Category – General.

Получаем номера дней:

– начало интервала = 43101

– конец интервала = 43830.

Рис. 4.7. Даты интервала моделирования

Задание. Определите номера дней для своего интервала.

Сделаем зарисовку —как должен выглядеть график. Как должна проходить линия тренда. Рисуем от руки на бумаге, фотографируем и вставляем в наш отчёт (рис. 4.8).

Работу с зарисовками мы уже обсуждали в предыдущих работах. Главное – нужно мысленно представлять себе, что мы ожидаем получить. Тогда можно будет обнаружить грубые ошибки.

Рис. 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. Результаты регрессионного анализа

Задание. Запишите уравнение тренда с точностью до пяти значащих цифр.