Statistikformeln und Datenanalyse

Datenanalysen sind in vielen Bereichen unverzichtbar geworden. Um das volle Potenzial von Excel auszuschöpfen, ist es wichtig, einige grundlegende Statistikformeln zu verstehen. In diesem Blogbeitrag stelle ich Dir einige wichtige Formeln vor, erläutere ihre Syntax und zeige jeweils zwei mögliche Anwendungsfälle auf.

Standardabweichung (STABW)

Die Standardabweichung ist ein Mass für die Streuung von Datenpunkten um den Durchschnitt (auf die wohl allbekannte Formel "MITTELWERT" gehe ich hier nicht ein), wobei es eine Unterscheidung zwischen STABW.N (Grundgesamtheit) und STABW.S (Stichprobe) gibt.

Syntax

=STABW.S(Zahl1; Zahl2; ...)

Die Funktion berechnet die Standardabweichung einer Gruppe von Zahlen.

Anwendungsfälle

  1. Qualitätskontrolle: In der Produktion kann die Funktion verwendet werden, um die Streuung von Produktionsmessungen zu analysieren und die Qualität zu überwachen.
    Beispielsweise: "=STABW.S(3; 4; 2; 5; 3)"
  2. Finanzanalyse: Bei der Analyse von Finanzdaten kann die Standardabweichung verwendet werden, um das Risiko einer bestimmten Investition zu bewerten.

Korrelationskoeffizient (KORREL)

Der Korrelationskoeffizient misst die Stärke und Richtung der linearen Beziehung zwischen zwei Variablen respektive zwei Bereichen.

Syntax

=KORREL(Matrix1; Matrix2)

KORREL berechnet den Korrelationskoeffizienten zwischen zwei Datenbereichen.

Anwendungsfälle

  1. Marktforschung: In der Marktforschung wird der Korrelationskoeffizient verwendet, um die Beziehung zwischen Werbeausgaben und Verkaufszahlen zu analysieren.
    Beispielsweise: "=KORREL(A2:A13; B2:B13)", wobei Spalte A die Werbeausgaben für 12 Monate beinhaltet, Spalte B die Verkaufszahlen.
  2. Sportanalyse: Bei der Analyse von sportlichen Leistungen zeigt der Korrelationskoeffizient den Zusammenhang zwischen Trainingsstunden und Leistungsergebnisse auf.

Quantile (QUANTIL.INKL)

Die QUANTIL.INKL-Funktion gibt den Wert zurück, unter welchem eine gegebene Prozentsatzmenge der Daten liegt.

Syntax

=QUANTIL.INKL(Bereich; K)

Die Funktion QUANTIL-INKL gibt das Quantil (K) einer Datenmenge zurück, wobei K im Bereich von 0 bis 1 liegt. Das Quantil entspricht dem Wert, unter welchem eine bestimmte Prozentsatzmenge der Daten fällt. (Es gibt zudem die Option "QUANTIL-EXKL", wobei K ausgeschlossen und nicht eingeschlossen ist.)

Anwendungsfälle

  1. Einkommensverteilung: Um das k-te Quantil des Einkommens in einer Bevölkerung zu berechnen.
    Beispielsweise: "=QUANTIL.INKL(A2:A100; 0.9)"
  2. Leistungsanalyse: Um den unteren Leistungsstand zu identifizieren, kann das 25. Quantil von Prüfungsergebnissen ermittelt werden.

Regressionsanalyse (RGP)

Die RGP-Funktion führt eine einfache lineare Regression durch und gibt eine Analyse der Regressionsstatistik zurück. Dies dient zur grafischen Darstellung der linearen Beziehung einer unabhängigen und einer abhängigen Variablen.

Syntax

=RGP(Y-Werte; X-Werte; Konstante; Statistik)

Die Funktion berechnet die Regressionsstatistik für eine Gruppe von Datenpunkten und gibt verschiedene Statistiken zurück, einschliesslich der Regressionsgeraden und dem Bestimmtheitsmass (R²).

Anwendungsfälle

  1. Verkaufsprognose: Um den Zusammenhang zwischen Werbeausgaben (X) und Verkaufszahlen (Y) zu analysieren und darzustellen, kommt die Regressionsanalyse (RGP-Funktion) zum Einsatz.
    Beispielsweise: "=RGP(B2:B13; A2:A13; WAHR; WAHR)", wobei Spalte A die Werbeausgaben für 12 Monate beinhaltet, Spalte B die Verkaufszahlen.
  2. Wettervorhersage: Meteorologen verwenden die Funktion, um den Zusammenhang zwischen der Temperatur (X) und der Niederschlagsmenge (Y) zu untersuchen.

Die Anwendung dieser Formeln erweitert Deine Fähigkeit in der Datenanalyse mit Excel und ermöglichen es, fortgeschrittenere Einblicke in Deine Daten zu gewinnen. Experimentiere mit verschiedenen Datensätzen und passe die Formeln auf Deine spezifischen Analyseanforderungen an.

Daten transponieren (Formel ZEILENUMBRUCH)

Angenommen Du erhältst Daten in einer ungünstigen Struktur. Womöglich kennst Du die Funktion "Transponieren", indem Du die Daten markierst, kopierst und mittels "transponieren" einfügst. Dies "dreht" Deinen Bericht von einer Zeilen- zu einer Spaltenansicht (oder vice versa). Siehe hierzu auch diesen Beitrag: Einen Bericht drehen (Spalten zu Zeilen - Transponieren).

Für diesen Beitrag haben wir das untenstehende Datenset, mit zufälligen Adressdaten erhalten:

Daten transponieren - Beispieldaten

Wir können die Daten nicht weiterverwenden und müssen sie zuerst in dedizierte Spalten "Name", "Adresse" und "Telefon" bringen. Welche Lösungsansätze bieten sich uns? Mittels eingangs erwähntem "Transponieren" kommen wir nicht weiter; respektive nur ansatzweise (Eintrag für Eintrag). Ein manueller Übertrag kommt für uns natürlich nicht in Frage. Ein Makro mit einer For Each-Schleife würde funktionieren, es bietet sich jedoch mit einer einzigen Excel-Formel eine Lösung an: Die Formel "=ZEILENUMBRUCH" kann die Daten direkt für uns aufbereiten. Dies ist die Formel-Syntax:

Formel Zeilenumbruch-Syntax

Konkret können wir unsere Daten (die übrigens in den Zellen A1:A45 sind), mit folgender Formel aufbereiten lassen:

=ZEILENUMBRUCH(A1:A45;3)

Der "Vektor" beinhaltet der Bereich, wo unsere Daten aufgeführt sind. Die zweite Angabe, der "wrap_count" definiert, nach wie vielen Einträgen Excel jeweils einen "Zeilenumbruch" einfügen soll, um auf der nächsten Zeile fortzufahren.

Daten transponieren - Result

Die Formel hat eine optionale Angabe "pad_with". Falls Du im Allgemeinen eine grosse Datenmenge zusammenführen möchtest und in der letzten Zeile nicht alle Einträge gefüllt werden, erhältst Du einen "#N/V"-Fehler. Mit der "pad_with"-Angabe kannst Du einen Wert dafür definieren (z.B. "NULL", "LEER", etc.).

Anstelle von "ZEILENUMBRUCH" könntest Du je nach Anwendungsfall auch "SPALTENUMBRUCH" verwenden; das Konzept ist ähnlich wie beim "SVERWEIS" und dem "WVERWEIS". Falls Du Excel auf Englisch benutzt, wären dies die Formeln "WRAPROWS" (ZEILENUMBRUCH) oder "WRAPCOLS" (SPALTENUMBRUCH), Rows für Zeilen, Cols für Spalten (Columns).

Die zufällig generierten "Testdaten" habe ich im Übrigen mit UiPath und dem "Testing" Aktivitäten-Package aufbereitet (UiPath.Testing.Activites). Es sind dies die Aktivitäten GivenName, LastNameAddress und RandomNumber.

Datumsaggregation und -gliederung in Pivot-Tabellen

Vor einiger Zeit habe ich bereits einmal über die automatische Datumsaggregation in Pivot-Tabellen (in Excel 2016) getextet. Ich zeige Dir hier auf, wie Du selbst steuern kannst, auf welcher Ebene Du ein Datum aggregiert und gegliedert haben möchtest - auch in älteren Excel-Versionen. Wähle dazu zuerst die entsprechenden Felder, die Du im Bericht darstellen möchtest:

pivotfeldlistedatumaggregation

Klicke als nächstes auf Deine Pivot-Tabelle und selektiere eine Zelle mit einem Datum:

datumselektierendatumaggregation

Daraufhin gehst Du im Menüband unter dem Punkt "Daten" auf "Gruppieren":

gruppierendatumaggregation

In der nächsten Maske kannst Du Deine Gliederung der Daten beliebig wählen - klicke einfach auf die entsprechenden Schaltflächen und bestätige mit "OK":

gruppieren2datumaggregation

Das Ergebnis ist in diesem Falle, dass wir die Daten auf Monate, Quartale und Jahre aggregiert haben und alle Gruppierungen in einer separaten Zeile zum Auf- und Zuklappen haben:

gruppierenergebnisdatumaggregation