Funzioni statistiche di Excel: RSQ

Riassunto

Questo articolo descrive la funzione RSQ in Microsoft Office Excel 2003 e nelle versioni successive di Excel. Questo articolo illustra come viene usata la funzione e confronta i risultati di RSQ in queste versioni successive di Excel con i risultati di RSQ nelle versioni precedenti di Excel.

Altre informazioni

La funzione RSQ(array1, array2) restituisce il quadrato del coefficiente di correlazione di Pearson Product-Moment tra due matrici di dati.

Sintassi

RSQ(array1, array2)

Gli argomenti, array1 e array2, devono essere numeri o nomi, costanti di matrice o riferimenti che contengono numeri.

L'utilizzo più comune di RSQ include due intervalli di celle che contengono i dati, ad esempio RSQ(A1:A100, B1:B100).

Esempio di utilizzo

Per illustrare la funzione RSQ, seguire questa procedura:

1. Creare un foglio di lavoro di Excel vuoto e quindi copiare la tabella seguente.

   Un	B	C	D
   1		= 3 + 10^$D$2	Potenza di 10 per potenziare i dati
   2	=4 + 10^$D$2		0
   3	=2 + 10^$D$2
   4	=5 + 10^$D$2
   5	=4+10^$D$2
   6	=7+10^$D$2		pre-Excel 2003
   =RSQ(A1:A6,B1:B6)			quando D2 = 7,5
   =PEARSON(A1:A6,B1:B6)^2		RSQ = PEARSON^2	0.492857142857143
   =CORREL(A1:A6,B1:B6)^2		CORREL^2	0.509470304975923
   			quando D2 = 8
   		RSQ = PEARSON^2	#DIV/0!
   		CORREL^2	0.509470304975923

2. Selezionare la cella A1 nel foglio di lavoro di Excel vuoto e quindi incollare le voci in modo che la tabella riempia le celle A1:D13 nel foglio di lavoro.

3. Dopo aver incollato la tabella nel nuovo foglio di lavoro di Excel, selezionare il pulsante Opzioni incolla e quindi selezionare Formatta come destinazione. Con l'intervallo incollato ancora selezionato, usare una delle procedure seguenti, a seconda della versione di Excel in uso.

   • In Microsoft Office Excel 2007 selezionare la scheda Home , selezionare Formato nel gruppo Celle e quindi selezionare Adatta larghezza colonna.
   • In Excel 2003 scegliere Colonna dal menu Formato e quindi selezionare Selezione automatica.

Annotazioni

È possibile formattare le celle B1:B6 come Numero con 0 posizioni decimali.

Le celle A1:A6 e B1:B6 contengono le due matrici di dati usate in questo esempio per chiamare RSQ, PEARSON e CORREL nelle celle A8:A10. RSQ viene calcolato essenzialmente calcolando PEARSON e squando il risultato. Poiché PEARSON e CORREL calcolano entrambi il coefficiente di correlazione di Pearson Product-Moment, i risultati dovrebbero essere d'accordo. RSQ potrebbe essere (ma non è stato) implementato come essenzialmente il calcolo correL e l'ordinamento al quadrato del risultato.

Nelle versioni di Excel precedenti a Excel 2003, PEARSON può presentare errori di round-off. Questo comportamento comporta errori di arrotondamento in RSQ. Il comportamento di PEARSON, e quindi di RSQ, è stato migliorato per Excel 2003 e per le versioni successive di Excel. CORREL è sempre stato implementato utilizzando la procedura migliorata disponibile in Excel 2003 e nelle versioni successive di Excel. Pertanto, un'alternativa a RSQ per una versione precedente di Excel consiste nell'usare CORREL e poi elevare al quadrato il risultato.

Nelle versioni di Excel precedenti a Excel 2003, è possibile utilizzare il foglio di lavoro in questo articolo per eseguire un esperimento e individuare quando si verificano errori di round-off. Se si aggiunge una costante a ognuna delle osservazioni in B1:B6, i valori di RSQ, PEARSON^2 e CORREL^2 nelle celle A7:A9 non devono essere interessati. Se si aumenta il valore in D2, viene aggiunta una costante più grande a B1:B6. Se D2 <= 7, non ci sono errori di arrotondamento che compaiono in A7:A9. Modificare ora il valore 7,25, 7,5, 7,75 e quindi 8. CORREL^2 in A9 non subisce effetti, ma RSQ e PEARSON^2 (queste espressioni sono sempre d'accordo tra loro) mostrano errori di arrotondamento in A7:A8. D6:D13 mostra i valori di RSQ = PEARSON^2 e CORREL^2 quando D2 = 7,5 e 8, rispettivamente.

Si noti che CORREL è ancora ben comportato, ma gli errori di arrotondamento in PEARSON sono diventati così gravi che la divisione per 0 avviene in RSQ e PEARSON^2 quando D2 = 8.

Le versioni precedenti di Excel presentano risposte errate in questi casi perché gli effetti degli errori di arrotondamento sono più profondi con la formula di calcolo usata da queste versioni di Excel. Tuttavia, i casi usati in questo esperimento possono essere considerati estremi.

Se si dispone di Excel 2003 o di una versione successiva di Excel, non vengono visualizzate modifiche nei valori rsq e PEARSON^2 se si prova l'esperimento. Tuttavia, le celle D6:D13 mostrano errori di arrotondamento ottenuti con le versioni precedenti di Excel.

Risultati nelle versioni precedenti di Excel

Se si assegnano un nome alle due matrici di dati X e Y, nelle versioni precedenti di Excel è stato usato un singolo passaggio tra i dati per calcolare la somma dei quadrati di X, la somma dei quadrati di Y, la somma di X, la somma di Y, la somma di XY e il conteggio del numero di osservazioni in ogni matrice. Queste quantità sono state quindi combinate nella formula di calcolo specificata nel file della Guida nelle versioni precedenti di Excel. Il file della Guida per RSQ mostra la formula per il coefficiente di correlazione di Pearson Product-Moment. Questo risultato è quadratizzato per ottenere RSQ.

Risultati in Excel 2003 e nelle versioni successive di Excel

La procedura utilizzata in Excel 2003 e nelle versioni successive di Excel usa un processo a due passaggi attraverso i dati. In primo luogo, vengono calcolate le somme di X e Y e il conteggio del numero di osservazioni in ogni matrice e da questi mezzi (medie) di osservazioni X e Y possono essere calcolati. Quindi, al secondo passaggio, viene trovata la differenza quadrata tra ogni X e la media X e queste differenze quadrate vengono sommate. La differenza quadrata tra ogni Y e la media Y viene trovata e queste differenze quadrate vengono sommate. Inoltre, i prodotti (X - media X) * (Y - media Y) vengono calcolati per ogni coppia di punti dati e sommati. Queste tre somme sono combinate nella formula per PEARSON. Si noti che nessuna delle tre somme è interessata se si aggiunge una costante a ogni valore nella matrice Y (o nella matrice X). Questo comportamento si verifica perché lo stesso valore viene aggiunto alla media Y (o alla media X). Negli esempi numerici, anche con un'elevata potenza di 10 nella cella D12, queste tre somme non sono interessate e i risultati del secondo passaggio sono indipendenti dall'inserimento nella cella D2. Pertanto, i risultati in Excel 2003 e nelle versioni successive di Excel sono più stabili numericamente.

Conclusioni

La sostituzione di un approccio one-pass con un approccio a due passaggi garantisce prestazioni numeriche migliori di PEARSON, e quindi RSQ, in Excel 2003 e nelle versioni successive di Excel. I risultati ottenuti in Excel 2003 e nelle versioni successive di Excel non saranno mai meno accurati rispetto ai risultati ottenuti nelle versioni precedenti di Excel.

Nella maggior parte degli esempi pratici non è probabile che si verifichi una differenza tra i risultati nelle versioni successive di Excel e i risultati nelle versioni precedenti di Excel. Questo comportamento si verifica perché è improbabile che i dati tipici presentino il tipo di comportamento insolito illustrato da questo esperimento. L'instabilità numerica è molto probabile che venga visualizzata nelle versioni precedenti di Excel quando i dati contengono un numero elevato di cifre significative combinate con una variazione relativamente piccola tra valori di dati.

La procedura di ricerca della somma delle deviazioni quadrate su una media campione trovando la media del campione, calcolando ogni deviazione quadrata e sommando le deviazioni quadrate è più accurata rispetto alla procedura alternativa. Questa procedura alternativa è stata spesso denominata "formula calcolatrice" perché era adatta per l'uso di una calcolatrice in pochi punti dati. La procedura alternativa ha utilizzato la procedura seguente:

• Trovata la somma dei quadrati di tutte le osservazioni, il campione e la somma di tutte le osservazioni.
• Calcolata la somma dei quadrati di tutte le osservazioni meno ([somma di tutte le osservazioni]^2) diviso per la dimensione del campione.

Esistono molte altre funzioni migliorate per Excel 2003 e per le versioni successive di Excel. Queste funzioni sono migliorate perché le versioni successive di Excel sostituiscono la routine a un passaggio con la routine a due passaggi che trova la media di esempio nel primo passaggio e quindi calcola la somma delle deviazioni quadrate circa la media del campione al secondo passaggio.

L'elenco seguente è un elenco di tali funzioni:

• VAR
• VARP
• STDEV
• STDEVP
• DVAR
• DVARP
• DSTDEV
• DSTDEVP
• PREVISIONE
• PENDIO
• INTERCETTARE
• PEARSON
• RSQ
• STEYX

Sono stati apportati miglioramenti simili in ognuno dei tre strumenti di Analisi della Varianza nell'Analisi ToolPak.