Lies diesen Artikel und viele weitere mit einem kostenlosen, einwöchigen Testzugang.
Microsoft hat die Pivot-Tabellen und -Diagramme mit der Version 2010 aus Access her-ausgenommen. Damit ist Excel das einzige Produkt in der Office-Familie, das diese praktische Darstellungsweise von Daten beherrscht. Schauen wir uns also an, wie wir diese nutzen können. Im vorliegenden Beitrag widmen wir uns den Grundlagen von Pivot-Tabellen und -Diagrammen in Excel. In weiteren Beiträgen schauen wir uns dann an, wie wir diese von Access aus füllen und nutzen können, sodass der Benutzer möglichst wenige zusätzliche Handgriffe ausführen muss.
Wozu Pivot-Tabellen oder -Diagramme
Manche Datenmengen sind so groß, dass es unmöglich ist, ohne weitere Unterstützung Rückschlüsse daraus zu ziehen. In diesem Fall muss man die Daten irgendwie anders darstellen oder zusammenfassen, um diese sinnvoll auswerten zu können. Unter Access kennen wir dazu Abfragen. Allerdings bedingen Abfragen, dass der Benutzer sich mit dem Entwurf von Abfragen auskennt. Das ist trotz der Entwurfsansicht von Abfragen von Access, welche die dahinterliegende Abfragesprache SQL maskiert, nicht trivial. Einfacher geht es mit sogenannten Pivot-Tabellen. Diese erlauben es, die Daten des gewünschten Bereichs einer Tabelle in verschiedenen Konstellationen darzustellen, diese zu gruppieren und Berechnungen anzustellen.
Beispieldaten
Als Beispieldaten nutzen wir einige Daten der Südsturm-Datenbank, die wir in einer Abfrage zusammengefasst haben. Diese sieht wie in Bild 1 aus und enthält Daten aus den Tabellen tblKunden, tblBestellungen, tblBestelldetails, tblArtikel, tblKategorien und tblLieferanten. So haben wir ausreichend Spielmaterial für die ersten Gehversuche mit Pivot-Tabellen und -Diagrammen unter Excel.
Bild 1: Datenquelle für die Pivot-Auswertungen
Die Daten können wir nach Artikel, Lieferant, Bestelldatum und Zielland gruppieren, und mit über 2.000 Datensätzen sollte auch die Datenmenge ausreichend sein (siehe Bild 2).
Bild 2: Daten für die Pivot-Auswertungen
Beispieldaten exportieren
Die Daten exportieren wir nun im Excel-Format. Dazu markieren Sie die unter dem Namen qryPivot gespeicherte Abfrage im Navigationsbereich und wählen den Kontextmenü-Eintrag Exportieren|Excel aus (siehe Bild 3).
Bild 3: Exportieren der Daten in eine Excel-Datei
Im ersten Schritt des Export-Assistenten geben Sie den Namen der Zieldatei an. Danach klicken Sie einfach auf OK, wir benötigen keinen Export mit Layout oder dergleichen. Die neu erstellte Excel-Datei enthält dann ein Tabellenblatt mit den Daten (siehe Bild 4). Damit können wir die Access-Datenbank vorerst schließen.
Bild 4: Unser Beispielmaterial für das Erstellen von Pivot-Tabellen
Erste Pivot-Schritte
Nach dem Öffnen der Excel-Datei wollen wir gleich eine erste Pivot-Tabelle erstellen. Unter Excel wechseln Sie dazu im Ribbon zum Bereich Einfügen. Hier wählen wir den Eintrag Tabellen|PivotTable (siehe Bild 5).
Bild 5: Erstellen einer Pivot-Tabelle per Ribbon-Befehl
Dies öffnet den Dialog aus Bild 6. Hier legen Sie im oberen Bereich fest, aus welchem Bereich die Daten stammen, die mit der Pivot-Tabelle ausgewertet werden sollen. In unserem Fall ist die Auswahl bereits automatisch erfolgt und umfasst alle Zeilen und Spalten inklusive Spaltenüberschriften. Wir können hier auch eine alternative Datenquelle verwenden, also beispielsweise direkt auf eine Access-Abfrage zugreifen. Wie das gelingt, schauen wir uns später an.
Bild 6: Dialog zum Erstellen einer Pivot-Tabelle
Außerdem geben wir in diesem Dialog an, wo die neue Pivot-Tabelle platziert werden soll. Dazu können Sie ein neues Arbeitsblatt nutzen, aber Sie können diese auch direkt auf dem Arbeitsplatt mit den Daten platzieren.
Nach einem Klick auf OK erscheint das neue Arbeitsblatt, das zwei wesentliche Bereiche anzeigt – auf der linken Seite den Platzhalter für die zu erzeugende Pivot-Tabelle und auf der rechten die Feldliste, mit der Sie die Felder der Datenquelle den verschiedenen Aufgaben zuweisen können (siehe Bild 7). Die vier Bereiche lauten Filter, Spalten, Zeilen und Werte.
Bild 7: Ausgangspunkt zum Einrichten der Pivot-Tabelle
Zuweisen der Felder zur Pivot-Tabelle
Nun folgt der interessante Teil: Das Zuordnen der Felder aus der Feldliste zu den vier Bereichen. Dies können Sie auf zwei Arten erledigen:
- Durch Setzen von Haken an die hinzuzufügenden Felder aus der Feldliste. Dies fügt die Felder automatisch den Bereichen zu. Damit geben Sie die Entscheidung, welche Felder in welchem Bereich landen, in die Hand von Excel.
- Per Drag and Drop der Felder in jeweils einen der vier Bereiche.
Welches Feld Sie in welchen Bereich ziehen, hängt von Ihren Anforderungen ab. Wir wollen im ersten Schritt einmal ermitteln, welche Artikel in welche Länder verkauft wurden. Dazu ziehen wir das Feld Land in den Bereich Spalten, das Feld Artikelname in den Bereich Zeilen und das Feld Anzahl in den Bereich Werte (siehe Bild 8).
Bild 8: Zuordnen der Felder zu den Bereichen
Damit erhalten wir das Ergebnis aus Bild 9.
Bild 9: Ergebnis der ersten Pivot-Tabelle
Wir haben hier noch eine kleine Änderung vorgenommen: Wir haben die Spaltenüberschriften vertikal angeordnet. Dazu klicken Sie mit der rechten Maustaste auf alle anzupassenden Felder, hier die mit den Spaltenüberschriften, und wählen den Kontextmenü-Eintrag Zellen formatieren aus. Im nun erscheinenden Dialog Zellen formatieren wechseln Sie zur Registerseite Ausrichtung und stellen diese auf 90° ein (siehe Bild 10).
Bild 10: Vertikale Ausrichtung der Spaltenüberschriften
Das Ergebnis ist schon recht interessant: Wir sehen die Menge der verkauften Artikel je Land, die verkaufte Gesamtmenge eines Artikels sowie die Gesamtmenge aller Artikel je Land.
Filtern nach Zeilen oder Spalten
Die nun in den Zeilen und Spalten befindlichen Überschriften können Sie filtern, das heißt, Sie können zum Beispiel nur die Bestellungen aus europäischen Ländern anzeigen lassen.
Dazu klicken Sie neben dem Text Spaltenbeschriftungen auf die Schaltfläche mit dem nach unten zeigenden Dreieck und selektieren die anzuzeigenden Einträge beziehungsweise wählen die nicht mehr gewünschten Einträge ab (siehe Bild 11).
Bild 11: Filtern nach Spaltenüberschriften
Nach einem Klick auf die Schaltfläche OK zeigt die Pivot-Tabelle direkt die resultierenden Werte an. Außerdem ändert sich das Symbol vom nach unten zeigenden Dreieck der Schaltfläche in ein Filter-Symbol. Entsprechend der Änderungen wird auch das Gesamtergebnis in der Spalte ganz rechts neu berechnet.
Auf die gleiche Art können Sie auch die anzuzeigenden Artikel filtern. Außerdem lässt sich über diese Kontextmenüs die Sortierung der Spalten- und Zeilenüberschriften festlegen.
Weitere Ebenen hinzufügen
Im ersten Beispiel haben wir die Artikel als Zeilenüberschriften angegeben. Sie können die Anzeige nun direkt erweitern, indem Sie beispielsweise die Kategorien als weitere Zeilenüberschrift hinzufügen.
Ziehen wir also das Feld Kategorien auch noch in den Bereich Zeilen. Achten Sie hier direkt darauf, ob Sie das Feld Kategoriename über oder unter dem Feld Artikelname per Drag and Drop ablegen. Wenn Sie es unterhalb ablegen, wird zu jedem Artikel eine weitere Zeile mit der Kategorie hinzugefügt, zu welcher der jeweilige Artikel gehört – die Kategorien werden also nach Artikel gruppiert.
Das macht natürlich keinen Sinn, weshalb wir die Anordnung wie in Bild 12 vornehmen.
Bild 12: Einfügen einer zweiten Zeile
Das liefert uns das Ergebnis aus Bild 13. Die Artikel werden nach Kategorie gruppiert und erhalten eine entsprechende Zeilenüberschrift. Außerdem zeigt die Zeilenüberschrift mit dem Namen der Kategorie die Anzahl der verkauften Artikel je Kategorie in Summe über die verschiedenen Länder an.
Bild 13: Artikel nach Kategorien
Filtern und Sortieren bei mehr als einer Zeilen- oder Spaltenüberschrift
Wenn Sie nun nicht nur nach Artikeln filtern, sondern komplette Kategorien ausblenden wollen, brauchen Sie nur wieder auf die Schaltfläche mit dem nach unten zeigenden Dreieck zu klicken.
Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...
Testzugang
eine Woche kostenlosen Zugriff auf diesen und mehr als 1.000 weitere Artikel
diesen und alle anderen Artikel mit dem Jahresabo