Значение R-квадрата в Excel с функцией «addtrendline»?

Я некоторое время боролся с этим; Короче говоря, я не могу найти уравнения, которые Excel использует для R2.

  • Вот мои данные:
    x: 1 2 3 4 5 6 7 8 9 10
    y: 4 9 1 2 1 1 8 5 5 1

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

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

Задача 1

Если я вычислю его в Excel, используя функцию «RSQ()» (взяв значения параметров, найденных Excel для функции подбора), или вручную, используя определение (википедия)...
R2 = 0,0272

Задача 2

В Matlab, используя функцию «подгонки», параметры функции подгонки (и, конечно же, R2) не те, которые нашел EXCEL.

Вопросы:

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

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

большое спасибо!

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

%% R-squared with the fit function
% use the fit function in Matlab, yobs being the data
[param, results] = fit(x,yobs,'power1');   

% R-squared from the fit function :
r_sq_from_fit = results.rsquare;

%% here I calculate "by hand" the R-squared, from the general definition (wikipedia!)
% calculates the fitting data yfit
yfit = (p_powerlaw.a).*x.^p_powerlaw.b;

% mean of the yobs, total sum of squares, and residual sum of squares
yobs_mean = mean(yobs);
SStot = sum((yobs-yobs_mean).^2);
SSres = sum((yobs-yfit).^2);

r_sq_hand = 1-SSres/SStot;

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

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

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

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


matlab excel regression curve-fitting trendline
person Lucile    schedule 30.06.2014    source источник
comment
Как вы добираетесь до 0,0272 точно?   -  person Dan    schedule 30.06.2014
comment
Я беру параметры, найденные Excel для подгонки (3,6153 и -0,217), вычисляю подобранные значения (назовем их yfit) и вычисляю функцию RSQ с аргументами данных (y) и подобранные значения (yfit).   -  person Lucile    schedule 30.06.2014

Ответы (3)


arrow_upward
2
arrow_downward

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

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

person Ron Rosenfeld    schedule 30.06.2014
comment
Хорошо, спасибо ... это действительно сбивает с толку, что Excel использует свое собственное определение R-квадрата в «добавить линию тренда», которое не совпадает с тем, что используется в функции RSQ! А как насчет разницы в параметрах подгонки между Excel и Matlab? с y=ax^b; Excel: a=3,61, b=-0,23 VS Matlab a=4,81 и b=-0,18...! - person Lucile; 30.06.2014
comment
@user3789714 user3789714 Результат тот же для линейной линии тренда, для которой предназначена функция RSQ. И, если вы пытаетесь использовать параметры линии тренда для расчетов, вы должны использовать a = 3,61530123330863 и b = -0,216941129430519, а не округленные числа, которые вы использовали выше. Может быть еще одна проблема, заключающаяся в том, что я не верю, что ваши данные достаточно хорошо соответствуют силовой линии тренда, но я думаю, что статьи по ссылке могут дать больше информации об этом. - person Ron Rosenfeld; 30.06.2014
comment
Да, я округлил параметры, чтобы рассчитать подходящие значения yfit, так как интуитивно я думал, что разница в значении R-квадрата не будет существенной при округлении параметров (теперь я вычислил его снова, используя неокругленные параметры, это почти такое же значение: 0,02723398). Эти данные выдуманы. Мои реальные данные — это огромная матрица. Но последняя проблема заключается в том, что если я сравниваю степенную и экспоненциальную аппроксимацию в Excel, я выбираю степенную зависимость (более высокий R-квадрат), тогда как в Matlab я использую экспоненциальную. это большая проблема... - person Lucile; 30.06.2014
comment
... Наконец, да, есть другие способы - и лучше - заявить о соответствии нелинейных фитингов (Нэш Сатклифф в моей области или тот, что в ссылке из вашей ссылки), проблема в том, что в статье , те другие способы обычно вообще не понятны обозревателям/читателям... - person Lucile; 30.06.2014
comment
@user3789714 user3789714 Добавление линии тренда в Excel может быть не лучшей оптимизацией. Попробуйте использовать решатель в Excel, чтобы найти параметры и сравнить их с Matlab. - person Dan; 30.06.2014

arrow_upward
3
arrow_downward

Этот вопрос (как на самом деле работает «добавить линию тренда» в 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 одинаково хорошо работает и в линейном случае.

person David Sustr    schedule 05.07.2015

arrow_upward
1
arrow_downward

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

person Dan    schedule 30.06.2014
comment
Привет! спасибо, я отредактировал свой вопрос с кодами Matlab! Надеюсь, вы можете помочь! - person Lucile; 30.06.2014