Metoda najmniejszych kwadratów w Excelu

Metoda najmniejszych kwadratów jest procedurą matematyczną do budowy równania liniowego, który najbardziej odpowiadałby zestawowi dwóch rzędów liczb. Celem korzystania z tej metody jest zminimalizowanie całkowitego błędu kwadratowego. Program Excel ma narzędzia, z którymi ten sposób można zastosować przy obliczaniu. Zajmijmy się tym, jak się skończy.

Korzystanie z metody w programie Excel

Metoda najmniejszej kwadratu (MNC) jest opisem matematycznym zależności jednej zmiennej od drugiego. Może być używany podczas przewidywania.

Włączanie dodatku "Szukaj rozwiązania"

Aby użyć MNA w Excelu, musisz włączyć dodatek "Szukaj rozwiązania", który jest domyślnie wyłączony.

1. Przejdź do zakładki "Plik".



2. Kliknij sekcję Nazwa "Parametry".



3. W oknie, które otwierają się, zatrzymaj wybór na podsekcji "Dodawanie".



4. W bloku "Zarządzanie", który znajduje się na dole okna, ustaw przełącznik do pozycji "Excel Add-in" (jeśli zostanie ustawiona kolejna wartość) i kliknij przycisk "GO ...".



5. Otworzy się małe okno. Wkładamy to kleszcz w pobliżu parametru "Szukaj Rozwiązania". Kliknij przycisk "OK".

Teraz aktywowany jest funkcja funkcji rozwiązania w programie Excel, a jego narzędzia pojawiły się na taśmie.

Lekcja: Wyszukaj rozwiązania w Excele

Warunki problemu

Opisujemy użycie MNK na konkretnym przykładzie. Mamy dwa rzędy liczb X. oraz y. Sekwencja, której jest przedstawiona na obrazku poniżej.

Najdokładniej dana zależność może opisać funkcję:

y = a + nx

Jednocześnie wiadomo, że kiedy x = 0. y. Również równy 0 . Dlatego to równanie można opisać przez uzależnienie y = nx..

Musimy znaleźć minimalną sumę kwadratów różnicy.

Rozwiązanie

Odwróćmy się do opisu bezpośredniego zastosowania metody.

1. Po lewej stronie pierwszego znaczenia X. umieść cyfrę 1. . Będzie to przybliżona wartość pierwszej wartości współczynnika N..



2. Po prawej stronie kolumny y. Dodaj kolejną kolumnę - NX. W pierwszej komórce tej kolumny wpisz mnożenie formuły współczynnika N. na komórce pierwszej zmiennej X. . Jednocześnie link do pola ze współczynnikiem jest absolutny, ponieważ ta wartość się nie zmieni. Kliknij przycisk Enter.



3. Korzystanie z markera wypełnienia, skopiuj ten wzór do całego zakresu tabeli w kolumnie poniżej.



4. W oddzielnej komórce oblicz sumę różnicy w kwadratach wartości y. oraz Nx. . Aby to zrobić, kliknij przycisk "Wstaw funkcję".



5. W "Master of Funkcje" otwiera się, szukam rekordu "Sumkvson". Wybierz go i kliknij przycisk "OK".



6. Otworzy się okno argumentu. W polu "Massive_x" wprowadzamy zakres komórek kolumnowych y. . W polu "Massive_y" wchodzimy do zakresu komórek kolumnowych Nx. . Aby wprowadzić wartości, wystarczy zainstalować kursor w polu i wybierz odpowiedni zakres na arkuszu. Po wejściu naciśnij przycisk "OK".



7. Przejdź do zakładki "Data". Na taśmie w bloku narzędzi "Analiza" kliknij przycisk "Rozwiązanie Szukaj".



8. Otworzy się okno parametrów. W polu "Optymalizuj funkcję docelowej", określ adres komórki za pomocą formuły "Sumkvson". W parametrze "BC" należy ustawić przełącznik na pozycję "minimalną". W polu "Zmiana komórki" określa adres o wartości współczynnika N. . Kliknij przycisk "Znajdź rozwiązanie".



9. Rozwiązanie zostanie wyświetlone w komórce współczynnika N. . Ta wartość będzie najmniejszym kwadratem funkcji. Jeśli wynik spełnia użytkownika, a następnie kliknij przycisk "OK" w opcji.

Jak widać, zastosowanie metody najmniejszej metody kwadratowej jest raczej skomplikowaną procedurą matematyczną. Pokazaliśmy go w działaniu na najprostszym przykładzie, a istnieje znacznie bardziej złożone przypadki. Jednak narzędzie Microsoft Excel ma na celu uproszczenie obliczeń.