Коэффициент детерминации как найти в excel

Корпорация "Центр"

Расчет коэффициента детерминации в Microsoft Excel

Коэффициент детерминации в Microsoft Excel

Одним из показателей, описывающих качество построенной модели в статистике, является коэффициент детерминации (R^2), который ещё называют величиной достоверности аппроксимации. С его помощью можно определить уровень точности прогноза. Давайте узнаем, как можно произвести расчет данного показателя с помощью различных инструментов программы Excel.

Вычисление коэффициента детерминации

В зависимости от уровня коэффициента детерминации, принято разделять модели на три группы:

  • 0,8 – 1 — модель хорошего качества;
  • 0,5 – 0,8 — модель приемлемого качества;
  • 0 – 0,5 — модель плохого качества.

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

Выбор способа вычисления указанного значения в Excel зависит от того, является ли регрессия линейной или нет. В первом случае можно использовать функцию КВПИРСОН, а во втором придется воспользоваться специальным инструментом из пакета анализа.

Способ 1: вычисление коэффициента детерминации при линейной функции

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

Таблица с данными в Microsoft Excel

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

Переход в Мастер функций в Microsoft Excel

Запускается Мастер функций. Перемещаемся в его категорию «Статистические» и отмечаем наименование «КВПИРСОН». Далее клацаем по кнопке «OK».

Переход в окно аргументов функции КВПИРСОН в Microsoft Excel

Происходит запуск окна аргументов функции КВПИРСОН. Данный оператор из статистической группы предназначен для вычисления квадрата коэффициента корреляции функции Пирсона, то есть, линейной функции. А как мы помним, при линейной функции коэффициент детерминации как раз равен квадрату коэффициента корреляции.

Синтаксис этого оператора такой:

Таким образом, функция имеет два оператора, один из которых представляет собой перечень значений функции, а второй – аргументов. Операторы могут быть представлены, как непосредственно в виде значений, перечисленных через точку с запятой (;), так и в виде ссылок на диапазоны, где они расположены. Именно последний вариант и будет использован нами в данном примере.

Устанавливаем курсор в поле «Известные значения y». Выполняем зажим левой кнопки мышки и производим выделение содержимого столбца «Y» таблицы. Как видим, адрес указанного массива данных тут же отображается в окне.

Аналогичным образом заполняем поле «Известные значения x». Ставим курсор в данное поле, но на этот раз выделяем значения столбца «X».

После того, как все данные были отображены в окне аргументов КВПИРСОН, клацаем по кнопке «OK», расположенной в самом его низу.

Окно аргументов функции КВПИРСОН в Microsoft Excel

  • Как видим, вслед за этим программа производит расчет коэффициента детерминации и выдает результат в ту ячейку, которая была выделена ещё перед вызовом Мастера функций. В нашем примере значение вычисляемого показателя получилось равным 1. Это значит, что представленная модель абсолютно достоверная, то есть, исключает погрешность.
  • Результат расчета функции КВПИРСОН в Microsoft Excel

    Способ 2: вычисление коэффициента детерминации в нелинейных функциях

    Но указанный выше вариант расчета искомого значения можно применять только к линейным функциям. Что же делать, чтобы произвести его расчет в нелинейной функции? В Экселе имеется и такая возможность. Её можно осуществить с помощью инструмента «Регрессия», который является составной частью пакета «Анализ данных».

      Но прежде, чем воспользоваться указанным инструментом, следует активировать сам «Пакет анализа», который по умолчанию в Экселе отключен. Перемещаемся во вкладку «Файл», а затем переходим по пункту «Параметры».

    Переход в окно параметров в Microsoft Excel

    В открывшемся окне производим перемещение в раздел «Надстройки» при помощи навигации по левому вертикальному меню. В нижней части правой области окна располагается поле «Управление». Из списка доступных там подразделов выбираем наименование «Надстройки Excel…», а затем щелкаем по кнопке «Перейти…», расположенной справа от поля.

    Переход в окно надстроек в Microsoft Excel

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

    Окно надстроек в Microsoft Excel

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

    Запуск пакета анализ данных в Microsoft Excel

    Активируется окошко «Анализ данных» со списком профильных инструментов обработки информации. Выделяем из этого перечня пункт «Регрессия» и клацаем по кнопке «OK».

    Запуск инструмента Регрессия в окне Анализ данных в Microsoft Excel

    Затем открывается окно инструмента «Регрессия». Первый блок настроек – «Входные данные». Тут в двух полях нужно указать адреса диапазонов, где находятся значения аргумента и функции. Ставим курсор в поле «Входной интервал Y» и выделяем на листе содержимое колонки «Y». После того, как адрес массива отобразился в окне «Регрессия», ставим курсор в поле «Входной интервал Y» и точно таким же образом выделяем ячейки столбца «X».

    Около параметров «Метка» и «Константа-ноль» флажки не ставим. Флажок можно установить около параметра «Уровень надежности» и в поле напротив указать желаемую величину соответствующего показателя (по умолчанию 95%).

    В группе «Параметры вывода» нужно указать, в какой области будет отображаться результат вычисления. Существует три варианта:

    • Область на текущем листе;
    • Другой лист;
    • Другая книга (новый файл).

    Остановим свой выбор на первом варианте, чтобы исходные данные и результат размещались на одном рабочем листе. Ставим переключатель около параметра «Выходной интервал». В поле напротив данного пункта ставим курсор. Щелкаем левой кнопкой мыши по пустому элементу на листе, который призван стать левой верхней ячейкой таблицы вывода итогов расчета. Адрес данного элемента должен высветиться в поле окна «Регрессия».

    Группы параметров «Остатки» и «Нормальная вероятность» игнорируем, так как для решения поставленной задачи они не важны. После этого клацаем по кнопке «OK», которая размещена в правом верхнем углу окна «Регрессия».

    Окно инструмента Регрессия Пакета анализа в Microsoft Excel

  • Программа производит расчет на основе ранее введенных данных и выводит результат в указанный диапазон. Как видим, данный инструмент выводит на лист довольно большое количество результатов по различным параметрам. Но в контексте текущего урока нас интересует показатель «R-квадрат». В данном случае он равен 0,947664, что характеризует выбранную модель, как модель хорошего качества.
  • Результат расчета коэффициента детерминации с помощью инструмента Регрессия в окне Анализ данных в Microsoft Excel

    Способ 3: коэффициент детерминации для линии тренда

    Кроме указанных выше вариантов, коэффициент детерминации можно отобразить непосредственно для линии тренда в графике, построенном на листе Excel. Выясним, как это можно сделать на конкретном примере.

      Мы имеем график, построенный на основе таблицы аргументов и значений функции, которая была использована для предыдущего примера. Произведем построение к нему линии тренда. Кликаем по любому месту области построения, на которой размещен график, левой кнопкой мыши. При этом на ленте появляется дополнительный набор вкладок – «Работа с диаграммами». Переходим во вкладку «Макет». Клацаем по кнопке «Линия тренда», которая размещена в блоке инструментов «Анализ». Появляется меню с выбором типа линии тренда. Останавливаем выбор на том типе, который соответствует конкретной задаче. Давайте для нашего примера выберем вариант «Экспоненциальное приближение».

    Создание линии тренда в Microsoft Excel

    Эксель строит прямо на плоскости построения графика линию тренда в виде дополнительной черной кривой.

    Линия тренда в Microsoft Excel

    Теперь нашей задачей является отобразить собственно коэффициент детерминации. Кликаем правой кнопкой мыши по линии тренда. Активируется контекстное меню. Останавливаем выбор в нем на пункте «Формат линии тренда…».

    Переход в окно формата линии тренда в Microsoft Excel

    Для выполнения перехода в окно формата линии тренда можно выполнить альтернативное действие. Выделяем линию тренда кликом по ней левой кнопки мыши. Перемещаемся во вкладку «Макет». Клацаем по кнопке «Линия тренда» в блоке «Анализ». В открывшемся списке клацаем по самому последнему пункту перечня действий – «Дополнительные параметры линии тренда…».

    Переход в окно дополнительных параметров линии тренда через кнопку на ленте в Microsoft Excel

    После любого из двух вышеуказанных действий запускается окошко формата, в котором можно произвести дополнительные настройки. В частности, для выполнения нашей задачи необходимо установить флажок напротив пункта «Поместить на диаграмму величину достоверности аппроксимации (R^2)». Он размещен в самом низу окна. То есть, таким образом мы включаем отображение коэффициента детерминации на области построения. Затем не забываем нажать на кнопку «Закрыть» внизу текущего окна.

    Окно формата линии тренда в Microsoft Excel

    Значение достоверности аппроксимации, то есть, величина коэффициента детерминации, будет отображено на листе в области построения. В данном случае эта величина, как видим, равна 0,9242, что характеризует аппроксимацию, как модель хорошего качества.

    Коэффициент детерминации линии тренда в Microsoft Excel

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

    Смена типа линии тренда в окне формат линии тренда в Microsoft Excel

    При линейном типе линия тренда уже имеет значение достоверности аппроксимации равное 0,9477, что характеризует эту модель, как ещё более достоверную, чем рассматриваемую нами ранее линию тренда экспоненциального типа.

    Величина достоверности аппроксимации для линейного типа линии тренда в Microsoft Excel

    Корпорация "Центр"

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

    Например, для нашего случая опытным путем удалось установить, что самый высокий уровень достоверности имеет полиномиальный тип линии тренда второй степени. Коэффициент детерминации в данном случае равен 1. Это говорит о том, что указанная модель абсолютно достоверная, что означает полное исключение погрешностей.

    Величина достоверности аппроксимации для полиномиального типа линии тренда в Microsoft Excel

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

    В Экселе существуют два основных варианта вычисления коэффициента детерминации: использование оператора КВПИРСОН и применение инструмента «Регрессия» из пакета инструментов «Анализ данных». При этом первый из этих вариантов предназначен для использования только в процессе обработки линейной функции, а другой вариант можно использовать практически во всех ситуациях. Кроме того, существует возможность отображения коэффициента детерминации для линии трендов графиков в качестве величины достоверности аппроксимации. С помощью данного показателя имеется возможность определить тип линии тренда, который располагает самым высоким уровнем достоверности для конкретной функции.

    Мы рады, что смогли помочь Вам в решении проблемы.

    Помимо этой статьи, на сайте еще 11905 инструкций.
    Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Коэффициент детерминации в Excel (Эксель)

    Для статистических моделей во многих случаях необходимо определить точность прогноза. Это производится с помощью специальных расчётов в Microsoft Excel, а использоваться будет коэффициент детерминации. Он обозначается как R^2.

    Статистические модели можно разделить на качественные уровни в зависимости от коэффициента. От 0.8 до 1 относятся модели хорошего качества, модели достаточного качества имеют уровень от 0.5 до 0.8, а плохое качество имеет диапазон от 0 до 0.5.

    Способ определения точности с помощью функции КВПИРСОН

    В линейной функции коэффициент детерминации будет равен квадрату корреляционного коэффициента. Рассчитать его можно с помощью специальной функции. Для начала создадим таблицу с данными.

    Коэффициент детерминации в Excel (Эксель)

    Потом нужно выбрать место, где будет показан результат расчёта и нажимаем на кнопку вставки функции.

    Коэффициент детерминации в Excel (Эксель)

    После этого откроется специальное окно. Категорию нужно выбрать "Статистические" и выбираем КВПИРСОН. Эта функция позволяет определить коэффициент корреляции касательно функции Пирсона, соответственно квадратное значение коэффициента корреляции = коэффициенту детерминации.

    Коэффициент детерминации в Excel (Эксель)

    После подтверждения действия, появится окно в котором нужно в полях выставить "Известные значения Х" и "Известные значения Y". Нажимаем мышкой поле "Известные значения Y" и в рабочем окне выделяем данные столбца Y. Аналогичное действие делаем и с другим полем выбирая данные уже с таблицы Х.

    Коэффициент детерминации в Excel (Эксель)

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

    Коэффициент детерминации в Excel (Эксель)

    Определение коэффициента детерминации если функция не является линейной.

    Если функция нелинейная, то инструментарий Excel также позволяет рассчитать коэффициент с помощью инструмента "Регрессия". Его можно найти в пакете анализа данных. Но для начала нужно активировать этот пакет, перейдя в раздел "Файл" и в списке открыть "Параметры".

    Коэффициент детерминации в Excel (Эксель)

    После этого можно увидеть новое окно, в котором нужно в меню выбрать "Надстройки", а в специальном поле по управлению надстройками выбираем "Надстройки Excel" и переходим к ним.

    Коэффициент детерминации в Excel (Эксель)

    После перехода в надстройки Excel появится новое окно. В нём можно увидеть доступные для пользователя надстройки. Ставим галочку возле "Пакет анализа" и подтверждаем действие.

    Коэффициент детерминации в Excel (Эксель)

    Найти его можно в разделе "Данные", после перехода в который нажимаем на "Анализ данных" в правой части экрана.

    Коэффициент детерминации в Excel (Эксель)

    После его открытия, в списке выбираем "Регрессия"и подтверждаем действие.

    Коэффициент детерминации в Excel (Эксель)

    После этого появится новое окно в котором можно производить настройки. Входные данные позволяют настроить значение интервалов Х и Y, достаточно выделить соответствующие ячейки аргументов другого аргумента. В поле уровня надежности можно выставить нужный показатель. Параметры вывода позволяют задать где будет показан результат. Если к примеру выбрать показ на текущем листе, то для начала нужно выбрать пункт "Выходной интервал" — и нажать на области основного окна где будет в будущем отображаться результат и координаты ячейки будут показаны соответствующем поле. В конце подтверждаем действие.

    Коэффициент детерминации в Excel (Эксель)

    В рабочем окне появится результат. Так как мы вычисляем коэффициент детерминации, то в итогах нам нужен R-коэффициент. Если посмотреть на значение, то можно увидеть что оно относится к наилучшему качеству.

    Коэффициент детерминации в Excel (Эксель)

    Способ определения коэффициента детерминации для линии тренда

    Имея созданную таблицу с соответствующими значение, создаем график. Чтобы провести на нём линию тренда надо нажать на график, а именно на область где строится линия. Сверху в панели инструментов выбрать раздел "Макет", а в нём выбрать "Линия тренда". После этого в контексте данного примера в списке выбираем "Экспоненциальное приближение".

    Коэффициент детерминации в Excel (Эксель)

    Линия тренда будет отображена на графике как кривая с черным цветом.

    Коэффициент детерминации в Excel (Эксель)

    Для того чтобы показать коэффициент детерминации, нужно по черной кривой нажать правой кнопкой мыши и выбрать в списке "Формат линии тренда".

    Коэффициент детерминации в Excel (Эксель)

    После этого появится новое окно. В нём нужно отметить флажком и выбрать нужное действие (показано на скриншоте). Благодаря этому коэффициент будет отображен на графике. После того как это было сделано, закрываем окно.

    Коэффициент детерминации в Excel (Эксель)

    После закрытия окна формата линии тренда в рабочем окне можно увидеть значение коэффициента детерминации.

    Коэффициент детерминации в Excel (Эксель)

    Если пользователю нужен другой типаж линии тренда, то в окне "Формат линии тренда" можно выбрать его. Не забыв задать его ранее при создании линии тренда в разделе "Макет" или в контекстном меню. Также не забываем ставить флажок для функции R^2.

    Коэффициент детерминации в Excel (Эксель)

    Как результат можно увидеть изменение линии тренда и число достоверности.

    Коэффициент детерминации в Excel (Эксель)

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

    Так было рассмотрено несколько способов по нахождению коэффициента детерминации. Пользователь может выбрать наиболее оптимальный для своих целей.

    Корпорация "Центр"

    Ссылка на основную публикацию