Coefficiente di determinazione in Excel

Uno degli indicatori che descrive la qualità del modello costruito in statistiche è il coefficiente di determinazione (R ^ 2), che è anche chiamato il valore dell'accuratezza dell'approssimazione. Con esso, è possibile determinare il livello di previsione di precisione. Scopriamo come calcolare questo indicatore usando vari strumenti Excel.

Calcolo del coefficiente di determinazione

A seconda del livello del coefficiente di determinazione, è consuetudine condividere modelli in tre gruppi:

• 0,8 - 1 - un modello di buona qualità;
• 0.5 - 0.8 - Modello di qualità accettabile;
• 0 - 0.5 - Un modello di scarsa qualità.

In quest'ultimo caso, la qualità del modello indica l'impossibilità del suo utilizzo per le previsioni.

La scelta di un metodo per il calcolo del valore specificato in Excel dipende dal fatto che la regressione sia lineare o meno. Nel primo caso, è possibile utilizzare la funzione di Firson e nel secondo dovrai utilizzare uno strumento speciale dal pacchetto di analisi.

Metodo 1: calcolo del coefficiente di determinazione con una funzione lineare

Prima di tutto, scopri come trovare il coefficiente di determinazione con una funzione lineare. In questo caso, questo indicatore sarà uguale al quadrato del coefficiente di correlazione. Lo faremo calcolati utilizzando la funzione Excel Embedded sull'esempio di una tabella specifica, che è mostrata di seguito.

1. Evidenziamo la cella in cui il coefficiente di determinazione viene emesso dopo il suo calcolo e fare clic sull'icona "Inserisci funzione".



2. Inizia il master delle funzioni. Passiamo alla sua categoria "statistica" e notando il nome "KvGirson". Quindi, argilla sul pulsante "OK".



3. Inizia la finestra Argomenti funzione Quilinson. Questo operatore dal gruppo statistico è progettato per calcolare il quadrato del coefficiente di correlazione della funzione Pearson, cioè una funzione lineare. E come ricordiamo, con una funzione lineare, il coefficiente di determinazione è solo uguale alla piazza del coefficiente di correlazione.

   La sintassi di questo operatore è:

   = Cvgirson (noto_stations_y; noto_name_x)

   Pertanto, la funzione ha due operatori, uno dei quali è un elenco di valori di funzionalità e il secondo argomento. Gli operatori possono essere rappresentati direttamente come i valori elencati attraverso un punto e virgola (;) e sotto forma di riferimenti alle gamme in cui si trovano. È l'ultima opzione e sarà utilizzata da noi in questo esempio.

   Installare il cursore nel campo "Valori V conosciuti". Eseguiamo il morsetto del pulsante sinistro del mouse e produciamo il contenuto della colonna "y" del tavolo. Come puoi vedere, l'indirizzo dell'array dei dati specificato viene immediatamente visualizzato nella finestra.

   Allo stesso modo, riempire il campo "conosciuto X". Mettiamo il cursore in questo campo, ma questa volta alloca i valori della colonna "X".

   Dopo che tutti i dati sono stati visualizzati nella finestra degli argomenti di CVGIRSON, l'argilla sul pulsante "OK" situato nel suo fondo.



4. Come puoi vedere, dopo questo, il programma rende il calcolo del coefficiente di determinazione e fornisce il risultato nella cella che è stata evidenziata prima di chiamare la procedura guidata delle funzioni. Nel nostro esempio, il valore dell'indicatore calcolato si è rivelato uguale a 1. Ciò significa che il modello presentato è assolutamente affidabile, cioè elimina l'errore.

Lezione: Master of Functions in Microsoft Excel

Metodo 2: calcolo del coefficiente di determinazione nelle funzioni non lineari

Ma l'opzione di cui sopra per il calcolo del valore desiderato può essere utilizzata solo in funzioni lineari. Cosa fare per rendere il suo calcolo in una funzione non lineare? In Excel c'è un'opportunità. Può essere fatto usando lo strumento "regressione", che è parte integrante del pacchetto di analisi dei dati.

1. Ma prima di utilizzare lo strumento specificato, il "pacchetto di analisi" deve essere attivato, che è disabilitato per impostazione predefinita in Excele. Passiamo alla scheda "File", quindi passa attraverso l'elemento "Parametri".



2. Nella finestra apre, rendono il passaggio alla sezione "add-in" utilizzando la navigazione nel menu verticale sinistro. Nella parte inferiore della finestra destra si trova il campo di controllo. Dall'elenco delle sottosezioni disponibili, selezionare il nome del "componente aggiuntivo di Excel ...", quindi fare clic sul pulsante "Vai ...", situato a destra del campo.



3. La finestra è stata lanciata. Nella sua parte centrale c'è un elenco di componenti aggiuntivi disponibili. Installare la casella di controllo vicino alla posizione "Pacchetto di analisi". Seguendo questo, è necessario fare clic sul pulsante "OK" sul lato destro dell'interfaccia della finestra.



4. Il pacchetto di strumenti di analisi dei dati nell'istanza di Excel corrente sarà attivata. L'accesso ad esso si trova sul nastro nella scheda Dati. Spostarsi nella scheda e nell'argilla specificata sul pulsante "Analisi dati" nel gruppo delle impostazioni "Analysis".



5. La finestra "Analisi dati" è attivata con un elenco di strumenti di elaborazione delle informazioni. Assegniamo da questa voce Elemento "Regressione" e argilla sul pulsante "OK".



6. Quindi si apre la finestra dello strumento "Regressione". Il primo blocco impostazioni è "Dati di input". Qui in due campi è necessario specificare l'indirizzo delle gamme in cui si trovano i valori dell'argomento e della funzione. Mettiamo il cursore nel campo "Intervallo di ingresso Y" e assegniamo il contenuto della colonna "Y" sul foglio. Dopo che l'indirizzo dell'array viene visualizzato nella finestra di regressione, mettiamo il cursore nell'intervallo "Input Interval Y" e assegniamo le celle della colonna "X" allo stesso modo.

   Vicino ai parametri "Tag" e "Constant-Zero", i flags non vengono messi. La casella di controllo può essere installata vicino al parametro "Affidabilità" e nel campo opposto Specificare il valore desiderato dell'indicatore corrispondente (per impostazione predefinita del 95%).

   Nel gruppo "Parametri di output", è necessario specificare in quale area verrà visualizzata il risultato del calcolo. Ci sono tre opzioni:

   • Area sul foglio corrente;
   • Un altro foglio;
   • Un altro libro (nuovo file).

   Stabilire la tua scelta nella prima forma di realizzazione in modo che i dati di origine e il risultato siano posizionati su un foglio di lavoro. Mettiamo l'interruttore vicino al parametro "Intervallo di output". Nel campo di fronte a questo articolo, mettiamo il cursore. Fare clic sul pulsante sinistro del mouse sull'elemento vuoto sul foglio, che è progettato per diventare la cella superiore sinistra della tabella di uscita del calcolo. L'indirizzo di questo elemento dovrebbe essere evidenziato nella finestra "Regressione".

   Gruppi di parametri "residui" e "probabilità normale" Ignora, in quanto non è importante per risolvere il compito. Dopodiché, siamo argillosi sul pulsante "OK", che è posto nell'angolo in alto a destra della finestra "Regressione".



7. Il programma effettua un calcolo in base ai dati precedentemente inseriti e visualizza il risultato all'intervallo specificato. Come puoi vedere, questo strumento visualizza un numero abbastanza elevato di risultati su vari parametri. Ma nel contesto della lezione attuale, siamo interessati all'indicatore R-Square. In questo caso, è uguale a 0,947664, che caratterizza il modello selezionato come modello di buona qualità.

Metodo 3: Coefficiente di determinazione per la linea di tendenza

Oltre alle opzioni di cui sopra, il coefficiente di determinazione può essere visualizzato direttamente per la linea di tendenza nel grafico integrata sul foglio Excel. Scopri come questo può essere fatto su un esempio specifico.

1. Abbiamo un programma basato sulla base della tabella argomentazione e delle funzioni della funzione utilizzata per l'esempio precedente. Costruiremo una linea di tendenza ad esso. Fare clic su qualsiasi sito dell'area di costruzione su cui si trova il programma con il tasto sinistro del mouse. Allo stesso tempo, un set aggiuntivo di schede appare sul nastro - "Lavorare con i grafici". Vai alla scheda "Layout". Argilla sul pulsante "Trend Line", che si trova nella barra degli strumenti "Analisi". Un menu appare con una scelta del tipo di linea di tendenza. Inizia la scelta sul tipo che corrisponde all'attività specifica. Scegliamo l'opzione "Approvvigionamento esponenziale" per il nostro esempio.



2. Excel si basa direttamente sul piano di costruire una linea di tendenza sotto forma di una curva nera aggiuntiva.



3. Ora il nostro compito è quello di visualizzare il coefficiente di determinazione effettivo. Fai clic con il tasto destro del mouse sulla riga di tendenza. Il menu di scelta rapida è attivato. Inizia la scelta in esso nel punto di formato Trend Line.

   

   Per eseguire una transizione verso la finestra Formato Trend Line, è possibile effettuare un'azione alternativa. Evidenziamo la riga di tendenza con il tasto sinistro del mouse. Sposta nella scheda "Layout". Argilla sul pulsante "Trend Line" nel blocco "Analisi". Nell'elenco dell'elenco di argilla nel miglior punto di vista dell'elenco Azione - "Parametri aggiuntivi della riga di tendenza ...".



4. Dopo una qualsiasi delle due azioni sopra, la finestra Formato viene avviata in cui è possibile produrre ulteriori impostazioni. In particolare, per soddisfare il nostro compito è necessario stabilire una casella di controllo di fronte all'elemento "Posizionare il valore dell'accuratezza dell'approssimazione (R ^ 2)". Si trova nella parte inferiore della finestra. Cioè, quindi includiamo la visualizzazione del coefficiente di determinazione nell'area di costruzione. Quindi non si dimentica di fare clic sul pulsante "Chiudi" nella parte inferiore della finestra corrente.



5. Il valore dell'accuratezza dell'approssimazione, cioè il valore del coefficiente di determinazione verrà visualizzato su una foglia nell'area di costruzione. In questo caso, questo valore è visibile, pari a 0.9242, che caratterizza approssimazione come modello di buona qualità.



6. Assolutamente in questo modo, è possibile stabilire uno spettacolo di coefficiente di determinazione per qualsiasi altro tipo di linea di tendenza. È possibile modificare il tipo di linea di tendenza facendo una transizione attraverso il pulsante sul nastro o il menu di scelta rapida nella finestra dei suoi parametri, come mostrato sopra. Quindi nella finestra stessa nel gruppo "Build Trend Line", è possibile passare a un altro tipo. Non dimentichiamo di controllarlo per "posizionare il valore dell'accuratezza dell'approssimazione sul diagramma del valore dell'approssimazione". Dopo aver completato le azioni di cui sopra, fare clic sul pulsante "Chiudi" nell'angolo in basso a destra della finestra.



7. Con un tipo lineare, la linea di tendenza ha già il valore dell'accuratezza dell'approssimazione pari a 0,9477, che caratterizza questo modello, anche più affidabile della linea della tendenza del tipo esponenziale considerato da noi.



8. Pertanto, passare da diversi tipi di righe di tendenza e confrontando il loro approssimativo valori di affidabilità (coefficiente di determinazione), è possibile trovare l'opzione che il modello descrive più accuratamente il programma inviato. Un'opzione con il più alto tasso di determinazione sarà il più affidabile. Sulla sua base, è possibile costruire la previsione più accurata.

   Ad esempio, per il nostro caso, è stato possibile stabilire che il più alto livello di affidabilità ha un tipo polinomiale di una tendenza di secondo grado. Il coefficiente di determinazione in questo caso è 1. Ciò suggerisce che il modello specificato è assolutamente affidabile, il che significa che la completa eccezione degli errori.

   

   Ma, allo stesso tempo, questo non significa che per un altro grafico, questo tipo di linea di tendenza sarà anche il più affidabile. La scelta ottimale del tipo di linea di tendenza dipende dal tipo di funzione, sulla base della quale è stato costruito il programma. Se l'utente non ha abbastanza conoscenza su "On the Eye" per stimare l'opzione di massima qualità, l'unica output di determinazione della migliore previsione è solo un confronto dei coefficienti di determinazione, come mostrato nell'esempio sopra.

Guarda anche:

Costruire una linea di tendenza in Excel

Approssimazione in Excel.

In Excel, ci sono due opzioni principali per il calcolo del coefficiente di determinazione: l'uso dell'operatore del Firson e dell'applicazione dello strumento di regressione dal pacchetto di strumenti di analisi dei dati. Allo stesso tempo, la prima di queste opzioni è destinata all'uso solo durante l'elaborazione di una funzione lineare, e un'altra opzione può essere utilizzata in quasi tutte le situazioni. Inoltre, è possibile visualizzare il coefficiente di determinazione per la linea di grafici dei grafici come valore dell'accuratezza dell'approssimazione. Con questo indicatore, è possibile determinare il tipo di linea di tendenza, che ha il massimo livello di affidabilità per una funzione specifica.