Rheinwerk Computing < openbook > Rheinwerk Computing - Professionelle Bücher. Auch für Einsteiger.
Professionelle Bücher. Auch für Einsteiger.

Inhaltsverzeichnis
1 Einleitung
2 Basiswissen für die Arbeit mit Excel 2007
3 Aufbau von Kalkulationstabellen
4 Entwicklung von Berechnungen mit Formeln
5 Gestaltung von Tabellenblättern
6 Auswertungen und Was-wäre-wenn-Analysen
7 Optimierungen
8 Grafische Präsentation von Daten
9 Diagramme optimal einsetzen
10 Tabellen grafisch aufbereiten
11 Verteilungsvorbereitung
12 Ausdruck und E-Mail-Versand
13 Excel-Daten im Web
14 Gemeinsame Arbeit an Arbeitsmappen
15 Tabellenfunktionen
16 Informationen als Tabellen ordnen und verwalten
17 Datenabfragen und Datenauszüge
18 Pivot–Tabellen und -Diagramme
19 Arbeit mit externen Daten
20 Datenaustausch zwischen Anwendungen
21 Datenaustausch mit anderen Anwendungen
22 Routineaufgaben mit Makros automatisieren
23 Visual Basic für Applikationen
A Tastenkombinationen
Stichwort

Buch bestellen
Ihre Meinung?

Spacer
 <<   zurück
Excel 2007 - Das umfassende Handbuch von Helmut Vonhoegen
Buch: Excel 2007 - Das umfassende Handbuch

Excel 2007 - Das umfassende Handbuch

1012 S., 39,90 Euro
Rheinwerk Computing
ISBN 978-3-89842-864-4
gp 18 Pivot–Tabellen und -Diagramme
  gp 18.1 Vertriebsergebnisse analysieren
    gp 18.1.1 Auswahl der Quelldaten
    gp 18.1.2 Layout der Pivot-Tabelle
    gp 18.1.3 Sortieren in der Pivot-Tabelle
    gp 18.1.4 Schnelle Datenauszüge zu einzelnen Werten
    gp 18.1.5 Ändern der Berechnungsart
    gp 18.1.6 Neue Gruppen zusammenstellen
    gp 18.1.7 Zahlenmaterial ordnen
    gp 18.1.8 Berechnete Felder und Elemente in Pivot-Tabellen
    gp 18.1.9 Pivot-Tabellen formatieren
    gp 18.1.10 Ändern der Datenquelle, Verschieben, Löschen
    gp 18.1.11 Pivot-Tabelle aus externen Daten
  gp 18.2 Dynamische Diagramme aus Pivot-Tabellen

18 Pivot–Tabellen und -Diagramme

Häufig werden in Firmen enorme Datenmengen erfasst und berechnet, die für die Abwicklung der verschiedenen Aufgaben notwendig sind. Typisches Beispiel sind etwa die Daten, die bei der Fakturierung anfallen.

Firmen, die mit Vertretern arbeiten, erstellen beispielsweise Tabellen, in denen den einzelnen Vertretern die Umsatzzahlen zugeordnet werden, für die sie verantwortlich sind. Meist werden dabei die Umsätze nach Produktgruppen aufgeteilt, damit erkennbar ist, welcher Vertreter welche Produkte besser und welche weniger gut verkauft hat. Die folgende Abbildung zeigt ein einfaches Beispiel einer solchen Liste.

Vertriebstabelle als Quelldaten

In dieser Liste sind ganz unterschiedliche Dimensionen miteinander verknüpft. Zum einen wird die zeitliche Entwicklung der Umsätze dargestellt, zum anderen die räumliche Verteilung nach Regionen. Die dritte Dimension ist die Verteilung der Umsätze nach Produktgruppen, also eine sachliche. Diese Dimension könnte je nach Art der Produkte noch weiter untergliedert sein; eine weitere ist die Verteilung nach Vertretern.

In der abgebildeten Fassung ist die Liste nicht besonders aussagekräftig. Sie können zwar schnell nachsehen, wie viel Umsatz der Vertreter Hansen 2001 mit Waschmaschinen gemacht hat. Aber welchen Anteil die Waschmaschinen am Gesamtumsatz in der Region Nord haben, ist nicht auf einen Blick zu sehen.

Ob sich die Region Ost in den letzten drei Jahren schneller entwickelt hat als die Region West, ist ebenfalls nicht gleich zu erkennen. Dennoch ist in der Liste alles enthalten, um diese Fragen zu beantworten. Es kommt nur darauf an, die Rohdaten zu brauchbaren Informationen aufzubereiten.


Rheinwerk Computing

Interaktive Tabellen und Diagramme  downtop

Für diesen Zweck bietet Excel seine Pivot-Tabellen und Pivot-Diagramme an. Im Unterschied zu den bisher erwähnten Tabellen handelt es sich bei der Pivot-Tabelle um eine »interaktive« Tabelle. »Pivot« ist das englische Wort für einen Drehpunkt, wie die Angel in der Tür. Damit wird angesprochen, dass in der Pivot-Tabelle die Daten nach wechselnden Gesichtspunkten an- und umgeordnet, zusammengefasst und ausgewertet werden können. Technisch wird das dadurch erreicht, dass für die Spaltennamen aus der Tabelle Schaltflächen erzeugt werden, die mit der Maus hin und her geschoben werden können, wobei die dazugehörigen Daten jeweils mitwandern. Auch die Zeilen- und Spaltenbeschriftungen in der Pivot-Tabelle können mit der Maus neu angeordnet werden. Die Elemente und die dazugehörigen Daten sind also intern miteinander verknüpft. Außerdem werden automatisch zusammenfassende Berechnungen erzeugt, die jedes Mal der Gruppierung der Daten angepasst werden. Zusätzlich können weitere berechnete Felder in die Tabelle eingefügt werden.

Die Anzahl der Pivot-Tabellen ist weder pro Tabellenblatt noch pro Arbeitsmappe begrenzt. Es können sowohl verschiedene Pivot-Tabellen von einer Quelltabelle gezogen werden als auch Pivot-Tabellen von verschiedenen Quelltabellen im Tabellenblatt oder in der Arbeitsmappe. Allerdings ist die Funktion ziemlich speicherintensiv, sodass die tatsächlichen Grenzen stark von der Ausstattung Ihres Hauptspeichers abhängig sind.

Speicherplatz sparen lässt sich, falls Sie mehrere Pivot-Tabellen aus denselben Quelldaten benötigen, wenn Sie die folgenden Pivot-Tabellen nicht wieder direkt aus den Quelldaten entwickeln, sondern für diese Pivot-Tabellen jeweils eine der vorhandenen Pivot-Tabellen als Quellbereich angeben. Die Pivot-Tabelle muss sich in derselben Arbeitsmappe befinden. Excel speichert dann nicht jedes Mal die Quelldaten neu in dem speziellen PivotTable-Cache, der bei der Erstellung aus einem Quellbereich angelegt wird, sondern verwendet den bereits vorhandenen Cache.

Mit Excel 2007 lassen sich aus Pivot-Tabellen zudem interaktive Diagramme erzeugen. Auch diese Diagramme lassen sich mit Hilfe der Schaltflächen für die einzelnen Felder der zugrunde liegenden Liste mit der Maus erweitern, verdichten oder umbauen. Dabei sind die Pivot-Tabelle und das zugehörige Pivot-Diagramm so verknüpft, dass Änderungen am Diagramm auch die Tabelle berühren und umgekehrt.


Rheinwerk Computing

Anwendungsgebiete  downtop

Die PivotTable- und PivotChart-Funktion ist eine Art Berichts- und Diagrammgenerator, mit dem ganz unterschiedliche Auswertungen von vorhandenen Datenbeständen möglich sind. Hier noch ein paar Anregungen zu möglichen Anwendungen:

  • Berichte für die Vertriebssteuerung
  • Lagerbestandsanalysen
  • Berichte für die Sortimentsplanung
  • Personalstatistiken
  • Berichte für die Projektplanung und -kontrolle
  • Auswertung von statistischen Erhebungen
  • Auswertung von Fehlerprotokollen und Materialtests

Mit den Pivot-Tabellen und -Diagrammen bietet Excel eine sehr mächtige und dynamische Methode, umfangreiche Datenbestände zu analysieren und zusammenzufassen. Mit diesem Werkzeug können auf einfache Weise ganz unterschiedliche Berichte aus ein und demselben Datenmaterial erzeugt werden, je nachdem, welcher Gesichtspunkt gerade im Vordergrund steht. Die Daten lassen sich umordnen und neu zusammenstellen oder auswählen, ohne eine einzige Zelle in der Originaltabelle ändern zu müssen. Excel stellt Ihnen für diese Aufgabe den Pivot-Assistenten als Helfer zur Seite.


Rheinwerk Computing

Welche Daten können in Pivot-Tabellen oder -Diagrammen verarbeitet werden?  downtop

Der Pivot-Assistent verarbeitet Daten aus normalen Zellbereichen, die eine entsprechende Struktur vorweisen, oder aus Tabellen und erstellt daraus neue, zusätzliche Kalkulationsmodelle und wahlweise zusätzlich entsprechende Diagramme. Es ist also nicht notwendig, den Datenbereich vorher in eine Excel-Tabelle im oben beschriebenen Sinne zu verwandeln, es ist aber in jedem Fall zu empfehlen.

Eine Pivot-Tabelle enthält zunächst keine Formeln, sondern immer nur die Ergebnisse der Formeln aus der zugrunde liegenden Originaltabelle und aus zusätzlichen Berechnungen durch die Pivot-Funktion selbst, wie der Bildung von Gruppen- oder Gesamtsummen.

Auch Daten aus externen Datenquellen, etwa DAccess oder SQL-Datenbanken, können in einer solchen Pivot-Tabelle ausgewertet werden. Bereits bestehende Pivot-Tabellen bzw. -Diagramme können selbst wieder die Basis für weitere solche Tabellen und Diagramme bilden.


Rheinwerk Computing

18.1 Vertriebsergebnisse analysieren  downtop

Die Pivot-Funktion kann erst angewandt werden, wenn Daten vorhanden sind, die dafür brauchbar sind. Zunächst soll der Fall behandelt werden, dass diese Daten als Tabelle in einem Tabellenblatt abgelegt sind. Dazu wird das angesprochene Beispiel mit der Vertriebssteuerung aufgegriffen. Die Funktion kann aber auch mit einem einfachen Zellbereich arbeiten.

Soll diese Tabelle als Basis für eine Pivot-Tabelle verwendet werden, reicht es auch hier wieder, eine beliebige Zelle der Tabelle zu markieren. Wenn die Ausgangstabelle eine Kopfzeile mit den Spaltenbeschriftungen und einen geschlossenen Datenblock mit den einzelnen Datensätzen enthält, kann die Pivot-Funktion den Gesamtbereich der Tabelle korrekt erkennen.


Filter oder Teilergebnisse vorher entfernen

Enthält die Tabelle Teilergebnisse oder Filter, sollten Sie diese zunächst entfernen.



Rheinwerk Computing

18.1.1 Auswahl der Quelldaten  downtop

Der Dialog für eine PivotTable wird über das Register Einfügen mit dem Symbol PivotTable in der Gruppe Tabellen aufgerufen. Das Symbol hat ein kleines Menü, das auch den Befehl PivotChart anbietet. Zunächst wird dann die Herkunft der Daten geklärt. Steht der Zellzeiger in dem Datenblock, der ausgewertet werden soll, zeigt Excel unter Tabelle/Bereich: den gefundenen Bereich an, der bei Bedarf korrigiert werden kann. Bei normalen Zellbereichen müssen Sie darauf achten, dass die Zeile mit den Spaltenüberschriften in einen Bereich eingeschlossen wird. Handelt es sich um eine Excel-Tabelle, wird der für die Tabelle vergebene Name angeboten.

Dialog PivotTable erstellen

Im unteren Teil des Dialogs legen Sie fest, wo die Pivot-Tabelle eingefügt werden soll. Sie kann auf einem neuen Blatt angelegt werden oder an einer bestimmten Stelle des aktiven Blatts beginnen. Im zweiten Fall geben Sie die Adresse der entsprechenden oberen Eckzelle an oder markieren sie.

Beachten Sie, dass die erzeugte Tabelle alle bereits vorhandenen Daten in dem angegebenen Bereich überschreibt, die von der Tabelle berührt werden. Sie erhalten eine entsprechende Warnung angezeigt und sollten dann einen anderen Bereich bestimmen, wenn die vorhandenen Daten erhalten bleiben sollen.

Es ist in der Regel günstig, ein eigenes Blatt in der Arbeitsmappe zu verwenden. Das verhindert Probleme, die z. B. beim Löschen von Zeilen auftreten können, die den Bereich der Pivot-Tabelle kreuzen.

Wird der Dialog bestätigt, erstellt Excel einen zunächst leeren Pivot-Tabellenbereich und blendet den Aufgabenbereich PivotTable-Feldliste ein. Außerdem werden die PivotTable-Tools aktiviert und das Register Optionen eingeblendet, das gleich acht Gruppen von Befehlen und Schaltflächen zur Arbeit mit Pivot-Tabellen anbietet.

Es ist sinnvoll, zunächst den vorgegebenen Namen für die Pivot-Tabelle durch einen aussagekräftigeren zu ersetzen. Dies geschieht auf dem Optionen-Register in der Gruppe PivotTable.

Der noch leere PivotTable-Bericht und die Werkzeuge zur Bearbeitung

Außerdem stehen nun auf dem Register Entwurf drei Gruppen zum Layout und Format der Pivot-Tabelle zur Verfügung.

Das Register Entwurf für Pivot-Tabellen


Rheinwerk Computing

18.1.2 Layout der Pivot-Tabelle  downtop

Zunächst muss nun geklärt werden, unter welchem Gesichtspunkt die Daten hauptsächlich zu betrachten sind. In der neuen Tabelle sollen die Daten so angeordnet und zusammengefasst werden, dass erkennbar wird, wie sich der Anteil der beiden Produktgruppen im Lauf der Jahre entwickelt hat, und zwar sowohl insgesamt als auch in den vier Regionen.

Excel bietet in dem automatisch eingeblendeten Aufgabenbereich PivotTable-Feldliste für jede Spalte in der Ausgangstabelle ein Element an. Diese Elemente können einem der vier Bereiche der Pivot-Tabelle zugewiesen werden:

  • Die Felder, die die Ordnung der Seiten des Berichts bestimmen, müssen dem Berichtsfilterbereich zugewiesen werden.
  • Die Felder, die die Ordnung der Spalten bestimmen, gehören in den Spaltenbeschriftungsbereich, und zwar in der Reihenfolge von links nach rechts.
  • Die Felder, die die Ordnung der Zeilen bestimmen, gehören untereinander in den Zeilenbeschriftungsbereich.
  • Unbedingt muss wenigstens eines der Felder in den Wertebereich eingefügt werden, damit das Programm erkennt, welche Werte in der Tabelle auszugeben sind.

Wird ein Feld in der Feldliste ausgewählt, wird es einem vorgegebenen Bereich zugeordnet. Bei diesem Beispiel würden die Felder Vertreter, Produktgruppe und Region nacheinander in den Bereich Zeilenbeschriftung übernommen, wobei die Reihenfolge des Anklickens der Felder die Hierarchie der Beschriftungen bestimmt. Die Felder mit den Jahreszahlen werden als Vorgabe in den Wertebereich eingefügt. Nach Auswahl aller Felder ergibt sich also folgender Bericht:

Erste Auswertung mit vorgegebenen Bereichszuweisungen

Soll ein Feld in einen anderen als den vorgegebenen Bereich eingefügt werden, klicken Sie den Feldnamen mit rechts an und wählen aus dem Kontextmenü den Zielbereich aus.

Menü zu einem Feld in der Feldliste

Die andere Möglichkeit ist, die Verteilung der Felder auf die vier möglichen Bereiche direkt mit der Maus zusammenzustellen. Dazu werden im unteren Teil des Aufgabenbereichs die vier Bereiche mit den aktuell zugeordneten Feldern angezeigt. Durch einfaches Ziehen kann beispielsweise das Feld Region aus dem Bereich Zeilenbeschriftungen in den Bereich Berichtsfilter übernommen werden. Anders als in den bisherigen Excel-Versionen werden die Felder also nicht direkt in der Pivot-Tabelle verschoben, sondern über den Aufgabenbereich.

Umordnen der Felder über den Aufgabenbereich

Das Layout des Aufgabenbereichs kann übrigens über die Palette der Schaltfläche oben rechts in verschiedener Weise aufgeteilt werden.

Palette für die Aufteilung des Aufgabenbereichs

Welches Feld muss nun wohin, wenn aus der Tabelle die Information herausgeholt werden soll, die oben angefordert worden ist? Um die Arbeitsweise der Pivot-Tabelle zu verstehen, ist es zunächst wichtig, dass Excel die Auswertung der Daten gleichsam kreuzweise vornimmt.

Wenn beispielsweise zuerst das Feld Produktgruppe dem Spaltenbeschriftungsbereich zugeordnet wird, legt Excel für jede in der bisherigen Spalte mit dem Namen Produktgruppe vorkommende Produktgruppe jeweils eine Spalte als Element in der Pivot-Tabelle an und verwendet als Beschriftung den Namen des Feldes, also »Produktgruppe«, vorausgesetzt, über das Register Entwurf ist in der Gruppe Layout in dem Menü der Schaltfläche Berichtslayout die Option Im Gliederungsformat anzeigen oder Im Tabellenformat anzeigen ausgewählt. Das alternative Kurzformat zeigt anstelle der Feldbezeichnungen nur allgemeine Bezeichnungen wie Spaltenbeschriftungen etc.

Das Feld Produktgruppe wird dem Spaltenbeschriftungsbereich zugewiesen.

In diesem Fall sind das nur zwei unterschiedliche Produktgruppen: »Waschmaschinen« und »Kühlaggregate«, also werden zwei Spalten angelegt.

Zu dem Spaltenfeld Produktgruppe gehören folglich zwei Feldelemente. Zusätzlich wird eine Spalte für das Gesamtergebnis eingefügt, es sei denn, Sie schalten diese Voreinstellung über den Dialog Optionen vorher ab. Mehr dazu weiter unten.

Die Zeilen sollen die Daten pro Region anzeigen.

Wird nun gleichzeitig dem Bereich Zeilenbeschriftung das Feld Region zugewiesen, legt Excel für jedes der vier Vertriebsgebiete ein Zeilenelement an. Es entsteht also zunächst eine Tabelle mit vier Zeilen- und zwei Spaltenelementen. Jede Zelle im Datenbereich ist also ein Schnittpunkt zwischen einem Produktgruppen- und einem Vertriebsgebietselement. Hinzu kommen die Spalte und die Zeile mit den jeweiligen Gesamtergebnissen.

Nun fehlt noch die Information, welche Daten in den Schnittpunkten erscheinen sollen. Um die Sache übersichtlich zu halten, werden zunächst nur die Umsatzwerte für ein Jahr verwendet und dazu das Feld »2001« dem Wertebereich zugewiesen.


Rheinwerk Computing

Vertriebsauswertung – 1. Version  downtop

Sobald Sie das Feld im Wertebereich abgelegt haben, wird die Pivot-Tabelle mit den Auswertungen der Daten aus der Originaltabelle gefüllt. In der Zelle für das Feld 2001 erscheint die Bezeichnung Summe von 2001.

Erste Auswertung

Diese Bezeichnung Summe von 2001 gibt an, welche Art der Zusammenfassung der Daten die Pivot-Funktion vorgenommen hat. Nehmen Sie die erste Zelle im Wertebereich. Sie ist der Schnittpunkt zwischen Region Nord und Produktgruppe Kühlaggregate, was auch bestätigt wird, wenn Sie mit der Maus die Zelle berühren. In dieser Zelle wird deshalb der Gesamtumsatz der beiden Vertreter aus der Region Nord in der Produktgruppe Kühlaggregate erscheinen. Excel berechnet zusätzlich das Gesamtergebnis pro Region und pro Produktgruppe.


Rheinwerk Computing

Elementfilter  downtop

Zu den beiden Feldnamen, die als Ordnungskriterien dienen, Region und Produktgruppe, erscheinen Schaltflächen mit kleinen Pfeilen. Ein Klick darauf öffnet die Liste der jeweiligen Elemente. Löschen Sie beispielsweise das Häkchen bei Waschmaschinen im Spaltenfeld Produktgruppe, werden diese Daten aus der Tabelle herausgenommen. Im Aufgabenbereich PivotTable-Feldliste und hinter dem Feldnamen in der Pivot-Tabelle erscheinen dann hinter dem betreffenden Feld Filtersymbole als Hinweis darauf, dass für dieses Feld ein Filter verwendet wird.

So lassen sich einzelne Elemente abwählen.

Anstelle solch einfacher Filter durch Aus- und Abwahl in der angezeigten Elementliste werden außerdem unter den Optionen Beschriftungsfilter und Wertefilter zahlreiche Möglichkeiten angeboten, Elemente mit Hilfe von Auswahlkriterien in der Anzeige aus- oder einzuschließen.

Filteroptionen zu Beschriftungen

Wertbezogene Filteroptionen

Wird ein Filter nicht mehr benötigt, kann er über den hier angebotenen Befehl Filter löschen entfernt werden.


Rheinwerk Computing

Wertespalten hinzufügen  downtop

Die angestrebte Lösung wurde absichtlich noch nicht vollständig durchgeführt, damit Ihnen die Arbeitsweise der Pivot-Funktion möglichst durchsichtig bleibt. Gemessen an dem zunächst beschriebenen Ziel fehlt in dieser ersten Version noch die zeitliche Entwicklung, weil ja bisher nur die Daten für ein Jahr dargestellt werden.

Das lässt sich nun aber leicht korrigieren. Dazu stellen Sie den Zellzeiger wieder direkt in die Pivot-Tabelle, sodass der Aufgabenbereich PivotTable-Feldliste wieder die Feldschaltflächen anbietet – sie werden ausgeblendet, wenn eine Zelle außerhalb der Pivot-Tabelle ausgewählt ist. Um die Daten der beiden noch fehlenden Jahre einzubeziehen, haken Sie die beiden Felder 2002 und 2003 ebenfalls ab. Zunächst erscheinen dann zusätzliche Spalten im Wertebereich für jedes der Jahre. Wollen Sie die Jahreswerte untereinander angeordnet haben, benutzen Sie im Kontextmenü zu Werte die Option Werte verschieben nach/Werte in Zeilen verschieben.

Pivot-Tabelle mit der Auswertung für drei Jahre

Nun stehen die Ergebnisse pro Produktgruppe für die drei Jahre geordnet nach Regionen untereinander und am Ende finden Sie die drei Gesamtsummen. Die Zusammenfassung der Daten, die erreicht werden sollte, ist damit hergestellt. Die Details, die im Moment nicht interessieren, die Ergebnisse der einzelne Vertreter, gehen in die berechneten Summen ein.


Rheinwerk Computing

Ändern des Pivot-Tabellen-Layouts  downtop

Nun mag es sein, dass die spaltenweise Anordnung des Zeitverlaufs nicht so recht gefällt. Meist sind wir es gewohnt, Zeitverläufe von links nach rechts darzustellen und auch wahrzunehmen. Die Ordnung der Daten kann dadurch umgebaut werden, dass die beiden Felder Daten und Produktgruppe über Kreuz verschoben werden.

1 Im Bereichsabschnitt des Aufgabenbereichs PivotTable-Feldliste ziehen Sie zunächst das Element Werte, das bisher als Zeilenbeschriftung verwendet wurde, in den Bereich Spaltenbeschriftungen.

2 Mit Produktgruppe verfahren Sie andersherum. Sie ziehen das Feld aus dem Bereich Spaltenbeschriftungen heraus und fügen es unter dem Feld Region in den Bereich Zeilenbeschriftungen ein. Die Zeilen werden dann zweistufig gruppiert. Das übergeordnete Kriterium ist Region, das untergeordnete Produktgruppe. Nun stehen die Jahresergebnisse nebeneinander, wie die folgende Abbildung zeigt:

3 Ist die Anordnung der Jahresspalten absteigend, weil das Feld 2003 zuerst in den Datenbereich gezogen wurde, lässt sich die Reihenfolge ändern, wenn Sie die Felder mit rechts anklicken und die Optionen in der ersten Gruppe verwenden, mit denen die Reihenfolge beliebig geändert werden kann.

Kontextmenü mit Optionen für die Umstellung der Reihenfolge

Es ist natürlich bei mehr als einer Zeilenrubrik nicht unerheblich, in welcher Reihenfolge Sie diese anordnen. Wenn Sie z. B. zuerst die Produktgruppe angeben und erst an zweiter Stelle die Region, werden die Daten auch zuerst nach Produktgruppen zusammengefasst und erst innerhalb der einzelnen Gruppen nach der Region.

Um diese Anordnung nachträglich zu erreichen, müssen Sie das Feld Region nur unter das Feld Produktgruppe ziehen.

Hier hat die Produktgruppe Vorrang vor der Region bei der Anordnung der Daten


Rheinwerk Computing

Berichtsfilter  downtop

Nun könnten Sie auf die Idee kommen, die Daten noch weiter zu komprimieren, und zwar so, dass immer nur einzelne Regionen angezeigt werden. Dazu lässt sich das Feld Region im Bereichsabschnitt des Aufgabenbereichs PivotTable-Feldliste in den Bereich Berichtsfilter ziehen, der in früheren Versionen noch als Seitenbereich bezeichnet wurde. Denselben Effekt hat der Kontextmenübefehl Zum Berichtsfilter hinzufügen in der Feldliste. Die Tabelle sieht dann so aus:

Pivot-Tabelle mit Berichtsfilter für die Regionen

Die zunächst angezeigten Werte geben das Gesamtergebnis für alle Gebiete wieder. Wenn Sie nun die Schaltfläche mit dem Pfeil anklicken, können Sie die Daten für die einzelnen Regionen zur Anzeige bringen.

Nun könnte es sein, dass für jede Region eine separate Tabelle gewünscht wird. Das ist bei diesem Stand der Pivot-Tabelle schnell erreicht. Wenn Sie in der Gruppe Optionen/PivotTable das Menü der Schaltfläche Optionen öffnen, wird der Befehl Berichtsfilterseiten anzeigen angeboten, der für jede mögliche Seite, hier also für jede Region, ein neues Blatt in die Mappe einfügt und die Register mit den Elementnamen beschriftet.

Auswahl der Region West

Alle neuen Tabellen sind vollwertige Pivot-Tabellen. Es ist dann auch kein Problem, diese Blätter in verschiedene Arbeitsmappen zu kopieren oder zu verschieben, um sie beispielsweise anderen Stellen zur Verfügung zu stellen.

Eigene Blätter für die Regionen


Rheinwerk Computing

Daten weiter komprimieren  downtop

Sie können das Ordnungskriterium »Region« auch ganz aus der Pivot-Tabelle entfernen, um die Auswertung noch stärker zusammenzufassen. Wählen Sie dazu das Feld in der Feldliste einfach ab. Die Umsatzzahlen für die einzelnen Jahre werden jetzt nur noch nach Produktgruppen getrennt geführt.

Reduzierte Darstellung


Rheinwerk Computing

Optionen für den Pivot-Tabellenbericht  downtop

Wenn eine Zelle in einer Pivot-Tabelle ausgewählt ist, wird auf dem Register Optionen in der Gruppe PivotTable die Schaltfläche Optionen angeboten. Der dazu gehörende Dialog PivotTable-Optionen kann auch direkt über das Kontextmenü jeder Zelle der PivotTable erreicht werden.

Das Dialogfeld PivotTable-Optionen

Im ersten Feld können Sie den Namen der Pivot-Tabelle ändern. Auf dem Register Layout & Format steht ein Reihe von Optionen zur Verfügung, die die Darstellung der Beschriftungen und Werte betreffen. Die Option Für Fehlerwerte anzeigen erlaubt die Eingabe eines Textes, der anstelle einer Fehlermeldung angezeigt wird. Auch für leere Zellen können Texte festgelegt werden, etwa »Wert fehlt«. Das Häkchen bei Zellformatierung bei Aktualisierung behalten hat zur Folge, dass ein spezielles Format für eine bestimmte Datengruppe, etwa eine Hintergrundfarbe, auch dann für die betreffenden Elemente beibehalten wird, wenn die Daten umgruppiert werden. Auf andere Optionen wird weiter unten noch eingegangen.

Das Register Summen & Filter im Dialog PivotTable-Optionen

Auf dem Register Summen & Filter kann gewählt werden, ob zeilen- oder spaltenweise Gesamtsummen angezeigt werden oder nicht.

PivotTable-Optionen zur Anzeige

Das Register Anzeige bietet zunächst die Optionen Schaltflächen zum Erweitern/Reduzieren anzeigen und Kontextbezogene Quickinfos anzeigen. Weitere Optionen betreffen die Feldbeschriftungen und die Reihenfolge in der Feldliste.

PivotTable-Optionen zum Drucken

Auf dem Register Druckt kann Einfluss darauf genommen werden, wie eine Pivot-Tabelle ausgedruckt wird.

PivotTable-Optionen zu Daten

Das Register Daten schließlich erlaubt Einstellungen zur Handhabung der PivotTable-Daten. Quelldaten mit Datei speichern bedeutet, dass externe Daten zusammen mit der Pivot-Tabelle gespeichert werden.


Rheinwerk Computing

Hinzufügen von Feldern  downtop

In den bisher gezeigten Pivot-Tabellen sind die einzelnen Vertreter vernachlässigt worden. Wenn deren Ergebnisse gefragt sind, können Sie die Pivot-Tabelle leicht umbauen oder auch eine neue für diesen Zweck anlegen.

Für die Erweiterung stellen Sie den Zellzeiger wieder in die Pivot-Tabelle und ordnen das Feld Vertreter aus der PivotTable-Feldliste über das Kontextmenü entweder dem Bereich Zeilenbeschriftungen oder dem Bereich Spaltenbeschriftungen zu, je nachdem, wie die Daten geordnet werden sollen. Wird das Feld Vertreter dem Bereich Spaltenbeschriftungen hinzugefügt, finden Sie anschließend für jeden Vertreter drei Spalten mit den Jahresergebnissen.

Produktgruppenergebnisse nach Vertretern aufgeschlüsselt

Wird das Feld Vertreter in den Bereich Zeilenbeschriftungen geschoben, haben Sie die Wahl, das Vertreter-Feld über oder unter das Feld Produktgruppe zu setzen. Im ersten Fall werden die Daten erst nach Vertretern und pro Vertreter jeweils noch einmal nach Produktgruppen geordnet; im zweiten Fall ist es umgekehrt.


Rheinwerk Computing

Wenn sich die Werte in der Liste ändern  downtop

Falls Sie versuchen, einen der Werte im Datenbereich der Pivot-Tabelle zu verändern, werden Sie feststellen, dass das nicht möglich ist. Sie erhalten den Hinweis, dass dieser Teil der Pivot-Tabelle nicht geändert werden kann. Änderungen von Daten können sinnvollerweise immer nur in der Ausgangstabelle, also bei den Quelldaten vorgenommen werden, die die Pivot-Tabelle auswertet.

Nun passt Excel die Pivot-Tabelle(n) aber nicht automatisch an, wenn in der Vertriebsliste ein Wert geändert wird. Sie erhalten leider auch keinen Hinweis, dass die Quelldaten und die Auswertungen in der Pivot-Tabelle nicht mehr übereinstimmen.

Um die Pivot-Tabelle auf den neuesten Stand zu bringen, können Sie eine Zelle darin auswählen und in der Gruppe Daten auf dem Register Optionen die Schaltfläche Aktualisieren anklicken oder Alt-F5 benutzen.

Es gibt auch die Möglichkeit festzulegen, dass eine Pivot-Tabelle automatisch beim Öffnen der Arbeitsmappe aktualisiert wird. Dazu muss im Dialogfeld PivotTable-Optionen auf dem Register Daten die Option Aktualisieren beim Öffnen der Datei aktiviert werden.


Rheinwerk Computing

18.1.3 Sortieren in der Pivot-Tabelle  downtop

So wie es unterschiedliche Ordnungskriterien für die Pivot-Tabelle insgesamt geben kann, je nach Anordnung der verschiedenen Bereiche, so sind auch innerhalb der Elemente, die zu einem dieser Felder gehören, jeweils unterschiedliche Anordnungen möglich. Sie sortieren entweder nach der Reihenfolge der Beschriftungen oder nach der Rangfolge der Werte. Außerdem werden zwei verschiedene Verfahren angeboten:

  • Die Sortierung kann bei Bedarf manuell vorgenommen werden. Dafür werden die in Excel üblichen Sortierbefehle verwendet.
  • Für jedes Feld kann eine dynamische Sortierung einzeln festgelegt werden, die immer dann automatisch angepasst wird, wenn sich die Daten in der Quelltabelle geändert haben und die Pivot-Tabelle durch den Befehl Aktualisieren aufgefrischt wird.

Rheinwerk Computing

Manuelle Sortierung  downtop

Nehmen Sie als Beispiel eine Pivot-Tabelle, die nur die Umsätze pro Vertreter anzeigt.

Um die Tabelle nach den Vertreternamen zu sortieren, die hier als Beschriftungen der Zeilenelemente benutzt werden, klicken Sie in der Tabelle den Feldnamen an, zu dem die Elemente gehören – hier Vertreter – oder einen der Vertreternamen und arbeiten mit den Sortiersymbolen in der Gruppe Optionen/ Sortieren. Die zu den Elementen gehörenden Daten werden automatisch mit geordnet. Stattdessen kann auch über die Schaltfläche zum Feldnamen oder das Kontextmenü eines der Elemente im Feld der Befehl Sortieren aufgerufen werden.

Soll dagegen nach der Rangfolge der Umsatzergebnisse des Jahres 2003 sortiert werden, wählen Sie zunächst einen der Umsatzwerte in der Spalte 2003. Benutzen Sie dann das Sortiersymbol Nach Größe sortieren (absteigend).

Nach Namen sortierte Vertreter-Tabelle

Sortierung nach Werten


Rheinwerk Computing

Dynamische Sortierung  downtop

Für jedes Feld in der Pivot-Tabelle kann separat eine bestimmte Sortierung festgelegt werden, die bei jeder Aktualisierung der Tabelle automatisch wiederhergestellt wird. Nehmen Sie als Beispiel eine Auswertung der Vertriebstabelle, bei der die Vertreter im Zeilenfeld, die Produktgruppen im Spaltenfeld angeordnet sind. Die Tabelle soll immer so ausgegeben werden, dass der Vertreter mit dem höchsten Gesamtumsatz zuerst aufgeführt wird. Die erste Abbildung zeigt die Tabelle zunächst so, wie Excel sie bei alphabetischer Sortierung der Vertreter ausgibt.

Tabelle mit alphabetischer Reihenfolge

1 Um die Zeilen der Vertreter dynamisch nach dem Umsatz zu sortieren, klicken Sie den Feldnamen Vertreter an und wählen in der Gruppe Optionen/Sortieren die Schaltfläche Sortieren.2 Wählen Sie die Option Absteigend, und wählen Sie aus dem Listenfeld dazu den Eintrag Summe von 2003.

3 Die Tabelle wird so ausgegeben, dass der Vertreter mit dem höchsten Gesamtergebnis immer zuerst erscheint.

Testen Sie die Sortierautomatik, indem Sie in der Quelltabelle gezielt bestimmte Werte ändern. Wenn Sie die Pivot-Tabelle aktualisieren, wird die gewünschte Sortierreihenfolge wiederhergestellt.


Rheinwerk Computing

18.1.4 Schnelle Datenauszüge zu einzelnen Werten  downtop

Dass die Daten in der Pivot-Tabelle und in der Ausgangstabelle verknüpft sind, hat noch einen sehr praktischen Nebeneffekt. Sie können sich blitzschnell bestimmte Auszüge aus dem gesamten Datenmaterial besorgen. Wenn Sie z. B. einen der zusammenfassenden Werte, also hier eine der Umsatzsummen, in der Pivot-Tabelle doppelt anklicken, werden alle Daten aus der Originaltabelle, die in diesen Wert eingeflossen sind, auf einem Extrablatt zusammengestellt. Wenn Sie z. B. in der schon beschriebenen komprimierten Pivot-Tabelle die Zelle B6 doppelt anklicken, ...

... erhalten Sie eine detaillierte Liste zu den Waschmaschinen. Diese neue Tabelle ist keine Pivot-Tabelle. Sie kann ganz normal wie eine Tabelle behandelt werden. Excel weist der Liste eines der automatischen Listenformate zu.

Listenauszug für Waschmaschinen

Diese Funktion, einen Wert in die in ihn einfließenden Detailwerte zu zerlegen, kann auch unterbunden werden, wenn Sie verhindern wollen, dass jemand die Detaildaten ansieht. Das mag sinnvoll sein, wenn Sie eine Pivot-Tabelle in eine eigene Mappe kopieren und weiterreichen. Dazu muss bei der Anlage der Pivot-Tabelle über das Dialogfeld PivotTable-Optionen auf dem Register Daten die Option 'Details anzeigen' aktivieren abgeschaltet werden.


Rheinwerk Computing

Einstellungsänderungen zu einzelnen Feldern  downtop

Für jedes Feld, das in der Pivot-Tabelle erscheint, können einzeln bestimmte Einstellungen festgelegt werden. Wenn der Feldname ausgewählt ist, benutzen Sie in der Gruppe Optionen/Aktives Feld die Option Feldeinstellungen. Geöffnet wird das Dialogfeld Feldeinstellungen, das zwei Register anbietet. Bei einem Seitenfeld ist der Dialog noch dahingehend erweitert, dass auch hier Elemente des Feldes ausgeblendet werden können.

Dialogfeld Feldeinstellungen

Zunächst kann unter Benutzerdefinierter Name, wenn es gewünscht wird, der Name des Feldes geändert werden, etwa um eine deutlichere Bezeichnung zu erreichen.


Rheinwerk Computing

Wahl der Auswertungsart  downtop

Interessanter sind die Möglichkeiten auf dem Register Teilergebnisse & Filter. Excel zeigt normalerweise nur Teilergebnisse für die äußeren Spalten- bzw. Zeilenfelder an, falls mehr als ein Spalten- bzw. Zeilenfeld existiert, also das am weitesten nach links angeordnete Spalten- bzw. Zeilenfeld. Handelt es sich um Zahlenwerte, werden Summen angezeigt, bei Textfeldern wird die Anzahl der vorkommenden Einträge geliefert. Sie können für die äußeren Spalten- und Zeilenfelder aber auch noch zusätzliche Teilauswertungen hinzufügen oder auch alle Teilergebnisse entfernen.

Angenommen, die Pivot-Tabelle ist im Bereich Zeilenbeschriftungen zunächst nach Regionen und innerhalb der Regionen nach Produktgruppen geordnet. Excel liefert zunächst pro Vertriebsgebiet eine Zwischensumme.

Soll zusätzlich zur Teilsumme noch der Mittelwert angezeigt werden, markieren Sie den Feldnamen Region, öffnen den Dialog Feldeinstellungen und wählen unter Teilergebnisse die Option Benutzerdefiniert und markieren dann in der Liste die Funktion Mittelwert. Es können auch mehrere Funktionen gleichzeitig ausgewählt werden, wenn Sie gleichzeitig die Umschalttaste gedrückt halten. Sie haben hier dieselben Möglichkeiten wie bei der Funktion Teilergebnisse. Die folgende Abbildung zeigt eine Tabelle mit zwei Arten von Teilergebnissen für die verschiedenen Regionen. Dabei bezieht sich der Mittelwert auf die einzelnen Vertreterergebnisse.

Tabelle mit mehreren Teilergebnissen

Sollen die Teilergebnisse wieder entfernt werden, benutzen Sie die Option Keine. Über das Dialogfeld lässt sich das Feld auch ganz ausblenden. Klicken Sie dazu auf die Schaltfläche Ausblenden.


Rheinwerk Computing

Teil- und Gesamtergebnisse ein- und ausblenden  downtop

Es ist jederzeit möglich, einmal eingerichtete Teil- oder Gesamtergebnisse vorübergehend auszublenden. Auf dem Register Entwurf werden in der Gruppe Layout die Schaltflächen Teilergebnisse und Gesamtergebnisse angeboten, die Menüs mit Optionen zur Anzeige der Ergebnisse öffnen.

Optionen für Teil- und Gesamtergebnisse


Rheinwerk Computing

Layout-Alternativen  downtop

Über die Schaltfläche Berichtslayout in der Gruppe Optionen/Layout erreichen Sie Optionen für eine andere Darstellung der Tabellenelemente, die insbesondere bei mehreren Ordnungskriterien im Zeilenbereich interessant sind. Die folgenden Abbildungen zeigen den Effekt an derselben Tabelle:

Berichtslayoutoptionen

Im Kurzformat werden die Feldnamen ersetzt durch einen allgemeinen Namen: Zeilenbeschriftungen, Spaltenbeschriftungen. Die Elementnamen erscheinen in derselben Spalte, wobei für nachrangige Felder Einzüge verwendet werden. Diese Darstellung ist besonders kompakt.

Tabelle im Kurzformat

In den beiden anderen Formaten werden die Feldnamen angezeigt und für jedes Feld eine eigene Spalte verwendet.

Tabelle im Gliederungsformat

Im Gliederungsformat werden die Elemente zu dem untersten Feld im Zeilenbeschriftungsbereich jeweils eine Zeile tiefer ausgegeben, im Tabellenformat erscheinen sie dagegen in derselben Zeile wie die Namen der Elemente.

Tabelle im Tabellenformat

Wird mit mehreren Ebenen im Bereich Spaltenbeschriftungen gearbeitet, gilt Entsprechendes für diese.


Rheinwerk Computing

18.1.5 Ändern der Berechnungsart  downtop

Wenn Sie nichts ändern, summiert Excel in den Zellen des Wertebereichs die zusammengehörigen Werte, etwa die Umsätze der verschiedenen Vertreter für eine Produktgruppe. Bei Textfeldern wird die Anzahl angegeben, etwa die Anzahl der Vertreter in einer Region.

Sie haben die Möglichkeit, auch andere Berechnungsmethoden zur Zusammenfassung der Daten zu verwenden. Eine solche Änderung gilt jeweils für ein bestimmtes Wertfeld. Wählen Sie dazu ein Wertfeld aus und verwenden Sie Optionen/Aktives Feld/Feldeinstellungen. Diesmal zeigt Excel mit diesem Befehl den Dialog Wertfeldeinstellungen.

Sie erhalten zunächst den Hinweis auf den Quellennamen und können dann wieder unter Benutzerdefinierter Name den von Excel automatisch generierten Namen ändern. Statt wie im Beispiel »Summe von 2003« kann etwa »Umsatz 2003« eingetragen werden.

Auf dem Register Zusammenfassen mit wählen Sie die Funktion aus, die zur Berechnung verwendet werden soll. Sie finden auch hier wieder die statistischen Funktionen, die auch der Befehl Teilergebnisse anbietet.

Über die Schaltfläche Zahlenformat haben Sie außerdem die Möglichkeit, die Werte im Datenbereich zu formatieren. Excel übernimmt zunächst immer das Format der Quelldaten, es kann aber gut sein, dass dieses Format für die zusammenfassenden Ergebnisse nicht günstig ist.

Einstellungen zu Wertfeldern

Interessante Möglichkeiten bietet auch das zweite Register in diesem Dialog Werte anzeigen als. Wenn Sie z. B. anstelle der Summenwerte jeweils die Prozentanteile der zusammengefassten Werte sehen wollen, können Sie unter Werte zeigen als den Punkt % des Ergebnisses wählen. Auch Differenzbildungen zu anderen Spalten sind möglich. Dafür muss dann unter Basisfeld bzw. Basiselement der passende Eintrag ausgewählt werden.

Wahl der Prozentanzeige

Die nächste Abbildung zeigt eine Tabelle mit Prozentanteilen.

Tabelle mit Prozentanteilen

Auch Differenzbildungen zu anderen Spalten sind möglich. Um etwa die Differenz der anderen Regionen zur Region West zu berechnen, wählen Sie aus der Liste die Option % Differenz von, dann unter Basisfeld das Feld Region und unter Basiselement eben West aus.


Rheinwerk Computing

Besondere Möglichkeiten bei Berichtsfiltern  downtop

Für das Layout einer Pivot-Tabelle mit Berichtsfiltern bietet Excel noch einige zusätzliche Möglichkeiten. So wie bei den Zeilen- oder Spaltenfeldern kann auch bei den Berichtsfiltern mit mehreren Feldern gleichzeitig gearbeitet werden.

Im folgenden Beispiel wird als erster Filter die Region, als zweiter die Produktgruppe verwendet. Sind mehrere Berichtsfilter gleichzeitig vorgesehen, haben Sie nun die Wahl, diese untereinander in einer Spalte oder nebeneinander in einer Zeile anzuordnen oder auch bei mehr als zwei Seitenfeldern diese sowohl zeilen- als auch spaltenweise anzuordnen.

Wahl des Layouts bei Berichtsfiltern

Die Entscheidung darüber kann über das schon beschriebene Dialogfeld PivotTable-Optionen getroffen werden. Sollen beispielsweise die Berichtsfilter nebeneinander angeordnet werden, wählen Sie auf dem Register Layout & Format unter Felder im Berichtsfilterbereich anzeigen/Seitenlayout die Einstellung Rechts, dann nach unten.

Sind nun beispielsweise vier Seitenfelder vorhanden, können Sie bei Berichtsfilterfelder pro Zeile »2« angeben, dann werden zwei Zeilen mit je zwei Filtern angelegt, wobei die Hierarchie von oben nach unten verläuft.


Rheinwerk Computing

Ein- und Ausblenden von Detailinformationen  downtop

Wird in einer Pivot-Tabelle mit mehreren Feldern im Zeilen- oder Spaltenbereich gearbeitet, können die untergeordneten Elemente sehr einfach per Doppelklick auf das jeweils höherrangige Element aus- und wieder eingeblendet werden. Wird z. B. der Elementname einer bestimmten Produktgruppe – hier z. B. der Eintrag »Waschmaschinen« – doppelt angeklickt, lassen sich für diese Warengruppe die Einzelergebnisse der Vertreter ausblenden. Erneuter Doppelklick blendet die Details wieder ein.

Statt mit dem Doppelklick kann auch mit den »+/–«-Symbolen aus der Gruppe Optionen/Aktives Feld oder mit den »+/–«-Schaltflächen vor den höherrangigen Elementnamen gearbeitet werden.

Die Einzelergebnisse für die Produktgruppe »Waschmaschinen« sind ausgeblendet.


Rheinwerk Computing

18.1.6 Neue Gruppen zusammenstellen  downtop

Wenn es zu einem Feld zahlreiche Elemente gibt, ist es möglich, Untergruppen zu bilden und die Ergebnisse für diese Untergruppen berechnen zu lassen. Bei Textelementen kann das manuell geschehen, bei Zahlen- oder Datumselementen ist auch eine automatische Zusammenfassung nach bestimmten Intervallen möglich.

Wenn eine Firma z. B. mehrere Vertreter hat, muss das nicht heißen, dass sie alle gleich behandelt werden. Die Provision kann bei einigen höher sein als bei den anderen. Angenommen, Sie haben zwei verschiedene Provisionsstufen und wollen die Vertreter einer der beiden Gruppen zuordnen.

Im Folgenden wird von einer Pivot-Tabelle ausgegangen, in der nur die Vertreter als Zeilenbeschriftungsfeld erscheinen. Zunächst sollten Sie, falls es nicht schon geschehen ist, den speziellen Auswahlcursor aktivieren, mit dem sich Zeilen oder Spalten bequem auswählen lassen. Wählen Sie in der Gruppe Optionen/Aktionen über die Schaltfläche Auswählen die Option Auswahl aktivieren. Wenn Sie jetzt den Mauszeiger an den linken Rand einer Vertreterzelle – nicht auf den Zeilenkopf! – führen, verwandelt er sich in einen schwarzen Pfeil. Nun genügt ein Klick, um die Zeile mit Elementnamen und Daten komplett auszuwählen.

Um die Spalte mit den Vertretern in zwei Gruppen zu unterteilen, wählen Sie zuerst die Vertreter, die zur ersten Gruppe gehören sollen, mit der Maus aus. Die Elemente müssen nicht benachbart sein. Drücken Sie und führen Sie einen Mausklick aus.

Dann benutzen Sie in der Gruppe Optionen/Gruppieren den Befehl Gruppenauswahl. Excel fasst die ausgewählten Elemente zu einer Gruppe zusammen und fügt Feld und ein Gruppenelement jeweils mit vorläufigen Namen ein. Beide Namen können über die Bearbeitungsleiste beliebig geändert werden, wenn Sie sie vorher anklicken.

Wählen Sie dann die restlichen Vertreter mit der Maus aus und klicken Sie erneut auf das Symbol Gruppenauswahl. Die Abbildung zeigt das Ergebnis der Gruppierung:

Verteilung der Vertreter auf zwei Gruppen

Um die Gruppen wieder aufzuheben, ziehen Sie mit dem Pfeilcursor über beide Gruppenbereiche und benutzen in der Gruppe Optionen/Gruppieren oder über das Kontextmenü den Befehl Gruppierung aufheben.


Rheinwerk Computing

18.1.7 Zahlenmaterial ordnen  downtop

An dieser Stelle soll noch auf einige andere Möglichkeiten hingewiesen werden, die auf das Vertriebsbeispiel nicht anwendbar sind. Enthält eine Spalte Zahlenelemente, können die Zahlen nach bestimmten Intervallen gruppiert werden. Als Beispiel wird eine kleine Tabelle für die Ergebnisse eines Tests verwendet. In der zweiten Spalte ist das Alter der jeweiligen Testperson eingetragen, in der dritten das Testergebnis. Sie werden vielleicht zunächst Zweifel haben, ob hier mit einer Pivot-Tabelle überhaupt irgend etwas erreicht werden kann.

Die Ausgangstabelle

Der erste Versuch ist eher ernüchternd. Sie können eine Pivot-Tabelle erzeugen, die für die Zeilenbeschriftung das Alter und als Wertfeld das Testergebnis benutzt. Die Pivot-Tabelle liefert dann mehr oder weniger eine Kopie der Originaltabelle, abgesehen von der Summe, die am Ende erscheint. Aber damit sind die Möglichkeiten nicht erschöpft. Wenn Sie beispielsweise die Frage klären wollen, wie die durchschnittlichen Testergebnisse in einer bestimmten Altersklasse aussehen, kann die Pivot-Tabelle helfen. Wählen Sie dazu ein beliebiges Element in der Spalte mit den Altersangaben aus und benutzen Sie in der Gruppe Optionen/Gruppieren den Befehl Gruppenfeld. Da es sich um Zahlenelemente handelt, wird ein kleines Dialogfeld angeboten, in dem Sie in diesem Fall bestimmte Altersintervalle festlegen können.

Wahl der Intervalle

Das Dialogfeld zeigt zunächst den niedrigsten und den höchsten vorhandenen Alterswert an und schlägt unter Nach ein Intervall von zehn Jahren vor. Sie können diese Werte übernehmen oder ändern. Wenn Sie mit OK bestätigen, wird die Pivot-Tabelle mit den Altersklassen ausgegeben.


Rheinwerk Computing

Durchschnitt ermitteln  downtop

Nun ist es in diesem Fall allerdings nicht sehr sinnvoll, die Testergebnisse zu addieren. Es interessiert ja, wie schon erwähnt, das durchschnittliche Testergebnis pro Altersklasse. Das ist schnell geregelt.

1 Wählen Sie eines der Testwerte aus und benutzen Sie Optionen/Aktives Feld/Feldeinstellungen.

2 Unter Zusammenfassen mit wählen Sie Mittelwert.

3 Um die vielen Nachkommastellen zu entfernen, klicken Sie noch die Schaltfläche Zahlenformat an und wählen ein Zahlenformat ohne Dezimalstellen.

Die nächste Tabelle zeigt das Ergebnis der Verteilung auf verschiedene Altersklassen:

Gruppierung nach Altersklassen


Rheinwerk Computing

Gruppen nach Zeit oder Datum bilden  downtop

Wenn ein Feld aus Datums- oder Zeitelementen besteht, können Sie ähnlich verfahren. Das Dialogfeld bietet dann eine Gruppierung nach Tagen, Monaten, Quartalen oder Jahren an. Wird Tage gewählt, können mehrere Tage zu einer Gruppe zusammengefasst werden, etwa jeweils zu einer Woche.

Im Folgenden wird zunächst mit einer kleinen Liste gearbeitet, in der die täglichen Besucherzahlen einer Ausstellung über einen Monat hinweg erfasst sind. Wenn nun die Frage interessiert, wie sich die Besucherzahlen auf die verschiedenen Wochen des Monats verteilen, können Sie ähnlich verfahren wie in dem letzten Beispiel.

1 Sie bauen zunächst eine Pivot-Tabelle mit dem Datum als Zeilenbeschriftungsfeld und den Besucherzahlen als Wertfeld auf.

2 Dann wählen Sie ein beliebiges Datum aus und verwenden den Befehl Optionen/Gruppieren/Gruppenfeld.

Gruppierung bei Datumswerten

3 Unter Nach wählen Sie Tage und unter Tage anzeigen 7. (Mit Starten könnte auch noch angegeben werden, wann die erste Woche anfängt.)

Liste mit den Wochenergebnissen


Rheinwerk Computing

18.1.8 Berechnete Felder und Elemente in Pivot-Tabellen  downtop

Pivot-Tabellen lassen sich nicht nur zur Auswertung vorhandener Tabellen verwenden, sondern auch zum Experimentieren mit den ausgeworfenen Daten. Zwar zeigt die Pivot-Tabelle in Bezug auf die Formeln in der Quelltabelle immer nur die Ergebnisse an, nie die Formeln selbst, aber die Tabelle lässt sich durch berechnete Felder erweitern. Die Formeln beziehen sich entweder auf ein Feld oder auf ein einzelnes Element eines Feldes.

Allerdings können die Formeln nur Bezüge auf Felder der Pivot-Tabelle selbst und konstante Werte enthalten, nicht dagegen Bezüge auf andere Zellen im Tabellenblatt. Um sich auf Elemente der Pivot-Tabelle zu beziehen, werden die Elementnamen bzw. die Feldnamen selbst verwendet.

Auf diese Weise sind z. B. Planspiele möglich, etwa um festzustellen, welche Werte bei einer 20  %igen Steigerung der Umsätze für die einzelnen Elemente erreicht werden.


Rheinwerk Computing

Berechnetes Feld einfügen  downtop

Nehmen Sie beispielsweise folgende Ausgangstabelle:

Die Pivot-Tabelle vor dem Einfügen eines berechneten Feldes

1 Klicken Sie das Spaltenbeschriftungsfeld Produktgruppe an und wählen Sie auf dem Register Optionen in der Gruppe Tools den Befehl Formeln und im Untermenü Berechnetes Feld.2 Im Dialogfeld Berechnetes Feld einfügen sollten Sie zunächst den vorgeschlagenen Feldnamen durch einen passenden Namen ersetzen, etwa »Planwert«.

3 Geben Sie im nächsten Textfeld die Formel ein. Die Formel soll sich auf das Wertfeld 2003 beziehen. Dieses Feld kann aus der Liste Felder ausgewählt und mit Feld einfügen in die Formel übernommen werden.

4 Als Planwert ist pauschal vorgesehen, die Werte von 2003 jeweils um 20  % zu übertreffen. Also wird in der Formel noch* 1,2 angehängt.

5 Sollen keine weiteren berechneten Felder definiert werden, kann das Dialogfeld mit OK verlassen werden, ansonsten klicken Sie die obere Schaltfläche Hinzufügen an und bestimmen das nächste berechnete Feld.

6 In der Tabelle werden für das neue Feld jeweils zusätzliche Spalten mit den Planwerten für die einzelnen Werte eingefügt.

Wenn Sie berechnete Felder zu einer Pivot-Tabelle hinzufügen, erscheinen diese auch in der Feldliste im Aufgabenbereich.


Rheinwerk Computing

Formeln für einzelne Elemente  downtop

In diesem Fall berechnet die oben benutzte Feldformel Planwerte für alle Werte, die zu dem Feld Produktgruppe gehören. Es ist aber auch möglich, Formeln zu verwenden, die nur Werte für ein bestimmtes Element liefern. Soll beispielsweise nur bei den Waschmaschinen berechnet werden, wie sich der Umsatz prozentual zu dem der Kühlaggregate verhält, gilt folgende Vorgehensweise:

1 Klicken Sie auf den Elementnamen Waschmaschinen und benutzen Sie im Menü der Schaltfläche Optionen/Tools/Formeln den Befehl Berechnetes Element.

2 Geben Sie einen Namen für das neue Feld ein, z. B. »WM/KA«.

3 Im Feld Formel können Sie die Elementnamen Waschmaschinen und Kühlaggregate aus der Liste Elemente per Doppelklick übernehmen und dazwischen das Divisionszeichen setzen.

4 Die Tabelle liefert eine zusätzliche Spalte neben der Spalte für die Waschmaschinenumsätze. Am besten formatieren Sie die neue Spalte noch mit einem Prozentformat.

Die Tabelle mit einem zusätzlichen berechneten Element

Wurden vorher bereits Pivot-Tabellen aus derselben Quelle in der Arbeitsmappe angelegt, erscheinen die berechneten Elemente unter Umständen auch in den früheren Tabellen. Um sie auszublenden, muss dann ein entsprechender Filter gesetzt werden.


Rheinwerk Computing

Lösungsreihenfolge festlegen  downtop

Es kann vorkommen, dass verschiedene Formeln Auswirkungen auf die Werte in einer Zelle der Pivot-Tabelle haben. In diesem Fall kann über den Befehl Optionen/Tools/Formeln/Lösungsreihenfolge bestimmt werden, welche dieser Formeln den Wert der Zelle bestimmen soll. Im Dialogfeld lässt sich die Priorität der markierten Formeln mit den Schaltflächen Nach Oben oder Nach Unten entsprechend ändern. Die letzte Formel in der Reihe bestimmt jeweils den aktuellen Wert der Zelle.

Da die Formeln nicht direkt in der Tabelle oder in der Bearbeitungsleiste dokumentiert werden, ist auch im Menü Formeln noch ein Befehl enthalten, der die Formeln in einem separaten Blatt auflistet.


Rheinwerk Computing

18.1.9 Pivot-Tabellen formatieren  downtop

Die Möglichkeiten, Pivot-Tabellen zu formatieren, erreichen mit der Version Excel 2007 ein neues Niveau, das wesentlich professionellere Berichte und Auswertungen erlaubt. Dabei helfen insbesondere die vorgegebenen PivotTable-Formate, die den Formaten für Tabellen weitgehend entsprechen.

Die Festlegung des Zahlenformats ist oben schon angesprochen worden. Alle weiteren Möglichkeiten für die Zellformatierung – Schrift, Hintergrund, Rahmen etc. – stehen auch für die Pivot-Tabelle zur Verfügung. Häufig besteht Interesse, bestimmte Datengruppen z. B. durch unterschiedliche Hintergrundfarben voneinander abzuheben. In Excel 2007 ist gewährleistet, dass eine solche Farbzuordnung nicht verloren geht, wenn die Daten umgruppiert werden.


Rheinwerk Computing

Schnelle Markierung von Datengruppen  downtop

Um die Daten für eine eventuelle Formatierung oder auch für andere Operationen wie Kopieren etc. leichter markieren zu können, stellt Excel in der Gruppe Optionen/Aktionen über die Schaltfläche Auswählen Befehle zur Verfügung, die verwendet werden können, um bestimmte Teile oder die gesamte Pivot-Tabelle auszuwählen.

Die ersten drei Optionen lassen sich erst verwenden, wenn zunächst die gesamte PivotTable ausgewählt oder mit Auswahl aktivieren der oben schon verwendete Auswahlcursor benutzt wurde, um einzelne Elemente zu markieren.

Befehle zum Markieren

Die Auswahl Werte markiert dann jeweils alle Daten aus dem Wertebereich einschließlich der vorhandenen Auswertungen. Separat können auch die Beschriftungen markiert werden, etwa um andere Schriften zuzuordnen.

Der angesprochene Auswahlcursor vereinfacht die Auswahl von Elementgruppen. Um beispielsweise alle Daten für die Region Nord zu markieren, klicken Sie in der Spalte Region mit dem schwarzen Pfeil den linken Rand der Bezeichnung Nord an. Alle Zellen für Nord werden markiert. Nun können Sie mit Hilfe der Markierungssymbole für die aktuelle Auswahl bestimmen, ob nur die Werte oder die Beschriftungen oder beide markiert werden sollen.


Rheinwerk Computing

Farben für zusammengehörige Daten  downtop

Sind beispielsweise die Werte der Region Nord markiert, lässt sich dafür ganz einfach eine Hintergrundfarbe vergeben. Ein Klick auf das Symbol Füllfarbe in der Gruppe Start/Schriftart und die Auswahl einer entsprechenden Farbe genügt.

Die Werte der Region Nord mit einer einheitlichen Füllfarbe als Hintergrund

Wird nun nachträglich das Layout der Tabelle geändert, z. B. indem das Feld Region als Spaltenbeschriftungsfeld verwendet wird, bleibt die zugeordnete Füllfarbe trotzdem für die Werte im Gebiet Nord erhalten.

Das Format wandert mit


Rheinwerk Computing

Bedingte Formate in Pivot-Tabellen  downtop

Die in der Version Excel 2007 so praktischen Möglichkeiten der bedingten Formatierung lassen sich sehr effektiv auch in Pivot-Tabellen einsetzen. Die folgende Abbildung zeigt eine Tabelle mit Datenbalken. Wird ein solches Format einer Zellauswahl in einer Pivot-Tabelle zugewiesen, erscheint am Ende des Bereichs die Schaltfläche Formatierungsoptionen, die gezielte Zuweisungen auf bestimmte Werte in der Pivot-Tabelle erlaubt.

PivotTable mit bedingten Formaten

Statt der vorher markierten Zellen könnte in dem abgebildeten Fall auch nur eine Zuweisung zu der ersten Spalte erfolgen, die die aktive Zelle enthält. Bei der zweiten Option würde auch das Gesamtergebnis mitformatiert, bei der dritten nur die Einzelwerte der Vertreter in der betreffenden Spalte.


Rheinwerk Computing

PivotTable-Formate  downtop

Häufig bietet es sich bei Pivot-Tabellen an, sie als geschlossenen Tabellenblock zu formatieren. Dazu kann eine der Formatvorlagen benutzt werden, die auf dem Register Entwurf in der Gruppe PivotTable-Formate angeboten werden. Einfluss auf das konkrete Aussehen dieser Formate haben die Optionen, die in der Nachbargruppe Entwurf/Optionen für PivotTable-Formate enthalten sind.

Optionen für PivotTable-Formate

Wird dort beispielsweise Verbundene Zeilen aktiviert, werden die Zeilen jeweils paarweise formatiert, sodass je nach verwendeter Formatvorlage wechselnde Zellhintergründe verwendet werden. Für die Option Verbundene Spalten gilt Entsprechendes. Die in der Formatpalette angebotenen Muster hängen also von den hier gewählten Optionen ab.

Zuweisen eines PivotTable-Formats

Wurde eine Zelle in der Pivot-Tabelle ausgewählt, kann mit einem Mausklick auf eines der Formatmuster aus der Palette PivotTable-Formate das Format zugewiesen werden. Soll es gelöscht werden, genügt ein Klick auf die Schaltfläche Löschen in der Vorlagen-Palette.

Sollen eigene Vorlagen für Pivot-Tabellen gestaltet werden, öffnen Sie den Dialog über den Palettenbefehl Neue PivotTable-Formatvorlage. Dort können dann die einzelnen Tabellenelemente ausgewählt und über die Schaltfläche Format die Formatierungen ausgewählt werden. Die eigenen Formate tauchen anschließend in der Formatpalette in einem eigenen Block auf.

Entwurf eigener PivotTable-Formate


Rheinwerk Computing

18.1.10 Ändern der Datenquelle, Verschieben, Löschen  downtop

Möglicherweise stellt sich nach dem Aufbau einer komplexen Auswertung in Form einer Pivot-Tabelle heraus, dass auch andere Datenquellen nach diesem Schema analysiert werden können. In solchen Fällen ist es möglich, die Datenquelle nachträglich zu ändern. Benutzen Sie dazu in der Gruppe Optionen/Daten die Schaltfläche Datenquelle ändern und wählen Sie im Dialog die neue Datenquelle aus. Enthält die neue Datenquelle zusätzliche Felder, werden diese in die Feldliste übernommen und können dann dort ausgewählt werden, um auch die entsprechenden Daten in die Pivot-Tabelle einzubinden. Enthält die neue Datenquelle dagegen weniger Felder, werden die in der neuen Quelle nicht vorhandenen Felder aus der Feldliste und aus der Tabelle entfernt.

Soll eine Pivot-Tabelle nur verschoben werden, benutzen Sie in der Gruppe Optionen/Aktionen die Schaltfläche PivotTable verschieben und geben im Dialog die gewünschte Platzierung an.

Das Löschen des Inhalts einer kompletten Pivot-Tabelle ist sehr einfach. Wenn der Zellzeiger innerhalb der Pivot-Tabelle steht, genügt der Befehl Optionen/Aktionen/Löschen.


Rheinwerk Computing

18.1.11 Pivot-Tabelle aus externen Daten  toptop

Die Anwendungsmöglichkeiten für Pivot-Tabellen und -Diagramme gehen weit über die Auswertung von Daten hinaus, die in Arbeitsmappen abgelegt sind. Der Dialog PivotTable erstellen bietet als zweite Option für die Angabe der zu analysierenden Daten Externe Datenquelle verwenden. Um die Datenquelle anzugeben, öffnet die Schaltfläche Verbindung auswählen den Dialog Vorhandene Verbindungen. Die Einrichtung von Verbindungen, die für eine pivotierende Auswertung geeignet sind, etwa der Zugriff auf eine SQL-Datenbank, wird weiter unten noch beschrieben.

Erstellen einer Pivot-Tabelle aus externen Daten

Ist eine der vorhandenen Verbindungen ausgewählt, müssen bei Datenquellen auf einem Server unter Umständen noch die Zugangsdaten eingegeben werden.



Ihr Kommentar

Wie hat Ihnen das <openbook> gefallen? Wir freuen uns immer über Ihre freundlichen und kritischen Rückmeldungen. >> Zum Feedback-Formular
 <<   zurück
  
  Zum Katalog
Zum Katalog: Excel 2007 - Das umfassende Handbuch
Excel 2007 -
Das umfassende
Handbuch

Jetzt bestellen


 Ihre Meinung?
Wie hat Ihnen das <openbook> gefallen?
Ihre Meinung

 Buchtipps
Zum Katalog: VBA mit Excel






 VBA mit Excel


Zum Katalog: Einstieg in VBA mit Excel






 Einstieg in VBA
 mit Excel


Zum Katalog: Excel 2010 im Controlling






 Excel 2010
 im Controlling


Zum Katalog: IT-Handbuch für Fachinformatiker






 IT-Handbuch für
 Fachinformatiker


Zum Katalog: Visual C# 2012






 Visual C# 2012


 Shopping
Versandkostenfrei bestellen in Deutschland und Österreich
InfoInfo




Copyright © Rheinwerk Verlag GmbH 2008
Für Ihren privaten Gebrauch dürfen Sie die Online-Version natürlich ausdrucken. Ansonsten unterliegt das <openbook> denselben Bestimmungen, wie die gebundene Ausgabe: Das Werk einschließlich aller seiner Teile ist urheberrechtlich geschützt. Alle Rechte vorbehalten einschließlich der Vervielfältigung, Übersetzung, Mikroverfilmung sowie Einspeicherung und Verarbeitung in elektronischen Systemen.


[Rheinwerk Computing]

Rheinwerk Verlag GmbH, Rheinwerkallee 4, 53227 Bonn, Tel.: 0228.42150.0, Fax 0228.42150.77, service@rheinwerk-verlag.de