sap dateien mit excel 2010 auswerten - bremen€¦ · in excel 2010 wird ohne nachfrage erneut ein...
TRANSCRIPT
Aus- und Fortbildungszentrum
Lehreinheit für Informationstechnologien
Stand: August 2012
Freie Hansestadt Bremen
SAP Dateien mit Excel 2010 auswerten
Erzeugen einer Pivottabelle
Gestalten einer Pivottabelle
Gruppieren, sortieren und filtern
Externe Daten
Impressum
Herausgeber
Aus- und Fortbildungszentrum für den bremischen öffentlichen Dienst Doventorscontrescarpe 172C 28195 Bremen
Redaktion und Koordination
Aus- und Fortbildungszentrum Lehreinheit für Informationstechnologien Doventorscontrescarpe 172C 28195 Bremen
Tel.: +49 (0)421 361-5337
Fax: +49 (0)421 496-5337 E-Mail: [email protected] E-Mail-Hotline: [email protected]
Die Texte, Bilder und Grafiken sind ein Angebot des Aus- und Fortbildungszentrums. Sie dienen aus-schließlich dem persönlichen Service und der persönlichen Information der Nutzerin und des Nutzers und der bremischen Dienststellen, Einrichtungen und Betriebe.
Eine Vervielfältigung, Veröffentlichung oder Speicherung in Datenbanken sowie jegliche Form der gewerblichen Nutzung oder die Weitergabe an Dritte ist nicht gestattet. Das Copyright für Texte, Bilder und Grafiken liegt beim Aus- und Fortbildungszentrum.
Inhalt
3
©
1. Grundlagen Pivottabelle 5
1.1 Voraussetzungen für eine Pivottabelle 5
1.2 Erstellen einer Pivottabelle 5
1.3 Hinzufügen weiterer Felder 8
1.4 Ändern des Pivottabellen Layouts 9
1.5 Drag and drop und andere Funktionalitäten 11
2. Technik und Aufbau einer Pivottabelle 14
2.1 Der Pivot Cache 14
2.2 Bezeichnungen in einer Pivottabelle 15
3. PivotTable-Tools und die Schaltzentrale PivotTable-Feldliste 18
3.1 Ändern der Datenquelle 18
3.2 Löschen des Pivottabelleninhalts 18
3.3 Pivottabelle verschieben 19
3.4 Markieren der Pivottabellenelemente 19
3.5 Anzeige der PivotTable-Feldliste 20
4. Gestalten der Pivottabelle 21
4.1 Zahlenformat anwenden 21
4.2 Leere Wertezellen mit Nullen auffüllen 23
4.3 Autoformate 24
4.4 Layout-Stile 25
5. Datenquellen für Pivottabellen 30
5.1 Exceltabelle als Datenquelle 30
5.1.1 Filter in der Tabelle 31
5.1.2 Formeln in der Tabelle 34
5.1.3 Funktion Links und Funktion Sverweis 35
5.2 Daten extern übernehmen 37
6. Pivottabellenfelder 41
6.1 Feldeinstellungen für Werte 42
6.2 Feldeinstellungen für Zeilen- bzw. Spaltenbeschriftungen 45
6.3 Felder sortieren 47
6.4 Felder filtern 48
6.5 Filtern mit Datenschnitten 54
6.6 Felder gruppieren 56
7. Drucken einer Pivottabelle 59
8. Berechnetes Feld 60
Lernmaterial 63
Tipps & Tricks 63
Softwarehilfe (Mail-Hotline) 63
Grundlagen Pivottabelle
5
©
1. Grundlagen Pivottabelle
Pivottabellen dienen dazu, Rohdaten unterschiedlich auszuwerten und anschaulich aufzu-
bereiten. Rohdaten können z. B. Exporte aus SAP sein oder Tabellen aus Datenbankpro-
grammen (z. B. Access) oder Rechnungseingangslisten, die mit Excel geführt werden etc.
Pivottabellen bieten die Möglichkeit, andere Sichten auf die Rohdaten zu liefern. Hinzu
kommt, dass diese Sichten dynamisch sind, d. h. Zeilen und Spalten können beliebig ge-
dreht und angepasst werden.
1.1 Voraussetzungen für eine Pivottabelle
Um eine Pivottabelle zu erzeugen,
- müssen die Spalten der Quelltabelle eine eindeutige Überschrift haben,
- müssen die Daten unter den jeweiligen Spaltenüberschriften von einem Datentyp sein
(Text, Zahl, Datum usw.),
- darf der Quellbereich keine komplett leeren Zeilen oder Spalten enthalten,
- es dürfen keine Gliederungen oder sonstige Zusammenfassungen im Quellbereich vor-
handen sein. Bei der Übernahme der Daten aus SAP müssen Sie darauf achten, dass
die Daten z. B. keine Zwischensummen enthalten.
1.2 Erstellen einer Pivottabelle
Im Gegensatz zu vorangegangenen Excel Versionen wird bei der Erstellung einer Pivotta-
belle auf den Pivottabellen-Assistenten verzichtet.
Erzeugen Sie eine Pivottabelle wie folgt:
- Setzen Sie den Cursor in den Datenbereich, aus dem eine Pivottabelle erzeugt werden
soll,
- klicken Sie im Menüband auf die Registerkarte Einfügen,
wählen Sie aus der Gruppe Tabellen den Befehl PivotTable.
Wenn Sie direkt auf den Befehl klicken, gelangen Sie sofort
in das Dialogfeld PivotTable erstellen. Klicken Sie auf den
Listenpfeil , erhalten Sie die Auswahl zwischen PivotTable
und PivotChart (grafische Darstellung).
6
Grundlagen Pivottabelle
- Sie erhalten das folgende Dialogfeld:
- Zwei wichtige Fragen werden in diesem Dialogfeld geklärt, woher stammen die Da-
ten, aus der die Pivottabelle erzeugt werden soll und wo soll die entstehende
Pivottabelle gespeichert werden.
- Klicken Sie abschließend auf die Schaltfläche OK.
- Excel fügt ein neues Tabellenblatt hinzu, das eine leere Pivottabelle enthält. Gleich-
falls automatisch erscheint das Dialogfeld PivotTable-Feldliste mit den Tabellen-
feldern.
- Das Dialogfeld PivotTable-Feldliste ist die Schaltzentrale für den Aufbau der Pivotta-
belle. Sie können dieses Dialogfeld größer ziehen, in dem Sie auf den Rand zeigen
und mit gedrückter Maustaste ziehen.
Grundlagen Pivottabelle
7
©
- Sie können nun die Pivottabelle befüllen, in dem Sie beispielsweise auf das Kontroll-
kästchen bei Konto klicken.
- Nachdem Sie ein Häkchen bei Konto gesetzt haben, erscheinen in der Pivottabelle die
einzelnen Elemente der Spalte Konto. Gleichzeitig wird das Feld Konto im unteren
Bereich des Dialogfeldes, dem so genannten Layout-Abschnitt, in den Bereich
Zeilenbeschriftungen eingetragen.
- Klicken Sie nun das Feld Betrag in Hauswährung an. Die Pivottabelle ändert sich
wie folgt:
8
Grundlagen Pivottabelle
- Setzen Sie ein Häkchen an ein Textfeld, wird automatisch eine Zeilenbeschriftung ein-
gefügt. Setzen Sie das Häkchen an ein Zahlenfeld werden automatisch die Werte
summiert. Und zwar für jedes Element der Zeilenbeschriftung.
1.3 Hinzufügen weiterer Felder
Sie können weitere Felder hinzufügen, indem Sie die entsprechenden Felder aus der Liste
anhaken.
Das weitere Feld, in diesem Fall das Feld Kostenstelle, wird unten im Layout-Abschnitt
Zeilenbeschriftungen unter dem Feld Konto eingefügt. In der Pivottabelle wird es als
weitere Ebene angezeigt. Es entsteht eine Gliederung. Sie müssen nur bedenken, welche
Hierarchie Sie darstellen wollen. Jedes weitere angehakte Kontrollkästchen in der Feldlis-
te fügt eine weitere Gliederungsebene ein. In diesem Fall ist die Reihenfolge: oberste
Ebene das Konto, darunter die Kostenstelle.
Hätten Sie zuerst das Feld Kostenstelle und dann das Feld Konto angeklickt, sähe die
Pivottabelle folgendermaßen aus:
Grundlagen Pivottabelle
9
©
Hätten Sie weitere nummerische Felder, die Sie anklicken könnten, würden diese im Wer-
tebereich angebracht und entsprechende Spalten eingefügt.
1.4 Ändern des Pivottabellen Layouts
In der Abbildung oben sind die Kostenstellen die oberste Ebene. Darunter stehen die
Konten, die mehrfach erscheinen. Sie können die Kostenstellen oder die Konten neu ar-
rangieren.
- Klicken Sie dazu im Dialogfeld PivotTable-Feldliste im Layout-Abschnitt Zeilenbe-
schriftungen auf den Listenpfeil.
- Das Auswahlmenü bietet im oberen Abschnitt die Möglichkeit, die Reihenfolge zu
ändern. Sie können die Kostenstelle jeweils um eine Position nach unten bewegen
oder ans Ende stellen. Die anderen Optionen sind ausgegraut, da das Feld Kosten-
stelle bereits an erster Stelle steht.
- Sie können das ausgewählte Feld auch in einen anderen Bereich verschieben. Im Be-
reich darunter finden Sie die Wechselmöglichkeiten.
- Sie können das Feld auch ganz entfernen .
10
Grundlagen Pivottabelle
- Der letzte Punkt Feldeinstellungen führt zu einem weiteren Dialogfeld, das an
anderer Stelle erläutert wird.
- Klicken Sie nun auf den Eintrag Wechseln zu Spaltenbeschriftungen. Das Ergebnis
sieht wie folgt aus:
Wie Sie sehen, stehen die Kostenstellen nun als Spaltenüberschriften in der Tabelle.
Obendrein erhalten Sie auch noch die Gesamtergebnisse für die jeweilige Zeile bzw.
Spalte.
Grundlagen Pivottabelle
11
©
1.5 Drag and drop und andere Funktionalitäten
Sie können Pivottabellen durch Klicken auf das entsprechende Kontrollkästchen erzeugen
oder Sie ziehen aus der Feldliste das jeweilige Feld in den Bereich. Im Bild unten sehen
Sie, dass der Mauszeiger sich verändert, wenn Sie auf ein Feld zeigen. Sie können dann
mit gedrückter linker Maustaste das Feld in den vorgesehenen Bereich ziehen.
Auf diese Weise können Sie auch die Felder zwischen den Bereichen hin- und herziehen
oder aus dem Bereich herausziehen.
In Excel 2003 konnten Sie die Felder direkt in die Pivottabelle ziehen. Das können Sie
nach wie vor tun.
- Klicken Sie mit der rechten Maustaste in die Pivottabelle.
Im Kontextmenü klicken Sie auf den Punkt PivotTable-
Optionen.
- Als nächstes erscheint das Dialogfeld PivotTable-
Optionen.
- Wählen Sie die Registerkarte Anzeige.
- Auf der Registerkarte Anzeige setzen Sie ein Häkchen in
das Kontrollkästchen Klassisches PivotTable-Layout.
12
Grundlagen Pivottabelle
Klicken Sie abschließend auf die Schaltfläche OK. Nun können Sie wie gewohnt die Felder
direkt in die Pivottabelle ziehen. Wenn Sie außerdem auch noch den Pivottabellen-
assistenten benutzen wollen, drücken Sie die Tastenkombination
Um den Pivottabellenassistenten benutzen zu können, der ja auch weitere Funktionalitä-
ten enthält, können Sie auch das entsprechende Symbol in die Symbolleiste für den
Schnellzugriff einfügen.
- Klicken Sie in der Symbolleiste für den Schnellzugriff auf das Dreieck am rechten En-
de. Anschließend auf den Eintrag Weitere Befehle.
Grundlagen Pivottabelle
13
©
- Es öffnet sich das Dialogfeld Excel-Optionen.
- Wählen Sie im Listenfeld Befehle auswählen den Eintrag alle, dann werden al-
phabetisch sortiert alle Befehle angezeigt.
- Wählen Sie aus der Liste das Symbol für den PivotTable-Assistenten aus und kli-
cken anschließend auf die Schaltfläche Hinzufügen .
- Das Symbol wird rechts in das Feld eingetragen. Klicken Sie abschließend auf die
Schaltfläche OK.
14
Technik und Aufbau einer Pivottabelle
2. Technik und Aufbau einer Pivottabelle
2.1 Der Pivot Cache
In dem Moment, in dem Sie eine Pivottabelle erzeugen, wird ein Abbild der Daten ihrer
Tabelle in einen sogenannten Pivot Cache geschrieben. Ein Pivot Cache ist ein spezielles
Speichersubsystem, in dem die Daten zwischengelagert werden, um schnell darauf zu-
greifen zu können. Dieser Pivot Cache ist allerdings nicht sichtbar, wird jedoch an die
Datei angehängt. D. h. der Umfang der Datei erhöht sich damit erheblich. Im Cache sel-
ber sind die Daten indiziert, so dass problemlos Zeilenfelder und Spaltenfelder vertauscht
werden können.
Die Daten für die Pivottabelle bezieht Excel aus dem Pivot
Cache. Tragen Sie in die Quelltabelle neue Daten ein,
muss der Cache aufgefrischt werden.
Dazu klicken Sie mit der rechten Maustaste in die Pivotta-
belle und wählen aus dem Kontextmenü den Punkt Aktua-
lisieren.
Haben Sie bereits eine Pivottabelle erzeugt und wollen eine weitere Pivottabelle erstellen,
erfolgte bisher eine Meldung, dass bereits ein Cache vorhanden ist. Sie konnten auswäh-
len, ob Sie den bereits vorhandenen Cache benutzen oder einen neuen erstellen wollen.
In Excel 2010 wird ohne Nachfrage erneut ein Cache erzeugt, wenn Sie die Quelldaten
verändern und anschließend die Befehle Einfügen> Pivottabelle anklicken. Es wird ein
weiterer Cache erzeugt. Damit nimmt der Umfang der Datei zu und es wird mehr Arbeits-
speicher verbraucht.
Wollen Sie den bereits vorhandenen Cache nutzen, können Sie die Pivottabelle einfach
kopieren. Die Kopie benutzt dann den gleichen Cache.
Technik und Aufbau einer Pivottabelle
15
©
2.2 Bezeichnungen in einer Pivottabelle
Sobald Sie einen Pivot Cache erstellt haben, können Sie über die PivotTable-Feldliste auf
den Cache zugreifen und die Tabelle entsprechend gestalten.
Die Feldnamen werden aus den Überschriften der Quelldaten generiert, und zwar von
links nach rechts. Ein Pivotfeld entspricht somit der Überschrift aus den Quelldaten.
Das Pivotfeld weist mehrere Pivotelemente auf. In der unteren Abbildung sehen Sie,
dass das Pivotfeld Artikel unter anderem das Pivotelement Bildschirm enthält. Die
Bezeichnung Bildschirm erscheint in der Quelltabelle mehrere Male. Als Pivotelement
erscheint die Bezeichnung Bildschirm nur ein Mal.
16
Technik und Aufbau einer Pivottabelle
Wollen Sie wissen, wie viel Mal der Begriff Bildschirm in der Quelltabelle vorkommt, zie-
hen Sie das Feld Artikel in den Wertebereich. Da das Feld Artikel ein Textfeld ist, wird
automatisch die Funktion Anzahl benutzt.
In der Abbildung sehen Sie, dass das Feld Artikel, wenn es angeklickt wird, automatisch
in den Bereich Zeilenbeschriftungen gelangt, da es ein Textfeld ist. Der Bereich Zeilenbe-
schriftungen sorgt dafür, dass alle Elemente einmal als Zeilenbeschriftung in der Pivotta-
belle erscheinen.
Ziehen Sie das dieses Textfeld in den Wertebereich, wird automatisch die Funktion An-
zahl aktiviert. Nun können Sie erkennen, wie viel Mal das Wort Bildschirm in der Quellta-
belle aufgelistet ist.
Technik und Aufbau einer Pivottabelle
17
©
Schieben Sie den Mauszeiger auf eine beliebige Stelle der Pivottabelle, wird in einem
Quick-info angezeigt, um welchen Sachverhalt es geht.
Die Pivottabelle bewegt sich außerhalb des normalen Zeilen- und Spaltenschemas von
Excel. Als Orientierung dienen die Beschriftungen in der Quelltabelle. Es müssen, wie
oben bereits beschrieben, die Überschriften eindeutig sein. Aus den Überschriften der
Quelltabelle werden die Pivotfelder gebildet. Aus den darunter befindlichen Daten werden
die Pivotelemente gebildet. Unten in der Abbildung sehen Sie, dass das Element MS-
Office 2003 zweimal vorkommt. Das bedeutet, dass das Element unterschiedlich einge-
tippt wurde, z. B. mit einem zusätzlichen Leerzeichen am Ende.
18
PivotTable-Tools und die Schaltzentrale PivotTable-Feldliste
3. PivotTable-Tools und die Schaltzentrale PivotTable-Feldliste
In vorangegangenen Excelversionen gab es bereits viele verschiedene Möglichkeiten, die
Pivottabelle zu bedienen. In der Version Excel 2010 sind weitere hinzugekommen. Im
folgenden Abschnitt werden einige Funktionalitäten, die früher etwas versteckter waren,
erläutert.
Wenn Sie mit dem Cursor in einer Pivottabelle stehen, wird die Registerkarte PivotTab-
le-Tools aktiviert. Diese wiederum hat zwei weitere Registerkarten: Optionen und
Entwurf.
Beispielsweise können Sie auch hier über die Registerkarte Optionen, in der Gruppe Da-
ten auf das Symbol für Aktualisieren klicken. Oder Sie können wie oben beschrieben
über die rechte Maustaste das Aktualisieren auslösen.
3.1 Ändern der Datenquelle
Wenn neue Datensätze zur Datenquelle hinzukommen oder die Datenquelle sich in ir-
gendeiner Form verändert hat, z. B. durch ein neues Feld, dann muss die Datenquelle
verändert werden.
In der Gruppe Daten finden Sie neben dem Aktualisieren
auch den Befehl Datenquelle ändern.
Wenn Sie auf den Befehl klicken, springt Excel in die Daten-
quelle und das folgende Dialogfeld öffnet sich:
Der Bereich, der die Datenquelle enthält, ist im
Feld Tabelle/Bereich schwarz unterlegt.
Wenn Sie den Tastenschlüssel + (alles
markieren) drücken, wird der neue Bereich mar-
kiert und in das Feld eingetragen.
Klicken Sie auf die Schaltfläche OK, um die Einga-
be abzuschließen.
3.2 Löschen des Pivottabelleninhalts
Bei den Optionen in der Gruppe Aktionen gibt es die Möglichkeit, den Inhalt der Pivotta-
belle komplett zu löschen.
PivotTable-Tools und die Schaltzentrale PivotTable-Feldliste
19
©
3.3 Pivottabelle verschieben
In der Gruppe Aktionen finden Sie den Befehl PivotTable verschieben. Sie können die
komplette Tabelle im gleichen Tabellenblatt verschieben oder in ein neues Tabellenblatt.
Wenn Sie auf den Befehl klicken, öffnet sich das folgende Dialogfeld:
Aus der Vorgängerversion ist die Technik des Markierens übernommen worden.
3.4 Markieren der Pivottabellenelemente
In der Gruppe Aktionen, finden Sie auch den Befehl Auswählen. Wenn Sie auf den Be-
fehl Gesamte PivotTable klicken, wird die Pivottabelle komplett markiert. Jetzt könnten
Sie die Tabelle beispielsweise kopieren.
Wenn die anschließend wieder auf den Befehl Auswählen
klicken, sehen Sie, dass nun auch die anderen Befehle
aktiv werden.
Sie können dann separat nur Beschriftungen auswählen.
Bereits ausgewählt ist die unterste Position Auswahl ak-
tivieren. Sie erkennen dies an der orangenen Umran-
dung. Dies ist die Standardeinstellung.
Es bedeutet, dass Sie in der Pivottabelle ein schwarzer
Pfeil zum Markieren benutzt werden kann. Sie zeigen beispielsweise, wie in der Abbildung
unten, auf eine Spaltenüberschrift. Wenn der Pfeil erscheint, können Sie mit Linksklick
die Spalte markieren. Nur die Spalte in der Pivottabelle wird damit markiert, die Spalte
des Excelarbeitsblattes bleibt davon unberührt.
Sie können so einzelne Elemente der Pivottabelle markieren.
20
PivotTable-Tools und die Schaltzentrale PivotTable-Feldliste
3.5 Anzeige der PivotTable-Feldliste
Je nachdem wie viele Pivotfelder Sie in ihrer Pivottabelle haben oder mit wie vielen Felder
Sie im Layoutbereich arbeiten wollen, können Sie sich die PivotTable-Feldliste anders
anzeigen lassen. Beispielsweise haben Sie sehr viele Felder, die Sie aber auf einen Blick
sehen wollen. Um sich die PivotTable-Feldliste anders anzeigen zu lassen, klicken Sie
rechts oben auf die Auswahlliste.
Die erste Einstellung ist die Standardeinstellung. In der Abbildung wurde bereits die zwei-
te Einstellung gewählt. Die Feldliste wird links angezeigt und der Layout-Abschnitt rechts.
Zusätzlich können Sie, wenn Sie mit der Maus auf die Ränder der PivotTable-Feldliste
zeigen, diese noch größer aufziehen.
Sie können die PivotTable-Feldliste ein- bzw. ausblenden. In den PivotTable-Tools auf
der Registerkarte Optionen, in der Gruppe Anzeigen, gibt es den Befehl Feldliste zum
Ein- und Ausblenden.
Gestalten der Pivottabelle
21
©
4. Gestalten der Pivottabelle
Um eine Pivottabelle lesbarer zu machen, gibt es eine Reihe von Automatismen, die spä-
ter vorgestellt werden. Zunächst sollten die Werte der Pivottabelle mit einem Zahlenfor-
mat versehen werden.
4.1 Zahlenformat anwenden
Sie können den Bereich markieren, dann das Währungsformat auswählen. Dieses Zahlen-
format liegt dann auf der Zelle, d. h. wenn diese zu schmal ist, müssen Sie die Spalte
anpassen. Wenn die Pivottabelle außerdem sehr groß ist, ist es schwierig, sämtliche Wer-
te zu erfassen. Deshalb ist es ratsamer wie folgt vorzugehen:
Klicken Sie mit der rechten Maustaste auf einen beliebigen Wert in der Pivottabelle.
- Wählen Sie aus dem Kontextmenü den Eintrag Wertfeldeinstellungen.
22
Gestalten der Pivottabelle
- Das Dialogfeld Wertfeldeinstellungen wird angezeigt. Sie können hier sehen, wie
das Quellfeld ursprünglich heißt . Aus dem ursprünglichen Namen und der Funkti-
on, hier die Summenfunktion, wird der benutzerdefinierte Name zusammengesetzt.
Sie können sowohl hier als auch direkt in der Pivottabelle diesen Namen ändern.
Das Dialogfeld besteht aus zwei Registerkarten . Auf der Registerkarte Werte zu-
sammenfassen nach können Sie mit anderen Funktionen zusammenfassen. Die
Standardeinstellung ist Summe .
- Um die Werte zu formatieren, klicken Sie auf die Schaltfläche Zahlenformat . Es
wird das Dialogfeld Zellen formatieren geöffnet. Wählen Sie hier die Kategorie Wäh-
rung und die Anzahl der Dezimalstellen aus.
- Bestätigen Sie die Auswahl jeweils mit der Schaltfläche OK. Anschließend sind alle
Werte, die zu diesem Pivotfeld gehören, mit dem ausgewählten Zahlformat gestaltet.
Gestalten der Pivottabelle
23
©
Das Dialogfeld Wertefeldeinstellungen können Sie auch auf einem anderen Weg errei-
chen. In den PivotTable-Tools auf der Registerkarte Optionen, in der Gruppe Aktives
Feld finden Sie ebenfalls den Befehl Feldeinstellungen. Darüber befindet sich das Feld
Benutzerdefinierter Name, das Sie auch hier abändern können.
4.2 Leere Wertezellen mit Nullen auffüllen
Wenn Sie die Pivottabelle aus der Abbildung oben mit dem Währungsformat versehen
haben, enthalten einige Zellen keinen Wert. Um deutlich zu machen, dass in diesen Zel-
len kein Eurobetrag vorhanden ist, können Sie die Zellen mit Nullen auffüllen.
- Dazu klicken Sie mit der rechten Maustaste in den Wertebereich der Pivottabelle.
- Im Kontextmenü wählen Sie den Eintrag PivotTable-Optionen, um in das entspre-
chende Dialogfeld zu gelangen.
Sie können aber auch über die PivotTable-Tools auf der
Registerkarte Optionen, in der Gruppe PivotTable auf
den Befehl Optionen klicken.
- Im Dialogfeld PivotTable-Optionen auf der Registerkarte Layout&Format finden
Sie im Bereich Format ein Ankreuzkästchen.
- Klicken Sie das Ankreuzkästchen an und geben im zugehörigen Feld eine Null ein. Kli-
cken Sie abschließend auf die Schaltfläche OK.
24
Gestalten der Pivottabelle
Nachdem sich das Dialogfeld geschlossen hat, sehen Sie in der Pivottabelle statt der
leeren Zellen jeweils 0,00 €.
4.3 Autoformate
Wollen Sie ihre Pivottabellen noch weiter ausschmücken, können Sie dieses mit Hilfe der
Autoformate machen. Insbesondere bei recht langen Tabellen, unterstützt eine Hervor-
hebung jeder zweiten Zeile die Lesbarkeit.
- Klicken Sie in den PivotTable-Tools auf die Registerkarte Entwurf. Dort gibt es zwei
Gruppen, die Optionen für PivotTable-Formate und die PivotTable-Formate
.
- Klicken Sie zunächst in der Gruppe PivotTable-Formate auf den Doppellistenpfeil,
damit Sie alle vorhandenen Formate sehen können.
- Die Autoformate haben eine Vorschau. Wenn Sie auf ein Format zeigen, passt sich die
Pivottabelle an. Sie können so schon vorher sehen, wie ihre Tabelle aussehen wird.
Erst wenn Sie das richtige Layout gefunden haben, klicken Sie es an.
Gestalten der Pivottabelle
25
©
- Es gibt in den Autoformaten drei Farbschemata: Hell ist oben, dann Mittel und zum
Schluss Dunkel. In der Abbildung sehen Sie, dass das Kontrollkästchen Verbundene
Zellen ausgewählt ist. Das bewirkt, dass jede zweite Zeile, in diesem Fall in der Farbe
Blau, farblich hervorgehoben wird. Aber nicht alle Formate können diese alternierende
Farbdarstelllung abbilden. Das gilt auch für die Kontrollkästchen Zeilenüberschriften
und Spaltenüberschriften. Diese sind standardmäßig gesetzt. Werden die Häkchen
entfernt, werden die Zeilen- bzw. Spaltenüberschriften nicht mehr besonders
hervorgehoben.
- Sie können auch selbst neue Formatvorlagen erstellen, indem Sie ganz unten auf den
Eintrag NeuePivotTable-Formatvorlage.
- Klicken Sie auf den Eintrag Löschen, wird die Formatierung mit der Formatvorlage
gelöscht, nicht jedoch andere Formatierungen, die Sie eventuell vorher vorgenommen
haben.
4.4 Layout-Stile
Auf der Registerkarte Entwurf gibt es noch die Gruppe Layout mit vier Befehlen, um die
Anordnung in der Pivottabelle zu gestalten.
Standardmäßig wird eine neu erzeugte Pivottabelle im Berichtsformat In Kurzformat
anzeigen abgebildet. D. h. in diesem Fall die Pivotfelder Konto und Kostenstelle wer-
den beide in der Spalte A dargestellt.
26
Gestalten der Pivottabelle
Das Berichtslayout In Gliederungsformat anzeigen bedeutet, dass die Pivotfelder je-
weils eine eigene Spalte haben. In der linken Abbildung sehen Sie die Einstellung Ele-
mentnamen nicht wiederholen, in der rechten Abbildung die Einstellung Alle Ele-
mentnamen. Diese Einstellung brauchen Sie, wenn die Tabelle weiter verarbeitet wer-
den soll.
Wenn Sie die folgende Abbildung analysieren, erkennen Sie, dass drei Pivotfelder in Spal-
te A zusammengefasst sind. Es sind die Felder Belegart, Konto und Kostenstelle. Der
Zellzeiger steht auf einer Kostenstelle.
Gestalten der Pivottabelle
27
©
In den PivotTable-Tools auf der Registerkarte Optionen, in der Gruppe Aktives Feld
wird dieses ebenfalls angezeigt.
Wenn Sie auf das Minuszeichen neben dem aktiven Feld klicken, wird die Auflistung der
Kostenstellen reduziert. Das Layout sieht dann wie folgt aus:
Die Kostenstellen sind nicht mehr
sichtbar, sondern nur noch Konten.
Die Konten haben jeweils ein Pluszei-
chen vor der Kontonummer, d. h. Sie
können bei einzelnen Konten die Kos-
tenstellen wieder aufklappen.
Klicken Sie auf das Pluszeichen im
Menüband, werden wieder alle Kos-
tenstellen eingeblendet.
In der Gruppe Aktives Feld sehen
Sie, dass automatisch die nächst hö-
here Ebene aktiv geworden ist.
Klicken Sie erneut auf das Minuszei-
chen, wird auf die nächst höhere
Ebene, in diesem Fall auf Belegart,
reduziert.
Analog zum Reduzieren können Sie wieder Ebene für Ebene komplett über das Pluszei-
chen einblenden. Haben Sie die Felder bis zur letzten Ebene (Kostenstelle) eingeblendet
und klicken erneut auf das Pluszeichen, wird das Dialogfeld Detail einblenden geöffnet.
In der Abbildung ist das Feld Belegdatum ausgewählt.
28
Gestalten der Pivottabelle
Wenn Sie auf die Schaltfläche OK klicken,
wird das Feld als weitere Ebene unter den
Kostenstellen eingefügt.
Sie können das Feld wieder entfernen, in dem
Sie über die PivotTable-Feldliste das Feld ent-
fernen.
In der Gruppe Layout gibt es die Befehle Teilergebnisse und Leere Zeilen. In der Ab-
bildung unten sehen Sie, wie sich die Einstellungen auswirken. Für jedes Konto wird die
Zwischensumme unter dem Konto angezeigt, Sie können sich die Zwischensumme aber
auch oben über dem jeweiligen Konto anzeigen lassen.
Der Befehl Leere Zeilen bewirkt, dass unter jede Zwischensumme eine leere Zeile ein-
gefügt wird.
Gestalten der Pivottabelle
29
©
Mit dem Befehl Gesamtergebnisse können Sie die Gesamtergebnisse für Zeilen und
Spalten einzeln oder komplett ausblenden. Die Voreinstellung ist, wie aus der Abbildung
ersichtlich, dass die Gesamtergebnisse sowohl für die Zeilen als auch für die Spalten an-
gezeigt werden.
30
Datenquellen für Pivottabellen
5. Datenquellen für Pivottabellen
Oben wurde bereits beschrieben, wie Sie aus einem Datenbereich eine Pivottabelle er-
zeugen. Sie können aber auch einen Datenbereich als Tabelle definieren oder aus einer
externen Quelle Daten für die Pivottabelle einlesen.
5.1 Exceltabelle als Datenquelle
Auf der Registerkarte Einfügen in der Gruppe Tabelle gibt es neben dem Befehl
PivotTable auch den Befehl Tabelle.
Wenn Sie in einem Datenbereich stehen und auf diesen Be-
fehl klicken, öffnet sich das folgende Dialogfenster:
Sobald Sie auf die Schaltfläche OK geklickt haben, ist aus dem Datenbereich eine Tabel-
le erstellt worden. In der Excelversion 2003 hieß diese Funktionalität Liste. In der Excel-
version 2010 ist diese Tabellenfunktionalität mit weitergehenden Möglichkeiten ausge-
stattet worden.
Wie Sie in der Abbildung unten erkennen, wird eine Registerkarte Tabellentools ein-
geblendet. Darunter erscheint eine Registerkarte Entwurf. Diese ähnelt der Registerkar-
te Entwurf bei den PivotTable-Tools. Die Gruppe Tabellenformatvorlagen finden
Sie auch bei den Pivottabellen wieder. In der Gruppe Optionen für Tabellenformat
ist bereits das Kontrollkästchen Verbundene Zellen angeklickt. Das bedeutet, dass jede
zweite Zeile andersfarbig hervorgehoben ist, um die Lesbarkeit zu verbessern. Darüber
befindet sich das Kontrollkästchen Ergebniszeile. Wenn Sie hier ein Häkchen setzen,
erscheint am Ende der Tabelle eine zusätzliche Zeile, die die gefilterten Daten nach ver-
schiedenen Funktionen zusammenfasst. Sie klicken auf die entsprechende Zelle, in die-
sem Fall auf H9089, und erhalten einen Listenpfeil. Aus dieser Tabelle können Sie wiede-
rum eine Pivottabelle erstellen .
Datenquellen für Pivottabellen
31
©
Der Vorteil dieser Tabellenfunktionalität besteht darin, dass die Tabelle erweitert werden
kann und dabei Gestaltungsmerkmale und Formeln fortgeführt werden. Wenn Sie durch
die Tabelle nach unten scrollen, werden die Tabellenüberschriften in die Spaltenköpfe
übernommen.
5.1.1 Filter in der Tabelle
Die Filter- und Sortiermöglichkeiten sind in der neuen Version stark erweitert worden. So
erkennt Excel automatisch, um welchen Datentyp es sich handelt und zeigt die entspre-
chenden Filter an.
Wenn Sie auf das Filtersymbol in der Überschriftenzeile klicken, klappt ein Auswahlmenü
herunter. Im oberen Bereich werden die Sortiermöglichkeiten aufgelistet. Bei diesem
Filter handelt es sich um einen Textfilter . Das hat Excel auf Grund der in der Spalte
vorhandenen Daten erkannt. Zum Textfilter gehören die verschiedenen Filtermöglichkei-
ten . Wenn Sie eine Filtermöglichkeit anklicken, gelangen Sie in ein Dialogfeld, das an
vorangegangene Excelversionen erinnert. Sie können aber auch über das Suchfeld
filtern. In der Abbildung sehen Sie, dass alle Haushaltsstellen, die mit 3 beginnen, in dem
darunterliegenden Bereich aufgelistet werden sollen. Wichtig ist, dass Sie das * mit ein-
geben, sonst werden die Haushaltstellen aufgelistet, die an irgendeiner Stelle eine 3 auf-
weisen.
Sobald Sie auf die Schaltfläche OK geklickt haben, wird die Tabelle gefiltert. Das Filter-
symbol hat sich ebenfalls geändert. Wenn Sie auf das Symbol für gefilterte Daten zeigen,
erscheint ein Quickinfo mit Informationen zum angewendeten Filter.
Um den Filter wieder zu entfernen, klicken Sie auf das Filtersymbol und dann, in diesem
Fall, auf den Eintrag Filter löschen aus Haushaltsstelle.
32
Datenquellen für Pivottabellen
Wenn Sie nicht einzelne Filter, sondern alle komplett löschen wollen, klicken Sie auf die
Registerkarte Daten. Dort auf den Befehl Löschen.
Klicken Sie auf den Filter eines Zahlenfeldes, erhalten Sie eine Auswahl, angepasst an
Werte.
Wie oben auch, können Sie anstatt mit dem Suchfeld oder dem Dialogfeld zu filtern, in
der darunter befindlichen Liste einzelne Positionen durch Anklicken auswählen.
Datenquellen für Pivottabellen
33
©
Der Datumsfilter enthält in seiner Auflistung zusätzlich eine Gliederung in Jahre, Monate
und Tage.
Diese Filter und Sortiermöglichkeiten können Sie aber nicht nur nutzen, wenn Sie zuvor
den Bereich als Tabelle definiert haben. Bereits auf der Registerkarte Start in der Gruppe
Bearbeiten finden sich Filter- und Sortiermöglichkeiten.
34
Datenquellen für Pivottabellen
5.1.2 Formeln in der Tabelle
Wenn Sie in der Tabelle Berechnungen durchführen, Sie wollen beispielsweise die Diffe-
renz aus dem Anschlag 2013 und dem Anschlag 2012 bilden, so fügen Sie zunächst eine
Spalte ein. Schon beim Einfügen der Spalte bemerken Sie, dass die Spalte automatisch
einen Namen bekommt. Diesen Namen können Sie natürlich ändern.
- Um die Differenz zu bilden, klicken Sie in die Zelle I2 und geben ein Gleichheitszeichen
ein.
- Wenn Sie nun auf die Zelle G2 klicken, wird anstelle des Zellbezuges (G2) ein struktu-
rierter Verweis eingefügt.
- Geben Sie nun das Minuszeichen ein und klicken auf die Zelle H2. Es wird wiederum
ein Verweis eingefügt.
- Abschließend drücken Sie die Return-Taste. Die komplette Spalte wird mit der Formel
ausgefüllt. Sie brauchen die Formel also nicht nach unten zu kopieren.
Wenn Sie also in Excel einen Bereich als Tabelle definiert haben, können Sie Berechnun-
gen innerhalb und außerhalb der Tabellen mit den Feldnamen (Überschriften der Tabelle)
durchführen.
Befinden Sie sich außerhalb des Tabellenbereichs und wollen die Formel =G2-H2 einge-
ben, erhalten Sie folgende Formel:
=Tabelle1[@[Anschlag 2013]]-Tabelle1[@[Anschlag 2012]]
Außerhalb der Tabelle wird der Tabellenname eingegeben. Es folgt eine öffnende eckige
Klammer, danach das @-Zeichen. Dieses Zeichen bedeutet, dass sich die Formel auf die
aktuelle Zeile bezieht. Als nächstes kommt wiederum ein eckiges Klammerpaar, das den
Feldnamen enthält. Die folgende schließende eckige Klammer gehört zur ersten eckigen
Klammer.
Wenn Sie beispielsweise in einem anderen Tabellenblatt die Verweise nutzen wollen,
können Sie wie folgt vorgehen:
- Wenn Sie beispielsweise eine Summe bilden wollen, tippen Sie die ersten paar Buch-
staben ein. Sofort erscheint eine Liste mit der Kennzeichnung für Funktion und al-
len Funktionen, auf die die bereits eingetippten Buchstaben zutreffen.
- Sie können nun die Funktion mit Doppelklick oder mit der Tab-Taste übernehmen. Der
Funktionsname wird einschließlich geöffneter runder Klammer übernommen.
Datenquellen für Pivottabellen
35
©
Geben Sie nun den Tabellennamen ein. Im Beispiel heißt die Tabelle Tabelle1. Sobald Sie die
ersten Buchstaben eingeben, erscheint eine Auflistung von Funktionen, die mit den Buchsta-
ben beginnen und der Tabelle mit der Kennzeichung für Tabelle.
- Übernehmen Sie die Tabelle mit der Tab-Taste oder mit einem Doppelklick. Anschlie-
ßend fügen Sie eine eckige Klammer ein. Nun werden Ihnen alle Tabellenüberschriften
aufgelistet, die in der Tabelle vorhanden sind.
- Wählen Sie das entsprechende Feld aus. Abschließend müssen Sie noch die schließen-
de eckige Klammer und die schließende runde Klammer eingeben.
5.1.3 Funktion Links und Funktion Sverweis
Die obige Tabelle weist die Überschrift Produktgruppe auf. Wenn Sie aus der Produkt-
gruppe den Produktplan herausziehen wollen, nehmen Sie die Funktion Links.
Die Funktion Links gibt aus einem Text, der in diesem Fall in Zelle C2 steht, die ersten
beiden Zeichen zurück. Das erste Argument der Funktion ist die Zeichenkette, das zweite
Argument ist die Anzahl Zeichen, die von der linken Seite des Textes ausgegeben werden
sollen. Da der Bereich zuvor als Tabelle definiert wurde, wird nicht der Zellbezug, son-
dern der Feldname für die Funktion benutzt.
Neben dem Produktplan soll die Bezeichnung des Produktplanes stehen. Dazu ist eine
weitere Spalte und die Verweisfunktion notwendig.
36
Datenquellen für Pivottabellen
- Erfassen Sie in einem separaten Tabellenblatt die
Produktplandaten.
- Markieren Sie den Bereich, der die Nummern und
die Zurodnung enthält. Sie sehen, dass in Spal-
te A die Nummern als Texte formatiert wurden.
- Tragen Sie in das Namensfeld den Namen Pro-
duktplan ein. Bestätigen Sie die Eingabe mit der
Returntaste.
- Fügen Sie eine weitere Spalte nach der Über-
schrift PPL ein, um daneben den Namen des Pro-
duktplanes ausgeben zu können.
- Die Sverweisfunktion braucht als erstes Argu-
ment das Suchkriterium. Dieses steht in Zelle
D2. Wenn Sie die Sverweisfunktion über den
Funktionsassistenten aufrufen oder selbst eintip-
pen, erscheint nicht der Zellbezug, sondern der
Feldname der betreffenden Spalte.
- Das nächste Argument ist der Bereich, in dem in
der ersten Spalte nach dem Suchbegriff gesucht
werden soll. Da vorher ein Name für diesen Be-
reich eingegeben wurde (Produktplan), kann man
diesen aus dem Dialogfeld Namen einfügen
auswählen. Das Dialogfeld können Sie mit der
Taste aufrufen.
- Das nächste Argument, die Zahl 2 gibt an, dass Sie die zweite Spalte aus dem Bereich
Produktgruppen ausgegeben haben wollen. Das letzte Argument FALSCH bedeutet,
dass eine genaue Übereinstimmung zwischen Suchspalte (der ersten) und Suchbegriff
vorhanden sein muss.
Produktplan
Datenquellen für Pivottabellen
37
©
5.2 Daten extern übernehmen
Wenn Sie neue Daten aus SAP bekommen, müssen Sie die Berechnungen, Formatierun-
gen usw. erneut wieder herstellen. Eine bessere Lösung wäre, einmal eine Auswertung zu
erzeugen, um diese nur noch zu aktualisieren. Folgende Lösung wäre anzustreben:
Die Tabelle mit den Rohdaten aus SAP wird in eine neue Auswertungsdatei übernommen.
Dazu gehen Sie wie folgt vor:
- Speichern Sie eine leere Excelarbeitsmappe ab, z. B. unter dem Namen Auswertung.
- Auf der Registerkarte Daten in der Gruppe Externe Daten abrufen klicken Sie auf
den Befehl Aus anderen Quellen. Hier wählen Sie den Eintrag Von Microsoft
Query.
- Es wird das Dialogfeld Datenquelle auswählen geöffnet. Klicken Sie auf den Eintrag
Excel Files. Schließen Sie das Dialogfeld mit der Schaltfläche OK.
Übernahme der Daten in Zieldatei Pivottabelle
Zieldatei mit über-nommenen Daten
Tabelle mit Rohdaten aus SAP exportiert
ÜÜüber
38
Datenquellen für Pivottabellen
- Als nächstes erscheint das Dialogfeld Arbeitsmappe auswählen. In einem Auswahl-
fenster , in dem Sie die Datei auswählen können. Im Verzeichnisbereich können
Sie das Verzeichnis auswählen, in dem ihre Datei gespeichert ist. Der Dateityp wurde
bereits im ersten Schritt ausgewählt, kann aber hier noch verändert werden. Sie
können auch ein anderes Laufwerk auswählen oder aus einem Netzwerk . Zu-
sätzlich wird Ihnen angezeigt, dass eine Verbindung hergestellt wird .
- Sobald Sie alle Eintragungen vorgenommen haben und das Dialogfenster mit der
Schaltfläche OK geschlossen haben, erscheint das Dialogfenster des Query Assisten-
ten. Im Queryfenster Spalten auswählen können Sie die komplette Tabelle über-
nehmen oder Sie klicken auf das Pluszeichen in der rechten Fensterhälfte. Dann wer-
den alle Spalten der Tabelle aufgelistet und Sie können einzelne Spalten mit der Pfeil-
schaltfläche in den rechten Bereich übernehmen.
Datenquellen für Pivottabellen
39
©
- Wenn Sie auf die Schaltfläche Weiter geklickt haben, kommen Sie in das Queryfens-
ter Daten filtern. Hier können Sie die Datensätze filtern.
- Klicken Sie auf die Schaltfläche Weiter. Sie gelangen in das Queryfenster Sortierrei-
henfolge.
40
Datenquellen für Pivottabellen
- Klicken Sie auch hier auf die Schaltfläche Weiter. Abschließend kommen Sie in das
Queryfenster Fertig stellen.
- Klicken Sie auf die Schaltfläche Fertig stellen. Als letztes Dialogfenster öffnet sich
Daten importieren. Sie können hier verschiedene Einstellungen vornehmen. In die-
sem Fall brauchen Sie die Einstellung Tabelle, da ja noch Formeln eingeben werden
sollen.
- Die Daten werden eingelesen und die eingelesenen Daten werden automatisch in eine
Tabelle umgewandelt.
- Sie geben die notwendigen Formeln und Formatierungen ein. Wenn die Quelldatei an-
dere Datensätze enthält, klicken Sie auf den Befehl Aktualisieren.
Pivottabellenfelder
41
©
6. Pivottabellenfelder
Wenn Pivotfelder in die Zeilenbeschriftung oder in die Spaltenbeschriftung eingefügt wer-
den, haben sie andere Einstellungen als würden sie in den Wertebereich eingefügt. Die
Einstellungen für die Felder, können Sie auf verschiedene Weise öffnen:
- Klicken Sie mit der rechten Maustaste in ein Feld hinein und wählen aus dem Kon-
textmenü den Eintrag Feldeinstellungen
- oder in den PivotTable-Tools auf der Registerkarte Optionen, in der Gruppe Akti-
ves Feld finden Sie ebenfalls den Befehl Feldeinstellungen.
- Oder in der PivotTable-Feldliste können Sie über das schwarze Dreieck bei der Feld-
bezeichnung die Feldeinstellungen aufrufen.
Das Wertefeld hat andere Einstellungsmöglichkeiten als die Felder, die sich in den Zeilen-
bzw. Spaltenbeschriftungen befinden.
42
Pivottabellenfelder
6.1 Feldeinstellungen für Werte
Unter Punkt 4.1 wurde bereits erläutert, wie das Dialogfeld Wertfeldeinstellungen auf-
gebaut ist. Standardmäßig wird eine Summe gezogen, wenn es sich um ein Zahlenfeld
handelt. Ist in der Spalte, die die Werte enthält, nur ein leeres Feld vorhanden, wird au-
tomatisch die Funktion Anzahl eingetragen.
Wenn Sie, wie im Beispiel unten, einmal die Summe der auf den Konten gebuchten Be-
träge und einmal die Anzahl der gebuchten Positionen ermitteln wollen, müssen Sie das
Feld Betrag in Hauswährung zweimal in den Bereich Werte ziehen.
Klicken Sie nun auf das zweite Feld, um das Dialogfeld Wertfeldeinstellungen aufzuru-
fen. Hier wählen Sie die Funktion Anzahl. Sie können auf ein Wertefeld auch ein Drill-
down machen. D. h. wenn Sie auf einen Wert einen Doppelklick machen, wird ein neues
Tabellenblatt eingefügt. In dem Tabellenblatt werden alle Einzeldaten aufgelistet, aus
denen sich dieser Wert zusammensetzt.
Wenn Sie z. B. wissen wollen, wie viel Prozent der Betrag von 52.086,12 € am Gesamt-
ergebnis ausmacht, gehen Sie wie folgt vor:
- Ziehen Sie das Feld Betrag in Hauswährung nochmals in den Wertebereich. Klicken Sie
auf das Feld und wählen den Eintrag Wertfeldeinstellungen.
- Im Dialogfeld Wertfeldeinstellungen wählen Sie die Registerkarte Werte zusam-
menfassen nach den Eintrag Summe.
- Dann wählen Sie auf der Registerkarte Werte anzeigen als im Listenfeld den Eintrag
% des Gesamtergebnisses.
Pivottabellenfelder
43
©
- Wenn Sie die Eingaben mit der Schaltfläche OK bestätigt haben, sieht die Pivottabelle
wie folgt aus:
- Sie können die Tabelle auch so darstellen, dass in der Zeilenbeschriftung das Konto
steht und in der Spaltenbeschriftung die Kostenstelle. Der Gesamtbetrag beispielswei-
se des Kontos 67100000 ist in mehrere Kostenstellen aufgeteilt. In den Zellen B6 bis
H6 sind die einzelnen Beträge ausgewiesen. In den Zellen I6 bis O6 sind die Prozent-
sätze ausgewiesen. D. h. der Betrag von 34.682,76 € ist 4,4 % von der Gesamtsum-
me über 793.159,92 €. Die Prozentdarstellung beginnt ab Spalte I.
Wesentlich übersichtlicher ist es, wenn Sie die Kostenstelle in diesem Fall ebenfalls in die
Zeilenbeschriftung verschieben. Die Tabelle sieht dann wie folgt aus:
44
Pivottabellenfelder
Die Registerkarte Werte anzeigen als stellt noch mehr Eintragungen zur Verfügung. Die
Eintragung Werte anzeigen als % des Spaltenergebnisses bedeutet, dass die Pro-
zentwerte auf Basis der Gesamtsumme pro Spalte gerechnet werden. Für die Zahlen in
der Abbildung unten heißt das, der Gesamtbetrag von 389.288,78 € bei Kostenstelle
10000000 wurde bei verschiedenen Konten gebucht. Davon entfielen 8,91 % auf das
Konto 67100000.
Die Eintragung Werte anzeigen als % des Zeilenergebnisses bedeutet, dass die
Prozentwerte auf Basis der Gesamtsumme pro Zeile gerechnet werden. D. h. für das
Konto 67100000 wurde ein Gesamtbetrag von 52.086,12 € gebucht. Dieser wird prozen-
tual auf die einzelnen Kostenstellen verteilt.
Die Berechnungseinstellungen für das Wertefeld finden Sie auch in den PivotTable-
Tools, Registerkarte Optionen, dort die Schaltfläche Berechnungen.
Pivottabellenfelder
45
©
6.2 Feldeinstellungen für Zeilen- bzw. Spaltenbeschriftungen
Aus den Haushaltsdaten wurde folgende Pivottabelle erzeugt: Die erste Spalte unterteilt
nach Einnahmen und Ausgaben. In der zweiten Spalten folgen die jeweiligen Produktplä-
ne.
Als Teilergebnis wird standardmäßig die Summe eingeblendet. Sie können jedoch auch
andere Funktionen für das Zeilenfeld sichtbar machen. Gehen Sie wie folgt vor:
46
Pivottabellenfelder
Setzen Sie den Cursor in das Feld Einnahmen/Ausgaben oder klicken ein Element des
Feldes an.
- Rufen Sie die Feldeinstellungen über das Menüband oder die rechte Maustaste auf.
- Im Dialogfeld Feldeinstellungen, auf der Registerkarte Teilergebnisse&Filter ist
normalerweise die Voreinstellung Automatisch ausgewählt.
- Als Teilergebnis wird üblicherweise die Summe ausgeworfen. Sie können bei den Teil-
ergebnissen aber auch nach anderen Funktionen zusammenfassen. In der Abbildung
wurde nach den Funktionen Summe, Anzahl und Mittelwert zusammengefasst. Das Er-
gebnis sieht wie folgt aus:
Pivottabellenfelder
47
©
6.3 Felder sortieren
In der Abbildung oben sehen Sie, dass das Feld Produktplan nach Alphabet aufsteigend
sortiert ist. Sie können die Sortierreihenfolge auch umdrehen.
- Achten Sie zunächst darauf, dass der Zellzeiger in der richtigen Zelle steht.
- In den PivotTable-Tools, Registerkarte Optionen finden Sie in der Gruppe Sortie-
ren und Filtern die Symbole für das aufsteigende Sortieren sowie das absteigen-
de Sortieren . Sie können Texte als auch Werte mit diesen Symbolen sortieren.
- Wenn Sie auf die Schaltfläche klicken, öffnet sich das dazugehörige Dialogfeld.
Die Voreinstellung bei den Sortieroptionen ist Manuell. D. h. Sie können die Elemente
des Feldes mit der Maus verschieben.
- In der Abbildung unten ist das Listenfeld Absteigend ausgewählt. Im Listenfeld selber
wurde das Pivotfeld Ist 2010 ausgewählt.
- Es wird nun nicht nach dem Alphabet, sondern nach der höchsten Zahl im Pivotfeld
Ist2010 sortiert. Das Ergebnis sieht folgendermaßen aus:
Sie sehen, dass der Produktplan Zentrale Finanzen jeweils die höchsten Beträge bei den
Einnahmen und Ausgaben hat. Jedoch an zweiter Position steht bei Einnahmen Bildung
und bei Ausgaben Jugend Soziales.
Die Sortiermöglichkeiten finden Sie alternativ auch bei den Filtern.
48
Pivottabellenfelder
6.4 Felder filtern
Um alle, in der Pivottabelle zur Verfügung stehenden Filter zu sehen, wählen Sie zu-
nächst das Berichtsformat Tabelle. In der Abbildung unten sehen Sie, dass die Felder
Einnahme/Ausgaben und Produktplan mit einem Filtersymbol versehen sind. Das
Feld Produktplan wurde bereits gefiltert, deshalb sieht das Symbol anders aus.
Ist ein Feld gefiltert, können sämtliche Filter wieder gelöscht werden . Im Beispiel wer-
den nicht mehr alle Daten angezeigt. Das können Sie am Häkchen und an dem ausgefüll-
ten Kästchen (Alle anzeigen) sehen. Bei der letzten Position wurde der Haken ent-
nommen.
Sobald das Feld gefiltert wird, erscheint auch in der PivotTable-Feldliste ein Symbol .
Die Filter können Sie nun wie in den anderen Exceltabellen benutzen. Bei den Pivottabel-
len kommt jedoch hinzu, dass Sie bei den ausklappbaren Filtern unterscheiden können,
zwischen Beschriftungsfiltern und Wertefiltern.
Im Beispiel oben sind die Beschriftungsfilter die Produktpläne, während sich die Wertefil-
ter auf die zugehörigen Zahlen im Feld IST2010 beziehen.
Pivottabellenfelder
49
©
Sie können sich beispielsweise die Top 10 Werte der Spalte IST2010 wie folgt ausgeben
lassen:
- Klicken Sie auf das Filtersymbol bei Produktplan und wählen den Eintrag Wertefilter.
Es klappt ein weiteres Filtermenü auf.
- Wählen Sie den untersten Eintrag Top 10..
- Wählen Sie im Dialogfeld Top-10-Filter im Listenfeld Anzeigen aus, ob Sie die
obersten oder die untersten Werte angezeigt bekommen wollen.
- Im Drehfeld rechts daneben können Sie die Anzahl der Elemente einstellen, die Sie
sich anzeigen lassen wollen.
50
Pivottabellenfelder
- Das Ergebnis sehen Sie in der folgenden Abbildung. Sowohl die Ausgaben als auch die
Einnahmen wurden nach ihren höchsten zehn Werten gefiltert. Anschließend wurde die
Spalte IST2010 absteigend sortiert.
Pivottabellenfelder
51
©
- Um die Filter zu entfernen, klicken Sie wiederum auf das Filtersymbol. Am Symbol
erkennen Sie, dass das Feld Produktplan gefiltert und sortiert wurde.
- Die Sortierreihenfolge erkennen Sie daran, welches Sortiersymbol gelb markiert ist .
- Wenn Sie auf das Symbol Filter löschen klicken, werden alle Filter dieses Feldes
gelöscht. Wenn Sie nur den TOP 10 Filter löschen wollen, klicken Sie auf den Eintrag
Wertefilter.
- Die Wertefilter klappen aus und Sie können den Eintrag Filter löschen auswählen.
Damit wird nur der TOP 10 Filter gelöscht.
- Um mehrere Filter auf ein Feld anwenden zu können, müssen Sie im Dialogfeld
PivotTable-Optionen, auf der Registerkarte Summen&Filter ein Häkchen bei Meh-
rere Filter pro Feld zulassen setzen.
52
Pivottabellenfelder
Neben den Filtermöglichkeiten in der Pivottabelle gibt es den sogenannten Berichtsfilter
im Layoutbereich der PivotTable-Feldliste.
In früheren Excelversionen hieß der Berichtsfilter Seitenfeld. Wenn Sie den Berichtsfil-
ter aufklappen, haben Sie die Möglichkeit weiter zu filtern.
Pivottabellenfelder
53
©
Der Berichtsfilter Land/Kommune enthält nur zwei Positionen. Sie können anhand des
Filtersymbols erkennen, dass die Option Land ausgewählt wurde.
Der Berichtsfilter Einnahmen/Ausgaben filtert die Ausgaben heraus.
Der Berichtsfilter Titel/Konto enthält mehrere Einträge. Hier ist es möglich, noch weiter
zu filtern.
Klicken Sie auf das Kästchen Mehrere Elemente auswählen. Es können nun weitere
Filterungen durchgeführt werden. Im Suchfeld wurde 4* eingeben. Das bedeutet, alle
Titel, die mit der 4 beginnen werden herausgefiltert.
54
Pivottabellenfelder
6.5 Filtern mit Datenschnitten
Ab der Excelversion 2010 gibt es sogenannte Datenschnitte. Um einen Datenschnitt ein-
zufügen, gehen Sie wie folgt vor:
- Klicken Sie in den PivotTable-Tools auf der Register-
karte Optionen, in der Gruppe Sortieren und Filtern
auf die Schaltfläche Datenschnitt einfügen. Das Dia-
logfeld Datenschnitt auswählen öffnet sich.
- Wählen aus den Pivotfeldern diejenige aus, nach denen
Sie weiter filtern wollen.
- Wählen Sie die Felder Land/Kommune, Titel/Konto,
Einnahmen/Ausgaben und Produktplan aus.
- Die Datenschnitte werden erzeugt. Sie liegen außerhalb
der Pivottabelle hintereinander angeordnet.
- Sie können die Reihenfolge der Anordnung ändern, in
dem Sie den jeweiligen Datenschnitt anklicken und ver-
schieben.
Pivottabellenfelder
55
©
In der folgenden Abbildung wurden die Datenschnitte oberhalb der Pivottabelle angeord-
net. Im Datenschnitt Produktplan können Sie am Symbol erkennen, dass bereits
gefiltert wurde. Wenn Sie auf einen Eintrag klicken, wird der entsprechende Produktplan
blau hervorgehoben . In diesem Fall ist es Bau. Im Datenschnitt Land/Kommune
wurde Land ausgewählt . Bei Einnahmen/Ausgaben wurden Einnahmen ausgewählt .
Das Resultat der Filterung sehen Sie in der Pivottabelle .
Sie können auch mehrere Einträge in den Datenschnitten auswählen, in dem Sie die
-Taste gedrückt halten und die Einträge anklicken. Die Filterungen können Sie wieder
löschen, indem Sie auf das entsprechende Symbol klicken. Um die Datenschnitte ganz zu
entfernen, klicken Sie die Datenschnitte an und drücken die Löschtaste.
Sobald Sie einen Datenschnitt angeklickt haben, wird die Registerkarte Datenschnitt-
tools eingeblendet. Auf dieser Registerkarte können Sie die Datenschnitte anordnen und
gestalten.
56
Pivottabellenfelder
6.6 Felder gruppieren
Sie können Datums- und Zahlenfelder automatisch gruppieren. Um beispielsweise nach
einem Buchungsdatum zu gruppieren, gehen Sie wie folgt vor:
- Klicken Sie in ein Datumsfeld und wählen dann in den PivotTable-Tools, Registerkar-
te Optionen in der Gruppe Gruppieren die Schaltfläche Gruppenauswahl oder
Gruppenfeld.
- Sobald Sie auf die Schaltfläche geklickt haben, öffnet sich das Dialogfeld Gruppie-
rung.
Die Felder Starten und Beenden zeigen Ihnen das älteste
und das jüngste Buchungsdatum an. Sie können auch sel-
ber den Start- bzw. den Endzeitpunkt festlegen. Die Daten,
die außerhalb des von Ihnen festgelegten Zeitraumes lie-
gen, werden dann zu Zeiträumen zusammengefasst.
Im Feld Nach bestimmen Sie, wie die Daten zusammenge-
fasst werden sollen. Wenn sich der Buchungszeitraum über
mehrere Jahre erstreckt, sollten Sie auf jeden Fall Jahre
anklicken. Es werden sonst die Daten nach Monaten und
Quartalen über die verschiedenen Jahre hinweg zusam-
mengefasst.
Pivottabellenfelder
57
©
- Wenn Sie ihre Auswahl getroffen haben, klicken Sie auf die Schaltfläche OK. Die Zu-
sammenfassung der Daten sieht dann wie folgt aus:
Sie können nun die Ansicht reduzieren, z. B.
in dem Sie den Zellzeiger in ein Feld mit
Jahreszahl stellen und auf die Reduzieren-
schaltfläche klicken.
Es werden dann nur die Ergebnisse von
2009 und 2010 angezeigt. Quartale und Mo-
nate werden ausgeblendet.
Die Schaltfläche blendet
wieder Quartale und Monate ein.
Wenn Sie den Zellzeiger in ein Quartal stel-
len und reduzieren, werden die Monate aus-
geblendet.
Sie können auch die gebuchten Beträge gruppieren und zusammenfassen. In der folgen-
den Abbildung sehen Sie, dass die Zeilenbeschriftung aus den Buchungsbeträgen be-
steht. Im Wertefeld wird gezählt, wie oft ein bestimmter Betrag gebucht wurde.
58
Pivottabellenfelder
Die Buchungsbeträge können Sie wie folgt zu Gruppen zusammenfassen:
- Klicken Sie in ein Feld, das einen Buchungsbetrag enthält, und wählen dann in den
PivotTable-Tools, Registerkarte Optionen in der Gruppe Gruppieren die Schaltflä-
che Gruppenauswahl.
- Sobald Sie auf die Schaltfläche geklickt haben, öffnet sich das Dialogfeld Gruppie-
rung. Da es sich hier um ein Zahlenfeld und nicht um ein Datumsfeld handelt, sieht
das Dialogfeld etwas anders aus.
Die Felder Starten und Beenden zeigen den ge-
ringsten und den höchsten Betrag. Sie können hier
andere Werte eingeben, dann verschwinden die Häk-
chen an den Feldern. Sobald Sie die Häkchen wieder
setzen, werden die Beträge erneut eingetragen.
Im Feld Nach tragen Sie die Schrittweite ein, mit der
zusammengefasst werden soll. Sie können aber auch
den von Excel vorgeschlagenen Wert stehen lassen.
Sie sehen in den Abbildungen die eingestellten Werte und das Resultat.
Drucken einer Pivottabelle
59
©
7. Drucken einer Pivottabelle
Standardmäßig druckt Excel das gesamte Arbeitsblatt aus. Leider steht Ihnen nicht, wie
bei der "normalen" Tabelle, die Möglichkeit zur Verfügung, nur die Tabelle auszudrucken.
Sie müssen die Pivottabelle zum Druckbereich erklären, um nicht andere Daten, die sich
auf dem Arbeitsblatt befinden, mit zu drucken.
Wenn Sie mehrseitige Pivottabellen haben, sollten die Überschriften auf jedem Blatt aus-
gedruckt werden. Dazu gehen Sie wie folgt vor:
- In den PivotTable-Tools, Registerkarte Optionen, Gruppe PivotTable klicken Sie
auf die Schaltfläche Optionen. Das Dialogfeld PivotTable-Optionen öffnet sich.
- Klicken Sie auf die Registerkarte Druckt und anschließend auf das Kästchen Druckti-
tel festlegen.
Auf jeder Seite werden jetzt die Überschriften wiederholt.
60
Berechnetes Feld
8. Berechnetes Feld
Außer den bereits oben erwähnten Möglichkeiten in Pivottabellen zu rechnen, können Sie
zusätzliche Felder hinzufügen, die Berechnungen enthalten. Sie möchten in der unten
abgebildeten Tabelle die prozentuale Veränderung des Anschlages 2011 nach 2012 er-
mitteln. Dazu benötigen Sie ein weiteres Pivotfeld, dass aus den Pivotfeldern Anschlag
2011 und Anschlag 2012 berechnet wird.
Um das berechnete Feld zu erzeugen, gehen Sie wie folgt vor:
- In den PivotTable-Tools, auf der Registerkarte Optionen, in der Gruppe Berech-
nungen klicken Sie auf das Listenfeld Felder, Elemente und Gruppen.
Berechnetes Feld
61
©
- Wählen Sie dort den Eintrag Berechnetes Feld… Es öffnet sich das Dialogfeld Be-
rechnetes Feld einfügen.
- Im Feld Namen geben Sie eine sprechende Bezeichnung ein. Im Feld Formel müssen
Sie nun die Berechnungsformel für die Änderung eingeben. Die vollständige Formel
lautet: .
- Zum Eingeben der Formel wählen Sie aus dem Bereich Felder das Feld Anschlag
2012 aus und klicken auf die Schaltfläche Feld einfügen.
- Als nächstes tippen Sie das Geteiltzeichen ein. Anschließend wählen Sie das Feld An-
schlag 2011 aus und klicken wieder auf die Schaltfläche Feld einfügen.
- Um die Formel zu vervollständigen, geben Sie noch den Rest ein. Sie können nun auf
die Schaltfläche OK klicken. Das Dialogfeld schließt sich. Wenn Sie weitere berechnete
Felder einfügen wollen, klicken Sie auf die Schaltfläche Hinzufügen.
- In der Abbildung sehen Sie, dass das Feld in die Feldliste und in den Wertebereich ein-
gefügt wurde.
- Allerdings erscheinen die Werte nicht automatisch als Prozentwerte. Sie müssen die
Zahlen anschließend noch mit dem entsprechenden Zahlenformat versehen.
62
Berechnetes Feld
Das berechnete Feld gehört jetzt zu den Pivottabellenfeldern. Wenn Sie es löschen wol-
len, müssen Sie wieder in das Dialogfeld Berechnetes Feld einfügen gehen. Dort wäh-
len Sie im Listenfeld Name das entsprechende Feld aus und klicken anschließend auf die
Schaltfläche Löschen.
Lernmaterial
©
Lernmaterial
Lernmaterial – Schulungsunterlagen, Übungsdateien, Lernprogramme und Tipps & Tricks –
finden Sie unter der Adresse
http://www.afz.bremen.de/lernen
Wählen Sie das ge-
wünschte Thema
über die Menü-
struktur oder aus
der Liste, die Sie
über die Infobox
Gesamtliste im
Hauptmenüpunkt
Lernmaterial er-
reichen können.
Sie können Themen nachschlagen, Ihre Kenntnisse aktualisieren (z. B. bei neuer Programm-
version) oder sich zusätzliche Themen erarbeiten.
Sie können Lernmaterial als PDF-Dokumente (Symbol ) am Bildschirm lesen, auf Ihrem
Computer speichern oder ausdrucken. Zum Teil stellen wir zusätzlich Übungsdateien in
"gepackter" Form (Zip-Archiv, Symbol ) zur Verfügung.
Zu einzelnen Themen sind Lernprogramme vorhanden, erkennbar an dem Symbol .
Die einzelnen Lerneinheiten dauern 2 bis maximal 15 Minuten und können bei Bedarf am
Arbeitsplatz genutzt werden.
Tipps & Tricks
Oft sind es die kleinen Dinge, die die Arbeit am
PC erleichtern. Dazu haben wir Tipps und Tricks
zusammengestellt. Vielleicht finden Sie hier et-
was, um Ihre Arbeit effektiver zu gestalten.
Softwarehilfe (Mail-Hotline)
Wenden Sie sich mit Ihren Fragen, Problemstellungen oder Fehlermeldungen an:
Beschreiben Sie Ihre Frage bzw. die Fehlersituation und Ihre bisherige Vorgehensweise und
fügen Sie die Dateien im Original-Dateiformat als Anlage bei. Wir beantworten Ihre Fragen
so schnell wie möglich, in jedem Fall melden wir uns innerhalb weniger Tage bei Ihnen.