4. Datenanalyse mit Excel: Tools, Funktionen, Features und Einsatzbereiche

Was versteht man unter Datenanalyse?

Datenanalyse bezeichnet den Prozess der Untersuchung, Bereinigung, Transformation und Modellierung von Daten, um Informationen zu entdecken, Schlussfolgerungen zu ziehen und Entscheidungen zu unterstützen. Sie umfasst verschiedene Techniken und Methoden, die statistische, mathematische und algorithmische Ansätze verwenden.

Zu den grundlegenden Schritten gehören

1. das Sammeln von Daten,
2. die Datenbereinigung zur Entfernung von Fehlern und Unvollständigkeiten sowie
3. die Datenvisualisierung zur Darstellung der Ergebnisse.

Ziel einer Datenanalyse ist es, Muster, Trends und Beziehungen innerhalb der Daten zu erkennen:

• Deskriptive Analysen helfen dabei, den aktuellen Zustand der Daten zu verstehen,
• Prädiktive Analysen sollen zukünftige Trends vorhersagen.
• Explorative Datenanalysen (EDA) dienen dazu, erste Einblicke (Zusammenhänge) in die Daten zu gewinnen und Hypothesen zu generieren.
• Maschinelles Lernen und künstliche Intelligenz können eingesetzt werden, um komplexere Muster und Vorhersagen zu erkennen.

Diverse Power Tools ermöglichen die Analyse von Daten jenseits der Grenze von 1,5 Mio. Datensätzen. Für viele statistische Verfahren gibt es einfach anzuwendende Funktionen. Sind die anzuwendenden statistischen und mathematischen Verfahren komplexer, steigt auch der Aufwand der Modellerstellung in Excel und die Gefahr von Fehlern. In diesen Fällen ist zu überlegen, ob Excel nicht um Add-ins erweitert und so der Umfang erweitert wird.

Welches Excel-Feature für welche Datenanalyse?

Welche Arten von Analysen lassen sich mit Excel-Features durchführen? Jede! Daher im Folgenden ein paar davon vorstellt und beschrieben.

Pivot-Tabelle: Excel-Feature zur explorativen Datenanalyse

Eine Pivot-Tabelle strukturiert mit geringem Aufwand Daten in Tabellenform, fasst sie zusammen und ermöglicht deren Auswertung. Für die Erstellung einer Pivot-Tabelle sind nur wenige Klicks notwendig, um Daten zu strukturieren, zu verdichten und zu analysieren, ohne die Ausgangsdaten durch Sortieren, Filtern usw. zu verändern.

Pivot-Tabellen machen das Erstellen komplexer Verknüpfungen oder Formeln innerhalb von Tabellen überflüssig. Änderungen in einer Pivot-Tabelle haben keine Aktualisierung der Originaldaten zur Folge. Damit erfüllt die Pivot-Tabelle das wichtige Prinzip der Datenkonsistenz.

Der Einsatz von Pivot-Tabellen ist immer dann sinnvoll, wenn eine Datenquelle (z.B. eine große Tabelle mit vielen Daten) ausgewertet werden soll und nicht alle Spalten (Felder) dafür benötigt werden.

Merkmale einer Pivot-Tabelle

• Sämtliche Daten stammen aus einer Ursprungstabelle.
• Pivot-Tabellen fassen Daten zusammen und stellen sie in aggregierter Form dar.
• Ihre Struktur besteht aus mehreren Bereichen. Diese Bereiche können beliebige Felder der Ausgangstabelle aufnehmen.
• Der Anwender bestimmt, nach welchen Feldern gruppiert wird und welche Felder angezeigt werden sollen. Mit Feldern sind in der Logik von Datenbanken bei Excel-Tabellen die Spalten gemeint.
• Eine Pivot-Tabelle aktualisiert sich nicht nach jeder Änderung der Ausgangsdaten. Dazu muss die Pivot-Tabelle manuell oder per Voreinstellung beim Öffnen der Datei automatisch aktualisiert werden.

Mit sogenannten Zeilen- und Spaltenfeldern werden die Ausgangsdaten nach dem jeweils ausgewählten Feld gruppiert. Die Gruppierung erfolgt entweder zeilen- oder spaltenbasiert. Datenfelder stellen die Informationen an den Schnittpunkten von Zeilen und Spalten dar. Über Funktionen wie SUMME() oder ANZAHL() erfolgt die Ermittlung eines Datenwerts. Mit Seitenfeldern ist das Einschränken von Datensätzen auf bestimmte Werte möglich.

Die Pivot-Tabelle ist damit für eine explorative Datenanalyse gut geeignet.

Voraussetzungen für das Erstellen einer Pivot-Tabelle

Die Ausgangsdaten für eine Pivot-Tabelle liefern („Intelligente“) Tabellen oder externe Datenquellen mittels Power Query.

Power Pivot: Excel-Feature zur erweiterten Datenanalyse

Power Pivot scheint aufgrund der Bezeichnung eine sehr leistungsstarke Pivot Tabelle zu sein. Tatsächlich ist sie eine Integration der Microsoft SQL Server Analysis Service (SSAS) in Excel. Technisch gesehen erstellen Sie mit Power Pivot ein Datenmodell, das als lokaler OLAP-Cube generiert wird und mit dem Sie große Datenmengen (32-bit-Variante von Excel maximal 2 GB RAM, 64-bit mehr) analysieren können. Das Ergebnis wird in eine Pivot-Tabelle ausgegeben oder kann mit den CUBE-Funktionen von Excel genutzt werden.

Mit Power Pivot können Sie Daten aus Datenbanken, MS Analysis Service, Text- und Excel-Dateien in ein Datenmodell übernehmen.

Mit Hilfe von DAX-Funktionen (Data Analysis EXpressions) können Sie Berechnungen (Measures) erstellen, die als Teil des Datenmodells gespeichert werden. Bei Aktualisierungen des Modells werden diese Measures im Datenmodell aktualisiert und nicht wie bei der "klassischen" Pivot-Tabelle im Pivot-Cache. So sorgt der Einsatz von DAX-Funktionen für eine bessere Performance und ermöglicht überhaupt erst die Analyse von Millionen von Datensätzen! Interessant sind dabei besondere die Funktionen der Zeitintelligenz. Sie erlauben das Aggregieren (z.B. Year-to-Date) und Vergleichen von Daten über einen in der Power Pivot-Tabelle definierten Zeitraum (z.B. die Funktion SAMEPERIODLASTYEAR).

Wenn allerdings die Daten nicht in den notwendigen Strukturen (Tabellen) aus den Datenquellen) bezogen werden können, müssen Sie zunächst Power Query verwenden und das Ergebnis der Transformation an Power Pivot übergeben (nur ein Klick!). Power Pivot ist auch Teil der Power BI-Plattform. die Erfahrungen mit Power Pivot aus dem Umgang mit Excel-Modellen lassen sich daher gut in Power BI-Projekte einbringen.

Die Datenanalyse aus dem Menü Daten (nur Excel 365) analysiert alle Daten einer Tabelle, deren Zusammenhänge und identifiziert Muster in der Veränderung von Daten. Dieses Feature soll dabei helfen, Trends, Muster und Ausreißer in einem Datensatz schneller identifizieren zu können und den Analyseaufwand zu verringern. Die Ergebnisse werden automatisch in kurzer Zeit in Form von Diagrammen und Kommentaren in einem speziellen Fenster angezeigt. Vergleichbare Ergebnisse würden mit den Statistikfunktionen und dem Add-in für die Regressionsanalyse erheblich länger dauern und aufwändiger sein!

Statistische Analyse mit über 100 Excel-Funktionen

Für eine statistische Analysen bietet Excel ca. 110 Funktionen an. Die sind technisch gesehen einfach zu bedienen, erfordern aber statistisches Know-How. Generell kann man drei Teilbereiche der Statistik unterscheiden:

• Für Verfahren der deskriptiven Statistik werden im Rahmen von Excel Funktionen zur Berechnung von z.B. Mittelwert, Varianz Standardabweichung, Maximum, Minimum, Ränge, Quartile und Häufigkeiten angeboten. Damit kann man die Verteilung und Lage eines Merkmals ermittelt werden.
• Die Ergebnisse einer Datenanalyse sind nicht durch Fehlerwahrscheinlichkeiten abgesichert. Dies kann durch die Methoden der induktiven Statistik erfolgen, sofern die untersuchten Daten den dort unterstellten Modellannahmen (Hypothesen) genügen. Dafür gibt es im Rahmen von Excel Hypothesentests (Gaußtest, T-Tests, F-Test, Student-Test).
• Außerdem stellt Excel statistische Modelle für eine lineare und nichtlineare Regressionsanalyse sowie Trendanalysen zur Verfügung. Varianzanalysen mit einem oder mehreren Faktoren, Korrelationsanalyse nach Pearson und die Fourier Transformation (Zeitreihenanalyse) sind ebenfalls möglich.
• Für die Wahrscheinlichkeitsrechnungen bietet Excel Funktionen zur Generierung von Zufallszahlen, Stichprobenziehungen und von Permutationen an.

Predictive Analytics: Mögliche Erweiterungen für Excel

Möchte man Excel auch für Predictive Analytics nutzen, so ist das mit dem angebotenen Funktionsumfang nur bedingt möglich. Excel bietet bspw. kein Verfahren für Random Forest (ein aus mehreren unkorrelierten Entscheidungsbäumen bestehendes Klassifikations- und Regressionsverfahren), keines für die Warenkorbanalyse und keines für eine Segmentierung (s. Oehler, Predictive Analytics mit Excel und R: Positionierung und erste Schritte, In Andreas Klein (Hrsg.): Controllinginstrumente mit Excel umsetzen, Haufe, 2018).

Daher macht es Sinn hier den Funktionsumfang von Excel um ein Add-in namens BERT zu erweitern. Mit diesem Add-in können die gewünschten Funktionalitäten für Predictive Analytics mit Hilfe der Sprache R selbst programmiert werden. Die Sprache R kann übrigens auch für Anpassungen von Power BI-Desktop verwendet werden.

Ein weiteres AddIn ist Python. Python ist eine Programmiersprache, die in u.a. in Data Science und im Machine Learning (ML) verwendet wird. Generell kann man mit Python Predictive-Analytics-Modelle erstellen. Das Python-Addin sich derzeit in der Vorschauphase und kann derzeit nur von Teilnehmenden des Microsoft 365-Insider-Programms genutzt werden.

Das kann man durch die Nutzung von ChatGPT4.0 umgehen, da hier generell für Datenanalyse Python verwendet wird. Indem man Daten in ChatGPT hochlädt, wäre also eine entsprechende Analyse möglich. Die Ergebnisse können dann wieder in Excel bereitgestellt werden. Allerdings sollte man genau überlegen, ob die entsprechenden Daten mit OpenAI geteilt werden dürfen.

Maschinelles Lernen mit Excel

Durch die Kombination von ChatGPT, Excel und Python können Sie einen effizienten und leistungsfähigen Workflow für maschinelles Lernen schaffen. Excel eignet sich hervorragend für die Datenvorbereitung und Exploration, während Python für die Modellierung und Analyse verwendet wird. ChatGPT kann dabei helfen, diesen Prozess zu unterstützen und zu automatisieren, indem es Anleitungen gibt, Code erstellt und Prozesse orchestriert.

Ist die Analyse von Daten mit Excel noch zeitgemäß? Auf jeden Fall!