La régression linéaire avec Excel : Guide complet

Parmi les concepts statistiques à maîtriser absolument pour concevoir des analyses prédictives et des modèles d’apprentissage automatique, il y a la régression linéaire. S’il s’agit d’une des bases mathématiques à connaître, son calcul peut s’avérer plus ou moins complexe. Heureusement, il existe différents outils permettant de rendre vos prédictions beaucoup plus fiables. C’est notamment le cas du calcul de la régression linéaire avec Excel. Alors comment faire ? Liora vous explique toutes les étapes à suivre.

C’est quoi la régression linéaire ?

La régression linéaire est une méthode statistique qui sert à modéliser la relation entre une variable dépendante que l’on veut prédire et une ou plusieurs variables indépendantes qui l’expliquent. Elle ajuste une droite qui décrit au mieux, en moyenne, comment la valeur de la variable dépendante évolue lorsque les variables explicatives varient.

Y = aX + b

où :

• Y = la variable dépendante que vous essayez de prédire.
• X = la variable indépendante utilisée pour expliquer Y.
• a = le coefficient de pente, qui indique de combien Y change quand X augmente d’une unité.
• b = l’ordonnée à l’origine, la valeur de Y lorsque X est égal à zéro.

En pratique, on rencontre souvent la forme Y = aX + b + ε où ε représente les erreurs de prédiction (résidus), c’est‑à‑dire l’écart entre les valeurs observées et celles prévues par le modèle.

L’objectif est de créer un modèle utilisable pour faire des prédictions sur Y à partir des valeurs de X.

On distingue deux cas : la régression linéaire simple lorsqu’il n’y a qu’une seule variable indépendante X, et la régression linéaire multiple lorsqu’il y en a plusieurs. Exemple : prédire le prix d’un logement à partir de sa surface seule (simple), puis à partir de sa surface, de son âge et de son quartier (multiple), que ce soit dans Excel ou Power BI.

Pourquoi et quand utiliser Excel pour la régression ?

Pour explorer rapidement une relation linéaire, Excel fait très bien le travail sur des jeux de données petits à moyens. Sans code, vous pouvez obtenir en quelques minutes une pente, une interception et un R², puis visualiser le tout dans un nuage de points avec ligne de tendance. Selon vos besoins, cela passe par les fonctions DroiteReg et Tendance, ou par l’option Analyse de Données qui fournit un rapport complet avec coefficients, valeurs de p et résidus.

En comparaison d’environnements comme Python ou R, Excel convient très bien au prototypage, au reporting et aux analyses ad hoc partagées avec des équipes métiers. Il atteint toutefois ses limites dès que le volume de données augmente, que les workflows se complexifient ou que vous avez besoin d’une évaluation rigoureuse des modèles (séparation entraînement/test, validation croisée, régularisation, automatisation reproductible). Dans ces cas, mieux vaut basculer vers des outils dédiés au Machine Learning.

• Excel est suffisant si :
  • vous travaillez sur des données structurées de taille modeste, avec peu de variables explicatives ;
  • vous devez aller vite pour tester une hypothèse, estimer une tendance ou bâtir un premier modèle interprétable ;
  • vous souhaitez intégrer directement résultats et graphiques à un fichier partagé ou une présentation ;
  • vous avez besoin d’indicateurs simples à expliquer aux parties prenantes (pente, intercept, R², valeurs de p).

• le volume de données est important, ou le nombre de variables et d’interactions devient élevé ;
• vous devez mettre en place des procédures d’évaluation solides et automatisées (train/test, validation croisée, sélection de variables, régularisation) ;
• vous avez besoin de modèles au-delà du linéaire simple ou multiple, ou de pipelines de préparation de données avancés ;
• vous devez versionner, industrialiser et rejouer facilement vos analyses.

• inspecter les données et tracer un nuage de points avant d’ajuster un modèle ;
• vérifier les hypothèses de base de la régression linéaire : relation approximativement linéaire, résidus sans structure marquée et de variance homogène ;
• se souvenir que corrélation n’implique pas causalité, et documenter les hypothèses de votre modèle ;
• centraliser les sorties de l’option Analyse de Données dans une feuille dédiée pour faciliter la relecture.

Comment préparer les données dans Excel ?

1. Rassembler et nettoyer la base : centralisez vos sources dans une seule feuille, supprimez les lignes totalement vides et les doublons (Données > Supprimer les doublons). Triez rapidement chaque colonne pour repérer des valeurs anormales, des fautes de frappe et des unités incohérentes.
2. Nommer clairement les colonnes : utilisez des en-têtes uniques, courts et explicites, sans caractères spéciaux ni espaces superflus. Placez Y (variable dépendante) et vos X (variables explicatives) dans des colonnes distinctes. Conservez une seule ligne d’en-têtes afin de pouvoir cocher l’option Étiquettes dans l’outil Analyse de Données.
3. Vérifier les formats numériques : assurez-vous que les nombres sont bien reconnus comme nombres. Si Excel signale « nombre stocké en texte », utilisez la commande de conversion ou Données > Texte en colonnes avec le type Général. Unifiez les séparateurs décimaux et de milliers via Rechercher/Remplacer et appliquez des formats cohérents (Nombre, Pourcentage, Monétaire). Pour les dates, convertissez-les en vraies dates reconnues par Excel via Texte en colonnes en choisissant le bon ordre jour/mois/année.
4. Uniformiser les unités et les catégories : alignez toutes les unités (par exemple tout en euros, tout en kg) et les libellés catégoriels (mêmes orthographes et capitalisations) pour éviter les doublons cachés.
5. Gérer les valeurs manquantes : quantifiez d’abord les manquants avec un filtre. S’il y en a peu, supprimez les lignes concernées. Sinon, appliquez une stratégie simple et documentée, par exemple une imputation par médiane ou moyenne pour les numériques, ou la modalité la plus fréquente pour les catégorielles. Évitez de mélanger cellules vides, « n/a » et erreurs, choisissez une représentation unique et filtrez-les avant le calcul de la régression, car celle-ci attend des valeurs numériques.
6. Détecter les outliers en amont : créez un nuage de points entre Y et chaque X pour repérer visuellement les points très éloignés. Complétez avec des statistiques descriptives simples, comme la moyenne et l’écart-type, puis isolez temporairement les observations au-delà de seuils raisonnables. Vérifiez la cause métier avant d’exclure un point, consignez votre décision et, si besoin, conservez une colonne « Inclure » pour piloter l’analyse.
7. Figer une version prête pour l’analyse : dupliquez l’onglet en « Données nettoyées », vérifiez une dernière fois la cohérence des en-têtes, puis seulement ensuite lancez DROITEREG, TENDANCE ou l’outil Analyse de Données décrit plus haut.

Conseil pratique : mettez vos données « prêtes » Mettre sous forme de tableau pour bénéficier automatiquement des filtres, références structurées et d’une mise à jour plus fiable des plages. Identifiez clairement Y et X avant toute régression, et gardez une feuille « Brute » intacte pour pouvoir revenir en arrière si nécessaire.

Exemple : vous souhaitez prédire Ventes (Y) à partir de Prix et Température (X). 1) Renommez les colonnes en « Ventes », « Prix », « Température » et unifiez les unités. 2) Convertissez « 1.234,50 » ou « 1,234.50 » en nombres homogènes via Texte en colonnes puis appliquez le format Nombre. 3) Filtrez et traitez les cellules vides de « Prix ». 4) Créez un nuage de points Ventes vs Température et vérifiez l’absence de points aberrants évidents. 5) Sur l’outil Régression de l’Analyse de Données, sélectionnez vos plages en cochant Étiquettes, puis générez la sortie.

Comment activer l’outil Analyse de données (ToolPak) ?

Avant de lancer une régression avec Excel, vérifiez que le complément « Utilitaire d’analyse » (Analysis ToolPak) est bien chargé. Une fois activé, la commande « Analyse de données » apparaît dans l’onglet Données, groupe « Analyse ».

Sur Windows (Microsoft 365, 2024, 2021, 2019)

1. Ouvrez Excel puis Fichier > Options > Compléments.
2. En bas, dans « Gérer », choisissez « Compléments Excel », puis cliquez sur « Atteindre… ».
3. Cochez « Analysis ToolPak » (et, si nécessaire pour des macros, « Analysis ToolPak, VBA »), puis validez avec OK.
4. Retournez dans l’onglet Données et contrôlez la présence du bouton « Analyse de données » dans le groupe « Analyse ».
5. Si le bouton n’apparaît pas, fermez et rouvrez Excel, puis réessayez.

Sur Mac (Microsoft 365, 2024, 2021)

1. Allez dans Outils > Compléments Excel…
2. Cochez « Analysis ToolPak », puis cliquez sur OK.
3. Quittez puis redémarrez Excel pour finaliser le chargement.
4. Dans l’onglet Données, vérifiez que « Analyse de données » est visible dans le groupe « Analyse ».

Sur Microsoft 365 (Excel pour le web)

Excel pour le web ne propose pas l’outil « Régression » de l’Utilitaire d’analyse. Vous pouvez consulter des résultats existants, mais pour exécuter une régression, ouvrez le classeur dans l’application de bureau et utilisez l’onglet Données > « Analyse de données ».

Vérifier sa présence avant de lancer une régression

1. Ouvrez votre fichier dans l’application de bureau.
2. Allez dans Données > « Analyse de données » (groupe « Analyse »). S’il n’apparaît pas, suivez l’activation ci-dessus.
3. Si le bouton reste introuvable ou grisé, fermez et rouvrez Excel, puis vérifiez que vous n’êtes pas dans Excel pour le web et que les compléments ne sont pas bloqués par la politique de votre organisation.

En résumé, Windows et Mac permettent d’activer le ToolPak via les « Compléments Excel » et d’accéder ensuite à « Analyse de données » dans l’onglet Données, tandis qu’Excel pour le web ne permet pas d’exécuter la régression. Dans l’article, lorsque vous utiliserez l’option « Analyse de Données », commencez par ce contrôle rapide pour éviter les erreurs au moment de paramétrer la « Régression ».

Quelle méthode choisir : DROITEREG, TENDANCE ou l’outil Régression ?

Selon votre objectif, vous ne choisirez pas la même approche dans Excel. Besoin d’estimer des coefficients et d’obtenir des statistiques d’ajustement, envie d’un modèle simple ou multiple, ou recherche d’une prévision rapide sans détails d’inférence, voici comment décider entre DROITEREG, TENDANCE et l’outil Analyse de Données (Régression).

Outil	Objectif idéal	Type de modèle	Sortie principale	Interprétation	Quand l’utiliser
DROITEREG (LINEST)	Estimer les coefficients et piloter des calculs directement dans la feuille	Simple et multiple	Pentes et intercept. Possibilité de statistiques supplémentaires via la formule matricielle avec options	Lisible si vous êtes à l’aise avec les formules. Permet d’obtenir la valeur de R² et des erreurs types avec les bons arguments	Quand vous voulez réutiliser les coefficients dans d’autres cellules, automatiser ou construire un modèle multiple léger
TENDANCE (TREND)	Prévoir rapidement Y pour de nouvelles valeurs de X	Principalement des usages simples	Valeurs prédites. N’isole pas la pente ni l’ordonnée à l’origine	Interprétation limitée, pas de p-values ni d’ANOVA	Quand vous avez besoin d’une série de prévisions pour un tableau ou un graphique, sans analyse statistique détaillée
Outil Régression dans Analyse de Données	Analyse complète avec estimation et diagnostics	Simple et multiple	Tableaux détaillés : statistiques de régression, ANOVA, coefficients, erreurs types, t, p-values, intervalles de confiance, résidus	Idéal pour documenter, justifier un modèle, comparer des spécifications et communiquer les résultats	Quand vous devez interpréter, reporter des résultats, vérifier la significativité et la qualité d’ajustement

En résumé, DROITEREG convient si vous souhaitez garder la main dans les cellules et, au besoin, récupérer des métriques d’ajustement via la formule matricielle. TENDANCE est la voie la plus rapide pour obtenir des valeurs prédites, sans détails d’inférence. L’outil Régression d’Analyse de Données est le meilleur choix si vous devez interpréter le modèle : vous y trouverez p-values, ANOVA, R², erreurs types et résidus pour juger la pertinence statistique et la qualité de vos prédictions.

• Vous voulez des prévisions immédiates pour quelques points et un graphique : utilisez TENDANCE ou ajoutez une courbe de tendance sur un nuage de points avec affichage de l’équation et du R².
• Vous devez estimer et réutiliser des coefficients, avec un modèle simple ou multiple : optez pour DROITEREG.
• Vous avez besoin d’interprétation (p-values, ANOVA, intervalles de confiance) et d’un rapport clair : passez par l’outil Régression de l’Analyse de Données.

Comment effectuer une régression avec l’outil Analyse de données ?

Le complément Analyse de données d’Excel propose un module « Régression » qui génère en quelques clics un rapport complet pour une régression linéaire simple ou multiple. Suivez ce pas-à-pas pour sélectionner vos plages, choisir les options utiles et obtenir une sortie exploitable.

1. Préparez vos données: placez la variable dépendante Y dans une colonne, et les variables explicatives X dans des colonnes adjacentes. Mettez des étiquettes en en-tête de chaque colonne et évitez les cellules vides.
2. Allez dans l’onglet « Données », cliquez sur « Analyse de données », puis choisissez « Régression » et validez.
3. Renseignez la « Plage d’entrée Y » en sélectionnant la colonne de votre variable dépendante, étiquette comprise si vous en avez une.
4. Renseignez la « Plage d’entrée X » en sélectionnant les colonnes contiguës des variables explicatives, étiquettes comprises.
5. Cochez « Étiquettes » si vos sélections incluent les en-têtes de colonnes.
6. Dans « Options », activez au besoin « Niveau de confiance » et indiquez la valeur souhaitée (par exemple 95 %). Cochez « Résidus » et « Résidus normalisés » pour obtenir un diagnostic d’ajustement. L’option « Constante égale à zéro » force l’ordonnée à l’origine à 0 lorsque c’est pertinent.
7. Choisissez l’emplacement de sortie: « Nouvelle feuille de calcul » pour un rapport dédié, « Plage de sortie » pour afficher le résultat dans la feuille active, ou « Nouveau classeur ».
8. Cliquez sur « OK ». Excel crée le rapport de régression et, selon vos choix, les tableaux et diagnostics associés.

Paramétrer l’outil Régression (plages, étiquettes, options)

1. Y en entrée: sélectionnez la colonne de la variable à prédire, idéalement avec son étiquette.
2. X en entrée: sélectionnez les colonnes adjacentes des variables explicatives. Pour une régression multiple, incluez toutes les colonnes X côte à côte.
3. Étiquettes: cochez si vos premières lignes contiennent les noms de variables.
4. Niveau de confiance: laissez 95 % par défaut ou adaptez selon votre besoin d’intervalle plus étroit ou plus large.
5. Résidus et diagnostics: cochez « Résidus » et « Résidus normalisés ». Selon votre version, vous pouvez aussi générer les « Tracés des résidus », le « Tracé d’ajustement de la ligne » ou le « Tracé de probabilité normale » pour vérifier les hypothèses du modèle.
6. Emplacement de sortie:
   • « Nouvelle feuille de calcul »: recommandé pour un rapport clair et complet.
   • « Plage de sortie »: indiquez la cellule de départ si vous souhaitez insérer le rapport dans la feuille en cours.
   • « Nouveau classeur »: utile pour isoler totalement l’analyse.

Sorties générées par Excel (ANOVA, coefficients, résidus)

Le rapport de l’outil « Régression » comprend plusieurs tableaux. Ils servent à évaluer la qualité d’ajustement du modèle, la significativité des variables et la distribution des erreurs.

Bloc de sortie	Ce que vous y trouvez	À quoi cela sert
Statistiques de régression	R multiple, R², R² ajusté, erreur standard, nombre d’observations	Mesure la qualité globale d’ajustement. R² et R² ajusté quantifient la part de variance de Y expliquée par X.
ANOVA	df, SS, MS, F, Significativité F	Teste si le modèle apporte une amélioration significative par rapport à un modèle sans variables explicatives.
Coefficients	Intercept, coefficients des variables X, erreur standard, t, valeur p, IC bas et haut au niveau choisi	Évalue l’effet de chaque variable. Les valeurs p et les intervalles de confiance aident à juger de la significativité et de l’amplitude des effets.
Sorties résiduelles	Y prédit, résidu, résidu normalisé, éventuellement tracés des résidus	Diagnostique les hypothèses: résidus centrés, dispersion homogène, absence de structure marquée. Aide à repérer les points influents.

Interprétez d’abord le bloc « Statistiques de régression » pour juger de l’ajustement global. Vérifiez ensuite l’ANOVA pour la significativité du modèle, puis le tableau des coefficients pour déterminer quelles variables expliquent réellement Y. Terminez par les résidus et leurs tracés afin de confirmer que les hypothèses du modèle sont raisonnablement satisfaites.

Comment interpréter les résultats de la régression dans Excel ?

La sortie « Régression » d’Excel regroupe tout ce qu’il faut pour passer d’un modèle à une décision. L’idée clé : relier chaque chiffre à une question métier simple (sens et ampleur de l’effet, qualité d’ajustement, pertinence statistique, incertitude et précision prédictive) afin de juger si votre équation peut vraiment guider l’action.

Coefficients (pente, intercept) et sens des signes

Dans le tableau « Coefficients », chaque pente mesure la variation attendue de Y quand X augmente d’une unité, toutes choses égales par ailleurs. Un signe positif indique un effet direct, un signe négatif un effet inverse. L’intercept (ordonnée à l’origine) est la valeur prédite de Y quand toutes les X valent 0. L’échelle s’interprète dans les unités d’origine : un coefficient de 2,5 « euros par m² » n’a pas le même impact opérationnel qu’un 0,025 « pourcentage par point d’index ».

Exemple rapide : si « Température » a un coefficient de +9,7, une hausse d’un degré augmente la vente moyenne d’environ 9,7 unités, à prix constant. Si « Prix » vaut −37,7, +1 unité de prix diminue la vente d’environ 37,7 unités.

R² et R² ajusté, que mesurent-ils ?

Le R² indique la part de variance de Y expliquée par le modèle, entre 0 et 1. Plus il est élevé, meilleure est l’adéquation apparente.

Le R² ajusté corrige l’optimisme du R² en présence de plusieurs variables. Il pénalise les prédicteurs superflus. À privilégier pour comparer des modèles de tailles différentes : si R² monte mais que le R² ajusté stagne ou baisse, l’ajout n’apporte pas d’information utile.

Erreur standard, t et p-value : sont-ils significatifs ?

Chaque coefficient est estimé avec une incertitude. Excel fournit l’erreur standard du coefficient, la statistique t et la p-value correspondante pour tester H0: « coefficient = 0 ».

• Erreur standard du coefficient : plus elle est faible, plus l’estimation est précise.
• t Stat = coefficient / erreur standard : plus |t| est grand, plus l’évidence contre H0 est forte.
• p-value : probabilité d’obtenir un tel |t| si H0 était vraie. Seuils usuels 0,05 ou 0,01. p-value petite, effet statistiquement significatif.
• Attention : significatif ne veut pas dire important. Jaugez aussi l’amplitude du coefficient et sa pertinence métier.

ANOVA et F-statistique : le modèle est-il globalement pertinent ?

La table ANOVA décompose la variance et calcule la statistique F qui teste H0: « aucun prédicteur n’explique Y ». Une F élevée avec une p-value associée très faible signifie que le modèle, pris dans son ensemble, apporte une explication meilleure qu’un modèle sans variables.

Exemple : si « Signification F » est inférieure à 0,05, vous pouvez conclure que votre combinaison de variables explique Y de façon globalement pertinente.

Intervalles de confiance des coefficients (95 %)

1. Lisez les bornes « 95 % inf » et « 95 % sup » pour chaque coefficient.
2. Si l’intervalle ne contient pas 0, l’effet est statistiquement différent de 0 au niveau 5 %.
3. Plus l’intervalle est étroit, plus l’estimation est précise. Comparez ces bornes avec des seuils métier pour juger de l’impact minimal/plausible.

Astuce décision : si l’intervalle plausible d’un coefficient inclut des effets contraires à votre tolérance au risque (par exemple, une baisse potentielle de marge), envisagez de collecter plus de données ou de simplifier le modèle.

Mesures d’erreur (RMSE, MAE, SSE)

Au-delà du R², mesurez la précision prédictive via les résidus e = Y observé − Y prédit. Placez Y observé en B2:Bn et Y prédit en C2:Cn :

Mesure	Ce que cela évalue	Formule Excel (exemple)	Lecture rapide
SSE	Somme des carrés des erreurs	=SOMMEPROD((B2:B101-C2:C101)^2)	Plus petit, meilleur. Utile pour comparer des modèles sur le même jeu de données.
RMSE	Erreur quadratique moyenne, en unités de Y	=RACINE(MOYENNE((B2:B101-C2:C101)^2))	Se compare directement à Y. Proche de « Erreur type de la régression » d’Excel sur l’échantillon.
MAE	Erreur absolue moyenne	=MOYENNE(ABS(B2:B101-C2:C101))	Mesure robuste, moins sensible aux valeurs extrêmes que le RMSE.

Bon réflexe : complétez ces chiffres par un graphique des résidus pour vérifier qu’ils sont centrés, d’ampleur similaire et sans structure évidente. Si un motif apparaît, votre modèle peut être incomplet ou non linéaire.

Comment utiliser DROITEREG (LINEST) pour une régression simple et multiple ?

La fonction matricielle DROITEREG (LINEST) calcule une régression linéaire simple ou multiple et peut renvoyer, au choix, uniquement les coefficients du modèle ou un bloc complet de statistiques. Sous Excel récent, les résultats débordent automatiquement dans les cellules adjacentes, sous Excel plus ancien il faut valider en mode matriciel (Ctrl + Maj + Entrée). Les séparateurs d’arguments (; ou ,) dépendent de vos paramètres régionaux.

Syntaxe de DROITEREG et options (constante, stats)

Syntaxe : DROITEREG(y_connus; x_connus; [constante]; [statistiques])

• y_connus : plage des valeurs dépendantes Y.
• x_connus : plage des valeurs indépendantes X. Pour une régression multiple, indiquez une plage 2D contenant toutes les colonnes prédictrices (par exemple B2:D17).
• constante : VRAI ou omis pour estimer l’interception b, FAUX pour forcer b = 0.
• statistiques : VRAI pour renvoyer des statistiques supplémentaires, FAUX ou omis pour ne renvoyer que les coefficients.
• Bloc de sortie quand statistiques=VRAI : 5 lignes par (k + 1) colonnes, où k est le nombre de prédicteurs :
  • Ligne 1 : coefficients des X et, en dernière colonne, l’interception b.
  • Ligne 2 : erreurs types des coefficients et de b.
  • Ligne 3 : R² puis l’erreur type de l’estimation (sey).
  • Ligne 4 : statistique F puis degrés de liberté (ddl).
  • Ligne 5 : somme des carrés de régression (SSreg) puis somme des carrés des résidus (SSresid).

Exemple régression simple avec DROITEREG (et récupération des stats)

Cas d’école : Y en A2:A17, X en B2:B17.

1. Saisissez dans une cellule vide : =DROITEREG(A2:A17;B2:B17). Excel renvoie la pente et l’interception (deux cellules).
2. Pour le bloc complet de statistiques, utilisez : =DROITEREG(A2:A17;B2:B17;;VRAI). Sous Excel récent, le bloc se déverse automatiquement. Sous Excel plus ancien, sélectionnez au préalable une zone de 5 lignes sur 2 colonnes puis validez avec Ctrl + Maj + Entrée.
3. Extraire les principaux indicateurs avec INDEX :
   • Pente : =INDEX(DROITEREG(A2:A17;B2:B17;;VRAI);1;1)
   • Interception b : =INDEX(DROITEREG(A2:A17;B2:B17;;VRAI);1;2)
   • Erreur type de la pente : =INDEX(DROITEREG(A2:A17;B2:B17;;VRAI);2;1)
   • Erreur type de b : =INDEX(DROITEREG(A2:A17;B2:B17;;VRAI);2;2)
   • R² : =INDEX(DROITEREG(A2:A17;B2:B17;;VRAI);3;1)
   • F et ddl : =INDEX(...;4;1) et =INDEX(...;4;2)
4. Besoin de t et de p-valeurs ? Calculez la statistique t comme coefficient / erreur_type, puis utilisez une fonction de loi de Student pour obtenir la p-valeur.

Exemple régression multiple avec DROITEREG (plusieurs X)

Cas d’école : Y en A2:A17, prédicteurs X1, X2, X3 en B2:D17.

1. Placez le curseur dans la cellule où commencera la sortie, puis entrez : =DROITEREG(A2:A17;B2:D17;VRAI;VRAI).
2. Lecture des coefficients (ligne 1) :
   • INDEX(...;1;1) renvoie le coefficient de la dernière colonne de x_connus (ici X3 en D).
   • INDEX(...;1;3) renvoie le coefficient de la première colonne (X1 en B).
   • INDEX(...;1;4) renvoie l’interception b.
3. Erreurs types des coefficients : même logique sur la ligne 2 (INDEX(...;2;col)).
4. Qualité globale du modèle : R² et sey en ligne 3, F et ddl en ligne 4, SSreg et SSresid en ligne 5.
5. Astuce de mise en forme : nommez vos colonnes de X clairement et conservez l’ordre logique des prédicteurs, vous éviterez toute confusion due à l’ordre de retour de DROITEREG.

Comment prédire avec TENDANCE/FORECAST et calculer des intervalles ?

Pour passer de la régression à la prévision opérationnelle, partez des coefficients estimés (pente a et intercept b) ou utilisez les fonctions de prédiction d’Excel. Alimentez une colonne de futures valeurs de X (indices de période ou dates), puis générez les Y prévus pour piloter un budget, un plan de production ou un atterrissage mensuel.

• Si vous avez besoin d’une série de prévisions sur plusieurs périodes, utilisez TENDANCE, qui « déborde » facilement sur un ensemble de nouveaux X.
• Pour prédire une valeur unique de Y à X donné, utilisez PRÉVISION.LINEAIRE (FORECAST.LINEAR), la version moderne de PRÉVISION.
• Pour encadrer vos prévisions, calculez un intervalle de confiance ou de prédiction avec l’erreur standard et la loi de Student.

La fonction TENDANCE (TREND) : syntaxe et exemples

La fonction TENDANCE renvoie des valeurs le long d’une droite ajustée par moindres carrés. Elle est idéale pour projeter plusieurs périodes d’un coup. La syntaxe générale est : =TENDANCE(y_connus; x_connus; [nouveaux_x]; [constante]). Elle n’isole pas les paramètres de pente et d’ordonnée à l’origine, contrairement à DROITEREG qui fournit ces coefficients explicitement.

1. Prévoir une seule valeur
   =TENDANCE(B2:B25; A2:A25; E2)
   où A2:A25 sont les X connus, B2:B25 les Y connus, et E2 contient le nouveau X.
2. Prévoir plusieurs périodes à la suite
   Placez vos futurs X dans E2:E13, puis saisissez dans F2 :
   =TENDANCE(B2:B25; A2:A25; E2:E13)
   Le résultat remplit F2:F13. Avec Excel moderne, vous pouvez aussi générer les futurs X à la volée :
   =TENDANCE(B2:B25; A2:A25; SEQUENCE(6;1;MAX(A2:A25)+1;1))
3. Quand X est implicite et régulier (1, 2, 3, …), vous pouvez omettre x_connus :
   =TENDANCE(B2:B25;;SEQUENCE(6))
4. Forcer une régression sans intercept (b = 0) si le modèle le justifie :
   =TENDANCE(B2:B25; A2:A25; E2:E13; FAUX)

Rappel utile issu de l’article d’origine : bien que la formule soit proche de DROITEREG, TENDANCE n’affiche pas directement la pente et l’intercept. Si vous souhaitez piloter vos prévisions à partir des coefficients, calculez-les avec =DROITEREG(Y;X;;VRAI), puis appliquez Y_prévus = a*X + b dans votre feuille.

FORECAST.LINEAR et PRÉVISION : différences et usages

Depuis Excel 2016, PRÉVISION.LINEAIRE (FORECAST.LINEAR) remplace la fonction historique PRÉVISION tout en gardant la même logique de régression linéaire simple.

Fonction	Disponibilité	Syntaxe (FR)	Usage recommandé	Remarques
PRÉVISION.LINEAIRE (FORECAST.LINEAR)	Excel 2016 et versions ultérieures	=PREVISION.LINEAIRE(x; y_connus; x_connus)	Prédire une valeur de Y pour un X donné	Remplace PRÉVISION. Accepte les références classiques et peut s’intégrer dans des formules dynamiques.
PRÉVISION (FORECAST)	Hérité, pour compatibilité	=PREVISION(x; y_connus; x_connus)	Maintien de classeurs existants	Fonction dépréciée. À migrer vers PRÉVISION.LINEAIRE dans les nouveaux fichiers.
TENDANCE (TREND)	Toutes versions	=TENDANCE(y_connus; x_connus; [nouveaux_x]; [constante])	Projeter plusieurs périodes d’un coup	Pratique pour « déverser » une série complète de prévisions sur un vecteur de nouveaux_x.

En pratique, PRÉVISION.LINEAIRE et TENDANCE reposent sur le même modèle linéaire. Choisissez PRÉVISION.LINEAIRE pour une valeur unique de X, et TENDANCE pour générer toute une série. Pour des données saisonnières, tournez-vous plutôt vers PRÉVISION.ETS.

Intervalles de confiance/prédiction : calcul pas-à-pas dans Excel

Les intervalles quantifient l’incertitude autour d’une prévision. L’intervalle de confiance encadre la moyenne attendue de Y pour un X donné, tandis que l’intervalle de prédiction encadre une observation future individuelle, toujours plus large.

1. Calculez la prévision ponctuelle
   Dans F2 : =PREVISION.LINEAIRE(E2; B2:B25; A2:A25) ou, pour une série, utilisez =TENDANCE(...).
2. Obtenez l’erreur standard de la régression
   Option 1 : via DROITEREG(Y;X;;VRAI) qui renvoie, parmi ses statistiques, l’« erreur-type y ». Option 2 : calculez-la à partir des résidus :
   , En G2, résidu : =B2 - PREVISION.LINEAIRE(A2; B$2:B$25; A$2:A$25), recopiez jusqu’à G25
   , En H1, effectif : =NB(A2:A25)
   , En H2, erreur standard s : =RACINE(SOMMEPROD((G2:G25)^2)/(H1-2))
3. Préparez les quantités communes
   , Moyenne de X en H3 : =MOYENNE(A2:A25)
   , Somme des carrés de X en H4 (SSx) : =(H1-1)*(ECARTTYPE(A2:A25)^2)
4. Valeur critique de Student pour un niveau de confiance 95 % (alpha = 0,05) et ddl = H1 − 2
   En H5 : =LOI.STUDENT.INVERSE.BILATERALE(0,05; H1-2)
5. Écart-type de la moyenne prédite à X = E2
   En H6 : =H2*RACINE(1/H1 + (E2-H3)^2/H4)
6. Intervalle de confiance 95 %
   En F3 : borne inférieure : =F2 - H5*H6
   En F4 : borne supérieure : =F2 + H5*H6
7. Écart-type de prédiction individuelle
   En H7 : =H2*RACINE(1 + 1/H1 + (E2-H3)^2/H4)
8. Intervalle de prédiction 95 %
   En F5 : borne inférieure : =F2 - H5*H7
   En F6 : borne supérieure : =F2 + H5*H7

Notes de méthode : pour une régression multiple, remplacez les degrés de liberté par n − p − 1 (avec p le nombre de variables explicatives) et privilégiez les sorties de l’outil Analyse de données pour récupérer l’erreur standard et les degrés de liberté. Si X sont des dates, construisez nouveaux_x avec des fonctions calendaires adaptées (par exemple FIN.MOIS pour des pas mensuels).

Comment visualiser et présenter la régression dans Excel ?

Un bon graphique rend votre régression immédiatement compréhensible. L’objectif, côté analyse, est de vérifier la relation et les hypothèses du modèle. Côté communication, il s’agit de livrer un visuel clair, lisible et sourcé.

Créer un nuage de points propre (labels, formats)

1. Sélectionnez vos deux colonnes de données, avec X en abscisse (variable explicative) et Y en ordonnée (variable à expliquer).
2. Allez dans Insertion, puis choisissez le nuage de points (Dispersion).
3. Dans Outils de graphique, ouvrez Ajouter un élément au graphique pour activer le Titre du graphique, les Titres d’axes et, si utile, la Légende.
4. Nommez précisément le graphique et les axes (unités, période). Appliquez un format de nombre cohérent: séparateur de milliers, décimales utiles seulement.
5. Rendez les points lisibles: couleur sobre avec contraste, taille 4 à 6 pt, éventuellement 60 à 70 % d’opacité pour éviter la sursaturation. Supprimez les lignes de grille si elles distraient la lecture.
6. Ajoutez des étiquettes de données uniquement pour quelques points clés (valeurs extrêmes, cas emblématiques) afin de ne pas surcharger le graphique.

Conseil pratique: vérifiez l’échelle des axes. Des bornes trop larges écrasent les variations, trop serrées accentuent artificiellement les tendances.

Ajouter la droite de tendance et afficher l’équation/R²

1. Faites un clic droit sur la série de points, puis sélectionnez Ajouter une courbe de tendance.
2. Choisissez Linéaire.
3. Cochez Afficher l’équation sur le graphique.
4. Cochez Afficher le coefficient de détermination (R²).

Lecture rapide: l’équation Y = aX + b résume la relation (a est la pente, b l’ordonnée à l’origine). R² indique la part de variabilité expliquée par la droite, entre 0 et 1. Plus R² est proche de 1, plus l’ajustement est bon, dans la limite de la pertinence du modèle et des données.

Personnaliser la ligne de tendance (extrapolation, formats)

• Extrapolation: dans Options de la courbe de tendance, utilisez Prévision vers l’avant pour projeter quelques périodes. Restez parcimonieux et indiquez visuellement la zone extrapolée.
• Ordonnée à l’origine: cochez Fixer l’ordonnée à l’origine uniquement si la théorie l’exige (par exemple, passage imposé par 0). Sinon, laissez Excel estimer b.
• Styles: augmentez l’épaisseur à 2 ou 3 pt, choisissez une couleur contrastée par rapport aux points et au fond. Harmonisez les polices avec le reste du rapport.
• Axes: définissez des bornes min./max. et un pas régulier. Conservez la même échelle entre graphiques comparables pour éviter les biais visuels.
• Légende et annotation: renommez la série de tendance en “Régression linéaire” ou “Y ~ X”, ajoutez une note brève sur la source des données.

Astuce communication: placez l’équation et R² dans une zone dégagée du graphique. Si nécessaire, copiez les valeurs dans une zone de texte formatée pour assurer une parfaite lisibilité.

Graphiques diagnostiques: résidus vs ajustés, histogramme/QQ-plot

Avant de conclure, vérifiez les hypothèses de base avec quelques visuels simples. Utilisez l’outil Analyse de données > Régression, en cochant l’export des valeurs ajustées et des résidus.

• Résidus vs ajustés: insérez un nuage de points avec X = valeurs ajustées et Y = résidus, puis ajoutez une ligne horizontale à 0. Attendu: nuage aléatoire sans motif. Un effet entonnoir suggère une hétéroscédasticité, une forme courbe suggère une relation non linéaire.
• Histogramme des résidus: via Insertion > Histogramme. Attendu: distribution à peu près symétrique et centrée sur 0. Des queues très lourdes ou des asymétries marquées indiquent des anomalies ou des variables manquantes.
• QQ-plot: triez les résidus standardisés, calculez les quantiles théoriques NORM.S.INV((rang − 0,5)/n), puis créez un nuage de points quantiles théoriques (X) vs résidus standardisés (Y). Attendu: points proches de la diagonale. Des écarts en bouts de courbe signalent des queues non normales.

Ces diagnostics ne remplacent pas l’analyse statistique complète, ils la rendent visible et actionnable pour vos destinataires.

Quelles hypothèses et diagnostics vérifier ?

Avant d’interpréter une régression, il faut contrôler quelques hypothèses clés afin d’éviter des conclusions erronées. Avec Excel, beaucoup de vérifications se font rapidement, surtout si vous extrayez et tracez les résidus via l’outil Analyse de données ou à l’aide de formules. Les points ci‑dessous vous guident, du contrôle de la linéarité à la multicolinéarité, en passant par l’homoscédasticité, la normalité et l’indépendance des erreurs.

Linéarité et spécification du modèle

La régression linéaire suppose une relation linéaire entre Y et les X. Vérifiez‑la visuellement avec un nuage de points de Y en fonction de chaque X, puis à l’aide d’un graphique résidus versus valeurs ajustées. Si vous observez des courbures, testez des transformations simples dans Excel en ajoutant des colonnes dérivées : log(Y), log(X), X², interactions X1*X2. Recalibrez ensuite le modèle avec les nouvelles colonnes via Régression dans Données > Analyse de données ou avec DroiteReg (LINEST).

Homoscédasticité et hétéroscédasticité : comment les repérer

L’homoscédasticité signifie une variance des erreurs constante pour toutes les valeurs prédites. Pour la contrôler, tracez un nuage de points résidus versus valeurs ajustées :

• Nuage en forme d’éventail ou de cône, variance qui augmente avec le niveau de Y ajusté : hétéroscédasticité probable.
• Bandes horizontales approximativement constantes autour de 0, sans structure visible : hypothèse plus crédible.
• Groupes, vagues, motifs récurrents : suspectez une variable omise, une spécification inadaptée ou une dépendance temporelle.

En pratique, essayez des transformations de Y (par exemple log) ou ajoutez des variables explicatives pertinentes, puis réestimez le modèle et recontrôlez les résidus.

Normalité des résidus : tests visuels dans Excel

Pour un contrôle pragmatique, combinez histogramme et QQ‑plot. Excel ne propose pas un QQ‑plot prêt à l’emploi, mais on peut le construire facilement.

1. Obtenir les résidus avec l’outil Régression en cochant Résidus (et Résidus normalisés si besoin), ou par formules, puis placez‑les dans une colonne dédiée.
2. QQ‑plot : triez les résidus par ordre croissant, créez en regard les quantiles théoriques N(0,1) à l’aide de NORM.S.INV (ou son équivalent français selon votre version) appliqué aux rangs (rang−0,5)/n, puis insérez un nuage de points quantiles théoriques en abscisse vs résidus triés en ordonnée. Un alignement proche d’une droite est favorable.
3. Histogramme : insérez un histogramme des résidus via Insertion > Histogramme, ou via Analyse de données > Histogramme, et vérifiez une forme à peu près symétrique et unimodale.

Des écarts modérés en queue sont courants. Si la non‑normalité est marquée, vérifiez la spécification, les points influents et les transformations.

Indépendance des erreurs : cas des séries temporelles

Sur données chronologiques, les erreurs successives peuvent être autocorrélées. Repérez‑le en traçant les résidus dans l’ordre du temps pour déceler des vagues ou cycles, puis calculez une approximation de la statistique de Durbin‑Watson avec Excel.

Durbin‑Watson (approximation pratique) : placez les résidus en colonne E. En colonne F, calculez (Et−Et−1)² à partir de la deuxième ligne. En colonne G, calculez Et². La statistique est DW = SOMME(F) / SOMME(G). Une valeur proche de 2 suggère une absence d’autocorrélation, bien en dessous de 2 indique souvent une autocorrélation positive, bien au‑dessus peut signaler une autocorrélation négative. Interprétez avec prudence et contexte.

Multicolinéarité : signes d’alerte et VIF (approche Excel)

1. Signes d’alerte : coefficients instables selon les sous‑échantillons, p‑values élevées alors que le R² global est bon, corrélations fortes entre prédicteurs.
2. Matrice de corrélation : avec COEFFICIENT.CORRELATION, explorez les couples de X pour repérer des valeurs proches de ±1.
3. Calculer le VIF dans Excel : pour chaque X_i, lancez une régression auxiliaire où X_i est la variable dépendante et les autres X sont explicatives (Données > Analyse de données > Régression). Relevez le R² de cette auxiliaire et calculez VIFi = 1 / (1 − R²).
4. Interprétation : VIF proche de 1, pas de problème majeur ; autour de 5, vigilance ; au‑delà de 10, multicolinéarité forte à traiter (regrouper des variables, centrer, supprimer des prédicteurs redondants).

Obtenir et tracer les résidus (outil Régression ou formules)

1. Via l’outil Régression : dans Données > Analyse de données > Régression, renseignez Y et X, cochez Résidus et, si utile, Résidus normalisés. Excel crée des colonnes dédiées avec valeurs ajustées et résidus.
2. Tracer les diagnostics : insérez un nuage de points résidus vs valeurs ajustées, ajoutez une ligne horizontale à 0, et un second nuage pour résidus vs X les plus importants. Cela aide à voir motifs, hétéroscédasticité et non‑linéarités.
3. Par formules : récupérez la pente et l’interception avec DroiteReg (LINEST). Calculez la prédiction Ŷ = a*X + b dans une nouvelle colonne, puis le résidu e = Y − Ŷ. Tracez ensuite les mêmes graphiques de diagnostic. Pour la régression multiple, étendez Ŷ avec toutes les colonnes X retenues.

Une fois ces vérifications faites, interprétez les coefficients, la qualité d’ajustement et les métriques d’Excel avec plus de confiance. Vous pouvez aussi afficher l’équation et le coefficient de détermination sur une courbe de tendance pour une lecture rapide.

Un exemple pas à pas : du jeu de données aux prédictions

Suivez ce mini‑cas concret pour passer d’un petit jeu de données à vos premières prévisions dans Excel. Objectif : modéliser l’impact de la température sur les ventes quotidiennes d’un stand de glaces, interpréter la régression, tracer les graphiques et produire des prédictions.

1. Préparez les données
   Créez une feuille avec deux colonnes : A = Température (°C), B = Ventes (unités). Saisissez par exemple :
   

   Température	Ventes
   15	120
   18	150
   21	170
   24	220
   27	260
   30	310
   33	350
   35	380

   Vérifiez qu’il n’y a ni lignes vides ni valeurs aberrantes évidentes.
2. Estimez la droite de régression avec DroiteReg
   Sélectionnez deux cellules vides côte à côte pour recevoir la pente puis l’ordonnée à l’origine, et entrez : =DROITEREG(B2:B9, A2:A9). Appuyez sur Entrée. Excel renvoie le coefficient de pente (variation attendue des ventes quand la température augmente d’1 °C) et l’intercept, comme rappelé plus haut dans l’article : Y = aX + b.
3. Interprétez les coefficients
   – Signe de la pente : positif si les ventes montent quand la température augmente.
   – Ordonnée à l’origine : estimation des ventes lorsque X = 0 °C, utile surtout pour l’équation.
   Pour des informations plus complètes (valeurs de t, de p, intervalles, etc.), utilisez l’outil Analyse de données d’Excel : Données > Analyse de données > Régression, puis indiquez Y = B2:B9 et X = A2:A9. Vous obtiendrez aussi les statistiques d’ajustement et la valeur de R².
4. Visualisez la relation
   Créez un nuage de points pour voir la corrélation entre température et ventes :
   – Sélectionnez A2:B9, puis Insertion > Graphique > Nuage de points.
   – Clic droit sur la série > Ajouter une courbe de tendance > choisir Linéaire, cocher Afficher l’équation et Afficher R² pour évaluer l’ajustement.
5. Contrôlez les résidus
   Dans la sortie de l’outil Régression, ajoutez l’option Résidus. Des résidus dispersés sans structure particulière soutiennent l’hypothèse de linéarité. Des motifs marqués peuvent indiquer un modèle mal spécifié ou la nécessité d’ajouter une variable explicative.
6. Calculez une prédiction
   Deux options simples :
   – Avec l’équation : si la pente est en D1 et l’intercept en E1, la prévision à 32 °C vaut =D1*32+E1.
   – Avec TENDANCE : =TENDANCE(B2:B9, A2:A9, 32) renvoie directement la valeur prédite des ventes à 32 °C.
7. Aller plus loin, même jeu de données
   Ajoutez par exemple une colonne C = Prix et estimez une régression multiple avec =DROITEREG(B2:B9, A2:C9) ou via Données > Analyse de données > Régression pour comparer les effets respectifs de la température et du prix.

Vous disposez maintenant d’un fil directeur, du tableau source au graphique, puis à l’équation et à la prévision. Répétez ces étapes sur vos propres données pour produire rapidement des estimations fiables et communicables.

Quelles limites et pièges éviter ?

La régression linéaire est puissante pour expliquer et prévoir, mais son interprétation exige de la prudence. Avant d’automatiser vos prédictions dans Excel, gardez en tête ces points clés pour éviter des conclusions trompeuses.

• Corrélation ≠ causalité : une relation statistique ne prouve pas qu’une variable cause l’autre. Cherchez des variables omises, un sens temporel crédible et, si possible, des validations externes. Dans Excel, commencez par un nuage de points et complétez avec l’outil Analyse de données pour examiner les coefficients et leurs valeurs de p, sans confondre association et causalité.
• Effet des valeurs aberrantes (outliers) : quelques points extrêmes peuvent faire basculer la pente et gonfler ou réduire artificiellement le R². Dans Excel, inspectez visuellement le nuage de points avec la courbe de tendance, affichez l’équation et le R², puis examinez les résidus via l’outil Régression. Documentez tout traitement des outliers, et testez l’impact de leur exclusion sur les résultats.
• Extrapolation hasardeuse : prédire pour des valeurs de X en dehors de la plage observée conduit souvent à des erreurs importantes, surtout si la relation n’est linéaire que localement. Limitez vos prévisions à l’intervalle de données disponible et interprétez avec prudence les projections lointaines.
• Petits échantillons : avec trop peu d’observations, les estimations deviennent instables et les tests peu fiables. Assurez-vous d’avoir un volume de données suffisant au regard du nombre de variables explicatives. Dans Excel, surveillez les larges erreurs standards et les intervalles de confiance très amples fournis par l’outil Régression.
• Surapprentissage (overfitting) : ajouter de nombreuses variables peut faire monter le R² sans améliorer les prédictions réelles. Suivez le R² ajusté, contrôlez la pertinence des variables (valeurs de p), et validez hors échantillon lorsque c’est possible, par exemple en réservant une période temporelle pour tester vos prévisions dans Excel.

En pratique, combinez vérifications graphiques, lecture critique des sorties de l’outil Régression, et validations simples pour fiabiliser vos analyses.