<\/p>\n
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.<\/b><\/p>\nWozu Pivot-Tabellen oder -Diagramme<\/h2>\n
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.<\/p>\n
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<\/b>, tblBestellungen<\/b>, tblBestelldetails<\/b>, tblArtikel<\/b>, tblKategorien<\/b> und tblLieferanten<\/b>. So haben wir ausreichend Spielmaterial für die ersten Gehversuche mit Pivot-Tabellen und -Diagrammen unter Excel.<\/p>\n
![\"Datenquelle](\"..\/fileadmin\/_temp_\/2021_01\/pic_1286_001.png\")
<\/p>\n
Bild 1: Datenquelle für die Pivot-Auswertungen<\/span><\/b><\/p>\n 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).<\/p>\n Bild 2: Daten für die Pivot-Auswertungen<\/span><\/b><\/p>\n Die Daten exportieren wir nun im Excel-Format. Dazu markieren Sie die unter dem Namen qryPivot <\/b>gespeicherte Abfrage im Navigationsbereich und wählen den Kontextmenü-Eintrag Exportieren|Excel <\/b>aus (siehe Bild 3).<\/p>\n Bild 3: Exportieren der Daten in eine Excel-Datei<\/span><\/b><\/p>\n Im ersten Schritt des Export-Assistenten geben Sie den Namen der Zieldatei an. Danach klicken Sie einfach auf OK<\/b>, 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.<\/p>\n Bild 4: Unser Beispielmaterial für das Erstellen von Pivot-Tabellen<\/span><\/b><\/p>\n 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<\/b>. Hier wählen wir den Eintrag Tabellen|PivotTable<\/b> (siehe Bild 5).<\/p>\n Bild 5: Erstellen einer Pivot-Tabelle per Ribbon-Befehl<\/span><\/b><\/p>\n 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.<\/p>\n Bild 6: Dialog zum Erstellen einer Pivot-Tabelle<\/span><\/b><\/p>\n 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.<\/p>\n Nach einem Klick auf OK<\/b> 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<\/b>, Spalten<\/b>, Zeilen <\/b>und Werte<\/b>.<\/p>\n Bild 7: Ausgangspunkt zum Einrichten der Pivot-Tabelle<\/span><\/b><\/p>\n Nun folgt der interessante Teil: Das Zuordnen der Felder aus der Feldliste zu den vier Bereichen. Dies können Sie auf zwei Arten erledigen:<\/p>\n 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 <\/b>in den Bereich Spalten<\/b>, das Feld Artikelname <\/b>in den Bereich Zeilen <\/b>und das Feld Anzahl <\/b>in den Bereich Werte <\/b>(siehe Bild 8).<\/p>\n Bild 8: Zuordnen der Felder zu den Bereichen<\/span><\/b><\/p>\n Damit erhalten wir das Ergebnis aus Bild 9.<\/p>\n Bild 9: Ergebnis der ersten Pivot-Tabelle<\/span><\/b><\/p>\n 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 <\/b>aus. Im nun erscheinenden Dialog Zellen formatieren <\/b>wechseln Sie zur Registerseite Ausrichtung<\/b> und stellen diese auf 90° <\/b>ein (siehe Bild 10).<\/p>\n Bild 10: Vertikale Ausrichtung der Spaltenüberschriften<\/span><\/b><\/p>\n 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.<\/p>\n 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.<\/p>\n <\/p>\n Dazu klicken Sie neben dem Text Spaltenbeschriftungen <\/b>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).<\/p>\n Bild 11: Filtern nach Spaltenüberschriften<\/span><\/b><\/p>\n Nach einem Klick auf die Schaltfläche OK <\/b>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<\/b>-Symbol. Entsprechend der Änderungen wird auch das Gesamtergebnis in der Spalte ganz rechts neu berechnet.<\/p>\n 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.<\/p>\n 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.<\/p>\n Ziehen wir also das Feld Kategorien <\/b>auch noch in den Bereich Zeilen<\/b>. Achten Sie hier direkt darauf, ob Sie das Feld Kategoriename <\/b>über oder unter dem Feld Artikelname <\/b>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.<\/p>\n Das macht natürlich keinen Sinn, weshalb wir die Anordnung wie in Bild 12 vornehmen.<\/p>\n Bild 12: Einfügen einer zweiten Zeile<\/span><\/b><\/p>\n 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.<\/p>\n Bild 13: Artikel nach Kategorien<\/span><\/b><\/p>\n 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.<\/p>\n 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.<\/p>\n Bild 14: Filtern nach verschiedenen Zeilenüberschriften<\/span><\/b><\/p>\n In unserer Beispiel-Pivot-Tabelle wurde automatisch die Berechnungsart Summe<\/b> 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 <\/b>die folgenden Optionen finden (siehe Bild 15):<\/p>\n Bild 15: Auswählen einer anderen Berechnung<\/span><\/b><\/p>\n 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 <\/b>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.<\/p>\n Bild 16: Werte in anderer Form anzeigen<\/span><\/b><\/p>\n Der Menüeintrag Werte anzeigen als <\/b>stellt die folgenden Möglichkeiten zur Verfügung:<\/p>\n Bild 17: Werte als prozentualer Anteil am Spaltengesamtergebnis<\/span><\/b><\/p>\n Bild 18: Werte als prozentualer Anteil am Gesamtergebnis<\/span><\/b><\/p>\n Ä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 <\/b>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.<\/p>\n 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 <\/b>den Untereintrag Symbolsätze <\/b>aufrufen und sich die verfügbaren Symbole anschauen (siehe Bild 19).<\/p>\n Bild 19: Möglichkeiten der bedingten Formatierung<\/span><\/b><\/p>\n 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.<\/p>\n Bild 20: Bedingte Formatierung mit Symbolsätzen<\/span><\/b><\/p>\n 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<\/b>.<\/p>\n Hier finden Sie in der Gruppe Diagramme <\/b>alle benötigten Vorlagen, die Sie auch von Access bereits kennen.<\/p>\n Sie können auch, wenn sich die aktuell markierte Zelle in einer Pivot-Tabelle befindet, den Ribbon-Tab PivotTable-Analyse <\/b>auswählen und finden dort in der Gruppe Tools <\/b>den Befehl PivotChart <\/b>vor. Betätigen Sie diese Schaltfläche, erscheint der Dialog aus Bild 21 namens Diagramm einfügen<\/b>.<\/p>\n Bild 21: Dialog zum Auswählen eines einzufügenden Diagramms<\/span><\/b><\/p>\n Wenn Sie mindestens eine Zelle unserer nach einigen wenigen Ländern und Artikeln gefilterten Tabelle markieren, dann den Ribbon-Eintrag PivotChart analysieren|Tools|PivotChart <\/b>auswählen und dann aus dem Dialog etwa den Eintrag Säule|Gruppierte Säulen <\/b>wählen, erhalten Sie ein neues Pivot-Chart wie in Bild 22.<\/p>\n Bild 22: Pivot-Chart zu unserer Beispieltabelle<\/span><\/b><\/p>\n 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 <\/b>oder Einfügen|Diagramme|PivotChart|PivotChart und PivotTable <\/b>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).<\/p>\n Bild 23: Konfigurieren eines PivotCharts<\/span><\/b><\/p>\n 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.<\/p>\n 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.<\/p>\n Danach wählen Sie im Ribbon den Eintrag Daten|Daten abrufen und transformieren|Daten abrufen|Aus Datenbank|Aus Microsoft Access-Datenbank <\/b>aus (siehe Bild 24). Es erscheint ein Dialog namens Daten importieren<\/b>, mit dem Sie die Quelldatenbank selektieren können.<\/p>\n Bild 24: Start einer Verbindung zu Daten aus einer Microsoft Access-Datenbank<\/span><\/b><\/p>\n Der folgende Dialog hat die Überschrift Navigator <\/b>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.<\/p>\n Wir wollen die Abfrage qryPivot<\/b> nutzen, die wir bereits weiter oben erstellt haben und die auch als Grundlage für die bisherigen Beispiele diente (siehe Bild 25).<\/p>\n![\"Daten](\"..\/fileadmin\/_temp_\/2021_01\/pic_1286_002.png\")
<\/p>\nBeispieldaten exportieren<\/h2>\n
![\"Exportieren](\"..\/fileadmin\/_temp_\/2021_01\/pic_1286_003.png\")
<\/p>\n![\"Unser](\"..\/fileadmin\/_temp_\/2021_01\/pic_1286_004.png\")
<\/p>\nErste Pivot-Schritte<\/h2>\n
![\"Erstellen](\"..\/fileadmin\/_temp_\/2021_01\/pic_1286_005.png\")
<\/p>\n![\"Dialog](\"..\/fileadmin\/_temp_\/2021_01\/pic_1286_006.png\")
<\/p>\n![\"Ausgangspunkt](\"..\/fileadmin\/_temp_\/2021_01\/pic_1286_007.png\")
<\/p>\nZuweisen der Felder zur Pivot-Tabelle<\/h2>\n
\n
![\"Zuordnen](\"..\/fileadmin\/_temp_\/2021_01\/pic_1286_008.png\")
<\/p>\n![\"Ergebnis](\"..\/fileadmin\/_temp_\/2021_01\/pic_1286_009.png\")
<\/p>\n![\"Vertikale](\"..\/fileadmin\/_temp_\/2021_01\/pic_1286_010.png\")
<\/p>\nFiltern nach Zeilen oder Spalten<\/h2>\n
![\"Filtern](\"..\/fileadmin\/_temp_\/2021_01\/pic_1286_011.png\")
<\/p>\nWeitere Ebenen hinzufügen<\/h2>\n
![\"Einfügen](\"..\/fileadmin\/_temp_\/2021_01\/pic_1286_012.png\")
<\/p>\n![\"Artikel](\"..\/fileadmin\/_temp_\/2021_01\/pic_1286_013.png\")
<\/p>\nFiltern und Sortieren bei mehr als einer Zeilen- oder Spaltenüberschrift<\/h2>\n
![\"Filtern](\"..\/fileadmin\/_temp_\/2021_01\/pic_1286_014.png\")
<\/p>\nVerschiedene Berechnungen<\/h2>\n
![\"Auswählen](\"..\/fileadmin\/_temp_\/2021_01\/pic_1286_015.png\")
<\/p>\n\n
Werte anzeigen als …<\/h2>\n
![\"Werte](\"..\/fileadmin\/_temp_\/2021_01\/pic_1286_016.png\")
<\/p>\n\n
![\"Werte](\"..\/fileadmin\/_temp_\/2021_01\/pic_1286_018.png\")
<\/p>\n![\"Werte](\"..\/fileadmin\/_temp_\/2021_01\/pic_1286_017.png\")
<\/p>\nBedingte Formatierung nutzen<\/h2>\n
![\"Möglichkeiten](\"..\/fileadmin\/_temp_\/2021_01\/pic_1286_019.png\")
<\/p>\n![\"Bedingte](\"..\/fileadmin\/_temp_\/2021_01\/pic_1286_020.png\")
<\/p>\nPivot-Charts erstellen<\/h2>\n
![\"Dialog](\"..\/fileadmin\/_temp_\/2021_01\/pic_1286_021.png\")
<\/p>\n![\"Pivot-Chart](\"..\/fileadmin\/_temp_\/2021_01\/pic_1286_022.png\")
<\/p>\nPivot-Table und Pivot-Chart in einem erstellen<\/h2>\n
![\"Konfigurieren](\"..\/fileadmin\/_temp_\/2021_01\/pic_1286_023.png\")
<\/p>\nDaten direkt aus Access beziehen<\/h2>\n
![\"Start](\"..\/fileadmin\/_temp_\/2021_01\/pic_1286_024.png\")
<\/p>\n![\"Auswählen](\"..\/fileadmin\/_temp_\/2021_01\/pic_1286_025.png\")
<\/p>\n
![\"Anzeige](\"..\/fileadmin\/_temp_\/2021_01\/pic_1286_026.png\")
<\/p>\n