L’analyse statistique à 2 variables , ou encore de 2 séries distinctes, vous permet de trouver des connexions entre ces séries et ainsi d'affiner les résultats.
C’est le cas par exemple entre :
- le salaire et l'âge des salariés
- le temps d’utilisation d’une machine outil et le nombre d’unités produites (notre exemple ci-après)
- le chiffre d’affaire des ventes de glaces avec les relevés pluviométrie
- ...
Comment trouver la connexion entre ces séries ? C'est là ou les fonctions de statistiques à 2 variables vont nous aider. Il existe plusieurs fonctions dans Excel comme, COVARIANCE, COEFFICIENT.CORRELATION, DROITEREG ou encore TENDANCE.
COVARIANCE
La fonction COVAR retourne la covariance de 2 séries de données.
La covariance évalue le sens de variation de 2 variables et, ainsi, qualifie l'indépendance de ces variables. Deux autres fonctions calculent la covariance COVARIANCE.S (échantillon) ou COVARIANCE.P (Population entière).
COEFFICIENT.CORRELATION
Calculer le coefficient de corrélation entre 2 variables numériques revient à chercher la liaison qui existe entre les variables à l'aide d'une droite. Un coefficient de corrélation égal à 0 indique que les 2 séries sont indépendantes et inversement, un coefficient de corrélation proche de 1 indique une forte dépendance entre les 2 séries.
DROITEREG
La fonction DROITEREG, dans sa forme simple, renvoie les 2 valeurs qui constituent la droite de régression d’un nuage de points. Mais elle peut également vous renvoyer une analyse très détaillée des valeurs entre-elles.
Présentation
La fonction a besoin de 4 arguments
- Votre série de donnée Y
- Votre série de donnée X (obligatoirement dans cette ordre)
- La prise en compte, ou non d’une constante (Vrai / Faux)
- Afficher les résultats sous forme réduite (FAUX) ou étendue (VRAI)
Une droite a pour équation :
y = ax + b
La valeur de y est déterminée par un coefficient a multiplié à la valeur x auquel est ajouté un terme constant b.
La fonction DROITEREG vous calcule en même temps les coefficients a et b. Si vous travaillez avec une version d'Excel différente d'Excel 365, il faut valider la fonction selon la méthode matricielle (Ctrl + Shift + Entrée)
Exemple de calcul de droite de régression dans sa forme réduite.
Pour déterminer les 2 paramètres de l’équation :
- Sélectionnez les deux cellules qui vont accueillir les résultats (les deux cellules doivent être contiguës et horizontales).
- Saisissez ensuite la formule =DROITEREG
- Sélectionnez la série de valeur correspondant à Y
- Sélectionnez la série de valeur correspondant à X.
- Saisissez la valeur VRAI (calcul de la constante)
- Saisissez la valeur FAUX (écriture réduite)
=DROITEREG(B2:O2;B1:O1;VRAI;FAUX)
Le résultat donné en R2:S2 renvoit les paramètres de l’équation de la droite. A titre de vérification, la droite de tendance affichée dans le graphique donne exactement le même résultat comme vous pouvez le visualiser dans la figure.
Tendance
La fonction TENDANCE vous permet de trouver la valeur d’un élément qui suit la tendance d’une droite de régression.
La fonction TENDANCE a besoin de 4 arguments
- La série des données Y
- La série des données X
- La valeur de X
- La constante (VRAI ou FAUX)
Pour déterminer le nombre d’unité nous estimons produire si nous faisons fonctionner notre machine pendant 8 heures, nous allons écrire la fonction suivante.
= TENDANCE (B2:O2,B1:O1,B5,1) => 168,46
Nous pouvons estimer produire 168 unités si nous laissons notre machine tourner pendant 8 heures.