LSM pour une fonction de deux variables. Rapprochement des données expérimentales. Méthode des moindres carrés. Implémentation pratique des moindres carrés pour la dépendance linéaire sur une calculatrice non programmable

Exemple.

Données expérimentales sur les valeurs des variables X Et à sont données dans le tableau.

Grâce à leur alignement, la fonction est obtenue

En utilisant méthode des moindres carrés, approximons ces données par une dépendance linéaire y=hache+b(trouver les paramètres UN Et b). Découvrez laquelle des deux droites (au sens de la méthode des moindres carrés) aligne le mieux les données expérimentales. Faites un dessin.

L'essence de la méthode des moindres carrés (LSM).

La tâche consiste à trouver les coefficients de dépendance linéaire auxquels la fonction de deux variables UN Et b prend la plus petite valeur. C'est-à-dire étant donné UN Et b la somme des carrés des écarts des données expérimentales par rapport à la droite trouvée sera la plus petite. C’est tout l’intérêt de la méthode des moindres carrés.

Ainsi, résoudre l’exemple revient à trouver l’extremum d’une fonction de deux variables.

Dériver des formules pour trouver des coefficients.

Un système de deux équations à deux inconnues est compilé et résolu. Trouver les dérivées partielles d'une fonction par rapport aux variables UN Et b, nous assimilons ces dérivées à zéro.

Nous résolvons le système d'équations résultant en utilisant n'importe quelle méthode (par exemple par méthode de substitution ou ) et obtenez des formules pour trouver des coefficients en utilisant la méthode des moindres carrés (LSM).

Donné UN Et b fonction prend la plus petite valeur. La preuve de ce fait est donnée.

C'est toute la méthode des moindres carrés. Formule pour trouver le paramètre un contient les sommes , , et le paramètre n- quantité de données expérimentales. Nous recommandons de calculer séparément les valeurs de ces montants. Coefficient b trouvé après calcul un.

Il est temps de se souvenir de l'exemple original.

Solution.

Dans notre exemple n=5. Nous remplissons le tableau pour faciliter le calcul des montants inclus dans les formules des coefficients requis.

Les valeurs de la quatrième ligne du tableau sont obtenues en multipliant les valeurs de la 2ème ligne par les valeurs de la 3ème ligne pour chaque nombre je.

Les valeurs de la cinquième ligne du tableau sont obtenues en mettant au carré les valeurs de la 2ème ligne pour chaque nombre je.

Les valeurs de la dernière colonne du tableau sont les sommes des valeurs des lignes.

On utilise les formules de la méthode des moindres carrés pour trouver les coefficients UN Et b. Nous y substituons les valeurs correspondantes de la dernière colonne du tableau :

Ainsi, y = 0,165x+2,184- la droite de rapprochement souhaitée.

Reste à savoir laquelle des lignes y = 0,165x+2,184 ou se rapproche mieux des données originales, c'est-à-dire effectue une estimation en utilisant la méthode des moindres carrés.

Estimation des erreurs de la méthode des moindres carrés.

Pour ce faire, vous devez calculer la somme des écarts carrés des données originales par rapport à ces lignes Et , une valeur plus petite correspond à une droite qui se rapproche mieux des données originales au sens de la méthode des moindres carrés.

Depuis, puis directement y = 0,165x+2,184 se rapproche mieux des données originales.

Illustration graphique de la méthode des moindres carrés (LS).

Tout est clairement visible sur les graphiques. La ligne rouge est la ligne droite trouvée y = 0,165x+2,184, la ligne bleue est , les points roses sont les données originales.

Pourquoi est-ce nécessaire, pourquoi toutes ces approximations ?

Je l'utilise personnellement pour résoudre des problèmes de lissage de données, d'interpolation et d'extrapolation (dans l'exemple original, on pourrait leur demander de trouver la valeur d'une valeur observée). ouià x=3 ou lorsque x=6 en utilisant la méthode des moindres carrés). Mais nous en reparlerons plus tard dans une autre section du site.

Preuve.

Pour que lorsqu'on le trouve UN Et b fonction prend la plus petite valeur, il faut qu'à ce stade la matrice de la forme quadratique de la différentielle du second ordre pour la fonction était positif et définitif. Montrons-le.

Ses applications sont nombreuses, car elle permet une représentation approximative d’une fonction donnée par d’autres plus simples. Le LSM peut être extrêmement utile dans le traitement des observations, et il est activement utilisé pour estimer certaines quantités sur la base des résultats de mesures d'autres contenant des erreurs aléatoires. Dans cet article, vous apprendrez comment implémenter les calculs des moindres carrés dans Excel.

Énoncé du problème à l'aide d'un exemple précis

Supposons qu'il existe deux indicateurs X et Y. De plus, Y dépend de X. Puisque les MCO nous intéressent du point de vue de l'analyse de régression (dans Excel, ses méthodes sont implémentées à l'aide de fonctions intégrées), nous devons immédiatement considérer un problème spécifique.

Soit donc X la surface de vente d'une épicerie, mesurée en mètres carrés, et Y le chiffre d'affaires annuel, mesuré en millions de roubles.

Il est nécessaire de faire une prévision du chiffre d'affaires (Y) que réalisera le magasin s'il dispose de tel ou tel espace de vente. Évidemment, la fonction Y = f (X) est croissante, puisque l'hypermarché vend plus de marchandises que l'étal.

Quelques mots sur l'exactitude des données initiales utilisées pour la prédiction

Disons que nous avons une table construite à partir des données de n magasins.

Selon les statistiques mathématiques, les résultats seront plus ou moins corrects si les données sur au moins 5 à 6 objets sont examinées. De plus, les résultats « anormaux » ne peuvent pas être utilisés. En particulier, une petite boutique d'élite peut avoir un chiffre d'affaires plusieurs fois supérieur à celui des grands points de vente de la classe « masmarket ».

L'essence de la méthode

Les données du tableau peuvent être représentées sur un plan cartésien sous la forme de points M 1 (x 1, y 1), ... M n (x n, y n). Maintenant la solution du problème se réduira à la sélection d'une fonction d'approximation y = f (x), qui a un graphe passant le plus près possible des points M 1, M 2, .. M n.

Bien sûr, vous pouvez utiliser un polynôme de haut degré, mais cette option est non seulement difficile à mettre en œuvre, mais aussi tout simplement incorrecte, car elle ne reflétera pas la tendance principale à détecter. La solution la plus raisonnable est de rechercher la droite y = ax + b, qui se rapproche le mieux des données expérimentales, ou plus précisément des coefficients a et b.

Évaluation de la précision

Quelle que soit l'approximation, l'évaluation de son exactitude revêt une importance particulière. Notons e i la différence (écart) entre les valeurs fonctionnelles et expérimentales pour le point x i, c'est-à-dire e i = y i - f (x i).

Évidemment, pour évaluer la précision de l'approximation, vous pouvez utiliser la somme des écarts, c'est-à-dire que lors du choix d'une ligne droite pour une représentation approximative de la dépendance de X à Y, vous devez privilégier celle avec la plus petite valeur de somme e i à tous les points considérés. Cependant, tout n'est pas si simple, car à côté des écarts positifs, il y aura aussi des écarts négatifs.

Le problème peut être résolu en utilisant des modules de déviation ou leurs carrés. Cette dernière méthode est la plus utilisée. Il est utilisé dans de nombreux domaines, notamment l'analyse de régression (implémentée dans Excel à l'aide de deux fonctions intégrées), et a depuis longtemps prouvé son efficacité.

Méthode des moindres carrés

Excel, comme vous le savez, possède une fonction AutoSum intégrée qui vous permet de calculer les valeurs de toutes les valeurs situées dans la plage sélectionnée. Ainsi, rien ne nous empêchera de calculer la valeur de l'expression (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

En notation mathématique, cela ressemble à :

Puisque la décision a été initialement prise d’approximer par une ligne droite, nous avons :

Ainsi, la tâche de trouver la droite qui décrit le mieux la dépendance spécifique des quantités X et Y revient à calculer le minimum d'une fonction de deux variables :

Pour ce faire, vous devez assimiler les dérivées partielles par rapport aux nouvelles variables a et b à zéro, et résoudre un système primitif composé de deux équations à 2 inconnues de la forme :

Après quelques transformations simples, dont la division par 2 et la manipulation des sommes, on obtient :

En le résolvant, par exemple, à l'aide de la méthode de Cramer, on obtient un point stationnaire avec certains coefficients a* et b*. C'est le minimum, c'est-à-dire pour prédire le chiffre d'affaires qu'un magasin réalisera pour une certaine zone, la droite y = a * x + b * convient, qui est un modèle de régression pour l'exemple en question. Bien sûr, cela ne vous permettra pas de trouver le résultat exact, mais cela vous aidera à avoir une idée de savoir si l'achat d'une zone spécifique à crédit en magasin sera rentable.

Comment implémenter les moindres carrés dans Excel

Excel dispose d'une fonction permettant de calculer des valeurs à l'aide des moindres carrés. Il a la forme suivante : « TENDANCE » (valeurs Y connues ; valeurs X connues ; nouvelles valeurs X ; constante). Appliquons la formule de calcul des OLS dans Excel à notre tableau.

Pour cela, saisissez le signe « = » dans la cellule dans laquelle doit être affiché le résultat du calcul par la méthode des moindres carrés dans Excel et sélectionnez la fonction « TENDANCE ». Dans la fenêtre qui s'ouvre, remplissez les champs appropriés en mettant en surbrillance :

plage de valeurs connues pour Y (dans ce cas, les données sur le chiffre d'affaires commercial) ;
plage x 1 , …x n , c'est-à-dire la taille de l'espace de vente au détail ;
valeurs à la fois connues et inconnues de x, pour lesquelles vous devez connaître la taille du chiffre d'affaires (pour plus d'informations sur leur emplacement sur la feuille de calcul, voir ci-dessous).

De plus, la formule contient la variable logique « Const ». Si vous saisissez 1 dans le champ correspondant, cela signifie que vous devrez effectuer les calculs en supposant que b = 0.

Si vous avez besoin de connaître la prévision pour plus d'une valeur x, après avoir entré la formule, vous ne devez pas appuyer sur "Entrée", mais vous devez taper la combinaison "Shift" + "Contrôle" + "Entrée" sur le clavier.

Certaines fonctionnalités

L'analyse de régression peut être accessible même aux nuls. La formule Excel permettant de prédire la valeur d'un tableau de variables inconnues (TREND) peut être utilisée même par ceux qui n'ont jamais entendu parler des moindres carrés. Il suffit de connaître quelques-unes des caractéristiques de son travail. En particulier:

Si vous organisez la plage de valeurs connues de la variable y dans une ligne ou une colonne, alors chaque ligne (colonne) avec des valeurs connues de x sera perçue par le programme comme une variable distincte.
Si une plage avec x connu n'est pas spécifiée dans la fenêtre TENDANCE, alors lors de l'utilisation de la fonction dans Excel, le programme la traitera comme un tableau composé d'entiers dont le nombre correspond à la plage avec les valeurs données du variable y.
Pour générer un tableau de valeurs « prédites », l’expression permettant de calculer la tendance doit être saisie sous forme de formule matricielle.
Si de nouvelles valeurs de x ne sont pas spécifiées, alors la fonction TREND les considère égales aux valeurs connues. S'ils ne sont pas spécifiés, alors le tableau 1 est pris comme argument ; 2 ; 3 ; 4;…, ce qui est proportionnel à la plage avec les paramètres y déjà spécifiés.
La plage contenant les nouvelles valeurs x doit avoir la même ou plusieurs lignes ou colonnes que la plage contenant les valeurs y données. Autrement dit, il doit être proportionnel aux variables indépendantes.
Un tableau avec des valeurs x connues peut contenir plusieurs variables. Cependant, si nous ne parlons que d'un seul, il est alors nécessaire que les plages avec les valeurs données de x et y soient proportionnelles. Dans le cas de plusieurs variables, il est nécessaire que la plage avec les valeurs y données tienne dans une colonne ou une ligne.

Fonction PRÉDICTION

Implémenté à l'aide de plusieurs fonctions. L’un d’eux s’appelle « PRÉDICTION ». Il est similaire à « TENDANCE », c’est à dire qu’il donne le résultat de calculs utilisant la méthode des moindres carrés. Cependant, seulement pour un X, pour lequel la valeur de Y est inconnue.

Vous connaissez désormais les formules dans Excel pour les nuls qui vous permettent de prédire la valeur future d'un indicateur particulier selon une tendance linéaire.

La méthode des moindres carrés est l’une des plus courantes et des plus développées en raison de sa simplicité et efficacité des méthodes d'estimation des paramètres de linéaire. Dans le même temps, lors de son utilisation, une certaine prudence doit être observée, car les modèles construits à l'aide de celui-ci peuvent ne pas satisfaire un certain nombre d'exigences concernant la qualité de leurs paramètres et, par conséquent, ne reflètent pas « bien » les modèles de développement des processus. assez.

Examinons plus en détail la procédure d'estimation des paramètres d'un modèle économétrique linéaire par la méthode des moindres carrés. Un tel modèle peut en général être représenté par l’équation (1.2) :

y t = une 0 + une 1 x 1 t +...+ une n x nt + ε t.

Les données initiales lors de l'estimation des paramètres a 0 , a 1 ,..., a n sont un vecteur de valeurs de la variable dépendante oui= (y 1 , y 2 , ... , y T)" et la matrice des valeurs des variables indépendantes

dans laquelle la première colonne, composée de uns, correspond au coefficient du modèle.

La méthode des moindres carrés tire son nom du principe de base selon lequel les estimations des paramètres obtenues sur cette base doivent satisfaire : la somme des carrés de l'erreur du modèle doit être minime.

Exemples de résolution de problèmes par la méthode des moindres carrés

Exemple 2.1. L'entreprise commerciale dispose d'un réseau de 12 magasins dont les informations sur les activités sont présentées dans le tableau. 2.1.

La direction de l'entreprise aimerait savoir comment le montant annuel dépend de la surface de vente du magasin.

Tableau 2.1

Numéro de magasin	Chiffre d'affaires annuel, millions de roubles.	Surface commerciale, milliers de m2

Solution des moindres carrés. Notons le chiffre d'affaires annuel du ème magasin, en millions de roubles ; — surface commerciale du ème magasin, mille m2.

Figure 2.1. Nuage de points pour l'exemple 2.1

Pour déterminer la forme de la relation fonctionnelle entre les variables et nous construirons un diagramme de dispersion (Fig. 2.1).

Sur la base du diagramme de dispersion, nous pouvons conclure que le chiffre d'affaires annuel dépend positivement de l'espace de vente au détail (c'est-à-dire que y augmentera avec l'augmentation de ). La forme de connexion fonctionnelle la plus appropriée est linéaire.

Les informations pour d'autres calculs sont présentées dans le tableau. 2.2. En utilisant la méthode des moindres carrés, nous estimons les paramètres d'un modèle économétrique linéaire à un facteur

Tableau 2.2

Ainsi,

Par conséquent, avec une augmentation de la surface commerciale de 1 000 m2, toutes choses égales par ailleurs, le chiffre d'affaires annuel moyen augmente de 67,8871 millions de roubles.

Exemple 2.2. La direction de l'entreprise a remarqué que le chiffre d'affaires annuel dépend non seulement de la surface de vente du magasin (voir exemple 2.1), mais aussi du nombre moyen de visiteurs. Les informations pertinentes sont présentées dans le tableau. 2.3.

Tableau 2.3

Solution. Notons le nombre moyen de visiteurs du ème magasin par jour, en milliers de personnes.

Pour déterminer la forme de la relation fonctionnelle entre les variables et nous construirons un diagramme de dispersion (Fig. 2.2).

Sur la base du nuage de points, nous pouvons conclure que le chiffre d'affaires annuel dépend positivement du nombre moyen de visiteurs par jour (c'est-à-dire que y augmentera avec l'augmentation de ). La forme de dépendance fonctionnelle est linéaire.

Riz. 2.2. Nuage de points pour l'exemple 2.2

Tableau 2.4

De manière générale, il est nécessaire de déterminer les paramètres d'un modèle économétrique à deux facteurs

y t = une 0 + une 1 x 1 t + une 2 x 2 t + ε t

Les informations requises pour d'autres calculs sont présentées dans le tableau. 2.4.

Estimons les paramètres d'un modèle économétrique linéaire à deux facteurs par la méthode des moindres carrés.

Ainsi,

L'estimation du coefficient =61,6583 montre que, toutes choses égales par ailleurs, avec une augmentation de la surface commerciale de 1 000 m 2, le chiffre d'affaires annuel augmentera en moyenne de 61,6583 millions de roubles.

Méthode des moindres carrés

Méthode des moindres carrés ( MCO, MCO, moindres carrés ordinaires) - l'une des méthodes de base d'analyse de régression pour estimer les paramètres inconnus des modèles de régression à l'aide d'échantillons de données. La méthode est basée sur la minimisation de la somme des carrés des résidus de régression.

Il convient de noter que la méthode des moindres carrés elle-même peut être appelée une méthode permettant de résoudre un problème dans n'importe quel domaine si la solution réside ou satisfait à un critère de minimisation de la somme des carrés de certaines fonctions des variables requises. Par conséquent, la méthode des moindres carrés peut également être utilisée pour une représentation approximative (approximation) d'une fonction donnée par d'autres fonctions (plus simples), lors de la recherche d'un ensemble de quantités qui satisfont des équations ou des contraintes, dont le nombre dépasse le nombre de ces quantités. , etc.

L’essence de la multinationale

Soit un modèle (paramétrique) d'une relation probabiliste (de régression) entre la variable (expliquée) oui et de nombreux facteurs (variables explicatives) X

où est le vecteur des paramètres de modèle inconnus

- erreur de modèle aléatoire.

Qu'il y ait également des exemples d'observations des valeurs de ces variables. Soit le numéro d'observation (). Viennent ensuite les valeurs des variables de la ème observation. Ensuite, pour des valeurs données des paramètres b, il est possible de calculer les valeurs théoriques (modèles) de la variable expliquée y :

La taille des résidus dépend des valeurs des paramètres b.

L'essence de la méthode des moindres carrés (ordinaire, classique) est de trouver des paramètres b pour lesquels la somme des carrés des résidus (eng. Somme résiduelle des carrés) sera minime :

Dans le cas général, ce problème peut être résolu par des méthodes d'optimisation (minimisation) numérique. Dans ce cas, ils parlent de moindres carrés non linéaires(NLS ou NLLS - anglais) Moindres carrés non linéaires). Dans de nombreux cas, il est possible d'obtenir une solution analytique. Pour résoudre le problème de minimisation, il faut trouver les points stationnaires de la fonction en la différenciant par rapport aux paramètres inconnus b, en assimilant les dérivées à zéro et en résolvant le système d'équations résultant :

Si les erreurs aléatoires du modèle sont normalement distribuées, ont la même variance et ne sont pas corrélées, les estimations des paramètres OLS sont identiques aux estimations du maximum de vraisemblance (MLM).

OLS dans le cas d'un modèle linéaire

Soit la dépendance de régression linéaire :

Laisser oui est un vecteur colonne d'observations de la variable expliquée, et est une matrice d'observations factorielles (les lignes de la matrice sont les vecteurs de valeurs de facteurs dans une observation donnée, les colonnes sont le vecteur de valeurs d'un facteur donné dans toutes les observations). La représentation matricielle du modèle linéaire est :

Alors le vecteur des estimations de la variable expliquée et le vecteur des résidus de régression seront égaux

En conséquence, la somme des carrés des résidus de régression sera égale à

En différenciant cette fonction par rapport au vecteur de paramètres et en assimilant les dérivées à zéro, on obtient un système d'équations (sous forme matricielle) :

La solution de ce système d'équations donne la formule générale des estimations des moindres carrés pour un modèle linéaire :

À des fins analytiques, cette dernière représentation de cette formule est utile. Si dans un modèle de régression les données centré, alors dans cette représentation la première matrice a la signification d'un échantillon de matrice de covariance de facteurs, et la seconde est un vecteur de covariances de facteurs avec la variable dépendante. Si en plus les données sont également normaliséà MSE (c'est-à-dire, en fin de compte standardisé), alors la première matrice a la signification d'une matrice de corrélation d'échantillons de facteurs, le deuxième vecteur - un vecteur de corrélations d'échantillons de facteurs avec la variable dépendante.

Une propriété importante des estimations MCO pour les modèles avec constante- la droite de la régression construite passe par le centre de gravité des données de l'échantillon, c'est-à-dire que l'égalité est satisfaite :

En particulier, dans le cas extrême, lorsque le seul régresseur est une constante, nous constatons que l'estimation MCO du seul paramètre (la constante elle-même) est égale à la valeur moyenne de la variable expliquée. C'est-à-dire que la moyenne arithmétique, connue pour ses bonnes propriétés issues des lois des grands nombres, est également une estimation des moindres carrés - elle satisfait au critère de la somme minimale des écarts carrés par rapport à celle-ci.

Exemple : régression la plus simple (par paires)

Dans le cas de la régression linéaire appariée, les formules de calcul sont simplifiées (on peut se passer de l'algèbre matricielle) :

Propriétés des estimateurs OLS

Tout d’abord, nous notons que pour les modèles linéaires, les estimations MCO sont des estimations linéaires, comme le découle de la formule ci-dessus. Pour les estimations MCO non biaisées, il est nécessaire et suffisant de remplir la condition la plus importante de l’analyse de régression : l’espérance mathématique d’une erreur aléatoire, conditionnelle aux facteurs, doit être égale à zéro. Cette condition est notamment remplie si

l'espérance mathématique des erreurs aléatoires est nulle, et
les facteurs et les erreurs aléatoires sont des variables aléatoires indépendantes.

La deuxième condition – la condition d’exogénéité des facteurs – est fondamentale. Si cette propriété n'est pas remplie, alors nous pouvons supposer que presque toutes les estimations seront extrêmement insatisfaisantes : elles ne seront même pas cohérentes (c'est-à-dire que même une très grande quantité de données ne nous permet pas d'obtenir des estimations de haute qualité dans ce cas ). Dans le cas classique, une hypothèse plus forte est faite sur le déterminisme des facteurs, par opposition à une erreur aléatoire, ce qui signifie automatiquement que la condition d'exogénéité est remplie. Dans le cas général, pour la cohérence des estimations, il suffit de satisfaire la condition d'exogénéité ainsi que la convergence de la matrice vers une matrice non singulière à mesure que la taille de l'échantillon augmente jusqu'à l'infini.

Pour qu'en plus de la cohérence et de l'impartialité, les estimations des moindres carrés (ordinaires) soient également efficaces (les meilleures de la classe des estimations linéaires sans biais), des propriétés supplémentaires d'erreur aléatoire doivent être remplies :

Ces hypothèses peuvent être formulées pour la matrice de covariance du vecteur d'erreur aléatoire

Un modèle linéaire qui satisfait à ces conditions est appelé classique. Les estimations MCO pour la régression linéaire classique sont impartiales, cohérentes et constituent les estimations les plus efficaces de la classe de toutes les estimations linéaires non biaisées (dans la littérature anglaise, l'abréviation est parfois utilisée BLEU (Meilleur estimateur linéaire sans évaluation) - la meilleure estimation linéaire sans biais ; dans la littérature russe, le théorème de Gauss-Markov est plus souvent cité). Comme il est facile de le montrer, la matrice de covariance du vecteur d'estimations de coefficients sera égale à :

MCO généralisé

La méthode des moindres carrés permet une large généralisation. Au lieu de minimiser la somme des carrés des résidus, on peut minimiser une forme quadratique définie positive du vecteur des résidus, où est une matrice de poids défini positif symétrique. Les moindres carrés conventionnels sont un cas particulier de cette approche, où la matrice de poids est proportionnelle à la matrice d'identité. Comme le montre la théorie des matrices symétriques (ou opérateurs), pour de telles matrices, il existe une décomposition. Par conséquent, la fonctionnelle spécifiée peut être représentée comme suit, c'est-à-dire que cette fonctionnelle peut être représentée comme la somme des carrés de certains « restes » transformés. Ainsi, on peut distinguer une classe de méthodes des moindres carrés - les méthodes LS (Least Squares).

Il a été prouvé (théorème d'Aitken) que pour un modèle de régression linéaire généralisée (dans lequel aucune restriction n'est imposée sur la matrice de covariance des erreurs aléatoires), les plus efficaces (dans la classe des estimations linéaires non biaisées) sont les soi-disant estimations. Moindres carrés généralisés (GLS - Moindres carrés généralisés)- Méthode LS avec une matrice de poids égale à la matrice de covariance inverse des erreurs aléatoires : .

On peut montrer que la formule pour les estimations GLS des paramètres d'un modèle linéaire a la forme

La matrice de covariance de ces estimations sera donc égale à

En fait, l’essence de l’OLS réside dans une certaine transformation (linéaire) (P) des données originales et dans l’application de l’OLS ordinaire aux données transformées. Le but de cette transformation est que pour les données transformées, les erreurs aléatoires satisfont déjà aux hypothèses classiques.

MCO pondéré

Dans le cas d'une matrice de poids diagonale (et donc d'une matrice de covariance d'erreurs aléatoires), nous avons ce que l'on appelle les moindres carrés pondérés (WLS). Dans ce cas, la somme des carrés pondérée des résidus du modèle est minimisée, c'est-à-dire que chaque observation reçoit un « poids » inversement proportionnel à la variance de l'erreur aléatoire dans cette observation : . En fait, les données sont transformées en pondérant les observations (en divisant par un montant proportionnel à l'écart type estimé des erreurs aléatoires), et une MCO ordinaire est appliquée aux données pondérées.

Quelques cas particuliers d'utilisation de MNC en pratique

Approximation de la dépendance linéaire

Considérons le cas où, à la suite de l'étude de la dépendance d'une certaine quantité scalaire sur une certaine quantité scalaire (cela pourrait être, par exemple, la dépendance de la tension sur l'intensité du courant : , où est une valeur constante, la résistance de le conducteur), des mesures de ces grandeurs ont été effectuées, à la suite desquelles les valeurs et leurs valeurs correspondantes. Les données de mesure doivent être enregistrées dans un tableau.

Tableau. Résultats de mesure.

Numéro de mesure.
1
2
3
4
5
6

La question est : quelle valeur du coefficient peut-on choisir pour décrire au mieux la dépendance ? Selon la méthode des moindres carrés, cette valeur doit être telle que la somme des carrés des écarts des valeurs par rapport aux valeurs

était minime

La somme des écarts au carré a un extremum - un minimum, ce qui nous permet d'utiliser cette formule. Retrouvons à partir de cette formule la valeur du coefficient. Pour ce faire, on transforme son côté gauche comme suit :

La dernière formule nous permet de trouver la valeur du coefficient, ce qui était requis dans le problème.

Histoire

Jusqu'au début du 19ème siècle. les scientifiques n'avaient pas certaines règles pour résoudre un système d'équations dans lequel le nombre d'inconnues est inférieur au nombre d'équations ; Jusqu'à cette époque, on utilisait des techniques privées qui dépendaient du type d'équations et de l'esprit des calculateurs, et donc différents calculateurs, basés sur les mêmes données d'observation, arrivaient à des conclusions différentes. Gauss (1795) fut le premier à utiliser la méthode, et Legendre (1805) la découvrit et la publia indépendamment sous son nom moderne (français. Méthode des moindres carrés ) . Laplace a lié la méthode à la théorie des probabilités, et le mathématicien américain Adrain (1808) a examiné ses applications en théorie des probabilités. La méthode a été largement répandue et améliorée grâce à des recherches ultérieures menées par Encke, Bessel, Hansen et d'autres.

Utilisations alternatives de l'OLS

L'idée de la méthode des moindres carrés peut également être utilisée dans d'autres cas non directement liés à l'analyse de régression. Le fait est que la somme des carrés est l’une des mesures de proximité les plus courantes pour les vecteurs (métrique euclidienne dans les espaces de dimension finie).

Une application est la « solution » de systèmes d’équations linéaires dans lesquels le nombre d’équations est supérieur au nombre de variables.

où la matrice n'est pas carrée, mais rectangulaire de taille .

Un tel système d’équations, dans le cas général, n’a pas de solution (si le rang est effectivement supérieur au nombre de variables). Par conséquent, ce système ne peut être « résolu » que dans le sens de choisir un tel vecteur pour minimiser la « distance » entre les vecteurs et . Pour ce faire, vous pouvez appliquer le critère de minimisation de la somme des carrés des différences entre les côtés gauche et droit des équations système, c'est-à-dire. Il est facile de montrer que la résolution de ce problème de minimisation conduit à résoudre le système d’équations suivant

Exemple.

Données expérimentales sur les valeurs des variables X Et à sont données dans le tableau.

Grâce à leur alignement, la fonction est obtenue

L'essence de la méthode des moindres carrés (LSM).

Ainsi, résoudre l’exemple revient à trouver l’extremum d’une fonction de deux variables.

Dériver des formules pour trouver des coefficients.

Un système de deux équations à deux inconnues est compilé et résolu. Trouver les dérivées partielles d'une fonction par variables UN Et b, nous assimilons ces dérivées à zéro.

Nous résolvons le système d'équations résultant en utilisant n'importe quelle méthode (par exemple par méthode de substitution ou La méthode de Cramer) et obtenez des formules pour trouver des coefficients en utilisant la méthode des moindres carrés (LSM).

Donné UN Et b fonction prend la plus petite valeur. La preuve de ce fait est donnée ci-dessous dans le texte en fin de page.

C'est toute la méthode des moindres carrés. Formule pour trouver le paramètre un contient les sommes ,, et le paramètre n- quantité de données expérimentales. Nous recommandons de calculer séparément les valeurs de ces montants. Coefficient b trouvé après calcul un.

Il est temps de se souvenir de l'exemple original.

Solution.

Dans notre exemple n=5. Nous remplissons le tableau pour faciliter le calcul des montants inclus dans les formules des coefficients requis.

Les valeurs de la quatrième ligne du tableau sont obtenues en multipliant les valeurs de la 2ème ligne par les valeurs de la 3ème ligne pour chaque nombre je.

Les valeurs de la cinquième ligne du tableau sont obtenues en mettant au carré les valeurs de la 2ème ligne pour chaque nombre je.

Les valeurs de la dernière colonne du tableau sont les sommes des valeurs des lignes.

On utilise les formules de la méthode des moindres carrés pour trouver les coefficients UN Et b. Nous y substituons les valeurs correspondantes de la dernière colonne du tableau :

Ainsi, y = 0,165x+2,184- la droite de rapprochement souhaitée.

Reste à savoir laquelle des lignes y = 0,165x+2,184 ou se rapproche mieux des données originales, c'est-à-dire effectue une estimation en utilisant la méthode des moindres carrés.

Estimation des erreurs de la méthode des moindres carrés.

Depuis, puis directement y = 0,165x+2,184 se rapproche mieux des données originales.

Illustration graphique de la méthode des moindres carrés (LS).

Tout est clairement visible sur les graphiques. La ligne rouge est la ligne droite trouvée y = 0,165x+2,184, la ligne bleue est , les points roses sont les données originales.

En pratique, lors de la modélisation de divers processus - notamment économiques, physiques, techniques, sociaux - l'une ou l'autre méthode de calcul des valeurs approximatives des fonctions à partir de leurs valeurs connues en certains points fixes est largement utilisée.

Ce type de problème d’approximation de fonctions se pose souvent :

lors de la construction de formules approximatives pour calculer les valeurs des quantités caractéristiques du processus étudié à l'aide de données tabulaires obtenues à la suite de l'expérience ;

en intégration numérique, différenciation, résolution d'équations différentielles, etc.;

si nécessaire, calculer les valeurs des fonctions aux points intermédiaires de l'intervalle considéré ;

lors de la détermination des valeurs de grandeurs caractéristiques d'un processus en dehors de l'intervalle considéré, notamment lors de la prévision.

Si, pour modéliser un certain processus spécifié par un tableau, nous construisons une fonction qui décrit approximativement ce processus sur la base de la méthode des moindres carrés, elle sera appelée fonction d'approximation (régression) et la tâche de construction des fonctions d'approximation elle-même sera appelée un problème d'approximation.

Cet article traite des capacités du package MS Excel pour résoudre ce type de problèmes. En outre, il fournit des méthodes et des techniques pour construire (créer) des régressions pour les fonctions tabulées (qui constituent la base de l'analyse de régression).

Excel propose deux options pour créer des régressions.

Ajout de régressions sélectionnées (lignes de tendance) à un diagramme construit sur la base d'un tableau de données pour la caractéristique du processus étudié (disponible uniquement si un diagramme a été construit) ;

Utilisation des fonctions statistiques intégrées de la feuille de calcul Excel, vous permettant d'obtenir des régressions (lignes de tendance) directement à partir du tableau de données source.

Ajouter des lignes de tendance à un graphique

Pour un tableau de données décrivant un processus et représenté par un diagramme, Excel dispose d'un outil d'analyse de régression efficace qui vous permet de :

construire sur la base de la méthode des moindres carrés et ajouter au diagramme cinq types de régressions, qui modélisent le processus étudié avec plus ou moins de précision ;

ajoutez l'équation de régression construite au diagramme ;

déterminer le degré de correspondance de la régression sélectionnée avec les données affichées sur le graphique.

Sur la base des données graphiques, Excel vous permet d'obtenir des types de régressions linéaires, polynomiales, logarithmiques, de puissance et exponentielles, qui sont spécifiées par l'équation :

y = y(x)

où x est une variable indépendante qui prend souvent les valeurs d'une séquence d'entiers naturels (1 ; 2 ; 3 ; ...) et produit, par exemple, un compte à rebours du temps du processus étudié (caractéristiques).

1 . La régression linéaire est idéale pour modéliser des caractéristiques dont les valeurs augmentent ou diminuent à un rythme constant. Il s’agit du modèle le plus simple à construire pour le processus étudié. Il est construit selon l'équation :

y = mx + b

où m est la tangente de la pente de régression linéaire à l'axe des x ; b - coordonnée du point d'intersection de la régression linéaire avec l'axe des ordonnées.

2 . Une ligne de tendance polynomiale est utile pour décrire des caractéristiques qui présentent plusieurs extrêmes distincts (maxima et minima). Le choix du degré polynomial est déterminé par le nombre d'extrema de la caractéristique étudiée. Ainsi, un polynôme du deuxième degré peut très bien décrire un processus qui n'a qu'un seul maximum ou minimum ; polynôme du troisième degré - pas plus de deux extrema; polynôme du quatrième degré - pas plus de trois extrema, etc.

Dans ce cas, la ligne de tendance est construite selon l'équation :

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

où les coefficients c0, c1, c2,... c6 sont des constantes dont les valeurs sont déterminées lors de la construction.

3 . La ligne de tendance logarithmique est utilisée avec succès lors de la modélisation de caractéristiques dont les valeurs changent initialement rapidement puis se stabilisent progressivement.

y = c ln(x) + b

4 . Une ligne de tendance en loi de puissance donne de bons résultats si les valeurs de la relation étudiée sont caractérisées par un changement constant du taux de croissance. Un exemple d’une telle dépendance est le graphique du mouvement uniformément accéléré d’une voiture. S'il y a des valeurs nulles ou négatives dans les données, vous ne pouvez pas utiliser de ligne de tendance de puissance.

Construit conformément à l'équation :

y = cxb

où les coefficients b, c sont des constantes.

5 . Une ligne de tendance exponentielle doit être utilisée lorsque le taux de changement des données augmente continuellement. Pour les données contenant des valeurs nulles ou négatives, ce type d'approximation n'est pas non plus applicable.

Construit conformément à l'équation :

y = c ebx

où les coefficients b, c sont des constantes.

Lors de la sélection d'une ligne de tendance, Excel calcule automatiquement la valeur de R2, qui caractérise la fiabilité de l'approximation : plus la valeur de R2 est proche de l'unité, plus la ligne de tendance se rapproche de manière fiable du processus étudié. Si nécessaire, la valeur R2 peut toujours être affichée sur le graphique.

Déterminé par la formule :

Pour ajouter une ligne de tendance à une série de données :

activer un graphique basé sur une série de données, c'est-à-dire cliquer dans la zone du graphique. L'élément Diagramme apparaîtra dans le menu principal ;

après avoir cliqué sur cet élément, un menu apparaîtra sur l'écran dans lequel vous devrez sélectionner la commande Ajouter une ligne de tendance.

Les mêmes actions peuvent être facilement mises en œuvre en déplaçant le pointeur de la souris sur le graphique correspondant à l'une des séries de données et en cliquant avec le bouton droit ; Dans le menu contextuel qui apparaît, sélectionnez la commande Ajouter une ligne de tendance. La boîte de dialogue Trendline apparaîtra à l'écran avec l'onglet Type ouvert (Fig. 1).

Après cela, vous avez besoin de :

Sélectionnez le type de ligne de tendance requis dans l'onglet Type (le type Linéaire est sélectionné par défaut). Pour le type Polynôme, dans le champ Degré, précisez le degré du polynôme sélectionné.

1 . Le champ Construit sur la série répertorie toutes les séries de données du graphique en question. Pour ajouter une ligne de tendance à une série de données spécifique, sélectionnez son nom dans le champ Construit sur la série.

Si nécessaire, en allant dans l'onglet Paramètres (Fig. 2), vous pouvez définir les paramètres suivants pour la ligne de tendance :

modifiez le nom de la ligne de tendance dans le champ Nom de la courbe approximative (lissée).

définir le nombre de périodes (en avant ou en arrière) pour la prévision dans le champ Prévisions ;

afficher l'équation de la ligne de tendance dans la zone du diagramme, pour laquelle vous devez cocher la case Afficher l'équation sur le diagramme ;

afficher la valeur de fiabilité d'approximation R2 dans la zone du diagramme, pour laquelle vous devez cocher la case Placer la valeur de fiabilité d'approximation sur le diagramme (R^2) ;

définissez le point d'intersection de la ligne de tendance avec l'axe Y, pour lequel vous devez cocher la case pour l'intersection de la courbe avec l'axe Y en un point ;

Cliquez sur le bouton OK pour fermer la boîte de dialogue.

Pour commencer à éditer une ligne de tendance déjà tracée, il existe trois manières :

utiliser la commande Ligne de tendance sélectionnée du menu Format, après avoir sélectionné au préalable la ligne de tendance ;

sélectionnez la commande Formater la ligne de tendance dans le menu contextuel, appelée par un clic droit sur la ligne de tendance ;

double-cliquez sur la ligne de tendance.

La boîte de dialogue Format de ligne de tendance apparaîtra à l'écran (Fig. 3), contenant trois onglets : Affichage, Type, Paramètres, et le contenu des deux derniers coïncide complètement avec les onglets similaires de la boîte de dialogue Ligne de tendance (Fig. 1). -2). Dans l'onglet Affichage, vous pouvez définir le type de ligne, sa couleur et son épaisseur.

Pour supprimer une ligne de tendance déjà tracée, sélectionnez la ligne de tendance à supprimer et appuyez sur la touche Suppr.

Les avantages de l'outil d'analyse de régression considéré sont :

la relative facilité de construire une ligne de tendance sur des graphiques sans créer de tableau de données pour celle-ci ;

une liste assez large de types de lignes de tendance proposées, et cette liste comprend les types de régression les plus couramment utilisés ;

la capacité de prédire le comportement du processus étudié par un nombre arbitraire (dans les limites du bon sens) de pas en avant et également en arrière ;

la capacité d'obtenir l'équation de la ligne de tendance sous forme analytique ;

la possibilité, le cas échéant, d'obtenir une évaluation de la fiabilité de l'approximation.

Les inconvénients sont les suivants :

la construction d'une ligne de tendance n'est réalisée que s'il existe un diagramme construit sur une série de données ;

le processus de génération de séries de données pour la caractéristique étudiée sur la base des équations de ligne de tendance obtenues pour celle-ci est quelque peu encombré : les équations de régression requises sont mises à jour à chaque changement dans les valeurs de la série de données d'origine, mais uniquement dans la zone du graphique , tandis que la série de données formée sur la base de l'ancienne tendance de l'équation linéaire reste inchangée ;

Dans les rapports de graphique croisé dynamique, la modification de l'affichage d'un graphique ou d'un rapport de tableau croisé dynamique associé ne conserve pas les courbes de tendance existantes, ce qui signifie qu'avant de tracer des courbes de tendance ou de formater un rapport de graphique croisé dynamique, vous devez vous assurer que la présentation du rapport répond aux exigences requises.

Les lignes de tendance peuvent être utilisées pour compléter les séries de données présentées sur des graphiques tels que des graphiques, des histogrammes, des graphiques à aires plates non standardisées, des graphiques à barres, des graphiques à nuages de points, des graphiques à bulles et des graphiques boursiers.

Vous ne pouvez pas ajouter de lignes de tendance aux séries de données dans les graphiques 3D, normalisés, radar, circulaires et en anneau.

Utiliser les fonctions intégrées d'Excel

Excel dispose également d'un outil d'analyse de régression pour tracer des lignes de tendance en dehors de la zone du graphique. Il existe un certain nombre de fonctions de feuille de calcul statistique que vous pouvez utiliser à cette fin, mais toutes vous permettent uniquement de créer des régressions linéaires ou exponentielles.

Excel dispose de plusieurs fonctions pour construire une régression linéaire, notamment :

S'ORIENTER;

PENTE et COUPE.

Ainsi que plusieurs fonctions pour construire une ligne de tendance exponentielle, notamment :

LGRFPRIBL.

Il convient de noter que les techniques de construction de régressions utilisant les fonctions TENDANCE et CROISSANCE sont quasiment les mêmes. La même chose peut être dite à propos de la paire de fonctions LINEST et LGRFPRIBL. Pour ces quatre fonctions, la création d'un tableau de valeurs utilise des fonctionnalités d'Excel telles que les formules matricielles, ce qui encombre quelque peu le processus de construction des régressions. Notez également que la construction d'une régression linéaire, à notre avis, est plus facilement réalisée en utilisant les fonctions SLOPE et INTERCEPT, où la première d'entre elles détermine la pente de la régression linéaire, et la seconde détermine le segment intercepté par la régression sur le y -axe.

Les avantages de l'outil de fonctions intégré pour l'analyse de régression sont :

un processus assez simple et uniforme de génération de séries de données de la caractéristique étudiée pour toutes les fonctions statistiques intégrées qui définissent les lignes de tendance ;

méthodologie standard pour construire des lignes de tendance basées sur des séries de données générées ;

la capacité de prédire le comportement du processus étudié par le nombre requis de pas en avant ou en arrière.

Les inconvénients incluent le fait qu'Excel ne dispose pas de fonctions intégrées pour créer d'autres types de lignes de tendance (sauf linéaires et exponentielles). Cette circonstance ne permet souvent pas de choisir un modèle suffisamment précis du processus étudié, ni d'obtenir des prévisions proches de la réalité. De plus, lors de l'utilisation des fonctions TENDANCE et CROISSANCE, les équations des lignes de tendance ne sont pas connues.

Il convient de noter que les auteurs n’ont pas eu pour objectif de présenter le déroulement de l’analyse de régression de manière exhaustive. Sa tâche principale est de montrer, à l'aide d'exemples précis, les capacités du package Excel lors de la résolution de problèmes d'approximation ; démontrer les outils efficaces dont dispose Excel pour créer des régressions et des prévisions ; illustrent comment de tels problèmes peuvent être résolus relativement facilement, même par un utilisateur qui n'a pas de connaissances approfondies en analyse de régression.

Exemples de résolution de problèmes spécifiques

Examinons la résolution de problèmes spécifiques à l'aide des outils Excel répertoriés.

Problème 1

Avec un tableau de données sur les bénéfices d'une entreprise de transport automobile pour 1995-2002. vous devez faire ce qui suit :

Construisez un diagramme.

Ajoutez des lignes de tendance linéaires et polynomiales (quadratiques et cubiques) au graphique.

À l'aide des équations de ligne de tendance, obtenez des données tabulaires sur les bénéfices des entreprises pour chaque ligne de tendance pour 1995-2004.

Faites une prévision du bénéfice de l'entreprise pour 2003 et 2004.

La solution du problème

Dans la plage de cellules A4:C11 de la feuille de calcul Excel, entrez la feuille de calcul illustrée à la Fig. 4.

Après avoir sélectionné la plage de cellules B4:C11, nous construisons un diagramme.

Nous activons le diagramme construit et, selon la méthode décrite ci-dessus, après avoir sélectionné le type de ligne de tendance dans la boîte de dialogue Ligne de tendance (voir Fig. 1), nous ajoutons alternativement des lignes de tendance linéaires, quadratiques et cubiques au diagramme. Dans la même boîte de dialogue, ouvrez l'onglet Paramètres (voir Fig. 2), dans le champ Nom de la courbe approximative (lissée), saisissez le nom de la tendance à ajouter, et dans le champ Prévision pour : périodes, définissez le valeur 2, puisqu'il est prévu de faire une prévision de bénéfice pour deux ans à l'avance. Pour afficher l'équation de régression et la valeur de fiabilité d'approximation R2 dans la zone du diagramme, cochez les cases Afficher l'équation à l'écran et placez la valeur de fiabilité d'approximation (R ^ 2) sur le diagramme. Pour une meilleure perception visuelle, nous modifions le type, la couleur et l'épaisseur des lignes de tendance construites, pour lesquelles nous utilisons l'onglet Affichage de la boîte de dialogue Format de ligne de tendance (voir Fig. 3). Le diagramme résultant avec les lignes de tendance ajoutées est présenté sur la Fig. 5.

Obtenir des données tabulaires sur les bénéfices des entreprises pour chaque ligne de tendance pour 1995-2004. Utilisons les équations de courbe de tendance présentées dans la Fig. 5. Pour ce faire, dans les cellules de la plage D3:F3, saisissez des informations textuelles sur le type de ligne de tendance sélectionnée : Tendance linéaire, Tendance quadratique, Tendance cubique. Ensuite, entrez la formule de régression linéaire dans la cellule D4 et, à l'aide du marqueur de remplissage, copiez cette formule avec les références relatives à la plage de cellules D5:D13. Il convient de noter que chaque cellule avec une formule de régression linéaire de la plage de cellules D4:D13 a comme argument une cellule correspondante de la plage A4:A13. De même, pour la régression quadratique, remplissez la plage de cellules E4:E13, et pour la régression cubique, remplissez la plage de cellules F4:F13. Ainsi, une prévision du bénéfice de l'entreprise pour 2003 et 2004 a été établie. en utilisant trois tendances. Le tableau de valeurs résultant est présenté sur la Fig. 6.

Problème 2

Construisez un diagramme.

Ajoutez des lignes de tendance logarithmiques, de puissance et exponentielles au graphique.

Dérivez les équations des lignes de tendance obtenues, ainsi que les valeurs de fiabilité de l'approximation R2 pour chacune d'elles.

À l'aide des équations de ligne de tendance, obtenez des données tabulaires sur les bénéfices de l'entreprise pour chaque ligne de tendance pour 1995-2002.

Faites une prévision des bénéfices de l'entreprise pour 2003 et 2004 à l'aide de ces lignes de tendance.

La solution du problème

En suivant la méthodologie donnée dans la résolution du problème 1, nous obtenons un diagramme auquel sont ajoutées des lignes de tendance logarithmiques, de puissance et exponentielles (Fig. 7). Ensuite, en utilisant les équations de ligne de tendance obtenues, nous remplissons un tableau de valeurs pour le bénéfice de l'entreprise, comprenant les valeurs prévues pour 2003 et 2004. (Fig. 8).

En figue. 5 et fig. on voit que le modèle à tendance logarithmique correspond à la valeur la plus faible de fiabilité d'approximation

R2 = 0,8659

Les valeurs les plus élevées de R2 correspondent aux modèles à tendance polynomiale : quadratique (R2 = 0,9263) et cubique (R2 = 0,933).

Problème 3

Avec le tableau des données sur les bénéfices d'une entreprise de transport automobile pour 1995-2002, donné dans la tâche 1, vous devez effectuer les étapes suivantes.

Obtenez des séries de données pour les lignes de tendance linéaires et exponentielles à l'aide des fonctions TREND et GROW.

À l’aide des fonctions TENDANCE et CROISSANCE, faites une prévision du bénéfice de l’entreprise pour 2003 et 2004.

Construisez un diagramme pour les données originales et la série de données résultante.

La solution du problème

Utilisons la feuille de travail pour le problème 1 (voir Fig. 4). Commençons par la fonction TREND :

sélectionnez la plage de cellules D4:D11, qui doit être remplie avec les valeurs de la fonction TENDANCE correspondant aux données connues sur le bénéfice de l'entreprise ;

Appelez la commande Fonction depuis le menu Insertion. Dans la boîte de dialogue Assistant de fonction qui apparaît, sélectionnez la fonction TENDANCE dans la catégorie Statistique, puis cliquez sur le bouton OK. La même opération peut être effectuée en cliquant sur le bouton (Insérer une fonction) dans la barre d'outils standard.

Dans la boîte de dialogue Arguments de fonction qui apparaît, entrez la plage de cellules C4:C11 dans le champ Known_values_y ; dans le champ Known_values_x - la plage de cellules B4:B11 ;

Pour transformer la formule saisie en formule matricielle, utilisez la combinaison de touches + + .

La formule que nous avons saisie dans la barre de formule ressemblera à : =(TREND(C4:C11,B4:B11)).

En conséquence, la plage de cellules D4:D11 est remplie des valeurs correspondantes de la fonction TREND (Fig. 9).

Faire une prévision du bénéfice de l'entreprise pour 2003 et 2004. nécessaire:

sélectionnez la plage de cellules D12:D13 où les valeurs prédites par la fonction TENDANCE seront saisies.

appelez la fonction TREND et dans la boîte de dialogue Arguments de fonction qui apparaît, entrez dans le champ Known_values_y - la plage de cellules C4:C11 ; dans le champ Known_values_x - la plage de cellules B4:B11 ; et dans le champ New_values_x - la plage de cellules B12:B13.

transformez cette formule en formule matricielle en utilisant la combinaison de touches Ctrl + Maj + Entrée.

La formule saisie ressemblera à : =(TREND(C4:C11;B4:B11;B12:B13)), et la plage de cellules D12:D13 sera remplie avec les valeurs prédites de la fonction TREND (voir Fig. 9).

La série de données est également renseignée à l'aide de la fonction CROISSANCE, qui est utilisée dans l'analyse des dépendances non linéaires et fonctionne exactement de la même manière que son homologue linéaire TENDANCE.

La figure 10 montre le tableau en mode d'affichage de formule.

Pour les données initiales et la série de données obtenues, le diagramme présenté à la Fig. onze.

Problème 4

Avec le tableau des données de réception des demandes de prestations par le service de répartition d'une entreprise de transport automobile pour la période du 1er au 11 du mois en cours, vous devez effectuer les actions suivantes.

Obtenez des séries de données pour la régression linéaire : en utilisant les fonctions SLOPE et INTERCEPT ; en utilisant la fonction LINEST.

Obtenez une série de données pour la régression exponentielle à l'aide de la fonction LGRFPRIBL.

A l'aide des fonctions ci-dessus, faites une prévision de la réception des candidatures au service dispatch pour la période du 12 au 14 du mois en cours.

Créez un diagramme pour les séries de données originales et reçues.

La solution du problème

Notez que contrairement aux fonctions TENDANCE et CROISSANCE, aucune des fonctions listées ci-dessus (SLOPE, INTERCEPT, LINEST, LGRFPRIB) n'est une régression. Ces fonctions ne jouent qu'un rôle de support, déterminant les paramètres de régression nécessaires.

Pour les régressions linéaires et exponentielles construites à l'aide des fonctions SLOPE, INTERCEPT, LINEST, LGRFPRIB, l'apparence de leurs équations est toujours connue, contrairement aux régressions linéaires et exponentielles correspondant aux fonctions TENDANCE et CROISSANCE.

1 . Construisons une régression linéaire avec l'équation :

y = mx+b

en utilisant les fonctions SLOPE et INTERCEPT, avec la pente de régression m déterminée par la fonction SLOPE, et le terme libre b par la fonction INTERCEPT.

Pour ce faire, nous effectuons les actions suivantes :

entrez le tableau d'origine dans la plage de cellules A4:B14 ;

la valeur du paramètre m sera déterminée dans la cellule C19. Sélectionnez la fonction Pente dans la catégorie Statistique ; entrez la plage de cellules B4:B14 dans le champ known_values_y et la plage de cellules A4:A14 dans le champ known_values_x. La formule sera saisie dans la cellule C19 : =SLOPE(B4:B14,A4:A14);

En utilisant une technique similaire, la valeur du paramètre b dans la cellule D19 est déterminée. Et son contenu ressemblera à : =SEGMENT(B4:B14,A4:A14). Ainsi, les valeurs des paramètres m et b nécessaires à la construction d'une régression linéaire seront respectivement stockées dans les cellules C19, D19 ;

Ensuite, entrez la formule de régression linéaire dans la cellule C4 sous la forme : =$C*A4+$D. Dans cette formule, les cellules C19 et D19 sont écrites avec des références absolues (l'adresse de la cellule ne doit pas changer lors d'une éventuelle copie). Le signe de référence absolue $ peut être saisi soit au clavier, soit à l'aide de la touche F4, après avoir placé le curseur sur l'adresse de la cellule. À l’aide de la poignée de recopie, copiez cette formule dans la plage de cellules C4:C17. Nous obtenons la série de données requise (Fig. 12). Étant donné que le nombre de requêtes est un nombre entier, vous devez définir le format numérique avec le nombre de décimales sur 0 dans l'onglet Nombre de la fenêtre Format de cellule.

2 . Construisons maintenant une régression linéaire donnée par l'équation :

y = mx+b

en utilisant la fonction LINEST.

Pour ça:

Entrez la fonction LINEST sous forme de formule matricielle dans la plage de cellules C20:D20: =(LINEST(B4:B14,A4:A14)). En conséquence, nous obtenons la valeur du paramètre m dans la cellule C20, et la valeur du paramètre b dans la cellule D20 ;

entrez la formule dans la cellule D4 : =$C*A4+$D ;

copiez cette formule à l'aide du marqueur de remplissage dans la plage de cellules D4: D17 et obtenez la série de données souhaitée.

3 . Nous construisons une régression exponentielle avec l'équation :

en utilisant la fonction LGRFPRIBL, cela s'effectue de la même manière :

Dans la plage de cellules C21:D21, nous entrons la fonction LGRFPRIBL sous forme de formule matricielle : =( LGRFPRIBL (B4:B14,A4:A14)). Dans ce cas, la valeur du paramètre m sera déterminée dans la cellule C21, et la valeur du paramètre b sera déterminée dans la cellule D21 ;

la formule est saisie dans la cellule E4 : =$D*$C^A4 ;

à l'aide du marqueur de remplissage, cette formule est copiée dans la plage de cellules E4:E17, où se trouvera la série de données pour la régression exponentielle (voir Fig. 12).

En figue. La figure 13 montre un tableau dans lequel vous pouvez voir les fonctions que nous utilisons avec les plages de cellules requises, ainsi que les formules.

Ordre de grandeur R. 2 appelé coefficient de détermination.

La tâche de construction d'une dépendance de régression est de trouver le vecteur des coefficients m du modèle (1) auquel le coefficient R prend la valeur maximale.

Pour évaluer la signification de R, le test F de Fisher est utilisé, calculé à l'aide de la formule

Où n- taille de l'échantillon (nombre d'expériences) ;

k est le nombre de coefficients du modèle.

Si F dépasse une valeur critique pour les données n Et k et la probabilité de confiance acceptée, alors la valeur de R est considérée comme significative. Des tableaux de valeurs critiques de F sont donnés dans des ouvrages de référence sur les statistiques mathématiques.

Ainsi, la signification de R est déterminée non seulement par sa valeur, mais aussi par le rapport entre le nombre d'expériences et le nombre de coefficients (paramètres) du modèle. En effet, le rapport de corrélation pour n=2 pour un modèle linéaire simple est égal à 1 (une seule droite peut toujours être tracée passant par 2 points sur un plan). Cependant, si les données expérimentales sont des variables aléatoires, une telle valeur de R doit être considérée avec beaucoup de prudence. Habituellement, pour obtenir un R significatif et une régression fiable, ils s'efforcent de garantir que le nombre d'expériences dépasse largement le nombre de coefficients du modèle (n>k).

Pour construire un modèle de régression linéaire, vous avez besoin de :

1) préparer une liste de n lignes et m colonnes contenant des données expérimentales (colonne contenant la valeur de sortie Oui doit être soit le premier, soit le dernier de la liste ); Par exemple, reprenons les données de la tâche précédente, en ajoutant une colonne appelée « N° de période », numérotons les numéros de période de 1 à 12. (ce seront les valeurs X)

2) allez dans le menu Données/Analyse des données/Régression

Si l'élément « Analyse des données » dans le menu « Outils » est manquant, vous devez alors accéder à l'élément « Compléments » dans le même menu et cocher la case « Package d'analyse ».

3) dans la boîte de dialogue "Régression", définissez :

· intervalle d'entrée Y ;

· intervalle d'entrée X ;

· intervalle de sortie - la cellule supérieure gauche de l'intervalle dans laquelle les résultats du calcul seront placés (il est recommandé de les placer sur une nouvelle feuille de calcul) ;