La méthode des moindres carrés dans Excel

La méthode des moindres carrés est une procédure mathématique de construction d'une équation linéaire, ce qui correspondrait le plus précisément à l'ensemble de deux rangées de nombres. L'utilisation de cette méthode est de minimiser l'erreur quadratique totale. Le programme Excel dispose d'outils avec lesquels cette méthode peut être appliquée lors du calcul. Traisons comment cela se fait.

Utilisation de la méthode dans Excel

La méthode des moindres carrés (MNC) est une description mathématique de la dépendance d'une variable de la seconde. Il peut être utilisé lors de la prévision.

Activation du complément de «recherche de solutions»

Pour utiliser MNA dans Excel, vous devez activer le complément «Solution Search», qui est désactivé par défaut.

1. Allez dans l'onglet "Fichier".



2. Cliquez sur la section Nom "Paramètres".



3. Dans la fenêtre qui s'ouvre, arrêtez le choix sur la sous-section "Complémentation".



4. Dans le bloc "Gestion", situé au bas de la fenêtre, réglez le commutateur sur la position "Excel Add-in" (si une autre valeur est définie) et cliquez sur le bouton "Go ...".



5. Une petite fenêtre s'ouvre. Nous avons mis une tique près du paramètre "Solution de recherche". Cliquez sur le bouton "OK".

Maintenant, la fonction de fonction Solution dans Excel est activée et ses outils sont apparus sur la bande.

Leçon: Recherche de solutions dans Excele

Conditions du problème

Nous décrivons l'utilisation de MNK sur un exemple spécifique. Nous avons deux rangées de chiffres X. et y. La séquence dont elle est présentée dans l'image ci-dessous.

La dépendance la plus précisée peut décrire la fonction:

y = a + nx

Dans le même temps, on sait que quand x = 0. y. Aussi égal 0 . Par conséquent, cette équation peut être décrite par dépendance y = nx..

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

Solution

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

1. À gauche de la première signification X. mettre un chiffre 1 . Ce sera une valeur approximative de la première valeur du coefficient N..



2. À droite de la colonne y. Ajouter une autre colonne - NX. Dans la première cellule de cette colonne, écrivez la multiplication de formule du coefficient. N. sur la cellule de la première variable X. . Dans le même temps, le lien vers le champ avec le coefficient est fabriqué absolu, car cette valeur ne changera pas. Cliquez sur le bouton ENTER.



3. En utilisant le marqueur de remplissage, copiez cette formule sur toute la plage de la table dans la colonne ci-dessous.



4. Dans une cellule distincte, calculez la somme de la différence dans les carrés des valeurs y. et Nx. . Pour ce faire, cliquez sur le bouton "Insérer la fonction".



5. Dans le "maître des fonctions" qui s'ouvre, je suis à la recherche d'un disque "sommvson". Choisissez-le et cliquez sur le bouton "OK".



6. La fenêtre d'argumentation s'ouvre. Dans le champ "massive_x", nous entrons dans la gamme de cellules de colonne. y. . Dans le champ "Massive_Y", nous entrons dans la gamme de cellules de colonne. Nx. . Pour entrer les valeurs, installez simplement le curseur dans le champ et sélectionnez la plage appropriée sur la feuille. Après avoir entré, appuyez sur le bouton "OK".



7. Aller à l'onglet "Données". Sur la bande dans le bloc d'outils "Analyse", nous cliquons sur le bouton "Solution Search".



8. La fenêtre de paramètre s'ouvre. Dans le champ "Optimiser la fonction cible", spécifiez l'adresse de la cellule avec la formule "Sumkvson". Dans le paramètre "BC", veillez à définir le commutateur sur la position "minimum". Dans le champ "Changement de cellule", spécifiez l'adresse avec la valeur du coefficient N. . Cliquez sur le bouton "Rechercher la solution".



9. La solution sera affichée dans la cellule du coefficient N. . Cette valeur sera le plus petit carré de la fonction. Si le résultat satisfait à l'utilisateur, cliquez sur le bouton "OK" de l'option.

Comme vous pouvez le constater, l'utilisation de la méthode de la méthode la moins carrée est une procédure mathématique plutôt compliquée. Nous l'avons montré en action sur l'exemple le plus simple et il y a des cas beaucoup plus complexes. Cependant, la boîte à outils Microsoft Excel est conçue pour simplifier les calculs.