Metoda najmniejszych kwadratów w programie Excel. Analiza regresji

Metoda najmniejszych kwadratów (MNC) należy do dziedziny analizy regresji. Ma wiele aplikacji, ponieważ pozwala na przybliżoną reprezentację danej funkcji przez inne, prostsze. MNC może być bardzo przydatna w przetwarzaniu obserwacji i jest ona aktywnie wykorzystywana do oceny niektórych zmiennych dla innych pomiarów zawierających błędy. Z tego artykułu dowiesz się, jak zaimplementować obliczenia najmniejszych kwadratów w programie Excel.

Problem konkretny przykład

Załóżmy, że istnieją dwa wskaźnik X i Y. A Y zależy od X. Ponieważ MNC interesuje nas w zakresie metod analizy regresji w Excel wprowadziły go przy użyciu wbudowanych funkcji), należy natychmiast tak samo idą do rozważenia konkretnego zadania.


, - X więc spożywczy obszaru magazynu handlu, mierzoną w metrach, a Y - roczne obrót, określona milionów rubli. Trzeba dokonać prognozy, że obrót (Y) będzie rozejrzeć się, jeśli on lub inna powierzchnia handlowa. Oczywiście, funkcja y = f (x) rośnie od hipermarketu sprzedaje więcej towarów niż przeciągnięciem.

słów o poprawności danych źródłowych stosować do przewidywania

Załóżmy, że mamy tabeli zbudowanej według n sklepów.

X



x-1



x 2







Xn



r



r 1



y 2







in

Zgodniestatystyka matematyczna, wyniki będą mniej więcej poprawne, jeśli badane są dane dotyczące co najmniej 5-6 obiektów. Ponadto nie można użyć "nieprawidłowych" wyników. W szczególności elitarny mały butik może mieć obroty przekraczające czasami obrót dużych sklepów detalicznych klasy "rynek masowy".


Sposób

Poniższe tabele można wyciągnąć w kartezjańskim płaszczyźnie co wskazuje M 1 (x, y 1 1), m (n x n, y n). Sprowadzona na rozwiązanie problemu wyboru funkcji przybliżenie y = f (x), która ma rozkład, który jest tak blisko jak to możliwe M punktów 1M 2 Mn. Oczywiście, można użyć wielomianem wysokim stopniu, ale opcja ta jest nie tylko trudnorealyzuem, ale po prostu błędne, ponieważ nie odzwierciedlają główny trend, który również trzeba odkryć. Najbardziej rozsądnym rozwiązaniem jest znalezienie prostej y = ax + b, która jest najbliższa dane doświadczalne, bardziej precyzyjnie, współczynniki a, b.

Oszacowanie dokładności

W każdym przybliżeniu ocena jego dokładności staje się szczególnie ważna. E i oznacza różnicę (odchylenie) między wartości funkcjonalnych i doświadczalnych dla punktów x i, czyli E i = y I - F (xi). Oczywiście, w celu oceny dokładności aproksymacji mogą korzystać ilość odchyleń, że w wyborze żyć przybliżoną reprezentację X zależy od Y powinny dać pierwszeństwo do tego, który ma najmniejszą wartość sumy E i we wszystkich lokalizacjach. Jednak nie wszystko jest takie proste, ponieważ wraz z dodatnimi odchyleniami będą praktycznie negatywne. Możesz rozwiązać to pytanie za pomocąmoduły odchyłek lub ich kwadraty. Ostatnia metoda otrzymała najbardziej rozpowszechnioną. Jest wykorzystywany w wielu obszarach, w tym w analizie regresji w Excelu, jego implementacja odbywa się za pomocą dwóch wbudowanych funkcji) i od dawna udowadnia swoją skuteczność.

Metoda najmniejszych kwadratów

Excel, jak wiadomo, ma wbudowaną funkcję automatycznego sumowania, która pozwala obliczyć wartości wszystkich wartości znajdujących się w wybranym zakresie. Nic nie stoi więc na przeszkodzie, aby obliczać wartość wyrażenia (e 1 2 + e 2 2 + e 3 2 + e n 2). W zapisie matematycznym ma postać:
Ponieważ pierwotnie podjęto decyzję o przybliżeniu za pomocą linii prostej, mamy:
W związku z tym problem znalezienia prostej, która najlepiej opisuje specyficzną zależność wielkości X i Y, zmniejsza się do obliczenia minimalnej funkcji dwóch zmiennych:
W tym celu musimy zrównoważyć częściowe pochodne cząstkowe nowych zmiennych aib do zera i rozwiązać prymitywny układ składający się z dwóch równań dwóch nieznanych gatunków:
Po prostych przekształceniach, w tym dzieleniu na 2 i manipulowaniu sumami, uzyskujemy:
Rozwiązując to, na przykład, metodą Cramera, uzyskujemy punkt stacjonarny o pewnych współczynnikach a * i b *. Jest to minimum, czyli przewidywanie, jaki będzie obrót towarowy w sklepie na określonym obszarze, zbliża się linia prosta y = a * x + b *, która jest modelem regresji dla danego przykładu. Oczywiście nie pozwoli ci znaleźć dokładnego wyniku, ale pomoże ci zorientować się, czy chcesz kupićobszar kredytowy.

Jak zaimplementować metodę najmniejszych kwadratów w Excelu

W Excelu istnieje funkcja do obliczania wartości dla MNC. Ma następującą postać: "TREND" (od wartości Y, wartość X, nowa wartość to X, const.). Zastosuj wzór do obliczania MNC w tabeli Excel. Aby to zrobić, w komórce, w której wynik obliczenia za pomocą metody najmniejszych kwadratów powinien zostać wyświetlony program Excel, wprowadzamy znak "=" i wybieramy funkcję "TREND". W otwartym oknie wypełnij odpowiednie pola, podświetlając:
  • zakres znanych wartości dla Y (w tym przypadku dane dotyczące obrotów);
  • zakres x 1, x n, czyli wielkość przestrzeni handlowej;
  • znane, nieznane wartości x, dla których konieczne jest ustalenie wielkości obrotu (informacje o ich położeniu na arkuszu, patrz poniżej).
  • Ponadto we wzorze występuje logiczna zmienna "Stała". Jeśli wprowadzisz go w odpowiednim polu 1 będzie to oznaczać, że należy dokonać obliczeń, zważywszy, że b = 0.
    Jeśli chcesz poznać prognozę pogody dla więcej niż jednej wartości x, a następnie po wprowadzeniu formuły nie powinien kliknąć na «Enter», oraz konieczność pisania na kombinację klawiszy Shift, Ctrl + «»»+«Enter»(«Enter»).

    W niektórych Opis

    Analiza regresji można uzyskać nawet zaślepki. Formuła programu Excel do przewidywania wartości tablicy nieznanych zmiennych - "TREND" - może być używana nawet przez osoby, które nigdy nie słyszały o metodzie najmniejszych kwadratów. Wystarczy znać niektóre cechy jej pracy. W szczególności:
  • Jeśli ustawisz zakres znanych wartości zmiennej y w jednym wierszu lub kolumnie, toKażdy wiersz (kolumna) o znanych wartościach x będzie odbierany przez program jako jedna zmienna.
  • Jeżeli skrzynka „tendencji” nie podano szereg znanych x, w przypadku funkcji w programie Excel traktować go jako matrycy zawierającej liczby całkowite, których liczba odpowiada zakresie podanych wartości zmiennych y.
  • W przypadku układu odbiorczego „przewidywane wartości oblicza tendencji ekspresyjny powinien być wprowadzone jako preparat tablicy.
  • Jeżeli nie podano nowe wartości x, wówczas funkcja „Trends” uważało równie znanych. Jeśli nie są podane, to tablica jest traktowana jako argument 1; 2; 3; 4, który jest mierzalnym zakresem z już ustawionymi parametrami y.
  • ,
  • , w przedziale zawierającym się nowe wartości x, powinien się składać z takich samych lub większej liczby wierszy i kolumn w zakresie z góry określonej wartości y. Innymi słowy, powinny to być odpowiednie zmienne niezależne.
  • Tablica znanych wartości x może zawierać kilka zmiennych. Jeśli jednak jest to około 1, zakresy z podanymi wartościami x i y powinny być proporcjonalne. W przypadku kilku zmiennych muszą mieścić się w zakresie od podanych wartości y mieści się na jednej linii i w jednej linii.
  • Funkcja PROGNOZY

    Analiza regresji w programie Excel jest realizowana z kilkoma funkcjami. Jedna z nich nosi nazwę "PROGNOZA". Jest podobny do "TRENDÓW", to znaczy, że daje wynik obliczeń za pomocą metody najmniejszych kwadratów. Jednak tylko jeden X, dla których nieznana wartość Y. Teraz, gdy wiesz formułę w Excelu dla opornych, pozwalającprzewidzieć wartość przyszłej wartości jednego lub drugiego wskaźnika zgodnego z trendem liniowym.

    Powiązane publikacje