Выполнение регрессии с помощью линии тренда

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

Регрессионный анализ в Ехсеl невероятно прост. Как только данные представлены в графическом виде, регрессия выполняется с помощью нескольких щелчков мыши. Сформулируем практическое правило: всегда следует строить диаграмму, на которой представлена кривая регрессии и данные, чтобы можно было визуально оценить степень совпадения. В том случае, когда регрессия проводится с помощью линии тренда, кривая регрессии автоматически добавляется на диаграмму с соответствующими данными. При построении на диаграмме линии тренда Ехсеl автоматически вычисляет все необходимые суммы, затем находит значения коэффициентов а₁ и a₀, а также квадрат коэффициента корреляции R² (достоверность аппроксимации). Благодаря тому, что линия тренда строится на диаграмме; можно убедиться, насколько хорошо она описывает данные. Уравнение линии тренда и значение R² по умолчанию на диаграмме не отображаются. Чтобы отобразить эту информацию, следует воспользоваться вкладкой Параметры диалогового окна Линия тренда.

Продемонстрируем применение линии тренда на примере данных, рассмотренных в предыдущем разделе. Когда исходный динамический ряд из двух элементов данных создан, можно формировать график и встраивать в него линию тренда.

1. Определить блок исходного динамического ряда, т.е. диапазон ячеек А3:В10.Важно, чтобы в блок вошла и специальная строка меток данных. Щелкнуть левой кнопкой мыши по пиктограмме Мастер диаграмм. Выбрать Точечная диаграмма в стандартном списке типов (слева). В правой части окна выбрать Вид точечной диаграммы (рис.2.5). В нашем примере выбран первый вид Точечная диаграмма с маркерами. Обычно данные на точечной диаграмме отмечаются маркерами, но не соединяются кривыми, чтобы лучше было видно, насколько близко от них проходит аппроксимирующая линия.

Рис. 2.5

2. Щелчок по кнопке Далее> активизирует второй шаг Мастера диаграмм, где предоставляется возможность уточнить или задать исходный диапазон (если он не был задан еще до щелчка по пиктограмме Мастера диаграмм) (рис. 2.6). Здесь же уточняется, как следует воспринимать данные (по столбцам или по строкам). После этого щелчком по кнопке Далее> активизируется третий шаг Мастера диаграмм.

Рис. 2.6

3. На третьем шаге Мастера диаграмм устанавливаются все специальные параметры диаграммы (заголовки, оси, линии сетки, легенда, подписи данных). Выбрать вкладку Заголовки и убрать установленный в ней по умолчанию заголовок "Уровни". В окне Ось X (категорий) и в окне Ось У (значений) ввести наименование осей. Щелкнуть по кнопке Далее> и активизировать четвертый шаг Мастера диаграмм.

4. На четвертом шаге Мастера диаграмм определяется, где разместить диаграмму: на отдельном или на имеющемся листе электронной таблицы. Диаграмма по умолчанию размещается на имеющемся листе, если щелкнуть по кнопке Готово (рис. 2.7).

Рис. 2.7

5. Создать копию диаграммы исходного ряда (не обязательно).

6. Выделить щелчком левой кнопки мыши линию показателя, для которого следует построить тренд (линия должна промаркироваться).

7. Щелкнуть правой кнопкой мыши по маркированной линии для открытия контекстно-зависимого меню.

8. Выбрать опцию Добавить линию тренда... (рис. 2.8). Опция недоступна, если не выделена линия данных – придется вернуться к предыдущему шагу. Если же все в порядке, то откроется основное диалоговое окно Линия тренда, которое предоставляет возможность моделировать тренд, используя пять основных типов регрессии. С учетом пяти форм полиномиальных уравнений тренда, можно получить девять форм уравнений регрессии.

Рис. 2.8

9. В открывшемся диалоговом окне Линия тренда перейти на вкладку Тип и выбрать команду Линейная (рис. 2.9). Чтобы закрыть диалоговое окно Линия тренда и добавить к диаграмме линию тренда, нажмите кнопку ОК.

Рис. 2.9

10. Дважды щелкните на линии тренда, что приведет к отображению на экране диалогового окна Формат линии тренда(рис. 2.10). В окне выбрать вкладку Параметры. В открывшемся диалоговом окне параметров тренда установить флажок «показывать уравнение на диаграмме» и флажок «поместить на диаграмму величину достоверности аппроксимации R²», чтобы включить эти элементы в диаграмму.

Рис. 2.10

Ввести (по желанию) название моделируемого тренда, которое будет помещено в метку диаграммы. Названия даются и по умолчанию, но без указания степени полиномиального тренда. Поэтому в случае моделирования нескольких полиномиальных трендов стоит воспользоваться этой возможностью. Активизировать кнопку ОК.После нажатия кнопки ОКЕхсеl отобразит на диаграмме уравнение регрессии и величину R² (рис.2.11).

Рис. 2.11

Как видно из диаграммы, уравнение регрессии имеет вид c достоверностью аппроксимации 0,6929. Задача решена.

Для получения каждого нового тренда необходимо повторить действия от 1 до 10.

Напомним, что те же результаты были получены с помощью функции НАКЛОН(), ОТРЕЗОК() и КВПИРСОН(). Поскольку идеальной аппроксимации соответствует величина R²=1, можно сделать вывод, что прямая линия не описывает зависимости идеально. Присутствие линии тренда на диаграмме позволяет визуально оценить качество описания. Из примера видно, что линия тренда не точно ложится на данные. Нужно попытаться подобрать другую кривую регрессии, обладающую некоторой кривизной.