Funkcje statystyczne programu Excel: samouczek z przykładami, część trzecia

Excel udostępnia szeroką gamę funkcji statystycznych, które wykonują obliczenia od średniej do bardziej złożonych funkcji rozkładu statystycznego i linii trendu.

W tym artykule zagłębimy się w funkcje statystyczne programu Excel służące do obliczania funkcji linii trendu.

Należy pamiętać, że w najnowszych wersjach programu Excel wprowadzono pewne funkcje statystyczne i dlatego nie są one dostępne w starszych wersjach.

Szacowany czas czytania: 12 minuti

Funkcje linii trendu

Forecast

Funkcja prognozy programu Excel przewiduje przyszły punkt na liniowej linii trendu dopasowanej do danego zestawu wartości x i y.

składnia

= FORECAST( x, known_y's, known_x's )

Tematy

• x: Liczbowa wartość x, dla której chcesz przewidzieć nową wartość y.
• known_y's: Tablica znanych wartości y
• known_x's: Tablica znanych wartości x

Należy pamiętać, że długość tablicy known_x musi być taki sam jak known_y i wariancja known_x nie musi wynosić zero.

przykład

W poniższym arkuszu kalkulacyjnym funkcja FORECAST Excel służy do przewidywania dodatkowego punktu wzdłuż najlepiej dopasowanej linii prostej poprzez serię znanych wartości x i y (przechowywanych w komórkach F2:F7 i G2:G7).

Jak pokazano w komórce F7 arkusza kalkulacyjnego, funkcją obliczającą oczekiwaną wartość y przy x=7 jest: :=FORECAST( 7, G2:G7, F2:F7 )

To daje wynik 32.666667 .

Intercept

Wśród funkcji prognostycznych Excela znajdziemy Intercept. Funkcja przecięcia programu Excel oblicza punkt przecięcia (wartość na przecięciu osi y) linii regresji liniowej w obrębie danego zestawu wartości x i y.

składnia

= INTERCEPT( known_y's, known_x's )

Tematy

• known_y's: Tablica znanych wartości y
• known_x's: Tablica znanych wartości x

Należy pamiętać, że długość tablicy known_x musi być taki sam jak known_y i wariancja known_x nie musi wynosić zero.

przykład

Poniższy arkusz kalkulacyjny przedstawia przykład funkcji Intercept programu Excel użytego do obliczenia punktu, w którym linia regresji liniowej przechodzi przez known_x i known_y (wymieniony w komórkach F2:F7 i G2:G7) przecina oś y.

Le known_x i known_y są nanoszone na wykres w arkuszu kalkulacyjnym.

Jak pokazano w komórce F9 arkusza kalkulacyjnego, wzór funkcji przecięcia wygląda następująco :=INTERCEPT( G2:G7, F2:F7 )

co daje wynik 2.4 .

Slope

Inną bardzo interesującą funkcją przewidywania jest nachylenie (Slope) Excel oblicza nachylenie linii regresji liniowej dla danego zestawu wartości x i y.

Składnia funkcji jest następująca:

składnia

= SLOPE( known_y's, known_x's )

Tematy

• known_y's: Tablica znanych wartości y
• known_x's: Tablica znanych wartości x

Należy pamiętać, że długość tablicy known_x musi być taki sam jak known_y i wariancja known_x nie musi wynosić zero.

przykład

Poniższy arkusz kalkulacyjny przedstawia przykład funkcji Slope (nachylenie) programu Excel używanego do obliczania nachylenia linii regresji liniowej przez known_x i known_y, w komórkach F2:F7 i G2:G7.

Le known_x i known_y są nanoszone na wykres w arkuszu kalkulacyjnym.

Jak pokazano w komórce F9 arkusza kalkulacyjnego, wzór funkcji przecięcia wygląda następująco :=SLOPE( G2:G7, F2:F7 )

co daje wynik 4.628571429.

Trend

Bardzo interesującą funkcją prognozy Excela jest TENDENCJA Excel (Trend) oblicza linię trendu liniowego poprzez zadany zestaw wartości y i (opcjonalnie) dany zestaw wartości x.

Funkcja następnie rozszerza linię trendu liniowego, aby obliczyć dodatkowe wartości y dla dodatkowego zestawu nowych wartości x.

Składnia funkcji jest następująca:

składnia

= TREND( known_y's, [known_x's], [new_x's], [const] )

Tematy

• known_y's: Tablica znanych wartości y
• [known_x's]: Jedna lub więcej tablic znanych wartości x. Jest to opcjonalny argument, który, jeśli zostanie podany, powinien mieć tę samą długość co zbiór known_y's. Jeśli zostanie pominięty, zbiór [known_x's] przyjmuje wartość {1, 2, 3, …}.
• [nowe_x]: Opcjonalny argument, dostarczający jedną lub więcej tablic wartości liczbowych reprezentujących zbiór nowych wartości x, dla których chcesz obliczyć odpowiadające im nowe wartości y. Każda tablica [nowe_x] powinien pasować do tablicy [known_x's] Jeżeli argumentem [nowe_x] jest pominięty, jest ustawiony na [known_x's] .
• [koszt]: Opcjonalny argument logiczny określający, czy w równaniu liniowym występuje stała „b”. y = m x + b , musi być równe zero. Samego siebie [koszt] ma wartość PRAWDA (lub jeśli ten argument zostanie pominięty), stała b jest traktowana normalnie;
• Samego siebie [koszt] ma wartość FAŁSZ, stała b jest ustawiana na 0 i równanie linii prostej przyjmuje postać y = mx .

przykład

W poniższym arkuszu kalkulacyjnym funkcja Excel Trend służy do przedłużenia serii wartości x i y leżących na linii prostej y = 2x + 10. Znane wartości x i y są przechowywane w komórkach A2-B5 arkusza kalkulacyjnego i są również wyświetlane na wykresie arkusza kalkulacyjnego.

Zauważ, że nie jest konieczne, aby podane punkty leżały dokładnie na linii prostej y = 2x + 10 (chociaż w tym przykładzie tak jest). Funkcja Trend programu Excel znajdzie najlepiej pasującą linię dla dowolnego podanego zestawu wartości.

Funkcja Trend wykorzystuje metodę najmniejszych kwadratów, aby znaleźć linię najlepszego dopasowania, a następnie wykorzystuje ją do obliczenia nowych wartości y dla podanych nowych wartości x.

W tym przykładzie wartości [nowe_x] są przechowywane w komórkach A8–A10, a w komórkach B8–B10 wykorzystano funkcję Trend programu Excel w celu znalezienia nowych odpowiednich wartości y. Jak pokazano na pasku formuły, formuła wygląda następująco := TREND (B2:B5, A2:A5, A8:A10)

Widzisz, że funkcja Trend na pasku formuły jest ujęta w nawiasy klamrowe { }. Oznacza to, że funkcja została wprowadzona jako formuła tablicowa .

Growth

Wśród funkcji prognostycznych Excela znajdziemy Growth, Funkcja Growth Excel oblicza krzywą wzrostu wykładniczego na podstawie danego zestawu wartości y i (opcjonalnie) jednego lub więcej zestawów wartości x. Funkcja następnie wydłuża krzywą, aby obliczyć dodatkowe wartości y dla dodatkowego zestawu nowych wartości x.

Składnia funkcji jest następująca:

składnia

= GROWTH( known_y's, [known_x's], [new_x's], [const] )

Tematy

• known_y's: Tablica znanych wartości y
• [known_x's]: Jedna lub więcej tablic znanych wartości x. Jest to opcjonalny argument, który, jeśli zostanie podany, powinien mieć tę samą długość co zbiór known_y's. Jeśli zostanie pominięty, zbiór [known_x's] przyjmuje wartość {1, 2, 3, …}.
• [nowe_x]: Zbiór nowych wartości x, dla których funkcja oblicza odpowiednie nowe wartości y. Jeżeli zostanie pominięty, zakłada się, że zbiór [nowe_x] jest równe [known_x's], a funkcja zwraca wartości y leżące na obliczonej krzywej wzrostu wykładniczego.
• [koszt]: Opcjonalny argument logiczny określający, czy w równaniu liniowym występuje stała „b”. y = b * m^x , musi być równe 1. Jeśli [koszt] ma wartość PRAWDA (lub jeśli ten argument zostanie pominięty), stała b jest traktowana normalnie; Samego siebie [koszt] ma wartość FAŁSZ, stała b jest ustawiana na 1 i równanie linii prostej przyjmuje postać y = mx .

przykład

W poniższym arkuszu kalkulacyjnym funkcja wzrostu programu Excel służy do rozszerzenia szeregu wartości x i y, które leżą na krzywej wzrostu wykładniczego y = 5 * 2^x. Są one przechowywane w komórkach A2-B5 arkusza kalkulacyjnego i pojawiają się również na wykresie arkusza kalkulacyjnego.

Funkcja Wzrost oblicza wykładniczą krzywą wzrostu, która najlepiej pasuje do podanych znanych wartości x i y. W tym prostym przykładzie najlepiej dopasowaną krzywą jest krzywa wykładnicza y = 5 * 2^x.

Gdy Excel obliczy równanie krzywej wzrostu wykładniczego, może go użyć do obliczenia nowych wartości y dla nowych wartości x podanych w komórkach A8-A10.

W tym przykładzie wartości [new_x's] są przechowywane w komórkach A8-A10 i funkcji Growth Excela został wstawiony do komórek B8-B10. Jak pokazano na pasku formuły, formuła na to wygląda:=Growth(B2:B5, A2:A5, A8:A10)

Jak widać, funkcja Wzrost na pasku formuły jest ujęta w nawiasy klamrowe { }. Oznacza to, że funkcja została wprowadzona jako formuła tablicowa .

Zauważ, że chociaż punkty w powyższym przykładzie pasują dokładnie wzdłuż krzywej y = 5 * 2^x, nie jest to konieczne. Funkcja Growth Excel znajdzie najlepiej dopasowaną krzywą dla dowolnego podanego zestawu wartości.

Funkcje finansowe

Efekt

Funkcja Effect Excel zwraca efektywną roczną stopę procentową dla danej nominalnej stopy procentowej i danej liczby okresów kapitalizacji w roku.

Efektywna roczna stopa procentowa

Efektywna roczna stopa procentowa to miara odsetek uwzględniająca kapitalizację odsetek i często używana do porównywania pożyczek finansowych o różnych warunkach kapitalizacji.

Efektywną roczną stopę procentową oblicza się za pomocą następującego równania:

gołąb nominal_rate jest nominalną stopą procentową e npery to liczba okresów kapitalizacji w roku.

Składnia funkcji jest następująca:

składnia

= EFFECT( nominal_rate, npery )

Tematy

• nominal_rate: Nominalna stopa procentowa (musi być wartością liczbową z zakresu od 0 do 1)
• npery: Liczba okresów kapitalizacji w roku (musi być dodatnią liczbą całkowitą).

przykład

Poniższy arkusz kalkulacyjny przedstawia trzy przykłady funkcji Excel Effect:

Jeśli wynik funkcji Effect wyświetla się w postaci dziesiętnej lub pokazuje 0%, oba te problemy są prawdopodobnie spowodowane formatowaniem komórki zawierającej funkcję Effect.

Dlatego problem można rozwiązać, formatując komórkę w procentach, z miejscami dziesiętnymi.

Aby to zrobić:

1. Wybierz komórki, które chcesz sformatować jako procent.
2. Otwórz okno dialogowe „Formatuj komórki”, korzystając z jednej z następujących metod:
   • Kliknij prawym przyciskiem myszy wybraną komórkę lub zakres i wybierz opcję Formatuj komórki… z menu kontekstowego;
   • Kliknij przycisk Uruchom okno dialogowe w grupie Liczby na karcie Strona główna Wstążka Excela;
   • Użyj skrótu klawiaturowego CTRL-1 (tj. wybierz klawisz CTRL i przytrzymując go, wybierz klawisz „1” (jeden)).
3. W oknie dialogowym „Formatuj komórki”:
   • Upewnij się, że karta liczba w górnej części okna dialogowego jest zaznaczone.
   • wybierać Odsetek z listy Kategoria po lewej stronie okna dialogowego .Spowoduje to wyświetlenie dodatkowych opcji po prawej stronie pola wyboru, umożliwiających wybranie liczby miejsc po przecinku, które mają się pojawiać.
   • Po wybraniu liczby miejsc po przecinku, którą chcesz wyświetlić, kliknij OK .

Nominal

Funkcja Nominal Excel zwraca nominalną stopę procentową dla danej efektywnej stopy procentowej i danej liczby okresów kapitalizacji w roku.

Składnia funkcji jest następująca:

składnia

= NOMINAL( effect_rate, npery )

Tematy

• effect_rate: Efektywna stopa procentowa (wartość liczbowa od 0 do 1).
• npery: Liczba okresów kapitalizacji w roku (musi być dodatnią liczbą całkowitą).

przykład

W poniższym arkuszu kalkulacyjnym funkcja Nominal programu Excel służy do obliczenia nominalnej stopy procentowej trzech kredytów o różnych warunkach.

Ercole Palmeri