COEFFICIENT.DETERMINATION (RSQ en anglais) calcule le R², aussi appelé coefficient de détermination. Le R² mesure la qualité d'une régression linéaire en te disant quelle proportion de la variation de tes données est expliquée par ton modèle. En clair : si tu essaies de prédire les ventes en fonction du budget marketing, le R² te dit à quel point ton modèle est fiable.
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 de la fonction COEFFICIENT.DETERMINATION
=COEFFICIENT.DETERMINATION(y_connus; x_connus)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.
Astuce : Trace toujours un nuage de points de tes données avant de calculer le R². Cela te permet de vérifier visuellement si une relation linéaire a du sens, et d'identifier d'éventuelles valeurs aberrantes qui fausseraient le résultat.
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
Directeur marketing : évaluer l'impact du budget publicitaire sur les ventes
Tu es directeur marketing et tu veux savoir si augmenter le budget publicitaire augmente réellement les ventes. Tu as collecté les données des 6 derniers mois et tu veux mesurer la qualité de cette relation avec le R².
Un R² de 0,982 signifie que 98,2% de la variation des ventes est expliquée par le budget pub. C'est un indicateur très fort pour justifier tes futurs investissements marketing auprès de la direction et construire un modèle de prévision fiable.
| A | B | C | |
|---|---|---|---|
| 1 | Mois | Budget pub (k€) | Ventes (k€) |
| 2 | Janvier | 10 | 45 |
| 3 | Février | 15 | 62 |
| 4 | Mars | 12 | 53 |
| 5 | Avril | 20 | 85 |
| 6 | Mai | 18 | 76 |
| 7 | Juin | 25 | 102 |
=COEFFICIENT.DETERMINATION(C2:C7;B2:B7)Analyste pricing : tester la sensibilité du volume aux variations de prix
Tu es analyste pricing et tu testes différents prix pour ton produit phare. Tu veux savoir si le volume vendu est fortement corrélé au prix pratiqué pour optimiser ta stratégie tarifaire.
Un R² de 0,893 confirme que le prix est un facteur déterminant du volume : 89,3% de la variation des ventes s'explique par le prix. Tu peux maintenant construire un modèle de pricing fiable et calculer l'élasticité-prix pour optimiser ton chiffre d'affaires.
| A | B | C | |
|---|---|---|---|
| 1 | Semaine | Prix unitaire (€) | Volume vendu |
| 2 | S1 | 49,90 | 820 |
| 3 | S2 | 44,90 | 1050 |
| 4 | S3 | 54,90 | 620 |
| 5 | S4 | 39,90 | 1280 |
| 6 | S5 | 59,90 | 480 |
| 7 | S6 | 47,90 | 910 |
=COEFFICIENT.DETERMINATION(C2:C7;B2:B7)Data analyst : valider un modèle prédictif de demande
Tu es data analyst et tu as développé un modèle pour prédire la demande en fonction de la température. Avant de déployer ce modèle en production, tu veux vérifier qu'il est suffisamment fiable.
Avec un R² de 0,947, ton modèle est très solide : 94,7% de la variation des ventes de glaces est expliquée par la température. Tu peux l'utiliser en toute confiance pour prévoir les besoins en stock à partir des prévisions météo. Les 5,3% restants s'expliquent par d'autres facteurs (weekend, événements locaux, etc.).
| A | B | C | |
|---|---|---|---|
| 1 | Jour | Température (°C) | Ventes glaces (unités) |
| 2 | Lundi | 18 | 45 |
| 3 | Mardi | 23 | 78 |
| 4 | Mercredi | 28 | 125 |
| 5 | Jeudi | 31 | 156 |
| 6 | Vendredi | 26 | 98 |
| 7 | Samedi | 21 | 62 |
| 8 | Dimanche | 25 | 89 |
=COEFFICIENT.DETERMINATION(C2:C8;B2:B8)Responsable RH : mesurer l'impact de la formation sur la productivité
Tu es responsable RH et tu as investi dans un programme de formation. Tu veux prouver au COMEX que les heures de formation augmentent réellement la productivité de tes équipes, avec des chiffres à l'appui.
Un R² de 0,856 est excellent dans le contexte RH où de nombreux facteurs influencent la performance. Tu as un argument chiffré solide pour continuer à investir dans la formation et demander plus de budget lors du prochain arbitrage.
| A | B | C | |
|---|---|---|---|
| 1 | Employé | Heures formation | Productivité (%) |
| 2 | Marie | 5 | 78 |
| 3 | Pierre | 15 | 85 |
| 4 | Sophie | 8 | 82 |
| 5 | Lucas | 20 | 92 |
| 6 | Emma | 12 | 88 |
| 7 | Thomas | 3 | 74 |
| 8 | Julie | 18 | 90 |
=COEFFICIENT.DETERMINATION(C2:C8;B2:B8)Envie de t'entraîner sur de vrais exercices Excel ?
M'entraîner
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 =VALEUR(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 =CORREL(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 sur la fonction COEFFICIENT.DETERMINATION
Comment interpréter la valeur de R² dans mon modèle ?
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.
Quelle est la relation entre COEFFICIENT.DETERMINATION et CORREL ?
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).
Un R² faible signifie-t-il que mon modèle est inutile ?
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.
Peut-on utiliser COEFFICIENT.DETERMINATION pour des relations non linéaires ?
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.
Que faire si mes deux plages n'ont pas la même taille ?
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
Bloqué sur une formule Excel ?
Pose ta question à notre assistant Excel IA, il te sort la bonne formule en quelques secondes.
Essayer l'assistant IAGratuit · 10 questions par mois