Statistische Excel-Funktionen: Tutorial mit Beispielen, Teil drei

Excel bietet eine breite Palette statistischer Funktionen, die Berechnungen vom Mittelwert bis hin zu komplexeren statistischen Verteilungs- und Trendlinienfunktionen durchführen.

In diesem Artikel werden wir uns eingehender mit den Statistikfunktionen von Excel zur Berechnung von Trendlinienfunktionen befassen.

Bitte beachten Sie, dass einige Statistikfunktionen in neueren Excel-Versionen eingeführt wurden und daher in älteren Versionen nicht verfügbar sind.

Geschätzte Lesezeit: 12 minuti

Trendlinienfunktionen

Forecast

Die Prognosefunktion von Excel sagt einen zukünftigen Punkt auf einer linearen Trendlinie voraus, die an einen bestimmten Satz von x- und y-Werten angepasst ist.

Syntax

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

Themen

• x: Ein numerischer X-Wert, für den Sie einen neuen Y-Wert vorhersagen möchten.
• known_y's: Ein Array bekannter y-Werte
• known_x's: Ein Array bekannter x-Werte

Beachten Sie, dass die Länge des Arrays von known_x muss das gleiche sein wie das von known_y und die Varianz von known_x es muss nicht Null sein.

Beispiel

In der folgenden Tabelle ist die Funktion FORECAST Excel wird verwendet, um einen zusätzlichen Punkt entlang der am besten passenden geraden Linie anhand einer Reihe bekannter x- und y-Werte (gespeichert in den Zellen F2:F7 und G2:G7) vorherzusagen.

Wie in Zelle F7 der Tabelle gezeigt, lautet die Funktion zur Berechnung des erwarteten y-Werts bei x=7 :=FORECAST( 7, G2:G7, F2:F7 )

Dies ergibt das Ergebnis 32.666667 .

Intercept

Unter den Prognosefunktionen von Excel finden wir die Intercept. Die Funktion „Intercept“ von Excel berechnet den Achsenabschnitt (den Wert am Schnittpunkt der y-Achse) der linearen Regressionslinie über einen bestimmten Satz von x- und y-Werten.

Syntax

= INTERCEPT( known_y's, known_x's )

Themen

• known_y's: Ein Array bekannter y-Werte
• known_x's: Ein Array bekannter x-Werte

Beachten Sie, dass die Länge des Arrays von known_x muss das gleiche sein wie das von known_y und die Varianz von known_x es muss nicht Null sein.

Beispiel

Die folgende Tabelle zeigt ein Beispiel der Funktion Intercept von Excel verwendet, um den Punkt zu berechnen, an dem die lineare Regressionsgerade durch die known_x und known_y (aufgelistet in den Zellen F2:F7 und G2:G7) schneidet die y-Achse.

Le known_x und known_y werden in der Grafik der Tabelle dargestellt.

Wie in Zelle F9 der Tabelle gezeigt, lautet die Formel für die Intercept-Funktion :=INTERCEPT( G2:G7, F2:F7 )

was das Ergebnis ergibt 2.4 .

Slope

Eine weitere sehr interessante Vorhersagefunktion ist die Steigung (Slope) Excel berechnet die Steigung der linearen Regressionsgeraden anhand eines bestimmten Satzes von x- und y-Werten.

Die Syntax der Funktion lautet:

Syntax

= SLOPE( known_y's, known_x's )

Themen

• known_y's: Ein Array bekannter y-Werte
• known_x's: Ein Array bekannter x-Werte

Beachten Sie, dass die Länge des Arrays von known_x muss das gleiche sein wie das von known_y und die Varianz von known_x es muss nicht Null sein.

Beispiel

Die folgende Tabelle zeigt ein Beispiel der Funktion Slope (Steigung) von Excel zur Berechnung der Steigung der linearen Regressionsgeraden durch known_x und das known_y, in den Zellen F2:F7 und G2:G7.

Le known_x und known_y werden in der Grafik der Tabelle dargestellt.

Wie in Zelle F9 der Tabelle gezeigt, lautet die Formel für die Intercept-Funktion :=SLOPE( G2:G7, F2:F7 )

was das Ergebnis ergibt 4.628571429.

Trend

Eine sehr interessante Excel-Prognosefunktion ist die TREND Excel (Trend) berechnet die lineare Trendlinie durch einen gegebenen Satz von y-Werten und (optional) einen gegebenen Satz von x-Werten.

Anschließend erweitert die Funktion die lineare Trendlinie, um zusätzliche y-Werte für einen zusätzlichen Satz neuer x-Werte zu berechnen.

Die Syntax der Funktion lautet:

Syntax

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

Themen

• known_y's: Ein Array bekannter y-Werte
• [known_x's]: Ein oder mehrere Arrays bekannter x-Werte. Dies ist ein optionales Argument, das, wenn es angegeben wird, dieselbe Länge wie die Menge von haben sollte known_y's. Wenn es weggelassen wird, ist die Menge von [known_x's] nimmt den Wert {1, 2, 3, …} an.
• [new_x's]: Ein optionales Argument, das ein oder mehrere Arrays numerischer Werte bereitstellt, die eine Reihe neuer x-Werte darstellen, für die Sie die entsprechenden neuen y-Werte berechnen möchten. Jedes Array von [new_x's] sollte mit einem Array von [ übereinstimmenknown_x's]. Wenn das Argument [new_x's] weggelassen wird, wird es auf [ gesetztknown_x's] .
• [kosten]: Ein optionales logisches Argument, das angibt, ob die Konstante „b“ in der linearen Gleichung enthalten ist y = mx + b , muss zwangsweise gleich Null sein. Selbst [kosten] TRUE ist (oder wenn dieses Argument weggelassen wird), wird die Konstante b normal behandelt;
• Selbst [kosten] FALSCH ist, wird die Konstante b auf 0 gesetzt und die Geradengleichung wird y = mx .

Beispiel

In der folgenden Tabelle wird die Excel-Trendfunktion verwendet, um eine Reihe von x- und y-Werten zu erweitern, die auf der geraden Linie y = 2x + 10 liegen. Die bekannten x- und y-Werte werden in den Zellen A2-B5 von gespeichert werden im Tabellenkalkulationsdiagramm angezeigt und auch im Tabellenkalkulationsdiagramm angezeigt.

Beachten Sie, dass es nicht unbedingt erforderlich ist, dass die angegebenen Punkte genau entlang der geraden Linie y = 2x + 10 passen (obwohl dies in diesem Beispiel der Fall ist). Die Trendfunktion von Excel findet die am besten passende Linie für jeden von Ihnen angegebenen Wertesatz.

Die Trendfunktion verwendet die Methode der kleinsten Quadrate, um die beste Anpassungslinie zu finden, und berechnet dann daraus die neuen y-Werte für die bereitgestellten neuen x-Werte.

In diesem Beispiel sind die Werte von [new_x's] werden in den Zellen A8–A10 gespeichert, und in den Zellen B8–B10 wurde die Trendfunktion von Excel verwendet, um die neuen entsprechenden y-Werte zu finden. Wie in der Formelleiste gezeigt, lautet die Formel := TREND( B2:B5, A2:A5, A8:A10 )

Sie sehen, dass die Trendfunktion in der Bearbeitungsleiste in geschweifte Klammern {} eingeschlossen ist. Dies zeigt an, dass die Funktion als eingegeben wurde Matrixformel .

Growth

Unter den Prognosefunktionen von Excel finden wir die Growth. Die Funktion Growth Excel berechnet die exponentielle Wachstumskurve anhand eines bestimmten Satzes von y-Werten und (optional) eines oder mehrerer Sätze von x-Werten. Anschließend erweitert die Funktion die Kurve, um zusätzliche y-Werte für einen zusätzlichen Satz neuer x-Werte zu berechnen.

Die Syntax der Funktion lautet:

Syntax

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

Themen

• known_y's: Ein Array bekannter y-Werte
• [known_x's]: Ein oder mehrere Arrays bekannter x-Werte. Dies ist ein optionales Argument, das, wenn es angegeben wird, dieselbe Länge wie die Menge von haben sollte known_y's. Wenn es weggelassen wird, ist die Menge von [known_x's] nimmt den Wert {1, 2, 3, …} an.
• [new_x's]: Eine Menge neuer x-Werte, für die die Funktion die entsprechenden neuen y-Werte berechnet. Wenn es weggelassen wird, wird angenommen, dass die Menge von [new_x's] ist gleich dem von [known_x's] und die Funktion gibt die y-Werte zurück, die auf der berechneten exponentiellen Wachstumskurve liegen.
• [kosten]: Ein optionales logisches Argument, das angibt, ob die Konstante „b“ in der linearen Gleichung enthalten ist y = b * m^x , muss zwangsweise gleich 1 sein. Wenn [kosten] TRUE ist (oder wenn dieses Argument weggelassen wird), wird die Konstante b normal behandelt; Selbst [kosten] FALSCH ist, wird die Konstante b auf 1 gesetzt und die Geradengleichung wird y = mx .

Beispiel

In der folgenden Tabelle wird die Excel-Wachstumsfunktion verwendet, um eine Reihe von x- und y-Werten zu erweitern, die auf der exponentiellen Wachstumskurve y = 5 * 2^x liegen. Diese werden in den Zellen A2–B5 der Tabellenkalkulation gespeichert und erscheinen auch im Tabellenkalkulationsdiagramm.

Die Wachstumsfunktion berechnet die exponentielle Wachstumskurve, die am besten zu den bereitgestellten bekannten x- und y-Werten passt. In diesem einfachen Beispiel ist die am besten passende Kurve die Exponentialkurve y = 5 * 2^x.

Sobald Excel die Gleichung der exponentiellen Wachstumskurve berechnet hat, kann es damit die neuen y-Werte für die neuen x-Werte berechnen, die in den Zellen A8–A10 bereitgestellt werden.

In diesem Beispiel sind die Werte von [new_x's] werden in den Zellen A8-A10 und der Funktion gespeichert Growth Excel wurde in die Zellen B8-B10 eingefügt. Wie in der Formelleiste gezeigt, lautet die Formel hierfür:=Growth(B2:B5, A2:A5, A8:A10)

Sie können sehen, dass die Wachstumsfunktion in der Bearbeitungsleiste in geschweifte Klammern {} eingeschlossen ist. Dies zeigt an, dass die Funktion als eingegeben wurde Matrixformel .

Beachten Sie, dass die Punkte im obigen Beispiel zwar genau entlang der Kurve y = 5 * 2^x passen, dies jedoch nicht unbedingt erforderlich ist. Die Funktion Growth Excel findet für jeden von Ihnen angegebenen Wertesatz die am besten passende Kurve.

Finanzielle Funktionen

Bewirken

die Funktion Effect Excel gibt den effektiven Jahreszinssatz für einen bestimmten Nominalzinssatz und eine bestimmte Anzahl von Zinsperioden pro Jahr zurück.

Effektiver Jahreszins

Der effektive Jahreszins ist ein Zinsmaß, das die Zinskapitalisierung einbezieht und häufig zum Vergleich von Finanzkrediten mit unterschiedlichen Kapitalisierungsbedingungen verwendet wird.

Der effektive Jahreszins errechnet sich nach folgender Gleichung:

wo nominal_rate ist der Nominalzins e npery ist die Anzahl der Zinsperioden pro Jahr.

Die Syntax der Funktion lautet:

Syntax

= EFFECT( nominal_rate, npery )

Themen

• nominal_rate: Der Nominalzinssatz (muss ein numerischer Wert zwischen 0 und 1 sein)
• npery: Die Anzahl der Zinsperioden pro Jahr (muss eine positive ganze Zahl sein).

Beispiel

Die folgende Tabelle zeigt drei Beispiele der Excel-Effektfunktion:

Wenn das Ergebnis der Funktion Effect Ob als Dezimalzahl oder als 0 % angezeigt wird, sind beide Probleme wahrscheinlich auf die Formatierung der Zelle zurückzuführen, die die Funktion enthält Effect.

Daher kann das Problem gelöst werden, indem die Zelle in Prozent mit Dezimalstellen formatiert wird.

Um dies zu tun:

1. Wählen Sie die Zellen aus, die als Prozentsatz formatiert werden sollen.
2. Öffnen Sie das Dialogfeld „Zellen formatieren“ mit einer der folgenden Methoden:
   • Klicken Sie mit der rechten Maustaste auf die ausgewählte Zelle oder den ausgewählten Bereich und wählen Sie die Option aus Zellen formatieren… aus dem Kontextmenü;
   • Klicken Sie auf der Registerkarte in der Zahlengruppierung auf den Dialogbox-Starter Startseite Excel-Menüband;
   • Verwenden Sie die Tastenkombination STRG-1 (d. h. wählen Sie die STRG-Taste und wählen Sie bei gedrückt gehaltener Taste die Taste „1“ (Eins).
3. Im Dialogfeld „Zellen formatieren“:
   • Stellen Sie sicher, dass die Karte vorhanden ist Anzahl oben im Dialogfeld ist ausgewählt.
   • wählen Prozentsatz von der Liste Kategorie auf der linken Seite des Dialogfelds .Dadurch werden auf der rechten Seite des Kontrollkästchens zusätzliche Optionen angezeigt, mit denen Sie die Anzahl der Dezimalstellen auswählen können, die angezeigt werden sollen.
   • Nachdem Sie die Anzahl der anzuzeigenden Dezimalstellen ausgewählt haben, klicken Sie auf OK .

Nominal

die Funktion Nominal Excel gibt den Nominalzinssatz für einen bestimmten Effektivzinssatz und eine bestimmte Anzahl von Zinsperioden pro Jahr zurück.

Die Syntax der Funktion lautet:

Syntax

= NOMINAL( effect_rate, npery )

Themen

• effect_rate: Der effektive Zinssatz (ein numerischer Wert zwischen 0 und 1).
• npery: Die Anzahl der Zinsperioden pro Jahr (muss eine positive ganze Zahl sein).

Beispiel

In der folgenden Tabelle ist die Funktion Nominal Mithilfe von Excel wird der Nominalzins von drei Krediten mit unterschiedlichen Laufzeiten berechnet.

Ercole Palmeri