Валентин Арьков - Статистический анализ взаимосвязи в Excel

Для специалистов, для серьёзной работы существуют СТАНДАРТЫ. И в стандартах есть грамотные названия, правильные ТЕРМИНЫ. Это язык конкретной профессии. Возьмём, к примеру, такой документ:

ГОСТ 2.303—68 ЕСКД. Линии.

Это отечественный государственный стандарт (сокращённо ГОСТ). Данный стандарт входит в Единую систему конструкторской документации (сокращённо ЕСКД). С конструкторскими чертежами работает инженер-конструктор. По сути, это изображение конструкции изделия, которое затем рабочие будут изготавливать на станках по этим чертежам. В данном стандарте есть только ТОЛЩИНА ЛИНИИ. Ознакомьтесь с текстом стандарта и обратите внимание на выражение ТОЛЩИНА ЛИНИИ.

Линейная регрессия

Пока что мы рассматриваем линейную регрессию, поэтому прямая линия получилась довольно приличная. Для нелинейной регрессии линию придётся рисовать совсем по-другому.

Нелинейная регрессия — это построение уравнения связи и графика с использованием нелинейной функции. Другими словами, здесь работает всё, что не является линейным. Но только с одной оговоркой: уравнение должно выглядеть как сумма или разность. Тогда для каждого члена уравнения программа сможет подобрать коэффициенты.

В данной работе мы рассмотрим самые простые уравнения регрессии, в которых «икс» участвует не только в первой степени, но также в квадрате и в кубе — см. формулы.

Уравнения регрессии

Для построения нелинейной регрессии придётся создать вспомогательные столбцы нелинейных членов уравнения. Для параболы и кубической параболы необходимо в качестве входного интервала Xвыбрать соответствующие столбцы с разными степенями «икса». Эти значения желательно расположить в соседних столбах и выделить как один диапазон.

Обратите внимание на то, в каком порядке идут члены уравнения. По возрастанию или по убыванию степени «икса»? За этим нужно будет следить при формировании вспомогательных столбцов, при вызове функций регрессионного анализа и при чтении результатов.

Итак, на новый лист копируем исходные данные и добавляем колонку квадратов «икса» — рядом с «иксами» в первой степени. Будущие члены нашего уравнения регрессии называются красивым словом РЕГРЕССОРЫ. Это просто «участники» уравнения, для которых подбирают коэффициенты. В этом названии нет негативного оттенка, как и в слове РЕГРЕССИЯ. В статистике слово «регрессия» означает просто «зависимость в среднем». А вот в других дисциплинах регрессия может означать движение назад, противоположность прогрессу или возвращение в прошлое.

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

Украсим заголовки столбцов со степенями «иксов». Выделяем показатель степени — цифру 2 — и вызываем форматирование, нажав кнопку

Font Settings

Настройки шрифта.

Это загадочная стрелочка в правом нижнем углу раздела

Font

Шрифт.

Форматирование текста

Появляется диалоговое окно настройки форматирования содержимого ячеек:

Format Cells

Формат ячеек.

Верхний индекс

Выбираем пункт

Superscript

Верхний индекс.

Нажимаем кнопку OK.

Форматирование заголовка столбца

Теперь заголовки столбцов со степенями «икса» легко читаются.

Исходные данные — регрессоры

Когда исходные данные подготовлены, вызываем надстройку и указываем входные данные, как показано на рисунке. По-прежнему, вначале указываем диапазон Y, а потом диапазон Xв разных степенях. Заголовки столбцов пригодятся при расшифровке результатов. Поэтому выбираем пункт

Labels

Метки.

Начнём с построения параболы. Указываем в качестве факторных признаков два столбца «иксов» — в первой и второй степени.

Нелинейная регрессия

Выбираем автоматическое построение графика «аппроксимации»:

Residuals — Line Fit Plots

Остатки — График подбора.

На экране появляются два загадочных графика.

Графики аппроксимации

Чтобы понять, почему появилось два графика вместо одного, заглянем в исходные данные второй диаграммы. Щёлкнем правой кнопкой по графику и выберем

Select Data

Выбрать данные.

В этой диаграмме использованы два набора данных: y(исходные «игреки») и Predicted y(прогноз значений «игрека» по уравнению регрессии). В окне

Select Data Source

Выбор источника данных

выберем строчку yи нажмём

Edit

Изменить.

Данные для второй диаграммы

Рассмотрим, какие данные были выбраны для диаграммы. Нужные сведения выводятся в окне

Edit Series

Изменение ряда.

Выясняется, что по горизонтальной оси были выбраны квадраты «иксов».

Данные для второй диаграммы

Оставляем только первый график, а второй — удаляем. Теперь настроим наш график аппроксимации и рассмотрим его поподробней.

«График» параболы

Вместо ЛИНИИ регрессии можно видеть странную фигуру, которая утолщается в середине и сужается по краям. Причина в том, что соседние точки на графике соединяются отрезками. Но эти точки идут в том же порядке, как в исходной таблице, а там данные расположены в случайном порядке, не по возрастанию. Придётся кое-что подправить.

Скопируем столбец «иксов» и вставим на место столбца

Observation

Наблюдение

в таблицу

RESIDUAL OUTPUT

ВЫВОД ОСТАТКА.

Столбец Observationсодержит порядковые номера i наблюдений в таблице исходных данных.

Третий столбец

Residuals

Остатки

нам для работы не потребуется — мы его просто удаляем.

Таблица прогнозов

Теперь у нас есть пары соответствующих «иксов» и «игреков». Отсортируем их по возрастанию. Для этого выделяем диапазон данных в столбцах Х и

Predicted y

Предсказанное Y.

Вызываем сортировку через верхнее меню:

Home — Editing — Sort & Filter — Sort Smallest to Largest

Главная — Редактирование — Сортировка и фильтр — Сортировка по возрастанию.

Сортировка по возрастанию

Сортировка ячеек выделенного диапазона выполняется по возрастанию чисел в первом столбце. Это значит, что «иксы» выстраиваются по возрастанию, а соответствующие им «игреки» перемещаются вслед за своими «иксами».

Отсортированные данные

Теперь изменим диапазоны ячеек для Predicted yв данных для графика:

Select Data — Select Data Source — Legend Entries (Series) — Predicted y — Edit.

Выбор данных — Выбор источника данных — Легенда — Предсказанное Y — Изменить.