MS Excel: Arbeiten mit Pivot-Tabellen - so geht's

Durch einfaches Umordnen, haben Sie die Möglichkeit, sich Daten aus einem völlig anderen Blickwinkel anzusehen. Auf diese Weise werden die vorhandenen Informationen aussagekräftiger und eignen sich zur Analyse sowie für spezielle Vergleiche.

Bevor wir Ihnen zeigen, wie Sie PivotTable auf umfangreiche Datenlisten anwenden und Daten nach unterschiedlichen Kriterien gliedern können, folgender Hinweis: Besonders einfach ist der Einsatz von Empfohlene PivotTables aus dem Menü Einfügen. Die Funktion schlägt Ihnen auf Basis des vorliegenden Datenbestands Auswertungsmöglichkeiten vor.

In der praktischen Anwendung sind empfohlene Gliederungen jedoch oft wenig detailliert und zum Teil nicht aussagekräftig. Aufgrund dieser Nachteile gehen die nachfolgenden Ausführungen nicht auf Empfohlene PivotTables ein, vielmehr wird erläutert, wie Sie Pivot-Tabellen individuell auf Ihre Daten und die von Ihnen gewünschten Aussagen zuschneiden.

Worauf muss ich bei der Datenbasis achten?

Daten, die Sie mithilfe von Pivots analysieren wollen, müssen nicht zwangsläufig in Excel vorliegen, Sie können auch Daten anderer Formate nach Excel importieren und diese anschließend auswerten (Option Externe Datenquelle verwenden im Dialog PivotTable erstellen). Bei Datenbeständen sollten Sie Folgendes beachten:

1. Die Datenliste sollte aus einer Überschriftenzeile und Datensätzen bestehen. Ordnen Sie die Datensätze in Zeilen an.
2. Stellen Sie sicher, dass alle Spaltenüberschriften eine eindeutige Bezeichnung enthalten. Fehlende Überschriften führen zu Problemen.
3. Vermeiden Sie Überschriften die über mehrere Zeilen gehen und verzichten Sie auf zusammengeführte Zellen, die man beispielsweise im Start-Menü über Verbinden und zentrieren zusammenlegt.
4. Zeilenumbrüche in den Überschriften der Ausgangstabelle werden ohne Leerzeichen dargestellt, Trennzeichen werden in Pivot-Tabellen übernommen.
5. Innerhalb der Datenbasis sind Formeln erlaubt. Sie können zum Beispiel Differenzen bilden und in Pivots nutzen.
6. Auch aus relationalen Daten, das heißt Daten, die Sie in separaten Tabellen gespeichert haben, lässt sich eine Pivot Tabelle erstellen.

Damit Sie die Arbeitsschritte dieses Workshops 1:1 nachvollziehen können, finden Sie unter diesem Absatz eine kurze Datenliste (Testdaten_Pivot_Workshop.xlsx) mit Projektdaten zum Download. In der Praxis funktioniert die Vorgehensweise selbstverständlich auch mit größeren Datenbeständen und anderen Datenstrukturen, zum Beispiel Kunden- oder Artikelinformationen.

Wie öffne ich eine Datenliste und lege eine PivotTable in einem neuen Arbeitsblatt an?

Öffnen Sie bei Bedarf die Excel-Arbeitsmappe mit den Testdaten. Die Datenliste setzt sich aus einer Überschriftenzeile (spätere Pivot-Felder) und einzelnen Datensätzen zusammen. Sie entsprechen im Beispiel Informationen zu unterschiedlichen Projekten, unter anderem basierend auf Arbeitspaketangaben als unterste Projektebene, den Verantwortlichen sowie Plan- und Ist-Zahlen von zeitlichen und finanziellen Ressourcen. Sobald die Tabelle vorliegt, setzen Sie die Eingabemarkierung in den Datenbestand.

Aktivieren Sie das Register Einfügen, und klicken Sie im Bereich Tabellen auf die Schaltfläche PivotTable. Im folgenden Dialog schlägt Excel im Feld Tabelle/Bereich die zu analysierende Datenliste vor (Abb. unten). Akzeptieren Sie den Vorschlag Neues Arbeitsblatt, und bestätigen Sie mit OK.

Excel legt ein neues Tabellenblatt mit einer leeren Grundstruktur für Pivot-Tabellen an. Im Arbeitsbereich PivotTable-Felder sind in der rechten Bildschirmhälfte alle vorhandenen Spaltenüberschriften Ihrer Datenliste aufgeführt, sodass Sie unmittelbar mit der Auswertung beginnen können.

Wie passe ich den Datenbestand individuell an meinen Bedarf an und wie bereite ich diese auf?

Die Beispieldaten sollen zunächst wie folgt ausgewertet werden: Die geplanten und tatsächlichen Projektkosten sowie deren Abweichungen sollen nach Projekten verdichtet werden. Setzen Sie zunächst einen Haken in das Feld Projekt.

Excel schlägt vor, die Daten in Zeilen anzuordnen. Sie erkennen das an der Anordnung im Tabellenarbeitsblatt, zudem erfolgt der Eintrag Projekt im Kasten Zeilen im unteren Teil des Arbeitsbereichs PivotTable-Felder.

Haken Sie anschließend die Felder GeplanteKosten, Istkosten gesamt und DifferenzKosten ab. Die zugehörigen Werte gruppiert Excel für die einzelnen Projekte und fasst sie als Summen in Spalten zusammen. Anhand der Überschriften und im Kasten Werte erkennen Sie, dass die Kosten addiert wurden.

Bereits nach wenigen Mausklicks liegt eine Übersicht mit verdichteten Zahlen vor, anhand derer Sie auf den ersten Blick erkennen können, in welchen Projekten Kosten überschritten wurden; sie sind als positive Werte ausgewiesen.

Wie ändere ich die Datenbasis und nutze die Interaktivität von PivotTable?

Die Auswertung können Sie jederzeit ändern oder erweitern. Um zusätzlich die Verantwortlichen für die Arbeitspakete anzuzeigen, haken Sie das Kontrollkästchen VerantwortlicherArbeitspaket ab. Die Darstellung wird erweitert.

Sie wollen wissen, welche der Verantwortlichen vermehrt Kostenüberschreitungen verursacht haben? Dazu müssen Sie nur die Projekte ausblenden, indem Sie den jeweiligen Haken im Arbeitsbereich PivotTable-Felder sowie im Kontrollkästchen Projekt entfernen.

Wie ermöglichen Feldeinstellungen die Auswertungen über Additionen hinaus?

Standardmäßig werden in Excel die Werte als Summen verdichtet. Wollen Sie in Erfahrung bringen, wie viele Positionen hinter einem Wert stehen, setzen Sie den Cursor in die Spalte, für die Sie die Information wünschen.

Wählen Sie PivotTabel-Tools/Analysieren, und klicken Sie im Bereich Aktives Feld auf Feldeinstellungen/Anzahl/OK. Wie Sie der Auswahl im Dialog Wertfeldeinstellung entnehmen können, ist es auch möglich, sich zum Beispiel Durchschnittswerte, Minimum, Maximum und weitere Werte anzeigen zu lassen.

Hinweis: Das Menü PivotTable-Tools mit den Registern Analysieren und Entwurf ist nur dann verfügbar, wenn die Pivot-Tabelle aktiv ist, das heißt: sich die Eingabemarkierung in der Pivot-Tabelle befindet.

Wie optimiere ich die Gestaltung von Pivot-Tabellen mit und ohne Register Entwurf?

Was die Gestaltung anbelangt, ist der Standard von PivotTable niedrig. Vermutlich haben Sie sich über die Spaltenüberschriften gewundert? Excel hat unter anderem Probleme, wenn diese in der Ursprungstabelle einen Zeilenumbruch oder ein Trennzeichen enthalten.

Das Trennzeichen können Sie einfach entfernen, den Textumbruch mit einem Klick auf die gleichnamige Schaltfläche unter Start/Ausrichtung/Textausrichtung nachbilden. Das heißt: Es sind alle Gestaltungselemente und Zahlenformate wie in herkömmlichen Tabellen verfügbar.

Tipp: Unter PivotTable-Tools/Entwurf finden Sie im Bereich PivotTable-Formate vordefinierte Layouts. Über die Schaltflächen Gebänderte Zeilen beziehungsweise Gebänderte Spalten in der Gruppe Optionen für PivotTable-Formate erhalten Sie blitzschnell eine Schraffur des Wertebereichs.

Wie schlüssele ich Werte für detaillierte Angaben zu einzelnen Positionen auf?

Sie wundern sich über einzelne Werte und wünschen dazu nähere Informationen? Eine Antwort auf Ihre Frage erhalten Sie per Doppelklick. Wenn Sie zum Beispiel wissen wollen, welche Einzelwerte hinter der Kostenüberschreitung des Verantwortlichen Grüner stehen, klicken Sie doppelt auf den entsprechenden Wert (hier 305). Excel legt eine eigene Tabelle mit Informationen zu diesem Wert an. Dazu werden dann alle Datensätze der Ausgangstabelle (Datenbasis) zum entsprechenden Wert herangezogen.

Wie nutze ich problemlos Datumsangaben mit und ohne Zeitachse?

Sie müssen sich in einer Arbeitsmappe nicht auf eine Pivot-Tabelle beschränken. In Excel können Sie gleichzeitig mehrere Pivots nach völlig unterschiedlichen Gesichtspunkten generieren. Beispiel: Die Testdatei enthält Angaben zu Start- und Endterminen der Arbeitspakete. Diese sollen genutzt werden, um zu analysieren, welche Kosten für die beendeten Arbeitspakete entstanden sind.

Legen Sie dazu eine weitere Pivot-Tabelle in einem neuen Arbeitsblatt an. Haken Sie die Felder Projekt, IstkostenPersonal, IstkostenMaterial, Istkosten gesamt sowie Enddatum Arbeitspaket ab. Sie erhalten eine nach Jahren differenzierte Aufgliederung der angefallenen Kosten.

Über die Plus-Schaltflächen vor den Jahreszahlen besteht die Möglichkeit, die Informationen zu erweitern. Excel weist umgehend für die Beispieldaten Quartalszahlen aus. Um Daten nach weiteren Zeiträumen zu differenzieren, ist zudem die Zeitachse zu empfehlen. Sie finden die Funktion unter PivotTable-Tools/Analysieren im Bereich Filtern/Zeitachse einfügen.

Im folgenden Dialog schlägt Excel Monate als Zeitebene vor. Sie lässt sich bei Bedarf jederzeit auf Jahre, Quartale oder Tage umstellen. Bestätigen Sie Monate. In der Zeitachse lassen sich anschließend einzelne Monat auswählen. Wenn Sie einmal nacheinander über die Monate Oktober, November, Dezember aus dem Jahr 2023 fahren, stellen Sie fest, dass die Werte in der Pivot-Tabelle entsprechend Ihrer Auswahl angepasst werden.

Bei Bedarf lässt sich der Zeitraum zudem jederzeit ausweiten, beispielsweise können Sie die Monate November und Dezember zusammenfassen. Achtung: Um später wieder eine vollständige Anzeige zu erhalten, achten Sie darauf, dass Sie die Auswahl über die Schaltfläche Filter löschen (kleines rotes x in der Achse) aufheben. Die Zeitachse selbst werden Sie über die [Entf]-Taste los.

Wie erstelle ich Diagramme mithilfe von PivotChart?

Pivots gibt es auch als Diagramm-Variante. Um zum Beispiel einen Vergleich der Soll- und Ist-Personalkosten der Projekte grafisch darzustellen, richten Sie eine weitere Pivot-Tabelle ein. Wenn das Tabellengrundgerüst steht, wählen Sie PivotTable-Tools/Analysieren/PivotChart. Für die vorliegende Datenbasis schlägt Excel Säulen vor. Gefällt Ihnen der Vorschlag nicht, wählen Sie einen anderen geeigneten Diagrammtyp aus. Bestätigen Sie mit OK.

Wie greife ich manuell ein, wenn Werte nicht automatisch aktualisiert werden?

Für den Fall, dass sich Daten in Ihrer Ausgangstabelle ändern, weil Sie zum Beispiel einen Zahlendreher in den Materialkosten korrigiert haben, werden weder die Pivot-Tabelle noch das PivotChart automatisch angepasst! Das müssen Sie manuell erledigen. Hierfür ist wichtig, dass sich die Eingabemarkierung in der Pivot-Tabelle befindet.

Wählen Sie PivotTable-Tools/Analysieren, und klicken Sie im Bereich Daten auf Aktualisieren. Für den Fall, dass sich mehrere Pivot-Tabellen in Ihrer Arbeitsmappe befinden, können Sie über Analysieren/Alle Aktualisieren alle Pivots gleichzeitig auf den neuesten Stand bringen. Achtung: Tabellen, die Zusatzinformationen zu einzelnen Werten enthalten, wie unter " Wie schlüssele ich Werte für detaillierte Angaben zu einzelnen Positionen auf? " beschrieben, werden nicht angepasst.

Kann ich PivotTable drucken und sichern?

Arbeitsmappen, die Pivot-Tabellen enthalten, sichern Sie wie jede herkömmliche Datei. Auch für den Ausdruck greifen Sie jederzeit auf die Ihnen bekannten Befehle zurück.

Was ist PowerPivot?

Kann ich PowerPivot als Excel-Turbo für Fortgeschrittene und Massendaten nutzen?

Eigentlich bringt PowerPivot im Vergleich zum „einfachen“ PivotTable nichts wirklich Neues. Mit dem entsprechenden Wissen kommen Sie in der Regel auch mit dem herkömmlichen Pivot zum Ziel. Sind die Datenmengen mit Millionen von Datensätzen extrem groß, entfaltet PowerPivot jedoch sein volles Potenzial.

Zudem erschließen zahlreiche Details weitere Vorteile. Damit lassen sich bei vielen Aufgaben Prozesse verkürzen und Zeit sparen. Darüber hinaus stellt Microsoft die OLAP-Technologien (Online Analytical Processing) zur Verfügung.

Auch was die Versorgung der Pivot-Tabelle mit Daten anbelangt, hat PowerPivot gegenüber seinem Standardkollegen klar die Nase vorn. PowerPivot versetzt Sie nämlich in die Lage, mehrere Quellen gleichzeitig abzufragen und zu verknüpfen. Berechnungen lassen sich über mehrere Tabellen hinweg realisieren.

Die DAX-Funktion verarbeitet riesige Datenmengen mit enormer Geschwindigkeit. Übrigens: DAX steht für Data Analysis Expressions (keine Programmier-, sondern eine Formelsprache) und hat nichts mit dem Deutschen Aktienindex zu tun. DAX ermöglicht hier vielmehr umfangreiche benutzerdefinierte Berechnungen.