Возможности использования графических опций программы MS EXCEL для решения задач регрессионного анализа

Линии тренда, аппроксимирующие экспериментальные данные, представляют собой графические представления регрессионных моделей. Построение линии тренда инициируется нажатием правой кнопки мыши на одну из точек ряда данных предварительно построенного графика (рис. 5.1).

Рис. 5.1. Опции, открывающиеся при нажатии правой кнопки мыши на ряд данных

После нажатия «Добавить линию тренда» открываются большие возможности выбора типа регрессионной зависимости: линейного, логарифмического, экспоненциального, степенного или полиномиального до шестой степени включительно (рис. 5.2).

Кроме того, появляется возможность использовать опцию «Линейная фильтрация», которая при правильном использовании должна сглаживать отклонения в данных и более чётко показывать форму линии тренда. Число точек, образующих линию тренда с линейной фильтрацией (Скользящее среднее. Последовательность средних значений, вычисленных по частям рядов данных. На диаграмме линия, построенная по точкам скользящего среднего, позволяет построить сглаженную кривую, более ясно показывающую закономерность в развитии данных.), равно общему числу точек ряда за вычетом числа, указанного для параметра «Точки». Если линейная фильтрация добавляется к точечной диаграмме, она базируется на порядке расположения значений X в диаграмме. Поэтому для получения нужного результата перед добавлением линейной фильтрации, возможно, потребуется отсортировать значения X.

Рис. 5.2. Окно, открывающеся при выборе опции «Добавить линию тренда»

Для более развёрнутой характеристики выбранной модели и построенной линии тренда служит опция «Параметры». Она позволяет «заказать» для выбранной линии тренда представление на диаграмме уравнения регрессии и коэффициента детерминации R². Опция «Параметры» открывается на второй вкладке окна «Линия тренда» (рис. 5.2) или вкладке окна «Формат линии тренда» (рис. 5.3), которое открывается нажатием правой кнопки мыши на уже построенную линию тренда.

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

Уравнение линии тренда округляется, что делает его удобочитаемым, но число десятичных знаков в подписи выбранной линии тренда (и R²) можно изменить в поле «Число десятичных знаков» на вкладке «Число» диалогового окна «Формат подписи данных». (При этом должен быть выбран числовой формат «Числовой».) Окно открывается нажатием правой кнопки мыши на уравнение регрессии (или R²) или через меню «Формат» - «Выделенные подписи данных».

Рис. 5.3. Опция «Параметры» в окне «Формат линии тренда»

Как видно из рис. 5.3, дополнительная опция «Прогноз» позволяет осуществлять экстраполяцию линии тренда на заданное количество «периодов» или «единиц» по оси абсцисс «вперёд» и «назад».

Таким образом, с использованием графических опций можно существенно расширить возможности программы MS EXCEL в области регрессионного анализа и применять нелинейную регрессию в её многообразных вариантах. Однако множественный регрессионный анализ в рамках программы MS EXCEL за исключением, разумеется, возможностей функций ЛГРФПРИБЛ и РОСТ (см. подраздел 4.4), может быть только линейным.