Валентин Арьков - Бизнес-аналитика. Сводные таблицы. Часть 2. Учебное пособие

Рис. 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 .

Последний разряд округляем.

Обратите внимание, как выглядят ЗНАЧАЩИЕ ЦИФРЫ. Перед ними и после них может быть много нулей, которые могут и не содержать полезной информации.