Moderne Datenanalyse in Excel

Kapitel 1. Tabellen: Das Portal zum modernen Excel

Diese Arbeit wurde mithilfe von KI übersetzt. Wir freuen uns über dein Feedback und deine Kommentare: [e-mail]

Excel verfügt über eine Vielzahl von Analysewerkzeugen, die es schwierig machen können, den besten Ausgangspunkt zu finden. Ein grundlegender Schritt ist jedoch die Beherrschung der Excel-Tabelle. Dieses Kapitel befasst sich mit den wesentlichen Elementen von Excel-Tabellen, die als Grundlage für Power Query, Power Pivot und weitere in diesem Buch vorgestellte Tools dienen. Es unterstreicht außerdem die Bedeutung der sorgfältigen Organisation von Daten in einer Tabelle. Um sich mit dem Inhalt dieses Kapitels zu beschäftigen, navigiere zu ch_01.xlsx im Ordner ch_01, der sich im Begleitarchiv des Buches befindet.

Tabellenüberschriften erstellen und darauf verweisen

Ein Datensatz ohne Spaltenüberschriften ist praktisch nutzlos, da ihm ein sinnvoller Kontext fehlt, um die Messwerte der einzelnen Spalten zu interpretieren. Leider ist es nicht ungewöhnlich, dass Datensätze gegen diese Grundregel verstoßen. Excel-Tabellen sind eine wertvolle Erinnerung daran, dass die Qualität eines Datensatzes von klaren und informativen Überschriften abhängt.

Im Arbeitsblatt start von ch_01.xlsx stößt du in den Spalten A:F auf Daten ohne entsprechende Überschriften, die sich derzeit in den Spalten H:M befinden. Dieses Design ist alles andere als optimal. Um es anzupassen, klickst du auf eine beliebige Stelle in der primären Datenquelle und gehst über das Menüband zu Einfügen → Tabelle → OK, wie in Abbildung 1-1 dargestellt. Alternativ kannst du in der Datenquelle auch Strg+T oder Strg+L drücken, um das gleiche Dialogfeld Tabelle erstellen zu öffnen.

Abbildung 1-1. Konvertieren der Datenquelle in eine Tabelle

Im Dialogfeld "Tabelle erstellen" wirst du automatisch aufgefordert, anzugeben, ob deine Daten Kopfzeilen enthalten. Derzeit ist das nicht der Fall. Wenn keine Kopfzeilen vorhanden sind, wird dem Datensatz automatisch eine Reihe von Kopfspalten mit den Namen Column1, Column2 usw. zugewiesen.

Von hier aus kannst du die Überschriften der Spalten H:M ausschneiden und in die Haupttabelle einfügen, um zu verdeutlichen, was in jeder Spalte gemessen wird, wie in Abbildung 1-2 zu sehen ist.

Abbildung 1-2. Excel-Tabelle mit Überschriften

Kopfspalten in Excel-Tabellen nehmen eine besondere Rolle im Datensatz ein. Sie sind zwar Teil der Tabelle, fungieren aber als Metadaten und nicht als Daten selbst. Excel-Tabellen bieten die Möglichkeit, programmatisch zwischen Überschriften und Daten zu unterscheiden, im Gegensatz zu klassischenExcel-Formeln.

Um diesen Unterschied in Aktion zu sehen, gehe zu einer leeren Zelle in deinem Arbeitsblatt und gib das Gleichheitszeichen ein. Zeigen Sie auf die Zellen A1:F1 als Referenz, und Sie werden feststellen, dass die Formel zu Table1[#Headers] wird.

Du kannst diese Referenz auch in anderen Funktionen verwenden. UPPER() Zum Beispiel kannst du verwenden, um die Groß- und Kleinschreibung aller Kopfzeilen dynamisch zu konvertieren, wie als in Abbildung 1-3.

Abbildung 1-3. Excel-Kopf-Referenzformeln

Anzeigen der Tabellenfußzeilen

So wie und jede Geschichte einen Anfang, eine Mitte und ein Ende hat, besteht auch jede Excel-Tabelle aus Kopf-, Daten- und Fußzeilen. Die Fußzeilen müssen jedoch manuell aktiviert werden. Dazu klickst du irgendwo in die Tabelle, navigierst im Menüband zu Tabellendesign und wählst in der Gruppe Tabellenstiloptionen die Option Gesamtzeile, wie in Abbildung 1-4 dargestellt.

Abbildung 1-4. Hinzufügen von Fußzeilen zu einer Tabelle

Standardmäßig berechnet die Gesamtzeile in einer Tabelle die Summe der letzten Spalte in deinen Daten, in diesem Fall frozen. Du kannst dies jedoch anpassen, indem du auf das Dropdown-Menü in der Fußzeile einer beliebigen Spalte klickst. So kannst du zum Beispiel den maximalen Verkaufsbetrag der Kategorie fresh ermitteln, wie in Abbildung 1-5 dargestellt.

Abbildung 1-5. Anpassen der Fußzeilen einer Excel-Tabelle

Tabelle 1-1 fasst die wichtigsten Formelverweise für die wichtigsten Komponenten von Excel-Tabellen zusammen, wobei wir davon ausgehen, dass die Tabelle Table1 heißt.

Tabelle 1-1. Zusammenfassung der Excel-Tabellenformel-Referenzen

Formel	Worauf sie sich bezieht
=Table1[#Headers]	Tabellenüberschriften
=Table1	Tabelle Daten
=Table1[#Totals]	Tabellenfußzeilen
=Table1[#All]	Tabellenköpfe, Daten und Fußzeilen

Wenn du mit deinen Excel-Tabellenkenntnissen vorankommst, wirst du weitere hilfreiche Formelverweise entdecken, die auf der grundlegenden Struktur von Kopfzeilen, Körper, und Fußzeilen basieren.

Excel-Tabellen benennen

Excel Tabellen bieten den Vorteil, dass sie die Verwendung von benannten Bereichen erzwingen, was einen strukturierteren Ansatz bei der Arbeit mit Daten fördert. Obwohl der Verweis auf Table1 eine Verbesserung gegenüber der Verwendung von Zellkoordinaten wie A1:F22 darstellt, ist es besser, einen beschreibenden Namen zu wählen, der widerspiegelt, was die Daten darstellen.

Dazu gehst du im Menüband auf die Registerkarte Formeln, wählst in der Gruppe Definierte Namen die Option Namensmanager und wählst für den Namen Table1 die Option Bearbeiten. Ändere den Namen in sales, und klicke dann auf OK. Abbildung 1-6 zeigt, wie dein Namensmanager nach dieser Änderung aussehen sollte.

Abbildung 1-6. Namensmanager in Excel

Wenn du den Namensmanager schließt, wirst du feststellen, dass alle Verweise auf Table1 automatisch auf den neuen Namen aktualisiert wurden: sales.

Excel-Tabellen formatieren

Als Excel-Nutzer weißt du, wie wichtig es ist, Daten in einem ansprechenden Format darzustellen. Tabellen können die Optik deines Arbeitsblatts im Handumdrehen verbessern. Mit Tabellen kannst du ganz einfach umrandete Zeilen, farbige Überschriften und vieles mehr hinzufügen. Um das Aussehen deiner Tabelle anzupassen, klicke auf eine beliebige Stelle in der Tabelle und gehe im Menüband zu Tabellendesign. In Abbildung 1-7 findest du verschiedene Optionen, wie z. B. das Ändern der Tabellenfarben oder das Ein- und Ausschalten von Banded Rows.

Abbildung 1-7. Anpassungsoptionen für den Tischentwurf

Tabellenbereiche aktualisieren

Mit Excel-Tabellen wird das Problem, dass Summen falsch werden, wenn Daten hinzugefügt oder entfernt werden, effektiv gelöst. Dank der strukturierten Verweise auf passen sich die Formeln nahtlos an die Änderungen in den Daten an und sorgen für Genauigkeit. Außerdem wird die Gesamtsumme am unteren Ende der Tabelle automatisch aktualisiert, um diese Änderungen widerzuspiegeln, und sie kann leicht von externen Referenzen ausgeschlossen werden, damit die Integrität deinerBerechnungen erhalten bleibt.

Berechne die Summe der Spalte fresh mit Hilfe der strukturierten Formel =SUM(sales[fresh]). Microsofts IntelliSense erleichtert diesen Prozess, indem es dir ermöglicht, Namen während der Eingabe effizient zu vervollständigen. Experimentiere mit dem Hinzufügen oder Entfernen von Zeilen oder dem Ändern der fresh Daten in der Tabelle sales. Du wirst feststellen, dass sich die Funktion zur Berechnung des Gesamtumsatzes fresh dynamisch aktualisiert und die Genauigkeit beibehält.

Wenn du dich auf die Daten mit ihrem Namen statt mit der Position der Zelle beziehst, werden mögliche Probleme mit Formeln minimiert, die durch die Änderung der Tabellengröße und -platzierung entstehen. Tabellen sind auch wichtig, um Probleme wie fehlende Daten in einer PivotTable zu vermeiden, wenn neue Zeilen hinzugefügt werden.

Daten für die Analyse organisieren

Tabellen sind zwar wertvoll, aber ein noch wichtigerer Aspekt für eine mühelose und genaue Datenanalyse ist die Speicherung der Daten in der richtigen Form.

Sieh dir die Tabelle sales als Beispiel an. Wenn du versuchst, eine PivotTable zu erstellen, um den Gesamtumsatz nach Region zu berechnen, stellt das Format, in dem die Daten gespeichert sind, eine Herausforderung dar. Idealerweise sollten alle Umsatzinformationen in einer einzigen Spalte zusammengefasst werden. In der aktuellen Konfiguration gibt es jedoch für jede Abteilung eine eigene Umsatzspalte: fresh, grocery, und frozen. Excel erkennt nicht, dass diese Spalten alle die gleiche Kennzahl, nämlich den Umsatz, darstellen.

Der Grund, warum diese und viele andere Datensätze schwer zu analysieren sind, liegt darin, dass sie nicht in einem Format gespeichert sind, das für die Analyse geeignet ist. Die Regeln für aufgeräumte Daten bieten eine Lösung. Während Hadley Wickham in seinem gleichnamigen Aufsatz von 2014 drei Regeln aufstellt, konzentriert sich dieses Buch auf die erste: Jede Variable bildet eine Spalte.

Der Datensatz sales verstößt gegen die Regel der ordentlichen Daten, da er in jeder Zeile mehrere Einträge für dieselbe Variable, field, in verschiedenen Abteilungen enthält. Eine hilfreiche Faustregel lautet: Wenn mehrere Spalten dasselbe messen, sind die Daten wahrscheinlich nicht aufgeräumt. Wenn du die Daten in ein ordentliches Format umwandelst, wirddie Analyse deutlicheinfacher.

In Abbildung 1-8 siehst du einen Vergleich des Datensatzes vor und nach der Umwandlung, der die verbesserte Ordnung und Einfachheit der Analyse verdeutlicht. In Kapitel 4 erfährst du, wie du diese grundlegende Transformation eines Datensatzes mit nur wenigen Klicks durchführen kannst. In der Zwischenzeit kannst du das Arbeitsblatt sales-tidy in ch01_solutions.xlsx erkunden, das bereits umgewandelt wurde. Sieh es dir an, um zu sehen, wie viel einfacher es ist, den Gesamtumsatz nach Regionen zu ermitteln.

Abbildung 1-8. Großhandelskunden, vor und nach dem Aufräumen

Fazit

Dieses Kapitel hat die Grundlagen für die effektive Nutzung von Excel-Tabellen gelegt. Wenn du dich eingehender mit der Maximierung des Potenzials von Tabellen befassen möchtest, einschließlich der Anwendung von strukturierten Verweisen zur Formulierung von berechneten Spalten, lies Excel-Tabellen: A Complete Guide for Creating, Using, and Automating Lists and Tables von Zack Barresse und Kevin Jones (Holy Macro! Books, 2014). Dieses Kapitel befasst sich außerdem mit der sorgfältigen Organisation von Daten, einem grundlegenden Aspekt jedes erfolgreichen Datenanalyseprojekts in Excel. Kapitel 2 bietet eine Einführung in die Vereinfachung der Datenumwandlung mit Power Query.

Übungen

Um Daten in Excel-Tabellen zu erstellen, zu analysieren und zu bearbeiten, bearbeitest du die Übungen mit dem Datensatz penguins, der sich in ch_01_exercises.xlsx im Ordner exercises\ch_01_exercises in der Begleitmappe des Buches befindet:

1. Konvertiere die Daten in eine Tabelle namens penguins.

2. Verwende eine Formelreferenz, um jede Spaltenüberschrift groß zu schreiben.

3. Erstelle eine neue Spalte namens bill_ratio, indem du bill_length_mm durch bill_depth_mm teilst.

4. Füge eine Gesamtzeile ein, um den Durchschnitt zu berechnen body_mass_g.

5. Entferne die Bandreihengestaltung aus der Tabelle.

Die Lösungen findest du in der Datei ch_01_exercise_solutions.xlsx, die sich im gleichen Ordner befindet.