Обзор инструментов и функций программы MS EXCEL, связанных с анализом тренда

Инструменты и функции анализа степени («тесноты») связи случайных величин, то есть методы корреляционного анализа, можно использовать для определения тренда и степени его статистической значимости [28]. Только в этом случае рассматривается связь не произвольных величин, как в корреляционном анализе, а элементов одного временного ряда (см. § 1.1):

- значения времени (или, например, номера обрабатываемой детали), x_i;

- значения контролируемой характеристики (размера, свойства, шероховатости и т.д.), соответствующей этому времени или номеру (y_i).

Аналитические характеристики «тесноты» связи этих элементов временного ряда (коэффициенты ковариации, коэффициенты корреляции, коэффициенты детерминации) играют ту же роль установления тесноты связи и достоверности связи величин x_i и y_i, что и при традиционном корреляционном анализе [28]. Но при этом x_i и y_i - не отдельные случайные величины, а элементы одного ряда.

Функции КОВАР, и КОРРЕЛ ПИРСОН, инструменты анализа «Ковариация» и «Корреляция», функция КВПИРСОН MS EXCEL [28] позволяют решать эти задачи. При этом если в одном столбце (или строке) приводятся временные характеристики элементов ряда, то в параллельном столбце (или строке) должны стоять соответствующие значения контролируемой характеристики. И эти столбцы (или строки) вводят как массивы исходных данных в аргументы используемых функций или инструментов.

Степень «тесноты» связи двух элементов временного ряда также можно оценивать по шкале Чеддока [28].

Детальное исследования динамики процессов может проводиться с помощью инструмента анализа «Регрессия» и ряда статистических функций, основанных на методе наименьших квадратов [28]. Может рассматриваться линейная регрессия (инструмент «Регрессия», функции ТЕНДЕНЦИЯ, НАКЛОН, ЛИНЕЙН, ПРЕДСКАЗ, ОТРЕЗОК) и нелинейная регрессия (функции ЛТРФПРИБЛ, РОСТ, СТОШУХ). Цели и приёмы использования каждой функции подробно рассмотрены в учебном пособии [28]. Напомним, что если для вывода результатов расчёта функции MS EXCEL требуется не одна ячейка, необходимо предварительно выделить требуемый массив ячеек. После введения всех аргументов необходимо набрать комбинацию клавиш CTRL+SHIFT+ENTER. Там же описан инструмент «Регрессия». Напомним, что для правильной его работы временные характеристики элементов ряда и значения контролируемой характеристики должны быть обязательно представлены в столбцах.

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

Построение линии тренда инициируется нажатием правой кнопки мыши на одну из точек ряда данных предварительно построенного графика (рис. 4.1).

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

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

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

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

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

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

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