This article is available in English on blog.haunschmid.name.

In meinem Beruf habe ich viel mit Datenanalysen und verschiedenen Datenquellen zu tun. Daten können als SQL Dump, XML Files uvm. übermittelt werden. Meistens bekommt man aber doch Excel Files oder eine Textdatei mit Comma separated values (.csv). Deshalb eignet sich Microsoft Excel sehr gut dazu einmal einen ersten Eindruck von seinen Daten zu bekommen und sie für weitere Datenverarbeitung aufzubereiten.

In diesem Post zeige ich wie man ein Datenset, das als Tabelle vorliegt, in Excel anzeigen und filtern und mit Hilfe der Table Tools analysieren kann.

Daten

Für diesen Post habe ich mir ein Datenset vom Machine Learning Repository heruntergeladen: Zum Online Retail (Onlineverkäufe) Datensatz

Die folgenden Spalten sind enthalten: InvoiceNo (Rechnungsnummer), StockCode (Lagercode), Description (Beschreibung – Produktname), Quantity (Anzahl), InvoiceDate (Rechnungsdatum), UnitPrice (Einheitspreis), CustomerID (Kunden ID), Country (Land)

Daten einlesen

Wenn ein Datensatz nicht im Excel Format vorliegt, sondern als .csv (comma separated) oder .tsv (tab separated) und du ihn mit Microsoft Excel öffnen möchtest, kannst du den Text Import Wizard verwenden. Diese Funktion und einige anderen (z.B. Import von HTML oder aus einer Datenbank) findet man im Reiter View (dt.: Ansicht). Unter dem folgenden Link findest du eine genau Anleitung zum Importieren oder Exportieren von Textdateien. Wenn die Trennzeichen mit der Sprache der Excel-Version zusammen passen kann man so ein File natürlich auch mit Rechtsklick > Öffnen mit > Excel … aufmachen.

Verschiedene Arten um Daten zu importieren.

Die ersten 2 Schritte, die ich immer durchführe um mir das Leben leichter zu machen sind:

• Freeze panes (dt.: Fenster einfrieren)
• Die Filterfunktion aktivieren

Fenster einfrieren funktioniert so: Zuerst markiert man die erste Zeile, die nicht mehr markiert werden soll (also normalerweise die zweite Zeile) und dann geht man zum Reiter View (dt.: Ansicht) und klickt auf Freeze panes (dt.: Fenster einfrieren). Wenn man die zweite Zeile mit einfrieren will markiert man die dritte Zeile und klickt dann auf Fenster einfrieren (das gleiche gilt für jede weitere Zeile, die man einfrieren möchte). Diese Funktion führt dazu, dass man die Spaltennamen immer vor Augen hat, auch wenn man in einem großen Dokument nach unten scrollt.

„Fenster einfrieren“ um die Spaltennamen zu fixieren.

Unter Data (dt.: Daten) gibt es die Funktion Filter. Wenn man diese aktiviert erscheint neben jeder Spalte in der ersten Zeile ein Pfeil. Klickt man darauf kann man die Daten nach dieser Spalte filtern oder sortieren. Dies ist auch ganz praktisch um einfach mal zu sehen welche Werte in einer Spalte überhaupt vorkommen.

Die Filter-Funktion ist hilfreich um seine Daten kennen zu lernen.

Tabellenfunktionen

Wenn man länger als ein paar Minuten mit dieser einen Datei in Excel arbeiten möchte, bietet es sich an statt der einfachen Filterfunktion überhaupt eine Tabelle einzufügen, weile diese noch mehr nützliche Funktionen bietet. Zuerst markiert man den Bereich der die Daten enthält und wandelt ihn unter Insert > Table (dt.: Einfügen > Tabelle) zu einer Tabelle um.

Tabelle Einfügen.

Dies hat mehrere Vorteile:

• Filtern & Sortieren wie oben schon gezeigt
• Formatierung: Man behält mit abwechselnd eingefärbten Zeilen einen besseren Überblick
• Viele Funktionen unter Table Tools (weiter unten erklärt)

Übersichtlichere Daten mit der Tabellenfunktionen.

Formeln verwenden

Mit Excel assoziiere ich zuallererst immer die vielen verschiedenen Formeln, die man auf Zellen, Spalten und Zeilen anwenden kann. Sehr nützlich ist in dem Zusammenhang auch die „Zieh“-Funktion mit der man Formeln von einer Zelle in die darunter oder daneben liegenden Zellen kopieren kann, wobei die verwendeten Variablen automatisch aktualisiert werden. In meinem Datenset bietet es sich an, die Quantity (Anzahl) mit dem UnitPrice (Stückpreis) zu multiplizieren um den Gesamtpreis zu berechnen. Ohne Tabelle müsste man =D2*F2 in die nächste freie Spalte schreiben (oder die betroffenen Kästchen auswählen), das Ausfüllkästchen anklicken und bis ganz nach unten ziehen. Bei mehr als 500.000 Zeilen kann das schon eine Weile dauern.

Verwendet man eine Tabelle, ist das ganze viel einfacher. Man klickt einfach in die nächste freie Spalte in der Tabelle, schreibt =[@Quantity]*[@UnitPrice] (oder wählt die gewünschten Spalten mittels Anklicken aus). Der erste Vorteil, der gleich ersichtlich ist, ist dass man die Spaltennamen zum Referenzieren verwenden kann, und die zweite Erleichterung, die eine Tabelle mit sich bringt ist, dass man die „Zieh“-Funktion nicht benötigt. Sobald man die Formel hineingeschrieben hat füllt sich die ganze Spalte von selbst und passt sich auch optisch an die restliche Tabelle an.

Table Tools

Wenn man in irgendeine Zelle innerhalb der Tabelle klickt, erscheint ein neuer Reiter Table (dt.: Tabelle).

Reiter „Tabelle“.

Hier hat man verschiedene Möglichkeiten:

• Mit PivotTable zusammenfassen: Diese Funktion erkläre ich weiter unten genauer.
• Duplikate entfernen
• In Bereich konvertieren
• Datenschnitt einfügen
• Exportieren
• Kopfzeile ein-/ausschalten: Standardmäßig ist die Kopfzeile eingeschaltet und ich sehe auch keinen Grund warum man sie ausschalten sollte, sofern sinnvolle Spaltennamen vorhanden sind. Wenn man hinunter scrollt und die erste Zeile nicht mehr sichtbar ist, erscheinen statt den Spaltenbezeichnungen A, B, C, … die Werte der ersten Zeile.
• Ergebniszeile: Diese Funktion erkläre ich weiter unten genauer.
• Gebänderte Zeilen: Färbt die Zeilen abwechselnd in zwei Farben und macht die Tabelle übersichtlicher.
• Erste Spalte / Letzte Spalte: Druckt die Werte in der ersten / letzten Spalte fett.
• Schältfläche „Filter“: Damit kann man den kleinen Pfeil neben jedem Spaltennamen aktivieren / deaktivieren.

Daten für die weitere Verarbeitung vorbereiten

Bevor ich die Ergebniszeile und die Funktion „Mit PivotTable zusammenfassen“ anwende, filtere ich noch ein paar unbrauchbare Produkte aus, die mir beim darüber scrollen aufgefallen sind. Dafür klickt man in der Spalte „Description“ auf den kleinen Pfeil und entfernt das Häkchen bei allen Werten die man nicht dabei haben will. Bei mir waren das der erste Eintrag weil es sich um eine Zahl handelt, alle, die mit einem ? beginnen (das dürften fehlerhafte Zeilen sein) und ganz unten in der Liste die Zelle „(BLANK)“, dabei handelt es sich um Zeilen ohne Wert.

Unbrauchbare Produkte filtern.

Neben dem Pfeil in der Spalte erscheint ein Trichter sobald man gefiltert hat damit man gleich erkennt, dass nach dieser Spalte gefiltert wurde.

Ergebniszeile

Da sie recht hilfreich ist, möchte ich euch die Funktion Total Row (dt.: Ergebniszeile) näher vorstellen. Um diese zu aktivieren muss man oben einfach die Checkbox bei Ergebniszeile anhaken.

In der Ergebniszeile kann man Funktionen auswählen.

Wenn man nach unten scrollt sieht man schon die Ergebniszeile. In jeder der Zellen kann man nun Funktionen auswählen, die auf die gesamte Spalte angewendet werden. Nicht für jede Spalte macht jede Funktion Sinn. Für die vorhin berechnete Spalte könnte man z.B. den Average (dt.: Durchschnitt) oder die Summe ausrechnen.

Pivot Tabellen

Richtig spannend wird es mit Pivot Tabellen. Pivot bedeutet so viel wie drehen und genauso kann man sich die Operation auch vorstellen. In dieser Tabelle könnte uns interessieren wie viele von jedem Produkt wir verkauft haben. Da die Tabelle so groß ist macht es keinen Sinn mehr, irgendwie händisch zu versuchen die Produkte nach Namen zu sortieren und dann die Zahlen zusammen und zu zählen. Zu diesem Zweck kann man eine Pivot Tabelle erstellen. Dafür muss man sich in der Tabelle befinden (einfach irgendeine Zelle anklicken) und auf Mit PivotTable zusammenfassen klicken.

In dem Feld ist automatisch die Tabelle ausgewählt, in die wir zuvor geklickt haben.

Erstellen der Pivot Tabelle.

Nach dem Klick auf „OK“ erscheint folgendes Fenster. Dort kann man auswählen aus welcher Spalte die Werte für die neuen Spaltennamen kommen, wonach gefiltert werden kann und welche Werte den Inhalt der neuen Tabelle ausmachen.

Ich habe die Tabelle wie folgt gestaltet:

• Filters: Man soll nach dem Land (Country) und der Kundennummer (CustomerID) selektieren können
• Columns: Die Spaltennamen sollen aus der Spalte Description (das sind die Produktnamen) kommen
• Rows: Für dieses Beispiel brauchen wir keine Zeilennamen
• Values: Die Werte sollen pro Produktname die Summe der verkauften Artikel sein

Auswählen der Pivot Spalten.

Während man die Spalten in die gewünschten Felder zieht, sieht man schon wie sich die Tabelle aufbaut. Mit einem Klick auf das rote Kreuz verschwindet der Dialog und die Tabelle ist fertig.

Das Filtern nach den Produktnamen, das ich weiter oben gezeigt habe wird leider nicht für die Pivot Tabelle übernommen. Mit einem Klick auf den kleinen Pfeil neben Column Labels kommt aber wieder der bereits bekannte Filter-Dialog und man kann ganz einfach die nicht erwünschten Produktnamen entfernen.

Da ich Country und CustomerID zu den Filtern gefügt habe, können wir zum Beispiel herausfinden welche Produkte nach Österreich verkauft wurden. Mit einem Klick auf den Pfeil neben Country erscheint ein weiterer Filter Dialog. Mit einem Klick auf „Select All“ werden alle entfernt und meinem weiteren Klick auf „Austria“ wird nur Österreich ausgewählt.

Filtern der Werte in der Pivot Tabelle.

Und schon habt ihr eine Tabelle, die euch anzeigt welche Produkte wie oft nach Österreich verkauft wurden.

So sieht die gefilterte Pivot Tabelle aus.

Fazit

Excel hat sehr mächtige Table Tools und man benötigt nicht immer Kenntnisse über eine Programmiersprache um aus großen Tabellen einfache Statistiken herauszuholen. Die Ribbon Menüs machen die Office Tools sowieso sehr übersichtlich und einfach zu verwenden und die graphische Oberfäche von Microsoft Office 2016 hat sich gegenüber seinen Vorgängern meiner Meinung nach nochmal stark verbessert.

Falls ihr Fragen oder Anregungen habt, freue ich mich über ein Kommentar.