SAP Dateien mit Excel 2010 auswerten - Bremen€¦ · In Excel 2010 wird ohne Nachfrage erneut ein Cache erzeugt, wenn Sie die Quelldaten verändern und anschließend die Befehle

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


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


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


- 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:


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


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


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


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.


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.


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


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.


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.


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


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


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


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


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.


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


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.


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


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.


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 .


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


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.


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


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.


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


- 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


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


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


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


- 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:

[email protected]

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.

http://www.afz.bremen.de/lernen

mailto:[email protected]

Documents

SAP Dateien mit Excel 2010 auswerten - Bremen€¦ · In Excel 2010 wird ohne Nachfrage erneut ein Cache erzeugt, wenn Sie die Quelldaten verändern und anschließend die Befehle