vynikať

Použitie metódy najmenších štvorcov v programe Excel

Metóda najmenších štvorcov je matematický postup pre zostavenie lineárnej rovnice, ktorá by najpresnejšie odpovedala množine dvoch sérií čísel. Účelom tejto metódy je minimalizovať celkovú kvadratickú chybu. V programe Excel existujú nástroje, s ktorými môžete použiť túto metódu vo svojich výpočtoch. Pozrime sa, ako sa to deje.

Použitie metódy v programe Excel

Metóda najmenších štvorcov (OLS) je matematický popis závislosti jednej premennej na druhej. Môže sa použiť pri prognózovaní.

Povolenie doplnku Nájsť riešenie

Ak chcete používať nástroj OLS v programe Excel, musíte povoliť predvolené nastavenie doplnku "Nájsť riešenie" .

  1. Prejdite na kartu "Súbor" .

    2. Prejdite na kartu Súbor v programe Microsoft Excel

  2. Klikneme na názov sekcie Parametre .

    3. Prejdite na možnosti v programe Microsoft Excel

  3. V okne, ktoré sa otvorí, zastavíme výber podsekcie "Doplnky" .

    4. Prejdite do podsekcie doplnku v programe Microsoft Excel

  4. V bloku "Správa" , ktorý sa nachádza v spodnej časti okna, prepnite prepínač na pozíciu "Doplnky programu Excel" (ak má inú hodnotu) a kliknite na tlačidlo "Prejsť ..." .

    5. Prejdite do doplnkov programu Excel v programe Microsoft Excel

  5. Otvorí sa malé okno. Dali sme do nej začiarknutie parametra "Hľadanie riešení" . Kliknite na tlačidlo "OK" .

Povolenie vyhľadávania riešení v programe Microsoft Excel

Teraz je funkcia Hľadanie riešenia v programe Excel povolená a jej nástroje sa zobrazia na páse.

Lekcia: Vyhľadávanie riešení v programe Excel

Podmienky úlohy

Opíšte aplikáciu OLS na konkrétnom príklade. Máme dva riadky číslic x a y , ktorých sekvencia je zobrazená na obrázku nižšie.

Premenné v programe Microsoft Excel

Funkcia môže najlepšie opísať túto závislosť:

y=a+nx

V tomto prípade je známe, že pre x = 0, y je tiež 0 . Preto táto rovnica môže byť opísaná závislosťou y = nx .

Musíme nájsť minimálny súčet štvorcov rozdielu.

Riešenie

Teraz popíšeme priamu aplikáciu metódy.

  1. Naľavo od prvej hodnoty x zadáme číslo 1 . Toto je približná hodnota prvej hodnoty koeficientu n .

    2. Hodnota koeficientu n v programe Microsoft Excel

  2. Vpravo od stĺpca y pridajte ešte jeden stĺpec - nx . V prvej bunke tohto stĺpca zapíšeme vzorec na vynásobenie koeficientu n bunkou prvej premennej x . Súčasne sa vykoná prepojenie na pole s koeficientom absolútny , pretože táto hodnota sa nezmení. Kliknite na tlačidlo Enter .

    3. Hodnota nx v programe Microsoft Excel

  3. použitím vyplniť značku , skopírujte tento vzorec do celého rozsahu tabuľky v stĺpci nižšie.

    4. Kopírovanie vzorca do programu Microsoft Excel

  4. V samostatnej bunke vypočítajte súčet rozdielov štvorcov hodnôt y a nx . Ak to chcete urobiť, kliknite na tlačidlo "Vložiť funkciu" .

    5. Prejdite na sprievodcu funkciami v programe Microsoft Excel

  5. V otvorenom "Sprievodcovi funkcií" hľadáme položku "SUMMKVRAZN" . Vyberte ho a kliknite na tlačidlo "OK" .

    6. Sprievodca funkciami v programe Microsoft Excel

  6. Otvorí okno s argumentmi. V poli "Array_x" zadáme rozsah buniek stĺpca y . V poli "Array_y" zadáme rozsah buniek stĺpca nx . Aby ste zadali hodnoty, jednoducho umiestnite kurzor do poľa a vyberte príslušný rozsah na hárku. Po zadaní kliknite na tlačidlo "OK" .

    7. Zadávanie argumentov funkcie v programe Microsoft Excel

  7. Prejdite na kartu "Dáta" . Na páse krabice v nástrojovej lište "Analýza" kliknite na tlačidlo "Nájsť riešenie" .

    8. Prejdite do vyhľadávania riešení v programe Microsoft Excel

  8. Otvorí sa okno s parametrami pre tento nástroj. V poli "Optimalizovať cieľovú funkciu" zadáme adresu bunky pomocou vzorca "SUMMKVRAZN" . V parametri "To" musíme nastaviť prepínač na pozíciu "Minimum" . V poli "Zmena buniek" určujeme adresu s hodnotou koeficientu n . Kliknite na tlačidlo "Nájsť riešenie" .

    9. Hľadanie riešenia s najmenším štvorcom v programe Microsoft Excel

  9. Riešenie sa zobrazí v bunke koeficientu n . Táto hodnota bude najmenším štvorcom funkcie. Ak výsledok uspokojuje používateľa, kliknite na tlačidlo "OK" v dodatočnom okne.

Potvrďte výsledok v programe Microsoft Excel

Ako vidíme, aplikácia metódy najmenších štvorcov je pomerne komplikovaný matematický postup. Ukázali sme to v akcii na najjednoduchšom príklade a existujú oveľa komplikovanejšie prípady. Súbor nástrojov programu Microsoft Excel je však navrhnutý tak, aby čo najviac zjednodušil výpočty.