Analyse de corrélation dans Excel. Exemple d'exécution d'une analyse de corrélation

L'analyse de corrélation est une méthode de recherche courante utilisée pour déterminer le niveau de dépendance de la 1ère valeur à la 2ème. Le tableur dispose d'un outil spécial qui vous permet de mettre en œuvre ce type de recherche.

L'essence de l'analyse de corrélation

Il est nécessaire de déterminer la relation entre deux grandeurs différentes. En d'autres termes, il révèle dans quelle direction (plus petit / plus grand) la valeur change en fonction des changements de la seconde.

Objectif de l'analyse de corrélation

La dépendance est établie lorsque l'identification du coefficient de corrélation commence. Cette méthode diffère de l'analyse de régression, car il n'y a qu'un seul indicateur calculé à l'aide de la corrélation. L'intervalle passe de +1 à -1. Si elle est positive, alors une augmentation de la première valeur contribue à une augmentation de la 2ème. Si négatif, alors une augmentation de la 1ère valeur contribue à une diminution de la 2ème. Plus le coefficient est élevé, plus une valeur affecte la seconde.

Important! Au coefficient 0, il n'y a pas de relation entre les grandeurs.

Calcul du coefficient de corrélation

Analysons le calcul sur plusieurs échantillons. Par exemple, il existe des données tabulaires, où les dépenses en promotion publicitaire et le volume des ventes sont décrits par mois dans des colonnes séparées. Sur la base du tableau, nous découvrirons le niveau de dépendance du volume des ventes à l'argent dépensé pour la promotion publicitaire.

Méthode 1 : détermination de la corrélation via l'assistant de fonction

CORREL - une fonction qui vous permet de mettre en œuvre une analyse de corrélation. Forme générale - CORREL(massiv1;massiv2). Instructions détaillées:

1. Il est nécessaire de sélectionner la cellule dans laquelle il est prévu d'afficher le résultat du calcul. Cliquez sur "Insérer une fonction" situé à gauche du champ de texte pour saisir la formule.

2. L'assistant de fonction s'ouvre. Ici, vous devez trouver CORREL, cliquez dessus, puis sur « OK ».

3. La fenêtre des arguments s'ouvre. Dans la ligne "Array1" vous devez entrer les coordonnées des intervalles de la 1ère des valeurs. Dans cet exemple, il s'agit de la colonne Valeur des ventes. Il vous suffit de sélectionner toutes les cellules qui se trouvent dans cette colonne. De même, vous devez ajouter les coordonnées de la deuxième colonne à la ligne "Array2". Dans notre exemple, il s'agit de la colonne Frais publicitaires.

4. Après avoir entré toutes les plages, cliquez sur le bouton "OK".

Le coefficient était affiché dans la cellule indiquée au début de nos actions. Le résultat obtenu est 0,97. Cet indicateur reflète la forte dépendance de la première valeur à la seconde.

Méthode 2 : Calculer la corrélation à l'aide de l'utilitaire d'analyse

Il existe une autre méthode pour déterminer la corrélation. Ici, une des fonctions trouvées dans le package d'analyse est utilisée. Avant de l'utiliser, vous devez activer l'outil. Instructions détaillées:

1. Allez dans la section "Fichier".

2. Une nouvelle fenêtre s'ouvrira, dans laquelle vous devrez cliquer sur la section "Paramètres".
3. Cliquez sur "Modules complémentaires".
4. On retrouve l'élément « Gestion » en bas. Ici, vous devez sélectionner "Excel Add-ins" dans le menu contextuel et cliquer sur "OK".

5. Une fenêtre spéciale de modules complémentaires s'est ouverte. Cochez la case à côté de l'élément « Package d'analyse ». Nous cliquons sur "OK".
6. L'activation a réussi. Passons maintenant aux données. Le bloc "Analyse" est apparu, dans lequel vous devez cliquer sur "Analyse des données".
7. Dans la nouvelle fenêtre qui apparaît, sélectionnez l'élément « Corrélation » et cliquez sur « OK ».

8. La fenêtre des paramètres d'analyse apparaît à l'écran. Dans la ligne "Intervalle d'entrée", il est nécessaire d'entrer la plage d'absolument toutes les colonnes participant à l'analyse. Dans cet exemple, il s'agit des colonnes "Valeur des ventes" et "Frais publicitaires". Les paramètres d'affichage de sortie sont initialement définis sur Nouvelle feuille de calcul, ce qui signifie que les résultats seront affichés sur une feuille différente. Vous pouvez éventuellement modifier l'emplacement de sortie du résultat. Après avoir effectué tous les réglages, cliquez sur "OK".

Les scores finaux sont sortis. Le résultat est le même que dans la première méthode - 0,97.

Définition et calcul du coefficient de corrélation multiple dans MS Excel

Pour identifier le niveau de dépendance de plusieurs grandeurs, plusieurs coefficients sont utilisés. À l'avenir, les résultats sont résumés dans un tableau séparé, appelé matrice de corrélation.

Guide détaillé:

1. Dans la section « Données », nous trouvons le bloc « Analyse » déjà connu et cliquez sur « Analyse des données ».

2. Dans la fenêtre qui apparaît, cliquez sur l'élément « Corrélation » et cliquez sur « OK ».
3. Dans la ligne "Intervalle d'entrée", nous conduisons dans l'intervalle pour trois colonnes ou plus de la table source. La plage peut être entrée manuellement ou simplement sélectionnée avec le LMB, et elle apparaîtra automatiquement dans la ligne souhaitée. Dans « Regroupement », sélectionnez la méthode de regroupement appropriée. Dans "Paramètre de sortie", spécifiez l'emplacement où les résultats de la corrélation seront affichés. Nous cliquons sur "OK".

4. Prêt! La matrice de corrélation a été construite.

Coefficient de corrélation de paires dans Excel

Voyons comment dessiner correctement le coefficient de corrélation de paire dans une feuille de calcul Excel.

Calcul du coefficient de corrélation de paire dans Excel

Par exemple, vous avez des valeurs x et y.

X est la variable dépendante et y est l'indépendante. Il est nécessaire de trouver le sens et la force de la relation entre ces indicateurs. Instruction étape par étape :

1. Trouvons les valeurs moyennes à l'aide de la fonction CŒUR.

2. Calculons chacun х и xmoy, у и avg à l'aide de l'opérateur "-".

3. Nous multiplions les différences calculées.

4. Nous calculons la somme des indicateurs dans cette colonne. Le numérateur est le résultat trouvé.

5. Calculer les dénominateurs de la différence х и x-moyenne, y и y-moyen. Pour ce faire, nous allons effectuer la quadrature.

6. Utilisation de la fonction AUTOSUMMA, trouvez les indicateurs dans les colonnes résultantes. On fait la multiplication. Utilisation de la fonction TRAITEMENT carré le résultat.

7. Nous calculons le quotient en utilisant les valeurs du dénominateur et du numérateur.

8. CORREL est une fonction intégrée qui vous permet d'éviter les calculs complexes. Nous allons dans «l'assistant de fonction», sélectionnez CORREL et spécifiez les tableaux d'indicateurs х и у. Nous construisons un graphique qui affiche les valeurs obtenues.

Matrice des coefficients de corrélation par paires dans Excel

Analysons comment calculer les coefficients des matrices appariées. Par exemple, il existe une matrice de quatre variables.

Instruction étape par étape:

1. Nous allons dans le "Analyse des données", situé dans le bloc "Analyse" de l'onglet "Données". Sélectionnez Corrélation dans la liste qui s'affiche.
2. Nous définissons tous les paramètres nécessaires. "Intervalle d'entrée" - l'intervalle des quatre colonnes. "Intervalle de sortie" - l'endroit où nous voulons afficher les totaux. Nous cliquons sur le bouton "OK".
3. Une matrice de corrélation a été construite à l'endroit choisi. Chaque intersection d'une ligne et d'une colonne est un coefficient de corrélation. Le chiffre 1 s'affiche lorsque les coordonnées correspondent.

Fonction CORREL pour déterminer la relation et la corrélation dans Excel

CORREL - une fonction utilisée pour calculer le coefficient de corrélation entre 2 tableaux. Regardons quatre exemples de toutes les capacités de cette fonction.

Exemples d'utilisation de la fonction CORREL dans Excel

Premier exemple. Il y a une plaque avec des informations sur les salaires moyens des employés de l'entreprise sur onze ans et le taux de change du $. Il est nécessaire d'identifier la relation entre ces deux grandeurs. Le tableau ressemble à ceci :

L'algorithme de calcul ressemble à ceci :

Le score affiché est proche de 1. Résultat :

Détermination du coefficient de corrélation de l'impact des actions sur le résultat

Deuxième exemple. Deux soumissionnaires ont approché deux agences différentes pour obtenir de l'aide avec une promotion de quinze jours. Chaque jour, un sondage social était mené, qui déterminait le degré de soutien de chaque candidat. Toute personne interrogée pouvait choisir l'un des deux candidats ou s'opposer à tous. Il est nécessaire de déterminer dans quelle mesure chaque promotion publicitaire a influencé le degré de soutien aux candidats, quelle entreprise est la plus efficace.

En utilisant les formules ci-dessous, nous calculons le coefficient de corrélation :

• =CORREL(A3:A17;B3:B17).
• =CORREL(A3:A17;C3:C17).

Résultats:

D'après les résultats obtenus, il ressort clairement que le degré de soutien au 1er candidat augmentait avec chaque jour de promotion publicitaire, par conséquent, le coefficient de corrélation se rapproche de 1. Lors du lancement de la publicité, l'autre candidat avait un grand nombre de confiance, et pour 5 jours, il y avait une tendance positive. Ensuite, le degré de confiance a diminué et au quinzième jour, il est tombé en dessous des indicateurs initiaux. Des scores faibles suggèrent que la promotion a eu un impact négatif sur le soutien. N'oubliez pas que d'autres facteurs concomitants qui ne sont pas pris en compte sous forme de tableau peuvent également affecter les indicateurs.

Analyse de la popularité du contenu par corrélation des vues vidéo et des reposts

Troisième exemple. Une personne pour promouvoir ses propres vidéos sur l'hébergement de vidéos YouTube utilise les réseaux sociaux pour faire la publicité de la chaîne. Il remarque qu'il existe une relation entre le nombre de reposts sur les réseaux sociaux et le nombre de vues sur la chaîne. Est-il possible de prédire les performances futures à l'aide d'outils de tableur ? Il est nécessaire d'identifier le caractère raisonnable de l'application de l'équation de régression linéaire pour prédire le nombre de vues vidéo en fonction du nombre de reposts. Tableau avec valeurs :

Il faut maintenant déterminer la présence d'une relation entre 2 indicateurs selon la formule ci-dessous :

0,7;SI(CORREL(A3:A8;B3:B8)>0,7;”Relation directe forte”;”Relation inverse forte”);”Relation faible ou inexistante”)' class='formule'>

Si le coefficient résultant est supérieur à 0,7, il est alors plus approprié d'utiliser la fonction de régression linéaire. Dans cet exemple, nous faisons :

Maintenant, nous construisons un graphique :

Nous appliquons cette équation pour déterminer le nombre de vues à 200, 500 et 1000 partages : =9,2937*D4-206,12. Nous obtenons les résultats suivants :

Fonction PRÉVISION vous permet de déterminer le nombre de vues à l'instant, s'il y avait, par exemple, deux cent cinquante reposts. Nous appliquons : 0,7;PRÉDICTION(D7;B3:B8;A3:A8);”Les valeurs ne sont pas liées”)' class='formula'>. Nous obtenons les résultats suivants :

Caractéristiques de l'utilisation de la fonction CORREL dans Excel

Cette fonction a les fonctionnalités suivantes :

1. Les cellules vides ne sont pas prises en compte.
2. Les cellules contenant des informations de type booléen et texte ne sont pas prises en compte.
3. La double négation "-" est utilisée pour rendre compte des valeurs logiques sous forme de nombres.
4. Le nombre de cellules dans les tableaux étudiés doit correspondre, sinon le message #N/A sera affiché.

Évaluation de la signification statistique du coefficient de corrélation

Lors du test de la signification d'un coefficient de corrélation, l'hypothèse nulle est que l'indicateur a une valeur de 0, alors que l'alternative n'en a pas. La formule suivante est utilisée pour la vérification :

Conclusion

L'analyse de corrélation dans une feuille de calcul est un processus simple et automatisé. Pour l'exécuter, il vous suffit de savoir où se trouvent les outils nécessaires et comment les activer via les paramètres du programme.