- Présentation de la fonction DROITEREG
- Fonction DROITEREG Syntaxe et entrées
- Qu'est-ce que la LIGNE?
- Comment utiliser LINEST
- Prévision avec DROITEREG (Régression simple)
- Prévision avec LINEST (Régression Linéaire Multiple)
- Conseils LINEST
- Intéressé par plus de prévisions?
- LIGNE fonction dans Google Sheets
- Exemples LINEST en VBA
Téléchargez l'exemple de classeur
Ce tutoriel montre comment utiliser le Excel Fonction DROITEREG dans Excel pour calculer des statistiques sur une ligne de tendance.
Présentation de la fonction DROITEREG
La fonction DROITEREG Calcule des statistiques sur une ligne de tendance ajustée à des points de données connus à l'aide de la méthode des moindres carrés.
Pour utiliser la fonction de feuille de calcul Excel DROITEREG, sélectionnez une cellule et tapez :
(Remarquez comment les entrées de formule apparaissent)
Fonction DROITEREG Syntaxe et entrées
| 1 | =LINEST(known_ys,known_xs,const,stats) |
connu_y - Un tableau de valeurs Y connues.
x_connus - Un tableau de valeurs X connues.
const - OPTIONNEL. Valeur logique indiquant s'il faut calculer B (l'interception dans y = mx + b) en utilisant la méthode des moindres carrés (VRAI ou omis) ou définir manuellement B = 0 (FAUX).
Statistiques - OPTIONNEL. Renvoie des statistiques supplémentaires (VRAI) ou ne renvoie que m (pente) et b (interception) (FAUX ou Omis)
Qu'est-ce que la LIGNE?
La fonction DROITEREG dans Excel est une fonction utilisée pour générer des statistiques de régression pour un modèle de régression linéaire. DROITEREG est une formule matricielle et peut être utilisée seule ou avec d'autres fonctions pour calculer des statistiques spécifiques sur le modèle.
La régression linéaire est une méthode statistique utilisée pour prédire les données suivant une ligne droite à l'aide de données connues. La régression est utilisée pour prédire des valeurs telles que la croissance des ventes, les besoins en stocks ou les tendances simples du marché.
LINEST est comme PREVISION en ce sens qu'il obtient un résultat similaire, mais avec beaucoup plus d'informations sur votre modèle de régression ainsi que la possibilité d'ajuster plus d'une variable indépendante.
Supposons que j'ai une table de données avec X et oui valeurs où X est la variable indépendante et oui est la variable dépendante :
Je veux savoir quelle est l'équation de régression des données ci-dessus. Utilisation de la LIGNE :
| 1 | = DROITEREG(B3:B7,C3:C7,VRAI,FAUX) |
La valeur d'ordonnée à l'origine ici est équivalente à 0, en notation scientifique.
L'équation de la droite est y= 2x + 0 . Notez que DROITEREG renvoie les deux la pente et l'interception de la ligne. Pour renvoyer les deux valeurs, la formule doit être saisie sous forme de formule matricielle. Plus d'informations sur les formules matricielles plus tard.
Comment utiliser LINEST
La fonction DROITEREG prend quatre arguments :
| 1 | =LINEST(known_y's,known_x's, const, stats) |
Où,
| Argument | La description |
| connu_y et x_connus | Est le X et oui données dans votre table de données |
| const | Option TRUE/FALSE pour savoir si l'ordonnée à l'origine doit être forcée à 0 ou calculée normalement |
| Statistiques | Option TRUE/FALSE indiquant si des statistiques de régression supplémentaires doivent être renvoyées |
En utilisant notre premier exemple, la fonction s'écrit :
| 1 | = DROITEREG(B3:B7,C3:C7,VRAI,FAUX) |
Quand le Statistiques est définie sur TRUE, l'organisation des statistiques de régression est la suivante :
Vous vous demandez peut-être ce que signifie chaque variable.
| Statistique | La description |
| mm | Coefficients de pente pour x variables |
| b | y-interception |
| sem | Erreur standard pour chaque coefficient de pente |
| seb | Erreur standard pour l'ordonnée à l'origine |
| r2 | Coefficient de détermination |
| seoui | Erreur standard pour le oui estimation |
| F | La statistique F (pour déterminer si la relation des variables se produit par hasard) |
| réF | Degrés de liberté |
| ssreg | Somme de régression des carrés |
| ssrésider | Somme résiduelle des carrés |
Les principales statistiques à comprendre sont les coefficients de pente, l'ordonnée à l'origine et le coefficient de détermination ou le r2 valeur du modèle.
En utilisant l'exemple ci-dessus et en sélectionnant VRAI pour le Statistiques paramètre:
Les cellules en surbrillance montrent la pente = 2, l'interception = 0 et r2 = 1.
Le r2 valeur est un indicateur de la force de la corrélation du modèle. Il peut être considéré comme un indicateur d'ajustement. Un faible r2 valeur signifierait une mauvaise corrélation entre vos variables dépendantes et indépendantes, et l'inverse est vrai pour un r élevé2 valeurs, avec r2 = 1 étant un ajustement parfait.
Dans les versions postérieures à janvier 2022 d'Excel dans Microsoft 365 (anciennement Office 365), les tableaux dynamiques ont modifié la façon dont les formules matricielles sont évaluées. Il n'est plus nécessaire d'utiliser CTRL + MAJ + ENTRÉE ou de mettre en surbrillance la zone de cellules que le tableau occupera. Entrez simplement la formule et cliquez sur Entrée et les cellules résultantes se «déverseront» dans le tableau.
Pour le reste de cet article, nous ferons référence à l'utilisation de DROITEREG en ce qui concerne les tableaux dynamiques dans Microsoft 365 Excel.
Prévision avec DROITEREG (Régression simple)
La combinaison des fonctions DROITEREG et SOMME peut être utilisée pour prédire la valeur d'une variable dépendante oui, étant donné que l'on sait X et oui Les données. Vous trouverez ci-dessous un exemple montrant ce que oui la valeur sera lorsque x=14.
| 1 | =SOMME(LINEST(C3:C7,B3:B7)*{14,1}) |
Le modèle se présente sous la forme y =mx + b . C'est la même chose que y = a+ bx, juste une manière différente de représenter l'équation. Un conseil à garder à l'esprit pour les équations linéaires est la variable à côté de X est toujours la pente, et la variable suivant un signe plus ou moins est toujours l'intersection, quelles que soient les lettres utilisées dans l'équation.
En utilisant la formule : =SUM(LINEST(C3:C7,B3:B7)*{14,1}) renvoie le résultat de 28. Comme il s'agit d'un résultat unique, il n'est pas nécessaire de le saisir sous forme de tableau.
La fin de la formule ci-dessus *{14,1} spécifie la variable indépendante à utiliser pour prédire la variable dépendante, dans ce cas, 14.
Nous pouvons le vérifier en entrant x= 14 dans l'équation de la droite, y =2x + 0.
Prévision avec LINEST (Régression Linéaire Multiple)
Le tableau de données suivant provient de la page LINEEST du site Web de support Microsoft.
Dans certains cas, plusieurs variables indépendantes doivent être prises en compte lors de la création d'un modèle de régression linéaire. C'est ce qu'on appelle la régression linéaire multiple (c'est-à-dire plusieurs variables indépendantes). Si je veux estimer le coût d'un immeuble de bureaux, des éléments comme l'espace au sol, le nombre d'entrées du bâtiment, l'âge du bâtiment et le nombre de bureaux feraient tous partie de l'équation. Voyons un exemple.
En tapant la formule DROITEREG dans la cellule G29 et en l'exécutant, nous obtenons :
| 1 | =LINEST(E3:E13,A3:D13,VRAI,VRAI) |
Le modèle se présente sous la forme :
N'oubliez pas que le tableau de résultats DROITEREG est dans l'ordre inverse de l'équation. Dans l'exemple ci-dessus, 52 317,8 est notre interception, b, et 27,6 est notre m1 ou la valeur de pente pour la variable Floor space, X1.
En utilisant la fonction DROITEREG avec les données fournies, notre modèle de régression est :
Avec un r2 valeur de 0,997, ce qui indique un modèle fort ou fortement corrélé. En utilisant le modèle, vous pouvez maintenant prédire quelle sera la valeur estimée d'un immeuble de bureaux en fonction de toute combinaison des variables indépendantes ci-dessus.
Conseils LINEST
- Assurez-vous que vous disposez de la version la plus récente de Microsoft 365 pour utiliser DROITEREG avec des tableaux dynamiques. Vous devrez peut-être activer Office Insider Current Channel (Aperçu) pour utiliser les fonctions de tableau dynamique. Sur la page du compte :
- Si vous utilisez une version non Microsoft 365, vous devrez utiliser la méthode héritée CTRL + MAJ + ENTRÉE (CSE) pour évaluer les formules matricielles.
- Si vous utilisez la méthode héritée, le nombre de colonnes à mettre en évidence lors de la saisie d'une fonction de tableau DROITEREG est toujours le nombre de X variables dans vos données plus 1. Le nombre de lignes à sélectionner pour le tableau est 5.
- Si vous souhaitez partager votre version d'Excel avec tableau dynamique activé avec quelqu'un qui utilise une version non-Microsoft 365, utilisez la méthode CSE héritée pour éviter les problèmes de compatibilité.
Intéressé par plus de prévisions?
Consultez nos autres articles sur les prévisions avec les fonctions de lissage exponentiel, TENDANCE, CROISSANCE et LOGEST.
LIGNE fonction dans Google Sheets
La fonction DROITEREG fonctionne exactement de la même manière dans Google Sheets que dans Excel.
Exemples LINEST en VBA
Vous pouvez également utiliser la fonction DROITEREG en VBA. Taper:application.worksheetfunction.linest(known_ys,known_xs, const, stats)
Exécuter l'instruction VBA suivante
| 1 | Range("D2") = Application.WorksheetFunction.LinEst(Range("A2:A8"), Range("B2:B8")) |
produira les résultats suivants
Pour les arguments de la fonction (known_y's, etc.), vous pouvez soit les entrer directement dans la fonction, soit définir des variables à utiliser à la place.
Revenir à la liste de toutes les fonctions dans Excel
