Formeln

In dieser Themengruppe sind Anleitungen zur Anwendung von einzelnen Formeln eingeordnet.

Datenanalyse mit Excel: Tipps für Power Query und Power Pivot

Roman Allgemein, Formeln, Pivot , , ,

Excel ist längst nicht mehr nur eine einfache Tabellenkalkulation. Mit "Power Query" und "Power Pivot" kannst Du Daten aus verschiedenen Quellen importieren, verwalten und analysieren. Das erlaubt fundierte Entscheidungen und spannende Einblicke in die Daten (respektive die daraus gewonnenen Informationen). In diesem Beitrag werde ich Dir einen kurzen Einblick in die Welt der fortgeschrittenen Datenanalyse mit Excel geben und einige Tipps für die effektive Nutzung der Tools "Power Query" und "Power Pivot" vorstellen.

Power Query ist ein Tool, das es Dir ermöglicht, Daten aus unterschiedlichen Quellen wie Datenbanken, Textdateien, Webseiten und mehr zu importieren und zu transformieren. Mit Power Query können Daten bereinigt und für Analysen vorbereitet werden, ohne dass komplexe Formeln geschrieben werden müssen. Power Pivot ist ein leistungsstarkes Datenmodellierungstool, das es Dir ermöglicht, grosse Mengen an Daten aus verschiedenen Quellen zusammenzuführen und zu analysieren. Es ermöglicht die Erstellung von Beziehungen zwischen Tabellen und die Durchführung komplexer Berechnungen mit einer benutzerfreundlichen Oberfläche.

Somit bietet der Einsatz von Power Query und Power Pivot eine Vielzahl von Vorteilen für Deine Datenanalyse in Excel: Effizienzsteigerung, Datenintegration, leistungsstarke Berechnungen und interaktive Analysen. Neben Zeitersparnissen und der Reduktion von Fehlern durch Direktanbindungen kannst Du komplexe Berechnungen durchführen, die über die Funktionen von Excel hinausgehen - beispielsweise durch die Erstellung von DAX-Formeln (Data Analysis Expressions). Es lassen sich interaktive Dashboards und Berichte erstellen, die auf dynamische Weise Analysen und Einblicke in Deine Daten ermöglichen.

Unterschied zu klassischen Pivot-Tabellen

Klassische Pivot-Tabellen in Excel bieten eine Möglichkeit, Daten zu analysieren und Zusammenfassungen zu erstellen, indem sie Zeilen- und Spaltendaten aggregieren. Während sie für grundlegende Analysebedürfnisse nützlich sind, haben sie ihre Grenzen, insbesondere wenn es um die Verarbeitung grosser Datenmengen und die Durchführung komplexer Berechnungen geht. Power Pivot bietet im Vergleich dazu erweiterte Funktionen und Flexibilität. Es ermöglicht nicht nur die Verbindung von Daten aus verschiedenen Quellen, sondern wie erwähnt auch die Erstellung von Beziehungen zwischen Tabellen und die Durchführung komplexer Berechnungen über DAX-Formeln. Darüber hinaus können mit Power Pivot umfassendere Analysemodelle erstellt werden, die eine detailliertere Untersuchung der Daten ermöglichen.

Tipps für den Einstieg

Für Einsteiger in die Nutzung von Power Query und Power Pivot gibt es einige Tipps, die Deinen Start erleichtern. Allen voran die praktische Anwendung und das Experimentieren: Beginne mit kleinen Datenmengen und übe die Anwendung von Power Query und Power Pivot in realen Szenarien, um dein Verständnis zu vertiefen. Experimentiere und nutze die verschiedenen Funktionen und Optionen von Power Query und Power Pivot, um zu verstehen, wie Du deine Daten am effektivsten analysieren und präsentieren kannst. So erhältst Du ein Grundverständnis, was möglich ist und kannst im Nachgang weitere (Online-)Ressourcen konsultieren, Übungen nachahmen und fortgeschrittenere Lerneinheiten absolvieren.

Statistikformeln und Datenanalyse

Roman Allgemein, Formeln , , , ,

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.

Text verketten (mit und ohne leeren Inhalten)

Roman Formeln ,

Vor einiger Zeit wurde hier das Verketten (Aneinanderreihen) von mehreren Spalteninhalten dargestellt. Hierzu wurde die Formel "=VERKETTEN" verwendet (siehe dieser Beitrag). Dabei wurde jeweils das Trennzeichen zwischen jeder Einzelzelle gesetzt. Die Formel "=TEXTVERKETTEN" jedoch ermöglicht es uns, ein generelles Trennzeichen zu definieren, mit welchem wir die zusammengefügte Zeichenfolge trennen möchten. Das kann ein Leerzeichen sein oder auch ein Sonderzeichen wie ",", ";" oder auch "|". Das kennen wir insbesondere, wenn wir eine "csv-Datei" (comma-separated values) erstellen oder anbinden möchten.

Zur Illustration haben wir folgende Datentabelle:
Textverketten_raw

Wenn wir diese Inhalte nun miteinander verketten möchten, fügen wir folgende Formel in der Zelle "F2" ein:

=TEXTVERKETTEN("|";FALSCH;A2:E2)

Als Trennzeichen haben wir das "Pipe-Symbol" ("|") definiert. Danach folgt eine wichtige Entscheidung mittels Angabe von "WAHR" oder "FALSCH", nämlich ob wir leere Zellen ignorieren möchten. Das Resultat sehen wir sogleich. Es ist für uns nicht dienlich, allfällige leere Zellen zu ignorieren (z.B. wenn kein Kommentar eingefügt wurde). Denn das führt lediglich dazu, dass jemand bei der Anbindung der Daten im Nachgang eine falsche Spaltenzuweisung haben könnte.

Das aggregierte Resultat sieht demnach so aus:
Textverketten_FALSCH

 Wie Du im Beispiel sehen kannst, sind die verketteten Einträge zum Dienstag, Mittwoch, Donnerstag und Freitag ohne Kommentar. Sie verfügen jedoch über zwei Trennzeichen zwischen "Temp." (Temperatur) und "Eintrag durch". Hätten wir in der Formel anstelle dessen "WAHR" aufgeführt und leere Zellen ignoriert, wäre das Resultat wie folgt:
Textverketten_WAHR