Zastosowanie metody najmniejszych kwadratów w programie Excel
Metoda najmniejszych kwadratów jest matematyczną procedurą konstruowania równania liniowego, które najdokładniej odpowiadałoby zestawowi dwóch serii liczb. Celem tej metody jest minimalizacja całkowitego błędu kwadratowego. W programie Excel są narzędzia, za pomocą których można zastosować tę metodę w swoich obliczeniach. Zobaczmy, jak to się robi.
Spis treści
Korzystanie z metody w programie Excel
Metoda najmniejszych kwadratów (OLS) to matematyczny opis zależności jednej zmiennej od drugiej. Może być stosowany w prognozowaniu.
Włączanie rozwiązania Add-on Znajdź
Aby korzystać z OLS w Excelu, musisz włączyć dodatek "Znajdź rozwiązanie", który jest domyślnie wyłączony.
- Przejdź do zakładki "Plik" .
- Klikamy na nazwę sekcji "Parametry" .
- W oknie, które się otworzy, przestajemy wybierać podsekcję "Dodatki" .
- W bloku "Zarządzanie" , który znajduje się w dolnej części okna, ustaw przełącznik na pozycję "Dodatki Excel" (jeśli ma inną wartość) i kliknij przycisk "Przejdź ..." .
- Otworzy się małe okno. Umieściliśmy w nim znacznik wyboru parametru "Wyszukiwanie rozwiązań" . Kliknij przycisk "OK" .
Teraz funkcja Znajdź rozwiązanie w Excelu jest włączona, a jej narzędzia pojawiają się na wstążce.
Lekcja: Wyszukiwanie rozwiązań w Excelu
Warunki zadania
Opiszmy zastosowanie OLS na konkretnym przykładzie. Mamy dwa rzędy liczb x i y , których kolejność pokazano na poniższym obrazku.
Funkcja najlepiej opisuje tę zależność:
y=a+nx
W tym przypadku wiadomo, że dla x = 0, y również wynosi 0 . Dlatego równanie to można opisać zależnością y = nx .
Musimy znaleźć minimalną sumę kwadratów różnicy.
Rozwiązanie
Opiszmy teraz bezpośrednie zastosowanie tej metody.
- Na lewo od pierwszej wartości x umieszczamy cyfrę 1 . Jest to przybliżona wartość pierwszej wartości współczynnika n .
- Po prawej stronie kolumny y dodaj jeszcze jedną kolumnę - nx . W pierwszej komórce tej kolumny zapisujemy wzór na przemnożenie współczynnika n przez komórkę pierwszej zmiennej x . W tym przypadku tworzy się łącze do pola ze współczynnikiem absolutny , ponieważ ta wartość się nie zmieni. Kliknij przycisk Enter .
- Używanie wypełnij znacznik , skopiuj tę formułę do całego zakresu tabeli w kolumnie poniżej.
- W oddzielnej komórce obliczyć sumę różnic kwadratów wartości y i nx . Aby to zrobić, kliknij przycisk "Wstaw funkcję" .
- W otwartym "Kreatorze funkcji" szukamy wpisu "SUMMKVRAZN" . Wybierz go i kliknij przycisk "OK" .
- Otwiera okno argumentów. W polu "Array_x" wpisujemy zakres komórek kolumny y . W polu "Array_y" wpisujemy zakres komórek kolumny nx . Aby wprowadzić wartości, po prostu umieść kursor w polu i wybierz odpowiedni zakres na arkuszu. Po wprowadzeniu kliknij przycisk "OK" .
- Przejdź do zakładki "Dane" . Na wstążce w przyborniku "Analiza" kliknij przycisk "Znajdź rozwiązanie" .
- Otworzy się okno parametrów tego narzędzia. W polu "Optymalizuj funkcję celu", podajemy adres komórki z formułą "SUMMKVRAZN" . W parametrze "Do" musimy ustawić przełącznik w pozycji "Minimum" . W polu "Zmiana komórek" podajemy adres z wartością współczynnika n . Kliknij przycisk "Znajdź rozwiązanie" .
- Rozwiązanie zostanie wyświetlone w komórce o współczynniku n . Jest to ta wartość, która będzie najmniejszym kwadratem funkcji. Jeśli wynik jest zadowalający dla użytkownika, kliknij przycisk "OK" w dodatkowym oknie.
Jak widzimy, zastosowanie metody najmniejszych kwadratów jest dość skomplikowaną procedurą matematyczną. Pokazaliśmy to w akcji na najprostszym przykładzie i jest dużo bardziej skomplikowanych przypadków. Jednak zestaw narzędzi Microsoft Excel został zaprojektowany tak, aby uprościć obliczenia w jak największym stopniu.