Excel

Metoda aproksymacji w programie Microsoft Excel

Spośród różnych metod prognozowania nie można pomóc w wyodrębnieniu przybliżenia. Za jego pomocą można wykonywać przybliżone obliczenia i obliczać planowane wskaźniki, zastępując oryginalne obiekty prostszymi. W Excelu istnieje również możliwość zastosowania tej metody do prognozowania i analizy. Spójrzmy, jak ta metoda może zostać zaimplementowana w określonym programie za pomocą wbudowanych narzędzi.

Podanie aproksymacji

Nazwa tej metody pochodzi od łacińskiego słowa proxima - "najbliższa". Jest to przybliżenie poprzez uproszczenie i wyrównanie znanych wskaźników, dostosowanie ich do trendu i jest jego podstawą. Ale ta metoda może być używana nie tylko do prognozowania, ale także do badań już dostępnych wyników. Przecież aproksymacja jest w istocie uproszczeniem oryginalnych danych, a uproszczona wersja jest łatwiejsza do zbadania.

Głównym narzędziem używanym do wykonywania wygładzania w programie Excel jest zbudowanie linii trendu. Najważniejsze jest to, że na podstawie już istniejących wskaźników zakończono harmonogram funkcji dla przyszłych okresów. Głównym celem linii trendu, jak nietrudno zgadnąć, jest przygotowanie prognoz lub identyfikacja ogólnego trendu.

Ale można go skonstruować przy użyciu jednego z pięciu typów aproksymacji:

  • Liniowy;
  • Wykładniczy;
  • Logarytmiczny;
  • Wielomian;
  • Moc.

Rozważmy każdą z opcji bardziej szczegółowo osobno.

Lekcja: Jak zbudować linię trendów w Excelu

Metoda 1: wygładzanie liniowe

Przede wszystkim rozważmy najprostszą wersję aproksymacji, a mianowicie za pomocą funkcji liniowej. Omówimy tutaj bardziej szczegółowo, ponieważ przedstawimy ogólne momenty charakterystyczne dla innych metod, a mianowicie budowę wykresu i kilka innych niuansów, na których nie będziemy się zastanawiać nad kolejnymi wersjami.

Przede wszystkim tworzymy wykres, na podstawie którego przeprowadzimy procedurę wygładzania. W celu wykreślenia wykresu, weźmy tabelę, w której jednostkowy koszt jednostki produkowanej przez przedsiębiorstwo i odpowiadający jej zysk w danym okresie są wykazywane co miesiąc. Funkcja graficzna, którą skonstruujemy, będzie odzwierciedlała zależność wzrostu zysku od spadku kosztów produkcji.

  1. Aby narysować wykres, najpierw wybieramy kolumny "Jednostkowy koszt produkcji" i "Zysk" . Następnie przejdź do zakładki "Wstaw" . Następnie kliknij przycisk "Spot" w oknie narzędziowym "Diagramy". Na otwartej liście wybierz nazwę "Spot with smooth curve and marker" . Jest to rodzaj diagramów najbardziej odpowiedni do pracy z linią trendu, a zatem do zastosowania metody aproksymacji do Excela.

    2. Budowanie wykresu w programie Microsoft Excel

  2. Wykres jest zbudowany.

    3. Wykres jest zbudowany w programie Microsoft Excel

  3. Aby dodać linię trendu, wybierz ją, klikając prawym przyciskiem myszy. Pojawi się menu kontekstowe. Wybierz pozycję "Dodaj linię trendu ..." .

    Dodanie linii trendu poprzez menu kontekstowe w programie Microsoft Excel

    Jest inny sposób, aby go dodać. W dodatkowej grupie kart na wstążce "Praca z wykresami" przechodzimy do zakładki "Układ" . Następnie w polu narzędzia "Analiza" kliknij przycisk "Linia trendu" . Lista zostanie otwarta. Ponieważ musimy zastosować liniową aproksymację, wówczas z przedstawionych pozycji wybieramy "przybliżenie liniowe" .

    4. Dodanie linii trendu za pomocą przybornika na taśmie w programie Microsoft Excel

  4. Jeśli wybrałeś pierwszą opcję z dodaniem za pomocą menu kontekstowego, otworzy się okno formatu.

    W bloku parametrów "Konstruowanie linii trendu (aproksymacja i wygładzanie)" ustawić przełącznik w pozycji "Linia" .
    Jeśli chcesz, możesz sprawdzić pozycję "Pokaż równanie na wykresie" . Następnie równanie funkcji wygładzania zostanie wyświetlone na schemacie.

    Także w naszym przypadku, aby porównać różne warianty aproksymacji, należy sprawdzić "Miejsce na wykresie wartości wiarygodnego przybliżenia (R ^ 2)" . Ten wskaźnik może się wahać od 0 do 1 . Im wyższy, tym lepsze przybliżenie (bardziej wiarygodne). Uważa się, że przy wartości tego wskaźnika wynoszącej 0,85 i wyższej wygładzenie można uznać za wiarygodne, a jeśli wskaźnik jest niższy, to nie jest.

    Po wykonaniu wszystkich powyższych ustawień. Kliknij przycisk "Zamknij" znajdujący się w dolnej części okna.

    5. Włączenie aproksymacji liniowej w programie Microsoft Excel

  5. Jak widać, linia trendu została zbudowana na wykresie. Dla przybliżenia liniowego jest oznaczana przez czarną linię prostą. Ten rodzaj wygładzania można zastosować w najprostszych przypadkach, gdy dane zmieniają się dość szybko, a zależność wartości funkcji od argumentu jest oczywista.

Linia trendu została skonstruowana przy użyciu aproksymacji liniowej w programie Microsoft Excel

Wygładzanie, które jest używane w tym przypadku, jest opisane następującym wzorem:

y=ax+b

W naszym konkretnym przypadku formuła ma następującą formę:

y=-0,1156x+72,255

Wartość wiarygodności przybliżenia w naszym kraju wynosi 0,9418 , co jest dość akceptowalnym wynikiem, charakteryzującym się wygładzaniem, jako wiarygodnym.

Metoda 2: wykładnicze przybliżenie

Teraz spójrzmy na wykładniczy typ przybliżenia w Excelu.

  1. Aby zmienić typ linii trendu, wybierz go, klikając prawym przyciskiem myszy i wybierz "Trendline format ..." w otwartym menu.

    2. Przejdź do formatu linii trendu w programie Microsoft Excel

  2. Następnie uruchamiane jest okno formatu, który już nam jest znany. W bloku wyboru typu aproksymacji ustaw przełącznik na pozycję "wykładniczą" . Pozostałe ustawienia pozostają takie same jak w pierwszym przypadku. Klikamy przycisk "Zamknij" .

    3. Budowanie wykładniczej linii trendu w Microsoft Excel

  3. Następnie linia trendu zostanie zbudowana na wykresie. Jak widać, podczas używania tej metody ma on nieco zakrzywiony kształt. Poziom ufności wynosi 0,9592 , czyli jest wyższy niż przy zastosowaniu przybliżenia liniowego. Metoda wykładnicza jest najlepiej stosowana, gdy najpierw wartości zmieniają się szybko, a następnie przyjmują zrównoważoną formę.

Wykładnicza linia trendu została zbudowana w programie Microsoft Excel

Ogólna forma funkcji wygładzania jest następująca:

y=be^x

gdzie e jest podstawą logarytmu naturalnego.

W naszym przypadku formuła przybrała następującą postać:

y=6282,7*e^(-0,012*x)

Metoda 3: Wygładzanie logarytmiczne

Teraz czas na rozważenie metody przybliżenia logarytmicznego.

  1. W ten sam sposób, jak w poprzednim czasie, poprzez menu kontekstowe uruchamiamy okno formatu linii trendu. Ustaw przełącznik w pozycji "Logarytmiczny" i kliknij przycisk "Zamknij" .

    2. Włącz aproksymację logarytmiczną w programie Microsoft Excel

  2. Istnieje procedura konstruowania linii trendu z przybliżeniem logarytmicznym. Podobnie jak w poprzednim przypadku, lepiej jest użyć tej opcji, gdy początkowo dane szybko się zmieniają, a następnie uzyskać wyważony wygląd. Jak widać, poziom ufności wynosi 0,946. Jest to wyższa niż przy użyciu metody liniowej, ale niższa niż jakość linii trendu z wykładniczym wygładzaniem.

Logarytmiczna linia trendu została zbudowana w programie Microsoft Excel

Ogólnie, formuła wygładzania wygląda następująco:

y=a*ln(x)+b

gdzie ln jest wartością logarytmu naturalnego. Stąd nazwa metody.

W naszym przypadku formuła ma następującą formę:

y=-62,81ln(x)+404,96

Metoda 4: Wygładzanie wielomianowe

Czas rozważyć metodę wygładzania wielomianu.

  1. Przechodzimy do okna formatu linii trendu, tak jak zrobiliśmy to więcej niż raz. W bloku "Budowanie linii trendu" ustaw przełącznik na pozycję "Wielomian" . Po prawej stronie tego pola znajduje się pole "Stopień" . Jeśli wybierzesz "Wielomian", stanie się aktywny. Tutaj możesz określić dowolną wartość mocy od 2 (domyślnie ustawiona) na 6 . Ten wskaźnik określa liczbę maksimów i minimów funkcji. Podczas instalowania wielomianu drugiego stopnia opisuje się tylko jedno maksimum, a przy ustawianiu wielomianu szóstego stopnia można opisać maksymalnie pięć maksimów. Najpierw opuszczamy domyślne ustawienia, czyli wskazujemy drugi stopień. Pozostałe ustawienia pozostają takie same, jak w poprzednich metodach. Kliknij przycisk "Zamknij" .

    2. Włączenie aproksymacji wielomianu w programie Microsoft Excel

  2. Linia trendu wykorzystująca tę metodę jest skonstruowana. Jak widać, jest jeszcze bardziej zakrzywiona niż przy użyciu przybliżenia wykładniczego. Poziom ufności jest wyższy niż w przypadku każdej z metod użytych poprzednio i wynosi 0,9724 .

    Wielomianowa linia trendu w Microsoft Excel

    Ta metoda może być z powodzeniem zastosowana w przypadku, gdy dane są ciągle zmienne. Funkcja opisująca ten rodzaj wygładzania wygląda następująco:

    y=a1+a1*x+a2*x^2+…+an*x^n

    W naszym przypadku formuła przybrała postać:

    y=0,0015*x^2-1,7202*x+507,01

  3. Teraz zmieńmy stopień wielomianów, aby zobaczyć, czy wynik będzie się różnić. Powracamy do okna formatu. Typ aproksymacji jest pozostawiony wielomianem, ale przed nim w oknie stopni ustawiamy maksymalną możliwą wartość - 6 .

    4. Włączenie aproksymacji wielomianu w szóstym stopniu w programie Microsoft Excel

  4. Jak widać, nasza linia trendu przybrała formę wyraźnej krzywej, w której liczba maksimów wynosi sześć. Poziom niezawodności wzrósł jeszcze bardziej, osiągając 0,9844 .

Wielomianowa linia trendu w szóstym stopniu w programie Microsoft Excel

Formuła opisująca ten typ wygładzania ma następującą postać:

y=8E-08x^6-0,0003x^5+0,3725x^4-269,33x^3+109525x^2-2E+07x+2E+09

Metoda 5: Stopniowe wygładzanie

Na koniec rozważmy metodę aproksymacji mocy w Excelu.

  1. Przechodzimy do okna "Trend Line Format" . Ustawiamy przełącznik typu wygładzania na pozycję "Moc" . Pokazując równanie i poziom pewności, jak zwykle, zostawiamy je. Kliknij przycisk "Zamknij" .

    2. Wielomianowa linia trendu w szóstym stopniu w programie Microsoft Excel

  2. Program tworzy linię trendu. Jak widzimy, w naszym przypadku jest to linia z lekkim zgięciem. Poziom ufności wynosi 0,9618 , co jest dość wysokim wskaźnikiem. Spośród wszystkich powyższych metod poziom ufności był wyższy tylko przy użyciu metody wielomianowej.

Linia trendu mocy jest zbudowana w programie Microsoft Excel

Metoda ta jest efektywnie stosowana w przypadkach intensywnej zmiany danych funkcji. Ważne jest, aby wziąć pod uwagę, że ten wariant ma zastosowanie tylko pod warunkiem, że funkcja i argument nie przyjmują wartości ujemnych lub zerowych.

Ogólna formuła opisująca tę metodę jest następująca:

y=bx^n

W naszym przypadku wygląda to tak:

y = 6E+18x^(-6,512)

Jak widzimy, na podstawie konkretnych danych, które wykorzystaliśmy w tym przykładzie, najwyższy poziom niezawodności został pokazany za pomocą metody wielomianowej aproksymacji z wielomianem w szóstej potędze ( 0,9844 ), najniższym poziomie niezawodności w metodzie liniowej ( 0,9418 ). Nie oznacza to jednak, że ta sama tendencja będzie stosowana podczas używania innych przykładów. Nie, poziom efektywności powyższych metod może się znacznie różnić, w zależności od konkretnego typu funkcji, dla której zostanie zbudowana linia trendu. Dlatego jeśli wybrana metoda jest najskuteczniejsza dla tej funkcji, nie oznacza to, że będzie ona również optymalna w innej sytuacji.

Jeśli nie możesz od razu ustalić, na podstawie powyższych zaleceń, jaki rodzaj przybliżenia jest odpowiedni dla ciebie, to warto wypróbować wszystkie metody. Po zbudowaniu linii trendu i przejrzeniu jej poziomu pewności możesz wybrać najlepszą opcję.