Decydować się problem z przybliżeniem dane eksperymentalne - sposoby budowania równania regresji. Problem aproksymacji pojawia się w razie potrzeby analitycznie, czyli w postaci zależności matematycznej, do opisu rzeczywistych zjawisk, których obserwacje podane są w postaci tabeli zawierającej wartości wskaźnika w różnym czasie lub w różne znaczenia niezależny argument. Na przykład,
Wskaźniki zysku są znane (można je wyznaczyć Y) w zależności od kwoty inwestycji ( x);
Wielkość sprzedaży firmy jest znana ( Y) w ciągu sześciu tygodni swojej pracy. W tym przypadku, x To ciąg tygodni.
Czasami mówią, że trzeba budować model empiryczny . Empiryczny nazywa się modelem zbudowanym na podstawie rzeczywistych obserwacji. Jeżeli uda się znaleźć model, możliwe jest dokonanie prognozy zachowania się badanego zjawiska i procesu w przyszłości oraz ewentualnie wybór optymalnego kierunku jego rozwoju.
Ogólnie problem z przybliżeniem dane eksperymentalne mają następujące inscenizacja :
Niech dane uzyskane w praktyczny sposób (w trakcien eksperymenty lub obserwacje), które mogą być reprezentowane przez pary liczb (X i; w i) ... Relację między nimi odzwierciedla tabela:
| x | x 1 | x 2 | x 3 | … | x n |
| Y | r 1 | r 2 | tak 3 | … | y n |
Istnieje klasa różnych funkcjiF ... Należy znaleźć analityczne (tj. matematyczne) wyrażenie zależności między tymi wskaźnikami, czyli należy wybrać ze zbioru funkcji F a funkcjęF , tak że. który Najlepszym sposobem wygładziłaby eksperymentalną zależność między zmiennymi i jak najdokładniej odzwierciedliłaby ogólny trend zależności międzyx oraz Y, z wyłączeniem błędów pomiarowych i przypadkowych odchyleń.
Możesz poznać formę funkcji albo z rozważań teoretycznych, albo analizując położenie punktów (xi; yi) na płaszczyźnie współrzędnych.
Rozwiąż graficznie problem z przybliżeniem znaczy narysować taką krzywą, której punkty (x i; ŷ i) będzie jak najbliżej punktów startowych (xi; yi) wyświetlanie danych eksperymentalnych.
Dla rozwiązań problemy z przybliżeniem posługiwać się metoda najmniejszych kwadratów .
W tym przypadku funkcja jest uważana za najlepsze przybliżenie, jeśli dla niej suma kwadratów odchyleń wartości „teoretycznych” znalezionych za pomocą wzoru empirycznego z odpowiednich wartości eksperymentalnych ma najmniejsza wartość w porównaniu z innymi funkcjami, z których wybiera się żądane przybliżenie.
Notacja matematyczna metoda najmniejszych kwadratów to:
gdzie n - liczba obserwacji wskaźników.
Zatem problem aproksymacji dzieli się na dwie części.
Najpierw ustala się rodzaj zależności i odpowiednio rodzaj formuły empirycznej, to znaczy decydują, czy jest liniowa, kwadratowa, logarytmiczna, czy jakakolwiek inna. W przypadku braku teoretycznych rozważań dotyczących wyboru postaci wzoru, zależność funkcjonalną wybiera się zwykle spośród najprostszych, porównując ich wykresy z wykresem danej funkcji.
Następnie wyznacza się wartości liczbowe nieznanych parametrów wybranego wzoru empirycznego, dla którego aproksymacja do danej funkcji okazuje się najlepsza.
Najprostszym typem modelu empirycznego z dwoma parametrami służącymi do aproksymacji wyników eksperymentalnych jest regresja liniowa, opisana przez funkcja liniowa:
gdzie a, b są wymagane parametry.
Dla modelu regresji liniowej zapiszemy metodę najmniejszych kwadratów (1):
Aby rozwiązać (2) w odniesieniu do a i b, pochodne cząstkowe są równe zeru:
W rezultacie, aby znaleźć a i b, konieczne jest rozwiązanie układu liniowych równań algebraicznych postaci:
(3)
Istnieją różne sposoby implementacji metody najmniejszych kwadratów dla regresji liniowej w programie Excel.
1 sposób. Skonstruuj układ liniowych równań algebraicznych, podstawiając wszystkie znane wartości do (3) i rozwiąż go np. metodą macierzową (patrz zadanie 4).
W postaci wzoru element tabeli obliczeniowej pokazano na ryc. 26.
Metoda 2. Rozwiąż problem optymalizacji (2) w Excelu, stosując Znalezienie rozwiązania(patrz oddz. 5).
Uwaga 1. Należy zauważyć, że dla funkcji celu S wygodnie jest korzystać z wbudowanego funkcja matematyczna SUMKVRAZN (tablica1, tablica2), w wyniku czego obliczana jest suma kwadratów różnic dwóch tablic. W naszym przypadku zakres wartości początkowych powinniśmy określić jako array1, a wartości „teoretyczne” wyliczone przez formułę jako array2, gdzie a oraz b - są to adresy komórek z żądanymi wartościami.
Uwaga 2. W oknie dialogowym polecenia Znajdź rozwiązanie należy określić komórkę docelową, kierunek celu - do minimum oraz komórki do zmiany (rys. 28). To zadanie nie zawiera żadnych ograniczeń.
Uwaga 3. Modele nieliniowe postaci mogą być również wykorzystywane jako modele empiryczne o dwóch parametrach:
Opisana metoda rozwiązywania metodą najmniejszych kwadratów ma zastosowanie dla zależności nieliniowych.
Metoda 3. Aby znaleźć wartości parametrów a oraz b w przypadku regresji liniowej możesz użyć następujących wbudowanych funkcji statystycznych w programie Excel:
NACHYLENIE (znane_y; znane_x)
PRZECIĘCIE (znane_Y; znane_X)
REGLINP (znane_y; znane_x)
Ponadto funkcja SLOPE() zwraca wartość parametru a , funkcja INTERCEPT() zwraca wartość parametru b. REGLINP() zwraca jednocześnie oba parametry zależności liniowej, ponieważ jest to funkcja tablicowa. Dlatego, aby wprowadzić do tabeli funkcję REGLINP(), należy przestrzegać następujące zasady:
Wybierz dwie sąsiednie komórki
Wprowadź formułę
· Na koniec naciśnij jednocześnie kombinację klawiszy Ctrl + Shift + Enter.
W rezultacie lewa komórka będzie zawierała wartość parametru a , a po prawej - wartość parametru b.
Aby rozwiązać problem aproksymacji graficznie w Excelu musisz zbudować wykres na podstawie danych początkowych, na przykład wykres punktowy z wartościami połączonymi liniami wygładzającymi (patrz punkt 1). Excel może kreślić na tym wykresie Linia trendu... Linię trendu można dodać do dowolnej serii danych za pomocą następujące typy wykresy: wykresy warstwowe, wykresy liniowe, wykresy słupkowe, wykresy słupkowe lub wykresy punktowe.
Podczas tworzenia linii trendu w programie Excel na podstawie danych wykresu stosowane jest jedno lub drugie przybliżenie. Excel pozwala wybrać jedną z pięciu aproksymujących linii lub obliczyć linię pokazującą średnią ruchomą.
Dodatkowo Excel daje możliwość wybrania wartości przecięcia linii trendu z osią Y, a także dodania do wykresu równania aproksymacji oraz wartości ufności aproksymacji (R2). Możesz również określić przyszłe i przeszłe wartości danych na podstawie linii trendu i związanego z nią równania aproksymacji.
Aproksymacja wielomianowa funkcji ciągłej na odcinku.
Aproksymacja (z łac. „w przybliżeniu” – „zbliżać się”) – przybliżone wyrażenie dowolnych obiektów matematycznych (na przykład liczb lub funkcji) poprzez inne prostsze, wygodniejsze w użyciu lub po prostu lepiej znane. W badaniach naukowych przybliżenie służy do opisywania, analizowania, uogólniania i dalsze użycie wyniki empiryczne.
Jak wiadomo, może istnieć dokładna (funkcjonalna) relacja między wielkościami, gdy jedna wartość argumentu odpowiada jednej określonej wartości, oraz mniej dokładna (korelacja) relacja, gdy jedna konkretna wartość argumentu odpowiada przybliżonej wartości lub zbiór wartości funkcji, które są mniej lub bardziej zbliżone do siebie. Podczas prowadzenia badania naukowe, przetwarzanie wyników obserwacji lub eksperymentu zwykle dotyczy drugiej opcji. Podczas badania zależności ilościowych różnych wskaźników, których wartości są określane empirycznie, z reguły istnieje pewna zmienność. Częściowo wynika to z niejednorodności badanych obiektów przyrody nieożywionej, a zwłaszcza ożywionej, częściowo z powodu błędu obserwacji i ilościowej obróbki materiałów. Ten ostatni składnik nie zawsze da się całkowicie wykluczyć, zminimalizować go można jedynie poprzez staranny dobór odpowiedniej metody badawczej i rzetelność pracy. Dlatego przy wykonywaniu jakichkolwiek prac badawczych pojawia się problem rozpoznania prawdziwej natury zależności badanych wskaźników, taki czy inny stopień jest maskowany brakiem rozliczalności zmienności wartości. W tym celu stosuje się aproksymację - przybliżony opis zależności korelacyjnej zmiennych odpowiednim równaniem zależność funkcjonalna, przekazujący główny nurt uzależnienia (lub jego „trend”).
Wybierając aproksymację, należy wyjść od konkretnego problemu badawczego. Zwykle im prostsze równanie zostanie użyte do aproksymacji, tym bardziej przybliżony będzie opis zależności.
Dlatego ważne jest, aby przeczytać jak istotne i co spowodowało odchylenia określonych wartości od wynikowego trendu. Przy opisywaniu zależności empirycznie pewne wartości znacznie większą dokładność można osiągnąć, stosując bardziej złożone, wieloparametrowe równanie. Nie ma jednak sensu dążyć z maksymalną dokładnością do przekazywania przypadkowych odchyleń wartości w określonych seriach danych empirycznych. Dużo ważniejsze jest uchwycenie ogólnego wzorca, który w w tym przypadku najbardziej logiczny i z akceptowalną dokładnością wyraża się dokładnie równaniem dwuparametrowym funkcja zasilania... Wybierając więc metodę aproksymacyjną, badacz zawsze dokonuje kompromisu: decyduje, na ile w tym przypadku celowe i właściwe jest „poświęcenie” szczegółów i odpowiednio, na ile ogólna powinna być wyrażona zależność porównywanych zmiennych. Wraz z identyfikacją wzorców zamaskowanych przez losowe odchylenia danych empirycznych od ogólny wzór aproksymacja pozwala również na rozwiązanie wielu innych ważnych problemów: sformalizowanie stwierdzonej zależności; znaleźć nieznane wartości zmiennej zależnej przez interpolację lub, jeśli ma to zastosowanie, ekstrapolację.
Aproksymacja wielomianowa zostanie omówiona tutaj. Oznacza to, że naszym zadaniem jest, aby na podstawie danych początkowych (funkcji i odcinka) znaleźć wielomian, którego odchylenie linii od wykresu funkcji początkowej będzie minimalne.
Najpopularniejszą metodą aproksymacji wielomianowej jest metoda najmniejszych kwadratów. W Excelu jest to zaimplementowane za pomocą wykresu i linii trendu.
Przeanalizujmy tę metodę w Excelu.
Wstępne dane:
Najpierw musimy podzielić ten segment za pomocą podziału „Czebyszewa”, ponieważ dany widok dzielenie zawsze daje dokładniejszy wynik.
W kolumnie I (rys. 1) zapisujemy liczby od 0 do 8, ponieważ Segment dzielimy na 8 części.
W kolumnie z komórki są obliczane według wzoru: COS (3.141593 * I / 8). Aby obliczyć każdą komórkę, używamy odpowiedniego I.
Wartość każdego x określa wzór: 2 * z + 1.
Kolumna F (x) oblicz wartość tej funkcji dla każdego x.
Obrazek 1
Następnie w komórkach H2, I2, J2 ustawiamy początkowe wartości współczynników a, b i c w pożądanym wielomianu (ryc. 2).
Zdjęcie 2
W kolumnie F z komórek od 2 do 10 obliczamy wartości odchyleń, tj. moduł różnicy między wartością funkcji początkowej a znalezionym wielomianem.
Formuła: ABS ((1 + x ^ 2) ^ 0,5 + 2 ^ (- x) - ($H 2 $ * x ^ 2 + $ I 2 $ * x + $ J 2 $).
Komórka B11 oblicza sumę wariancji, a komórka B12 oblicza odchylenie średnie (rysunek 3).
Rysunek 3
Użyj Kreatora wykresów, aby zbudować wykres punktowy na podstawie danych w kolumnach x i F (x). Teraz w zakładce „Diagram” wybierz „Dodaj linię trendu” i ustaw odpowiednie pole wyboru, aby równanie było widoczne na wykresie (rys. 4).
Rysunek 4
Teraz podstawiamy współczynniki z otrzymanego równania do komórek H2, I2 i J2 (ryc. 5).
Rysunek 5
Jak widać, średnie odchylenie wynosi 0,117006252.
Znaleziono wielomian: 0,363 * x² - 0,6901 * x + 2,2203.
Proponujemy inną metodę aproksymacji wielomianowej.
Otwórz zakładkę „Usługa” i wybierz „Wyszukaj rozwiązania”. W wyświetlonym oknie określ F11 jako komórkę docelową i jest ona równa wartości minimalnej. W polu „zmieniające się komórki” podaj H2, I2 i J2.
Kliknij przycisk „Uruchom”. Po zakończeniu procedury widzimy, że wyniki uległy zmianie (ryc. 6).
Rysunek 6
Tym razem średnie odchylenie wynosi 0,106084329.
Znaleziono wielomian: 0,35724 * x² - 0,702 * x + 2,259158.
Wynik ten jest znacznie dokładniejszy niż poprzedni, co potwierdza przewagę stosowania minimalizacji sumy odchyleń w porównaniu z metodą najmniejszych kwadratów.
ZALEŻNOŚCI
Excel posiada narzędzia do przewidywania procesów. Problem aproksymacji pojawia się, gdy konieczne jest analityczne opisanie zjawisk zachodzących w życiu i podanych w postaci tabel zawierających wartości argumentu(ów) i funkcji. W przypadku stwierdzenia zależności możliwe jest dokonanie prognozy zachowania się badanego systemu w przyszłości i ewentualnie wybór optymalnego kierunku jego rozwoju. Taka funkcja analityczna (zwana również trendem) może mieć różnego rodzaju oraz inny poziom złożoności w zależności od złożoności systemu i pożądanej dokładności reprezentacji.
10.1. Regresja liniowa
Najprostszym i najbardziej popularnym jest przybliżenie proste - regresja liniowa.
Załóżmy, że mamy aktualne informacje o poziomach zysku Y w zależności od wielkości X inwestycji kapitałowych - Y (X). Na ryc. 10.1-1 pokazuje cztery takie punkty M (Y, X). Niech też mamy powód, by przypuszczać, że ta zależność jest liniowa, tj. ma formę Y = A + BX. Gdybyśmy byli w stanie znaleźć współczynniki A i B i użyć ich do skonstruowania linii prostej (na przykład takiej jak na rysunku), w przyszłości moglibyśmy poczynić świadome założenia dotyczące dynamiki biznesu i możliwego stanu komercyjnego przedsiębiorstwo w przyszłości. Oczywiście zadowoliłaby się linia prosta położona jak najbliżej znanych nam punktów M (Y, X), czyli posiadające minimalną ilość odchyleń lub ilość błędów (na rysunku odchylenia są pokazane liniami przerywanymi). Wiadomo, że jest tylko jedna taka prosta.
|
|
Do rozwiązania tego problemu stosuje się metodę najmniejszych kwadratów błędów. Różnica (błąd) między znana wartość Y1 punktu М1 (Y1, X1) i wartość Y (X1), obliczona równaniem prostej dla tej samej wartości X1, będą
D1 = Y1 - A - B X1.
Ta sama różnica
dla X = X2 będzie D2 = Y2 - A - B X2;
dla X = X3 D3 = Y3 - A - B X3;
a dla X = X4 D4 = Y4 - A - B X4.
Napiszmy wyrażenie na sumę kwadratów tych błędów
Ф (A, B) = (Y1 – A – B X1) 2 + (Y2 – A – B X2) 2 + (Y3 – A – B X3) 2 + (Y4 – A – B X4) 2
lub w formie skróconej Ф (B, A) = е (Yi - A - BXi) 2.
Tutaj wiemy, że wszystkie X i Y oraz współczynniki A i B są nieznane.Narysujmy szukaną linię w taki sposób (czyli wybierzmy takie A i B), aby ta suma kwadratów błędów Ф (A, B) jest minimalny. Warunki minimalizmu to znane relacje
¶Ф (A, B) / ¶A = 0 i ¶Ф (A, B) / ¶B = 0.
Wyprowadźmy te wyrażenia (pomijamy indeksy przy znaku sumy):
¶ [å (Yi – A – B Xi) 2] / ¶A = å (Yi – A – B Xi) (- 1)
¶ [å (Yi – A – B Xi) 2] / ¶B = å (Yi – A – B Xi) (- Xi).
Przekształcamy otrzymane formuły i przyrównujemy je do zera
Rozwiązywanie problemów z aproksymacją za pomocą Excela
lekarz fiz.- mat. Nauki, profesor Gavrilenko V.V. asystent Parokhnenko L.M.
(Narodowa Wyższa Szkoła Transportu)
Informacje teoretyczne. W praktyce przy modelowaniu różnych
procesy, w szczególności ekonomiczne, fizyczne, techniczne, społeczne,
szeroko stosowane są różne metody obliczania wartości przybliżonych
funkcje na podstawie ich znanych wartości w niektórych stałych punktach.
Często pojawiają się takie problemy aproksymacji funkcji:
∙ przy konstruowaniu przybliżonych wzorów do obliczania wartości charakterystyki
wartości badanego procesu zgodnie z tabelami uzyskanymi w
wynik eksperymentu fizycznego lub obliczeniowego;
∙ z całkowaniem numerycznym, różniczkowaniem numerycznym, numerycznie
n decyzja równania różniczkowe itp.;
∙ jeśli konieczne jest obliczenie wartości funkcji w punktach pośrednich
kah rozważanego przedziału;
∙ przy określaniu wartości wielkości charakterystycznych procesu poza
interwał, w szczególności, jeśli to konieczne, zajrzyj do
„Przeszłość”), czyli przy ustalaniu wartości wskaźników procesu przed
godziny obserwacji;
∙ w prognozowaniu, czyli przy odbiorze oceny wstępne Będę-
wartości wskaźników procesu zainteresowania (umiejętność patrzenia)
w przyszłość").
Jeśli, aby zasymulować pewien proces podany w tabeli,
skonstruować przybliżony opis tej funkcji procesu na podstawie
metoda najmniejszych kwadratów, to nazywana jest funkcją aproksymującą
(regresja), a sam problem konstruowania funkcji aproksymujących nazywa się
problem aproksymacji.
W tym artykule omówiono możliwości pakietu Excel podczas rozwiązywania
problemy aproksymacyjne, czyli metody i techniki konstruowania
(tworzenie) regresji dla funkcji tabelarycznych, co jest podstawą analizy regresji.
Excel ma takie funkcje do budowania regresji:
1) dodanie wybranych regresji (linii trendu) do wykresu zbudowanego na podstawie tabeli danych dla badanej charakterystyki procesu (narzędzie to można wykorzystać tylko w przypadku skonstruowania dia-
2) korzystanie z wbudowanych funkcji statystycznych arkusza roboczego Przewyższać,
pozwalające na uzyskanie regresji (linii trendu) na podstawie tabeli wyników
dane (korzystanie z tego narzędzia nie jest wcześniej powiązane
z obecnością odpowiedniego schematu).
Dodawanie linii trendu do wykresu
W przypadku tabeli danych opisujących pewien proces i reprezentowanych przez diagram Excel ma skuteczne narzędzie Analiza regresji
za umożliwienie:
∙ zbuduj w oparciu o metodę najmniejszych kwadratów i dodaj do wykresu pięć typów regresji (linii trendu), które są mniej lub bardziej dokładne
sti symulują badany proces;
∙ dodać do diagramu równanie skonstruowanej regresji;
∙ określić stopień dopasowania wybranej regresji do danych wyświetlanych na wykresie.
Konstruowane modele procesów - pokazują linie trendu
trend zmiany danych, umożliwiają określenie wartości badania
charakterystyk w punktach pośrednich, przewidywać zachowanie tego procesu w przyszłości (problem ekstrapolacji), a także spojrzeć w jego przeszłość.
Na podstawie danych z wykresu Excel pozwala na uzyskanie tego typu regresji
to lub linie trendu, takie jak liniowa, wielomianowa, logarytmiczna, ste-
piana, wykładnicza, które są podane równaniem y = y (x), gdzie x nie jest
Zmienna zależna, która często przyjmuje wartości ciągu liczb naturalnych (1; 2; 3; ...) i generuje np. odliczanie czasu przebiegu badanego procesu.
1. Regresja liniowa jest dobra do modelowania charakterystyk, które rosną lub maleją w stałym tempie. Jest to najprostszy w budowie, ale najmniej dokładny model badanego procesu.
y = m x + b,
gdzie m jest kątem nachylenia regresji liniowej do osi odciętej; b - współrzędna punktu przecięcia regresji liniowej z osią rzędnych.
2. Wielomianowa linia trendu jest przydatna do opisu charakterystyk,
posiadające kilka wyraźnych ekstremów (maksymów i minimów)
ruch). O wyborze stopnia wielomianu linii trendu (wielomianu) decyduje liczba ekstremów badanej cechy. Tak więc wielomian drugiego stopnia może dobrze opisywać cechę, która ma tylko jedno maksimum
mama lub minimum; wielomian trzeciego stopnia - nie więcej niż dwa ekstrema; na-
linia czwartego stopnia - nie więcej niż trzy ekstrema itp.
Zbudowany zgodnie z równaniem
y = c0 + c1 x + c2 x2 + c3 x3 + c4 x4 + c5 x5 + c6 x6,
gdzie współczynniki c 0, c 1, c 2, ... c 6 są stałymi.
3. Logarytmiczne linia trendu jest z powodzeniem wykorzystywana w modelowaniu
cechy, których wartości początkowo szybko rosną lub maleją, a następnie stopniowo się stabilizują.
Zbudowany zgodnie z równaniem
y = c × ln (x) + b,
4. Linia trendu mocy daje dobre wyniki, jeśli wartości badania
Zależności te charakteryzują się stałą zmianą tempa wzrostu.
Przykładem takiej zależności jest wykres ruchu jednostajnie przyspieszonego.
nija samochód. Jeśli dane zawierają wartości zerowe lub ujemne, nie można użyć linii trendu potęgowego.
Zbudowany zgodnie z równaniem
y = c × xb,
gdzie współczynniki b, c są stałymi.
5. Wykładniczy Linia trendu powinna być używana, gdy tempo zmian danych stale rośnie. W przypadku danych zawierających wartości zerowe lub ujemne tego rodzaju przybliżenie nie ma zastosowania.
Zbudowany zgodnie z równaniem
y = c × eb × x,
gdzie współczynniki b, c są stałymi.
Przy wyborze linii trendu Excel automatycznie oblicza wartość R 2, która charakteryzuje dokładność aproksymacji: im bliższa jest wartość R 2, tym bardziej wiarygodnie linia trendu przybliża badaną
mój proces. W razie potrzeby wartość R 2 może być zawsze wyświetlana na
diagram.
Określone wzorem
R2 = 1- | Σ1 = ∑ (yj - Yj) 2 | S2 = ∑Y j 2 - | × (∑Yj) 2 | ||||||
Aby dodać linię trendu do serii danych:
1. Aktywuj wykres zbudowany na podstawie serii danych, tj. Kliknij-
w obszarze wykresu. Dia-
2. Po kliknięciu na tę pozycję na ekranie pojawi się menu, w którym należy wybrać polecenie Dodaj linię trendu.
najedź myszą na wykres wykreślony na serii danych i kliknij prawym przyciskiem myszy
za pomocą myszy i w wyświetlonym menu kontekstowym wybierz polecenie Dodaj
linia trendu. Na ekranie pojawi się okno dialogowe Trendline z rozwiniętym
zakładka Typ (rys. 1).
Rys. 1. Karta Typ w oknie dialogowym Format linii trendu
3. Wybierz żądany typ linii trendu na zakładce Typ (domyślnie wybrany jest typ Liniowy). Dla typu wielomianowego w polu Stopień obok
dmuchnij, aby ustawić stopień wybranego wielomianu.
4. Pole Wykreślone na serii zawiera wszystkie serie danych z danego wykresu. Aby dodać linię trendu do określonej serii danych, wybierz jej nazwę w polu Wykreślone na serii.
5. W razie potrzeby przechodząc do zakładki Parametry (rys. 2) można:
zestaw trendów następujące parametry:
∙ Zmień nazwę linii trendu w polu Nazwa aproksymacji
(wygładzona) krzywa;
∙ Ustaw liczbę okresów (do przodu lub do tyłu) dla prognozy w Pro-
∙ Wyświetl równanie linii trendu w obszarze wykresu, dla którego konieczne jest
Zaznacz pole wyboru opcji „pokaż równanie na diagramie”.
∙ Wyświetl wartość wiarygodności aproksymacji R 2 w obszarze wykresu,
dla których należy zaznaczyć pole przy opcji "wstaw na schemat
tożsamość wiarygodności aproksymacji (R^2)”.
∙ Ustaw punkt przecięcia linii trendu z osią Y, dla którego powinieneś ustawić
Zaznacz pole dla opcji „przecięcie krzywej z osią y w punkcie:”. 6. Naciśnij klawisz OK.
Rys. 2. Karta Opcje okna dialogowego Linia trendu
Aby edytować już zbudowaną linię trendu, należy:
1. Kliknij lewym przyciskiem myszy na wybraną linię trendu
zmiana.
2. Naciśnij klawisz Format w menu głównym, a następnie w menu kontekstowym, które się pojawi
nago wybierz polecenie Wybrana linia trendu.
Pozycje 1-2 są również łatwe do wdrożenia następna sztuczka: wysłać do
Ustaw wskaźnik myszy na wykresie liniowym trendu, kliknij prawym przyciskiem myszy iz wyświetlonego menu kontekstowego wybierz polecenie Formatuj linię trendu.
Pozycje 1–2 są jeszcze łatwiejsze do wdrożenia: poprzez dwukrotne kliknięcie lewym przyciskiem myszy na wykresie liniowym trendu.
3. Na ekranie pojawi się okno dialogowe Trendline Format (rys. 3) zawierające:
który zawiera trzy zakładki: Widok, Typ, Parametry, a zawartość zakładek to Typ,
Parametry całkowicie pokrywają się z podobnymi zakładkami okna dialogowego.
na linii Trendu (ryc. 1–2).
4. W razie potrzeby przechodząc do zakładki Widok (rys. 3) można ustawić typ linii, jej kolor oraz grubość dla linii trendu.
5. Naciśnij klawisz OK.
Aby usunąć już zbudowaną linię trendu, wybierz linię trendu do usunięcia i naciśnij klawisz Delete.
Zalety tego narzędzia do analizy regresji to:
∙ względna łatwość kreślenia linii trendu na wykresach bez tworzenia
tabele danych dla niego;
∙ dość szeroka lista typów proponowanych linii trendu, a ta lista zawiera najczęściej używane regresje;
∙ umiejętność losowego przewidywania zachowania badanego procesu
dowolna (w granicach zdrowego rozsądku) liczba kroków do przodu, a także do tyłu;
∙ umiejętność uzyskania równania linii trendu w postaci analitycznej;
∙ możliwość, w razie potrzeby, uzyskania oceny wiarygodności
aproksymacja danych.
Wady obejmują następujące punkty:
∙ budowa linii trendu jest przeprowadzana tylko wtedy, gdy istnieje wykres zbudowany na wielu danych;
∙ proces tworzenia serii danych do badań jest nieco zaśmiecony
moja charakterystyka na podstawie otrzymanych dla niej równań linii trendu
tak, skoro współczynniki tych równań przy każdej zmianie wartości szeregu
tak dane są przeliczane, ale tylko w obszarze wykresu;
∙ w raportach w formie wykresu przestawnego po zmianie widoku wykresu lub połączonego raportu Stół obrotowy istniejące linie trendu nie są zapisywane,
czyli przed narysowaniem linii trendu lub innym formatowaniem raportu podsumowanie
schematy, należy upewnić się, że układ raportu spełnia wymagane
wymagania.
Rys. 3. Karta Widok okna dialogowego Format linii trendu
Linie trendu można wykorzystać do uzupełnienia serii danych przedstawionych na wykresie.
fiks, histogramy, płaskie nieznormalizowane wykresy z obszarami lub
wykresy nagie, rozproszone, bąbelkowe i giełdowe.
Nie można dodawać linii trendu do serii danych na wolumetrycznych, znormalizowanych
Łazienki, wykresy radarowe, kołowe i pączkowe. Przy zmianie rodzaju dia-
gramów na jeden z powyższych, a po zmianie widoku raportu w formie wykresu przestawnego lub połączonego raportu w formie tabeli przestawnej odpowiedni
linie trendu pasujące do danych zostaną utracone.
Korzystanie z wbudowanych funkcji programu Excel
Excel zapewnia również narzędzie do analizy regresji do wykreślania linii trendu poza obszarem wykresu. W tym celu można użyć wielu funkcji statystycznych arkusza kalkulacyjnego, ale wszystkie z nich pozwalają na budowanie tylko regresji liniowej lub wykładniczej.
Excel ma kilka opcji konstruowania regresji liniowej (czy
trend neuronowy), w szczególności:
∙ za pomocą funkcji TREND;
∙ za pomocą funkcji REGLINP;
∙ z funkcjami TILT i INTERCEPT.
Excel ma również kilka opcji budowania wykładniczej linii trendu, w szczególności:
∙ za pomocą funkcji WZROST;
∙ za pomocą funkcji LGRFPRIBL.
Należy zauważyć, że techniki konstruowania regresji za pomocą funkcji
TREND i WZROST są prawie takie same. To samo można powiedzieć o parze funkcji REGLINP i LGRFPRIBL. W przypadku wszystkich tych czterech funkcji podczas tworzenia tabeli wartości używane są funkcje programu Excel, takie jak formuły tablicowe, co nieco zaśmieca proces budowania regresji.
ten. Zauważ też, że konstrukcję (tworzenie) regresji liniowej, naszym zdaniem, najłatwiej przeprowadzić za pomocą funkcji SLOPE i INTERCEPT,
gdzie pierwszy definiuje nachylenie regresja liniowa, a drugi
raj - odcinek odcięty przez regresję na osi rzędnych.
Zalety tego narzędzia do analizy regresji to:
∙ dość prostym procesem tego samego typu tworzenia serii danych jest
następująca charakterystyka dla wszystkich wbudowanych funkcji statystycznych,
nadawanie linii trendu;
∙ standardowa technika konstruowania linii trendu na podstawie wygenerowanych serii danych;
∙ umiejętność przewidywania zachowania badanego procesu w razie potrzeby
liczba kroków do przodu lub do tyłu.
Wadą tego narzędzia jest fakt, że Excel nie ma wbudowanych funkcji do tworzenia innych (poza liniowymi i wykładniczymi).
różne) typy linii trendu. Ta okoliczność często nie pozwala na wybór wystarczająco dokładnego modelu z wykorzystaniem powyższych wbudowanych funkcji.
del badanego procesu, a także uzyskanie prognoz zbliżonych do rzeczywistości.
Również podczas korzystania z funkcji TREND i WZROST równania linii trendu nie są znane.
tyi - on konkretne przykłady przy podejmowaniu decyzji pokaż możliwości pakietu Excel
problemy aproksymacji; pokazać, jak skuteczne są narzędzia
Excel posiada narzędzia do konstruowania regresji i prognozowania; pęd-
Aby zilustrować, jak stosunkowo łatwo takie problemy może rozwiązać nawet użytkownik, który nie ma głębokiej wiedzy z zakresu analizy regresji.
Zaproponowana w artykule metodologia doskonalenia umiejętności rozwiązywania środków
mi Excel tego rodzaju problemów (patrz też, gdzie metody rozwiązywania układów liniowych równań algebraicznych, równań nieliniowych,
problemy optymalizacyjne, problemy transportowe) mogą być przydatne i interesujące dla użytkowników. Wynika to z faktu, że Excel jest instalowany na prawie każdym nowoczesnym komputerze, podczas gdy tak dobrze znany specjalistyczny
pakiety matematyczne takie jak Mathematica, Maple, Matlab, Mathcad,
z potężniejszymi możliwościami konstruowania regresji i
prognozy są wykorzystywane przez znacznie mniej niestandardowych audytów
Poniżej znajdują się rozwiązania konkretnych problemów przy użyciu wymienionych narzędzi w pakiecie Excel.
Cel 1. Do tabeli danych o zyskach przedsiębiorstwa transportu samochodowego za lata 1995-2002. musisz wykonać następujące czynności.
(Zwróć uwagę na dodatkową sekcję z dnia 06.04.2017 na końcu artykułu.)
Księgowość i kontrola! Osoby po czterdziestce powinny dobrze pamiętać to hasło z czasów budowy socjalizmu i komunizmu w naszym kraju.
Ale bez ugruntowanej rachunkowości, efektywnego funkcjonowania ani kraju, ani regionu, ani przedsiębiorstwa, ani gospodarstwo domowe w każdej społeczno-gospodarczej formacji społeczeństwa! Do sporządzania prognoz i planów działań i rozwoju wymagane są dane wstępne. Gdzie je zdobyć? Tylko jeden wiarygodnyźródło to Twój dane statystyczne z poprzednich okresów.
Uwzględniaj wyniki swoich działań, zbieraj i zapisuj informacje, przetwarzaj i analizuj dane, stosuj wyniki analiz do adopcji prawidłowe decyzje w przyszłości powinna, w moim rozumieniu, każda rozsądna osoba. To nic innego jak gromadzenie i racjonalne wykorzystanie doświadczeń życiowych. Jeśli nie prowadzisz ewidencji ważnych danych, to po pewnym czasie zapomnisz o nich i, zaczynając ponownie zajmować się tymi problemami, ponownie popełnisz te same błędy, które popełniłeś, gdy robiłeś to po raz pierwszy.
„Pamiętam, że 5 lat temu robiliśmy do 1000 sztuk takich przedmiotów miesięcznie, a teraz z trudem możemy zebrać 700!”. Otwieramy statystyki i widzimy, że 5 lat temu 500 sztuk nie było produkowanych...
„Ile przebiegnie kilometr Twojego samochodu, biorąc pod uwagę ze wszystkich koszty? " Statystyki otwarcia - 6 rubli / km. Podróż do pracy - 107 rubli. Tańsze niż taksówka (180 rubli) ponad półtora raza. A były chwile, kiedy taksówka była tańsza...
„Ile czasu zajmuje wyprodukowanie konstrukcji metalowych do narożnej wieży komunikacyjnej o wysokości 50 m?” Otwieramy statystyki - i za 5 minut odpowiedź jest gotowa...
„Ile będzie kosztował remont pokoju w mieszkaniu?” Podnosimy stare rekordy, robimy korektę o inflację z ostatnich lat, bierzemy pod uwagę, że ostatnim razem kupowaliśmy materiały 10% taniej Cena rynkowa i - znamy już szacunkowy koszt...
Prowadzenie ewidencji twojego działalność zawodowa, zawsze będziesz gotowy odpowiedzieć na pytanie szefa: „Kiedy !!! ???”. Prowadząc ewidencję domowników, łatwiej zaplanować wydatki na duże zakupy, wakacje i inne wydatki w przyszłości, podejmując odpowiednie działania, aby zarobić dodatkowe pieniądze lub ograniczyć niepotrzebne wydatki już dziś.
W tym artykule jestem na prosty przykład Pokażę jak można przetworzyć zebrane dane statystyczne w Excelu w celu dalszego wykorzystania w prognozowaniu przyszłych okresów.
Aproksymacja danych statystycznych w Excelu funkcją analityczną.
Zakład produkcyjny zajmuje się produkcją metalowych konstrukcji budowlanych z walcowania blach i profili. Strona działa stabilnie, zamówienia są tego samego typu, liczba pracowników nieznacznie się waha. Dane o wielkości produkcji za poprzednie 12 miesięcy oraz ilości walcówki przetworzonej w tych okresach według grup: blachy, dwuteowniki, kanały, kątowniki, rury okrągłe, profile przekrój prostokątny, okrągłe produkty walcowane. Po wstępnej analizie danych wyjściowych założono, że łączna miesięczna produkcja konstrukcji metalowych w znacznym stopniu zależy od liczby narożników w zamówieniach. Sprawdźmy to założenie.
Przede wszystkim kilka słów o aproksymacji. Poszukamy prawa - funkcji analitycznej, czyli funkcji, podane przez równanie, który lepiej niż inne opisuje zależność całkowitej produkcji konstrukcji metalowych od liczby kątów w zrealizowanych zamówieniach. Jest to przybliżenie, a znalezione równanie nazywa się funkcją aproksymującą pierwotną funkcję, podaną w formie tabeli.
1. Włącz Excela i umieść w arkuszu tabelę z danymi statystycznymi.
2. Następnie budujemy i formatujemy wykres punktowy, w którym ustawiamy wartości argumentu wzdłuż osi X - ilość obrobionych narożników w tonach. Na osi Y wykreślamy wartości pierwotnej funkcji - całkowitej produkcji konstrukcji metalowych w miesiącu, zgodnie z tabelą.
3. „Najeżdżamy” myszką na dowolny punkt na wykresie i klikamy prawym przyciskiem myszy, aby wywołać menu kontekstowe (jak mówi mój dobry znajomy - podczas pracy w nieznanym programie, gdy nie wiesz, co robić, kliknij prawym przyciskiem myszy częściej ...). Z rozwijanego menu wybierz "Dodaj linię trendu...".
4. W wyświetlonym oknie „Linia trendu”, na zakładce „Typ”, wybierz „Liniowy”.
6. Na wykresie pojawiła się linia prosta, która przybliża naszą zależność tabelaryczną.
Oprócz samej linii widzimy równanie tej linii i, co najważniejsze, widzimy wartość parametru R 2 - wielkość wiarygodności aproksymacji! Im bliżej jego wartości jest 1, tym dokładniej wybrana funkcja aproksymuje dane tabelaryczne!
7. Linie trendu budujemy za pomocą przybliżeń potęgowych, logarytmicznych, wykładniczych i wielomianowych, podobnie jak w przypadku liniowej linii trendu.
Najlepiej ze wszystkich wybranych funkcji aproksymuje nasze dane wielomianem drugiego stopnia, ma maksymalny współczynnik ufności R 2.
Jednak chcę cię ostrzec! Jeśli weźmiesz wielomiany wyższych stopni, to być może dostaniesz inny najwyższe wyniki ale krzywe będą skomplikowane…. Ważne jest, aby zrozumieć tutaj, że szukamy funkcji, która ma fizyczne znaczenie... Co to znaczy? Oznacza to, że potrzebujemy funkcji aproksymującej, która da adekwatne wyniki nie tylko w rozważanym zakresie wartości X, ale także poza nim, czyli odpowie na pytanie: „Jaki będzie wydatek konstrukcji metalowych, jeśli liczba przerabianych narożników miesięcznie to niecałe 45 i ponad 168 ton!” Dlatego nie polecam dać się ponieść wielomianom wysokich stopni i ostrożnie wybierać parabolę (wielomian drugiego stopnia)!
Musimy więc wybrać funkcję, która nie tylko dobrze interpoluje dane tabelaryczne w zakresie wartości X = 45…168, ale również pozwoli na odpowiednią ekstrapolację poza ten zakres. W tym przypadku wybieram funkcję logarytmiczną, chociaż możesz wybrać liniową, jako najprostszą. W tym przykładzie przy wyborze przybliżenia liniowego w programie Excel błędy będą większe niż przy wyborze przybliżenia logarytmicznego, ale nie dużo.
8. Usuń wszystkie linie trendu z pola wykresu, z wyjątkiem funkcji logarytmicznej. Aby to zrobić, kliknij prawym przyciskiem myszy niepotrzebne linie i wybierz „Wyczyść” z rozwijanego menu kontekstowego.
9. Na koniec dodaj słupki błędów do tabelarycznych punktów danych. W tym celu kliknij prawym przyciskiem myszy dowolny punkt na wykresie iw menu kontekstowym wybierz „Formatuj serię danych...” i ustaw dane w zakładce „Błędy Y” jak na poniższym rysunku.
10. Następnie prawym przyciskiem myszy klikamy dowolny wiersz zakresów błędów, w menu kontekstowym wybieramy „Formatuj słupki błędów…” i w oknie „Formatuj słupki błędów” na zakładce „Widok” ustawiamy kolor i grubość linii.
Wszystkie inne obiekty wykresu są formatowane w ten sam sposób.Przewyższać!
Ostateczny wynik diagramu przedstawia poniższy zrzut ekranu.
Wyniki.
Wynikiem wszystkich poprzednich działań był wynikowy wzór na funkcję aproksymującą y = -172,01 * ln (x) +1188,2. Znając to oraz liczbę narożników w miesięcznym zestawie robót, można z dużym prawdopodobieństwem (± 4% - patrz słupki błędów) przewidzieć całkowitą produkcję konstrukcji metalowych na miesiąc! Na przykład, jeśli w planie miesięcznym jest 140 ton narożników, to całkowita produkcja, najprawdopodobniej, przy innych warunkach równych, wyniesie 338 ± 14 ton.
Aby zwiększyć wiarygodność aproksymacji danych statystycznych, powinno być ich dużo. Dwanaście par wartości to za mało.
Z praktyki powiem, że znalezienie funkcji aproksymującej o współczynniku ufności R2>0,87 należy uznać za dobry wynik. Doskonały wynik- gdy R 2 > 0,94.
W praktyce może być trudno wyodrębnić jeden najważniejszy czynnik decydujący (w naszym przykładzie masa obrobionych narożników w miesiącu), ale jeśli spróbujesz, zawsze możesz go znaleźć w każdym konkretnym zadaniu! Oczywiście całkowita miesięczna produkcja zależy od setek czynników, które wymagają znacznych kosztów pracy dla osób ustalających standardy i innych specjalistów. Tylko wynik będzie nadal przybliżony! Czy warto więc ponieść koszty, jeśli dostępne jest znacznie tańsze modelowanie matematyczne!
W tym artykule po prostu dotknąłem wierzchołka góry lodowej zwanego zbieraniem, przetwarzaniem i praktyczne użycie dane statystyczne. Niezależnie od tego, czy udało mi się wzbudzić Twoje zainteresowanie tym tematem, mam nadzieję, że wyciągnę wnioski z komentarzy i oceny artykułu w wyszukiwarkach.
Podniesione pytanie o aproksymację funkcji jednej zmiennej ma szeroki praktyczne użycie v różne obszaryżycie. Ale rozwiązanie problemu aproksymacji funkcji ma o wiele większe zastosowanie kilka niezależnych zmienne…. Przeczytaj o tym i nie tylko w następujących artykułach na blogu.
Subskrybuj dla ogłoszeń artykułów w oknie znajdującym się na końcu każdego artykułu lub w oknie na górze strony.
Nie zapomnij potwierdzać subskrypcja klikając na link w liście, który przyjdzie do Ciebie na podany mail (może przyjść do folderu « spam » )!!!
Z zainteresowaniem przeczytam Wasze komentarze, drodzy czytelnicy! Pisać!
PS (04.06.2017)
Bardzo dokładny, piękny zamiennik danych tabelarycznych za pomocą prostego równania.
Nie jesteś zadowolony z uzyskanej dokładności aproksymacji (R 2<0,95) или вид и набор функций, предлагаемые MS Excel?
Czy wielkość wyrazu i kształt linii wielomianu aproksymującego w dużym stopniu nie są przyjemne dla oka?
Skontaktuj się przez stronę "", aby uzyskać dokładniejszy i bardziej zwięzły wynik aproksymacji danych tabelarycznych oraz aby nauczyć się prostej techniki rozwiązywania problemów związanych z aproksymacją o wysokiej precyzji przez funkcję jednej zmiennej.
Korzystając z zaproponowanego algorytmu działań, znaleziono bardzo zwartą funkcję, która zapewnia najwyższą dokładność aproksymacji: R 2 = 0,9963 !!!