Fonctions statistiques Excel : RSQ

Résumé

Cet article décrit la fonction RSQ dans Microsoft Office Excel 2003 et dans les versions ultérieures d’Excel. Cet article explique comment la fonction est utilisée et compare les résultats de RSQ dans ces versions ultérieures d’Excel avec les résultats de RSQ dans les versions antérieures d’Excel.

Plus d’informations

La fonction RSQ(array1, array2) retourne le carré du coefficient de corrélation pearson Product-Moment entre deux tableaux de données.

Syntaxe

RSQ(array1, array2)

Les arguments, array1 et array2 doivent être des nombres ou des noms, des constantes de tableau ou des références qui contiennent des nombres.

L’utilisation la plus courante de RSQ comprend deux plages de cellules qui contiennent les données, telles que RSQ(A1 :A100, B1 :B100).

Exemple d’utilisation

Pour illustrer la fonction RSQ, procédez comme suit :

1. Créez une feuille de calcul Excel vide, puis copiez le tableau suivant.

   Un	B	C	D
   1		= 3 + 10^$D$2	Puissance de 10 à ajouter aux données
   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		pré-Excel 2003
   =RSQ(A1 :A6,B1 :B6)			quand D2 = 7,5
   =PEARSON(A1 :A6,B1 :B6)^2		RSQ = PEARSON^2	0.492857142857143
   =CORREL(A1 :A6,B1 :B6)^2		CORREL^2	0.509470304975923
   			quand D2 = 8
   		RSQ = PEARSON^2	#DIV/0 !
   		CORREL^2	0.509470304975923

2. Sélectionnez la cellule A1 dans votre feuille de calcul Excel vide, puis collez les entrées afin que le tableau remplisse les cellules A1 :D13 dans votre feuille de calcul.

3. Après avoir collé le tableau dans votre nouvelle feuille de calcul Excel, sélectionnez le bouton Options de collage, puis sélectionnez Adapter à la mise en forme de destination. Avec la plage collée toujours sélectionnée, utilisez l'une des procédures suivantes, en fonction de la version d'Excel que vous utilisez :

   • Dans Microsoft Office Excel 2007, sélectionnez l’onglet Accueil , sélectionnez Format dans le groupe Cellules , puis ajuster automatiquement la largeur de colonne.
   • Dans Excel 2003, pointez sur Colonne dans le menu Format , puis sélectionnez Ajuster automatiquement la sélection.

Remarque

Vous pouvez mettre en forme les cellules B1 :B6 en tant que nombre avec 0 décimales.

Les cellules A1 :A6 et B1 :B6 contiennent les deux tableaux de données utilisés dans cet exemple pour appeler RSQ, PEARSON et CORREL dans les cellules A8 :A10. RSQ est calculé en calculant PEARSON puis en élevant le résultat au carré. Étant donné que PEARSON et CORREL calculent le coefficient de corrélation pearson Product-Moment, leurs résultats doivent être d’accord. RSQ peut être (mais n’a pas été) implémenté comme calculant essentiellement CORREL et quarant le résultat.

Dans les versions d’Excel antérieures à Excel 2003, PEARSON peut présenter des erreurs d’arrondi. Ce comportement entraîne des erreurs d’arrondi dans RSQ. Le comportement de PEARSON, et donc de RSQ, a été amélioré pour Excel 2003 et pour les versions ultérieures d’Excel. CORREL a toujours été implémenté à l’aide de la procédure améliorée trouvée dans Excel 2003 et dans les versions ultérieures d’Excel. Par conséquent, une alternative à RSQ pour une version antérieure d’Excel consiste à utiliser CORREL à la place, puis à carrér le résultat.

Dans les versions d’Excel antérieures à Excel 2003, vous pouvez utiliser la feuille de calcul de cet article pour exécuter une expérience et découvrir quand des erreurs d’arrondi se produisent. Si vous ajoutez une constante à chacune des observations dans B1 :B6, les valeurs de RSQ, PEARSON^2 et CORREL^2 dans les cellules A7 :A9 ne doivent pas être affectées. Si vous augmentez la valeur en D2, une constante plus grande est ajoutée à B1 :B6. Si D2 <= 7, aucune erreur d’arrondi n’apparaît dans A7 :A9. Modifiez maintenant la valeur 7.25, 7.5, 7.75, puis 8. CORREL^2 dans A9 n’est pas affecté, mais RSQ et PEARSON^2 (ces expressions sont toujours d’accord les uns avec les autres) affichent des erreurs d’arrondi dans A7 :A8. D6 :D13 affiche les valeurs de RSQ = PEARSON^2 et CORREL^2 lorsque D2 = 7,5 et 8, respectivement.

Notez que CORREL est toujours stable, mais les erreurs d’arrondi dans PEARSON sont devenues si graves que la division par 0 se produit dans RSQ et PEARSON^2 lorsque D2 = 8.

Les versions antérieures d’Excel présentent des réponses incorrectes dans ces cas, car les effets des erreurs d’arrondi sont plus profonds avec la formule de calcul utilisée par ces versions d’Excel. Toutefois, les cas utilisés dans cette expérience peuvent être considérés comme extrêmes.

Si vous disposez d’Excel 2003 ou d’une version ultérieure d’Excel, vous ne voyez aucune modification des valeurs de RSQ et PEARSON^2 si vous essayez l’expérience. Toutefois, les cellules D6:D13 présentent des erreurs d’arrondi similaires à celles que vous auriez obtenues avec les versions antérieures d’Excel.

Résultats dans les versions antérieures d’Excel

Si vous nommez les deux tableaux de données X et Y, les versions antérieures d’Excel utilisaient une seule passe par les données pour calculer la somme des carrés de X, la somme des carrés de Y, la somme des X, la somme des Y, la somme des XY et le nombre d’observations dans chaque tableau. Ces quantités ont ensuite été combinées dans la formule de calcul qui est donnée dans le fichier d’aide dans les versions antérieures d’Excel. Le fichier d’aide pour RSQ affiche la formule du coefficient de corrélation Product-Moment Pearson. Ce résultat est carré pour obtenir RSQ.

Résultats dans Excel 2003 et dans les versions ultérieures d’Excel

La procédure utilisée dans Excel 2003 et dans les versions ultérieures d’Excel utilise un processus à deux pas via les données. Tout d’abord, les sommes de X et Y et le nombre d’observations dans chaque tableau sont calculées, et à partir de ces moyens (moyennes) des observations X et Y peuvent être calculées. Ensuite, sur la deuxième passe, la différence carrée entre chaque X et la moyenne X est trouvée, et ces différences carrées sont additionnées. La différence carrée entre chaque Y et la moyenne Y est trouvée, et ces différences carrées sont additionnées. En outre, les produits (X – X moyenne) * (Y – Y moyenne) sont trouvés pour chaque paire de points de données et additionnés. Ces trois sommes sont combinées dans la formule de PEARSON. Notez qu’aucune des trois sommes n’est affectée si vous ajoutez une constante à chaque valeur du tableau Y (ou dans le tableau X). Ce comportement se produit parce que cette même valeur est ajoutée à la moyenne Y (ou à la moyenne X). Dans les exemples numériques, même avec une puissance élevée de 10 dans la cellule D12, ces trois sommes ne sont pas affectées, et les résultats de la deuxième passe sont indépendants de l’entrée dans la cellule D2. Par conséquent, les résultats dans Excel 2003 et dans les versions ultérieures d’Excel sont plus stables numériquement.

Conclusions

Le remplacement d’une approche à passe unique par une approche à deux pas garantit de meilleures performances numériques de PEARSON, et donc RSQ, dans Excel 2003 et dans les versions ultérieures d’Excel. Les résultats que vous obtenez dans Excel 2003 et dans les versions ultérieures d’Excel ne seront jamais moins précis que les résultats obtenus dans les versions antérieures d’Excel.

Dans la plupart des exemples pratiques, vous n’êtes pas susceptible de voir une différence entre les résultats dans les versions ultérieures d’Excel et les résultats dans les versions antérieures d’Excel. Ce comportement se produit parce que les données classiques sont peu susceptibles d’exposer le type de comportement inhabituel que cette expérience illustre. L’instabilité numérique est plus susceptible d’apparaître dans les versions antérieures d’Excel lorsque les données contiennent un nombre élevé de chiffres significatifs combinés à une variation relativement faible entre les valeurs de données.

La procédure de recherche de la somme des écarts carrés sur un échantillon moyen en recherchant la moyenne de l’échantillon, en calculant chaque écart carré et en additionnant les écarts carrés est plus précise que la procédure alternative. Cette autre procédure s’appelait fréquemment la « formule de calculatrice », car elle convenait à l’utilisation d’une calculatrice sur quelques points de données. La procédure alternative a utilisé la procédure suivante :

• Trouver la somme des carrés de toutes les observations, la taille de l'échantillon et la somme de toutes les observations
• Calcule la somme des carrés de toutes les observations moins ([somme de toutes les observations]^2)/taille de l’échantillon).

Il existe de nombreuses autres fonctions qui ont été améliorées pour Excel 2003 et pour les versions ultérieures d’Excel. Ces fonctions sont améliorées, car les versions ultérieures d’Excel remplacent la procédure directe par la procédure à deux pass qui recherche l’exemple moyen sur la première passe, puis calcule la somme des écarts carrés sur la moyenne de l’exemple sur la deuxième passe.

La liste suivante est une liste de ces fonctions :

• VAR
• Programme d'analyse de variance (VARP)
• STDEV
• STDEVP
• DVAR
• DVARP
• DSTDEV
• DSTDEVP
• PRÉVISION
• PENTE
• INTERCEPTION
• PEARSON
• RSQ
• STEYX

Des améliorations similaires ont été apportées dans chacun des trois outils d’analyse de variance dans l’outil d’analyse.