Funzioni statistiche di Excel: RSQ

Riepilogo

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.

Ulteriori 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 contenenti 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.

   A	B	C	D
   1		= 3 + 10^$D$2	Potenza di 10 per l'aggiunta ai 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, fare clic sul pulsante Opzioni incolla e quindi fare clic su Corrispondenza formattazione destinazione. Con l'intervallo incollato ancora selezionato, usare una delle procedure seguenti, in base alle esigenze per la versione di Excel in esecuzione:

   • In Microsoft Office Excel 2007 fare clic sulla scheda Home , fare clic su Formato nel gruppo Celle e quindi su Adatta automaticamente larghezza colonna.
   • In Excel 2003 scegliere Colonna dal menu Formato e quindi fare clic su Adatta selezione automaticamente.

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 quadrando il risultato. Poiché PEARSON e CORREL calcolano entrambi il coefficiente di correlazione Product-Moment Pearson, i risultati devono essere d'accordo. RSQ avrebbe potuto essere (ma non) implementato come essenzialmente il calcolo CORREL e la quadratura del risultato.

Nelle versioni di Excel precedenti a Excel 2003, PEARSON potrebbe presentare errori di arrotondamento. Questo comportamento causa 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 usando 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 quindi nel quadrato del risultato.

Nelle versioni di Excel precedenti a Excel 2003, è possibile usare il foglio di lavoro in questo articolo per eseguire un esperimento e individuare quando si verificano errori di arrotondamento. 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 sono presenti errori di arrotondamento visualizzati in A7:A9. Modificare ora il valore di 7,25, 7,5, 7,75 e quindi 8. CORREL^2 in A9 non è interessato, 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 RSQ = PEARSON^2 e CORREL^2 rispettivamente quando D2 = 7,5 e 8.

Si noti che CORREL è ancora ben educato, ma gli errori di arrotondamento in PEARSON sono diventati così gravi che la divisione per 0 si verifica 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 ha Excel 2003 o 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 che si sarebbero ottenuti con le versioni precedenti di Excel.

Risultati nelle versioni precedenti di Excel

Se si denominano le due matrici di dati X e Y, le versioni precedenti di Excel usavano 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 X, 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 viene quadrato 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 utilizza un processo a due passaggi tra 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 i mezzi (medie) delle osservazioni X e Y possono essere calcolati. Quindi, al secondo passaggio, viene trovata la differenza al quadrato tra ogni X e la media X e queste differenze al quadrato vengono sommate. La differenza al quadrato tra ogni Y e la media Y viene trovata e queste differenze al quadrato vengono sommate. Inoltre, i prodotti (X - X media) * (Y - Y media) vengono trovati per ogni coppia di punti dati e sommati. Queste tre somme vengono 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 una potenza elevata di 10 nella cella D12, queste tre somme non sono interessate e i risultati del secondo passaggio sono indipendenti dalla voce nella cella D2. Di conseguenza, i risultati in Excel 2003 e nelle versioni successive di Excel sono numericamente più stabili.

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 dei risultati ottenuti nelle versioni precedenti di Excel.

Nella maggior parte degli esempi pratici, è probabile che non si noti 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 mostrino il tipo di comportamento insolito illustrato dall'esperimento. È più probabile che l'instabilità numerica venga visualizzata nelle versioni precedenti di Excel quando i dati contengono un numero elevato di cifre significative combinate con variazioni relativamente scarse tra i valori dei dati.

La procedura per trovare la somma delle deviazioni al quadrato rispetto a una media campione trovando la media del campione, calcolando ogni deviazione al quadrato e sommando le deviazioni al quadrato è più accurata della procedura alternativa. Questa procedura alternativa è stata spesso denominata "formula calcolatrice" perché è adatta per l'uso di una calcolatrice su un numero ridotto di punti dati. La procedura alternativa ha utilizzato la procedura seguente:

• È stata trovata la somma dei quadrati di tutte le osservazioni, la dimensione del campione e la somma di tutte le osservazioni
• Calcolata la somma dei quadrati di tutte le osservazioni meno ([somma di tutte le osservazioni]^2)/dimensione del campione).

Sono state migliorate molte altre funzioni per Excel 2003 e per le versioni successive di Excel. Queste funzioni vengono 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 al quadrato sulla media del campione al secondo passaggio.

L'elenco seguente è un elenco di tali funzioni:

• VAR
• VARP
• STDEV
• STDEVP
• DVAR
• DVARP
• DSTDEV
• DSTDEVP
• PREVISIONI
• PENDENZA
• INTERCETTARE
• PEARSON
• RSQ
• STEYX

Miglioramenti simili sono stati apportati in ognuno dei tre strumenti di analisi della varianza nella toolpak di analisi.