Расчет коэффициента детерминации в Microsoft Excel
Одним из показателей, описывающих качество построенной модели в статистике, является коэффициент детерминации (R^2), который ещё называют величиной достоверности аппроксимации. С его помощью можно определить уровень точности прогноза. Давайте узнаем, как можно произвести расчет данного показателя с помощью различных инструментов программы Excel.
Вычисление коэффициента детерминации
В зависимости от уровня коэффициента детерминации, принято разделять модели на три группы:
В последнем случае качество модели говорит о невозможности её использования для прогноза.
Выбор способа вычисления указанного значения в Excel зависит от того, является ли регрессия линейной или нет. В первом случае можно использовать функцию КВПИРСОН, а во втором придется воспользоваться специальным инструментом из пакета анализа.
Способ 1: вычисление коэффициента детерминации при линейной функции
Прежде всего, выясним, как найти коэффициент детерминации при линейной функции. В этом случае данный показатель будет равняться квадрату коэффициента корреляции. Произведем его расчет с помощью встроенной функции Excel на примере конкретной таблицы, которая приведена ниже.
-
Выделяем ячейку, где будет произведен вывод коэффициента детерминации после его расчета, и щелкаем по пиктограмме «Вставить функцию».
Таким образом, функция имеет два оператора, один из которых представляет собой перечень значений функции, а второй – аргументов. Операторы могут быть представлены, как непосредственно в виде значений, перечисленных через точку с запятой (;), так и в виде ссылок на диапазоны, где они расположены. Именно последний вариант и будет использован нами в данном примере.
Устанавливаем курсор в поле «Известные значения y». Выполняем зажим левой кнопки мышки и производим выделение содержимого столбца «Y» таблицы. Как видим, адрес указанного массива данных тут же отображается в окне.
Аналогичным образом заполняем поле «Известные значения x». Ставим курсор в данное поле, но на этот раз выделяем значения столбца «X».
Способ 2: вычисление коэффициента детерминации в нелинейных функциях
Но указанный выше вариант расчета искомого значения можно применять только к линейным функциям. Что же делать, чтобы произвести его расчет в нелинейной функции? В Экселе имеется и такая возможность. Её можно осуществить с помощью инструмента «Регрессия», который является составной частью пакета «Анализ данных».
-
Но прежде, чем воспользоваться указанным инструментом, следует активировать сам «Пакет анализа», который по умолчанию в Экселе отключен. Перемещаемся во вкладку «Файл», а затем переходим по пункту «Параметры».
Около параметров «Метка» и «Константа-ноль» флажки не ставим. Флажок можно установить около параметра «Уровень надежности» и в поле напротив указать желаемую величину соответствующего показателя (по умолчанию 95%).
В группе «Параметры вывода» нужно указать, в какой области будет отображаться результат вычисления. Существует три варианта:
Способ 3: коэффициент детерминации для линии тренда
Кроме указанных выше вариантов, коэффициент детерминации можно отобразить непосредственно для линии тренда в графике, построенном на листе Excel. Выясним, как это можно сделать на конкретном примере.
Например, для нашего случая опытным путем удалось установить, что самый высокий уровень достоверности имеет полиномиальный тип линии тренда второй степени. Коэффициент детерминации в данном случае равен 1. Это говорит о том, что указанная модель абсолютно достоверная, что означает полное исключение погрешностей.
Мы рады, что смогли помочь Вам в решении проблемы.
Помимо этой статьи, на сайте еще 12742 полезных инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
Поделиться статьей в социальных сетях:
Еще статьи по данной теме:
получены по формуле .y=x+sinx
Берем модель y=a+bx и находим
ВЫВОД ИТОГОВ
P-Значение показывает, что константу надо обнулить a=0 . Снова обращаемся к Регрессии
ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R 0,997002
R-квадрат 0,994013
Нормированный R-квадрат 0,922584
Стандартная ошибка 0,739847
Наблюдения 15
Дисперсионный анализ
df SS MS F Значимость F
Регрессия 1 1272,235 1272,235 2324,254 4,8053E-16
Остаток 14 7,663227 0,547373
Итого 15 1279,898 ? и здесь напахали TSS=288,6771544 и не должно меняться от вида модели.
То, что называется «приплыли»:
R*-квадрат 0,994013 > 0,9736; F*=2324,254 > 479,542.
Однако, основной показатель RSS=7,619 < SSR*=7,663 говорит, что первоначальная модель, все-таки точнее (в смысле МНК).
Могу предположить, что разработчики Регрессии не учли, что при насильственном изменении модели, Теорема о 3-х дисперсиях не выполняется: TSS 288,6771544 > )
У меня не вопрос. А благодарность разработчикам сайта. Спасибо за понятные и доступные разъяснения при построении требуемых данных.
Расчет коэффициента финансовой активности в Excel: формула по балансу
Можно заметить, что значение коэффициента у Новатэка в последнее время стало снижаться. Это связано с ростом операционных показателей компании и снижением корреляции между ценами нефти и газа. В Транснефти наблюдается аналогичная картина, которая связана с тем, что тарифы компании напрямую не связаны с ценами на нефть.
