Excel

Použití metody nejmenších čtverců v aplikaci Excel

Metoda nejmenších čtverců je matematický postup pro konstrukci lineární rovnice, která by přesně odpovídala množině dvou sérií čísel. Účelem této metody je minimalizovat celkovou kvadratickou chybu. V aplikaci Excel existují nástroje, s nimiž můžete tuto metodu použít ve svých výpočtech. Podívejme se, jak se to dělá.

Použití metody v aplikaci Excel

Metoda nejmenších čtverců (OLS) je matematický popis závislosti jedné proměnné na druhé. Může být použita při prognózování.

Povolení doplňku najít řešení

Chcete-li použít nástroj OLS v aplikaci Excel, je třeba povolit doplněk "Najít řešení", který je ve výchozím nastavení zakázán.

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

    2. Přejděte na kartu Soubor v aplikaci Microsoft Excel

  2. Klepněte na název oddílu "Parametry" .

    3. Přejděte na možnosti v aplikaci Microsoft Excel

  3. V okně, které se otevře, zastavíme výběr podčásti "Doplňky" .

    4. Přejděte na podčást doplňku v aplikaci Microsoft Excel

  4. V bloku "Management" , který se nachází v dolní části okna, nastavte přepínač na pozici "Excel Add-In" (pokud má jinou hodnotu) a klikněte na tlačítko "Go ..." .

    5. Přejít na doplňky aplikace Excel v aplikaci Microsoft Excel

  5. Otevře se malé okno. Vložili jsme zaškrtnutí parametru "Hledání řešení" . Klikněte na tlačítko "OK" .

Povolení hledání řešení v aplikaci Microsoft Excel

Nyní je funkce Find řešení v aplikaci Excel povolena a její nástroje se zobrazí na pásu karet.

Lekce: Hledání řešení v aplikaci Excel

Pracovní podmínky

Popište použití aplikace OLS na konkrétním příkladu. Máme dva řady čísel x a y , jejichž posloupnost je zobrazena na následujícím obrázku.

Proměnné v aplikaci Microsoft Excel

Funkci lze nejlépe popsat tuto závislost:

y=a+nx

V tomto případě je známo, že pro x = 0, y je také 0 . Proto lze tuto rovnici popsat závislosti y = nx .

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

Řešení

Pojďme nyní popsat přímou aplikaci metody.

  1. Vlevo od první hodnoty x nastavíme číslo 1 . Toto je přibližná hodnota první hodnoty koeficientu n .

    2. Hodnota koeficientu n v aplikaci Microsoft Excel

  2. Vpravo od sloupce y přidejte další sloupec - nx . V první buňce tohoto sloupce zapíšeme vzorec pro vynásobení koeficientu n buňkou první proměnné x . V tomto případě je provedena vazba na pole s koeficientem absolutní , protože tato hodnota se nezmění. Klikněte na tlačítko Enter .

    3. Hodnota nx v aplikaci Microsoft Excel

  3. Použití vyplňte značku , zkopírujte tento vzorec do celého rozsahu tabulky ve sloupci níže.

    4. Kopírování vzorce do aplikace Microsoft Excel

  4. V samostatné buňce vypočítat součet rozdílů čtverců hodnot y a nx . Chcete-li to provést, klepněte na tlačítko "Vložit funkci" .

    5. Přejděte na Průvodce funkcí v aplikaci Microsoft Excel

  5. V otevřeném "Průvodci funkcí" hledáme položku "SUMMKVRAZN" . Vyberte jej a klikněte na tlačítko "OK" .

    6. Průvodce funkcí v aplikaci Microsoft Excel

  6. Otevře okno s argumenty. V poli "Array_x" zadáme rozsah buněk sloupce y . V poli "Array_y" zadáme rozsah buněk sloupce nx . Pro zadání hodnot jednoduše umístěte kurzor do pole a vyberte příslušný rozsah na listu. Po zadání klikněte na tlačítko "OK" .

    7. Zadání argumentů funkce v aplikaci Microsoft Excel

  7. Přejděte na kartu "Data" . Na pásu karet v panelu nástrojů "Analýza" klikněte na tlačítko "Najít řešení" .

    8. Přejděte do vyhledávacího řešení v aplikaci Microsoft Excel

  8. Otevře se okno parametrů tohoto nástroje. V poli "Optimalizovat cílovou funkci" určíme adresu buňky pomocí vzorce "SUMMKVRAZN" . V parametru "Na" musíme přepínač nastavit do polohy "Minimální" . V poli "Změna buněk" specifikujeme adresu s hodnotou koeficientu n . Klikněte na tlačítko "Najít řešení" .

    9. Nalezení řešení nejmenších čtverců v aplikaci Microsoft Excel

  9. Řešení se zobrazí v buňce koeficientu n . Tato hodnota bude nejmenším čtvercem funkce. Pokud výsledek uspokojí uživatele, klikněte na tlačítko "OK" v dalším okně.

Potvrďte výsledek v aplikaci Microsoft Excel

Jak vidíme, aplikace metody nejmenších čtverců 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. Nástrojová sada Microsoft Excel je však navržena tak, aby co nejvíce zjednodušila výpočty.