La méthode des moindres carrés (OLS) appartient au domaine de l'analyse de régression. 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 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 qui doit être détectée. 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

L'analyse de régression dans Excel est mise en œuvre à 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 une procédure mathématique permettant de construire une équation linéaire qui correspondra le plus précisément à un ensemble de deux séries de nombres. Le but de l’utilisation de cette méthode est de minimiser l’erreur quadratique totale. Excel dispose d'outils qui peuvent vous aider à appliquer cette méthode à vos calculs. Voyons comment cela se fait.

· Utiliser la méthode dans Excel

o Activation du module complémentaire « Recherche de solutions »

o Conditions problématiques

oSolution

Utiliser la méthode dans Excel

La méthode des moindres carrés (LSM) est une description mathématique de la dépendance d'une variable par rapport à une autre. Il peut être utilisé à des fins de prévision.

Activation du module complémentaire Rechercher une solution

Pour utiliser MNC dans Excel, vous devez activer le complément "Trouver une solution", qui est désactivé par défaut.

1. Allez dans l'onglet "Déposer".

2. Cliquez sur le nom de la section "Options".

3. Dans la fenêtre qui s'ouvre, sélectionnez la sous-section "Modules complémentaires".

4. Dans le bloc "Contrôle", qui se trouve en bas de la fenêtre, placez le commutateur en position "Compléments Excel"(s'il a une valeur différente) et cliquez sur le bouton "Aller...".

5. Une petite fenêtre s'ouvre. On met une coche à côté du paramètre "Trouver une solution". Cliquez sur le bouton "D'ACCORD".

Maintenant la fonction Trouver une solution dans Excel est activé et ses outils apparaissent sur le ruban.

Leçon: Trouver une solution dans Excel

Conditions du problème

Décrivons l'utilisation de LSM à l'aide d'un exemple spécifique. Nous avons deux rangées de nombres X Et oui, dont la séquence est présentée dans l'image ci-dessous.

Cette dépendance peut être décrite plus précisément par la fonction :

En même temps, on sait que lorsque x=0 etégalement égal 0 . Par conséquent, cette équation peut être décrite par la dépendance y = nx.

Nous devons trouver la somme minimale des carrés de la différence.

Solution

Passons à une description de l'application directe de la méthode.

1. À gauche de la première valeur X mettre un numéro 1 . Ce sera une valeur approximative de la première valeur du coefficient n.

2. À droite de la colonne oui ajouter une autre colonne - nx. Dans la première cellule de cette colonne nous écrivons la formule de multiplication du coefficient n par cellule de la première variable X. En même temps, on fait le lien avec le champ à coefficient absolu, puisque cette valeur ne changera pas. Cliquez sur le bouton Entrer.

3. À l'aide du marqueur de remplissage, copiez cette formule dans toute la plage du tableau dans la colonne ci-dessous.

4. Dans une cellule séparée, calculez la somme des différences entre les carrés des valeurs oui Et nx. Pour cela, cliquez sur le bouton "Insérer une fonction".

5. À l'air libre "Assistant de fonction" je cherche une entrée "SUMMKVARNA". Sélectionnez-le et appuyez sur le bouton "D'ACCORD".

6. La fenêtre des arguments s'ouvre. Sur le terrain "Tableau_x" oui. Sur le terrain "Tableau_y" entrez la plage de cellules de la colonne nx. Pour saisir des valeurs, placez simplement le curseur dans le champ et sélectionnez la plage correspondante sur la feuille. Après avoir entré, cliquez sur le bouton "D'ACCORD".

7. Allez dans l'onglet "Données". Sur le ruban dans la boîte à outils "Analyse" cliquez sur le bouton "Trouver une solution".

8. La fenêtre des paramètres de cet outil s'ouvre. Sur le terrain « Optimiser la fonction objectif » indiquer l'adresse de la cellule avec la formule "SUMMKVARNA". Dans le paramètre "Avant" assurez-vous de mettre l'interrupteur en position "Le minimum". Sur le terrain "Changer de cellule" indiquer l'adresse avec la valeur du coefficient n. Cliquez sur le bouton "Trouver une solution".

9. La solution sera affichée dans la cellule du coefficient n. Cette valeur sera le moindre carré de la fonction. Si le résultat satisfait l'utilisateur, alors cliquez sur le bouton "D'ACCORD" dans une fenêtre supplémentaire.

Comme vous pouvez le constater, l’application de la méthode des moindres carrés est une procédure mathématique assez complexe. Nous l'avons montré en action à l'aide d'un exemple simple, mais il existe des cas beaucoup plus complexes. Cependant, les outils Microsoft Excel sont conçus pour simplifier au maximum les calculs.

http://multitest.semico.ru/mnk.htm

Dispositions générales

Plus le nombre en valeur absolue est petit, meilleure est la droite choisie (2). Comme caractéristique de la précision de la sélection d'une droite (2), on peut prendre la somme des carrés

Les conditions minimales pour S seront

	(6)
	(7)

Les équations (6) et (7) peuvent s'écrire comme suit :

	(8)
	(9)

À partir des équations (8) et (9), il est facile de trouver a et b à partir des valeurs expérimentales de xi et y i. La droite (2), définie par les équations (8) et (9), est appelée droite obtenue par la méthode des moindres carrés (ce nom souligne que la somme des carrés S a un minimum). Les équations (8) et (9), à partir desquelles la droite (2) est déterminée, sont appelées équations normales.

Vous pouvez indiquer une manière simple et générale de composer des équations normales. En utilisant les points expérimentaux (1) et l'équation (2), nous pouvons écrire un système d'équations pour a et b

y 1 =ax 1 +b,
y 2 =ax 2 +b, ...		(10)
y n = hache n + b,

Multiplions les côtés gauche et droit de chacune de ces équations par le coefficient de la première inconnue a (c'est-à-dire par x 1, x 2, ..., x n) et ajoutons les équations résultantes, ce qui donne la première équation normale (8) .

Multiplions les côtés gauche et droit de chacune de ces équations par le coefficient de la deuxième inconnue b, c'est-à-dire par 1, et additionnez les équations résultantes, le résultat est la deuxième équation normale (9).

Cette méthode d'obtention des équations normales est générale : elle convient par exemple à la fonction

il existe une valeur constante et elle doit être déterminée à partir de données expérimentales (1).

Le système d’équations de k peut s’écrire :

Trouvez la droite (2) en utilisant la méthode des moindres carrés.

Solution. Nous trouvons:

X je =21, y je =46,3, x je 2 =91, x je y je =179,1.

On écrit les équations (8) et (9)91a+21b=179.1,

21a+6b=46.3, d'ici on trouve
a=0,98 b=4,3.

Méthode des moindres carrés utilisé pour estimer les paramètres de l’équation de régression.

L'analyse de régression est l'une des méthodes permettant d'étudier les relations stochastiques entre les caractéristiques.
L'analyse de régression est la dérivation d'une équation de régression, à l'aide de laquelle la valeur moyenne d'une variable aléatoire (attribut de résultat) est trouvée si la valeur d'une autre (ou d'autres) variables (attributs de facteur) est connue. Il comprend les étapes suivantes :

1. sélection de la forme de connexion (type d'équation de régression analytique) ;
2. estimation des paramètres de l'équation ;
3. évaluation de la qualité de l'équation de régression analytique.

Le plus souvent, une forme linéaire est utilisée pour décrire la relation statistique entre les entités. L'accent mis sur les relations linéaires s'explique par l'interprétation économique claire de ses paramètres, la variation limitée des variables et le fait que dans la plupart des cas, les formes de relations non linéaires sont converties (par logarithme ou substitution de variables) en une forme linéaire pour effectuer des calculs. .
Dans le cas d'une relation linéaire par paire, l'équation de régression prendra la forme : y i =a+b·x i +u i . Les paramètres a et b de cette équation sont estimés à partir des données d'observation statistique x et y. Le résultat d'une telle évaluation est l'équation : , où , sont des estimations des paramètres a et b , est la valeur de l'attribut résultant (variable) obtenu à partir de l'équation de régression (valeur calculée).

Le plus souvent utilisé pour estimer des paramètres méthode des moindres carrés (LSM).
La méthode des moindres carrés fournit les meilleures estimations (cohérentes, efficaces et impartiales) des paramètres de l'équation de régression. Mais seulement si certaines hypothèses concernant le terme aléatoire (u) et la variable indépendante (x) sont remplies (voir hypothèses OLS).

Le problème de l'estimation des paramètres d'une équation linéaire par paire à l'aide de la méthode des moindres carrés est la suivante : pour obtenir de telles estimations de paramètres , , auxquelles la somme des écarts carrés des valeurs réelles de la caractéristique résultante - y i par rapport aux valeurs calculées - est minime.
Officiellement Critère OLS peut s'écrire ainsi : .

Classification des méthodes des moindres carrés

1. Méthode des moindres carrés.
2. Méthode du maximum de vraisemblance (pour un modèle de régression linéaire classique normal, la normalité des résidus de régression est postulée).
3. La méthode des moindres carrés généralisés MCO est utilisée dans le cas d'autocorrélation d'erreurs et dans le cas d'hétéroscédasticité.
4. Méthode des moindres carrés pondérés (cas particulier des MCO avec résidus hétéroscédastiques).

Illustrons le propos méthode classique des moindres carrés graphiquement. Pour ce faire, nous allons construire un nuage de points basé sur des données d'observation (x i, y i, i = 1; n) dans un système de coordonnées rectangulaires (un tel nuage de points est appelé champ de corrélation). Essayons de sélectionner une droite la plus proche des points du champ de corrélation. Selon la méthode des moindres carrés, la droite est sélectionnée pour que la somme des carrés des distances verticales entre les points du champ de corrélation et cette droite soit minimale.

Notation mathématique pour ce problème : .
Les valeurs de y i et x i =1...n nous sont connues ; ce sont des données d'observation. Dans la fonction S, ils représentent des constantes. Les variables de cette fonction sont les estimations requises des paramètres - , . Pour trouver le minimum d'une fonction de deux variables, il faut calculer les dérivées partielles de cette fonction pour chacun des paramètres et les assimiler à zéro, c'est-à-dire .
En conséquence, nous obtenons un système de 2 équations linéaires normales :
En résolvant ce système, nous trouvons les estimations de paramètres requises :

L'exactitude du calcul des paramètres de l'équation de régression peut être vérifiée en comparant les montants (il peut y avoir un certain écart en raison de l'arrondi des calculs).
Pour calculer les estimations des paramètres, vous pouvez créer le tableau 1.
Le signe du coefficient de régression b indique le sens de la relation (si b >0, la relation est directe, si b<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Formellement, la valeur du paramètre a est la valeur moyenne de y avec x égal à zéro. Si le facteur d'attribut n'a pas et ne peut pas avoir une valeur nulle, alors l'interprétation ci-dessus du paramètre a n'a pas de sens.

Évaluer l'étroitesse de la relation entre les caractéristiques réalisée à l'aide du coefficient de corrélation de paire linéaire - r x,y. Il peut être calculé à l'aide de la formule : . De plus, le coefficient de corrélation linéaire des paires peut être déterminé grâce au coefficient de régression b : .
La plage de valeurs acceptables du coefficient de corrélation linéaire des paires va de –1 à +1. Le signe du coefficient de corrélation indique le sens de la relation. Si r x, y >0, alors la connexion est directe ; si r x, y<0, то связь обратная.
Si ce coefficient est proche de l'unité en grandeur, alors la relation entre les caractéristiques peut être interprétée comme une relation linéaire assez étroite. Si son module est égal à un ê r x , y ê =1, alors la relation entre les caractéristiques est fonctionnellement linéaire. Si les caractéristiques x et y sont linéairement indépendantes, alors r x,y est proche de 0.
Pour calculer r x,y, vous pouvez également utiliser le tableau 1.

Pour évaluer la qualité de l'équation de régression résultante, calculez le coefficient de détermination théorique - R 2 yx :

,
où d 2 est la variance de y expliquée par l'équation de régression ;
e 2 - variance résiduelle (inexpliquée par l'équation de régression) de y ;
s 2 y - variance totale (totale) de y.
Le coefficient de détermination caractérise la proportion de variation (dispersion) de l'attribut résultant y expliquée par la régression (et, par conséquent, le facteur x) dans la variation totale (dispersion) y. Le coefficient de détermination R 2 yx prend des valeurs de 0 à 1. En conséquence, la valeur 1-R 2 yx caractérise la proportion de variance y causée par l'influence d'autres facteurs non pris en compte dans les erreurs de modèle et de spécification.
Avec régression linéaire appariée, R 2 yx =r 2 yx.

Méthode des moindres carrés (LSM)

Un système de m équations linéaires à n inconnues a la forme :

Trois cas sont possibles : m n. Le cas où m=n a été considéré dans les paragraphes précédents. Quand m

Si m>n et que le système est cohérent, alors la matrice A a au moins m - n lignes linéairement dépendantes. Ici, la solution peut être obtenue en sélectionnant n n'importe quelle équation linéairement indépendante (si elles existent) et en appliquant la formule X = A -1 CV, c'est-à-dire en réduisant le problème à un problème précédemment résolu. Dans ce cas, la solution résultante satisfera toujours aux m - n équations restantes.

Cependant, lorsque vous utilisez un ordinateur, il est plus pratique d'utiliser une approche plus générale : la méthode des moindres carrés.

Méthode des moindres carrés algébriques

La méthode des moindres carrés algébriques est une méthode de résolution de systèmes d'équations linéaires

en minimisant la norme euclidienne

Hache? b? >inf. (1.2)

Analyse des données expérimentales

Considérons une expérience au cours de laquelle à des moments donnés

Par exemple, la température Q(t) est mesurée. Laissez les résultats de mesure être spécifiés par un tableau

Supposons que les conditions expérimentales soient telles que les mesures soient effectuées avec une erreur connue. Dans ces cas, la loi de changement de température Q(t) est recherchée à l'aide d'un certain polynôme

P(t) = + + + ... +,

déterminer les coefficients inconnus, ..., à partir des considérations que la valeur E(, ...,), définie par l'égalité

approximation exel algébrique de Gauss

a pris la valeur minimale. Puisque la somme des carrés est minimisée, cette méthode est appelée approximation des données par les moindres carrés.

Si on remplace P(t) par son expression, on obtient

Fixons-nous la tâche de définir un tableau pour que la valeur soit minimale, c'est-à-dire Définissons le tableau en utilisant la méthode des moindres carrés. Pour ce faire, on assimile les dérivées partielles à zéro :

Si vous entrez la matrice m × n A = (), i = 1, 2..., m; j = 1, 2, ..., n, où

je = 1, 2..., m; j = 1, 2, ..., n,

alors l'égalité écrite prendra la forme

Réécrivons l'égalité écrite en termes d'opérations avec des matrices. Par la définition de multiplier une matrice par une colonne, on a

Pour une matrice transposée, une relation similaire ressemble à ceci

Introduisons la notation : nous désignerons la i-ème composante du vecteur Ax Conformément aux égalités matricielles écrites, nous aurons

Sous forme matricielle, cette égalité peut être réécrite comme

A T x = A T B (1.3)

Ici A est une matrice m×n rectangulaire. De plus, dans les problèmes d’approximation de données, en règle générale, m > n. L'équation (1.3) est appelée une équation normale.

Il a été possible dès le début, en utilisant la norme euclidienne des vecteurs, d'écrire le problème sous forme matricielle équivalente :

Notre objectif est de minimiser cette fonction en x. Pour qu'un minimum soit atteint en un point de solution, les dérivées premières par rapport à x en ce point doivent être égales à zéro. Les dérivées de cette fonction sont

2A T B + 2A T Axe

et donc la solution doit satisfaire le système d'équations linéaires

(UNE T UNE)x = (UNE T B).

Ces équations sont appelées équations normales. Si A est une matrice m× n, alors A>A - n × n est une matrice, c'est-à-dire La matrice d’une équation normale est toujours une matrice carrée symétrique. De plus, il a la propriété de définition positive dans le sens où (A>Ax, x) = (Ax, Ax) ? 0.

Commentaire. Parfois, la solution d'une équation de la forme (1.3) est appelée solution du système Ax = B, où A est une matrice rectangulaire m × n (m > n) utilisant la méthode des moindres carrés.

Le problème des moindres carrés peut être interprété graphiquement comme la minimisation des distances verticales entre les points de données et une courbe de modèle (voir Figure 1.1). Cette idée repose sur l’hypothèse que toutes les erreurs d’approximation correspondent à des erreurs dans les observations. S'il existe également des erreurs dans les variables indépendantes, il peut alors être plus approprié de minimiser la distance euclidienne entre les données et le modèle.

MNC dans Excel

L'algorithme ci-dessous pour implémenter OLS dans Excel suppose que toutes les données initiales sont déjà connues. On multiplie les deux côtés de l'équation matricielle AЧX=B du système de gauche par la matrice transposée du système А Т :

A TAX=A T B

Ensuite, nous multiplions les deux côtés de l'équation de gauche par la matrice (A T A) -1. Si cette matrice existe, alors le système est défini. Étant donné que

(A T A) -1 *(A T A)=E, on obtient

X = (AT A) -1 AT B.

L'équation matricielle résultante est une solution à un système de m équations linéaires à n inconnues pour m>n.

Considérons l'application de l'algorithme ci-dessus à l'aide d'un exemple spécifique.

Exemple. Qu'il soit nécessaire de résoudre le système

Dans Excel, la feuille de solution en mode d'affichage de formule pour ce problème ressemble à ceci :

Résultats du calcul :

Le vecteur X recherché est situé dans la plage E11:E12.

Lors de la résolution d'un système donné d'équations linéaires, les fonctions suivantes ont été utilisées :

1. MOBR - renvoie la matrice inverse de la matrice stockée dans le tableau.

Syntaxe : MOBR (tableau).

Un tableau est un tableau numérique comportant un nombre égal de lignes et de colonnes.

2. MULTIPULT - renvoie le produit des matrices (les matrices sont stockées dans des tableaux). Le résultat est un tableau avec le même nombre de lignes que le tableau1 et le même nombre de colonnes que le tableau2.

Syntaxe : MULTIPLE (tableau1, tableau2).

Array1, array2 sont des tableaux multiplicables.

Après avoir entré une fonction dans la cellule supérieure gauche d’une plage de tableau, sélectionnez le tableau en commençant par la cellule contenant la formule, appuyez sur F2, puis appuyez sur CTRL+MAJ+ENTRÉE.

3. TRANSPORT - convertit un ensemble vertical de cellules en un ensemble horizontal, ou vice versa. Suite à l'utilisation de cette fonction, un tableau apparaît avec un nombre de lignes égal au nombre de colonnes du tableau d'origine et un nombre de colonnes égal au nombre de lignes du tableau initial.

4.1. Utilisation des fonctions intégrées

Calcul coefficients de régression effectué à l'aide de la fonction

LIGNE(Valeurs_y; valeurs x; Const; statistiques),

Valeurs_y- tableau de valeurs y,

valeurs x- tableau de valeurs facultatif X, si tableau X est omis, on suppose qu'il s'agit d'un tableau (1;2;3;...) de la même taille que Valeurs_y,

Const- une valeur booléenne qui indique si la constante est requise bétait égal à 0. Si Const a le sens VRAI ou omis, alors b est calculé de la manière habituelle. Si l'argument Const est FAUX, alors b est supposé être 0 et les valeurs un sont sélectionnés pour que la relation soit remplie y = hache.

Statistiques est une valeur booléenne qui indique si des statistiques de régression supplémentaires doivent être renvoyées. Si l'argument Statistiques a le sens VRAI, alors la fonction LIGNE renvoie des statistiques de régression supplémentaires. Si l'argument Statistiques a le sens MENSONGE ou omis, alors la fonction LIGNE renvoie uniquement le coefficient un et constante b.

Il faut rappeler que le résultat des fonctions LIGNE() est un ensemble de valeurs – un tableau.

Pour le calcul Coefficient de corrélation la fonction est utilisée

CORREL(Tableau1;Tableau2),

renvoyant les valeurs du coefficient de corrélation, où Tableau1- tableau de valeurs oui, Tableau2- tableau de valeurs X. Tableau1 Et Tableau2 doit être de la même taille.

EXEMPLE 1. Dépendance oui(X) est présenté dans le tableau. Construire ligne de régression et calculer Coefficient de corrélation.

oui		0.5		1.5		2.5		3.5
X		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

Entrons un tableau de valeurs dans une feuille MS Excel et construisons un nuage de points. La feuille de travail prendra la forme montrée sur la Fig. 2.

Afin de calculer les valeurs des coefficients de régression UN Et b sélectionner les cellules A7:B7, Passons à l'assistant de fonction et dans la catégorie Statistique sélectionner une fonction LIGNE. Remplissons la boîte de dialogue qui apparaît comme le montre la Fig. 3 et appuyez sur D'ACCORD.

Par conséquent, la valeur calculée n'apparaîtra que dans la cellule A6(Fig. 4). Pour que la valeur apparaisse dans la cellule B6 vous devez entrer en mode édition (touche F2), puis appuyez sur la combinaison de touches CTRL+MAJ+ENTRÉE.

Pour calculer la valeur du coefficient de corrélation dans une cellule C6 la formule suivante a été introduite :

C7=CORREL(B3:J3;B2:J2).

Connaître les coefficients de régression UN Et b calculons les valeurs de la fonction oui=hache+b pour donné X. Pour ce faire, nous introduisons la formule

B5=7$AU*B2+7$B$

et copiez-le dans la plage C5:J5(Fig.5).

Traçons la droite de régression sur le diagramme. Sélectionnez les points expérimentaux sur le graphique, faites un clic droit et sélectionnez la commande Donnée initiale. Dans la boîte de dialogue qui apparaît (Fig. 5), sélectionnez l'onglet Rangée et cliquez sur le bouton Ajouter. Remplissons les champs de saisie comme indiqué sur la Fig. 6 et appuyez sur le bouton D'ACCORD. Une ligne de régression sera ajoutée au graphique des données expérimentales. Par défaut, son graphique sera dessiné sous forme de points non reliés par des lignes de lissage.

Riz. 6

Pour modifier l'apparence de la ligne de régression, effectuez les étapes suivantes. Faites un clic droit sur les points représentant le graphique linéaire et sélectionnez la commande Type de graphique et définissez le type de diagramme de dispersion, comme indiqué sur la Fig. 7.

Le type de ligne, la couleur et l'épaisseur peuvent être modifiés comme suit. Sélectionnez une ligne sur le diagramme, faites un clic droit et sélectionnez la commande dans le menu contextuel Format des séries de données... Ensuite, effectuez les réglages, par exemple, comme indiqué sur la Fig. 8.

À la suite de toutes les transformations, nous obtenons un graphique de données expérimentales et une ligne de régression dans une zone graphique (Fig. 9).

4.2. Utiliser une ligne de tendance.

La construction de diverses dépendances approximatives dans MS Excel est implémentée en tant que propriété de graphique - ligne de tendance.

EXEMPLE 2. À la suite de l'expérience, une certaine dépendance à la table a été déterminée.

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

Sélectionnez et construisez une dépendance approximative. Construire des graphiques de dépendances tabulaires et analytiques sélectionnées.

La résolution du problème peut être divisée en les étapes suivantes : saisie des données initiales, construction d'un nuage de points et ajout d'une ligne de tendance à ce graphique.

Examinons ce processus en détail. Entrons les données initiales dans la feuille de calcul et traçons les données expérimentales. Ensuite, sélectionnez les points expérimentaux sur le graphique, faites un clic droit et utilisez la commande Ajouter je ligne de tendance(Fig. 10).

La boîte de dialogue qui apparaît permet de construire une relation d'approximation.

Le premier onglet (Fig. 11) de cette fenêtre indique le type de dépendance approximative.

Sur la seconde (Fig. 12) les paramètres de construction sont déterminés :

· nom de la dépendance approximative ;

· prévoir en avant (en arrière) par n unités (ce paramètre détermine combien d'unités en avant (en arrière) la ligne de tendance doit être étendue) ;

s'il faut afficher le point d'intersection d'une courbe avec une ligne droite y=const;

· afficher ou non la fonction d'approximation sur le diagramme (possibilité d'afficher l'équation sur le diagramme) ;

· s'il faut ou non placer la valeur de l'écart type sur le diagramme (possibilité de placer la valeur de la fiabilité de l'approximation sur le diagramme).

Choisissons un polynôme du deuxième degré comme dépendance approximative (Fig. 11) et affichons l'équation qui décrit ce polynôme sur un graphique (Fig. 12). Le diagramme résultant est présenté sur la Fig. 13.

De même en utilisant lignes de tendance vous pouvez sélectionner les paramètres de dépendances telles que

linéaire oui=a∙x+b,

logarithmique oui=a∙ln(X)+b,

· exponentiel oui=a∙e b,

· calme oui=a∙xb,

polynôme oui=a∙x 2 +b∙x+c, oui=a∙x 3 +b∙x 2 +c∙x+d et ainsi de suite, jusqu'à un polynôme du 6ème degré inclus,

· filtration linéaire.

4.3. Utiliser un bloc solveur

L'implémentation dans MS Excel de la sélection de paramètres à l'aide de la méthode des moindres carrés à l'aide d'un bloc solveur est d'un intérêt significatif. Cette technique permet de sélectionner les paramètres d'une fonction de tout type. Considérons cette possibilité en utilisant le problème suivant comme exemple.

EXEMPLE 3. À la suite de l'expérience, la dépendance z(t) a été obtenue, présentée dans le tableau

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

Sélectionnez les coefficients de dépendance Z(t) = À 4 + Bt 3 + Ct 2 + Dt + K méthode des moindres carrés.

Ce problème est équivalent au problème de trouver le minimum d'une fonction de cinq variables

Considérons le processus de résolution du problème d'optimisation (Fig. 14).

Laissez les valeurs UN, DANS, AVEC, D Et À stocké dans des cellules A7:E7. Calculons les valeurs théoriques de la fonction Z(t)=À 4 +Bt 3 +Ct 2 +Dt+K pour donné t(B2:J2). Pour ce faire, dans la cellule B4 entrez la valeur de la fonction au premier point (cellule B2):

B4=$A$7*B2^4+$B$7*B2^3+$C$7*B2^2+$D$7*B2+$E$7.

Copions cette formule dans la plage C4:J4 et obtenir la valeur attendue de la fonction aux points dont les abscisses sont stockées dans les cellules B2:J2.

Vers la cellule B5 Introduisons une formule qui calcule le carré de la différence entre les points expérimentaux et calculés :

B5=(B4-B3)^2,

et copiez-le dans la plage C5:J5. Dans une cellule F7 nous stockerons l’erreur quadratique totale (10). Pour cela, saisissez la formule :

F7 = SOMME(B5:J5).

Utilisons la commande Service®Rechercher une solution et résolvez le problème d'optimisation sans restrictions. Remplissons les champs de saisie dans la boîte de dialogue illustrée sur la figure en conséquence. 14 et appuyez sur le bouton Exécuter. Si une solution est trouvée, la fenêtre illustrée à la Fig. 15.

Le résultat du bloc de décision sera affiché dans les cellules A7:E7valeurs des paramètres les fonctions Z(t)=À 4 +Bt 3 +Ct 2 +Dt+K. Dans les cellules B4:J4 on a valeur de fonction attendue aux points de départ. Dans une cellule F7 sera stocké erreur quadratique totale.

Vous pouvez afficher des points expérimentaux et une ligne ajustée dans une zone graphique en sélectionnant une plage B2:J4, appel Assistant Graphique puis formater apparence graphiques reçus.

Riz. 17 affiche la feuille de calcul MS Excel une fois les calculs effectués.

5. RÉFÉRENCES

1. Alekseev E.R., Chesnokova O.V., Résolution de problèmes de mathématiques computationnelles dans les packages Mathcad12, MATLAB7, Maple9. – NT Press, 2006.–596 p. :il. -(Didacticiel)

2. Alekseev E.R., Chesnokova O.V., E.A. Rudchenko, Scilab, résolution de problèmes d'ingénierie et de mathématiques. –M., BINOM, 2008.–260 p.

3. Berezin I.S., Zhidkov N.P., Méthodes de calcul. – M. : Nauka, 1966. – 632 p.

4. Garnaev A.Yu., Utilisation de MS EXCEL et VBA en économie et finance. – Saint-Pétersbourg : BHV - Pétersbourg, 1999.–332 p.

5. Demidovich B.P., Maron I.A., Shuvalova V.Z., Méthodes numériques d'analyse. – M. : Nauka, 1967. – 368 p.

6. Korn G., Korn T., Manuel de mathématiques pour scientifiques et ingénieurs. – M., 1970, 720 p.

7. Alekseev E.R., Chesnokova O.V. Lignes directrices pour effectuer des travaux de laboratoire dans MS EXCEL. Pour les étudiants de toutes spécialités. Donetsk, DonNTU, 2004. 112 p.