Qu'est-ce que la fonction COVARIANCE.
COVARIANCE.STANDARD te sert quand tu veux vérifier, sur un échantillon de données (quelques mois de ventes, un panel de clients), si deux variables bougent dans le même sens ou en sens inverse, avant de t'aventurer plus loin dans une analyse. C'est l'estimation à utiliser dès que tu ne disposes que d'une partie des données, jamais de la population entière.
Que tu travailles en finance, en marketing, en RH ou en analyse de données, cette fonction t'aide à détecter des relations entre tes indicateurs. C'est elle qui te dit si le budget publicitaire est vraiment lié aux ventes, si l'expérience de tes commerciaux influence leur productivité, ou si la température de production réduit le taux de défauts. Elle utilise la correction de Bessel (division par n-1) pour donner une estimation non biaisée à partir d'un échantillon.
Comprendre chaque paramètre de la fonction COVARIANCE.STANDARD
matrice1
: ta première série de données : une plage de valeurs numériques dont tu veux analyser la relation avec une seconde variableIl peut s'agir de budgets publicitaires, de températures de production, de scores de satisfaction client, d'années d'expérience, ou de toute autre série mesurable.
Excel ignore les cellules vides dans le calcul, mais si les deux matrices n'ont pas exactement le même nombre de valeurs après exclusion des vides, tu obtiens #N/A. Assure-toi que tes données sont propres et bien alignées.
matrice2
: ta deuxième série de données, celle que tu veux comparer à la premièreElle doit avoir exactement le même nombre de valeurs que matrice1. Excel analyse chaque paire de valeurs : première ligne de matrice1 avec première ligne de matrice2, et ainsi de suite.
La fonction utilise la correction de Bessel en divisant par n-1 au lieu de n : c'est parfait quand tu travailles avec un échantillon représentatif d'une population plus large, ce qui est le cas dans la grande majorité des analyses pratiques en entreprise.
Exemples pratiques pas à pas
Comment mesurer la covariance d'un échantillon avec la fonction COVARIANCE.STANDARD
Le budget publicitaire varie beaucoup d'un mois à l'autre et la direction veut savoir si ça se voit vraiment sur les ventes, ou si l'argent part en fumée. Tu disposes de 18 mois de dépenses et de chiffre d'affaires, un historique partiel qui ne couvre qu'une fraction de la vie du produit. La fonction COVARIANCE.STANDARD est taillée pour ce cas de figure : elle estime la relation entre deux variables à partir d'un simple échantillon.
- 1Dans une cellule, écris
=COVARIANCE.STANDARD(. - 2
- 3En 2ᵉ argument : sélectionne la colonne des ventes,
C2:C19. Elle couvre les mêmes 18 mois, ligne pour ligne, de façon que chaque budget soit apparié aux ventes du mois où il a été dépensé. Deux hauteurs différentes empêchent de former les paires et renvoient#N/A. - 4Ferme la parenthèse et appuie sur Entrée.
=COVARIANCE.STANDARD(B2:B19; C2:C19)
Comment déduire la pente d'une régression de la fonction COVARIANCE.STANDARD avec VAR.S
Savoir que budget et ventes montent ensemble ne suffit plus, car la question qu'on te pose maintenant est chiffrée : combien rapporte un millier d'euros de publicité en plus ? Tu as huit campagnes avec leur budget et les ventes qui ont suivi. En rapportant la covariance à la variance des seuls budgets, tu obtiens la pente de la droite de régression, c'est-à-dire les ventes gagnées par unité de budget.
- 1Dans une cellule, écris
=COVARIANCE.STANDARD(. - 2En 1er argument : sélectionne la colonne des budgets,
B2:B9. C'est la variable dont tu cherches l'effet sur les ventes, et elle va resservir un peu plus loin au dénominateur. - 3En 2ᵉ argument : sélectionne la colonne des ventes,
C2:C9. Les deux plages couvrent les mêmes huit campagnes, sans quoi les paires ne pourraient pas être formées. Cette première moitié de la formule vaut182,61. - 4Ferme la parenthèse, complète par
/VAR.S(B2:B9), puis appuie sur Entrée. Le dénominateur ne reprend que la colonne des budgets et vaut52,13, puisque la pente cherchée exprime des ventes gagnées par unité de budget.
=COVARIANCE.STANDARD(B2:B9; C2:C9)/VAR.S(B2:B9)
Comment la fonction COVARIANCE.STANDARD provoque l'erreur #DIV/0! sur une seule paire
Le produit vient d'être lancé, un seul mois est clôturé et les autres lignes du suivi attendent encore leurs chiffres. Tu lances quand même la formule sur toute la plage prévue pour l'année, par réflexe. La fonction COVARIANCE.STANDARD ne renvoie alors ni zéro ni un chiffre approximatif : elle affiche une erreur, et cette erreur t'apprend quelque chose sur la façon dont elle calcule.
- 1Dans une cellule, écris
=COVARIANCE.STANDARD(. - 2En 1er argument : sélectionne la plage réservée aux budgets,
B2:B5. Elle court sur les quatre mois prévus au suivi alors qu'une seule cellule est remplie à ce stade. - 3En 2ᵉ argument : sélectionne la plage réservée aux ventes,
C2:C5. Elle est renseignée sur cette même première ligne et vide en dessous, si bien qu'une seule paire complète subsiste une fois les lignes incomplètes écartées. - 4Ferme la parenthèse et appuie sur Entrée. Le diviseur n-1 tombe alors à zéro et Excel affiche
#DIV/0!au lieu d'un nombre.
=COVARIANCE.STANDARD(B2:B5; C2:C5)#DIV/0!. Il faut donc au minimum deux paires complètes, et bien davantage pour que l'estimation ait un sens. C'est la trace la plus visible du diviseur n-1 : dans la même situation, la version population se contente de renvoyer 0.
Les erreurs fréquentes avec la fonction COVARIANCE.STANDARD
Matrices de tailles différentes : erreur #N/A
Tes deux matrices doivent avoir exactement le même nombre de valeurs. Si tu sélectionnes A2:A10 (9 valeurs) et B2:B8 (7 valeurs), les paires ne peuvent pas être constituées et Excel retourne #N/A.
Solution : Vérifie que tes deux plages couvrent exactement le même nombre de lignes : =COVARIANCE.STANDARD(A2:A8; B2:B8). Si tes données ont des cellules vides décalées, utilise EQUIV ou un nettoyage préalable pour aligner les séries.
Échantillon trop petit : erreur #DIV/0!
Avec seulement 1 paire de valeurs, la division par n-1 = 0 est impossible, ce qui provoque #DIV/0!. C'est la limite mathématique de la covariance d'échantillon.
Solution : Assure-toi d'avoir au minimum 2 paires de valeurs numériques. Sur le plan statistique, vise au moins 15 à 20 paires pour que ton estimation soit fiable et représentative : avec 2 ou 3 valeurs, le résultat est mathématiquement valide mais statistiquement très instable.
Confondre covariance élevée et relation forte
La valeur brute de la covariance dépend de l'échelle de tes données. Une covariance de 10 000 entre deux variables en euros n'est pas nécessairement plus forte qu'une covariance de 2 entre deux variables en pourcentage. Comparer des covariances de variables sur des échelles différentes n'a pas de sens.
Solution : Utilise le coefficient de corrélation pour comparer l'intensité des relations : =COEFFICIENT.CORRELATION(A2:A10; B2:B10). Il normalise la covariance entre -1 (relation négative parfaite) et 1 (relation positive parfaite), indépendamment des unités.
Valeurs aberrantes qui faussent tout le calcul
Dans un petit échantillon, une seule valeur extrême (par exemple une erreur de saisie : 10 000 au lieu de 100) peut complètement changer le signe et l'ordre de grandeur de ta covariance.
Solution : Visualise tes données avec un nuage de points avant de calculer la covariance. Repère les outliers visuellement : une valeur isolée très loin des autres mérite vérification. Corrige l'erreur de saisie à la source plutôt que de l'exclure manuellement.
COVARIANCE.STANDARD vs COVARIANCE.PEARSON vs COEFFICIENT.CORRELATION
COVARIANCE.STANDARD est le bon choix par défaut pour les données d'échantillon. COVARIANCE.PEARSON s'utilise uniquement quand tu as la population complète. Pour comparer l'intensité de différentes relations, le coefficient de corrélation est plus lisible.
| Critère | COVARIANCE.STANDARD | COVARIANCE.PEARSON | COEFFICIENT.CORRELATION |
|---|---|---|---|
| Diviseur utilisé | n-1 (correction de Bessel) | n (population entière) | Normalisé (sans unité) |
| Quand l'utiliser | Données d'échantillon | Population complète connue | Comparer l'intensité entre paires |
| Plage de résultats | Valeur brute (dépend des unités) | Valeur brute (dépend des unités) | Entre -1 et 1 |
| Cas d'usage typique | Sondage, panel, historique partiel | Tous les employés d'une entreprise | Tableau de bord de corrélations |
Astuces avancées avec COVARIANCE.STANDARD
Passer à la corrélation pour des comparaisons
La covariance dépend de l'échelle des variables : un résultat de 27,75 entre budget et ventes (en milliers d'euros) n'est pas comparable à un 0,15 entre deux indices. Pour normaliser et comparer, la formule manuelle est =COVARIANCE.STANDARD(A2:A10; B2:B10) / (ECARTYPE.STANDARD(A2:A10) * ECARTYPE.STANDARD(B2:B10)).
Ou plus simplement, utilise directement =COEFFICIENT.CORRELATION(A2:A10; B2:B10) qui effectue ce calcul en une seule étape.
Calculer la pente d'une régression linéaire
La covariance divise par la variance de X pour donner la pente de la droite de régression : =COVARIANCE.STANDARD(A2:A10; B2:B10) / VAR.S(A2:A10). C'est la relation mathématique exacte entre covariance, variance et pente de régression.
Si tu veux directement la pente sans passer par la covariance, utilise =PENTE(B2:B10; A2:A10) qui arrive au même résultat.
Questions fréquentes
La division par n-1 (correction de Bessel) compense le biais quand tu estimes la covariance d'une population à partir d'un échantillon. En utilisant n-1 au lieu de n, tu obtiens une estimation non biaisée, plus fiable pour tes analyses et pour l'inférence statistique.
Utilise STANDARD quand tu travailles avec un échantillon représentatif d'une population plus large : un sondage de 100 clients sur 10 000, 6 mois de ventes sur plusieurs années, un panel de commerciaux. Utilise PEARSON quand tu as toutes les données de la population complète : tous les employés de ton entreprise, tous les produits de ton catalogue.
Non. La corrélation est la covariance normalisée par les écarts-types : si la covariance est élevée, la corrélation l'est aussi. L'inverse est possible : une faible covariance peut masquer une forte corrélation si les variables ont une très petite variance. C'est pourquoi la corrélation est souvent plus utile pour comparer.
COVARIANCE.STANDARD fonctionne dès 2 paires de valeurs (sinon #DIV/0!). Mais pour des résultats statistiquement fiables, vise au moins 15 à 20 paires. Avec moins, ton estimation sera très instable et peu représentative de la vraie relation entre les variables.
Oui. La syntaxe est identique : =COVARIANCE.STANDARD(matrice1; matrice2). La seule différence possible est le séparateur d'arguments : virgule ou point-virgule selon tes paramètres régionaux. Google Sheets gère parfaitement cette fonction statistique.
Pour aller plus loin
Continue sur ta lancée après la fonction COVARIANCE.STANDARD : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.
Tout voir
