Il valore R al quadrato in Excel con l’ ‘addtrendline’ funzione?

Ho lottato un po ‘ su;
In breve, non riesco a trovare le equazioni di Excel usi per R2.

  • Ecco i miei dati :

    x: 1 2 3 4 5 6 7 8 9 10

    y: 4 9 1 2 1 1 8 5 5 1

  • Ho tracciato i dati, in forma di una legge di potenza (funzione “aggiungi linea di tendenza”) e utilizzare “aggiungi linea di tendenza > opzioni > Display valore R al quadrato sul grafico’

Valore visualizzato :

R2 = 0.03008.

Problema 1

Se calcolo in excel utilizzando ‘RQ()’ funzione (prendendo i valori dei parametri di Excel ha trovato per il montaggio di una funzione), o a mano, utilizzando la definizione (wikipedia)…
R2 = 0.0272

Problema 2

In Matlab, utilizzando il ‘fit’ di funzione, i parametri per la funzione di raccordo (e poi, naturalmente, il R2) non sono quelli che EXCEL ha trovato.

Domande:

Quindi ecco la mia domanda principale : il

Come fa Excel calcola il R2 “aggiungi linea di tendenza”, come non è, ovviamente, quello della definizione (wikipedia)?

e la domanda bonus:

Perché Excel e Matlab non finire con gli stessi parametri di montaggio?

grazie mille!

%%%%%% EDIT QUI SOTTO! %%%%

Come risposta a un commento;
Qui è il codice di Matlab io uso:

%% 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;

Trovo gli stessi valori se ho R-squared dal fit funzione in Matlab o calcolare “a mano”. Matlab sembra essere coerente e a quanto pare utilizza la definizione rigorosa di R-squared nella sua funzione…

Tuttavia; quando metto a confronto:

  • R-quadrato valore dato da Excel da RSQ() funzione
  • e il valore di ottenere a mano il calcolo di R-quadrato dalla definizione (prendendo ovviamente il yfit valori che Excel mi ha restituito, non Matlab restituisce, come Excel e Matlab non sono d’accordo sui parametri di montaggio!)

…Ottengono valori diversi! Excel : 0.027, come ho detto prima, e calcolato a mano : -0.1109 (!)

Come si sta a 0.0272 esattamente?
Prendo i parametri Excel trovati per il montaggio (3.6153 e -0.217), calcolare la misura dei valori (diciamo yfit) e calcolare la funzione RQ con argomenti i dati (y) e i valori adattati (yfit).

OriginaleL’autore Lucile | 2014-06-30

3 Replies
  1. 1

    RQ fa non restituire il valore di r al quadrato per un potenza trendline, piuttosto restituisce l’r-quadro di una linea di tendenza lineare. In linea di Excel legge “Per logaritmica, potenza, esponenziale e linee di tendenza, Excel utilizza un trasformato modello di regressione“, ma io non sono in grado di trovare quel modello.

    Vedere anche Come Misurare la bontà di una misura di una linea di tendenza per una legge di potenza

    Ok grazie… è davvero confusionaria Excel utilizza la propria definizione di R-quadrato in “aggiungi linea di tendenza”, che non è lo stesso di quello utilizzato nella funzione RQ! E per quanto riguarda la differenza nei parametri di raccordo tra Excel e Matlab? con y=ax^b; Excel :=3.61, b = -0.23 VS Matlab a = 4.81 e b=-0.18…!
    Il risultato è lo stesso per la linea di tendenza lineare, che è ciò che la funzione RQ è stato progettato per. E, se si sta tentando di utilizzare la linea di tendenza parametri per il calcolo, si deve usare un= 3.61530123330863 e b = -0.216941129430519 e non arrotondati numeri che hai utilizzato in precedenza. Ci può essere un altro problema che non credo che i vostri dati fornisce una buona vestibilità per una potenza trendline, ma penso che gli articoli del link potrebbe dare più approfondita.
    Sì, ho arrotondato i parametri per calcolare il montaggio valori yfit, come intuitivamente ho pensato che la differenza nel valore di R al quadrato non sarebbe significativo quando l’arrotondamento dei parametri (ora ho calcolato utilizzando le non arrotondato parametri, è quasi lo stesso valore : 0.02723398). Questo dati. I miei dati reali è un enorme matrice. Ma l’ultimo problema è che se metto a confronto il potere-diritto di montaggio e raccordo esponenziale in Excel, scegliere la legge di potenza (maggiore di R al quadrato), mentre in Matlab, io uso esponenziale. questo è un grosso problema…
    …Infine sì, ci sono altri modi -e meglio – di dichiarare la bontà di adattamento del non-lineare raccordi (Nash Sutcliffe nel mio campo, o quello nel link dal tuo link), il problema è che, in un documento, questi “altri modi” di solito non sono affatto capito dai revisori / lettori…
    Excel aggiungi linea di tendenza potrebbe non essere la migliore ottimizzazione. Provare a utilizzare il risolutore di Excel per trovare i parametri e confrontare poi con Matlab

    OriginaleL’autore Ron Rosenfeld

  2. 2

    Questa domanda (come fa il “aggiungi linea di tendenza” in Excel funziona davvero?) inoltre mi ha sorpreso per un tempo più lungo, perché in una ricerca che ho bisogno di essere sicuro circa l’origine dei miei numeri. Perché non ho trovato molto su questo su internet, così ho provato diversi vays del manuale di R^2 (coefficiente di determinazione) di valutazione per ottenere gli stessi risultati come Excel.

    Ho fatto la stessa osservazione, come è stato accennato in questione. Quando uno usa “aggiungi linea di tendenza” (lineare e logaritmico) funzione di raccordo, la risultante R^2 e regresion parametri sono identici manualmente i parametri calcolati. Ma quando uno usa “aggiungi linea di tendenza” per altra funzione non lineare di montaggio (per esempio esponenziale), la risultante R^2 e regresion parametri differisce dal manualmente i parametri calcolati.

    La soluzione di questo problema è stato già in parte accennato in discussione qui. Sembra che per fit non lineare di tendenza nei dati forniti, Excel primaria linearizzare il problema. Così, per esempio, per adattarsi funzione esponenziale y=a* exp(b * x) è principalmente trasforma in funzione ln(y)=ln(a)+b*x. Quindi la relazione tra ln(y) e x è lineare. Dopo il linearizzato funzione è inserito in trasformata di dati utilizzando la solita strategia di minimizzazione della somma dei quadrati dei residui. Così il regresion parametri ln(a) e b) sono ottenute. Anche R^2 è calcolato dal linearizzato forma. Perché è dipendenza lineare, RQ() la funzione può essere utilizzata da Excel per il calcolo di R^2.

    Quando si segue questa procedura manualmente, quindi la risultante regresion parametri e R^2 valori sono identici a quelli forniti da Excel “aggiungi linea di tendenza”.

    Così generalmente il regresion parametri e R^2 valori fornite da Excel “aggiungi linea di tendenza” in caso di non-lineari regresion non sono vere non lineare ma più probabilmente, ottenuto dopo la linearizzazione del problema. Come conseguenza, questi parametri leggermente diversi rispetto dei parametri calcolati direttamente senza alcuna trasformazione.

    Nota in R^2: a Quanto ho capito, il R^2 per il caso lineare (meglio identificato da una piccola lettera: r^2) è calcolato come un quadrato di corelation coefficiente. (RQ()=CORRELAZIONE()^2=PEARSON()^2). A causa di questa relazione i valori di r^2 può variare tra lo 0 – 1. Un set di input ignoto può essere alterato da intercettare e/o in pendenza, senza cambiare di r^2 valore. D’altra parte la R^2 per un caso non lineare (meglio indicato da capitale letera) è definito in modo diverso (vedi Wikipedia). Si tratta di valori non inferiore limitata al valore 0, ma il valore massimo 1 indicare la soluzione migliore. L’alterazione di un set di input sa da intercettare e/o cambi di pendenza R^2 valore. R^2 funziona lo stesso, anche nel caso lineare.

    OriginaleL’autore David Sustr

  3. 1

    È perché si sta chiedendo per il R2 di montaggio di una funzione di potenza nel grafico (cioè y = a(x)b), mentre il RQS funzione in Excel offre la R2 per una misura lineare (cioè y = a(x) + b). Ho il sospetto di avere un problema simile in Matlab. Devi postare il tuo codice in Matlab, però, altrimenti si;d essere solo indovinare.

    Ciao! grazie, ho modificato la mia domanda con il Matlab codici! Spero che tu possa aiutare!

    OriginaleL’autore Dan

Lascia un commento