Metoda nejmenších čtverců v Excelu

Metoda nejmenších čtverců je matematický postup pro konstrukci lineární rovnice, která by nejpřesněji odpovídala sadě dvou řad čísel. Účelem použití této metody je minimalizovat celkovou kvadratickou chybu. Program Excel má nástroje, se kterými lze tuto metodu aplikovat při výpočtu. Pojďme se zabývat tím, jak se děje.

Použití metody v aplikaci Excel

Metoda nejmenších čtverců (MNC) je matematický popis závislosti jedné proměnné od druhé. Lze jej použít při predikci.

Povolení doplňkového vyhledávání řešení "

Abyste mohli používat MNA v aplikaci Excel, musíte povolit doplněk "Vyhledávání řešení", která je ve výchozím nastavení zakázána.

1. Přejděte na kartu "Soubor".



2. Klikněte na název "Parametry".



3. V okně, které se otevře, zastavte výběr na pododdílu "Doplňky".



4. V bloku "Management", který je umístěn v dolní části okna, nastavte přepínač do polohy "Excel Add-in" (pokud je nastavena jiná hodnota) a klikněte na tlačítko "Go ...".



5. Otevře se malé okno. Dali jsme do něj klíště v blízkosti parametru "řešení pro řešení". Klikněte na tlačítko "OK".

Funkce pro řešení funkce v aplikaci Excel je nyní aktivována a jeho nástroje se objevily na pásce.

Lekce: Vyhledávání řešení v Excelu

Podmínky problému

Popisujeme použití MNK na konkrétním příkladu. Máme dvě řádky čísel X. a y. Sekvence je uvedena v obrázku níže.

Nejpřesněji daná závislost může popisovat funkci:

y = a + nx

Zároveň je známo, že kdy x = 0. y. Rovné rovné 0 . Tato rovnice proto může být popsána závislostí y = nx..

Musíme najít minimální součet čtverců rozdílu.

Řešení

Obraťme se na popis přímého použití metody.

1. Vlevo od prvního významu X. dát simístný 1. . Bude to přibližná hodnota první hodnoty koeficientu N..



2. Vpravo od sloupu y. Přidat další sloupec - NX. V první buňce tohoto sloupce napište násobení součinitele N. Na buňce první proměnné X. . Současně je odkaz na pole s koeficientem absolutní, protože tato hodnota se nezmění. Klikněte na tlačítko Enter.



3. Použití značky výplně, zkopírujte tento vzorec do celého rozsahu tabulky ve sloupci níže.



4. V samostatné buňce vypočte součet rozdílu v čtvercích hodnot y. a Nx. . Chcete-li to provést, klikněte na tlačítko "Insert funkce".



5. V "Master of Funkce", který se otevírá, hledám záznam "Summkvson". Vyberte ji a klikněte na tlačítko "OK".



6. Otevře se okno argumentu. V poli "Massive_x" vstupujeme do rozsahu kolonových buněk y. . V poli "Massive_y" vstoupíme do řady kolonových buněk Nx. . Chcete-li zadat hodnoty, jednoduše nainstalujte kurzor do pole a vyberte vhodný rozsah na listu. Po zadání stiskněte tlačítko "OK".



7. Přejděte na kartu "Data". Na pásku v bloku nástroje "Analýza" klikneme na tlačítko "Vyhledávání řešení".



8. Otevře se okno parametrů. V poli "Optimalizace cílové funkce" zadejte adresu buňky se vzorcem "Summkvson". V parametru "BC" nastavte přepínač do polohy "Minimální". V poli "Změna buňky" zadejte adresu s hodnotou koeficientu N. . Klikněte na tlačítko "Najít řešení".



9. Řešení se zobrazí v koeficientu buňce N. . Tato hodnota bude nejmenší čtverec funkce. Pokud výsledek vyhovuje uživateli, klepněte na tlačítko "OK" v opci.

Jak vidíte, použití nejméně čtvercové metody metody je poměrně komplikovaný matematický postup. Ukázali jsme to v akci na nejjednodušším příkladu a existují mnohem složitější případy. Toolkit Microsoft Excel je však navržen tak, aby zjednodušilo výpočty.