3.2.5 Функции и инструменты ms excel, предназначенные для расчёта коэффициентов ковариации, корреляции и детерминации

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

Рис. 2.3. Аргументы функции КОРРЕЛ

В MS EXCEL имеются также инструменты анализа «Ковариация» и «Корреляция», сходные своими диалоговыми окнами (последнее представлено на рис. 2.4). Они служат той же цели, что и соответствующие функции, но в отличие от функций в данном случае задаётся общий входной интервал с разделением анализируемых переменных по строкам или столбцам. При этом коэффициенты корреляции (или ковариации) рассчитываются для всех сочетаний задаваемых строк или столбцов. Таким образом, формируется так называемая «матрица корреляций» (или аналогично - «матрица ковариаций»).

В инструментах анализа имеется возможность выбора места выходного диапазона (рис. 2.4). При выводе результатов на текущий лист («Выходной интервал») введённая ссылка на ячейку указывает левую верхнюю ячейку выходного диапазона.

Рис. 2.4. Диалоговое окно инструмента анализа «Корреляция»

Статистическая функция КВПИРСОН (рис. 2.5) позволяет определять квадрат коэффициента Пирсона r² для парной линейной зависимости. Он представляет собой коэффициент детерминации для такого рода зависимости. Практическое использование коэффициента детерминации, показывающего степень аппроксимации экспериментальных точек аналитической зависимостью, особенно эффективно, когда эти аналитические зависимости можно оперативно изменять. Тогда появляется возможность выбрать такую зависимость, для которой установлен максимальный коэффициент детерминации. Эти возможности открываются при использовании графических опций MS EXCEL (глава 5).

Рис. 2.5. Аргументы функции КВПИРСОН