24

Pivot-Tabellen

Mit Pivot-Tabellen können Sie Daten in Listen und Tabellen analysieren. Mit Pivot-Tabellen können Sie Daten nicht nur gruppieren und zusammenfassen, sondern auch vertiefen. Beim Erstellen einer Pivot-Tabelle teilen Sie Excel mit, welche Felder (in der Liste) in Zeilen und Spalten angeordnet werden sollen. Sie können auch die Anordnung als Seitenfeld wählen, wobei die Anordnung in einem Seitenstapel erfolgt. Sie können Pivot-Tabellenfelder im Bruchteil einer Sekunde neu anordnen – und Sie können die Daten sogar herumdrehen (daher auch der Name, »Pivot« bedeutet im Englischen Achse, Drehpunkt).

Arbeiten mit Pivot-Tabellen

Die meisten Excel-Listen sehen aus wie in Bild 24.1. Diese Listen enthalten Informationszeilen, die in Spalten angeordnet sind, die jeweils eine bestimmte Informationskategorie enthalten. Die Liste in Bild 24.1 enthält z. B. die Umsatzzahlen für verschiedene Produkte einer Werkzeugfabrik. Die Daten sind in erster Linie nach der Produktsparte organisiert.

Abbildung 24.1: Excel kann große Mengen von Tabellendaten aufnehmen, betrachtet man aber die Daten in diesem detaillierten Formular, wird eine Analyse schwierig.

Diese Datenbank enthält eine Unmenge an Informationen, aber einen Überblick zu bekommen ist schwierig. Hier helfen Pivot-Tabellen.

Pivot-Tabellen zeigen das Endergebnis einer Datenbankanalyse an. Pivot-Tabellen können Werte in einer Datenbank auf der Grundlage der verknüpften Felder analysieren. Die Pivot-Tabelle in Bild 24.2 zeigt z. B., wie jedes der Produkte in jeder der vier Verkaufsregionen verkauft wird. Der Pivot-Tabellen-Assistent hilft beim Erstellen dieses komplexen Berichts.

Abbildung 24.2: Der Pivot-Tabellen-Assistent erstellt komplexe Berichte mit Multifeld-
Analysen und Zwischensummen.

Pivot-Tabellen bieten eine Funktion der Analyse und Berichterstellung, die u. a. für folgende Zwecke nützlich sind:

• Erstellen von zusammenfassenden Tabellen. Wie Sie in Bild 24.2 gesehen haben, können Pivot-Tabellen Listen und Datenbanken zusammenfassen, um einen Überblick über die Daten zu geben. Sie können z. B. viele Transaktionen zu Gesamtsummen gruppieren oder Durchschnittswerte und Statistiken zur Aufzeichnung in einer Liste oder externen Datenbank anzeigen.
• Reorganisieren von Tabellen mit der Drag & Drop-Methode. Pivot-Tabellen können Trends und Beziehungen innerhalb und zwischen Datenelementen darstellen. Bild 24.3 zeigt die gleiche Pivot-Tabelle, die auch in Bild 24.2 abgebildet ist, nachdem sie reorganisiert wurde, um die Gebietsverkaufszahlen pro Monat aufzuzeigen. Sie reorganisieren Pivot-Tabellen, indem Sie die Textbeschriftungen an unterschiedliche Stellen auf dem Bildschirm ziehen.

Abbildung 24.3: Diese Pivot-Tabelle ist die gleiche wie in Bild 24.2. Hier wurde sie
reorganisiert, um eine andere Sicht auf die Daten zu ermöglichen.

• Filtern und Gruppieren von Daten in der Pivot-Tabelle. Beim Prüfen von Daten möchte man mitunter Hauptergebnisse anzeigen. Bei anderer Gelegenheit möchte man sich die Untermenge von Daten ansehen. Mit Pivot-Tabellen lassen sich bestimmte Daten herausgreifen. Bild 24.4 zeigt z. B. die gleichen Daten wie Bild 24.3, nur werden hier nur die Verkaufszahlen für nur einen Tag, den 1. Januar, gezeigt.

Abbildung 24.4: Sie können eine Filterdarstellung der Daten festlegen. Diese Pivot-Tabelle
faßt die Verkaufszahlen vom 1. Januar 1997 zusammen.

• Erstellen von Diagrammen von Pivot-Tabellen. Pivot-Tabellen lassen sich sehr gut präsentieren, Diagramme geben ihnen aber noch den letzten Schliff. Es ist einfach, aus Pivot-Tabellen Diagramme zu erstellen (siehe Bild 24.5). Die Diagramme ändern sich beim Bearbeiten einer Pivot-Tabelle automatisch.

Abbildung 24.5: Das Diagramm unten wurde aus der Pivot-Tabelle oben im Bildschirm erstellt.

Online-Hilfe von Microsoft erhalten Sie im World-Wide-Web unter: http://www.microsoft.com/support/ Zugang zu KnowledgeBase von Microsoft mit umfassender Problembehandlung haben Sie unter: http://www.microsoft.com/kb Anweisungen, Tips und Add-Ins für Microsoft Office erhalten Sie unter: http://www.ronperson.com/

Zum Verständnis von Pivot-Tabellen

Die Pivot-Tabelle ist ein Hilfsmittel, um Daten zu organisieren. Sie legen genau fest, wie die Daten organisiert werden sollen, indem Sie angeben, welche Felder und Einträge in der Pivot-Tabelle angezeigt werden sollen. Ein Feld ist eine auswählbare Kategorie; ein Eintrag ist ein einzelner Wert oder Objekt in dieser Kategorie.

Quelle der Tabelle kann eine Liste oder Tabelle in einem Excel-Tabellenblatt sein oder sogar Daten, die mit einem anderen Programm erstellt wurden. Mehrere Datenquellen können die Pivot-Tabelle mit Daten versorgen. In diesem Kapitel bezieht sich der Begriff Liste auf eine Liste in einem Excel-Tabellenblatt. Begriffe wie Tabellendaten und vielspaltige Tabelle beziehen sich allgemein auf Daten in Tabellenformularen, entweder in einem Excel-Tabellenblatt oder in einer Datei, die mit einem anderen Programm erstellt wurde. Der Begriff Externe Daten bezieht sich auf die Daten, die mit einem anderen Programm erstellt wurden.

Beim Erstellen einer Pivot-Tabelle geben Sie die Zeile, Spalte und Seitenfelder an (siehe Bild 24.6). In einem normalen Tabellenblatt können Sie die Zeilen und Spalten nur in der zweidimensionalen Tabelle sehen. In gleicher Weise können Sie in einer Pivot-Tabelle zu einer Zeit jeweils nur ein Seitenfeld sehen. Die Pivot-Tabelle kann jedoch so angezeigt werden, daß mehrere Blätter aufeinander gestapelt erscheinen (siehe Bild 24.6).

Abbildung 24.6: Pivot-Tabellen ordnen Daten dreidimensional an. In diesem Fall: Produkte in Zeilen, Zeitraum in Spalten und Regionen in Seiten. Jede Dimension kann mehrere Felder enthalten.

Obwohl die in Pivot-Tabellen angezeigten Daten aussehen wie andere Tabellenblattdaten auch, können Sie die Daten nicht direkt in den Datenbereich der Pivot-Tabelle eingeben bzw. dort ändern. Die Pivot-Tabelle ist mit den Quelldaten verknüpft, und in den Zellen der Tabellen finden Sie nur-lesbare Einträge. Sie können jedoch die Formatierung ändern und zwischen verschiedenen Berechnungsoptionen wählen.

Daten, die Sie analysieren wollen, gewinnen oft an Aussagekraft, wenn sie mit einer Datenbank kombiniert werden, die geographische oder demographische Daten enthält. Fügen Sie Ihren Daten Bevölkerungsstatistiken und demographische Daten hinzu, können Sie Einblick gewinnen, welche Art von Kunden Ihre Produkte kauft bzw. Dienstleistungen in Anspruch nimmt, wie geographische Gebiete wachsen oder sich verändern usw. Es gibt viele Quellen für demographische Daten, die Sie kostenlos oder käuflich erwerben können. Viele Daten über nationale Bevölkerungsstatistiken sind kostenlos im Internet erhältlich.

Erstellen einer Pivot-Tabelle

Sie erstellen eine Pivot-Tabelle mit dem Pivot-Tabellen-Assistenten. Die Tabelle ist in wenigen Schritten erstellt, aber Sie müssen sich überlegen, wie Sie die Daten zusammenfassen wollen. Sehen Sie sich die Daten in Bild 24.7 an. Diese Arbeitsmappe enthält Informationen über Verkaufszahlen und Umsatz einer Werkzeugfirma. Jeder Eintrag (Zeile) in dieser Tabelle enthält Daten für die folgenden Felder:

Tabelle 24.1: Felder in Pivot-Tabellen.

Diese Informationen werden in erster Linie aufgezeichnet, um die Verkaufszahlen für jedes Produkt nach Regionen festzustellen. Sie haben aber auch noch anderen Nutzen. Mit diesen Informationen kann ein Geschäftsleiter z. B. den Verkaufstrend an einem bestimmten Tag feststellen. Sie werden später sehen, wie Excel-Pivot-Tabellen genau auf die Bedürfnisse zugeschnitten werden können, um verschiedene Sichtweisen dieser Daten zu vermitteln, die für einen gesunden Geschäftsablauf notwendig sind.

Abbildung 24.7: Excel-Tabellen mit Verkaufszahlen sind ein gutes Beispiel für eine Transaktionsdatenbank, die mit einer Pivot-Tabelle zusammengefaßt werden kann.

In diesem ersten Beispiel sind nicht alle Optionen enthalten. Details über weitere Optionen in Pivot-Tabellen finden Sie in den weiteren Beispielen in diesem oder dem nächsten Kapitel.

Um mit dem Pivot-Assistenten eine Pivot-Tabelle zu erstellen, gehen Sie folgendermaßen vor.

• Wählen Sie im Menü Daten den Befehl Pivot-Tabellenbericht. Das erste Dialogfeld im Pivot-Tabellen-Assistenten wird angezeigt (siehe Bild 24.8). Sie arbeiten mit dem Pivot-Tabellen-Assistenten, bis die Pivot-Tabelle im Tabellenblatt fertig erstellt ist.

Mit den Schaltflächen unten im Dialogfeld können Sie sich im Pivot-Tabellen-Assistenten vorwärts oder rückwärts bewegen:

Tabelle 24.2: Schaltflächen des Pivot-Tabellen-Assistenten.

Abbildung 24.8: Das erste Dialogfeld im Pivot-Tabellen-Assistenten fordert Sie dazu auf, die Datenquelle festzulegen, die in der Pivot-Tabelle zusammengefaßt werden soll.

• Unter der Überschrift Erstellt Pivot-Tabelle basierend auf geben Sie die Quelle für die Tabellendaten an:

Tabelle 24.3: Quellenangabe im Pivot-Tabellen-Assistenten.

Wählen Sie MS Excel-Datenbank oder -Liste (wenn es nicht bereits selektiert ist); dann klicken Sie auf Weiter >, um das zweite Dialogfeld des Pivot-Tabellen-Assistenten anzuzeigen (siehe Bild 24.9).

Abbildung 24.9: Der zweite Schritt im Pivot-Tabellen-Assistenten,
in dem Sie den Bereich selektieren.

• In Schritt 2 des Pivot-Tabellen-Assistenten können Sie den Tabellenbereich, der die Daten enthält, in das Bearbeitungsfeld Bereich eingeben oder selektieren. Befindet sich die aktive Zelle in dem Bereich, den Sie als Datenbank angegeben haben, so wählt der Pivot-Tabellen-Assistent diesen Bereich. Aktivieren Sie die Schaltfläche Weiter >.

Befindet Sich die Datenquelle in einer anderen Arbeitsmappe, können Sie die Schaltfläche Durchsuchen zum Suchen der Arbeitsmappe verwenden.

Excel zeigt nun das Dialogfeld für Schritt 3 des Pivot-Tabellen-Assistenten an (siehe Bild 24.10).

Abbildung 24.10: Das Dialogfeld von Schritt 3 des Pivot-Tabellen-Assistenten. Dort legen Sie das Layout fest, indem Sie die Felder in die Bereiche Zeile, Spalte, Seite und Daten ziehen.

Wird eine Fehlermeldung angezeigt, so prüfen Sie, ob der angegebene Bereich eine leere Zelle in der ersten Zeile enthält (diese Zeile enthält die Feldnamen).

Erinnern Sie sich, was Sie über Zeilen-, Spalten und Seitenfelder weiter oben in diesem Kapitel gelesen haben? Hier brauchen Sie nun diese Begriffe. Was Sie hier festlegen, steuert, welche Daten angezeigt werden und wo sie in der Tabelle plaziert werden. Sie müssen sich aber keinen Hemmschuh anlegen – das Schöne an Pivot-Tabellen ist, daß Sie alles, was Sie machen, wieder ändern können, wenn Sie die Tabelle auf dem Bildschirm anzeigen.

Um das Layout festzulegen und die Pivot-Tabelle in Schritt 3 des Pivot-Tabellen-Assistenten zu erstellen, gehen Sie folgendermaßen vor:

• Bestimmen Sie, welches Feld die zusammengefaßten Daten enthalten soll, und ziehen Sie dann die entsprechenden Felder in den Bereich Daten. Oft haben Sie in diesem Schritt nicht viele Auswahlmöglichkeiten. Im Beispiel der Verkaufszahlen von Werkzeugen sollen die Verkaufszahlen zusammengefaßt werden. Darum muß das Datenfeld die Kategorie Verkaufszahlen enthalten; in diesem Fall gibt es keine logische Alternative.
• Bestimmen Sie, wie die Daten angeordnet werden sollen:
• Um die Einträge in einem Feld in Spalten und die Beschriftungen jeweils oben anzuordnen, ziehen Sie die Schaltfläche für dieses Feld in den Bereich Spalte. Bild 24.10 benötigt die Verkaufszahlen je nach Produkt, also ziehen Sie die Schaltfläche Produkt in den Bereich Spalte.
• Um die Daten in einem Feld in Zeilen und die Beschriftungen entlang der Seite anzuordnen, ziehen Sie die Schaltfläche für dieses Feld in den Bereich Zeile. Da in Bild 24.10 eine Liste mit Regionen als Zeilenüberschriften benötigt wird, ziehen Sie die Schaltfläche Regionen in den Bereich Zeile.
• Wie das Feld Seite in diesem Dialogfeld verwendet wird, erfahren Sie in diesem Kapitel weiter unten.
• Bild 24.11 veranschaulicht das Layout einer Pivot-Tabelle, die eine Zusammenfassung von Verkaufszahlen nach Produkten und Regionen erstellt.

Abbildung 24.11: Um das Layout der Pivot-Tabelle festzulegen, ziehen Sie die Schaltflächen der Felder im Dialogfeld Schritt 3 des Pivot-Tabellen-Assistenten an die gewünschte Stelle.

• Aktivieren Sie die Schaltfläche Weiter >.
• Im letzten Dialogfeld des Pivot-Tabellen-Assistenten (siehe Bild 24.12) teilen Sie Excel mit, wo die Pivot-Tabelle plaziert werden soll. Sie können die Pivot-Tabelle in jedes beliebige Tabellenblatt in jeder beliebigen Arbeitsmappe plazieren. (Achten Sie dabei darauf, daß Sie die Tabelle nicht an einer Stelle plazieren, wo Daten überschrieben werden können.) Geben Sie die obere linke Zelle der Tabelle als die Zelle an, bei der die Pivot-Tabelle beginnen soll (oder klicken Sie auf die Zelle). Im letzten Dialogfeld des Pivot-Tabellen-Assistenten haben Sie die Option, Gesamtsummen oder Zwischensummen mit in die Tabelle aufzunehmen (Excel empfiehlt das normalerweise). Deaktivieren Sie die Optionen für Gesamtsummen, können Sie den Pivot-Tabellen-Assistenten zu einem späteren Zeitpunkt wieder aufrufen und diese Option wieder aktivieren.

Abbildung 24.12: In Schritt 4 des Pivot-Tabellen-Assistenten teilen Sie Excel mit, wo die Pivot-Tabelle plaziert werden soll, und können nach Wunsch einige allgemeine Optionen ändern.

Sie können Daten auch mit der Pivot-Tabelle speichern. Das Speichern von Daten mit der Pivot-Tabelle speichert – auf dem Tabellenblatt mit der Pivot-Tabelle – eine Kopie der Daten, die analysiert werden. Das hat den Vorteil, daß die Originalquelle zum Ändern der Pivot-Tabelle nicht geöffnet werden muß. Nachteil ist, daß die Datei mit der Pivot-Tabelle mitunter sehr groß wird. Erstellen Sie eine Pivot-Tabelle aus einer Pivot-Tabelle, so werden die Daten nicht mehr mit der Pivot-Tabelle gespeichert, aus der die neue Pivot-Tabelle erstellt wurde.

• Aktivieren Sie die Schaltfläche Ende, um die Pivot-Tabelle zu erstellen.
• Um die Pivot-Tabelle zu formatieren, wählen Sie im Menü Format den Befehl AutoFormat und wählen eine Formatvorlage. Aktivieren Sie die Schaltfläche OK, um das Format zuzuweisen.

Erstellen Sie Pivot-Tabellen auf einem eigenen Tabellenblatt, so sind sie leichter zu finden, und Sie können das versehentliche Überschreiben anderer Abschnitte eines Tabellenblattes vermeiden.

Bild 24.13 zeigt die Pivot-Tabelle mit den Einstellungen aus Bild 24.11 und 24.12. Nachdem Sie eine Pivot-Tabelle erstellt haben, wird die Pivot-Tabellen-Symbolleiste in dem Dokument angezeigt (siehe Bild 24.13). (Haben Sie die Pivot-Tabellen-Symbolleiste jedoch früher bereits angezeigt und dann entfernt, so wird sie von Excel nicht mehr angezeigt.)

Abbildung 24.13: Die Pivot-Tabelle mit den Einstellungen aus Bild 24.11 und 24.12

Bearbeiten der Pivot-Tabelle

Wie bereits erwähnt, sind Pivot-Tabellen Hilfsmittel zum Anzeigen von Informationen. Daher können Beträge, die in der Tabelle angezeigt werden, nicht geändert werden. Excel stellt eine Reihe von Funktionen zur Verfügung, um die Art der Informationszusammenfassung und die Formatierung in der Tabelle zu steuern.

Sie können die Namen von Pivot-Tabellenfeldern ändern, indem Sie einen anderen Namen darüber tippen.

Sie können die Namen von Pivot-Tabellenfeldern und -einträgen ändern. Selektieren Sie das Feld oder den Eintrag, und geben Sie einen neuen Namen ein (siehe Zelle B2 in Bild 24.14). Excel läßt natürlich nicht zu, daß ein Name zweimal verwendet wird. Geben Sie aus Versehen einen bereits vorhandenen Feld- oder Eintragsnamen ein, so ordnet Excel die Pivot-Tabelle neu an und bewegt den Eintrag mit dem gleichen Namen an die Stelle, wo Sie den Namen neu eingegeben haben.

Ändern Sie die Namen von Feldern oder Elementen in einer Pivot-Tabelle, ändern sich die Namen in der Quellenliste oder Quelldatenbank nicht.

Aktualisieren einer Pivot-Tabelle

Die Pivot-Tabellenanzeige ändert sich nicht, wenn Sie die Daten in der Quellenliste oder Quellentabelle ändern. Sie können die Pivot-Tabelle bei folgenden Änderungen in der Datenquelle aktualisieren oder erneuern, indem Sie eine beliebige Zelle in der Pivot-Tabelle selektieren und die Schaltfläche Daten aktualisieren in der Pivot-Tabellen-Symbolleiste anklicken:

• Änderungen an Daten in einem Datenfeld
• Neue oder geänderte Einträge
• Einfügungen oder Löschen von Feldern oder Einträgen

Fügen Sie der Quellenliste neue Felder hinzu, werden sie in der Pivot-Tabelle nicht angezeigt, es sei denn, Sie rufen Schritt 3 des Pivot-Tabellen-Assistenten auf.

Die Aktualisierung einer Pivot-Tabelle nach Hinzufügen neuer Felder in der Quellenliste kann die Größe der Pivot-Tabelle erweitern. Lassen Sie unten und rechts von Pivot-Tabellen genug »Raum zum Wachsen«, so daß keine anderen Daten im Tabellenblatt überschrieben werden. Stellen Sie Pivot-Tabellen in eigene Tabellenblättern, haben Sie dieses Problem nicht.

Excel läßt nicht zu, daß Sie Zeilen in eine Excel-Quellenliste eingeben, wenn diese Zeilen sich mit einer Pivot-Tabelle überschneiden, denn Excel schützt die Unversehrtheit von Pivot-Tabellen. Sie können jedoch eine Zeile in eine Quellenliste eingeben, wenn der Bereich sich nicht mit einer Pivot-Tabelle schneidet.

Haben Sie die Namen von Feldern oder Elementen durch Direkteingabe in die Pivot-Tabelle geändert, bleiben die Änderungen auch bei einer Aktualisierung der Tabelle bestehen.

Manchmal wollen Sie eine Pivot-Tabelle vielleicht in der aktuellen Form belassen, auch wenn sich die Quelldaten in Zukunft ändern. Um einen »Schnappschuß« von einer Pivot-Tabelle zu machen, kopieren Sie die Pivot-Tabelle und fügen sie an anderer Stelle ein, indem Sie im Menü Bearbeiten den Befehl Inhalte einfügen wählen und dann die Option Werte wählen.

Beim Erneuern einer Pivot-Tabelle werden alle Formatierungen, die den Zellen in der Pivot-Tabelle zugeordnet sind, entfernt, die nicht mit dem Befehl AutoFormat im Menü Format zugeordnet wurden.

Angeben der Quelldaten

Neben einer Liste oder Tabelle in einem Excel-Tabellenblatt können Sie in anderen Programmen erstellte Daten als Quelle für Pivot-Tabellen verwenden. Sie können auch mehrere Listen aus einer oder mehreren Excel-Tabellenblättern verwenden.

Verwenden einer Liste oder Datenbank in der Arbeitsmappe

Um eine Pivot-Tabelle von einer vorhandenen Liste in der aktuellen Excel-Arbeitsmappe zu erstellen, wählen Sie die Option MS Excel-Datenbank oder -Liste im ersten Dialogfeld des Pivot-Tabellen-Assistenten, wenn sie nicht schon selektiert ist.

Die Quellenliste muß Spaltenbeschriftungen enthalten. Achten Sie darauf, daß Sie die Spaltenbeschriftungen in dem Bereich, den Sie in Schritt 2 des Pivot-Tabellen-Assistenten eingeben, mit selektieren. Excel verwendet die Werte in der ersten Zeile des angegebenen Bereichs als Feldnamen.

Verwenden einer Liste oder Datenbank in einer anderen Arbeitsmappe

Um eine Quellenliste in einer anderen Arbeitsmappe festzulegen, gehen Sie folgendermaßen vor:

• Wählen Sie die Schaltfläche Durchsuchen in Schritt 2 des Pivot-Tabellen-Assistenten. Das Dialogfeld Durchsuchen wird angezeigt.
• Wählen Sie die Datei, die die gewünschte Liste enthält, und aktivieren Sie dann die Schaltfläche OK, um zum Pivot-Tabellen-Assistenten zurückzukehren. Das Dialogfeld Schritt 2 wird nun wieder angezeigt, wobei der Dateiname im Bearbeitungsfeld Bereich angegeben ist.
• Geben Sie den Namen oder Bezugsbereich der Quellenliste ein.
• Aktivieren Sie die Schaltfläche Weiter >, um die übrigen Dialogfelder im Pivot-Tabellen-Assistenten zu vervollständigen.

Verwenden externer Datenbanken

Um eine externe Datenquelle zu verwenden, wählen Sie die Option Externer Datenquelle im ersten Dialogfeld des Pivot-Tabellen-Assistenten und aktivieren dann die Schaltfläche Weiter >, um Schritt 2 des Pivot-Tabellen-Assistenten anzuzeigen (siehe Bild 24.14).

Um Daten in einer externen Quelle zu finden, müssen Sie das Programm Microsoft Query, das mit Excel geliefert und zusammen mit Excel installiert werden kann, installiert haben.

Abbildung 24.14: Schritt 2 des Pivot-Tabellen-Assistenten, nachdem
in Schritt 1 die Option externer Datenquelle gewählt wurde.

Wählen Sie die Schaltfläche Daten abrufen. Das Programm Microsoft Query wird gestartet und zeigt ein Dialogfeld an, das ähnlich aussieht wie das in Bild 24.15 abgebildete Dialogfeld.

Abbildung 24.15: Der Pivot-Tabellen-Assistent verwendet Microsoft Query,
um in anderen Programmen erstellte Daten zu finden.

Im Programm Microsoft Query führen Sie mehrere Operationen durch, um die Daten zu bestimmen, die Excel zur Verfügung gestellt werden sollen. Bild 24.16 zeigt ein Beispiel einer Abfragedefinitionstabelle.

Abbildung 24.16: Sie füllen eine Abfragedefinitionstabelle aus, um dem Pivot-Tabellen-Assistenten mitzuteilen, welche Daten aus der festgelegten Quelle genommen werden sollen.

Nachdem Sie die Abfrage definiert haben, wählen Sie den Befehl Daten an Microsoft Excel zurückgeben aus dem Menü Datei von Microsoft Query. Sie kehren dann zu Schritt 2 des Pivot-Tabellen-Assistenten zurück (siehe Bild 24.17).

Abbildung 24.17: Schritt 2 des Pivot-Tabellen-Assistenten, nachdem
eine externe Datenbank abgefragt wurde.

Wird eine mit einer externen Datenquelle verknüpfte Pivot-Tabelle aktualisiert, so fragt Excel die Datenquelle erneut ab.

Konvertieren von Kreuztabellen aus Excel 4.0

Wenn Sie in Excel 4.0 Kreuztabellen erstellt haben, wollen Sie diese wahrscheinlich in Pivot-Tabellen konvertieren.

Um eine Kreuztabelle in eine Pivot-Tabelle zu konvertieren, gehen Sie folgendermaßen vor:

• Öffnen Sie das (in Excel 4.0 erstellte) Tabellenblatt mit der Kreuztabelle.
• Wählen Sie im Menü Daten den Befehl Pivot-Tabellenbericht. Schritt 3 des Pivot-Tabellen-Assistenten wird angezeigt.
• Vervollständigen Sie die übrigen Schritte im Pivot-Tabellen-Assistenten wie gewohnt.

Die Konvertierung einer Kreuztabelle kann nicht rückgängig gemacht werden. Achten Sie deshalb darauf, zum Konvertieren eine Kopie zu verwenden, wenn Sie die Kreuztabelle später eventuell nochmals für Excel 4.0 verwenden wollen.

Filtern von Daten durch das Erstellen von Seitenfeldern

Da der Mensch nicht in der Lage ist, dreidimensionalen Text und Ziffern zu lesen, müssen alle Felder, die in Ihren Pivot-Tabellen angezeigt werden sollen, in Schritt 3 des Pivot-Tabellen-Assistenten entweder in eine Zeile oder Spalte gezwängt werden.

Sie können jedoch eine dritte Dimension erstellen, um zusätzliche Flexibilität beim Prüfen der Daten zu ermöglichen. Beim Erstellen eines Seitenfelds wird eine Art Sichtfilter erzeugt. Um zu verstehen, wie dieser Vorgang funktioniert, sehen Sie sich zuerst die Pivot-Tabelle in Bild 24.18 an. Diese Pivot-Tabelle zeigt die Produktnamen nicht an, weil die angezeigten Daten den Umsatz nach Produktsparten darstellt.

Abbildung 24.18: Der Gesamtumsatz für jede Produktsparte ist nach Regionen dargestellt, aber detaillierte Informationen für einzelne Produkte sind in dieser Pivot-Tabelle nicht verfügbar.

Um die Option zum Durchblättern von Projekten oder Arbeitsbereichen hinzuzufügen und die Zahlen für einen beliebigen Einzelposten in einem der Bereiche anzeigen zu können, erstellen Sie ein Seitenfeld.

Um beim Erstellen einer Pivot-Tabelle ein Seitenfeld zu erzeugen, gehen Sie folgendermaßen vor:

• Starten Sie den Pivot-Tabellen-Assistenten, und vervollständigen Sie die Schritte 1 und 2 des Pivot-Tabellen-Assistenten.
• Im Dialogfeld von Schritt 3 des Pivot-Tabellen-Assistenten bewegen Sie das Feld, das gefiltert werden soll, in den Bereich Seite. Das kann ein früher nicht angezeigtes Feld sein, oder ein Feld, das in der Position Zeile oder Spalte angezeigt wurde.
• Aktivieren Sie die Schaltfläche OK, und vervollständigen Sie dann die übrigen Dialogfelder im Pivot-Tabellen-Assistenten.

Nachdem Sie Produkt als Seitenfeld hinzugefügt und das Produkt Heavy Cut aus der Liste im Seitenfeld gewählt haben, sieht die Pivot-Tabelle aus wie in Bild 24.20.

Klicken Sie auf den Pfeil im Seitenfeld, so wird eine Liste aller im Seitenfeld befindlichen Einträge angezeigt wie auch eine Auswahl an Gesamtsummen. Wählen Sie einfach einen Eintrag, den Sie sehen wollen, und die Pivot-Tabelle zeigt diese Seite an.

Abbildung 24.19: Die gleiche Pivot-Tabelle wie in Bild 24.18, nachdem Produkt als Seitenfeld hinzugefügt wurde und das Produkt Heavy Cut in der Liste des Seitenfelds selektiert wurde.

Konsolidierung von Daten mit der Pivot-Tabelle

Oft befinden sich die Daten, die zusammengefaßt werden sollen, in mehreren Bereichen – manchmal sogar in mehreren unterschiedlichen Tabellenblättern. Besitzen die Bereiche eine ähnliche Struktur und gemeinsame Zeilen- und Spaltenbeschriftungen, können sie zur Analyse in einer einzigen Pivot-Tabelle zusammengruppiert werden.

Die Daten, die konsolidiert werden sollen, müssen sich nicht in eigenen Tabellenblättern befinden. Sie können Daten konsolidieren, die sich in unterschiedlichen Bereichen in einem Tabellenblatt oder mehreren Tabellenblättern befinden bzw. können sie auch in beiden Stellen zu finden sein.

Mit der Pivot-Tabelle können Sie Daten aus mehreren Bereichen konsolidieren. Dieser Vorgang ist ähnlich wie die Verwendung des Befehls Konsolidieren im Menü Daten. Von Vorteil ist, daß Sie die Pivot-Tabelle bearbeiten können, um das Ergebnis der Konsolidierung in unterschiedlicher Art und Weise darzustellen.

Um eine Pivot-Tabelle aus mehreren Tabellenblattbereichen zu erstellen, starten Sie den Pivot-Tabellen-Assistenten und wählen dann unter Erstellt Pivot-Tabelle basierend auf die Option mehreren Konsolidierungsbereichen. Dann wird Schritt 2a des Pivot-Tabellen-Assistenten angezeigt (siehe Bild 24.19). Sie können Excel ein einfaches Seitenfeld erstellen lassen oder das Seitenfeld bzw. die Seitenfelder selbst erstellen.

Abbildung 24.20: Um mehrere Quellenbereiche zu verwenden, wählen Sie in
Schritt 2a des Pivot-Tabellen-Assistenten die Option Einfache Seitenfelderstellung,
die die Bereiche automatisch einem Seitenfeld zuordnet.

Um eine Pivot-Tabelle aus mehreren Tabellenblattbereichen zu erstellen und durch Excel das Seitenfeld automatisch erstellen zu lassen, gehen Sie folgendermaßen vor:

• In Schritt 2a wählen Sie die Option Einfache Seitenfelderstellung und aktivieren dann die Schaltfläche Weiter >. Excel zeigt nun Schritt 2b des Pivot-Tabellen-Assistenten an.
• Geben Sie alle Quellenbereiche ein bzw. markieren Sie sie (das Markieren von Bereichen ist normalerweise schneller). Haben Sie jeden Bereich selektiert, aktivieren Sie die Schaltfläche Hinzufügen; der selektierte Bereich wird dann der Liste Vorhandene Bereiche hinzugefügt.

Enthalten Ihre Quellenbereiche Gesamtsummen, so schließen Sie die Zeilen oder Spalten mit Gesamtsummen nicht in die Bereichsauswahl mit ein. Sonst bezieht der Pivot-Tabellen-Assistent die Gesamtsummen als Einträge in der Pivot-Tabelle mit ein.

• In Schritt 3 des Pivot-Tabellen-Assistenten aktivieren Sie die Schaltfläche Weiter >, um die Standard-Feldpositionen zu übernehmen. Verwenden Sie mehrere Quellenbereiche, hat Excel nicht die nötigen Informationen, um die Feldnamen zu bestimmen, und verwendet Oberbegriffe (»Seite 1«, »Zeile« usw.). Sie können die Feldnamen später bestimmen.
• Vervollständigen Sie Schritt 4 des Pivot-Tabellen-Assistenten wie gewohnt, und aktivieren Sie die Schaltfläche Ende, um die Pivot-Tabelle zu erstellen.

Bild 24.21 zeigt die aus den Beispieldaten erstellte Pivot-Tabelle. Sie können nun Feldnamen in die entsprechenden Zellen eingeben. Die folgende Tabelle listet die Änderungen auf, die an dem Pivot-Tabellenbeispiel vorgenommen werden sollen.

Tabelle 24.4: Ändern von Feldnamen.

Klicken Sie auf den Pfeil in Zelle B1. Anstelle der Gebietsnamen zeigt Excel nun Platzhalternamen an (Eintrag1, Eintrag2 usw.) Erstellt Excel das Seitenfeld, so spart Ihnen das zunächst Zeit, aber Sie müssen die Platzhalter durch aussagekräftige Namen ersetzen – in diesem Fall mit den Namen der vier Regionen. Natürlich ist dieser Vorgang zeitraubend, wenn die Pivot-Tabelle viele Elemente enthält.

Abbildung 24.21: Die aus mehreren Quellenbereichen erstellte Pivot-Tabelle

Gehen Sie davon aus, daß Sie die erstellte Pivot-Tabelle häufig verwenden werden, sollten Sie in Schritt 2a des Pivot-Tabellen-Assistenten die Option Benutzerdefinierte Seitenfelderstellung wählen. In diesem Fall wird in Schritt 2b des Pivot-Tabellen-Assistenten ein Dialogfeld angezeigt, in dem Sie jedem Feldeintrag einen Namen zuordnen.

Um konsolidierte Gesamtsummen anzuzeigen, wählen Sie aus der Liste neben der Seitenfeld-Schaltfläche die Option (Alle), um alle Produkte anzuzeigen. Der Pivot-Tabellen-Assistent verwendet diegleiche intelligente Konsolidierungsmethode wie der Befehl Konsolidieren im Menü Daten. Der Pivot-Tabellen-Assistent liest jedoch die Textbeschriftungen und faßt die Gesamtsumme richtig nach Textbeschriftungen und nicht nach Zellbezügen zusammen.

Um beim Erstellen einer Pivot-Tabelle die Seitenfelder selbst zu selektieren, gehen Sie folgendermaßen vor:

• In Schritt 2a des Pivot-Tabellen-Assistenten wählen Sie die Option Benutzerdefinierte Seitenfelderstellung.
• In Schritt 2b geben Sie alle Quellbereiche ein bzw. markieren sie und fügen sie der Liste Vorhandene Bereiche hinzu, wie im vorhergehenden Beispiel erklärt wurde.
• Wählen unter dem Feld Vorhandene Bereiche die Option 1, um festzulegen, daß Sie ein Seitenfeld wollen. (Um zwei oder mehrere Seitenfelder zu erstellen, wählen Sie die entsprechende Option oder wählen 0, wenn Sie kein Seitenfeld wünschen.)
• Für jeden der Bereich der Liste Vorhandene Bereiche wählen Sie den Bereich und geben eine Beschriftung für ihn im Feld Feld 1 usw. ein. Haben Sie die Beschriftungen für die entsprechende Anzahl von Bereichen eingegeben, aktivieren Sie die Schaltfläche Weiter >.
• Vervollständigen Sie die übrigen Dialogfelder im Pivot-Tabellen-Assistenten wie gewohnt.

Wenn Excel die Pivot-Tabelle erstellt, sind die Beschriftungen für die Bereiche in der Pulldown-Liste neben der Schaltfläche Seite1 aufgeführt.

Verwenden von mehr als einem Feld

In allen bisherigen Beispielen hat die Datenfeldbeschriftung (die über den Zeilenüberschriften und links von der Spaltenbeschriftung positioniert ist) die Art der Zusammenfassungskalkulation (wie z. B. Summe von) und den Namen des Datenfeld angegeben (wie z. B. Umsatz). Diese Beschriftung zeigt an, daß die Pivot-Tabelle nur ein Datenfeld enthält.

In Schritt 3 des Pivot-Tabellen-Assistenten können Sie mehr als ein Datenfeld festlegen. Sie können sogar mehr als ein Feld für Spalten, Zeilen und Seitenfeld eingeben. Im Beispiel, in dem der Umsatz angezeigt wird, wollen Sie z. B. die Verkaufszahlen für jede Produktsparte zusammen mit den Produkten in der Produktsparte anzeigen. Bild 24.22 zeigt eine Pivot-Tabelle mit zwei Zeilenfeldern, Produktsparte und Produkt, an.

Abbildung 24.22: Eine Pivot-Tabelle mit zwei Zeilenfeldern, Produktsparte und Produkt,
die in der Zeilenposition angezeigt werden.

Erstellen einer Pivot-Tabelle aus einer anderen Pivot-Tabelle

Pivot-Tabellen können komplex werden, wenn Sie viele Details anzeigen lassen – so komplex, daß Sie die Daten lieber weiter zusammenfassen, indem Sie auf der Grundlage der vorhandenen Pivot-Tabelle eine neue Pivot-Tabelle erstellen.

Um eine Pivot-Tabelle unter Verwendung der Quelldaten in einer anderen Pivot-Tabelle in der gleichen Arbeitsmappe zu erstellen, gehen Sie folgendermaßen vor:

• Achten Sie darauf, daß kein Abschnitt der Pivot-Tabelle markiert ist. (Ist ein Abschnitt einer Pivot-Tabelle markiert, geht Excel davon aus, daß Sie an dieser Pivot-Tabelle Änderungen vornehmen wollen.)
• Starten Sie den Pivot-Tabellen-Assistenten.
• Wählen Sie im Feld Erstellt Pivot-Tabelle basierend auf die Option anderer Pivot-Tabelle, und aktivieren Sie dann die Schaltfläche Weiter >. Der Pivot-Tabellen-Assistent zeigt eine Liste von Pivot-Tabellen in der aktiven Arbeitsmappe an.
• Wählen Sie die Pivot-Tabelle, die Sie als Datenquelle benutzen wollen, und aktivieren Sie die Schaltfläche Weiter >.
• Vervollständigen Sie die übrigen Dialogfelder im Pivot-Tabellen-Assistenten wie gewohnt.

Excel erstellt die zweite Pivot-Tabelle an der angegebenen Stelle. Die zwei Pivot-Tabellen werden synchron aktualisiert, immer wenn Sie eine der beiden erneuern.

Um eine Pivot-Tabelle unter Verwendung der Quelldaten in einer anderen Pivot-Tabelle in einer anderen Arbeitsmappe zu erstellen, kopieren Sie die vorhandene Pivot-Tabelle in die aktuelle Arbeitsmappe. Die kopierten Daten können, wenn Sie den Definitionen einer Liste entsprechen, als Quellenliste für eine neue Pivot-Tabelle verwendet werden. Achten Sie jedoch darauf, daß die neue Pivot-Tabelle Verknüpfungen zu den Originalquelldaten, die für die alte Pivot-Tabelle bestehen, verliert. Darum können Sie die neue Pivot-Tabelle nicht so bearbeiten, wie Sie das könnten, wenn sich die Quelldaten in der gleichen Arbeitsmappe befänden.

Erstellen eines Diagramms von einer Pivot-Tabelle

Sie können ein Diagramm erstellen, das mit der Pivot-Tabelle verknüpft ist. Das Diagramm ändert sich dynamisch, wenn Sie das Layout der Pivot-Tabelle ändern. Bild 24.23 zeigt eine Pivot-Tabelle und ein Diagramm, das aus dieser Tabelle erstellt wurde.

Abbildung 24.23: Dieses Diagramm wurde aus der oben angezeigten Pivot-Tabelle erstellt.

Beachten Sie, daß diese Pivot-Tabelle zwei Zeilenfelder und ein Spaltenfeld hat. In diesen Fällen zeigt Excel die Einträge der Spaltenfelder entlang der Rubrikenachse (X) des Diagramms. Excel gruppiert die Diagrammreihe wie in den Zeilengruppierungen in der Pivot-Tabelle nach der Kategorie Region.

Erstellen Sie ein Diagramm aus einer Pivot-Tabelle, sollten Sie in der Pivot-Tabelle nicht mehr als zwei Zeilenfelder und zwei Spaltenfelder verwenden, damit das Ganze übersichtlich bleibt.

Um ein Diagramm aus einer Pivot-Tabelle zu erstellen, gehen Sie folgendermaßen vor:

• Wählen Sie die Pivot-Tabelle. Achten Sie dabei darauf, keine Spalten zu markieren, die Gesamtsummen enthalten. Sie sollten auch vermeiden, an einem der Feldregister zu ziehen; sonst nimmt Excel an, daß Sie das Zeilenfeld bewegen wollen.

• Klicken Sie in der Standard-Symbolleiste auf die Schaltfläche Diagramm-Assistent. Folgen Sie den Anweisungen in den Dialogfeldern des Diagramm-Assistenten.

Um sicherzustellen, daß keine Gesamtsummen in Ihrem Diagramm angezeigt werden, ist es am besten, Sie entfernen Gesamt- und Zwischensummen aus der Pivot-Tabelle, bevor Sie das Diagramm erstellen.

Sie können die Art und Weise, wie Excel die Reihenmarkierungen in dem Diagramm gruppiert hat, ändern. (Sie wollen die Spalten im Diagramm in Bild 24.23 zum Beispiel lieber nach Produkten gruppiert haben.) Markieren Sie das Diagramm, rufen Sie den Diagramm-Assistenten auf, und ändern Sie in Schritt 2 die Einstellungen.

Der Diagramm-Assistent zeigt die Einträge in Seitenfeldern in einem Diagramm nicht an. Markieren Sie jedoch einen Eintrag in einem Seitenfeld, so ändert sich das von der Pivot-Tabelle erstellte Diagramm dynamisch und zeigt die Daten für den markierten Eintrag an.

Ändern sich die Dimensionen der Pivot-Tabelle, während Sie Einträge aus Seitenfeldern mit aufnehmen (aufgrund der unterschiedlichen Anzahl von Einträgen), so paßt sich das Diagramm nicht automatisch an die Änderungen an. Sie können das verhindern, indem Sie die Zeilen- und Spaltengesamtsummen in Schritt 4 des Pivot-Tabellen-Assistenten entfernen und die gesamte Pivot-Tabelle markieren, bevor Sie das Diagramm erstellen.

Speichern von Dateien mit Pivot-Tabellen

Manchmal sind Dateien mit Pivot-Tabellen erstaunlich groß, weil Excel eine Kopie der Quelldaten erstellt und sie als verborgene Daten mit der Arbeitsmappe, in der sich die Pivot-Tabelle befindet, speichert. Nimmt die Pivot-Tabelle Bezug auf eine große Datenmenge in einer anderen Datei, werden die gleichen Daten bei jedem Speichern der Datei mit der Pivot-Tabelle doppelt abgespeichert.

Um diese Wiederholung zu vermeiden, deaktivieren Sie das Kontrollkästchen Daten mit Tabellen-Layout speichern in Schritt 4 des Pivot-Tabellen-Assistenten. Excel speichert dann das Layout der Pivot-Tabelle, berücksichtigt dabei aber nicht die Kopie der Quelldaten. Ändern – oder erneuern – Sie die Pivot-Tabelle, aktualisiert Excel sie direkt von den Quelldaten. Beachten Sie bei der Verwendung, daß die Daten einer Pivot-Tabelle, deren Daten als Quelle für eine andere Pivot-Tabelle gespeichert wurden, nicht mehr in der Original-Pivot-Tabelle abgespeichert werden.

Arbeiten Sie mit einer Pivot-Tabelle, die mit einer großen Liste in einem Excel-Tabellenblatt verknüpft ist, so stellen Sie die Pivot-Tabelle besser in eine eigene Arbeitsmappe und verknüpfen sie mit einem geschlossenen Tabellenblatt, um Speicherkapazitäten zu sparen.

Schritt 3 des Pivot-Tabellen-Assistenten zeigt Zahlen und unsinnige Feldnamen an. Sie haben einen Listenabschnitt markiert und haben beim Festlegen des Bereichs mit den Quelldaten in Schritt 2 die Zeile mit den Feldnamen nicht mit angegeben. Aktivieren Sie die Schaltfläche Zurück, und legen Sie den Bereich erneut fest. Excel zeigt die Meldung Der Pivot-Tabellen-Feldname ist ungültig an. Beim Festlegen des Bereichs mit den Quelldaten haben Sie eine Spalte mit einem ungültigen Feldnamen angegeben. Das passiert meistens, wenn Sie versehentlich beim Festlegen des Bereichs in Schritt 2 eine leere Spalte markieren. Die Pivot-Tabelle zeigt die Werte »1« in jeder Zelle an. Höchstwahrscheinlich zeigt die Pivot-Tabelle anstelle der gewünschten Summe, des Mittelwerts oder einer anderen zusammenfassenden Funktion die Anzahl von Einträgen an. Die Pivot-Tabelle ist leer (oder beinahe leer). Sie erhalten eine leere Pivot-Tabelle, wenn sie wenigstens ein Seitenfeld enthält, dessen erstes Feld leer ist bzw. einen Nullwert eingetragen hat. Um festzustellen, ob es leere Felder oder Felder mit Nullwerten gibt, wählen Sie in der Seitenfeldliste die Option (Alle), und achten Sie darauf, ob die Pivot-Tabelle nun mehr Werte anzeigt.