Pivot-Tabellen und -Diagramme in Excel

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.

Datenquelle für die Pivot-Auswertungen

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).

Daten für die Pivot-Auswertungen

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).

Exportieren der Daten in eine Excel-Datei

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.

Unser Beispielmaterial für das Erstellen von Pivot-Tabellen

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).

Erstellen einer Pivot-Tabelle per Ribbon-Befehl

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.

Dialog zum Erstellen einer Pivot-Tabelle

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.

Ausgangspunkt zum Einrichten der Pivot-Tabelle

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).

Zuordnen der Felder zu den Bereichen

Bild 8: Zuordnen der Felder zu den Bereichen

Damit erhalten wir das Ergebnis aus Bild 9.

Ergebnis der ersten Pivot-Tabelle

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).

Vertikale Ausrichtung der Spaltenüberschriften

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).

Filtern nach Spaltenüberschriften

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.

Einfügen einer zweiten Zeile

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.

Artikel nach Kategorien

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.

Das Popup-Menü zeigt nun oben ein Kombinationsfeld an, mit dem Sie das Feld auswählen können, für das Sie eine Sortierung oder Filterkriterien angeben können (siehe Bild 14). Somit können Sie sowohl nach Kategorien als auch nach Artikel filtern und sortieren.

Filtern nach verschiedenen Zeilenüberschriften

Bild 14: Filtern nach verschiedenen Zeilenüberschriften

Verschiedene Berechnungen

In unserer Beispiel-Pivot-Tabelle wurde automatisch die Berechnungsart Summe ausgewählt. Es gibt jedoch noch einige weitere Berechnungsarten. Diese wählen Sie am schnellsten mit einem Rechtsklick auf eine der Zellen zwischen Spalten- und Zeilenüberschriften aus. Es erscheint dann ein Kontextmenü, in dem Sie unter dem Eintrag Werte zusammenfassen nach die folgenden Optionen finden (siehe Bild 15):

Auswählen einer anderen Berechnung

Bild 15: Auswählen einer anderen Berechnung

  • Summe: Berechnet die Summe des Wertes der Datensätze, die in der Zelle zusammengefasst werden, bezüglich des angezeigten Feldes (hier die Gesamtmenge).
  • Anzahl: Liefert die Anzahl der Datensätze, die in der Zelle zusammengefasst werden, bezüglich der Menge der Datensätze für diese Kombination. Hier also die Anzahl der Bestellpositionen pro Artikel und Land.
  • Mittelwert: Liefert den Mittelwert der zusammengefassten Datensätze bezüglich des angezeigten Feldes, in unserem Beispiel also den Durchschnitt der Werte des angezeigten Feldes pro Artikel und Land.
  • Max: Liefert den größten Wert der zusammengefassten Daten bezüglich des angezeigten Feldes, hier also die größte Anzahl der Artikel für eine Bestellung.
  • Min: Liefert den kleinsten Wert der zusammengefassten Daten bezüglich des angezeigten Feldes, hier also die kleinste Anzahl der Artikel für eine Bestellung.
  • Produkt: Multipliziert die Werte, die in der Zelle zusammengefasst werden. Das macht im aktuellen Beispiel keinen Sinn.

Werte anzeigen als …

Die Werte in den einzelnen Zellen können Sie auch als prozentualen Anteil an verschiedenen anderen Ergebnissen ausgeben. Dazu wählen Sie den Kontextmenü-Eintrag Werte anzeigen als und erhalten die Untereinträge aus Bild 16. Für die folgenden Beispiele haben wir die Daten der Pivot-Tabelle nach einigen wenigen Ländern und Artikeln gefiltert.

Werte in anderer Form anzeigen

Bild 16: Werte in anderer Form anzeigen

Der Menüeintrag Werte anzeigen als stellt die folgenden Möglichkeiten zur Verfügung:

  • Keine Berechnung (Standardeinstellung)
  • % des Gesamtergebnisses, also gemessen an der Summe aller Spalten oder Zeilen (siehe Bild 18)
  • Werte als prozentualer Anteil am Spaltengesamtergebnis

    Bild 17: Werte als prozentualer Anteil am Spaltengesamtergebnis

  • % des Spaltengesamtergebnisses, also gemessen an der Spaltensumme (siehe Bild 17)
  • Werte als prozentualer Anteil am Gesamtergebnis

    Bild 18: Werte als prozentualer Anteil am Gesamtergebnis

  • % des Zeilengesamtergebnisses, also gemessen an der Zeilensumme
  • % von…: Prozent gemessen an den Werten einer Zeile oder Spalte, die noch angegeben werden muss
  • % des übergeordneten Zeilenergebnisses: prozentualer Anteil gemessen an der Summe für die übergeordnete Zeile, beispielsweise der Kategorie für die Artikel
  • % des übergeordneten Spaltenergebnisses: prozentualer Anteil gemessen an der Summe für die übergeordnete Spalte, hier ohne Beispiel

Ähnliche und weitere Einstellungen gibt es noch für die Differenz gemessen an den Werten einer Zeile oder Spalte. Oder Sie lassen sich die laufende Summe der Elemente einer Spalte oder einer Zeile ausgeben. Sie können sich auch die Rangfolge nach der Größe der Werte einer Spalte oder Zeile ausgeben lassen. Und mit dem Befehl Weitere Optionen zeigen Sie noch einen Dialog an, mit dem Sie weitere Konstellationen wählen können. Die hier angebene Art der Anzeige wirkt sich immer auf alle Felder aus mit Ausnahme eventuell referenzierter Felder.

Bedingte Formatierung nutzen

Ein wichtiges Element zum Hervorheben von Daten in Pivot-Tabellen ist die bedingte Formatierung. Damit können Sie noch wesentlich mehr farbige und andere Markierungen zur Tabelle hinzufügen als es unter Access der Fall ist. Beispiele dafür finden Sie etwa, wenn Sie im Ribbon unter Start|Formatvorlagen|Bedingte Formatierung den Untereintrag Symbolsätze aufrufen und sich die verfügbaren Symbole anschauen (siehe Bild 19).

Möglichkeiten der bedingten Formatierung

Bild 19: Möglichkeiten der bedingten Formatierung

Die bedingte Formatierung setzen Sie weitgehend so ein wie Sie es von Access kennen. Interessant sind beispielswiese die Symbolsätze. Wenn wir den Symbolsatz mit den farbigen Pfeilen auf einen Bereich anwenden, indem wir diesen zunächst markieren und dann den Symbolsatz auswählen, sieht das Ergebnis wie in Bild 20 aus.

Bedingte Formatierung mit Symbolsätzen

Bild 20: Bedingte Formatierung mit Symbolsätzen

Pivot-Charts erstellen

Richtig interessant aus optischer Sicht wird es allerdings erst mit den Pivot-Charts. Diese haben eine ähnliche Funktion wie die, die wir bis Access 2010 auch in Access nutzen konnten. Um ein Pivot-Diagramm hinzuzufügen, wechseln Sie wieder zum Ribbon-Tab Einfügen.

Hier finden Sie in der Gruppe Diagramme alle benötigten Vorlagen, die Sie auch von Access bereits kennen.

Sie können auch, wenn sich die aktuell markierte Zelle in einer Pivot-Tabelle befindet, den Ribbon-Tab PivotTable-Analyse auswählen und finden dort in der Gruppe Tools den Befehl PivotChart vor. Betätigen Sie diese Schaltfläche, erscheint der Dialog aus Bild 21 namens Diagramm einfügen.

Dialog zum Auswählen eines einzufügenden Diagramms

Bild 21: Dialog zum Auswählen eines einzufügenden Diagramms

Wenn Sie mindestens eine Zelle unserer nach einigen wenigen Ländern und Artikeln gefilterten Tabelle markieren, dann den Ribbon-Eintrag PivotChart analysieren|Tools|PivotChart auswählen und dann aus dem Dialog etwa den Eintrag Säule|Gruppierte Säulen wählen, erhalten Sie ein neues Pivot-Chart wie in Bild 22.

Pivot-Chart zu unserer Beispieltabelle

Bild 22: Pivot-Chart zu unserer Beispieltabelle

Pivot-Table und Pivot-Chart in einem erstellen

Sie können Pivot-Tabelle und Pivot-Chart auch gleich in einem Schritt anlegen. Dazu klicken Sie in die Ausgangstabelle mit den Daten und wählen dann im Ribbon den Befehl Einfügen|Dia-gramme|PivotChart|PivotChart oder Einfügen|Diagramme|PivotChart|PivotChart und PivotTable aus. Beide öffnen wieder den Dialog, mit dem Sie erstens den Bereich mit den zu verwendenden Daten auswählen und zweitens das Ziel-Arbeitsblatt angeben. Beim Öffnen des neuen Tabellenblatts ist zunächst das neue PivotChart-Objekt markiert. Wenn die Feldliste gerade eingeblendet ist, scheint diese auf den ersten Blick mit der für das Konfigurieren einer Pivot-Tabelle identisch zu sein. Bei genauerem Hinsehen stellen wir jedoch fest, dass die Beschriftungen der Bereiche, denen wir die Felder hinzufügen können, anders lauten (siehe Bild 23).

Konfigurieren eines PivotCharts

Bild 23: Konfigurieren eines PivotCharts

Wenn Sie erst eine Pivot-Tabelle erstellen, die Ihre Daten bereits wie gewünscht anzeigt, können Sie auf dieser Basis ein Pivot-Chart erstellen. Dieses zeigt die Daten dann meist direkt korrekt an.

Daten direkt aus Access beziehen

In den ersten Beispielen haben wir die zu analysierenden Daten aus der Access-Datenbank als Excel-Datei exportiert und diese Daten dann aus Excel heraus als Quelle für die zu erstellenden Pivot-Tabellen und -Charts genutzt. Sie können Pivot-Tabellen und Pivot-Charts auch direkt mit Tabellen oder Abfragen einer Access-Datenbank verbinden. Dazu legen wir eine neue Excel-Datei an.

Danach wählen Sie im Ribbon den Eintrag Daten|Daten abrufen und transformieren|Daten abrufen|Aus Datenbank|Aus Microsoft Access-Datenbank aus (siehe Bild 24). Es erscheint ein Dialog namens Daten importieren, mit dem Sie die Quelldatenbank selektieren können.

Start einer Verbindung zu Daten aus einer Microsoft Access-Datenbank

Bild 24: Start einer Verbindung zu Daten aus einer Microsoft Access-Datenbank

Der folgende Dialog hat die Überschrift Navigator und zeigt alle Tabellen und Abfragen der gewählten Access-Datenbank an. Klicken wir einen der Einträge an, zeigt der Dialog die enthaltenen Daten.

Wir wollen die Abfrage qryPivot nutzen, die wir bereits weiter oben erstellt haben und die auch als Grundlage für die bisherigen Beispiele diente (siehe Bild 25).

Auswählen der Datenquelle für die Datenanalyse

Bild 25: Auswählen der Datenquelle für die Datenanalyse

Danach klicken Sie auf die Schaltfläche Laden. Nach einem kurzen Augenblick zeigt Excel die Daten in einem neuen Tabellenblatt an (siehe Bild 26).

Anzeige der verbundenen Daten in Excel

Bild 26: Anzeige der verbundenen Daten in Excel

Diese Ansicht bietet neben den Daten im Excel-Tabellenblatt noch einen Bereich auf der rechten Seite. Hier finden Sie den Eintrag qryPivot mit einer Schaltfläche im rechten Bereich, der das Aktualisieren der verknüpften Daten erlaubt.

Die Spaltenköpfe des Excel-Arbeitsblatts stellen eine Schaltfläche mit einem nach unten zeigenden Dreieck zur Verfügung, mit dem Sie Filter- und Sortierkriterien wie im Access-Datenblatt einstellen können.

Wenn Sie Werte ändern, wirken sich diese Änderungen lediglich auf andere Ansichten wie Pivot-Tabellen aus, die sich auf die Werte beziehen. Eine Aktualisierung überträgt die geänderten Werte nicht in die Access-Datenbank, aus der die Daten stammen.

Downloads zu diesem Beitrag

Enthaltene Beispieldateien:

PivotInExcel.accdb

PivotVonAccess.xlsx

qryPivot.xlsx

Download

Schreibe einen Kommentar