Как добавить r 2 в экселе

Обновлено: 15.01.2025

Я рисую данные, подбираю степенную функцию («добавить линию тренда») и использую «добавить линию тренда> параметры> Отображать значение R-квадрата на диаграмме».

Отображаемое значение:
R 2 = 0,03008 .

Проблема 1

Если я вычисляю его в Excel с помощью функции 'RSQ ()' (принимая значения параметров, которые Excel нашел для функции подгонки) или вручную, используя определение (википедия) .
R 2 = 0,0272

Проблема 2

Вопросов:

Итак, вот мой главный вопрос:
Как Excel вычисляет R 2 в функции «добавить линию тренда», поскольку он явно не тот, который указан в определении (википедия)?

И бонусный вопрос:
Почему Excel и Matlab не имеют одинаковых параметров подгонки?

%%%%%% ИЗМЕНИТЬ НИЖЕ! %%%%
Как ответ на комментарий; Вот код Matlab, который я использую:

Я нахожу те же значения, когда получаю R-квадрат из функции fit в Matlab или вычисляю его «вручную». Matlab кажется последовательным и, по-видимому, использует строгое определение R-квадрата в своей функции .

Однако ; когда я сравниваю:

значение R-квадрата, полученное в Excel из функции RSQ()
и значение, которое я получаю вручную, вычисляя R-квадрат из определения (принимая, конечно, значения yfit , которые мне вернул Excel, а не те, которые возвращает Matlab, поскольку Excel и Matlab не согласны с подгонкой параметры!)

. Я получаю разные значения! Excel: 0,027, как я уже говорил, и рассчитано вручную: -0,1109 (!)

3 ответа

RSQ не возвращает значение r-квадрата для линии тренда мощности , а возвращает r-квадрат для линейной линии тренда. Справка Excel гласит: « Для логарифмических, степенных и экспоненциальных линий тренда Excel использует преобразованную регрессионную модель », но я не могу найти эту модель.

Это потому, что вы просите R 2 для соответствия степенной функции в вашей диаграмме (т.е. y = a (x) b ), тогда как RQS функция в Excel дает вам R 2 для линейной аппроксимации (т.е. y = a (x) + b). Я подозреваю, что у вас аналогичная проблема в Matlab. Вам нужно будет опубликовать свой код в Matlab, иначе мы просто будем гадать.

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

Я сделал то же наблюдение, как будто это уже упоминалось. Когда кто-то использует «добавить линию тренда» для подбора линейной (а также логарифмической) функции, результирующие параметры R ^ 2 и регрессии идентичны параметрам, рассчитанным вручную. Но когда кто-то использует «добавить линию тренда» для подбора другой нелинейной функции (например, экспоненциальной), результирующие параметры R ^ 2 и регрессии отличаются от параметров, рассчитанных вручную.

Решение этой проблемы частично уже упоминалось в обсуждении здесь. Кажется, что для того, чтобы подогнать нелинейный тренд к предоставленным данным, Excel в первую очередь линеаризует проблему. Так, например, чтобы соответствовать экспоненциальной функции y = a * exp (b * x), она в первую очередь преобразует ее в функцию ln (y) = ln (a) + b * x. Тогда связь между ln (y) и x линейна. Затем линеаризованная функция вставляется в преобразованные данные с использованием обычной стратегии минимизации суммы квадратов остатков. Таким образом, получены параметры регрессии ln (a) и b. Также R ^ 2 рассчитывается из линеаризованной формы. Поскольку это линейная зависимость, функция RSQ () может использоваться Excel для вычисления R ^ 2.

Если следовать этой процедуре вручную, то результирующие параметры регрессии и значения R ^ 2 идентичны тем, которые предоставляются Excel «добавить линию тренда».

Таким образом, обычно параметры регрессии и значения R ^ 2, предоставляемые Excel «добавить линию тренда» в случае нелинейной регрессии, не являются истинно нелинейными, но, скорее всего, получены после линеаризации проблемы. Как следствие, эти параметры незначительно отличаются от параметров, рассчитанных напрямую без каких-либо преобразований.

Примечание о R ^ 2: насколько я понимаю, R ^ 2 для линейного случая (лучше обозначается маленькой буквой: r ^ 2) рассчитывается как квадрат коэффициента корреляции. (RSQ () = КОРРЕЛ () ^ 2 = ПИРСОН () ^ 2). Из-за этого отношения значения r ^ 2 могут находиться в диапазоне только от 0 до 1. Один набор входных данных может быть изменен путем пересечения и / или наклона без изменения значения r ^ 2. С другой стороны, R ^ 2 для нелинейного случая (лучше обозначаемого заглавной буквой) определяется по-другому (см. Википедию). Его значения не ограничены снизу значением 0, но максимальное значение 1 по-прежнему указывает на лучшее соответствие. Изменение одного набора входных данных путем перехвата и / или наклона изменяет значение R ^ 2. R ^ 2 работает так же хорошо и в линейном случае.

03.08.2021 | Александр Шихов | Комментарии

Когда научный руководитель сказал мне о необходимости указать на графике R² (р квадрат), я растерялся. В тот момент я не знал о трендах в диаграммах и графиках Excel. Этот материал поможет сориентироваться начинающим.

Что такое R² в Экселе

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

По умолчанию тренд линейный. Чуть позднее расскажу, как выбрать иную функцию, и стоит ли это делать. Теперь подводим курсор мыши к тренду и снова нажимаем правую кнопку.

Добавляем на график R².

Стоит ли добиваться максимального значения R²

Улучшить достоверность апроксимации можно меняя вид кривой. Это можно сделать в открывающемся справа окошке Формат линии тренда.

Если использовать полиноминальную функцию, то апроксимацию можно улучшить значительно. Но вот смысла это не имеет. Экономические показатели обычно укладываются в линейный (рост/падение) или экспоненциальный тренд. Экспоненциально, например, растет число клиентов быстрорастущей фирмы.

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

Как использовать тренд для прогноза

Кроме определения общего положения дел (рост/снижение), тренд может предсказать значения показателей в будущем. Это делается в окошке Формата линии тренда.

Попробуем предсказать продажи умных часов в первом квартале 2021 году и сравним их с фактом. Добавим два линейных тренда для Apple и Остальных.

Как видим, по яблочным часа прогноз построен верно, по остальным функция прогнозирует значение около 35%, а в реальности 46%. Возможно, это связано с выходом новых игроков на рынок или снижением доли Huawei. Мы имеем дело с относительными показателями (доля), а не с натуральными. Кстати, полиноминальный прогноз для категории Остальные дал бы еще менее точный прогноз, хотя R² и выше, что подтверждает необходимость осторожно выбирать функцию.

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

Когда вы выполняете регрессионный анализ, коэффициент детерминации, отмеченный как R ^ 2, говорит о том, насколько хорошо линия регрессии предсказывает фактические точки данных. Excel 2013 предоставляет способ для вычисления значения R ^ 2, а также для отображения его на диаграмме XY.

R2credit: Изображение предоставлено Microsoft

Compute R2

Данные для Analysiscredit: Изображение предоставлено Microsoft

Соберите данные о расходах на маркетинг и продажах вашей компании за 12 месяцев и внесите их в таблицу Excel. Вы хотите понять, влияют ли ваши маркетинговые усилия, связанные с расходами на маркетинг, на продажи.В формальном регрессионном анализе вы относитесь к маркетинговым расходам как к независимой переменной, отмеченной X, и к данным о продажах как к зависимой переменной, отмеченной Y.

R2 Formulacredit: Изображение предоставлено Microsoft

Используйте функцию Excel RSQ для вычисления R ^ 2. Функция RSQ имеет два аргумента: первый представляет значения Y, а второй представляет значения X. Предполагая, что вы сохраняете значения продаж в диапазоне C9: C20 и значения маркетинговых расходов в диапазоне B9: B20, запишите следующую формулу в C5: = RSQ (C9: C20, B9: B20)

Формула Resultcredit R2: Изображение предоставлено Microsoft

нажмите Войти нажмите на клавиатуре и подтвердите, что формула возвращает значение R ^ 2.

картирование

Вставить> Scatter XY Chartcredit: Изображение предоставлено Microsoft

Чтобы составить график данных, начните с выбора маркетинговых расходов и данных о продажах. Выберите Вставить> Scatter (X, Y) значок диаграммы и выберите рассеивать стиль.

Подробнее Trendline Optionscredit: Изображение предоставлено Microsoft

Excel вставляет диаграмму в лист. Чтобы отобразить значение R ^ 2 на графике, выберите Инструменты для работы с графиками> Дизайн> Добавить элемент диаграммы> Линия тренда> Дополнительные параметры линии тренда значок.

Показать R-squaredcredit: Изображение предоставлено Microsoft

В появившейся панели Format Trendline отметьте Отображение значения R-квадрата на графике флажок В Excel отображается значение R ^ 2, и вы можете подтвердить, что оно совпадает со значением, вычисленным функцией RSQ.

Как рассчитать будущую дату в Excel

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

Как рассчитать стандартное отклонение в Excel

Выберите одну из четырех формул стандартного отклонения в Excel и всегда рассчитывайте среднее или среднее значение при вычислении стандартного отклонения.

Как рассчитать будущую стоимость инвестиций с помощью Excel

Как рассчитать будущую стоимость инвестиций с помощью Excel. Использование Microsoft Excel для расчета будущей стоимости потенциальных инвестиций - относительно простая задача, если вы .

Глядя на любой набор данных распределенных во времени (динамический ряд), мы можем визуально определить падения и подъемы показателей, которые он содержит. Закономерность подъемов и падений называется трендом, который может говорить о том, увеличиваются или уменьшаются наши данные.

Базовые понятия

Думаю, еще со школы все знакомы с линейной функцией, она как раз и лежит в основе тренда:

Построение модели

Итак, мы знаем объем продаж за прошедшие 9 месяцев. Вот, что из себя представляет наша табличка:

Определение коэффициентов модели

Строим график. По горизонтали видим отложенные месяцы, по вертикали объем продаж:

Вот, что получилось:

На графике мы видим уравнение функции:

y = 4856*x + 105104

Она описывает объем продаж в зависимости от номера месяца, на который мы хотим эти продажи спрогнозировать. Рядом видим коэффициент детерминации R^2, который говорит о качестве модели и на сколько хорошо она описывает наши продажи (Y). Чем ближе к 1, тем лучше.

У меня R^2 = 0,75. Это средний показатель, он говорит о том, что в модели не учтены какие-то другие значимые факторы помимо времени t, например, это может быть сезонность.

Прогнозируем

Чтобы рассчитать продажи за 10-ый месяц, подставляем в функцию тренда 10 вместо x. То есть,

y = 4856*10 + 105104

Получаем 153664 продажи в следующем месяце. Если добавим новую точку на график, то сразу видим, что R^2 улучшился.

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

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

Читайте также: