Aproksymacja danych eksperymentalnych to metoda polegająca na zastąpieniu danych uzyskanych eksperymentalnie funkcją analityczną, która najbardziej przechodzi lub pokrywa się w punktach węzłowych z wartościami pierwotnymi (dane uzyskane podczas eksperymentu lub eksperymentu). Obecnie istnieją dwa sposoby definiowania funkcji analitycznej:
Konstruując n-stopniowy wielomian interpolacyjny, który przechodzi bezpośrednio przez wszystkie punkty daną tablicę danych. W w tym przypadku funkcja aproksymująca jest reprezentowana jako: wielomian interpolacyjny w postaci Lagrange'a lub wielomian interpolacyjny w postaci Newtona.
Konstruując n-stopniowy wielomian aproksymujący, który przechodzi w bezpośrednim sąsiedztwie punktów z danej tablicy danych. W ten sposób funkcja aproksymująca wygładza wszystkie losowe szumy (lub błędy), które mogą pojawić się podczas eksperymentu: zmierzone wartości podczas eksperymentu zależą od czynników losowych, które zmieniają się zgodnie z ich własnymi przypadkowe prawa(błędy pomiaru lub przyrządu, niedokładność lub błędy eksperymentalne). W tym przypadku funkcję aproksymującą wyznacza się metodą najmniejszych kwadratów.
Metoda najmniejszych kwadratów(w literaturze angielskiej Ordinary Least Squares, OLS) to metoda matematyczna polegająca na wyznaczeniu funkcji aproksymującej, która jest konstruowana w najbliższej odległości od punktów z danego układu danych eksperymentalnych. Zbliżenie funkcji pierwotnej i aproksymującej F(x) wyznacza się za pomocą miary numerycznej, a mianowicie: suma kwadratów odchyleń danych eksperymentalnych od krzywej aproksymującej F(x) powinna być najmniejsza.
Krzywa przybliżająca zbudowana metodą najmniejszych kwadratów
Stosuje się metodę najmniejszych kwadratów:
Rozwiązywanie nadokreślonych układów równań, gdy liczba równań przekracza liczbę niewiadomych;
Aby znaleźć rozwiązanie w przypadku zwykłego (nie zastąpionego) systemy nieliniowe równania;
Aby przybliżyć wartości punktowe za pomocą pewnej funkcji aproksymującej.
Funkcję aproksymującą metodą najmniejszych kwadratów wyznacza się z warunku minimalnej sumy kwadratów odchyleń obliczonej funkcji aproksymującej z zadanego układu danych eksperymentalnych. To kryterium metody najmniejszych kwadratów zapisuje się jako następujące wyrażenie:
Wartości obliczonej funkcji aproksymującej w punktach węzłowych,
Dana tablica danych eksperymentalnych w punktach węzłowych.
Kryterium kwadratowe ma wiele „dobrych” właściwości, takich jak różniczkowalność, zapewniając unikalne rozwiązanie problemu aproksymacji za pomocą wielomianowych funkcji aproksymujących.
W zależności od warunków zadania funkcją aproksymującą jest wielomian stopnia m
Stopień funkcji aproksymującej nie zależy od liczby punktów węzłowych, jednak jej wymiar musi być zawsze mniejszy od wymiaru (liczby punktów) danego układu danych eksperymentalnych.
∙ Jeżeli stopień funkcji aproksymującej wynosi m=1, to funkcję tabelaryczną aproksymujemy linią prostą (regresja liniowa).
∙ Jeżeli stopień funkcji aproksymującej wynosi m=2, to aproksymujemy funkcję tablicową parabola kwadratowa(przybliżenie kwadratowe).
∙ Jeżeli stopień funkcji aproksymującej wynosi m=3, to funkcję tablicową aproksymujemy parabolą sześcienną (aproksymacja sześcienna).
W przypadek ogólny gdy konieczne jest skonstruowanie wielomianu aproksymującego stopnia m dla danych wartości tabeli, warunek na minimalną sumę kwadratów odchyleń we wszystkich punktach węzłowych przepisuje się w postaci:
- nieznane współczynniki wielomianu aproksymującego stopnia m;
Liczba określonych wartości tabeli.
Warunkiem koniecznym istnienia minimum funkcji jest równość jej pochodnych cząstkowych względem nieznanych zmiennych do zera . W rezultacie otrzymujemy następujący system równania:
Przekształćmy wynik układ liniowy równania: otwórz nawiasy i przesuń wolne wyrazy na prawą stronę wyrażenia. Powstały układ liniowy wyrażenia algebraiczne zostanie zapisany w następującej formie:
Ten system liniowych wyrażeń algebraicznych można zapisać w postaci macierzowej:
W rezultacie powstał system równania liniowe wymiar m+1, który składa się z m+1 niewiadomych. Układ ten można rozwiązać dowolną metodą rozwiązywania problemów liniowych. równania algebraiczne(na przykład metodą Gaussa). W wyniku rozwiązania zostaną znalezione nieznane parametry funkcji aproksymującej, które dają minimalną sumę kwadratów odchyleń funkcji aproksymującej od danych pierwotnych, tj. najlepsze możliwe przybliżenie kwadratowe. Należy pamiętać, że jeśli zmieni się chociaż jedna wartość danych źródłowych, wszystkie współczynniki zmienią swoje wartości, ponieważ są one całkowicie zdeterminowane przez dane źródłowe.
Aproksymacja danych źródłowych metodą zależności liniowej
(regresja liniowa)
Jako przykład rozważ technikę wyznaczania funkcji aproksymującej podaną w formie zależność liniowa. Zgodnie z metodą najmniejszych kwadratów warunek na minimum sumy kwadratów odchyleń zapisuje się w postaci:
Współrzędne węzłów tabeli;
Nieznane współczynniki funkcji aproksymującej, która jest określona jako zależność liniowa.
Warunkiem koniecznym istnienia minimum funkcji jest równość jej pochodnych cząstkowych względem nieznanych zmiennych do zera. W efekcie otrzymujemy następujący układ równań:
Przekształćmy powstały liniowy układ równań.
Rozwiązujemy powstały układ równań liniowych. Współczynniki funkcji aproksymującej w postaci analitycznej wyznacza się następująco (metoda Cramera):
Współczynniki te zapewniają konstrukcję liniowej funkcji aproksymującej zgodnie z kryterium minimalizacji sumy kwadratów funkcji aproksymującej z zadanych wartości tabelarycznych (dane eksperymentalne).
Algorytm implementacji metody najmniejszych kwadratów
1. Dane wyjściowe:
Określono tablicę danych eksperymentalnych z liczbą pomiarów N
Określany jest stopień wielomianu aproksymującego (m).
2. Algorytm obliczeniowy:
2.1. Współczynniki wyznaczane są do budowy układu równań z wymiarami
Współczynniki układu równań ( lewa strona równania)
- indeks numeru kolumny macierzy kwadratowej układu równań
Terminy swobodne układu równań liniowych ( prawa część równania)
- indeks numeru wiersza macierzy kwadratowej układu równań
2.2. Tworzenie układu równań liniowych o wymiarze.
2.3. Rozwiązywanie układu równań liniowych w celu wyznaczenia nieznanych współczynników wielomianu aproksymującego stopnia m.
2.4 Wyznaczanie sumy kwadratów odchyleń aproksymującego wielomianu od wartości pierwotnych we wszystkich punktach węzłowych
Znaleziona wartość sumy kwadratów odchyleń jest minimalną możliwą wartością.
Aproksymacja z wykorzystaniem innych funkcji
Należy zaznaczyć, że przy aproksymacji danych źródłowych metodą najmniejszych kwadratów czasami jako funkcję aproksymującą wykorzystuje się funkcję logarytmiczną, funkcja wykładnicza i funkcję potęgową.
Przybliżenie logarytmiczne
Rozważmy przypadek, gdy funkcję aproksymującą podaje funkcja logarytmiczna o postaci:
Istotą metody najmniejszych kwadratów jest w znalezieniu parametrów modelu trendu, który najlepiej opisuje tendencję rozwoju dowolnego zjawiska losowego w czasie lub przestrzeni (trend to linia charakteryzująca tendencję tego rozwoju). Zadanie metody najmniejszych kwadratów (LSM) sprowadza się do znalezienia nie tylko jakiegoś modelu trendu, ale znalezienia modelu najlepszego, czyli optymalnego. Model ten będzie optymalny, jeśli suma kwadratów odchyleń pomiędzy obserwowanymi wartościami rzeczywistymi a odpowiadającymi im obliczonymi wartościami trendu będzie minimalna (najmniejsza):
Gdzie - odchylenie standardowe pomiędzy obserwowaną wartością rzeczywistą
i odpowiadająca obliczona wartość trendu,
Rzeczywista (obserwowana) wartość badanego zjawiska,
Obliczona wartość modelu trendu,
Liczba obserwacji badanego zjawiska.
MNC jest używany dość rzadko samodzielnie. Z reguły najczęściej stosuje się ją jedynie jako niezbędną technikę techniczną w badaniach korelacyjnych. Należy pamiętać, że baza informacyjna korporacji międzynarodowej może być jedynie wiarygodna szeregi statystyczne, a liczba obserwacji nie powinna być mniejsza niż 4, w przeciwnym razie procedury wygładzania OLS mogą stracić zdrowy rozsądek.
Zestaw narzędzi MNC sprowadza się do następujących procedur:
Pierwsza procedura. Okazuje się, czy w ogóle istnieje tendencja do zmiany wynikowego atrybutu w przypadku zmiany wybranego czynnika-argumentu, czyli innymi słowy, czy istnieje związek pomiędzy „ Na " I " X ».
Druga procedura. Określa się, która linia (trajektoria) najlepiej opisuje lub charakteryzuje ten trend.
Trzecia procedura.
Przykład. Załóżmy, że dysponujemy informacją o średnim plonie słonecznika w badanym gospodarstwie (tabela 9.1).
Tabela 9.1
|
Numer obserwacji |
||||||||||
|
Wydajność, c/ha |
Ponieważ poziom technologii produkcji słonecznika w naszym kraju praktycznie nie zmienił się na przestrzeni ostatnich 10 lat, oznacza to, że najwyraźniej wahania plonów w analizowanym okresie były w dużej mierze zależne od wahań warunków pogodowych i klimatycznych. Czy to naprawdę prawda?
Pierwsza procedura OLS. Testowana jest hipoteza o istnieniu trendu zmian plonów słonecznika w zależności od zmian warunków pogodowych i klimatycznych w ciągu analizowanych 10 lat.
W tym przykładzie dla „ y "wskazane jest zbieranie plonów słonecznika i dla" X » – numer roku obserwowanego w analizowanym okresie. Testowanie hipotezy o istnieniu jakiejkolwiek zależności pomiędzy „ X " I " y „można to zrobić na dwa sposoby: ręcznie i za pomocą programów komputerowych. Oczywiście, jeśli jest dostępny wyposażenie komputera ten problem rozwiązuje się sam. Aby jednak lepiej zrozumieć narzędzia MNC, wskazane jest przetestowanie hipotezy o istnieniu związku pomiędzy „ X " I " y » ręcznie, gdy pod ręką jest tylko długopis i zwykły kalkulator. W takich przypadkach hipotezę o istnieniu trendu najlepiej sprawdzić wizualnie poprzez lokalizację obrazu graficznego analizowanego szeregu dynamiki – pola korelacji:
Pole korelacji w naszym przykładzie jest umiejscowione wokół wolno rosnącej linii. To samo w sobie wskazuje na istnienie pewnego trendu w zmianach plonów słonecznika. O występowaniu jakiejkolwiek tendencji nie można mówić tylko wtedy, gdy pole korelacji ma postać koła, koła, chmury ściśle pionowej lub ściśle poziomej, albo składa się z chaotycznie rozproszonych punktów. We wszystkich pozostałych przypadkach hipoteza o istnieniu związku pomiędzy „ X " I " y " i kontynuuj badania.
Druga procedura OLS. Określa się, która linia (trajektoria) najlepiej opisuje lub charakteryzuje trend zmian plonu słonecznika w analizowanym okresie.
Jeśli dysponujesz technologią komputerową, wybór optymalnego trendu następuje automatycznie. Podczas przetwarzania ręcznego wybór optymalna funkcja odbywa się z reguły wizualnie - poprzez lokalizację pola korelacji. Oznacza to, że w zależności od rodzaju wykresu wybierane jest równanie prostej, która najlepiej pasuje do trendu empirycznego (rzeczywistej trajektorii).
Jak wiadomo, w przyrodzie istnieje ogromna różnorodność zależności funkcjonalnych, dlatego niezwykle trudno jest wizualnie przeanalizować nawet niewielką ich część. Na szczęście w rzeczywistej praktyce gospodarczej większość zależności można dość dokładnie opisać za pomocą paraboli, hiperboli lub linii prostej. Pod tym względem dzięki „ręcznej” opcji wyboru najlepszej funkcji można ograniczyć się tylko do tych trzech modeli.
|
Hiperbola: |
||
|
|
|
Parabola drugiego rzędu: 
:
Łatwo zauważyć, że w naszym przykładzie trend zmian plonów słonecznika na przestrzeni analizowanych 10 lat najlepiej charakteryzuje się linią prostą, zatem równanie regresji będzie równaniem linii prostej.
Trzecia procedura. Parametry są obliczane równanie regresji charakteryzującej daną linię, czyli innymi słowy wyznacza się wzór analityczny opisujący najlepszy model tendencja.
Znalezienie wartości parametrów równania regresji, w naszym przypadku parametrów i , jest podstawą OLS. Proces ten sprowadza się do rozwiązania układu równań normalnych.
(9.2)
Ten układ równań można dość łatwo rozwiązać metodą Gaussa. Przypomnijmy, że w wyniku rozwiązania w naszym przykładzie zostają znalezione wartości parametrów i. Zatem znalezione równanie regresji będzie miało następującą postać: 
Ma wiele zastosowań, gdyż pozwala na przybliżone przedstawienie danej funkcji za pomocą innych, prostszych. LSM może być niezwykle przydatny w przetwarzaniu obserwacji i jest aktywnie wykorzystywany do szacowania niektórych wielkości na podstawie wyników pomiarów innych zawierających błędy losowe. W tym artykule dowiesz się, jak wdrożyć obliczenia metodą najmniejszych kwadratów w programie Excel.
Sformułowanie problemu na konkretnym przykładzie
Załóżmy, że istnieją dwa wskaźniki X i Y. Co więcej, Y zależy od X. Ponieważ OLS interesuje nas z punktu widzenia analizy regresji (w Excelu jego metody są realizowane przy użyciu wbudowanych funkcji), powinniśmy od razu przejść do rozważania konkretny problem.
Niech więc X będzie powierzchnią handlową sklepu spożywczego mierzoną w metrach kwadratowych, a Y będzie rocznym obrotem mierzonym w milionach rubli.
Należy prognozować, jakie obroty (Y) będzie miał sklep, jeżeli będzie posiadał taką czy inną powierzchnię handlową. Oczywiście funkcja Y = f (X) jest rosnąca, ponieważ hipermarket sprzedaje więcej towarów niż stragan.
Kilka słów o poprawności danych wyjściowych wykorzystanych do predykcji
Załóżmy, że mamy tabelę zbudowaną przy użyciu danych dla n sklepów.
Według statystyka matematyczna, wyniki będą mniej więcej poprawne, jeśli zbadamy dane dotyczące co najmniej 5-6 obiektów. Ponadto nie można zastosować wyników „anomalnych”. W szczególności elitarny mały butik może osiągać obroty kilkakrotnie większe niż obroty dużych sklepów detalicznych klasy „masmarket”.
Istota metody
Dane tabeli można przedstawić na płaszczyźnie kartezjańskiej w postaci punktów M 1 (x 1, y 1), ... M n (x n, y n). Teraz rozwiązanie problemu sprowadzimy do wyboru funkcji aproksymującej y = f (x), która ma wykres przechodzący jak najbliżej punktów M 1, M 2, .. M n.
Oczywiście możesz użyć wielomianu wysokiego stopnia, ale ta opcja jest nie tylko trudna do wdrożenia, ale także po prostu niepoprawna, ponieważ nie będzie odzwierciedlać głównego trendu, który należy wykryć. Najrozsądniejszym rozwiązaniem jest poszukiwanie prostej y = ax + b, która najlepiej przybliża dane eksperymentalne, a dokładniej współczynniki a i b.
Ocena dokładności
Przy każdym przybliżeniu szczególne znaczenie ma ocena jego dokładności. Oznaczmy przez e i różnicę (odchylenie) między wartościami funkcjonalnymi i eksperymentalnymi dla punktu x i, tj. e i = y i - f (x i).
Oczywiście, aby ocenić dokładność aproksymacji, można skorzystać z sumy odchyleń, tj. wybierając linię prostą do przybliżonego przedstawienia zależności X od Y, należy dać pierwszeństwo tej z najmniejsza wartość sumuje e i we wszystkich rozpatrywanych punktach. Nie wszystko jest jednak takie proste, gdyż wraz z odchyleniami dodatnimi pojawią się również odchylenia ujemne.
Problem można rozwiązać za pomocą modułów odchyleń lub ich kwadratów. Ostatnia metoda jest najczęściej stosowana. Znajduje zastosowanie w wielu obszarach, m.in. w analizie regresji (realizowanej w Excelu za pomocą dwóch wbudowanych funkcji) i już dawno udowodniła swoją skuteczność.
Metoda najmniejszych kwadratów
Jak wiadomo, Excel ma wbudowaną funkcję AutoSum, która pozwala obliczyć wartości wszystkich wartości znajdujących się w wybranym zakresie. Zatem nic nie stoi na przeszkodzie, abyśmy obliczyli wartość wyrażenia (e 1 2 + e 2 2 + e 3 2 + ... e n 2).
W notacja matematyczna to wygląda jak:
Ponieważ początkowo podjęto decyzję o przybliżeniu za pomocą linii prostej, mamy:
Stąd zadanie znalezienia linii, która najlepiej opisuje konkretna zależność wielkości X i Y, sprowadza się do obliczenia minimum funkcji dwóch zmiennych:
W tym celu należy przyrównać pochodne cząstkowe nowych zmiennych a i b do zera i rozwiązać układ pierwotny składający się z dwóch równań z 2 niewiadomymi postaci:
Po kilku prostych przekształceniach, obejmujących dzielenie przez 2 i manipulację sumami, otrzymujemy:
Rozwiązując to np. metodą Cramera otrzymujemy punkt stacjonarny o określonych współczynnikach a* i b*. Jest to minimum, czyli aby przewidzieć, jakie obroty będzie miał sklep na danym obszarze, odpowiednia jest linia prosta y = a * x + b *, która jest modelem regresji dla omawianego przykładu. Oczywiście nie pozwoli Ci to znaleźć dokładnego wyniku, ale pomoże Ci zorientować się, czy zakup konkretnego obszaru na kredyt sklepowy się opłaci.
Jak zaimplementować metodę najmniejszych kwadratów w programie Excel
Excel posiada funkcję obliczania wartości metodą najmniejszych kwadratów. Ma następującą postać: „TREND” (znane wartości Y; znane wartości X; nowe wartości X; stała). Zastosujmy do naszej tabeli wzór na obliczenie OLS w Excelu.
W tym celu należy wpisać znak „=” w komórkę, w której ma wyświetlić się wynik obliczeń metodą najmniejszych kwadratów w Excelu i wybrać funkcję „TREND”. W oknie, które się otworzy, wypełnij odpowiednie pola, podkreślając:
- zakres znanych wartości dla Y (w tym przypadku dane dotyczące obrotów handlowych);
- zakres x 1 , …x n , czyli wielkość powierzchni handlowej;
- zarówno sławni, jak i nieznane wartości x, dla którego musisz dowiedzieć się o wielkości obrotu (informacje o ich lokalizacji w arkuszu znajdziesz poniżej).
Dodatkowo formuła zawiera zmienną logiczną „Const”. Jeśli w odpowiednim polu wpiszesz 1, będzie to oznaczać, że powinieneś przeprowadzić obliczenia, zakładając, że b = 0.
Jeśli chcesz poznać prognozę dla więcej niż jednej wartości x, po wprowadzeniu formuły nie powinieneś naciskać „Enter”, ale musisz wpisać na klawiaturze kombinację „Shift” + „Control” + „Enter”.
Niektóre funkcje
Analiza regresji może być dostępna nawet dla manekinów. Formuła Excela do przewidywania wartości tablicy nieznanych zmiennych – TREND – może być używana nawet przez tych, którzy nigdy nie słyszeli o metodzie najmniejszych kwadratów. Wystarczy poznać niektóre cechy jego działania. W szczególności:
- Jeżeli zakres znanych wartości zmiennej y uporządkujesz w jednym wierszu lub kolumnie, to każdy wiersz (kolumna) znane wartości x będzie traktowane przez program jako osobna zmienna.
- Jeżeli w oknie TREND nie wskazano zakresu o znanym x, to w przypadku zastosowania funkcji w programu Excela potraktuje to jako tablicę składającą się z liczb całkowitych, których liczba odpowiada zakresowi z podanymi wartościami zmiennej y.
- Aby wyprowadzić tablicę „przewidywanych” wartości, wyrażenie służące do obliczenia trendu należy wprowadzić w postaci formuły tablicowej.
- Jeśli nie zostaną określone nowe wartości x, funkcja TREND uzna je za równe znanym. Jeżeli nie są one określone, wówczas jako argument przyjmowana jest tablica 1; 2; 3; 4;…, co jest proporcjonalne do zakresu o zadanych już parametrach y.
- Zakres zawierający nowe wartości x musi mieć tyle samo lub więcej wierszy lub kolumn co zakres zawierający podane wartości y. Innymi słowy, musi być proporcjonalna do zmiennych niezależnych.
- Tablica ze znanymi wartościami x może zawierać wiele zmiennych. Jeśli jednak mówimy tylko o jednym, to wymagane jest, aby zakresy z podanymi wartościami x i y były proporcjonalne. W przypadku kilku zmiennych konieczne jest, aby zakres z podanymi wartościami y zmieścił się w jednej kolumnie lub jednym wierszu.
funkcja PRZEWIDYWANIE
Realizowane przy użyciu kilku funkcji. Jedna z nich nazywa się „PREDYKCJA”. Działa podobnie jak „TREND”, czyli podaje wynik obliczeń metodą najmniejszych kwadratów. Jednak tylko dla jednego X, dla którego wartość Y nie jest znana.
Teraz znasz formuły w Excelu dla manekinów, które pozwalają przewidzieć przyszłą wartość konkretnego wskaźnika według trendu liniowego.
Przykład.
Dane eksperymentalne dotyczące wartości zmiennych X I Na podano w tabeli.
W wyniku ich wyrównania uzyskuje się funkcję 
Za pomocą metoda najmniejszych kwadratów, aproksymuj te dane za pomocą zależności liniowej y=topór+b(znajdź parametry A I B). Dowiedz się, która z dwóch linii lepiej (w sensie metody najmniejszych kwadratów) wyrównuje dane eksperymentalne. Narysuj coś.
Istota metody najmniejszych kwadratów (LSM).
Zadanie polega na znalezieniu współczynników zależności liniowej, przy której funkcjonuje funkcja dwóch zmiennych A I B
przyjmuje najmniejszą wartość. To znaczy, dane A I B suma kwadratów odchyleń danych eksperymentalnych od znalezionej prostej będzie najmniejsza. Na tym polega cały sens metody najmniejszych kwadratów.
Zatem rozwiązanie przykładu sprowadza się do znalezienia ekstremum funkcji dwóch zmiennych.
Wyprowadzanie wzorów na znalezienie współczynników.
Układ dwóch równań z dwiema niewiadomymi jest kompilowany i rozwiązywany. Znajdowanie pochodnych cząstkowych funkcji 
przez zmienne A I B, przyrównujemy te pochodne do zera. 
Powstały układ równań rozwiązujemy dowolną metodą (np metodą podstawieniową Lub Metoda Cramera) i uzyskać wzory na znalezienie współczynników metodą najmniejszych kwadratów (LSM). 
Dany A I B funkcjonować 
przyjmuje najmniejszą wartość. Podano dowód tego faktu poniżej w tekście na końcu strony.
To cała metoda najmniejszych kwadratów. Wzór na znalezienie parametru A zawiera sumy ,, i parametr N- ilość danych eksperymentalnych. Zalecamy oddzielne obliczanie wartości tych kwot. Współczynnik B znalezione po obliczeniach A.
Czas przypomnieć sobie oryginalny przykład.
Rozwiązanie.
W naszym przykładzie n=5. Wypełniamy tabelę dla wygody obliczenia kwot uwzględnionych we wzorach wymaganych współczynników. 
Wartości w czwartym wierszu tabeli uzyskuje się poprzez pomnożenie wartości drugiego wiersza przez wartości trzeciego wiersza dla każdej liczby I.
Wartości w piątym wierszu tabeli uzyskuje się przez podniesienie do kwadratu wartości w drugim wierszu dla każdej liczby I.
Wartości w ostatniej kolumnie tabeli są sumami wartości w wierszach.
Do znalezienia współczynników używamy wzorów metody najmniejszych kwadratów A I B. Podstawiamy do nich odpowiednie wartości z ostatniej kolumny tabeli: 
Stąd, y = 0,165x+2,184- żądana przybliżająca linia prosta.
Pozostaje dowiedzieć się, która z linii y = 0,165x+2,184 Lub 
lepiej przybliża oryginalne dane, czyli dokonuje oszacowania metodą najmniejszych kwadratów.
Estymacja błędu metodą najmniejszych kwadratów.
Aby to zrobić, musisz obliczyć sumę kwadratów odchyleń oryginalnych danych od tych linii 
I 
, mniejsza wartość odpowiada linii, która lepiej przybliża oryginalne dane w sensie metody najmniejszych kwadratów. 
Od , potem prosto y = 0,165x+2,184 lepiej przybliża oryginalne dane.
Graficzna ilustracja metody najmniejszych kwadratów (LS).
Wszystko doskonale widać na wykresach. Czerwona linia to znaleziona linia prosta y = 0,165x+2,184, niebieska linia to 
, różowe kropki to dane oryginalne.
W praktyce przy modelowaniu różnych procesów - w szczególności ekonomicznych, fizycznych, technicznych, społecznych - powszechnie stosuje się tę lub inną metodę obliczania przybliżonych wartości funkcji na podstawie ich znanych wartości w określonych punktach stałych.
Często pojawia się tego rodzaju problem aproksymacji funkcji:
przy konstruowaniu przybliżonych wzorów do obliczania wartości wielkości charakterystycznych badanego procesu na podstawie danych tabelarycznych uzyskanych w wyniku eksperymentu;
w całkowaniu numerycznym, różniczkowaniu, rozwiązywaniu równania różniczkowe itp.;
w razie potrzeby obliczyć wartości funkcji w punktach pośrednich rozpatrywanego przedziału;
przy wyznaczaniu wartości wielkości charakterystycznych procesu poza rozpatrywanym przedziałem, w szczególności przy prognozowaniu.
Jeżeli do modelowania pewnego procesu określonego tabelą skonstruujemy funkcję, która w przybliżeniu opisuje ten proces w oparciu o metodę najmniejszych kwadratów, będzie to nazywać się funkcją aproksymującą (regresją), a sam problem konstruowania funkcji aproksymujących nazwiemy problem przybliżenia.
W artykule omówiono możliwości pakietu MS Excel do rozwiązywania tego typu problemów, ponadto przedstawiono metody i techniki konstruowania (tworzenia) regresji dla funkcji tabelarycznych (co jest podstawą analizy regresji).
W programie Excel dostępne są dwie opcje tworzenia regresji.
Dodanie wybranych regresji ( linie trendu- linie trendu) na diagram zbudowany na podstawie tabeli danych dla badanej charakterystyki procesu (dostępny tylko w przypadku posiadania skonstruowanego diagramu);
Wykorzystanie wbudowanych funkcji statystycznych arkusza Excel, pozwalających na uzyskanie regresji (linii trendu) bezpośrednio z tabeli danych źródłowych.
Dodawanie linii trendu do wykresu
W przypadku tabeli danych opisującej proces i przedstawionej w postaci diagramu Excel udostępnia skuteczne narzędzie do analizy regresji, które umożliwia:
budować w oparciu o metodę najmniejszych kwadratów i dodawać do diagramu pięć rodzajów regresji, które modelują badany proces z różnym stopniem dokładności;
dodaj do diagramu skonstruowane równanie regresji;
określić stopień zgodności wybranej regresji z danymi wyświetlanymi na wykresie.
Na podstawie danych wykresowych Excel pozwala uzyskać regresje liniowe, wielomianowe, logarytmiczne, potęgowe, wykładnicze, które są określone równaniem:
y = y(x)
gdzie x jest zmienną niezależną, która często przyjmuje wartości ciągu liczb naturalnych (1; 2; 3; ...) i daje na przykład odliczenie czasu badanego procesu (charakterystyka).
1 . Regresja liniowa jest dobra do modelowania cech, których wartości rosną lub maleją w stałym tempie. Jest to najprostszy model do skonstruowania dla badanego procesu. Konstruuje się go według równania:
y = mx + b
gdzie m jest tangensem kąta nachylenia regresja liniowa do osi odciętej; b - współrzędna punktu przecięcia regresji liniowej z osią rzędnych.
2 . Linia trendu wielomianowego jest przydatna do opisywania cech, które mają kilka różnych ekstremów (maksimów i minimów). O wyborze stopnia wielomianu decyduje liczba ekstremów badanej cechy. Zatem wielomian drugiego stopnia może dobrze opisać proces, który ma tylko jedno maksimum lub minimum; wielomian trzeciego stopnia - nie więcej niż dwa ekstrema; wielomian czwartego stopnia - nie więcej niż trzy ekstrema itp.
W tym przypadku linia trendu jest konstruowana zgodnie z równaniem:
y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6
gdzie współczynniki c0, c1, c2,... c6 są stałymi, których wartości wyznaczane są w trakcie budowy.
3 . Linię trendu logarytmicznego z powodzeniem stosuje się przy modelowaniu cech, których wartości początkowo szybko się zmieniają, a następnie stopniowo stabilizują.
y = do ln(x) + b
4 . Linia trendu prawa potęgowego daje dobre wyniki, jeśli wartości badanej zależności charakteryzują się stałą zmianą tempa wzrostu. Przykładem takiej zależności jest wykres ruchu samochodu z jednostajnym przyspieszeniem. Jeśli w danych znajdują się wartości zerowe lub ujemne, nie można użyć linii trendu mocy.
Zbudowane zgodnie z równaniem:
y = doxb
gdzie współczynniki b, c są stałymi.
5 . Jeśli tempo zmian danych stale rośnie, należy zastosować linię trendu wykładniczego. W przypadku danych zawierających wartości zerowe lub ujemne ten rodzaj przybliżenia również nie ma zastosowania.
Zbudowane zgodnie z równaniem:
y = do ebx
gdzie współczynniki b, c są stałymi.
Wybierając linię trendu, Excel automatycznie oblicza wartość R2, która charakteryzuje wiarygodność aproksymacji: niż bliższa wartość R2 do jedności, tym bardziej wiarygodnie linia trendu przybliża badany proces. W razie potrzeby wartość R2 można zawsze wyświetlić na wykresie.
Określone według wzoru:
Aby dodać linię trendu do serii danych:
aktywuj wykres na podstawie serii danych, czyli kliknij w obszarze wykresu. W menu głównym pojawi się pozycja Diagram;
po kliknięciu tej pozycji na ekranie pojawi się menu, w którym należy wybrać polecenie Dodaj linię trendu.
Te same działania można łatwo wykonać przesuwając wskaźnik myszy nad wykresem odpowiadającym jednej z serii danych i klikając prawym przyciskiem myszy; W wyświetlonym menu kontekstowym wybierz polecenie Dodaj linię trendu. Na ekranie pojawi się okno dialogowe Trendline z otwartą zakładką Type (rys. 1).
Następnie potrzebujesz:
Wybierz żądany typ linii trendu na karcie Typ (domyślnie wybrany jest typ Liniowy). W przypadku typu Wielomian w polu Stopień określ stopień wybranego wielomianu.
1 . Pole Seria zbudowana na podstawie zawiera listę wszystkich serii danych na danym wykresie. Aby dodać linię trendu do określonej serii danych, wybierz jej nazwę w polu Zbudowana na serii.
W razie potrzeby wchodząc w zakładkę Parametry (rys. 2) można ustawić następujące parametry linii trendu:
zmienić nazwę linii trendu w polu Nazwa krzywej aproksymowanej (wygładzonej).
w polu Prognoza ustaw liczbę okresów (do przodu lub do tyłu) prognozy;
wyświetlić równanie linii trendu w obszarze wykresu, dla którego należy włączyć opcję pokazuj równanie na wykresie;
wyświetlić w obszarze wykresu wartość wiarygodności aproksymacji R2, dla której należy zaznaczyć opcję Umieść na wykresie wartość wiarygodności aproksymacji (R^2);
ustawić punkt przecięcia linii trendu z osią Y, dla którego należy zaznaczyć checkbox przecięcia krzywej z osią Y w punkcie;
Kliknij przycisk OK, aby zamknąć okno dialogowe.
Aby rozpocząć edycję narysowanej już linii trendu, można skorzystać z trzech sposobów:
użyj polecenia Wybrana linia trendu z menu Format, po wcześniejszym wybraniu linii trendu;
z menu kontekstowego wybierz polecenie Formatuj linię trendu, które wywołuje się klikając prawym przyciskiem myszy na linię trendu;
kliknij dwukrotnie linię trendu.
Na ekranie pojawi się okno dialogowe Format linii trendu (rys. 3), zawierające trzy zakładki: Widok, Typ, Parametry, przy czym zawartość dwóch ostatnich całkowicie pokrywa się z podobnymi zakładkami okna dialogowego Linia trendu (rys. 1). -2). Na karcie Widok możesz ustawić rodzaj linii, jej kolor i grubość.
Aby usunąć narysowaną już linię trendu, wybierz linię trendu do usunięcia i naciśnij klawisz Delete.
Zaletami rozważanego narzędzia analizy regresji są:
względna łatwość konstruowania linii trendu na wykresach bez tworzenia dla niej tabeli danych;
dość szeroka lista typów proponowanych linii trendu, a lista ta obejmuje najczęściej stosowane typy regresji;
umiejętność przewidywania zachowania badanego procesu poprzez dowolną (w granicach zdrowego rozsądku) liczbę kroków do przodu, a także do tyłu;
możliwość otrzymania równania linii trendu w formie analitycznej;
możliwość, w razie potrzeby, uzyskania oceny wiarygodności przybliżenia.
Wady obejmują:
konstrukcja linii trendu odbywa się tylko wtedy, gdy istnieje diagram zbudowany na serii danych;
proces generowania serii danych dla badanej cechy na podstawie uzyskanych dla niej równań linii trendu jest nieco zaśmiecony: wymagane równania regresji są aktualizowane przy każdej zmianie wartości oryginalnej serii danych, ale tylko w obszarze wykresu , chwila serie danych, wygenerowane na podstawie starego równania linii trendu, pozostaje niezmienione;
W raportach wykresu przestawnego zmiana widoku wykresu lub powiązanego raportu w formie tabeli przestawnej nie powoduje zachowania istniejących linii trendu, co oznacza, że przed narysowaniem linii trendu lub innym formatowaniem raportu w formie wykresu przestawnego należy upewnić się, że układ raportu spełnia wymagane wymagania.
Linie trendu można wykorzystać do uzupełnienia serii danych prezentowanych na wykresach, takich jak wykresy, histogramy, płaskie, niestandaryzowane wykresy warstwowe, wykresy słupkowe, wykresy punktowe, wykresy bąbelkowe i wykresy giełdowe.
Nie można dodawać linii trendu do serii danych na wykresach 3D, znormalizowanych, radarowych, kołowych i pierścieniowych.
Korzystanie z wbudowanych funkcji programu Excel
Excel posiada także narzędzie do analizy regresji umożliwiające wykreślanie linii trendu poza obszarem wykresu. Istnieje wiele funkcji arkusza statystycznego, których można użyć w tym celu, ale wszystkie pozwalają jedynie na budowanie regresji liniowej lub wykładniczej.
Excel ma kilka funkcji do konstruowania regresji liniowej, w szczególności:
NACHYLENIE i CIĘCIE.
TENDENCJA;
A także kilka funkcji do konstruowania wykładniczej linii trendu, w szczególności:
LGRFPRIBL.
Należy zauważyć, że techniki konstruowania regresji przy użyciu funkcji TREND i WZROST są prawie takie same. To samo można powiedzieć o parze funkcji LINEST i LGRFPRIBL. W przypadku tych czterech funkcji przy tworzeniu tabeli wartości wykorzystuje się funkcje Excela takie jak formuły tablicowe, co nieco zaśmieca proces budowania regresji. Należy również zauważyć, że konstrukcję regresji liniowej naszym zdaniem najłatwiej przeprowadzić za pomocą funkcji SLOPE i INTERCEPT, gdzie pierwsza z nich określa nachylenie regresji liniowej, a druga wyznacza odcinek przechwytywany przez regresję na y -oś.
Zalety wbudowanego narzędzia funkcyjnego do analizy regresji to:
dość prosty, jednolity proces generowania serii danych o badanej charakterystyce dla wszystkich wbudowanych funkcji statystycznych wyznaczających linie trendu;
standardowa metodyka konstruowania linii trendu na podstawie wygenerowanych serii danych;
umiejętność przewidywania zachowania badanego procesu poprzez wymaganą liczbę kroków do przodu lub do tyłu.
Do wad można zaliczyć fakt, że Excel nie posiada wbudowanych funkcji umożliwiających tworzenie innych (poza liniowymi i wykładniczymi) typów linii trendu. Okoliczność ta często nie pozwala na wybór wystarczająco dokładnego modelu badanego procesu, a także na uzyskanie prognoz bliskich rzeczywistości. Dodatkowo przy korzystaniu z funkcji TREND i WZROST nie są znane równania linii trendu.
Należy zaznaczyć, że autorzy nie postawili sobie za cel przedstawienia przebiegu analizy regresji w jakimkolwiek stopniu kompletności. Jego głównym zadaniem jest pokazanie na konkretnych przykładach możliwości pakietu Excel przy rozwiązywaniu problemów aproksymacyjnych; zademonstrować, jakie skuteczne narzędzia ma Excel do budowania regresji i prognozowania; ilustrują, jak takie problemy mogą być stosunkowo łatwo rozwiązane nawet przez użytkownika, który nie ma rozległej wiedzy na temat analizy regresji.
Przykłady rozwiązania konkretnych problemów
Przyjrzyjmy się rozwiązywaniu konkretnych problemów za pomocą wymienionych narzędzi Excela.
Problem 1
Z tabelą danych o zyskach przedsiębiorstwa transportu samochodowego za lata 1995-2002. musisz wykonać następujące czynności:
Zbuduj diagram.
Dodaj do wykresu linie trendu liniowego i wielomianowego (kwadratowego i sześciennego).
Korzystając z równań linii trendu, uzyskaj dane tabelaryczne dotyczące zysków przedsiębiorstw dla każdej linii trendu w latach 1995-2004.
Proszę o prognozę zysków przedsiębiorstwa na lata 2003 i 2004.
Rozwiązanie problemu
W obszarze komórek A4:C11 arkusza Excel wpisz arkusz pokazany na ryc. 4.
Po wybraniu zakresu komórek B4:C11 budujemy diagram.
Aktywujemy skonstruowany diagram i zgodnie z metodą opisaną powyżej, po wybraniu rodzaju linii trendu w oknie dialogowym Linia trendu (patrz rys. 1), dodajemy do wykresu naprzemiennie linie trendu liniowego, kwadratowego i sześciennego. W tym samym oknie dialogowym należy otworzyć zakładkę Parametry (patrz rys. 2), w polu Nazwa krzywej aproksymowanej (wygładzanej) wpisać nazwę dodawanego trendu, a w polu Prognoza do przodu na: okresy ustawić wartość wartość 2, gdyż planuje się sporządzenie prognozy zysków na dwa lata do przodu. Aby wyświetlić równanie regresji i wartość niezawodności aproksymacji R2 w obszarze wykresu, należy włączyć opcję pokazywania równania na ekranie i umieścić na wykresie wartość wiarygodności aproksymacji (R^2). Dla lepszej percepcji wizualnej zmieniamy rodzaj, kolor i grubość konstruowanych linii trendu, do czego służy zakładka Widok okna dialogowego Format linii trendu (patrz rys. 3). Powstały diagram z dodanymi liniami trendu pokazano na ryc. 5.
Uzyskanie danych tabelarycznych o zyskach przedsiębiorstw dla każdej linii trendu za lata 1995-2004. Skorzystajmy z równań linii trendu przedstawionych na ryc. 5. W tym celu w komórkach zakresu D3:F3 należy wpisać informację tekstową o rodzaju wybranej linii trendu: Trend liniowy, Trend kwadratowy, Trend sześcienny. Następnie wpisz formułę regresji liniowej w komórce D4 i korzystając ze znacznika wypełnienia, skopiuj tę formułę z odniesieniami względnymi do zakresu komórek D5:D13. Należy zaznaczyć, że każda komórka posiadająca formułę regresji liniowej z zakresu komórek D4:D13 ma jako argument odpowiadającą komórkę z zakresu A4:A13. Podobnie w przypadku regresji kwadratowej wypełnij zakres komórek E4:E13, a w przypadku regresji sześciennej wypełnij zakres komórek F4:F13. W związku z tym sporządzono prognozę zysków przedsiębiorstwa na lata 2003 i 2004. wykorzystując trzy trendy. Wynikową tabelę wartości pokazano na ryc. 6.
Problem 2
Zbuduj diagram.
Dodaj do wykresu linie trendu logarytmicznego, potęgowego i wykładniczego.
Wyprowadź równania uzyskanych linii trendu, a także wartości niezawodności przybliżenia R2 dla każdej z nich.
Korzystając z równań linii trendu, uzyskaj dane tabelaryczne dotyczące zysku przedsiębiorstwa dla każdej linii trendu za lata 1995-2002.
Korzystając z tych linii trendu, sporządź prognozę zysków firmy na lata 2003 i 2004.
Rozwiązanie problemu
Kierując się metodologią podaną przy rozwiązaniu zadania 1, otrzymujemy diagram z dodanymi do niego liniami trendu logarytmicznego, potęgowego i wykładniczego (rys. 7). Następnie korzystając z otrzymanych równań linii trendu wypełniamy tabelę wartości zysku przedsiębiorstwa zawierającą przewidywane wartości na rok 2003 i 2004. (ryc. 8).
Na ryc. 5 i rys. widać, że model z trendem logarytmicznym odpowiada najniższej wartości niezawodności aproksymacji
R2 = 0,8659
Największe wartości R2 odpowiadają modelom o trendzie wielomianowym: kwadratowym (R2 = 0,9263) i sześciennym (R2 = 0,933).
Problem 3
Mając do dyspozycji tabelę danych o zyskach przedsiębiorstwa transportu samochodowego za lata 1995-2002 podaną w zadaniu 1 należy wykonać następujące czynności.
Uzyskaj serie danych dla linii trendu liniowego i wykładniczego za pomocą funkcji TREND i GROW.
Korzystając z funkcji TREND i WZROST, oszacuj prognozę zysków przedsiębiorstwa na lata 2003 i 2004.
Utwórz diagram dla oryginalnych danych i wynikowych serii danych.
Rozwiązanie problemu
Skorzystajmy z arkusza ćwiczeń dla zadania 1 (patrz rys. 4). Zacznijmy od funkcji TREND:
wybierz zakres komórek D4:D11, który należy wypełnić wartościami funkcji TREND odpowiadającymi znanym danym o zysku przedsiębiorstwa;
Wywołaj polecenie Funkcja z menu Wstaw. W wyświetlonym oknie dialogowym Kreator funkcji wybierz funkcję TREND z kategorii Statystyka, a następnie kliknij przycisk OK. Tę samą operację można wykonać, klikając przycisk (Wstaw funkcję) na standardowym pasku narzędzi.
W wyświetlonym oknie dialogowym Argumenty funkcji wprowadź zakres komórek C4:C11 w polu Znane_wartości_y; w polu Znane_wartości_x - zakres komórek B4:B11;
Aby wprowadzona formuła stała się formułą tablicową należy użyć kombinacji klawiszy + + .
Formuła, którą wpisaliśmy w pasku formuły, będzie wyglądać następująco: =(TREND(C4:C11,B4:B11)).
W rezultacie zakres komórek D4:D11 zostaje wypełniony odpowiednimi wartościami funkcji TREND (rys. 9).
Sporządzenie prognozy zysków przedsiębiorstwa na lata 2003 i 2004. niezbędny:
wybierz zakres komórek D12:D13, w którym zostaną wprowadzone wartości przewidywane przez funkcję TREND.
wywołaj funkcję TREND i w wyświetlonym oknie Argumenty funkcji wpisz w polu Znane_wartości_y - zakres komórek C4:C11; w polu Znane_wartości_x - zakres komórek B4:B11; oraz w polu Nowe_wartości_x - zakres komórek B12:B13.
zamień tę formułę w formułę tablicową, używając kombinacji klawiszy Ctrl + Shift + Enter.
Wprowadzona formuła będzie wyglądać następująco: =(TREND(C4:C11;B4:B11;B12:B13)), a zakres komórek D12:D13 zostanie wypełniony przewidywanymi wartościami funkcji TREND (patrz rys. 9).
Serię danych wypełnia się w podobny sposób za pomocą funkcji WZROST, która służy do analizy zależności nieliniowych i działa dokładnie tak samo, jak jej liniowy odpowiednik TREND.
Rysunek 10 przedstawia tabelę w trybie wyświetlania formuły.
Dla danych początkowych i otrzymanych serii danych schemat pokazany na rys. jedenaście.
Problem 4
Mając tabelę danych o przyjęciu wniosków o usługi przez służbę spedycyjną przedsiębiorstwa transportu samochodowego za okres od 1 do 11 dnia bieżącego miesiąca, należy wykonać następujące czynności.
Uzyskaj serie danych dla regresji liniowej: za pomocą funkcji SLOPE i INTERCEPT; za pomocą funkcji REGLINP.
Uzyskaj serię danych do regresji wykładniczej za pomocą funkcji LGRFPRIBL.
Korzystając z powyższych funkcji, sporządź prognozę wpływu wniosków do działu spedycyjnego na okres od 12 do 14 dnia bieżącego miesiąca.
Utwórz diagram dla oryginalnej i otrzymanej serii danych.
Rozwiązanie problemu
Należy zauważyć, że w przeciwieństwie do funkcji TREND i WZROST żadna z funkcji wymienionych powyżej (NACHYLENIE, PRZECIĘCIE, REGLINP, LGRFPRIB) nie jest regresją. Funkcje te pełnią jedynie rolę pomocniczą, wyznaczając niezbędne parametry regresji.
W przypadku regresji liniowych i wykładniczych budowanych za pomocą funkcji SLOPE, INTERCEPT, LINEST, LGRFPRIB zawsze znany jest wygląd ich równań, w przeciwieństwie do regresji liniowych i wykładniczych odpowiadających funkcjom TREND i GROWTH.
1 . Zbudujmy regresję liniową za pomocą równania:
y = mx+b
przy użyciu funkcji SLOPE i INTERCEPT, przy czym nachylenie regresji m jest określone funkcją SLOPE, a człon wolny b jest określany przez funkcję INTERCEPT.
W tym celu wykonujemy następujące czynności:
wprowadź oryginalną tabelę do zakresu komórek A4:B14;
wartość parametru m zostanie określona w komórce C19. Wybierz funkcję Nachylenie z kategorii Statystyka; wpisz zakres komórek B4:B14 w polu znane_wartości_y oraz zakres komórek A4:A14 w polu znane_wartości_x. Formuła zostanie wpisana w komórkę C19: =NACHYLENIE(B4:B14,A4:A14);
W podobny sposób określa się wartość parametru b w komórce D19. A jego zawartość będzie wyglądać następująco: =SEGMENT(B4:B14,A4:A14). Zatem wartości parametrów m i b wymagane do skonstruowania regresji liniowej zostaną zapisane odpowiednio w komórkach C19, D19;
Następnie wprowadź formułę regresji liniowej do komórki C4 w postaci: =$C*A4+$D. W tej formule komórki C19 i D19 zapisywane są z odwołaniami bezwzględnymi (adres komórki nie powinien zmieniać się podczas ewentualnego kopiowania). Znak odniesienia bezwzględnego $ można wpisać z klawiatury lub przy pomocy klawisza F4, po umieszczeniu kursora na adresie komórki. Używając uchwytu wypełniania, skopiuj tę formułę do zakresu komórek C4:C17. Otrzymujemy wymagane serie danych (ryc. 12). Z uwagi na to, że liczba żądań jest liczbą całkowitą, należy w zakładce Liczba okna Format komórki ustawić format liczb z liczbą miejsc po przecinku na 0.
2 . Zbudujmy teraz regresję liniową określoną równaniem:
y = mx+b
za pomocą funkcji REGLINP.
Dla tego:
Wprowadź funkcję REGLINP jako formułę tablicową w zakresie komórek C20:D20: =(LINEST(B4:B14,A4:A14)). W rezultacie otrzymujemy wartość parametru m w komórce C20 i wartość parametru b w komórce D20;
wpisz formułę w komórce D4: =$C*A4+$D;
skopiuj tę formułę za pomocą znacznika wypełnienia do zakresu komórek D4:D17 i uzyskaj żądaną serię danych.
3 . Regresję wykładniczą budujemy za pomocą równania:
korzystając z funkcji LGRFPRIBL wykonuje się to analogicznie:
W zakresie komórek C21:D21 wpisujemy funkcję LGRFPRIBL w postaci formuły tablicowej: =( LGRFPRIBL (B4:B14,A4:A14)). W tym przypadku wartość parametru m zostanie określona w komórce C21, a wartość parametru b zostanie określona w komórce D21;
formułę wpisuje się do komórki E4: =$D*$C^A4;
za pomocą znacznika wypełnienia formuła ta jest kopiowana do zakresu komórek E4:E17, gdzie będzie zlokalizowany szereg danych dla regresji wykładniczej (patrz rys. 12).
Na ryc. Rysunek 13 przedstawia tabelę, w której możesz zobaczyć funkcje, których używamy z wymaganymi zakresami komórek, a także formuły.
Ogrom R 2 zwany współczynnik determinacji.
Zadaniem konstrukcji zależności regresyjnej jest znalezienie wektora współczynników m modelu (1), przy którym współczynnik R przyjmuje wartość maksymalną.
Do oceny istotności R wykorzystuje się test F Fishera, obliczany ze wzoru
Gdzie N- wielkość próby (liczba eksperymentów);
k jest liczbą współczynników modelu.
Jeśli F przekracza pewną wartość krytyczną dla danych N I k i przyjęte prawdopodobieństwo ufności, wówczas wartość R uważa się za znaczącą. Stoły wartości krytyczne F podano w podręcznikach statystyki matematycznej.
Zatem o istotności R decyduje nie tylko jego wartość, ale także stosunek liczby eksperymentów do liczby współczynników (parametrów) modelu. Rzeczywiście, współczynnik korelacji dla n=2 dla prostego modelu liniowego wynosi 1 (pojedynczą linię prostą można zawsze poprowadzić przez 2 punkty na płaszczyźnie). Jeśli jednak danymi eksperymentalnymi są zmienne losowe, takiej wartości R należy ufać z dużą ostrożnością. Zwykle, aby uzyskać istotny R i wiarygodną regresję, dążą do tego, aby liczba eksperymentów znacznie przekraczała liczbę współczynników modelu (n>k).
Aby zbudować model regresji liniowej, potrzebujesz:
1) przygotować listę n wierszy i m kolumn zawierających dane eksperymentalne (kolumna zawierająca wartość wyjściową Y musi być pierwszy lub ostatni na liście); Weźmy np. dane z poprzedniego zadania, dodając kolumnę o nazwie „Nr okresu”, ponumerujmy numery okresów od 1 do 12. (będą to wartości X)
2) przejdź do menu Dane/Analiza danych/Regresja
Jeżeli w menu „Narzędzia” brakuje pozycji „Analiza danych”, należy w tym samym menu przejść do pozycji „Dodatki” i zaznaczyć pole wyboru „Pakiet analiz”.
3) w oknie dialogowym „Regresja” ustaw:
· przedział wejściowy Y;
· przedział wejściowy X;
· przedział wyjściowy - lewa górna komórka przedziału, w którym będą umieszczane wyniki obliczeń (zaleca się umieszczenie ich na nowym arkuszu);
4) kliknij „OK” i przeanalizuj wyniki.
Metoda najmniejszych kwadratów wykorzystywane do estymacji parametrów równania regresji.Jedną z metod badania stochastycznych zależności między cechami jest analiza regresji.
Analiza regresji polega na wyprowadzeniu równania regresji, za pomocą którego wyznaczana jest średnia wartość zmiennej losowej (atrybut wyniku), jeśli znana jest wartość innej (lub innych) zmiennych (atrybutów czynników). Obejmuje następujące kroki:
- wybór formy połączenia (rodzaj równania regresji analitycznej);
- estymacja parametrów równania;
- ocena jakości analitycznego równania regresji.
W przypadku liniowej zależności parami równanie regresji będzie miało postać: y i =a+b·x i +u i . Parametry aib tego równania są szacowane na podstawie danych obserwacja statystyczna x i y. Wynikiem takiej oceny jest równanie: , gdzie , to estymaty parametrów a i b, to wartość wynikowego atrybutu (zmiennej) otrzymana z równania regresji (wartość obliczona).
Najczęściej używany do szacowania parametrów metoda najmniejszych kwadratów (LSM).
Metoda najmniejszych kwadratów zapewnia najlepsze (spójne, efektywne i bezstronne) oszacowania parametrów równania regresji. Ale tylko wtedy, gdy zostaną spełnione pewne założenia dotyczące składnika losowego (u) i zmiennej niezależnej (x) (patrz założenia OLS).
Problem estymacji parametrów równania pary liniowej metodą najmniejszych kwadratów wygląda następująco: otrzymać takie oszacowania parametrów , , przy których suma kwadratów odchyleń rzeczywistych wartości charakterystyki wypadkowej – y i od obliczonych wartości – jest minimalna.
Formalnie Test OLS można zapisać w ten sposób: 
.
Klasyfikacja metod najmniejszych kwadratów
- Metoda najmniejszych kwadratów.
- Metoda największej wiarygodności (dla normalnego klasycznego modelu regresji liniowej postuluje się normalność reszt regresji).
- Uogólnioną metodę najmniejszych kwadratów OLS stosuje się w przypadku autokorelacji błędów oraz w przypadku heteroskedastyczności.
- Metoda ważonych najmniejszych kwadratów ( szczególny przypadek OLS z resztami heteroscedastycznymi).
Zilustrujmy tę kwestię metoda klasyczna graficznie metodą najmniejszych kwadratów. W tym celu skonstruujemy wykres punktowy na podstawie danych obserwacyjnych (x i, y i, i=1;n) w prostokątnym układzie współrzędnych (taki wykres punktowy nazywany jest polem korelacji). Spróbujmy wybrać linię prostą najbliższą punktom pola korelacji. Zgodnie z metodą najmniejszych kwadratów linię dobiera się w taki sposób, aby suma kwadratów odległości pionowych pomiędzy punktami pola korelacji a tą prostą była minimalna. 
Zapis matematyczny tego problemu: 
.
Wartości y i oraz x i =1...n są nam znane, są to dane obserwacyjne. W funkcji S reprezentują stałe. Zmienne w tej funkcji są wymaganymi oszacowaniami parametrów - , . Aby znaleźć minimum funkcji dwóch zmiennych, należy obliczyć pochodne cząstkowe tej funkcji dla każdego z parametrów i przyrównać je do zera, tj. 
.
W rezultacie otrzymujemy układ 2 normalnych równań liniowych: 
Decydowanie ten system, znajdujemy wymagane oszacowania parametrów: 
Poprawność wyliczenia parametrów równania regresji można sprawdzić porównując wielkości (mogą wystąpić pewne rozbieżności ze względu na zaokrąglenia obliczeń).
Aby obliczyć szacunki parametrów, możesz zbudować tabelę 1.
Znak współczynnika regresji b wskazuje kierunek zależności (jeśli b > 0, zależność jest bezpośrednia, jeśli b<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Formalnie wartość parametru a jest średnią wartością y przy x równym zero. Jeśli współczynnik atrybutu nie ma i nie może mieć wartości zerowej, to powyższa interpretacja parametru a nie ma sensu.
Ocena bliskości związku między cechami
przeprowadzono przy użyciu współczynnika korelacji par liniowych - r x,y. Można to obliczyć korzystając ze wzoru: 
. Dodatkowo współczynnik korelacji par liniowych można wyznaczyć poprzez współczynnik regresji b: 
.
Zakres dopuszczalnych wartości współczynnika korelacji pary liniowej wynosi od –1 do +1. Znak współczynnika korelacji wskazuje kierunek zależności. Jeżeli rx, y >0, to połączenie jest bezpośrednie; jeśli rx, y<0, то связь обратная.
Jeśli współczynnik ten jest bliski jedności, wówczas zależność między cechami można interpretować jako dość bliską liniową. Jeżeli jego moduł jest równy jeden ê r x , y ê =1, to zależność między cechami ma charakter liniowy funkcyjny. Jeżeli cechy x i y są liniowo niezależne, to r x,y jest bliskie 0.
Aby obliczyć r x, y, możesz także skorzystać z tabeli 1.
Tabela 1
| N obserwacji | x ja | tak, ja | x i ∙y i | ||
| 1 | x 1 | y 1 | x 1 y 1 | ||
| 2 | x 2 | y 2 | x 2 y 2 | ||
| ... | |||||
| N | x rz | y n | x n y n | ||
| Suma kolumny | ∑x | ∑ r | ∑xy | ||
| Średnia wartość |
 |
 |
,
gdzie d 2 jest wariancją y wyjaśnioną równaniem regresji;
e 2 - resztowa (niewyjaśniona równaniem regresji) wariancja y;
s 2 y - całkowita (całkowita) wariancja y.
Współczynnik determinacji charakteryzuje udział zmienności (rozproszenia) wynikowej cechy y wyjaśnionej regresją (a w konsekwencji współczynnikiem x) w całkowitej zmienności (rozproszeniu) y. Współczynnik determinacji R 2 yx przyjmuje wartości od 0 do 1. Odpowiednio wartość 1-R 2 yx charakteryzuje proporcję wariancji y spowodowaną wpływem innych czynników nieuwzględnionych w modelu i błędami specyfikacji.
W przypadku sparowanej regresji liniowej R 2 yx = r 2 yx.
