Calculu di u coefficiente di determinazione in Microsoft Excel

Un di i indicatori chì descrivenu a qualità di u mudellu custruitu in e statistiche hè u coefficiente di determinazione (R ^ 2), chì hè chjamatu ancu u valore di cunfidenza di approssimazione. Cun duie, pudete determinà u livellu di accuratezza di u previziu. Scopri cumu si pò calculà stu indicatore aduprendu vari attrezzi Excel.

Calculu di u coefficiente di determinazione

A seconda di u livellu di u coefficimentu di determinazione, hè usatu di dividere i mudelli in trè gruppi:

• 0,8 - 1 - un mudellu di bona qualità;
• 0.5 - 0.8 - un mudellu di qualità accettabile;
• 0 - 0,5 - un mudellu di scarsa qualità.

In questu ultimu casu, a qualità di u mudellu indica l'impussibilità di u so usu per a previsione.

A sceglie di u calculu di u valore specificatu in Excel dipende se a regressione hè lineare o micca. In u primu casu, pudete aduprà a funzione KVPIRSON, è in u secondu avete bisognu aduprà un strumentu speciale da u pacchetto di analisi.

Metudu 1: calculu u coefficiente di determinazione cù una funzione lineare

Prima di tutti, scopre cumu trovu u coefficiente di determinazione per una funzione lineare. In stu casu, stu indicatore hè uguale à u quadru di u coefficientu di correlazione. Calcogliamulimu cù a funzione Excel fatta aduprendu l'esempiu di una tavola specifica, chì si vene quì sottu.

1. Selezzi a cella induve u coefficientu di determinazione sarà visualizatu dopu à u so calculu, e cliccate nantu à l'icona "Inserisci funzione".



2. Iniziò Wizard di Funzione. Move a so categoria "Statistica" è marcate u nome KVPIRSON. Dopu, cliccate nantu à u bottinu "OK".



3. A finestra d'argomenza di e funzioni si principia. KVPIRSON. Questu operatore di u gruppu statisticu hè disignatu per calculà a piazza di u coefficiente di correlazione di a funzione Pearson, chì hè una funzione lineare. È cum'è si ricorda, cù una funzione lineare, u coefficiente di determinazione hè ghjustu à u quadru di u coefficientu di correlazione.

   A sintassi di sta dichjarazione hè:

   = KVPIRSON (cunnisciutu; ben cunnisciutu_x)

   Dunque, una funzione hà dui operatori, unu di i quali hè una lista di valori di a funzione, è u secondu hè un argomentu. L'operatori pò esse rapprisentatu cum'è i valori listati da un punto e virgula (;), è in forma di ligami per i spazii duve sò situati. Hè l'ultima opzione chì sarà aduprata da noi in stu esempiu.

   Piazzà u cursore in u campu "Valori Cunnisciuti Y". Facemu u stringhju di u bottinu sinu di u mouse è selezziunu u cuntenutu di a colonna. "Y" tabulati. Comu pudete vede, l'indirizzu di u array di dati specificatu hè immediatamente visualizatu in a finestra.

   Similmente rifornite u campu "Cunnisciuta x". Mette u cursore in stu campu, ma sta volta sceglite i valori di colonna "X".

   Quandu tutti i dati sò stati visualizzati in a finestra di argumenti KVPIRSONcliccate u pulsante "OK"situatu à u so fondu.



4. Comu pudete vede, dopu adu, u prugramma calcula u coefficiente di determinazione è restituisce u risultatu à a cella chì era stata selezata prima di a chiamata Maestri di funzioni. In u nostru esempiu, u valore di l'indicatore calculatu hè statu u 1. Ciò significa chì u mudellu presentatu hè assolutamente affidabile, cusì elimina l'errore.

Lezione: Assistente di Funzione in Microsoft Excel

Metudu 2: Calculu di u coefficiente di determinazione in funzioni non-lineari

Ma l'opzione di sopra di calculà u valore desideratu pò esse applicatu solu à funzioni lineari. Cosa fate per fà u so calculu in una funzione non lineare? In Excel ci hè una tale opportunità. Si pò fà cun un utallettu. "Regressione"chì hè a parte di u pacchettu "Analisi di dati".

1. Ma prima di aduprà stu uttenu, avete da attivà sè. "Package di analisi"chì per difettu hè disattivatu in Excel. Move to tab "File"è dopu passa da l'elemento "Opzioni".



2. In a finestra aperta ci spostemu à a sezione. Add-ons da navigà in u menù verticalu di sinistra. À u fondu di u pannellu di dirittu hè un campu "Gestione". Da a lista di sottusezzioni dispunibuli quì scegli u nome "L'aggiunta di Excel ..."è poi cliccate nantu à u pulsante "Vai ..."situatu à a destra di u campu.



3. A finestra di moduli aghjuntu si principia. In a parte centrali hè una lista di add-in dispunibili. Verificate a casella accantu à a posizione "Package di analisi". Dopu, cliccate nantu à u pulsante. "OK" in a parte diritta di a finestra di l'interfaccia.



4. Pacchetto di l'attrezzi "Analisi di dati" in l'istanza attuale di Excel sarà attivatu. L'accessu à esse si trova nantu à u nastro in a tab "Dati". Move a tabulazione specificata è cliccate nantu à u pulsante. "Analisi di dati" in u gruppu di impostazioni "Analisi".



5. Finestra attivata "Analisi di dati" cun un listinu di strumenti di prucessione di l'infurmazione specializati. Sceglite da sta lista d'elemento "Regressione" è cliccate nantu à u pulsante "OK".



6. Allora a finestra d'utile si aprite. "Regressione". U primu blocu di configurazione - "Input". Quì, in i dui campi, avete bisognu di specificà l'indirizzi di i spazii induve i valori di l'argumentu è funzioni si trovanu. Mette u cursore in u campu "Intervallo di entrata Y" è selezziunate u cuntenutu di a colonna nantu à u fogliu "Y". Dopu chì l'indirizzu di a matrice venga visualizzato in a finestra "Regressione"mette u cursore in u campu "Intervallo di entrata Y" è in exactamente u listessu modu selezziunate e celle di colonna "X".

   Circa i parametri "Tag" è "Constant-zero" i caselli ùn sò micca impostati. U casu di spaziu pò esse messu vicinu à u parametru "Livellu di affidabilità" è in u cuntrariu oppositu, indicate u valore desideratu di l'indicatore currette (per difettu 95%).

   In gruppo "Output Options" avete bisognu di specificà in quale zona u risultatu di u calculu sarà visualizatu. Ci sò trè opzioni:

   • Area nantu à u fogliu attuale;
   • Un altru fogliu;
   • Un altru libru (novu file).

   Smetta di a scelta nantu à a prima opzione chì i dati iniziali è u risultatu sò stati piazzati nantu à un fogliu di travagliu. Mette u switch vicinu à u parametru "Spaziatura di uscita". In u campu à u opposu di questu articulu mette u cursore. Facemu clic u bottinu sinu di u mouse nantu à l'elemento vuoto nantu à u fogliu, chì hè destinatu à diventà a cella superiore sinistra di a tavula di i risultati di u calculu. L'indirizzu di questu elementu deve esse messu in finestra "Regressione".

   Gruppi di parametri "Resti" è "Prubabilità normale" ignu, perchè ùn sò micca impurtanti per risolve u prublema. Dopu à stu cliccemu nantu à u pulsante. "OK"chì hè situatu in u angulu in dirittu superiore di a finestra "Regressione".



7. U prugramma calcula in base à i dati entrati in precedenza è mostra u risultu in l'ambitu specificatu. Comu pudete vede, st'arnesi mostra nantu à u strettu un numaru assai grande di risultati nantu à vari parametri. Ma in u cuntestu di a lezzione attuale sò interessati à l'indicatore "R-square". In stu casu, hè uguale a 0.947664, chì caratterizeghja u mudellu selezionatu cume un mudellu di bona qualità.

Metudu 3: u coefficientu di determinazione per a linea di tendenza

In più di e opzioni di sopra, u coefficiente di determinazione pò esse visualizatu direttamente per a linea di tendenza in un grafico custruitu nantu à un foglio di Excel. Avemu sapè cumu ciò si pò fà cù un esempio concretu.

1. Avemu un graficu basatu nantu à a tavula di argumenti è valori di a funzione chì era usata per l'esempiu precedente. Fighjà una linea di tendenza ad elli. Facemu clicchì nantu à un locu in a zona di custruzzioni nantu à chì u graficu hè posu cù u bottinu sinu di u mouse. In listessu tempu, un inseme di tabulazioni appaia nant'à u nastro - "U travagliu cù Grafici". Vai à la tabulazione "Layout". Facemu clic nantu à u pulsante "Linea di tendenza"chì hè situatu in u blocu di l'utile "Analisi". Un menu si vede cù una scelta di tippu linea di tendenza. Avemu fermu a scelta nantu à u tippu chì risponde à un compitu specificu. Per u nostru esempio, pudemu sceltu "Approssimazione esponenziale".



2. Excel crea una linea di tendenza in forma di curva nera addizionale diritta nantu à u pianu di cartografiatura.



3. Ora a nostra missione hè di vede u coefficientu di determinazione stessa. Facendu cliccate à dirittu à destra nantu à a linea di tendenza. U menu cuntestu hè attivatu. Stop a selezzione in questu nantu à l'elemento "Formatu di linea di tendenza ...".

   

   Per fà una transizione à a finestra di u formatu di a linea di tendenza, pudete fà una azzione alternativa. Selezziunà a linea di tendenza cliccendu nantu à u cù u bottinu sinu di u mouse. Move to tab "Layout". Facemu clic nantu à u pulsante "Linea di tendenza" in blocu "Analisi". In a lista chì si apre, cliccemu nantu à l'ultimu elementu in a lista di azzioni - "Opzioni avanzate di linea di tendenza ...".



4. Dopu à una qualsiasi di e due azzioni in sopra, una finestra di formattazione hè lanciata in cui pudete fà impostazioni addiziunali. In particulare, per portà a nostra missione, hè necessariu chjarizà a casella accanto à "Mette in u grafico u valore di l'accuratezza di l'approssimazione (R ^ 2)". Si trova in u fondu di a finestra. Hè cusì, in stu modu, includemu a mostra di u coefficiente di determinazione nantu à a zona di custruzzioni. Allora ùn vi scurdate di preme u pulsante "Chiudi" in fondo à a finestra attuale.



5. U valore di cunfidenza di l'approssimazione, chì hè u valore di u coefficiente di determinazione, sarà affissu nantu à u fogliu in a zona di l'intraduzione. In stu casu, stu valore, cum'è si vede, hè uguale a 0.9242, chì caratterizza l'approssimazione, cum'è un mudellu di bona qualità.



6. Assolutamente esattamente cusì pudete piazzà a mostra di u coefficiente di determinazione per qualunqui altru tipu di linea di tendenza. Pudete cambià u tippu di linea di tendenza da fà una transizione per mezu di u bottinu nantu à u nastro o di u menu cuntestu in a so finestra di parametri, cum'è mostra sopra. Poi dighjà in a finestra in u gruppu "Custruendu una linea di tendenza" pò passà à un altru tipu. Ùn vi scurdate di cuntrullà cusì chì vicinu à u puntu "Pone in u diagramma u valore di l'accuratezza di l'approssimazione" era cunsultatu. Dopu avà completatu i passi sopra ripusuti, cliccate nantu à u bottinu. "Chiudi" in u angulu inferiore destro di a finestra.



7. In u casu di un tipu lineare, a linea di tendenza hà già un valore di cunfidenza di 0.9477, chì caratterizeghja questu mudellu cum'è ancu di più affidabile chè a linea di tendenza di tipu esponenziale noi cunsideratu in prima.



8. Dunque, cambià trà diversi tippi di linee di tendenza è confrontendu i so valori di fiducia di approssimazione (coefficiente di determinazione), pudete truvà a variante chì u mudellu di quellu più discrema precisamente u graficu presentatu. A varianti cù u più indice di determinazione hè a più affidabile. In a so basa, pudete custruisce a previsione più precisa.

   Per esempiu, per u nostru casu, per sperimentu, avemu ritrovatu à stabilisce chì u più altu livellu di fiducia hè di tippu polinomiale di a linea di tendenza di u secondu gradu. U coefficimentu di determinazione in questo casu hè uguale a 1. Hè cusì chì stu mudellu hè assolutamente fidindu, chì significa l'eliminazione cumpleta di l'errori.

   

   Ma à u listessu tempu, quessa ùn significa micca quì chì questo tipu di linea di tendenza sarà ancu u più affidabile per un altru tabella. A scelta ottimale di u tippu di linia di tendenza dipende da u tipu di funzione nantu à a basa di a quale u graficu hè statu custruitu. Se l'utente ùn avè micca cunnoscenza per stimà l'opzione di qualità più altu, allora solu l'iddu puderete determinà a prughjizazione migliore hè solu un paragunu di i coefficienti di determinazione, cum'è hè dimostratu in l'esempiu sopra.

Vede ancu:
Costruzione di linee di tendenza in Excel
Approssimazione Excel

In Excel ci sò due opzioni principali per calculà u coefficiente di determinazione: aduprà l'operatore KVPIRSON è strumentu di applicazione "Regressione" da u pacchiu di strumentu "Analisi di dati". In stu casu, a prima di queste opzioni hè destinata à esse utilizata solu in l'elaborazione di una funzione lineare, è un'altra opzione pò esse aduprata in a quasi tutte e situazioni. Inoltre, hè pussibule di vene u coefficiente di determinazione per a linea di tendenza di i grafi cum'è un valore di approssimazione di cunfidenza. Usendu stu indicatore, hè pussibule di determinà u tipu di linea di tendenza chì hà u più altu livello di cunfidenza per una funzione particulare.