Metoda najmniejszych kwadratów i wyszukiwania rozwiązania w programie Excel. Zastosowanie metody najmniejszych kwadratów w obliczeniu Excel metodą najmniejszych kwadratów XLS

Cóż, w pracy Przed zgłoszonym inspekcją artykuł w domu na konferencję jest napisany - możesz teraz pisać na blogu. Dopóki te dane przetworzone, zdałem sobie sprawę, że nie mogłem pisać o bardzo fajnej i niezbędnej nadbudowy w Excelu, który jest nazywany. Dlatego artykuł zostanie poświęcony tej nadbudowie, a powiem ci o przykładzie używania metoda najmniejsze kwadraty (MNC) Aby wyszukać nieznane współczynniki równania przy opisywaniu danych eksperymentalnych.

Jak włączyć dodatek "Szukaj rozwiązania"

Najpierw zrozumiemy, jak włączyć tę nadbudowę.

1. Przejdź do menu "Plik" i wybierz element Ustawienia Excel.

2. W wyświetlonym oknie wybierz "Rozwiązanie Rozwiązanie" i kliknij "Idź".

3. W następnym oknie umieścimy kleszcz naprzeciwko elementu "Szukaj Rozwiązania" i kliknij OK.

4. Dodatek jest aktywowany - Teraz można go znaleźć w pozycji menu "Data".

Metoda najmniej kwadratowa

Teraz krótko O. metoda najmniejszych kwadratów (MNC) I o tym, gdzie można zastosować.

Załóżmy, że mamy zestaw danych po dokonaniu pewnego doświadczenia, gdzie studiowaliśmy skutki wartości wartości Y.

Chcemy, aby ten efekt opisać matematycznie, aby użyć tej formuły do \u200b\u200bużycia i wiedzieć, że jeśli tak bardzo zmienimy ilość X, otrzymujemy wartość Y ...

Wezmę super prosty przykład (patrz rys.).

Jest oczywiste, że punkty znajdują się dla siebie jak w linii prostej, a zatem odważnie zakładamy, że nasza zależność jest opisana przez funkcję liniową Y \u003d KX + B. Jednocześnie jesteśmy pewni, że z X jest równy zero, wartość Y ma również zero. Dlatego funkcja opisująca uzależnienie będzie jeszcze łatwiejsze: Y \u003d KX (pamiętaj o programie szkolnym).

Ogólnie rzecz biorąc, musimy znaleźć współczynnik k. To właśnie zrobimy MNA. za pomocą dodatkowego rozwiązania dla rozwiązań.

Metoda jest taka (tutaj - uwaga: musisz myśleć) suma kwadratów różnicy w uzyskaniu eksperymentalnie, a odpowiednie wartości obliczeniowe były minimalne. To jest, gdy X1 \u003d 1, faktycznie zmierzona wartość Y1 \u003d 4.6, a szacowany Y1 \u003d F (X1) wynosi 4, kwadrat różnicy woli (Y1-Y1) ^ 2 \u003d (4-4.6) ^ 2 \u003d 0,36. W taki sam sposób: Gdy X2 \u003d 2, faktycznie zmierzona wartość Y2 \u003d 8.1, a obliczona U2 wynosi 8, kwadrat różnicy będzie (Y2-Y2) ^ 2 \u003d (8-8,1) ^ 2 \u003d 0,01. A suma wszystkich tych kwadratów powinna być możliwa minimalna.

Więc przejdziemy do szkolenia na temat korzystania z MNK i dodatek Excel "Rozwiązanie Szukaj" .

Wyszukiwanie rozwiązań aplikacji aplikacji

1. Jeśli nie włączyłeś dodatku "Szukaj rozwiązania", wracamy do elementu Jak włączyć dodatkowe rozwiązanie "rozwiązanie" i włączyć 🙂

2. W komórce A1 przedstawiamy wartość "1". Ta jednostka będzie pierwszym przybliżeniem do rzeczywistej wartości współczynnika (k) naszej zależności funkcjonalnej Y \u003d KX.

3. W kolumnie B rozliczaliśmy wartości parametru X, w kolumnie C - wartości parametru Y. W komórek kolumn wprowadzamy wzór: "Ceffitt K pomnożone na wartość x. Na przykład, w komórce D1, wchodzimy "\u003d A1 * B1", w komórce D2, w której wejdziemy "\u003d A1 * B2" itp.

4. Wierzymy, że współczynnik do równy jedności I funkcja f (x) \u003d y \u003d 1 * x jest pierwszym przybliżeniem do naszego rozwiązania. Możemy obliczyć sumę kwadratów różnic między zmierzonymi wartościami wartości Y i obliczoną zgodnie z wzorem Y \u003d 1 * x. Możemy zrobić wszystko ręcznie, napędzane w wzorze odpowiednie powiązania do komórek: "\u003d (D2-C2) ^ 2 + (D3-C3) ^ 2 + (D4-C4) ^ 2 ... itp Koniec mylimy się i rozumiemy, że stracili kilka czasu. W programie Excel istnieje specjalny formuła do obliczania suma kwadratów różnic, które zostaną wprowadzone do komórki A2 i ustawić dane źródłowe: zakres mierzony Wartości y (kolumna C) i zakres obliczonych wartości Y (kolumna d).

4. Obliczono sumę różnicy w kwadratach - teraz przejdziemy do zakładki "Data" i wybierz "Szukaj rozwiązania".

5. W menu pojawiają się jako zmienna komórka, wybierz komórkę A1 (ten z współczynnikiem K).

6. W celu uzyskania celu wybierz komórkę A2 i ustaw warunek "ustanowić równą wartość minimalną". Pamiętaj, że jest to komórka, w której dokonujemy obliczania sumy kwadratów różnic między obliczonymi i mierzonymi wartościami, a ilość powinna być minimalna. Kliknij "Uruchom".

7. Wybrano współczynnik K. Teraz możesz upewnić się, że obliczone wartości są teraz bardzo blisko mierzonego.

Str.s.

Oczywiście, oczywiście, w przypadku przybliżenia danych eksperymentalnych w programie Excel znajdują się specjalne narzędzia, które umożliwiają opisanie danych za pomocą funkcji liniowej, wykładniczej, mocy i wielomianowej, dzięki czemu często można zrobić bez dotyk "Rozwiązanie rozwiązania". Opowiedziałem o wszystkich tych metod przybliżenia na swój sposób, więc jeśli się zastanawiam, spójrz. Ale jeśli chodzi o jakąś egzotyczną funkcję z jednym nieznanym współczynnikiem lub optymalizacja zadań, a następnie tutaj nadbudowa Tak jak przy okazji niemożliwe.

Dodaj "Szukaj rozwiązania" Można również użyć do innych zadań, główną rzeczą jest zrozumienie esencji: Istnieje komórka, w której wybieramy wartość, ale jest komórka docelowa, w której warunek jest określony dla wyboru nieznanego parametru.
To wszystko! W następnym artykule powiem ci bajkę o wakacjach, więc nie chronić produkcji artykułu,

Metoda najmniej kwadratowa Służy do oszacowania parametrów, równania regresji.

Jedną z metod studiowania stochastycznych więzi między znakami jest analiza regresji.
Analiza regresji Jest to wyjście równania regresji, z którymi znajduje się średnia wartość zmiennej losowej (wyników znakowa), jeśli znana jest wielkość kolejnych (lub innych) zmiennych (czynników). Obejmuje następujące kroki:

wybór formularza komunikacji (rodzaj równania regresji analitycznej);
oszacować parametry równania;
ocena jakości równania regresji analitycznej.

Najczęściej forma liniowa służy do opisania statystycznego połączenia znaków. Uwaga K. podłączenie liniowe. Wyjaśnia się przez jasną interpretację ekonomiczną jego parametrów ograniczonych odmian zmiennych i w większości przypadków, nieliniowe formy komunikacji dla obliczeń są konwertowane (przez logarytming lub zastępowanie zmiennych) do formularza liniowego.
W przypadku wiązania pary liniowej równanie regresji weźmie formularz: y i \u003d a + b · x i + u ja. Parametry tego równania A i B są oceniane według obserwacja statystyczna x i y. Wynikiem takiej oceny jest równanie:, gdzie - szacunki parametrów A i B, - wartość wynikowej funkcji (zmienna) uzyskana przez równanie regresji (wartość obliczona).

Najczęściej oszacować użycie parametrów metoda najmniejszych kwadratów (MNC).
Metoda najmniejszych kwadratów zapewnia najlepsze (zamożne, wydajne i odblokowane) szacunki parametrów równania regresji. Ale tylko wtedy, gdy pewne warunki wstępne są wykonywane w stosunku do losowego terminu (U) i niezależnej zmiennej (X) (patrz tła MNC).

Problem oceny parametrów równania pary liniowej przez metodę najmniejszych kwadratów Składa się na następujący sposób: Aby uzyskać takie szacunki parametrów, w którym suma kwadratów odchyleń rzeczywistych wartości efektywnego znaku - Y i na obliczonych wartości jest minimalna.
Formalnie kryterium MNK. Możesz napisać tak: .

Klasyfikacja metod najmniejszych kwadratów

Najmniejsza metoda kwadrata.
Maksymalna prawdomówna metoda (dla normalnego klasycznego modelu regresji liniowej, normalność pozostałości regresji jest przełożona).
Uogólniona metoda mniejszych kwadratów OMNA stosuje się w przypadku autokorelacji błędów oraz w przypadku heterosdastyczności.
Sposób zawieszonych najmniejszych kwadratów (specjalny przypadek OMNA z pozostałościami heter-visasic).

Zilustrujemy istotę klasyczna najmniejsza metoda kwadratowa graficznie. Aby to zrobić, budujemy harmonogram punktu zgodnie z obserwacjami (X I, Y i, I \u003d 1; n) w prostokątnym układzie współrzędnych (taki wykres punktowy nazywa się polem korelacji). Spróbujemy wybrać linię prostą, która jest najbliższa punktom pola korelacji. Zgodnie z metodą najmniejszych kwadratów linia jest wybrana tak, że suma kwadratów odległości pionowych między punktami pola korelacji a ta linia byłaby minimalna.

Rekord matematyczny tego zadania: .
Wartości Y I i X I \u003d 1 ... n są nam znane, są to dane obserwacyjne. W funkcji są stałymi. Zmienne W tej funkcji są pożądane szacunki parametrów - ,. Aby znaleźć minimum funkcji 2-zmiennej, konieczne jest obliczenie prywatnych pochodnych tych funkcji dla każdego z parametrów i zrównują je zero, tj. .
W rezultacie otrzymujemy system 2 normalnych równania liniowe.:
Rozwiązywanie tego systemu, znajdziemy pożądane szacunki parametrów:

Poprawność obliczenia parametrów równania regresji może być testowana przez porównanie kwot (być może pewna rozbieżność z powodu obliczeń zaokrągleń).
Aby obliczyć szacunki parametrów, możesz zbudować tabelę 1.
Znak współczynnika regresji wskazuje kierunek komunikacyjny (jeśli b\u003e 0, linia jest bezpośredni, jeśli b<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Formalnie wartość parametru A jest średnia wartość Y z X równą zero. Jeśli signator nie ma i nie może mieć wartości zerowej, następnie powyższej interpretacji parametru i nie ma sensu.

Oszacowanie szczelności komunikacji między znakami Jest przeprowadzany przy użyciu współczynnika korelacji pary liniowej - r x, y. Można obliczyć o wzorze: . Ponadto współczynnik liniowej korelacji parowania można określić za pomocą współczynnika regresji B: .
Obszar dopuszczalnych wartości współczynnika liniowego korelacji pary z -1 do +1. Znak współczynnika korelacji wskazuje kierunek komunikacji. Jeśli r x, y\u003e 0, połączenie jest proste; Jeśli r x, y<0, то связь обратная.
Jeśli ten współczynnik jest blisko jednego, połączenie między cechami można interpretować jako całkiem blisko liniowy. Jeśli jego moduł jest równy jednostce ê r x, y ê \u003d 1, połączenie między objawami jest funkcjonalna liniowa. Jeśli znaki X i Y są liniowo niezależne, wtedy R X, Y jest blisko 0.
Aby obliczyć R X, Y może również użyć tabeli 1.

Aby ocenić jakość uzyskanej równania regresji, obliczany jest teoretyczny współczynnik określony - R 2 YX:

,
gdzie D2 jest dyspersją Y; wyjaśniono przez równanie regresji;
e 2 - resztkowe (niewyjaśnione równanie regresji) Dyspersja Y;
s 2 Y to całkowita (kompletna) dyspersja Y.
Współczynnik determinacji charakteryzuje proporcję zmienności (dyspersji) efektywnego znaku Y, wyjaśnione przez regresję (a w związku z tym czynnikiem X) w ogólnej wariacji (dyspersji) y. Współczynnik determinacji R 2 YX ma odpowiednio wartości od 0 do 1. Wartość 1-R2 YX charakteryzuje się ułamek dyspersji Y spowodowanej wpływem innych nieustanowanych czynników w modelu i błędach specyfikacji.
Dzięki sparowanej regresji liniowej R 2 YX \u003d R 2 YX.

4.1. Przy użyciu wbudowanych funkcji

Obliczenie współczynniki recesji. przeprowadzone za pomocą funkcji

Linene(Wartości_y.; Wartości_x.; Konst; statystyka),

Wartości_y. - tablica wartości Y

Wartości_x.- Opcjonalna tablica wartości x.Jeśli tablica h. pominięto, zakłada się, że ta tablica (1; 2; 3; ...) o tym samym rozmiarze Wartości_y.,

Konst- wartość logiczna wskazuje, czy jest to wymagane do stałego b. był równy 0. Jeśli Konst ma znaczenie PRAWDZIWE lub pominięty wtedy b. Jest obliczany w zwykły sposób. Jeśli argument. Konst Wtedy ma kłamstwo b. opiera się równe 0 i wartości zA. wybrany tak, że stosunek y \u003d topór.

Statystyka- wartość logiczna wskazująca, czy zwrócić dodatkowe statystyki regresji. Jeśli argument. Statystyka ma znaczenie PRAWDZIWE, a następnie funkcjonuj Linene Zwraca dodatkowe statystyki regresji. Jeśli argument. Statystyka ma znaczenie FAŁSZYWE lub pominięty, a następnie funkcja Linene Zwraca tylko współczynnik zA. i stała b..

Należy pamiętać, że wynik funkcji Linene ()to wiele wartości - tablicy.

Do obliczenia współczynnik korelacji Używana funkcja

Dereń(Masywny1.;Masywny2.),

zwracanie wartości współczynnika korelacji Masywny1. - tablica wartości y., Masywny2. - tablica wartości x.. Masywny1. i Masywny2. Musi być jednym wymiarem.

Przykład 1.. Uzależnienie y.(x.) Przedstawione w tabeli. Budować linia regresji i oblicz współczynnik korelacji.

Y.		0.5		1.5		2.5		3.5
X.		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

Wprowadzamy tabelę wartości do arkusza Excel MS i budujemy harmonogram punktów. Lista robocza weźmie pogląd przedstawiony na FIG. 2.

W celu obliczenia wartości współczynników regresji alei b.najważniejsze A7: B7, Zwróć się do mistrza funkcji iw kategorii Statystyczny Wybierz funkcję Linene. Wypełnij pojawione okno dialogowe, jak pokazano na rys. 3 i kliknij DOBRZE..

W rezultacie obliczona wartość pojawi się tylko w komórce A6. (Rys.4). Aby wartość pojawić się w komórce B6. Musisz wprowadzić tryb edycji (klucz F2)a następnie naciśnij kombinację klawisza Ctrl + Shift + Enter.

Aby obliczyć wartość współczynnika korelacji w komórce C6. Wprowadzono następujący formula:

C7 \u003d Corvela (B3: J3; B2: J2).

Znając współczynniki regresji alei b. Oblicz wartości funkcji y.=tOPÓR.+b. Na określony x.. Aby to zrobić, przedstawiamy formułę

B5 \u003d $ A $ 7 * B2 + $ B 7 $

i skopiuj go w zakresie C5: J5.(Rys. 5).

Wykazam linię regresji na diagramie. Podświetl punkty eksperymentalne na wykresie, kliknij prawym przyciskiem myszy i wybierz polecenie Wstępne dane. W wyświetlonym oknie dialogowym (rys. 5) wybierz kartę Rząd i kliknij przycisk Dodaj. Wypełnij pola wejściowe, jak pokazano na FIG. 6 i naciśnij przycisk dobrze. Linia regresji zostanie dodana do harmonogramu danych eksperymentalnych. Domyślnie jego harmonogram zostanie przedstawiony w postaci punktów nie podłączonych przez linie wygładzające.

Figa. 6.

Aby zmienić typ linii regresji, wykonaj następujące kroki. Kliknij prawym przyciskiem myszy Punkty przedstawiające harmonogram linii, wybierz polecenie Rodzaj diagramu.i ustaw typ diagramu punktu, jak pokazano na rys. 7.

Typ linii, jego kolor i grubość można zmienić w następujący sposób. Wybierz linię na diagramie, kliknij prawym przyciskiem myszy i wybierz polecenie w menu kontekstowym Format serii danych ... Następnie wykonaj instalacje, na przykład, jak pokazano na rys. osiem.

W wyniku wszystkich transformacji otrzymujemy wykres eksperymentalnych danych i linii regresji w jednym obszarze graficznym (rys. 9).

4.2. Za pomocą linii trendu.

Budowa różnych przybliżonych zależności w MS Excel jest wdrażana jako właściwości wykresu - linia trendu..

Przykład 2.. W wyniku eksperymentu określono pewną zależność tabelarycznej.

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

Wybierz i zbuduj uzależnienie przybliżające. Zbuduj grafikę tabelaryczną i wybraną zależność analityczną.

Rozwiązywanie problemów można podzielić na następujące kroki: Wprowadzanie danych źródłowych, budując harmonogram punktów i dodanie linii trendu do tej grafiki.

Rozważ ten proces szczegółowo. Przedstawiamy dane źródłowe do arkusza roboczego i skonstruować wykres danych eksperymentalnych. Następnie wybierz punkty eksperymentalne na wykresie, kliknij prawym przyciskiem myszy i użyj polecenia Dodajl. trend i trend. (Rys. 10).

Okno dialogowe, które pojawiają się pozwala na zbudowanie przybliżonej zależności.

Na pierwszej karcie wskazano typ przybliżonej zależności.

Na drugim (rys. 12) określa parametry konstrukcji:

· Nazwa przybliżonej zależności;

· Prognoza do przodu (z powrotem) n. Jednostki (ten parametr określa liczbę jednostek naprzód (z powrotem) musi przedłużyć linię trendu);

· Pokaż, czy punkt przecięcia krzywej z linią prostą y \u003d const.;

· Pokaż funkcję przybliżoną na diagramie lub nie (parametr, aby pokazać równanie na diagramie);

· Czy diagram odchylenia standardowego lub nie (parametr jest umieszczony na diagramie wartości dokładności przybliżenia).

Wybieramy wielomian drugiego stopnia jako przybliżonej zależności (rys. 11) i usunąć równanie opisujące ten wielomian na wykresie (rys. 12). Uzyskany diagram jest prezentowany na FIG. 13.

Podobnie z pomocą linie trendów Możesz wybrać parametry takich zależności

· Liniowy y.=a ∙ X.+b.,

· Logarytmiczny. y.=a ∙ ln.(x.)+b.,

· Wykonywanie y.=a ∙ e b,

· Moc y.=a ∙ x b,

· Wielomian y.=a ∙ X. 2 +b ∙ x.+dO., y.=a ∙ X. 3 +b ∙ x. 2 +c ∙ x + d I tak dalej, do szóstego stopnia wielomicznego włącznie,

· Filtrowanie liniowe.

4.3. Korzystanie z decydującego bloku

Znaczące zainteresowanie jest wdrażaniem parametrów w MS Excel przy użyciu mniejszych kwadratów za pomocą decydującego bloku. Ta technika umożliwia wybranie parametrów dowolnego rodzaju funkcji. Rozważ tę funkcję na przykładzie następującego zadania.

Przykład 3.. W wyniku eksperymentu zależność z (t) przedstawiona w tabeli

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

Podnieś współczynniki uzależnienia Z (t) \u003d przy 4 + bt 3 + CT 2 + DT + K Metoda najmniejszych kwadratów.

To zadanie jest równoważne zadaniu znalezienia minimalnej funkcji pięciu zmiennych.

Rozważ proces rozwiązywania problemu optymalizacyjnego (rys. 14).

Niech znaczenia ALE, W, Z, RE. i DO Przechowywane w komórkach A7: E7.. Oblicz teoretyczne wartości funkcji Z.(t.)=W 4 + BT 3 + CT 2 + DT + K Na określony t.(B2: J2.). Zrobić to w komórce B4. Wprowadzamy wartość funkcji w pierwszym punkcie (komórka B2.):

B4 \u003d $ A $ 7 * B2 ^ 4 + $ B $ 7 * B2 ^ 3 + $ C $ 7 * B2 ^ 2 + $ D $ 7 * B2 + $ E $ 7.

Skopiuj ten wzór w zakresie C4: J4. I otrzymujemy oczekiwaną wartość funkcji w punktach, których odcięcie są przechowywane w komórkach B2: J2..

W komórce. B5. Wprowadzamy formułę, która oblicza kwadrat różnicy między punktami eksperymentalnymi i obliczonymi:

B5 \u003d (B4-B3) ^ 2,

i skopiuj go w zakresie C5: J5.. W komórce. F7. Będziemy przechowywać całkowity błąd kwadratowy (10). Aby to zrobić, przedstawiamy formułę:

F7 \u003d kwoty (B5: J5).

Używamy zespołu Service®POISK Solutions. i rozwiązać problem optymalizacji bez ograniczeń. Wypełnij odpowiednie pola wejściowe w oknie dialogowym pokazanym na FIG. 14 i naciśnij przycisk Wykonać. Jeśli zostanie znaleziony roztwór, okno pokazane na rys. piętnaście.

Wynikiem decydującego bloku będzie wnioskiem w komórce A7: E7.wartości parametrów Funkcje Z.(t.)=W 4 + BT 3 + CT 2 + DT + K. W komórkach B4: J4. Otrzymać oczekiwana funkcja wartości W punktach początkowych. W komórce. F7. zostanie zapisany całkowity błąd kwadratowy..

Wytwarzanie punktów eksperymentalnych i wybrana linia w jednym obszarze graficznym może być przeznaczona na zasięg B2: J4.Połączenie Master Chart.A następnie formatuj pojawienie się otrzymanych wykresów.

Figa. 17 Wyświetla listę roboczy MS Excel po wykonaniu obliczeń.

5. Lista referencji

1. Alekseev E.r., Chesnokova O.v., rozwiązywanie problemów matematyki obliczeniowej w pakietach MATHCAD12, MATLAB7, Maple9. - NT Press, 2006.-596C. : Il. - (instruktaż)

2. Alekseev E.r., Chesnova O.v., E.a. Rudchenko, Scylab, Rozwiązywanie inżynierii i zadań matematycznych. -M., Binom, 2008.-260s.

3. Berezin I.S., Lodine N.P., Metody obliczeń. - M.: Science, 1966.-632C.

4. Garnaev a.yu., używając MS Excel i VBA w ekonomii i finansach. - SPB.: BHV - Petersburg, 1999.-332C.

5. Demidovich B.P., Maron i A. Shuvalova V.Z., numeryczne metody analizy. - M.: Science, 1967.-368С.

6. Kukurydza G., Korn T., katalog matematyki dla naukowców i inżynierów. - m., 1970, 720s.

7. Alekseev E.r., Chesnova O.v. Instrukcje metodyczne do wykonywania prac laboratoryjnych w MS Excel. Dla studentów wszystkich specjałów. Donieck, Donntu, 2004. 112 p.

Ma wiele aplikacji, ponieważ umożliwia przeprowadzenie przybliżonej reprezentacji określonej funkcji przez inne prostsze. MNA może być niezwykle przydatny w obserwacjach przetwarzania i jest aktywnie używany do oceny niektórych wartości zgodnie z wynikami pomiarów innych błędów losowych. Z tego artykułu dowiesz się, jak wdrożyć obliczenia przy użyciu metody najmniejszych kwadratów w programie Excel.

Ustawianie zadania na określonym przykładzie

Przypuśćmy, że istnieją dwa wskaźniki X i Y. Rozważanie konkretnego zadania.

Niech X będzie dzielnicą handlową sklepu spożywczego, mierzone w metrach kwadratowych, a y - roczny obrót, określony w milionach rubli.

Wymagane jest prognozę, które handel (y) będzie w sklepie, jeśli ma jedną lub inną dzielnicę handlową. Oczywiście, funkcja Y \u003d F (X) rośnie, ponieważ hipermarket sprzedaje więcej produktów niż stoisko.

Kilka słów o poprawności danych źródłowych wykorzystywanych do przewidzenia

Przypuśćmy, że mamy stół zbudowany zgodnie z danymi dla sklepów.

Zgodnie z statystykami matematycznymi wyniki będą mniej więcej poprawne, jeśli zbadano dane przez co najmniej 5-6 obiektów. Ponadto nie można użyć "anomalicznych" wyników. W szczególności, elite mały butik może mieć obrót czasami większy niż obrót handlowy dużych punktów handlowych klasy Masarket.

Istota metody

Dane tabeli można przedstawić na płaszczyźnie destyjskim w postaci punktów M 1 (x 1, Y 1), ... m n (x n, y n). Teraz rozwiązanie problemu jest zmniejszone do wyboru funkcji przybliżonej Y \u003d F (X), który ma wykres, przechodząc jak najbliżej punktów M 1, M2, .. M N.

Oczywiście możliwe jest użycie wielomialnego wysokiego stopnia, ale opcja ta jest nie tylko trudniejsza, ale jest po prostu niepoprawna, ponieważ nie odzwierciedla głównego trendu, że konieczne jest wykrycie. Najbardziej rozsądnym rozwiązaniem jest wyszukiwanie bezpośredniego y \u003d ax + b, który najlepiej przynosi dane eksperymentalne, a dokładniej, współczynniki - A i B.

Dokładność oszacowania

W każdym przybliżeniu nabywa szczególne znaczenie do oceny jego dokładności. Oznacz e I Różnica (odchylenie) między wartościami funkcjonalnymi i doświadczalnymi dla punktu x i, tj. E i \u003d y i - f (x i).

Jest oczywiste, że ilość odchyleń może być wykorzystana do oszacowania dokładności przybliżenia, tj. Po wybraniu linii prostej dla przybliżonej reprezentacji X z Y, musisz preferować tę najmniejszą wartość kwoty EI w rozważane wszystkie punkty. Jednak nie wszystko jest takie proste, ponieważ wraz z pozytywnymi odchyleniami będzie prawie obecne i negatywne.

Możesz rozwiązać problem za pomocą modułów odchyleń lub ich kwadratów. Ostatnia metoda była szeroko rozpowszechniana. Jest używany w wielu obszarach, w tym analiza regresji (w programie Excel jego implementacja jest przeprowadzana przy użyciu dwóch wbudowanych funkcji) i długo okazała się jego skuteczność.

Metoda najmniej kwadratowa

W programie Excel, jak wiadomo, istnieje wbudowana funkcja autoSummy, która umożliwia obliczenie wartości wszystkich wartości znajdujących się w dedykowanym zakresie. W ten sposób nic nie przeszkadza nam obliczyć wartość ekspresji (E 1 2 + E 22 + E 3 2 + ... E N2).

W rekordzie matematycznej ma formularz:

Ponieważ pierwotnie zdecydowano się zbliżyć się z linią prostą, mamy:

Tak więc zadanie znalezienia linii prostej, która najlepiej opisuje specyficzną zależność wartości X i Y, zmniejsza się do obliczenia minimalnej funkcji dwóch zmiennych:

Aby to zrobić, konieczne jest równe zerowe prywatne pochodne zgodnie z nowymi zmiennymi A i B i rozwiązują system prymitywny składający się z dwóch równań z 2 nieznanym gatunkiem:

Po prostych transformacjach, w tym podziału na 2 i manipulowanie z sumami, otrzymujemy:

Rozwiązywanie go na przykład przez urządzenie, otrzymujemy stacjonarny punkt z pewnymi współczynnikami A * i B *. Jest to minimum, czyli w celu przewidywania, który handel będzie w sklepie na określonym obszarze, odpowiednią linię Y \u003d A * X + B * jest odpowiednie, co jest modelem regresji na przykład, który jest omawiany. Oczywiście nie pozwoli na znalezienie dokładnego wyniku, ale pomoże uzyskać pomysł, czy zakup zostanie zakupiony betonowego obszaru.

Jak wdrożyć metodę najmniejszych kwadratów w programie Excel

W "Excel" istnieje funkcja obliczania wartości MNA. Posiada następujący formularz: "Trend" (znany. Wartości y; znane. Wartości x; nowe wartości x; Const.). Zastosuj formułę obliczeniową MNC w programie Excel do naszego stołu.

Aby to zrobić, w komórce, w której wynik obliczenia mniejszych kwadratów należy wyświetlić w programie Excel, wprowadzamy znak "\u003d" i wybierz funkcję "Trend". W otwartym oknie wypełnij odpowiednie pola, podświetlanie:

zakres znanych wartości dla y (w tym przypadku, dane do obrotów);
zakres x 1, ... x N, tj. Wartości przestrzeni handlowej;
i znane i nieznane wartości x, dla których konieczne jest, aby dowiedzieć się o rozmiarze obrotu (aby uzyskać informacje o ich lokalizacji w arkuszu, patrz poniżej).

Ponadto formuła ma zmienną logiczną "Const". Jeśli wejdziesz do odpowiedniego pola 1, oznacza to, że należy wykonać obliczenia, wierząc, że b \u003d 0.

Jeśli chcesz nauczyć się prognozy na więcej niż jedną wartość x, a następnie po wejściu do formuły, nie należy klikać na "Enter", ale musisz wybrać połączenie "SHIFT" + "Control" + "Enter" na stronie klawiatura.

Niektóre funkcje

Analiza regresji może być dostępna nawet do czajników. Formuła Excel do przewidywania wartości tablicy nieznanych zmiennych - "Trend" - nawet ci, którzy nigdy nie słyszeli o najmniejszej metodę najmniejszych kwadratów. Wystarczy poznać pewne cechy swojej pracy. W szczególności:

Jeśli zorganizujesz zakres znanych wartości zmiennej Y w jednym rzędzie lub kolumnie, każda linia (kolumna) o znanych wartościach X zostanie postrzegana przez program jako oddzielna zmienna.
Jeśli okno "Trend" nie określa zakresu znanego X, a następnie w przypadku korzystania z funkcji w programie Excel, program rozważy go jako tablicę składającą się z liczb całkowitych, z których liczba odpowiada zakresowi o określonych wartościach Zmiennej y.
Aby uzyskać tablicę wartości "przewidywanych" na wyjściu, wyrażenie obliczania trendu musi być podawane jako formuła tablicy.
Jeśli nowe wartości X nie są określone, funkcja "Trend" uważa je za równą słynnym. Jeśli nie są określone, wkład 1 jest traktowany jako argument; 2; 3; 4; ..., który jest współmierny z zakresem z już określonymi parametrami Y.
Zakres zawierający nowe wartości X powinien składać się z tych samych lub więcej wierszy lub kolumn jako zakresu o określonych wartościach Y. Innymi słowy, musi być proporcjonalny do zmiennych niezależnych.
W tablicy znanymi wartościami X może być zawarte kilka zmiennych. Jeśli jednak rozmawiamy tylko o jednym, wymagane jest, aby zakresy z określonymi wartościami X i Y są proporcjonalne. W przypadku kilku zmiennych konieczne jest, aby zakres z określonymi wartościami mieszanki Y w jednej kolumnie lub w jednym rzędzie.

Funkcja Prediczy

Zaimplementowane za pomocą wielu funkcji. Jeden z nich jest nazywany "przewidywaniem". Jest podobny do "trendu", to znaczy wydawać wynik obliczeń przy użyciu metody najmniejszych kwadratów. Jednak tylko dla jednego X, dla którego Y jest nieznany.

Teraz znasz formułę w programie Excel dla czajników, umożliwiając przewidywanie wartości przyszłej wartości jednego lub innego wskaźnika zgodnie z trendem liniowym.

4.1. Przy użyciu wbudowanych funkcji

Obliczenie współczynniki recesji. przeprowadzone za pomocą funkcji

Linene(Wartości_y.; Wartości_x.; Konst; statystyka),

Wartości_y. - tablica wartości Y

Wartości_x.- Opcjonalna tablica wartości x.Jeśli tablica h. pominięto, zakłada się, że ta tablica (1; 2; 3; ...) o tym samym rozmiarze Wartości_y.,

Należy pamiętać, że wynik funkcji Linene ()to wiele wartości - tablicy.

Do obliczenia współczynnik korelacji Używana funkcja

Dereń(Masywny1.;Masywny2.),

zwracanie wartości współczynnika korelacji Masywny1. - tablica wartości y., Masywny2. - tablica wartości x.. Masywny1. i Masywny2. Musi być jednym wymiarem.

Przykład 1.. Uzależnienie y.(x.) Przedstawione w tabeli. Budować linia regresji i oblicz współczynnik korelacji.

Y.		0.5		1.5		2.5		3.5
X.		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

Wprowadzamy tabelę wartości do arkusza Excel MS i budujemy harmonogram punktów. Lista robocza weźmie pogląd przedstawiony na FIG. 2.

Aby obliczyć wartość współczynnika korelacji w komórce C6. Wprowadzono następujący formula:

C7 \u003d Corvela (B3: J3; B2: J2).

Znając współczynniki regresji alei b. Oblicz wartości funkcji y.=tOPÓR.+b. Na określony x.. Aby to zrobić, przedstawiamy formułę

B5 \u003d $ A $ 7 * B2 + $ B 7 $

i skopiuj go w zakresie C5: J5.(Rys. 5).

W wyniku wszystkich transformacji otrzymujemy wykres eksperymentalnych danych i linii regresji w jednym obszarze graficznym (rys. 9).

4.2. Za pomocą linii trendu.

Budowa różnych przybliżonych zależności w MS Excel jest wdrażana jako właściwości wykresu - linia trendu..

Przykład 2.. W wyniku eksperymentu określono pewną zależność tabelarycznej.

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

Wybierz i zbuduj uzależnienie przybliżające. Zbuduj grafikę tabelaryczną i wybraną zależność analityczną.

Rozwiązywanie problemów można podzielić na następujące kroki: Wprowadzanie danych źródłowych, budując harmonogram punktów i dodanie linii trendu do tej grafiki.

Okno dialogowe, które pojawiają się pozwala na zbudowanie przybliżonej zależności.

Na pierwszej karcie wskazano typ przybliżonej zależności.

Na drugim (rys. 12) określa parametry konstrukcji:

· Nazwa przybliżonej zależności;

· Prognoza do przodu (z powrotem) n. Jednostki (ten parametr określa liczbę jednostek naprzód (z powrotem) musi przedłużyć linię trendu);

· Pokaż, czy punkt przecięcia krzywej z linią prostą y \u003d const.;

· Pokaż funkcję przybliżoną na diagramie lub nie (parametr, aby pokazać równanie na diagramie);

· Czy diagram odchylenia standardowego lub nie (parametr jest umieszczony na diagramie wartości dokładności przybliżenia).

Wybieramy wielomian drugiego stopnia jako przybliżonej zależności (rys. 11) i usunąć równanie opisujące ten wielomian na wykresie (rys. 12). Uzyskany diagram jest prezentowany na FIG. 13.

Podobnie z pomocą linie trendów Możesz wybrać parametry takich zależności

· Liniowy y.=a ∙ X.+b.,

· Logarytmiczny. y.=a ∙ ln.(x.)+b.,

· Wykonywanie y.=a ∙ e b,

· Moc y.=a ∙ x b,

· Wielomian y.=a ∙ X. 2 +b ∙ x.+dO., y.=a ∙ X. 3 +b ∙ x. 2 +c ∙ x + d I tak dalej, do szóstego stopnia wielomicznego włącznie,

· Filtrowanie liniowe.

4.3. Korzystanie z narzędzia do analizy opcji: Szukaj rozwiązania.

Istotne zainteresowanie jest znaczącym zainteresowaniem MS Excel wybierając parametry zależności funkcjonalnej przez najmniejsze kwadraty przy użyciu narzędzia do analizy opcji: rozwiązanie roztworu. Ta technika umożliwia wybranie parametrów dowolnego rodzaju funkcji. Rozważ tę funkcję na przykładzie następującego zadania.

Przykład 3.. W wyniku eksperymentu zależność z (t) przedstawiona w tabeli

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

Podnieś współczynniki uzależnienia Z (t) \u003d przy 4 + bt 3 + CT 2 + DT + K Metoda najmniejszych kwadratów.

To zadanie jest równoważne zadaniu znalezienia minimalnej funkcji pięciu zmiennych.

Rozważ proces rozwiązywania problemu optymalizacyjnego (rys. 14).

B4 \u003d $ A $ 7 * B2 ^ 4 + $ B $ 7 * B2 ^ 3 + $ C $ 7 * B2 ^ 2 + $ D $ 7 * B2 + $ E $ 7.

Skopiuj ten wzór w zakresie C4: J4. I otrzymujemy oczekiwaną wartość funkcji w punktach, których odcięcie są przechowywane w komórkach B2: J2..

W komórce. B5. Wprowadzamy formułę, która oblicza kwadrat różnicy między punktami eksperymentalnymi i obliczonymi:

B5 \u003d (B4-B3) ^ 2,

i skopiuj go w zakresie C5: J5.. W komórce. F7. Będziemy przechowywać całkowity błąd kwadratowy (10). Aby to zrobić, przedstawiamy formułę:

F7 \u003d kwoty (B5: J5).

Figa. 17 Wyświetla listę roboczy MS Excel po wykonaniu obliczeń.