Qu'est-ce que la fonction DROITEREG ?
DROITEREG (LINEST en anglais) te sert dès que PENTE ou TENDANCE ne suffisent plus, parce que tu dois aussi juger la fiabilité du modèle avant de t'y fier : un R² pour savoir si la droite tient la route, une erreur type pour poser un intervalle de confiance. C'est l'outil à sortir quand la prévision seule ne suffit pas et qu'il faut pouvoir la défendre en réunion.
Que tu fasses des prévisions de ventes, des analyses financières ou du contrôle qualité, DROITEREG t'aide à quantifier une relation entre deux variables et à prédire des valeurs futures. Par exemple : modéliser l'évolution mensuelle de tes revenus pour projeter le trimestre suivant, comprendre comment la température affecte la consommation électrique, mesurer le ROI d'une campagne pub avec un intervalle de confiance, ou calculer un taux horaire pur quand les frais fixes sont nuls.
Syntaxe
=DROITEREG(y_connus; [x_connus]; [constante]; [statistiques])Clique sur un argument pour aller à son explication.
Comprendre chaque paramètre de la fonction DROITEREG
Imagine que tu poses une règle sur ton nuage de points pour trouver la droite qui passe au plus près de tout le monde : DROITEREG veut d'abord savoir quoi prédire (y_connus), puis sur quoi se baser ([x_connus]). Si tu laisses [x_connus] de côté, elle invente une échelle 1, 2, 3... à ta place.
Les deux derniers réglages changent tout : [constante] à FAUX cloue ta droite à l'origine (0,0), et c'est [statistiques]=VRAI qui ouvre le tableau complet de 5 lignes sur 2 colonnes, alors que sans lui tu ne récupères que la pente et l'ordonnée.
y_connus
: c'est ta variable dépendante, celle que tu veux prédirePar exemple : les ventes, la consommation électrique, les notes d'étudiants. C'est ce qui change en fonction d'autre chose. Dans l'équation y = mx + b, c'est le y.
Vérifie que tes données sont bien numériques. Les cellules vides, le texte et les valeurs logiques sont ignorées, mais cela réduit le nombre de points et peut affecter la qualité de ta régression.
[x_connus]
: ta variable indépendante, celle qui explique y(facultatif)Par exemple : le temps, la température, le budget marketing. Si tu omets ce paramètre, Excel utilise automatiquement la séquence 1, 2, 3, 4... de même longueur que y_connus.
Les deux plages doivent avoir exactement la même taille et la même orientation (toutes deux en ligne ou toutes deux en colonne). Si tu as 10 valeurs y, tu dois avoir 10 valeurs x, sinon Excel retourne #VALEUR!.
[constante]
: indique si tu veux calculer l'ordonnée à l'origine (b) normalement ou la forcer à 0(facultatif)VRAI ou omis = calcul normal. FAUX = force la droite à passer par l'origine (0,0). Utilise FAUX uniquement si tu es certain que y = 0 quand x = 0.
Attention : Forcer constante=FAUX peut fausser ta régression si la vraie relation n'est pas proportionnelle. Utilise cette option seulement si ta théorie l'impose (par exemple : 0 heure de travail = 0 € de coût).
[statistiques]
: détermine le niveau de détail des résultats(facultatif)VRAI = retourne un tableau complet 5 lignes × 2 colonnes avec toutes les statistiques (R², erreur type, statistique F, etc.). FAUX ou omis = retourne seulement la pente et l'ordonnée (2 valeurs sur une ligne).
Avec statistiques=VRAI, voici la structure du tableau retourné : ligne 1 = Pente / Ordonnée ; ligne 2 = Erreur type m / Erreur type b ; ligne 3 = R² / Erreur type y ; ligne 4 = Statistique F / Degrés de liberté ; ligne 5 = Somme régression / Somme résiduelle.
Exemples pratiques pas à pas
Comment calculer la pente et l'ordonnée d'une tendance avec la fonction DROITEREG
Tes ventes montent depuis huit mois et la direction te demande où elles atterriront le mois prochain. Tu as le chiffre d'affaires de chaque mois dans un tableau, et tu veux la tendance chiffrée plutôt qu'une impression à l'œil. La fonction DROITEREG trace la droite qui colle le mieux à tes points et te rend ses deux paramètres.
- 1Dans une cellule, écris
=DROITEREG(. - 2En 1er argument : sélectionne la colonne des ventes à expliquer,
B2:B9. C'est le chiffre qui monte ou descend d'un mois sur l'autre, et la droite sert justement à mesurer ce mouvement. - 3En 2ᵉ argument : sélectionne la colonne des mois qui sert de repère,
A2:A9. Les deux plages couvrent exactement les mêmes huit lignes, chaque numéro de mois faisant face au chiffre d'affaires qu'il a produit. - 4Ferme la parenthèse et appuie sur Entrée. La fonction rend deux valeurs côte à côte, donc sur une version antérieure à Excel 365 il faut sélectionner les deux cellules avant de taper la formule et valider par Ctrl+Maj+Entrée.
=DROITEREG(B2:B9; A2:A9)
Comment forcer la droite de régression à passer par zéro avec la fonction DROITEREG
Un prestataire te facture à l'heure et tu veux vérifier son vrai taux horaire avant de renégocier le contrat. Tu as relevé les heures et le montant de six interventions, et tu sais qu'à zéro heure la facture tombe à zéro puisqu'il n'y a aucun frais fixe. La fonction DROITEREG sait contraindre sa droite pour respecter cette règle métier.
- 1Dans une cellule, écris
=DROITEREG(. - 2En 1er argument : sélectionne la colonne des montants facturés,
B2:B7. C'est le coût qui dépend du volume d'heures, donc la variable que la droite doit expliquer. - 3En 2ᵉ argument : sélectionne la colonne des heures d'intervention,
A2:A7. Les six lignes se correspondent une à une, chaque volume d'heures face à la facture qu'il a déclenchée. - 4En 3ᵉ argument : écris la valeur qui interdit à la droite de décoller de l'origine,
FAUX. Zéro heure d'intervention donnant forcément zéro euro de facture, aucun montant ne doit s'ajouter en dehors du temps passé. - 5Ferme la parenthèse et appuie sur Entrée.
=DROITEREG(B2:B7; A2:A7; FAUX)
Comment obtenir le R² et le tableau de statistiques de la fonction DROITEREG
Ton comité de direction veut savoir si la météo explique vraiment la facture d'électricité du site avant de voter un budget de chauffage. Tu as huit relevés qui croisent la température moyenne et la consommation, et il te faut un chiffre solide qui prouve que le modèle tient debout. La fonction DROITEREG déroule tout son tableau statistique quand tu lui demandes le détail.
- 1Dans une cellule, écris
=DROITEREG(. - 2En 1er argument : sélectionne la colonne des consommations relevées,
B2:B9. C'est la facture d'électricité qui subit la météo, et non la météo qui suit la facture. - 3En 2ᵉ argument : sélectionne la colonne des températures moyennes,
A2:A9. Elle couvre exactement les mêmes huit relevés, du plus froid à 5 °C au plus doux à 26 °C. - 4En 3ᵉ argument : écris la valeur qui laisse l'ordonnée se calculer librement,
VRAI. À 0 °C la consommation n'est évidemment pas nulle, donc rien ne justifie de coincer la droite sur l'origine. - 5En 4ᵉ argument : écris la valeur qui déroule le tableau complet,
VRAI. Sans ce dernier réglage, la fonction s'arrêterait à la pente et à l'ordonnée, et le R² que ton comité attend ne s'afficherait nulle part. - 6Ferme la parenthèse et appuie sur Entrée. Le résultat occupe 5 lignes sur 2 colonnes, donc sur une version antérieure à Excel 365 il faut sélectionner cette plage entière avant de taper la formule et valider par Ctrl+Maj+Entrée.
=DROITEREG(B2:B9; A2:A9; VRAI; VRAI)
Comment calculer une régression à plusieurs variables avec la fonction DROITEREG
Le budget de l'année prochaine se joue entre deux leviers : payer plus de publicité ou recruter un commercial de plus. Tu as huit mois qui croisent le budget publicitaire, l'effectif commercial et le chiffre d'affaires, et tu veux savoir lequel des deux pèse le plus lourd. La fonction DROITEREG accepte plusieurs colonnes explicatives à la fois et te rend un coefficient pour chacune.
- 1Dans une cellule, écris
=DROITEREG(. - 2En 1er argument : sélectionne la colonne des ventes à expliquer,
C2:C9. C'est le résultat que les deux leviers sont censés produire, sur les huit mois relevés. - 3En 2ᵉ argument : sélectionne les deux colonnes explicatives d'un seul bloc,
A2:B9. C'est ce geste qui déclenche la régression multiple, là où une colonne seule ne rendrait qu'une pente unique. - 4Ferme la parenthèse et appuie sur Entrée. Le résultat s'étale sur trois cellules côte à côte, un coefficient pour chacune des deux colonnes explicatives puis l'ordonnée.
=DROITEREG(C2:C9; A2:B9)
Les erreurs fréquentes avec la fonction DROITEREG
Quand DROITEREG te trahit, c'est rarement bruyant : tu valides avec Entrée et elle n'affiche que la pente en avalant le reste du tableau sans broncher, alors qu'il faut Ctrl+Maj+Entrée pour tout dérouler. Le #VALEUR!, lui, gueule franchement dès que tes plages y_connus et x_connus n'ont pas la même taille ou la même orientation. Les autres ratés viennent de la lecture : un R² proche de 1 dit que la droite colle bien, jamais qu'une variable cause l'autre, et extrapoler loin de tes données te ramène vite des chiffres absurdes.
Oublier la validation matricielle (Ctrl+Maj+Entrée)
C'est l'erreur numéro 1 avec DROITEREG. Si tu valides juste avec Entrée, seule la première valeur s'affiche (la pente). Tu perds toutes les autres statistiques sans message d'erreur.
Solution : Sélectionne d'abord les cellules de destination (2 cellules côte à côte pour le résultat de base, ou une plage 5 lignes × 2 colonnes avec statistiques=VRAI), tape la formule, puis valide avec Ctrl+Maj+Entrée pour obtenir le tableau complet.
Erreur #VALEUR! avec des plages de tailles différentes
Si y_connus contient 10 valeurs et x_connus en contient 8, Excel retourne #VALEUR!. Les deux plages doivent avoir exactement la même taille et la même orientation (toutes deux en ligne ou toutes deux en colonne).
Solution : Vérifie que tes deux plages couvrent exactement le même nombre de cellules et commencent à la même ligne. Si y_connus va de B2:B11, alors x_connus doit aller de A2:A11.
Interpréter un R² élevé comme une preuve de causalité
Un R² proche de 1 indique que le modèle linéaire s'ajuste bien aux données, mais il ne prouve pas une relation de cause à effet. Deux tendances peuvent évoluer ensemble (corrélation) sans lien causal, surtout dans les séries temporelles.
Solution : Utilise le R² comme indicateur de qualité d'ajustement, pas comme preuve de causalité. Confronte toujours le modèle au sens métier : est-ce que la relation a un mécanisme explicatif logique ?
Extrapolation excessive hors de la plage de données
DROITEREG calcule une droite basée sur tes données observées. Prédire très loin en dehors de cette plage peut donner des résultats absurdes, car la tendance peut changer.
Solution : Limite tes prévisions à des valeurs proches de la plage de données observée. Si tu modélises des ventes de mois 1 à 6, une prévision au mois 7 ou 8 est raisonnable, mais une projection au mois 50 est risquée.
Résultats faussés par des valeurs aberrantes (outliers)
DROITEREG utilise la méthode des moindres carrés, qui est très sensible aux valeurs extrêmes. Une seule valeur aberrante peut fortement tirer la droite vers elle et fausser tous tes résultats.
Solution : Avant d'utiliser DROITEREG, crée un nuage de points pour identifier et examiner les outliers. Si une valeur est manifestement une erreur de saisie, corrige-la. Si c'est une vraie valeur, analyse-la séparément plutôt que de la supprimer.
DROITEREG vs PENTE vs TENDANCE vs PREVISION
Toutes ces fonctions tracent la même droite, mais elles ne te rendent pas la même chose une fois le calcul fait. DROITEREG est la seule à te tendre le tableau complet (R², erreur type, statistique F), donc tu la sors dès qu'il faut juger la qualité du modèle ou poser un intervalle de confiance.
Si tu ne veux qu'un chiffre, inutile de sortir l'artillerie matricielle : PENTE et ORDONNEE.ORIGINE t'extraient m ou b tout seuls, PREVISION te crache un point isolé, et TENDANCE calcule directement la série de valeurs ajustées.
| Fonction | Résultat retourné | Quand l'utiliser | Complexité |
|---|---|---|---|
| DROITEREG | Tableau complet (pente, ordonnée, R², erreur type, F…) | Analyse statistique complète, intervalle de confiance | Élevée (matricielle) |
| PENTE | Pente seule (m) | Mesurer le taux de variation d'une tendance | Simple |
| ORDONNEE.ORIGINE | Ordonnée à l'origine seule (b) | Récupérer la valeur de départ du modèle | Simple |
| TENDANCE | Valeurs prédites (tableau) | Calculer directement les valeurs ajustées ou prévues | Moyenne (matricielle) |
| PREVISION | Valeur prédite unique | Prévision d'un seul point, sans analyse des statistiques | Simple |
Astuces avancées avec DROITEREG
Extraire pente et ordonnée avec INDEX
Plutôt que de sélectionner une plage matricielle, utilise =INDEX(DROITEREG(y;x);1;1) pour la pente et =INDEX(DROITEREG(y;x);1;2) pour l'ordonnée dans deux cellules séparées. Tu évites la contrainte de la sélection préalable et tu peux référencer chaque valeur individuellement dans d'autres formules.
Chaque INDEX sur DROITEREG extrait une case précise du tableau sans validation matricielle.
Lire le R² sans tableau complet
Pour récupérer le R² seul sans saisir toute la formule matricielle, utilise =INDEX(DROITEREG(y;x;VRAI;VRAI);3;1) directement dans une cellule. Ligne 3, colonne 1 du tableau DROITEREG = le coefficient de détermination R².
Un R² supérieur à 0,9 indique un très bon ajustement ; en dessous de 0,7, ton modèle linéaire n'est probablement pas adapté à ces données.
Combiner DROITEREG et PEARSON pour valider ton modèle
Avant de lancer une régression complète, vérifie d'abord la corrélation avec PEARSON : si =PEARSON(y;x) est proche de 0, le lien linéaire est faible et DROITEREG ne donnera pas de résultats utiles. Un coefficient de Pearson supérieur à 0,8 (ou inférieur à -0,8) justifie une régression linéaire.
Cette étape de validation évite de construire un modèle sur une relation qui n'existe pas.
Questions fréquentes
PENTE retourne uniquement la pente de la régression linéaire, tandis que DROITEREG retourne un tableau complet avec la pente, l'ordonnée à l'origine, le coefficient R², l'erreur type et d'autres statistiques. Utilise PENTE pour un résultat simple quand tu veux juste mesurer le taux de variation. Utilise DROITEREG pour une analyse complète qui te permet d'évaluer la qualité du modèle et de calculer des intervalles de confiance.
Le R² (coefficient de détermination) mesure la qualité de l'ajustement entre 0 et 1. Un R² de 0,95 signifie que 95 % de la variance des données est expliquée par le modèle linéaire. Plus le R² est proche de 1, meilleur est l'ajustement.
En pratique : un R² supérieur à 0,9 est excellent, entre 0,7 et 0,9 c'est acceptable, en dessous de 0,7 le modèle linéaire n'est probablement pas le bon outil pour ces données.
Cette erreur survient principalement dans trois cas : les plages y_connus et x_connus n'ont pas la même taille, les données contiennent des valeurs non numériques (texte dans les cellules), ou les deux plages n'ont pas la même orientation (une en ligne et l'autre en colonne).
Vérifie que tes deux plages couvrent exactement le même nombre de cellules et qu'elles sont toutes deux orientées en colonnes ou toutes deux en lignes.
Après avoir obtenu la pente (m) et l'ordonnée à l'origine (b) avec DROITEREG, utilise l'équation y = mx + b pour calculer les valeurs prédites. Par exemple, si m=2 et b=5, pour x=10, la prévision est 2×10+5=25.
Tu peux aussi utiliser directement la fonction PREVISION ou TENDANCE si tu veux juste des valeurs prédites sans calculer toi-même l'équation.
Oui, dans les versions Excel antérieures à Excel 365. Si tu valides avec Entrée simple, Excel n'affiche que la première valeur du tableau (la pente) et ignore le reste.
Dans Excel 365 et Excel 2021 avec le moteur de tableaux dynamiques, tu peux valider normalement avec Entrée et DROITEREG déversera automatiquement ses résultats dans les cellules adjacentes. Mais si tu es sur une version antérieure, la validation matricielle reste obligatoire.
Pour aller plus loin
Continue sur ta lancée après la fonction DROITEREG : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.
Tout voir
