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 aux résultats de RSQ dans les versions antérieures d’Excel.

Informations supplémentaires

La fonction RSQ(array1, array2) retourne le carré du coefficient de corrélation de 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 inclut 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.

    A 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 remplit les cellules A1 :D13 de votre feuille de calcul.

  3. Après avoir collé le tableau dans votre nouvelle feuille de calcul Excel, cliquez sur le bouton Options de collage , puis cliquez sur Mettre en forme de la destination. Une fois la plage collée toujours sélectionnée, utilisez l’une des procédures suivantes, en fonction de la version d’Excel que vous exécutez :

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

Remarque

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

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 essentiellement PEARSON et en mettant en quadrature le résultat. Étant donné que PEARSON et CORREL calculent tous deux le coefficient de corrélation de pearson Product-Moment, leurs résultats doivent être d’accord. RSQ aurait pu être (mais n’a pas été) implémenté comme calcul DE CORREL et quadrature du résultat.

Dans les versions d’Excel antérieures à Excel 2003, PEARSON peut présenter des erreurs de 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 qui se trouve 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 à mettre en carré 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 de 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 de 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 unes avec les autres) affichent des erreurs de 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 se comporte toujours bien, 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 de arrondi sont plus profonds avec la formule de calcul utilisée par ces versions d’Excel. Néanmoins, les cas utilisés dans cette expérience peuvent être considérés comme extrêmes.

Si vous avez Excel 2003 ou une version ultérieure d’Excel, vous ne voyez aucune modification dans les valeurs de RSQ et PEARSON^2 si vous essayez l’expérience. Toutefois, les cellules D6 :D13 affichent les erreurs de arrondi 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 un simple passage dans 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 indiquée dans le fichier d’aide des versions antérieures d’Excel. Le fichier d’aide pour RSQ affiche la formule du coefficient de corrélation de l'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 étapes par le biais des 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 calculs, les moyennes (moyennes) des observations X et Y peuvent être calculées. Ensuite, à 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 pour PEARSON. Notez qu’aucune des trois sommes n’est affectée si vous ajoutez une constante à chaque valeur dans le 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 en une seule passe par une approche à deux passes garantit de meilleures performances numériques de PEARSON, et donc de 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 ceux que vous avez 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 qu’il est peu probable que les données classiques présentent le type de comportement inhabituel illustré par cette expérience. 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 par rapport à une moyenne d’échantillon 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 a souvent été appelée « formule de calculatrice », car elle convenait à l’utilisation d’une calculatrice sur un petit nombre de points de données. L’autre procédure a utilisé la procédure suivante :

  • A trouvé 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).

De nombreuses autres fonctions 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 d’une passe par la procédure à deux étapes qui recherche la moyenne de l’échantillon sur la première passe, puis calcule la somme des écarts carrés par rapport à la moyenne de l’échantillon sur la deuxième passe.

La liste suivante répertorie ces fonctions :

  • VAR
  • VARP
  • STDEV
  • STDEVP
  • DVAR
  • DVARP
  • DSTDEV
  • DSTDEVP
  • PRÉVISION
  • PENTE
  • INTERCEPTER
  • PEARSON
  • RSQ
  • STEYX

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