Qu'est-ce que la fonction COEFFICIENT.
Tu sors COEFFICIENT.DETERMINATION (RSQ en anglais) au moment de trancher si un modèle prédictif mérite ta confiance, avant de t'en servir pour projeter le futur. C'est le chiffre qui remplace un nuage de points qu'on regarde à l'oeil, où chacun voit midi à sa porte, par une mesure unique et vérifiable.
C'est la fonction des analystes, data scientists et contrôleurs de gestion qui construisent des modèles prédictifs. Elle te donne en un seul chiffre entre 0 et 1 un verdict objectif sur la solidité de tes prévisions, que tu travailles sur l'impact de la formation sur la productivité, la sensibilité des ventes au prix, ou la corrélation entre température et demande.
Syntaxe
Clique sur un argument pour aller à son explication.
Les deux plages doivent avoir exactement le même nombre de valeurs, sinon Excel renvoie #N/A. Les cellules vides ou contenant du texte dans les plages provoquent une erreur #VALEUR!.
Comprendre chaque paramètre de la fonction COEFFICIENT.DETERMINATION
Le piège est dans l'ordre : c'est y_connus (ce que tu cherches à expliquer, les ventes) qui vient en premier, et x_connus (ce qui explique, le budget) en second. C'est l'inverse de l'intuition de beaucoup, et comme R² reste identique si tu inverses les deux plages, l'erreur passe souvent inaperçue. Les deux sont obligatoires et doivent contenir le même nombre de valeurs numériques.
y_connus
: la plage contenant tes valeurs dépendantes, aussi appelée variable Y ou variable à expliquerPar exemple, si tu cherches à prédire les ventes en fonction du budget marketing, les ventes sont ton y_connus.
Format attendu : une plage de cellules comme B2:B10 contenant uniquement des valeurs numériques. Les cellules vides ou contenant du texte provoquent une erreur. Pense à vérifier tes données après un import CSV.
x_connus
: la plage contenant tes valeurs indépendantes, aussi appelée variable X ou variable explicativeDans l'exemple marketing, ce serait le budget publicitaire. C'est la variable que tu utilises pour expliquer ou prédire Y.
Elle doit avoir exactement la même taille que y_connus (même nombre de lignes). La plage doit contenir uniquement des valeurs numériques.
Exemples pratiques pas à pas
Comment mesurer la qualité d'une régression linéaire avec la fonction COEFFICIENT.DETERMINATION
La réunion budgétaire arrive et tu dois répondre à une question simple en apparence : est-ce que l'argent mis en publicité fait vraiment vendre. Tu as dix-huit mois d'historique avec le budget engagé d'un côté et les ventes réalisées de l'autre, et un nuage de points qui a l'air bien aligné ne suffira pas à emporter la décision. Le R² met un chiffre unique sur cette impression, en mesurant quelle part de la variation des ventes le budget explique. La fonction COEFFICIENT.DETERMINATION le calcule directement à partir des deux colonnes.
- 1Dans une cellule, écris
=COEFFICIENT.DETERMINATION(. - 2En 1er argument : sélectionne la colonne à expliquer,
C2:C19. Ce sont les ventes des dix-huit mois, et c'est leur variation que tu cherches à attribuer au budget. L'ordre surprend au premier abord, parce qu'on a spontanément envie de commencer par la cause plutôt que par l'effet. - 3En 2ᵉ argument : sélectionne la colonne qui explique,
B2:B19. Ce sont les budgets publicitaires engagés mois après mois, et ils sont dix-huit eux aussi. Si les deux plages n'avaient pas le même nombre de lignes, la fonction renverrait#N/A. - 4Ferme la parenthèse et appuie sur Entrée. Le R² sort identique si tu intervertis les deux plages, si bien qu'une erreur d'ordre passe inaperçue sur ce calcul précis, mais elle te trahira dès la première fonction qui distingue vraiment la cause de l'effet.
=COEFFICIENT.DETERMINATION(C2:C19; B2:B19)0,998, que la carte arrondit à 1,00 avec son format à deux décimales. Autrement dit, 99,8 % de la variation des ventes est expliquée par le budget publicitaire à lui seul. Ne lis surtout pas ce 1,00 comme un modèle parfait : il reste une part que le budget n'explique pas, et un R² qui frôle 1 sur des données réelles doit d'abord t'inciter à vérifier que tu n'as pas mis deux fois la même information de part et d'autre.
Comment détecter une relation non linéaire avec la fonction COEFFICIENT.DETERMINATION
On te demande si la température explique la consommation électrique du bâtiment, et l'intuition répond oui sans hésiter. Tu as huit relevés qui couvrent l'année, de 0 à 35 °C, et tu passes le R² dessus pour chiffrer la relation avant de présenter tes conclusions. Le résultat va te surprendre, et c'est tout l'intérêt de le regarder de près : il montre ce que le R² mesure réellement, et surtout ce qu'il est incapable de voir.
- 1Dans une cellule, écris
=COEFFICIENT.DETERMINATION(. - 2En 1er argument : sélectionne la colonne à expliquer,
B2:B9. Ce sont les huit relevés de consommation du bâtiment, exprimés en kilowattheures. C'est bien elle qui dépend de l'autre, donc elle passe en premier. - 3En 2ᵉ argument : sélectionne la colonne explicative,
A2:A9. Ce sont les températures relevées, de0à35degrés, et elles sont huit comme les consommations. - 4Ferme la parenthèse et appuie sur Entrée. Rien ne cloche dans cette formule, et c'est justement ce qui rend le cas instructif. Le résultat qui s'affiche n'est pas une erreur de saisie mais bien la réponse d'un outil qui ne cherche qu'une droite.
=COEFFICIENT.DETERMINATION(B2:B9; A2:A9)0,02, soit 2 % de la variation expliquée, ce qui ressemble à une absence totale de lien. Pourtant le lien crève les yeux : la consommation plonge jusqu'à 190 kWh vers 15 °C, puis remonte à 580 kWh par forte chaleur. Le R² ne mesure que l'alignement sur une droite, et cette courbe en U descend autant qu'elle monte, si bien que les deux moitiés s'annulent. Un R² au ras de zéro ne dit donc pas « aucune relation », il dit « aucune relation droite ».
Comment mesurer la qualité d'un modèle sur une variable transformée avec la fonction COEFFICIENT.DETERMINATION
Le R² au ras de zéro de l'exemple précédent ne condamne pas la relation, il condamne la façon de la décrire. Un bâtiment ne consomme pas parce qu'il fait froid ni parce qu'il fait chaud, mais parce qu'on s'écarte de la température de confort, autour de 18 °C : en dessous on chauffe, au-dessus on climatise. Tu ajoutes donc une colonne qui mesure cet écart avec la formule =ABS(A2-18), et tu relances le même calcul de R² sur cette nouvelle variable plutôt que sur la température brute.
- 1Dans une cellule, écris
=COEFFICIENT.DETERMINATION(. - 2En 1er argument : sélectionne la colonne à expliquer,
C2:C9. Ce sont les mêmes huit consommations qu'à l'exemple précédent, de520kWh à 0 °C jusqu'à580kWh à 35 °C, et aucune n'a été retouchée. - 3En 2ᵉ argument : sélectionne la colonne des écarts à 18 °C,
B2:B9. Elle prend la place de la température brute, et c'est le seul changement de toute la formule. Un relevé à 0 °C et un relevé à 35 °C y valent presque la même chose,18et17, alors que tout les opposait dans la colonne A. - 4Ferme la parenthèse et appuie sur Entrée. Le premier argument n'ayant pas bougé d'une valeur, tout l'écart avec le R² de l'exemple précédent vient de la seule colonne que tu as remplacée.
=COEFFICIENT.DETERMINATION(C2:C9; B2:B9)0,02 à 0,93 sans qu'une seule consommation ait changé. Seule la variable explicative a été remplacée : ce n'est plus la température brute mais son écart à 18 °C. Le modèle colle enfin parce qu'il dit maintenant la bonne chose, à savoir que le bâtiment consomme dès qu'on s'éloigne du confort, qu'il faille chauffer ou refroidir. C'est le R² faible de l'exemple précédent qui t'a mis sur la piste, à condition de ne pas l'avoir lu comme une absence de relation.
Les erreurs fréquentes avec la fonction COEFFICIENT.DETERMINATION
Deux soucis viennent de tes données brutes : des plages de tailles inégales sortent un #N/A, et du texte glissé au milieu des nombres (typique d'un import CSV) déclenche un #VALEUR!. Les deux autres pièges sont plus sournois car la formule ne proteste pas : un R² écrasé près de zéro quand la relation existe mais n'est pas linéaire, et un beau chiffre que tu interprètes sans avoir regardé le nuage de points.
Erreur #N/A : plages de tailles différentes
C'est l'erreur la plus courante. Elle survient quand y_connus et x_connus n'ont pas exactement le même nombre de cellules. Par exemple, B2:B10 (9 cellules) et A2:A12 (11 cellules).
Solution : Vérifie que tes deux plages commencent et finissent au même niveau. Sélectionne la première plage, note le nombre de lignes dans la barre d'état d'Excel, puis assure-toi que la deuxième plage a exactement le même nombre.
Erreur #VALEUR! : données non numériques dans les plages
Cette erreur apparaît quand une ou plusieurs cellules de tes plages contiennent du texte au lieu de nombres. Cela arrive souvent après un import de fichier CSV où les nombres sont stockés en texte.
Solution : Sélectionne tes plages et convertis-les en nombres. Utilise une colonne auxiliaire avec =CNUM(A2) pour convertir le texte en nombre, ou passe par l'option « Convertir en nombre » qui apparaît avec le triangle vert d'avertissement.
R² proche de 0 alors qu'une relation visuelle existe
Si ton R² est très faible mais que ton graphique montre clairement une relation, c'est probablement que cette relation n'est pas linéaire. Elle peut être exponentielle, logarithmique ou polynomiale.
Solution : Transforme tes données avant de calculer le R². Si la relation est exponentielle, calcule le LOG de ta variable Y et refais l'analyse. Ou utilise les outils de régression avancée d'Excel (module Analyse de données, régressions polynomial ou exponentiel).
Interpréter R² sans visualiser les données
Calculer le R² sans tracer un nuage de points peut masquer des patterns importants : valeurs aberrantes, groupes distincts, relations non linéaires. Un bon R² n'est pas une garantie si les données sont mal distribuées.
Solution : Crée toujours un graphique en nuage de points avant de calculer le R² (Insertion → Graphiques recommandés → Nuage de points). Cela prend 10 secondes et peut éviter des conclusions erronées présentées à la direction.
COEFFICIENT.DETERMINATION vs CORREL vs DROITEREG vs PENTE
Utilise COEFFICIENT.DETERMINATION pour évaluer la qualité de ton modèle. Combine-le avec CORREL pour connaître la direction de la relation. Si tu veux faire des prédictions concrètes, utilise DROITEREG qui te donne l'équation complète y = ax + b.
| Critère | COEFFICIENT.DETERMINATION | CORREL | DROITEREG | PENTE |
|---|---|---|---|---|
| Résultat | R² (0 à 1) | r (-1 à 1) | Équation complète | Coefficient a |
| Usage principal | Qualité du modèle | Force et direction | Prédiction complète | Taux de variation |
| Sensibilité à la direction | Non (toujours positif) | Oui (peut être négatif) | Oui (pente + ou -) | Oui (+ ou -) |
| Pour faire des prévisions | Non | Non | Oui | Partiel |
Astuces avancées avec COEFFICIENT.DETERMINATION
Combiner R² avec CORREL pour une vue complète
CORREL te donne la direction de la relation (positive ou négative), tandis que COEFFICIENT.DETERMINATION te donne la force de cette relation. Calcule =COEFFICIENT.CORRELATION(Y;X) puis =COEFFICIENT.DETERMINATION(Y;X) en parallèle pour une analyse complète.
Note que R² = CORREL au carré : si CORREL = 0,9, alors COEFFICIENT.DETERMINATION = 0,81.
Attention aux valeurs aberrantes
Une seule valeur extrême peut fausser complètement ton R². Avant de calculer le coefficient, repère les outliers avec un graphique en nuage de points et décide si tu dois les conserver ou les exclure de ton analyse.
Tu peux calculer deux R² (avec et sans l'outlier) pour mesurer son impact réel sur la qualité du modèle.
R² élevé ne signifie pas causalité
Tu peux avoir un R² de 0,95 entre deux variables qui n'ont aucun lien causal direct : les deux sont simplement influencées par un facteur commun. Un bon R² montre une corrélation, pas une relation de cause à effet.
Garde ton esprit critique et demande-toi toujours si la relation a du sens métier avant de présenter tes conclusions.
Questions fréquentes
R² va de 0 à 1. Un R² de 0,80 signifie que 80% de la variation de ta variable Y est expliquée par ta variable X. Plus R² est proche de 1, plus ton modèle est précis.
En pratique, les seuils dépendent du domaine : en physique, un R² de 0,95+ est attendu. En sciences sociales ou marketing, un R² de 0,30-0,40 peut déjà être significatif car le comportement humain dépend de nombreux facteurs non mesurés.
R² est tout simplement le carré du coefficient de corrélation. Si CORREL renvoie 0,9, alors COEFFICIENT.DETERMINATION renverra 0,81 (soit 0,9²). La différence principale : CORREL peut être négatif, mais R² est toujours positif.
C'est pour ça qu'on utilise R² pour mesurer la qualité d'un modèle sans se soucier du sens de la relation (positive ou négative).
Pas nécessairement. Tout dépend de ton domaine. En marketing ou en RH, un R² de 0,30-0,40 peut être significatif car de nombreux facteurs humains et contextuels influencent les résultats. Un R² de 0,60 pour prédire les ventes est souvent très bon en pratique. L'important est de comparer ton R² à celui des modèles alternatifs dans le même contexte, pas à un seuil absolu.
Non, cette fonction mesure uniquement la qualité d'une régression linéaire. Si tes données suivent une courbe exponentielle ou logarithmique, R² sera faible même si la relation est forte. Dans ce cas, transforme d'abord tes données (log, racine carrée) ou utilise les outils de régression avancée d'Excel (module Analyse de données) qui proposent des régressions polynomiales et exponentielles.
Excel renvoie l'erreur #N/A. Les plages y_connus et x_connus doivent avoir exactement le même nombre de valeurs.
Vérifie que tu n'as pas de cellules vides au milieu de tes plages et que tes deux plages commencent et finissent au même niveau. La barre d'état d'Excel affiche le nombre de cellules sélectionnées pour vérifier rapidement.
Pour aller plus loin
Continue sur ta lancée après la fonction COEFFICIENT.DETERMINATION : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.
Tout voir
