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.

Zellen mit Formeln schützen

Nehmen wir an, Du möchtest ein Spesenformular generieren. Unbedingt vorhanden sein muss eine Kilometerabrechnung für Fahrten mit einem Privatwagen sowie unterschiedliche Mehrwertsteuersätze. Im besten Fall werden solche Berechnungen direkt im Formular hinterlegt, ohne dass der Benutzer viel überlegen und selbst kalkulieren muss - somit reduzierst Du potentielle Fehler im Vornherein. Ob man dadurch gewisse Checks vernachlässigt und etwas übersieht, das ist ein ganz anderes Thema. Wie dem auch sei: Da an allen Formeln nichts mehr geändert werden soll, wollen wir diese Zellen spezifisch schützen. Dazu gehst Du wie folgt vor:

  • Markiere zuerst Dein gesamtes Tabellenblatt ("CTRL + A" oder im linken oberen Rand neben Spalte A und Zeile 1) und gehe mittels Rechtsklick in die Optionen "Zellen formatieren". Unter dem Register "Schutz" kannst Du zunächst für sämtliche Zellen das "Gesperrt" entfernen:

ZellenFormatierenGesperrt

  • Als Nächstes stelle bitte sicher, dass nach wie vor alle Zellen markiert sind. Gehe dann mittels "CTRL + G" in den "Gehe zu ..."-Dialog und dort auf "Inhalte..." (alternativ kannst Du mit der Maus via "Start" → "Suchen und Auswählen" → "Inhalte auswählen" dorthin gelangen). Wähle die Option "Formeln" aus und klicke auf OK. Nun sind in Deinem gesamten Tabellenblatt alle Zellen mit Formeln selektiert worden.

InhalteFormeln

  • Gehe wie in Schritt 1 erneut in die Zellenformatierung (das geht übrigens auch mit der Tastenkombination "CTRL + 1") und setze den Haken bei "Gesperrt" für diese Zellen im Register "Schutz" erneut.
  • Daraufhin aktivierest Du den Blattschutz im Menüband unter "Überprüfen" → "Blattschutz aktivieren" und entfernst den Haken bei "Gesperrte Zellen auswählen":

BlattSchutzGesperrteZellen

Nach diesen wenigen Schritten können generell die Zellen mit Formeln nicht mehr bearbeitet und selektiert werden.

Bezüge und Formeln in einem Textfeld

In Diagrammen kann man beispielsweise mit der "Datenbeschriftung" Werte anzeigen lassen. Unter Umständen möchte man jedoch zusätzliche Informationen präsentieren, wie beispielsweise die prozentuale Abweichung zwischen zwei Säulen. In einem solchen Fall eignen sich meist Textfelder, da diese unkompliziert eingefügt werden können. Falls sich jedoch die Datenbasis zwischenzeitlich ändert, muss man sich über diese manuell eingegebenen Texte stets bewusst sein und diese ebenfalls anpassen. In diesem Beitrag wollen wir anschauen, wie Du Formeln oder Bezüge in ein Textfeld eingeben kannst, damit zumindest der Wert aus Deiner Datenbasis direkt übernommen wird. Allfällige Verschiebungen des Textfeldes (falls sich die Säulen auch in der Höhe verändern) müsstest Du nach wie vor von Hand vornehmen.

Um eine Formel oder einen Bezug zu erstellen, kannst Du ja einfach "=A1" in eine Zelle schreiben. Wenn Du das in einem Textfeld machst, hast Du weniger Erfolg. Der Trick ist, das Textfeld zu aktivieren und dann in der Eingabeleiste "=A1" einzugeben – und schon hast Du einen Bezug/eine Formel erstellt! Du kannst dies auch andersrum ausprobieren: Eingetragene Texte in Deinem Textfeld werden nicht in der Eingabeleiste oben angezeigt.

 

Du kannst demnach ein Textfeld in Dein Diagramm einbetten, neben Deiner Datenbasis die entsprechenden Berechnungen zu Abweichungen oder Kommentare etc. vornehmen und gemäss dem vorherigen Abschnitt Deinen Bezug/Deine Formel erstellen. Als einfaches Beispiel findest Du in der Abbildung unten eine fiktive Zahlenbasis, die Veränderung der Säulen in % sowie das entsprechende Diagramm dazu. Das Textfeld hebt die ermittelte prozentuale Veränderung hervor. Die Pfeile habe ich wie auch die Textfelder via "Einfügen" → "Formen" erstellt und entsprechend formatiert.

 DiagrammFormelinTextfeld