Microsoft Excel Pivot-Tabellen – Das Praxisbuch E-Book

Helmut Schuster

• Herausgeber: O'Reilly
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Deutsch
• Veröffentlichungsjahr: 2020

Seitenzahl: 326

Beschreibung

Von ABC-Analyse bis Zeitachse: Mit PivotTables und Power Pivot alle Daten im Griff

• Für Excel 2010–2019 und Excel 365
• Mit vielen Praxisbeispielen aus dem Arbeitsalltag
• Inkl. kostenloser Übungsdateien zum Download

Für die Analyse größerer Datenmengen in Excel sind Pivot-Tabellen ein besonders leistungsfähiges Werkzeug. Das Buch zeigt, wie Excel-Anwender*innen mit Vorkenntnissen bei ihrer täglichen Arbeit von PivotTables profitieren können. Außerdem richtet sich das Buch an Anwender*innen, die PivotTables bereits nutzen, jedoch darüber hinaus verstehen wollen, wie diese richtig funktionieren, um gezielt professionelle Lösungen zu modellieren.
Ein eigenes Kapitel widmet sich dem Tool Power Pivot, mit welchem Sie große Datenmengen aus mehreren Quellen importieren und analysieren können. Anhand praktischer Beispiele wie der Projektfinanzsteuerung, der Qualitätsanalyse oder der Personalstrukturanalyse lernen Sie die Funktionsweise von PivotTables und PivotCharts in Excel 2010 bis 2019 sowie 365 kennen, verstehen und gewinnbringend einzusetzen.
Aus dem Inhalt:
- Tipps für den PivotTable-Schnellstart
- Planen und Vorbereiten
- Daten aufbereiten und auswerten
- Mit Daten jonglieren
- Projektfinanzsteuerung
- Stundenabweichungsanalyse
- ABC-Analyse mit PivotTables
- Personalkosten- und Personalstrukturanalyse
- Umsatzanalysen mit Plan-Ist-Vergleichen
- Datenmodelle und Beziehungen
- Datenauswertung mit Power Pivot

Leseprobe

Helmut Schuster

ist Diplom-Betriebswirt und seit mehr als 30 Jahren als Trainer und Berater im Bereich betriebswirtschaftliche Anwendungen mit Office tätig. Er ist Spezialist für Planung, Budgetierung, Prognosen und Analysen mit Microsoft Excel und Power BI. Darüber hinaus hat er als Co-Autor zahlreiche IT-Fachbücher, u. a. »Excel 2016 – Das Handbuch«, veröffentlicht. Außerdem ist er ausgebildeter psychologischer Coach und lässt dieses Know-how in die Trainings- und Projektprozesse nutzbringend einfließen.

Zu diesem Buch – sowie zu vielen weiteren O’Reilly-Büchern – können Sie auch das entsprechende E-Book im PDF-Format herunterladen. Werden Sie dazu einfach Mitglied bei oreilly.plus+:

www.oreilly.plus

Helmut Schuster

Microsoft Excel Pivot-Tabellen –Das Praxisbuch

Ideen und Lösungen für die Datenanalysemit PivotTables und PivotCharts sowie intensivem Einstiegin Power Pivot für Version 2013, 2016, 2019 & 365

2., aktualisierte Auflage

Helmut Schuster

Lektorat: Sandra Bollenbacher

Korrektorat: Annette Schwarz, Ditzingen

Satz: Gerhard Alfes, mediaService, Siegen, www.mediaservice.tv

Herstellung: Stefanie Weidner

Umschlaggestaltung: Michael Oreal, www.oreal.de

Coverbild: Stock-Fotografie-ID:648179250, Bildnachweis: wacomka

Bibliografische Information der Deutschen Nationalbibliothek

Die Deutsche Nationalbibliothek verzeichnet diese Publikation in der deutschen Nationalbibliografie; detaillierte bibliografische Daten sind im Internet über http://dnb.d-nb.de abrufbar.

ISBN:

Print    978-3-96009-152-3

PDF     978-3-96010-402-5

ePub    978-3-96010-403-2

mobi    978-3-96010-404-9

2., aktualisierte Auflage 2021

Copyright © 2021 dpunkt.verlag GmbH

Wieblinger Weg 17

69123 Heidelberg

Dieses Buch erscheint in Kooperation mit O'Reilly Media, Inc. unter dem Imprint »O'REILLY«. O'REILLY ist ein Markenzeichen und eine eingetragene Marke von O'Reilly Media, Inc. Und wird mit Einwilligung des Eigentümers verwendet.

Hinweis:

Dieses Buch wurde auf PEFC-zertifiziertem Papier aus nachhaltiger Waldwirtschaft gedruckt. Der Umwelt zuliebe verzichten wir zusätzlich auf die Einschweißfolie.

Schreiben Sie uns:

Falls Sie Anregungen, Wünsche und Kommentare haben, lassen Sie es uns wissen: [email protected].

Die vorliegende Publikation ist urheberrechtlich geschützt. Alle Rechte vorbehalten. Die Verwendung der Texte und Abbildungen, auch auszugsweise, ist ohne die schriftliche Zustimmung des Verlags urheberrechtswidrig und daher strafbar. Dies gilt insbesondere für die Vervielfältigung, Übersetzung oder die Verwendung in elektronischen Systemen. Es wird darauf hingewiesen, dass die im Buch verwendeten Soft- und Hardware-Bezeichnungen sowie Markennamen und Produktbezeichnungen der jeweiligen Firmen im Allgemeinen warenzeichen-, marken- oder patentrechtlichem Schutz unterliegen. Alle Angaben und Programme in diesem Buch wurden mit größter Sorgfalt kontrolliert. Weder Autor noch Verlag können jedoch für Schäden haftbar gemacht werden, die in Zusammenhang mit der Verwendung dieses Buches stehen.

5 4 3 2 1 0

Inhalt

Vorwort

Der Aufbau dieses Buches

Rechts- oder Linkshänder?

Übungsdateien und Hilfe

1Von der Idee zum Bericht

Entwurf von Modellen und deren Dokumentation

Planungsmethode und Lösungsentwicklung

Praxisbeispiel

Aufbereiten der Daten und Aufbau der Lösung

Verwaltung der Daten

2Entdecken Sie die Möglichkeiten von PivotTables und PivotCharts

Lassen sich alle Daten mit PivotTables auswerten?

Die verschiedenen Wege zu einer PivotTable

Diese Prozessschritte führen Sie zu einem Pivot-Bericht

Jetzt erstellen Sie eine PivotTable

So verschieben Sie im Layoutbereich die Felder blitzschnell

Darstellung der PivotTable ändern

Gruppierung von Elementen in einer PivotTable

Wie Datentypen die Gruppierung beeinflussen

Eine Gruppierung aufheben

Feldbeschriftungen in PivotTables und PivotCharts

So erweitern Sie die PivotTable und verändern die Feldanordnung

Mehrere Felder im Wertebereich

Mit dem Berichtsfilter Daten selektiv anzeigen

Alle Daten wieder anzeigen

Die Wirkung der Layoutaktualisierung

Felder verschieben, hinzufügen oder entfernen

Feld aus dem Layoutabschnitt entfernen

Feldbezeichnungen ändern

Nutzen der PivotTable-Felderansicht

Was beim Verschieben von Feldern in den vier Berichtsbereichen passiert

Arbeitserleichterung durch eine geeignete Datensortierung

Berechnungstypen in PivotTables

Schnellformatierung von PivotTables

PivotCharts: sich schon vorher ein Bild machen

Aus einer PivotTable ein PivotChart erstellen

Diagramm beschriften

Das Layout für Berichte gestalten

Berichtslayout in den PivotTable-Optionen anpassen

Tabellenoptionen erleichtern die Arbeit mit PivotTables

Anzeigen von Teilergebnissen und Gesamtergebnissen

Sortieren in PivotTables

Elemente mit der Maus verschieben

Benutzerdefinierte Sortierreihenfolge erstellen und anwenden

So sortieren Sie eine PivotTable nach Teilergebnissen

Suchen und Filtern in den PivotTables

Datenreduzierung auf oberster Ebene

Komplexe Filter über das Eingabefeld »Suchen« steuern

Die aktuelle Markierung als weitere Filtermöglichkeit verwenden

Felder vor dem Hinzufügen filtern

In einem Feld mehrere Filter aktivieren

Neue Elemente automatisch anzeigen

Vergleichsfilter im Umgang mit Datumswerten

Filter löschen und alle Daten wieder anzeigen

Genial: einfaches Filtern mit Datenschnitt und Zeitachse

Einen Datenschnitt an Ihre Bedingungen anpassen

Datenschnitte anzeigen oder ausblenden

Größe und Eigenschaften von Datenschnitten

Datenschnitt mit mehreren PivotTables verbinden

Aktives Filtern mit der Zeitachse

3Vertiefter Umgang mit PivotTable

Gruppierung des Datums ändern

Die Ansicht expandieren

Eine Pivot-Tabelle kopieren

Benutzerdefinierte Berechnungen für PivotTable-Wertefelder einsetzen

Löschen eines PivotTable-Berichts oder PivotChart-Berichts

Berechnungstypen im praktischen Einsatz

% des Gesamtergebnisses berechnen

% des Spaltenergebnisses

% des Zeilenergebnisses

% von

% des übergeordneten Zeilenergebnisses

% des übergeordneten Spaltenergebnisses

% des übergeordneten Ergebnisses

Differenz von

% Differenz von

Praxisbeispiel: Differenz zu einem vorherigen Basiselement in einem abweichenden Geschäftsjahr

Laufende Summe von

Rangfolge nach Größe (aufsteigend/absteigend)

Index

Praxisbeispiel: % Differenz und Fehlerbehandlung

In PivotTable Fehlerdarstellungen bearbeiten

Weitere Berechnungsmöglichkeiten in PivotTables

So erstellen Sie ein berechnetes Feld in einer PivotTable

So erstellen Sie ein berechnetes Element in einer PivotTable

Lösungsreihenfolge für berechnete Elemente ändern

Eine Liste der verwendeten Formeln erstellen

So verwenden Sie die Funktion PIVOTDATENZUORDNEN()

Zugriff auf Pivot-Daten

Fehlermeldung der Funktion

4In der Praxis: Logistikdienste mit PivotTables organisieren

Überlegte Vorbereitung erleichtert das Arbeiten: als Datenbasis eine strukturierte Tabelle verwenden

Die PivotTable erstellen

Die PivotTable informativer machen

Daten als Information flexibel anzeigen

Die Anordnung macht’s: die Felder im informativsten Layoutbereich anzeigen

Die Anzahl bestimmt die benötigte Kapazität

In welches Hotel geht der Transfer des Teilnehmers?

Den Check-in im Hotel planen

Elegante Dynamik: mit Datenschnitten die Informationen komfortabel filtern

Datenschnitte zum Filtern einbauen

Die Steuerzentrale

5Stundenabweichungsanalyse mit PivotTable

Die Projektstruktur aufbauen

Daten in PivotTable-Berichten auswerten

Daten aus einem Vorsystem und der Planung übernehmen

Daten aufbereiten, vervollständigen und pivotisieren

Das Tabellenblatt WBS-Planung pivotisieren

Importierte Daten pivotisieren und formatieren

Dezimale Stundenangaben in Uhrzeitformat umwandeln

Die Daten der Planung mit den Istdaten vergleichen und bewerten

Zwei PivotTable-Berichte konsolidieren

Zeitdifferenz berechnen

Abweichungen hervorheben: weitere Formatierungen vornehmen

Ein Projekt nach erweiterten Gesichtspunkten auswerten

6Mit PivotTable einen Kostenträger überwachen und auswerten

Ein Projekt nach erweiterten Gesichtspunkten auswerten

Kostenstellenanalyse der Kostenträger 100112 und 1414

Nur die betroffenen Kostenträger anzeigen

Kompaktansicht im Kostenträger: Details im Work Package ausblenden

Mitarbeiter, die die angezeigte Leistung erbracht haben

Die Gesamtkosten für den Kostenträger 100112 mit PivotTables berechnen

Mehr Aussagekraft durch Neuanordnung der Wertefelder

Die Stundenverteilung differenzieren – monatsweise anzeigen

Ermitteln Sie die zeitlichen Arbeitsleistungen der Mitarbeiter in einem Projekt

Die betroffenen Kostenstellen auswerten

7Personal- und Personalstrukturanalyse mit PivotTable-Berichten

Prozess der Analyse

Welche Felder in der Basistabelle verändert oder berechnet werden

Eine »intelligente« Tabelle

Strukturen im Stellenplan und in den Tätigkeitsfeldern entdecken

Zeigen Sie, wie viele Tätigkeitsfelder es im Unternehmen gibt

Zeigen Sie die fünf Tätigkeitsfelder mit den meisten Mitarbeitern

Neue Reihenfolge festlegen

Prozentuale Darstellung wählen

Prozentwerte und absolute Werte gleichzeitig darstellen

Datenschnitte: mehr als nur einfache Filter

Den Datenschnitt für die Vorselektion aufbauen

Die Datenschnitte perfekt auf die Arbeitsumgebung einstellen

Einstellungen für den Datenschnitt vornehmen

Strukturanalyse zur Altersverteilung und Betriebszugehörigkeit

Gruppen für Zeiträume bilden

Aufbau des PivotTable-Berichts Betriebszugehörigkeit

Betriebszugehörigkeit gruppieren

Den Namen einer Feldschaltfläche anpassen

Gruppen für Altersklassen bilden

Die Anzahl der Mitarbeiter je Altersgruppe in den Tätigkeitsfeldern ermitteln

Zeigen Sie in einer Grafik die Mitarbeiterverteilung in den Altersgruppen

Das Layout des Diagramms verändern

Zeigen Sie die grafische Verteilung der Betriebszugehörigkeit

Die Betriebszugehörigkeit als Grafik

Besondere Strukturen mit der PivotTable aufzeigen

Zeigen Sie die Struktur der Betriebszugehörigkeit für das Tätigkeitsfeld Produktberater/-in an

PivotTable-Bericht mit Minimum, Maximum und Durchschnittseinkommen je Altersgruppe

Die Anzahl der Mitarbeiter einfügen, die die Berechnungsgrundlage bildet

Mitarbeiter, die mehr als 120 Stunden monatlich tätig sind

Auswertung auf ausgewählte Tätigkeitsfelder begrenzen

8Wie Sie mit PivotTables Umsätze und Kosten berechnen und analysieren

Aktion und Analyse

Aufbau der PivotTable zur Kostenanalyse

PivotTable in die Tabellenansicht und in ein neues Pivot-Format überführen

Basisjahr 2019: Differenz der Kosten berechnen

Basiswert des Jahres 2019 in die Ansicht des Berichts integrieren

Die PivotTable informativer machen

Prozentanteil des Deckungsbeitrages ermitteln

Prozentanteil der Kosten am Umsatz

9Mit PivotTable aggregieren: mit PIVOTDATENZUORDNEN() Daten extrahieren

Die Arbeitsweise der Funktion

Syntax der Funktion

Vorbereitung der Arbeitsumgebung

Aufbau des Dashboards

Die Auswahllisten erstellen

Einrichten der Datenüberprüfung zur Auswahl der Steuergrößen

Überprüfung der Auswahl und Folgeeinträge in den Steuertabellen

Die Argumente der Funktion PIVOTDATENZUORDNEN()

Die Funktion PIVOTDATENZUORDNEN() entwickeln und dynamisieren

Fehlerbehandlung

10Innovative Analyse und Berichte mit Gruppierungen

Gruppierungen – die besondere Form der Datenbearbeitung

Die praktischen Schritte zum neuen Pivot-Bericht

Die Segmente in Regionen umarbeiten

Die Regionen in Cluster zusammenführen und gruppieren

Produkte in Klassen und Geschäftsbereichen zusammenfassen und gruppieren

Die Klassen zu Geschäftsbereichen (GB) zusammenfassen

Die Crux mit dem Cache

Wie entsteht und wirkt der Pivot-Cache?

Wie können Sie das umgehen?

Einen separaten Cache für eine PivotTable erstellen

Eine neue PivotTable mit einem bestimmten Cache verbinden

11Analysieren und Visualisieren mit PivotTables – Beispiel ABC-Analyse

ABC-Analyse mit PivotTable-Berichten erstellen

Vorbereiten der Basisdaten

Eine »intelligente Tabelle« anlegen

Aufbau der PivotTable

Formatierungen und Wertfeldberechnungen

Den Prozentanteil am Gesamtumsatz errechnen

Den Mittelwert berechnen

Den kumulierten Umsatz berechnen

Den kumulierten Umsatz in Prozent berechnen

ABC-Kategorien: die Einordnung der Umsätze berechnen

Bedingte Formatierung: die Zuordnung der Kunden in die Kategorie

Die Kategorien in geschlossener Formation anzeigen

Datenschnitt und Zeitachse: in den Kategorien nur ausgewählte Daten berechnen

Zeitachse einfügen

12Datenmodell und Beziehungen

Strukturierte Tabellen definieren und Beziehungen zwischen den Tabellen erstellen

Vorbereiten der Arbeitstabellen

Tabellen benennen

Beziehungen zwischen den Tabellen erstellen

Eine dritte Tabelle in die Informationsgewinnung einbeziehen

Bestehende Beziehungen bearbeiten

Der neue Weg – Berechnungen im Datenmodell

13Besondere PivotTable-Berichte

Daten-Set – die kreative Berichtsgestaltung

Daten verknüpfen und PivotTable erstellen

Datengruppe für den Bericht erstellen

14Power Pivot für Excel

Schneller denn je – Entscheidungen treffen

Power Pivot aktivieren

Unterschiede zwischen einer Tabelle in Power Pivot und einer Tabelle in einer Excel-Mappe

Power Pivot in Excel: leistungsstarke Datenmodellierung

Das Power-Pivot-Fenster: Ihre Arbeitsumgebung

Power Pivot: Excel-Tabellen auswerten

Beispiel: Datenzusammenführung in Power Pivot

Die Tabellen für die Verknüpfung vorbereiten

So verknüpfen Sie Tabellen in Power Pivot

Power Pivot: Beziehung zwischen den Tabellen herstellen

Power Pivot: Rechnen mit verknüpften Tabellen

Tabellen in Power Pivot um berechnete Spalten erweitern

Den Umsatzwert je Position berechnen

Formeleingabe in Power Pivot

Multidimensionale Berechnung – Rechnen über Beziehungen

Power Pivot: berechnete Felder

Ein berechnetes Feld (Measure) erstellen

Die Arbeitsweise der Funktion SUM() und der Funktion SUMX()

Power Pivot: Kontext

Zeilenkontext und erweiterter Kontext

Den Kontext des Benutzers überschreiben

Formeln: Dimension des Kontexts

Der Kontext am Beispiel von Formeln

Den Wert der Vergütung berechnen

PivotTable-Bericht. Die berechneten Rabattwerte anzeigen

Den PivotTable-Bericht formatieren

Zeitbezug in Power Pivot: die Datumstabelle

Datumstabelle: Struktur und Aufbau

Eine Datumstabelle zum Datenmodell hinzufügen

Kalendertabelle definieren

Eine Hierarchie erstellen

Im Kontext: Related() und Relatedtable()

Power Pivot: Berechnungen auf der Basis von Filtern

Summenbildung ohne Bedingungen und mit Bedingungen

Verhalten der Formeln bei Verwendung von Benutzerkontext

Prozentanteile am GesamtErgebnis berechnen

Power Pivot: Periodenvergleich

Den Umsatz berechnen

Den Umsatz des Vorjahres berechnen

Den PivotTable-Bericht erstellen

Kalendermonate in der korrekten Reihenfolge anzeigen

Power Pivot: Arbeiten mit Zeitintelligenz

Summenbildung über unterschiedliche Zeiträume

Laufende Summe YTD des Vorjahres

Umsatzvergleich zum gleichen Zeitraum des Vorjahres

Segmentanalyse anhand der Produktpreise

Die Auswahlsteuerung über einen Datenschnitt vornehmen

KPI-Analyse mit Power Pivot

Daten vorbereiten

Berechnete Felder und KPI erstellen

Den KPI (Key Performance Indicator) erstellen

PivotTable-Bericht erstellen

Datenschnitte steuern die Anzeige der PivotTable

Umsatzanalyse mit dynamischen Umsatzkategorien

Index

Vorwort

PivotTables, PivotCharts und insbesondere Power Pivot bedeuten selbst für erfahrene Excel-Anwender eine gewisse Herausforderung. Nicht selten lösen PivotTables bei Anwendern erst einmal eine gewisse Ratlosigkeit aus. Wenn Sie aber die Logik dieses Tools verstanden haben und damit umgehen können, werden Sie es mit Freude nutzen und hervorragend damit arbeiten.

Dieses Buch gibt Ihnen einen praxisorientierten Einblick in die vielfältigen Anwendungsmöglichkeiten von PivotTables und PivotCharts und macht Sie mit den Grundlagen und interessanten Berechnungsschritten von Power Pivot vertraut.

Die Herausforderung

Daten sind in der Unternehmensführung unentbehrlich und Grundlage für Entscheidungen, die nicht nur fundiert, sondern auch zeitnah getroffen werden sollen. Eine nahezu unüberschaubare Menge an Daten, die in unterschiedlichen Systemen erfasst wird, soll von Ihnen verarbeitet, aufbereitet, interpretiert, gedruckt und beispielsweise der Geschäftsführung zur Entscheidungsfindung präsentiert werden.

Das Szenario

Im Arbeitsalltag sieht das dann oft so aus: Sie holen Daten in eine Excel-Mappe, strukturieren sie, fassen sie mit Formeln und Filtern zusammen und stellen sie in einem Diagramm dar. Dann bekommen Sie neue Daten, ein Änderungswunsch hier, eine zusätzliche Berechnung dort – das Risiko, Fehler »einzubauen«, steigt enorm und alle Zeitpläne sind Makulatur.

Die Lösung …

… sind sichere Datenquellen, immer aktuelle und konsistente Daten in informativer Darstellung, perfekt aufbereitet und jederzeit leicht wandelbar.

Dazu finden Sie in diesem Buch eine Vielzahl von Ideen und praxiserprobten Lösungen. Die Beispiele sind auf das Wesentliche reduziert und können Schritt für Schritt nachvollzogen werden. Nutzen Sie die zahlreichen Tipps und Kniffe, um Ihre Datenflut zu kanalisieren und zu analysieren, und erstellen Sie informative und gut verständliche Auswertungen.

Der Aufbau dieses Buches

Das Buch beginnt mit den Überlegungen zum Aufbau eines Lösungsmodells und zeigt dann an Beispielen, wie Sie Berichtsstrukturen gestalten können. In den ersten Kapiteln lernen Sie die Funktionen und Formelbeispiele kennen und erhalten Hintergrundwissen zum Aufbau und der Anwendung von PivotTable-Berichten. In den folgenden Kapiteln finden Sie umfangreiche, aber dennoch kompakte Praxisbeispiele, die auch als Inspirationsquelle für eigene Aufgabenstellungen dienen können. Zum Abschluss des Buches lernen Sie an einem leicht verständlichen Beispiel Power Pivot kennen – ein Tool, das eine neue Dimension der Datenanalyse eröffnet.

Am Anfang steht die Datenvorbereitung, also die konzeptionelle Vorarbeit, die notwendig ist, um zu guten Ergebnissen zu kommen. Nachfolgend stelle ich dann einige Funktionen vor, die Sie beim Aufbau von Lösungen unterstützen oder mit denen Sie kleine Aufgaben schnell und effizient lösen können. Außerdem können sie die Grundlage für Zwischenschritte sein, auf deren Basis die nachfolgenden Schritte und Auswertung erst mühelos möglich werden.

Anhand von Praxisbeispielen zeige ich Ihnen, wie Sie Inhalte für die Weiterverarbeitung am besten aufbereiten, wie PivotTables oder PivotCharts aufgebaut werden können und für welche Aufgabenstellungen sie geeignet sind.

Die folgenden Hinweiskästen werden Ihnen beim Durcharbeiten des Buches immer wieder begegnen:

Achtung

Damit wird auf Gegebenheiten hingewiesen, die besonders beachtenswert sind.

Hinweis

Hier werden weitere Informationen zum behandelten Thema erwähnt, beispielsweise eine besondere Optionseinstellung für die gezeigte Darstellung.

Tipp

Mit diesem Hinweis wird beispielsweise auf eine alternative Lösung oder eine Besonderheit hingewiesen.

Wichtig

Diese Kennzeichnung macht Sie auf Punkte aufmerksam, die Sie unbedingt wissen und beachten sollten.

Übungsdateien

Hier wird erklärt, wo Sie die passenden Übungsdateien finden.

Rechts- oder Linkshänder?

Wenn Sie aufgefordert werden, die linke Maustaste zu drücken, dann ist darunter die Maustaste zu verstehen, die Sie mit der primären Funktion belegt haben. Wenn Sie als Linkshänder die beiden Maustasten in ihrer Primärfunktion vertauscht haben, dann ist diese Einstellung nicht konform mit der rechten Maustaste, wie dies für einen Rechtshänder zu verstehen ist.

Für einen Rechtshänder bedeutet die Anweisung »Klicken Sie mit der linken Maustaste«, dass er die linke Maustaste mit dem Zeigefinger seiner rechten Hand drückt. Ein Linkshänder, der die Maustasten vertauscht hat, muss bei dieser Anweisung jedoch die rechte Maustaste mit dem Zeigefinger seiner linken Hand drücken.

Sie können die Belegung der Maustasten in der Systemsteuerung von Windows überprüfen bzw. anpassen.

Übungsdateien und Hilfe

Die Übungs- und Lösungsdateien finden Sie im Download-Bereich zu diesem Buch auf www.oreilly.de/pivot2019.

Fragen und Feedback können Sie gerne an [email protected] schicken.

Kapitel 1

Von der Idee zum Bericht

In diesem Kapitel lernen Sie, …

wozu Lösungsmodelle sinnvoll sind,

wie Sie ein solches gestalten,

warum Dokumentationen notwendig und hilfreich sind und

wie Sie praktisch vorgehen, um eine Lösung zu implementieren.

Excel, das Lieblingsprogramm der Office-Anwender, ist einem permanenten Entwicklungs- und Veränderungsprozess unterworfen. Wie nun schon der Philosoph Heraklit vor über 2.500 Jahren wusste: Nichts ist so beständig wie der Wandel!

Der stetige Wandel und die immer wiederkehrenden Neuerungen erfordern kontinuierliche Anpassungen. Sowohl die Umwelt als auch die Datenlandschaft und die Anforderungen an das Reporting sind diesen Änderungsprozessen unterworfen. Bei geänderten Rahmenbedingungen müssen die dazu erstellten Modelle ebenfalls überarbeitet und angepasst werden. Darin jedoch liegt momentan die Crux: die Forderung nach Flexibilität auf der einen Seite, nach Stabilität und Schnelligkeit bei der Analyse auf der anderen.

Dennoch und auch deshalb ist es immer wieder verlockend und begeisternd, Excel aufzurufen und sofort Daten in einer Arbeitsmappe zu erfassen oder zu bearbeiten. Bei kleinen und einfachen Aufgabenstellungen ist dies ein häufig praktizierter, möglicher Lösungsweg. Wollen Sie aber mit umfangreichen Daten, verschiedenen Datenquellen und im Ergebnis mit zahlreichen Datenausgaben und Berichtsblättern arbeiten, ist es keineswegs ratsam, »mal einfach so« loszulegen. Hier bedarf es einiger Überlegungen, um ein übersichtliches, nachvollziehbares und vom Management akzeptiertes Arbeitsergebnis zu erzielen.

Entwurf von Modellen und deren Dokumentation

Microsoft Excel ist der Rechenkünstler und weit mehr als eine reine Tabellenkalkulationssoftware: Mithilfe zahlreicher Funktionen und Formeln, interessanter Techniken und aussagestarker Diagramme bereiten Sie die Daten auf. Je nach Aufgabenstellung müssen Daten aus anderen, oft verschiedenen Systemen integriert, Tabellen verknüpft, Daten verdichtet und aufbereitet werden. Planen Sie gerade zu Beginn der Aufgabenstellung genügend Zeit ein, um sich umfassende und detaillierte Gedanken über den Lösungsansatz und den erforderlichen Lösungsweg in Excel zu machen. Dies macht sich im Laufe der voranschreitenden Arbeit sehr schnell bezahlt. Ein überlegter und strukturierter Aufbau erleichtert jedes weitere Arbeiten sowie auch spätere Erweiterungen an und mit dem Excel-Modell.

Meine Empfehlung:

Investieren Sie Zeit in die Planung und Vorbereitung des (Lösungs-)Modells.

Überlegen Sie, welche Aufgaben und Anforderungen das Modell erfüllen soll und welche Struktur und Funktionalität Sie dazu benötigen.

Nicht zuletzt: Dokumentieren Sie das Modell, damit sowohl Sie selbst als auch Mitarbeiter und Kollegen den Aufbau und die Ziele verstehen und den Lösungsweg nachvollziehen können.

Planungsmethode und Lösungsentwicklung

Microsoft Excel stellt keinerlei Anforderungen an ein bestimmtes Vorgehen zum Erreichen der Lösung. Sie als Anwender entscheiden, meist nach Ausbildungsstand und Erfahrung, welchen Lösungsweg Sie einschlagen.

Die Entscheidung liegt ausschließlich bei Ihnen, ob Sie beispielsweise

mit Formeln oder Tabellenfunktionen arbeiten,

Datenbankfunktionen einsetzen oder unmittelbar zu einer Programmierlösung schreiten,

eine Lösung mit zahlreichen Tabellen in einer Mappe oder in verteilten Mappen anstreben,

Daten in Tabellenform mithilfe von Funktionen oder mit Assistenten und Datenbankfunktionen bearbeiten oder auch

PivotTables und PivotCharts verwenden oder

eine Lösung unter Einsatz von PivotTables in Verbindung mit Power Query und Power Pivot erarbeiten.

Nur wer das Endergebnis genau vor Augen hat, kann alle Schritte von der Datenerhebung bis zur Lösung vorausdenken. Mit einer derartigen soliden Planungsgrundlage können Sie sämtliche Module und Komponenten gezielt und im Kontext des Gesamtmodells gestalten und entwickeln.

Der Lösungsweg beginnt mit den Wünschen und Ideen, wie das Endergebnis in Form eines Berichts aussehen soll (Abbildung 1.1, Schritt [1] und [2]). In Schritt [3] finden zunächst Überlegungen zu den Anforderungen statt. Schritt [4] befasst sich mit der Entscheidung, welche Anforderungen davon konkret umgesetzt werden sollen, sowie mit Gedanken zum Design. Im nächsten Schritt [5] wird die Quelle bzw. die Herkunft der Daten unter die Lupe genommen. Im Anschluss daran läuft die Entwicklung und Lösungsumsetzung von Schritt [6] bis [7] – hier erstellen Sie anhand aller bekannten Anforderungen und Datenkonstellationen den Aufbau des realen Modells bis hin zum Druck des endgültigen Berichts.

Abbildung 1.1:Planung und Entwicklung eines Vorgehensmodells zum Aufbau eines Excel-Reports am Beispiel des V-Modells

Bevor Sie tatsächlich innerhalb der Mappe die Tabellenblätter aufbauen, ist es notwendig, sich die technischen und organisatorischen Komponenten für die Excel-Mappen und Tabellenblätter zu überlegen und festzuschreiben.

Praxisbeispiel

Angenommen, Sie erstellen einen monatlichen Bericht. Die Daten, die zu diesem Bericht führen, erhalten Sie aus einem Vorsystem in einem flachen (.txt, .csv) oder auch einem anderen Format. Diese Daten können Sie mit Power Query aufbereiten, in Ihr Lösungsmodell dynamisch und wiederholbar einlesen und verarbeiten. Der fertige Bericht wird als eigene Datei im Dateisystem abgelegt.

Dies wiederholt sich regelmäßig. Sie erhalten jeweils einen für sich abgeschlossenen Datenbestand, der umgesetzt und mit dem dazugehörigen Ergebnis gespeichert wird.

Eine andere Verarbeitungsvariante liest die Daten anstatt in eine Excel-Mappe in eine Datenbank ein und sammelt alle angefallenen Daten über einen größeren Zeitraum. Ihr (Berichts-)Modell greift auf die gesamte Datenbank zu und selektiert beispielsweise anhand eingegebener Zeitparameter lediglich den darzustellenden Zeitraum. Der Bericht wird erstellt, aber nicht gespeichert, weil er jederzeit aus dem Datenbestand in gleicher Weise oder aber auch über einen beliebigen anderen Zeitraum rekonstruiert werden kann. Abbildung 1.2 stellt einen beispielhaften schematischen Ablauf grafisch dar.

Abbildung 1.2:Schematischer Aufbau des Lösungswegs

Die Schritte im Einzelnen (Abbildung 1.2):

Im ersten Schritt werden die Daten in flacher Form als sequenzielle Textdatei aus einem Vorsystem in Microsoft Excel importiert oder, wenn es sich um sehr große Datenmengen handelt, in einer Datenbank zwischengespeichert.

Im zweiten Schritt werden die Daten aus unterschiedlichen Quellen mit Power Query aufbereitet und direkt aus der beispielsweise flachen Datei durch Power Query aufbereitet in Microsoft Excel eingelesen.

Oder sie werden in Microsoft Excel erfasst und dort für die weitere Bearbeitung aufbereitet.

Im dritten Arbeitsschritt werden die Daten in dem Lösungsmodell, beispielsweise PivotTables, verarbeitet.

Ausgabe des zusammengestellten Berichts, interaktiv oder statisch.

Aufbereiten der Daten und Aufbau der Lösung

Je nach Komplexität der Anforderung vollzieht sich der Weg zur Lösung genau wie der Aufbau der Daten innerhalb einer Mappe in folgenden Schritten:

Alle notwendigen Daten werden entweder komplett oder ggf. gefiltert aus der Datenbank in eine Excel-Tabelle übernommen.

Aus diesem importierten Datenmaterial werden beispielsweise mehrere Pivot-Tabellen oder formel- und funktionsbasierte Modelle aufgebaut.

Aus den Pivot-Tabellen werden die für die Grafik bzw. für die Darstellung erforderlichen Daten extrahiert,

in weiteren Modellen zusammengeführt und

mit dem Diagramm-Assistenten in eine Geschäftsgrafik umgesetzt oder unmittelbar in Tabellenform in den Bericht übernommen.

Verwaltung der Daten

Bei einem umfangreichen Modell ist auch zu überlegen, wo Hilfsdaten, beispielsweise umfangreiche Kriterienbereiche für Datenbankabfragen, aufgebaut werden, wo im Hinblick auf Power Pivot eine Kalendertabelle und welche Dimensionstabellen neben den Bewegungsdaten benötigt werden. In einem dynamischen Modell können die anzuzeigenden Daten des Berichts mit umfangreichen Datenschnitten gefiltert werden (Abbildung 1.3).

Abbildung 1.3:Steuerung der PivotTable-Anzeige über die Datenschnitte, Jahre, den Bereich und die Händler-Nummer

Mit einer durchdachten Abfrage bzw. Auswahlsteuerung automatisieren Sie als Anwender die erforderlichen Arbeitsprozesse und erhöhen vor allem die Qualität und Effizienz Ihrer Arbeit.

Kapitel 2

Entdecken Sie die Möglichkeiten von PivotTables und PivotCharts

In diesem Kapitel lernen Sie …

etwas über die Anwendungsmöglichkeiten von PivotTables und PivotCharts,

wie Sie PivotTables aufbauen und gestalten,

das Layout von PivotTables,

die Gruppierung und deren Möglichkeiten,

Datenschnitte aufzubauen und PivotTables zu steuern,

Sortieren und Filtern in PivotTables sowie

das Arbeiten mit dem Element Zeit.

Sie haben bisher Ihre Daten mit Formeln und Funktionen ausgewertet. Sind Sie dabei mit viel Mühe, aufwendigen Modellen und Methoden zu Ihren Ergebnissen gekommen, so bieten Ihnen PivotTables jetzt völlig neue Möglichkeiten: Sie können große Datenmengen schnell filtern, selektieren, umgestalten, berechnen, auswerten, neu anordnen oder in unterschiedlichsten Formen zusammenfassen und aggregieren. Nicht zuletzt lässt sich mit wenigen Handgriffen auch noch ein ansprechendes PivotChart erstellen.

Die PivotTable ist eine interaktive Tabelle, in der sich Daten in einer oder mehreren Gruppen zusammenfassen lassen. Jede Spalte in den Basisdaten wird mit ihrer Bezeichnung in der ersten, obersten Zelle in der PivotTable zu einem Feld. Gruppen entstehen unter Verwendung mathematischer Funktionen wie beispielsweise Summe oder Anzahl.

Sie erstellen eine PivotTable mithilfe des PivotTable-Assistenten. Anschließend können Sie die Felder beliebig in den jeweiligen Bereichen anordnen sowie jederzeit verschieben. Daten lassen sich unter wechselnden Gesichtspunkten verändern und auch neu bewerten. Innerhalb einer PivotTable sind die Daten nicht veränderbar – aber die Anordnung der Felder und die damit verbundene Sicht auf die Daten können angepasst bzw. verändert werden. Durch diesen Mechanismus ist eine versehentliche Veränderung der Daten beim Pivotisieren ausgeschlossen. Gewünschte bzw. erforderliche Änderungen führen Sie immer in den Basisdaten aus.

Die PivotTable bietet eine interaktive Möglichkeit für verschiedene Anwendungen:

Sie ermöglicht eine schnelle Analyse und übersichtliche Aufbereitung Ihrer umfangreichen numerischen Daten.

Mit wenigen Handgriffen und von Assistenten unterstützt erreichen Sie schnell eine anschauliche Darstellung Ihrer Daten.

Änderungen, Ergänzungen und das Hinzufügen neuer Elemente lassen sich in überschaubaren Schritten schnell erledigen.

Um bestimmte Ereignisse hervorzuheben, können Sie die Datenebenen sowohl erweitern als auch reduzieren. Ferner lässt sich ein »Drilldown« der zusammengefassten Daten ausführen, um damit die Datensätze aufzulisten, die hinter einem Ergebnis liegen.

Außerdem können Sie die PivotTable auch als Zwischeninstrument für die Zusammenführung unterschiedlicher Daten einsetzen.

Die PivotTable eignet sich auch als Instrument, um Daten in eine andere Form, eine sequenzielle Liste, zu konvertieren.

Um sich auf interessante und nützliche Teilmengen konzentrieren zu können, gibt es die Möglichkeit der Gruppierung, Filterung und bedingten Formatierung.

Umfassende Formatierungsmöglichkeiten und vorgefertigte Formatvorlagen unterstützen Sie bei ansprechenden Darstellungen am Bildschirm und ebenso bei Berichten, die gedruckt werden sollen.

Hinweis

Die PivotTables und PivotCharts, die Sie in den folgenden Kapiteln sehen, wurden mit Microsoft Excel 2019 und Excel Microsoft 365 erstellt.

Lassen sich alle Daten mit PivotTables auswerten?

Um mit PivotTables sinnvoll und zielgerichtet arbeiten zu können, spielt die Qualität der Basisdaten eine große Rolle. Deshalb sollten Sie für die Arbeit mit PivotTables – für Berechnungen bzw. Auswertungen – einige Dinge beachten. Die grundsätzliche Organisation der Daten richtet sich nach den Anforderungen, die auch bei Datenbanken zu erfüllen sind. Beachten Sie demnach folgende neun Anforderungen:

Jede Spalte benötigt eine Überschrift als Feldnamen (Feldbezeichner), der möglichst kurz gehalten werden sollte.

Der Datenbereich muss mindestens aus zwei Zeilen bestehen.

Die Anzahl der Spalten ist nur durch die Größe des Tabellenblattes begrenzt und innerhalb der PivotTable durch den zur Verfügung stehenden Speicherplatz.

Der Quellbereich darf keine leeren Spalten oder Zeilen enthalten.

Eine Spalte mit Datum sollte immer in jeder Zelle einen gültigen Datumseintrag aufweisen (ein fehlendes oder fehlerhaftes Datum führt zu einem falschen Ergebnis).

Innerhalb der Zeilen oder Spalten dürfen keine Berechnungen mit Zwischensummen bzw. Teilergebnisse enthalten sein.

keine Gliederung in einem Tabellenblatt mit Daten für die PivotTable-Auswertung

Vermeiden Sie Gesamtsummen am Ende des Quelldatenbereichs. Sind dennoch welche vorhanden, dürfen sie auf keinen Fall in den Datenbereich der PivotTable eingeschlossen werden. Durch Einschluss solcher Zeilen entstehen falsche Ergebnisse.

Die Anreicherung von Daten durch zusätzliche berechnete Spalten (Felder) ist erlaubt, in einigen Fällen sinnvoll, in manchen Fällen sogar erforderlich.

Stellen Sie dynamische Quellbereiche der PivotTable durch Bereichsnamen ebenfalls dynamisch zur Verfügung (beispielsweise durch die Verwendung von »intelligenten Tabellen«) – so umgehen Sie wiederkehrende manuelle Datenbereichsanpassungen). Intelligente Tabellen sind immer notwendig, wenn Sie mit dem Datenmodell arbeiten.

Ausgeblendete Zeilen oder Spalten in den Quelldaten werden in den PivotTables in die Berechnung mit einbezogen und angezeigt.

Die verschiedenen Wege zu einer PivotTable

Wollen Sie eine PivotTable erstellen, stehen Ihnen drei unterschiedliche Wege zur Verfügung, nämlich über:

empfohlene PivotTables

den PivotTable-Assistenten

den Befehl

Einfügen/PivotTables

Der Weg über die empfohlenen PivotTables [1] ist für die Anwender interessant, die wenig bis keine Erfahrung mit PivotTables haben oder keine Vorstellung davon, wie sie eine PivotTable aufbauen und Daten auswerten können. Hier bietet Excel Unterstützung und zeigt eine Auswahl von vordefinierten Varianten an. Anhand dieser Beispiele kann sich der Anwender ein Bild vom Ergebnis seiner Auswertabsicht machen.

Der Weg über den PivotTable-Assistenten [2] steht nicht ohne Vorbereitung oder das Kennen des ShortCuts zur Verfügung ++. Er bietet jedoch neben dem reinen Aufbau einer PivotTable noch einige zusätzliche Möglichkeiten, Daten auszuwerten oder Arbeitsschritte im Hintergrund einer PivotTable zu erledigen. Auf diese Möglichkeiten gehe ich in den praktischen Beispielen detailliert ein.

Der von Ihnen gezielt gesteuerte und kontrollierte Weg zu einer PivotTable führt über den Befehl Einfügen/PivotTable zum Dialog PivotTable erstellen, in dem Sie die für Ihre Zwecke entsprechenden Auswahlmöglichkeiten bearbeiten (Abbildung 2.1).

Abbildung 2.1:In diesem Dialogfeld bearbeiten Sie die Einstellungen und Optionen für die PivotTable.

Diese Prozessschritte führen Sie zu einem Pivot-Bericht

Eine PivotTable erstellen Sie immer in einem obligatorischen Prozessablauf. Es ist jedoch möglich, dass Sie für das Ergebnis Ihres Berichts einige Schritte nicht benötigen.

Abbildung 2.2:Erstellen einer PivotTable und zusätzliche Möglichkeiten zur Bearbeitung und Gestaltung einer PivotTable

Mit folgenden Schritten erstellen Sie Ihre PivotTable:

Eine intelligente Tabelle erzeugen oder den Quellbereich selektieren

Den Befehl PivotTable aufrufen und den Ausgabebereich der PivotTable bestimmen (

Abbildung 2.2

)

Das Layout der PivotTable finden: Welche Felder müssen an welche Position?

Das Ergebnis des PivotTable-Berichts

Aggregatfunktionen auswählen: Mit welcher Funktion sollen die Daten zusammengefasst werden?

Elemente nach Wunsch sortieren

Datenmenge durch Filter reduzieren

Elemente gruppieren

PivotTable-Bericht formatieren und präsentieren

Jetzt erstellen Sie eine PivotTable

Übungsdateien

Die Übungsdaten zu diesem und den folgenden Beispielen finden Sie in der Excel-Datei Kap_02_UEB.xlsx. Die Pivot-Arbeitsschritte in diesem Kapitel wurden mit Microsoft Excel 365 durchgeführt.

Öffnen Sie diese Datei und wechseln Sie auf die Registerkarte Basisdaten. Dort finden Sie Daten, die zur Erstellung einer PivotTable geeignet sind. Zuerst positionieren Sie die Einfügemarke innerhalb des Datenbereichs und führen folgende Schritte aus:

1.Klicken Sie im Menüband auf der Registerkarte

Einfügen

in der Befehlsgruppe

Tabellen

auf den Befehl

PivotTable

.

2.In dem Dialogfeld, das sich nun öffnet, bestimmen Sie, welche Daten ausgewählt werden sollen und an welcher Position der PivotTable-Bericht erstellt werden soll. Im Textfeld

Tabelle/Bereich

wird dabei automatisch der Bezug auf die aktive Tabelle vorgegeben.

Abbildung 2.3:In diesem Dialogfeld selektieren Sie den Datenbereich in den Basisdaten und bestimmen den Ausgabeort der PivotTable.

Hinweis

Ist der ausgewählte Datenbereich nicht korrekt, wählen Sie im Dialogfeld PivotTable erstellen im Textfeld Tabelle/Bereich die Schaltfläche Erweitern. Danach können Sie den Datenbereich neu auswählen oder auch die Zelladresse direkt in das Textfeld eintragen. Mit einem Klick auf die Schaltfläche Reduzieren kehren Sie wieder zurück in die vollständige Ansicht des Dialogfeldes.

3.Wenn der Datenbereich korrekt ist, bestätigen Sie mit

OK

.

Abbildung 2.4:Das Tabellenblatt mit der PivotTable, in der jetzt die Felder zur Auswertung angeordnet werden

4.Sie befinden sich jetzt unmittelbar im Tabellenblatt, im Aufbaudialog der PivotTable (

Abbildung 2.4

).

Hinweis

Eine zentrale Rolle für das Hinzufügen von Feldern in der PivotTable übernimmt die PivotTable-Feldliste. In diesem Aufgabenbereich werden die Felder hinzugefügt, neu angeordnet oder auch entfernt.

Um ein Feld innerhalb der PivotTable neu anzuordnen, benötigen Sie folglich die PivotTable-Feldliste. Die Feldliste wird immer eingeblendet, sobald Sie eine PivotTable erstellt haben – wenn Sie also die Auswahl der Quelldaten sowie den Ausgabebereich für die PivotTable festgelegt und das Dialogfeld per OK verlassen haben.

Der Aufgabenbereich PivotTable-Feldliste ist die zentrale Steuerstelle zum Aufbau einer PivotTable bzw. zum Verändern bestehender PivotTables.

In diesem Entwicklungsstadium zeigt die PivotTable noch keine Daten an. Im oberen Teil der PivotTable-Feldliste sehen Sie die Feldnamen, wie sie in den Basisdaten als Feldbezeichner eingetragen sind. Durch Anklicken des jeweiligen Kontrollkästchens und das Anordnen des Feldes im Layoutbereich wird die zugehörige Spalte mit ihren Daten im PivotTable-Bericht angezeigt.

Wählen Sie die Felder aus, die für den Aufbau Ihrer Pivot-Auswertung relevant sind. Dazu aktivieren Sie in der PivotTable-Feldliste die entsprechenden Kontrollkästchen vor den Feldnamen, woraufhin die Felder automatisch in einer beliebigen Rubrik des Layoutbereichs angeordnet werden. Danach verschieben Sie die betroffenen Felder in die Rubrik, die die beste Aussagequalität aufweist.

Für die erste Auswertung benötigen Sie folgende Feldanordnung im Layoutbereich (Abbildung 2.5):

das Feld

Wert

im

Wertebereich

das Feld

Land

im

Spaltenbereich

das Feld

Datum

im

Zeilenbereich

Abbildung 2.5:Anordnung der Felder im Layoutbereich

Hinweis

Sollte die PivotTable ein Feld im Layoutbereich als Datum identifizieren, wird dieses Feld automatisch in Jahr und Monat gruppiert. In den meisten Fällen wird die Monatsdarstellung in die PivotTable übernommen.

Hinweis

Bei der Aktivierung eines Feldnamens in der PivotTable-Feldliste wird dieses Feld automatisch einem Layoutbereich zugeordnet. Entscheidend für die Zuordnung ist der Datentyp des Feldes:

Enthält eine Spalte nur Zahlen, wird das Feld dem Bereich Werte zugeordnet und die Aggregatfunktion Summe angewendet.

Enthält die Spalte nur Text oder unterschiedliche Datentypen, wird das Feld dem Bereich Zeilen hinzugefügt.

Sie können jederzeit und beliebig oft sowohl die Zuordnung der Felder im Layoutbereich als auch den Berechnungstyp (Aggregatfunktion) ändern. Ein Feld kann allerdings in den Bereichen Filter, Spalten und Zeilen jeweils nur einmal zugeordnet werden. Hingegen lässt sich jedes Feld dem Bereich Werte mehrfach zuordnen. Zur Unterscheidung und vor allem, um Eindeutigkeit zu erhalten, bekommt das Feld dann zum Namen eine laufende Nummer, beispielsweise Land, Land2, Land3).

So verschieben Sie im Layoutbereich die Felder blitzschnell

Im täglichen Arbeitseinsatz ist es oft vorteilhafter, das jeweilige Feld mit der Maus direkt in den Layoutbereich zu ziehen. Ziehen Sie das gewünschte Feld bei gedrückt gehaltener Maustaste in den Zielbereich und lösen Sie dort die Maustaste. In der Standardeinstellung von Microsoft Excel können Sie die Felder in ihrer Position bzw. Anordnung nur in der PivotTable-Feldliste verändern.

Neben dem direkten Ziehen von Feldern in einen anderen Layoutbereich gibt es auch die Möglichkeit, einen Wechsel über ein Kontextmenü vorzunehmen, das hier entweder mit der rechten Maustaste geöffnet wird oder mit einem Klick auf den Pfeil rechts im Namensfeld (Abbildung 2.6).

Abbildung 2.6:Hinzufügen oder Wechseln eines Feldes über das Kontextmenü

Darstellung der PivotTable ändern

Die Darstellung der PivotTable genügt bei der ersten Anordnung oftmals nicht den Ansprüchen. Das Feld Wert wird bei der ersten Belegung häufig mit der Aggregatfunktion Anzahl zusammengefasst, und zwar dann, wenn sich neben Werten auch Text in der Spalte befindet. Bei dem Feld Datum kann es sein, dass die PivotTable zuerst die einzelnen Tagesdaten zeigt.

In einem solchen Fall sollten Sie das Feld im Wertebereich auf die benötigte Aggregatfunktion umstellen, in unserem Beispiel auf Summe. Dazu sind folgende Arbeitsschritte notwendig:

Abbildung 2.7:DialogfeldWertfeldeinstellungenzur Änderung der AggregatfunktionAnzahl in Summe

Um das Zahlenformat auf zwei Nachkommastellen und 1000er-Trennzeichen zu stellen, gehen Sie folgendermaßen vor:

Positionieren Sie die Einfügemarke auf einem Wert in der PivotTable. Danach öffnen Sie mit der

rechten

Maustaste das Kontextmenü und wählen den Befehl

Wertfeldeinstellungen

.

Klicken Sie auf die Schaltfläche

Zahlenformat

und aktivieren Sie in dem folgenden Dialogfeld die Kategorie

Zahl

. Im rechten Abschnitt des Dialogfeldes stellen Sie das Zahlenformat auf zwei Nachkommastellen ein und aktivieren das Kontrollkästchen

1000er-Trennzeichen verwenden

.

Alternativ können Sie im Kontextmenü direkt den Befehl Zahlenformat anklicken.

Mit einem Klick auf

OK

in diesem Dialogfeld und einem weiteren Klick auf

OK

im Dialogfeld

Wertfeldeinstellungen

übernehmen Sie die Formatierung in die PivotTable.

Sollte in der PivotTable das Tagesdatum gezeigt werden, gehen Sie folgendermaßen vor, um eine monatliche Darstellung zu erreichen:

1.Positionieren Sie den Cursor auf einem Datum, öffnen Sie mit der rechten Maustaste das Kontextmenü und wählen Sie dort den Befehl

Gruppieren

.

2.Im folgenden Dialogfeld übernehmen Sie die Voreinstellungen (

Abbildung 2.8

) mit einem Klick auf

OK

.

Abbildung 2.8:DialogfeldGruppierungzur Umwandlung des Datums in Tagesdatum und Monatsdarstellung

3.Als Ergebnis erhalten Sie die in

Abbildung 2.9

dargestellte PivotTable.

Abbildung 2.9:Ergebnis des ersten, noch unformatierten PivotTable-Berichts (Ausschnitt)

Hinweis

Die PivotTable-Feldliste wird nur angezeigt, wenn der Cursor in einer PivotTable positioniert ist.

Wird bei einer aktivierten PivotTable die PivotTable-Feldliste nicht eingeblendet, so wurde sie gezielt ausgeblendet. Sie kann jederzeit über die Registerkarte PivotTable-Analyse in der Befehlsgruppe Einblenden mit einem Klick auf den Befehl Feldliste wieder angezeigt werden. Ebenfalls finden Sie den gleichen Befehl im Kontextmenü, das mit der rechten Maustaste aufgerufen wird.

Gruppierung von Elementen in einer PivotTable

Mitunter erschweren umfangreiche oder zu detaillierte Daten die Übersicht. In diesem Fall kann es sinnvoll sein, Daten zu Gruppen zusammenzufassen. Im vorausgehenden Beispiel haben Sie Tagesdaten zu Monaten und ggf. Jahren gruppiert. Gleiches lässt sich mit numerischen oder Textdaten vornehmen. Sie können beispielsweise die Länder in zwei Gruppen von A–F und G–Z zusammenfassen.

Falls das Feld Land noch nicht im Zeilenbereich angeordnet ist, verschieben Sie es vom Layoutbereich Spalten in den Bereich Zeilen und entfernen das Feld Datum und Monat aus der PivotTable.

Die Gruppierung erreichen Sie in vier Schritten:

Markieren Sie in der PivotTable die Ländernamen, deren Anfangsbuchstabe im Bereich A–F liegt.

Öffnen Sie mit der rechten Maustaste das Kontextmenü und wählen Sie dort den Befehl

Gruppieren

. Daraufhin wird die erste Gruppierung mit der Bezeichnung Gruppe1 eingefügt.

Sie können eine Gruppierung auch über die Registerkarte PivotTable-Analyse erstellen: Hierzu markieren Sie ebenso die gewünschten Ländernamen in der PivotTable, öffnen im Register PivotTable-Analyse den Befehl Gruppieren und wählen dann den Eintrag Auswahl gruppieren.

Markieren Sie die restlichen Zeilen von G–Z und wählen Sie im Kontextmenü erneut den Befehl

Gruppieren

. Daraufhin wird die zweite Gruppierung mit der Bezeichnung

Gruppe2

eingefügt.

Als Ergebnis erhalten Sie eine in zwei Gruppen aufgeteilte PivotTable (

Abbildung 2.10

).

Abbildung 2.10:Manuelle Gruppierung von Textelementen

Tipp

Durch die Gruppierung entsteht ein neues Feld Land2. In der PivotTable werden die markierten Zeilen in Gruppe1 und Gruppe2 zusammengefasst. Sie haben jederzeit die Möglichkeit, die Bezeichnung für die Gruppe nach Ihren Wünschen anzupassen. Wenn Sie die Zelle mit der Gruppenbeschriftung markieren, können Sie in der Bearbeitungszeile einen beliebigen Text als Bezeichnung eintragen.

Wie Datentypen die Gruppierung beeinflussen

Für die unterschiedlichen Gruppierungsmöglichkeiten sind drei Datentypen maßgeblich:

Textdaten

lassen sich in einem neuen Feld gruppieren, indem aus mehreren Elementen eine neue Gruppe gebildet wird. Microsoft Excel erstellt automatisch das gruppierte Feld, in das nur die innerhalb der Gruppe definierten (markierten) Elemente aufgenommen werden.

Numerische Daten

können in Bereichen mit bestimmten Elementen gruppiert werden. Microsoft Excel erkennt automatisch numerische Elemente (Daten) und zeigt, wenn Sie den Befehl

Gruppieren

aufrufen, ein Dialogfeld mit möglichen numerischen Gruppierungsoptionen an.

Datumsangaben

lassen sich nach bestimmten Zeiträumen zusammenfassen. Hier erkennt Microsoft Excel ebenfalls gültige Datums- und Zeitformate und bietet im Dialogfeld entsprechende Auswahlmöglichkeiten für die Darstellung an (

Abbildung 2.8

).

Eine Gruppierung aufheben

Auf dem gleichen Weg, wie Sie die manuellen Gruppen erstellt haben, lösen Sie diese auch wieder auf: Nach dem Markieren der Gruppenbezeichnung wählen Sie im Kontextmenü anstelle des Befehls Gruppieren den Befehl Gruppierung aufheben.

Alternativ können Sie auf der kontextsensitiven Registerkarte PivotTable-Analyse in der Befehlsgruppe Gruppieren den Befehl Gruppierung aufheben auswählen. Alternativ gibt es die Tastenkombination ++ (Abbildung 2.11).

Wichtig

Einem gruppierten Feld können Sie keine berechneten Elemente hinzufügen. Lösen Sie in diesem Fall die zuvor hergestellte Gruppierung wieder auf. Nach dem Einfügen des berechneten Elements können Sie die Elemente des Feldes erneut gruppieren.

(Anmerkung: Ein »berechnetes Element« wird für eine differenzierte Berechnung innerhalb einer PivotTable verwendet. Grundlage sind Dimensionselemente wie beispielsweise Ist-Betrag und Plan-Betrag. Aus diesen beiden (Dimensions-)Elementen wird dann durch Subtraktion beispielsweise ein berechnetes Element »Abweichung« ermittelt).

Abbildung 2.11:Befehle rund um die Gruppierung im Menüband, RegisterkartePivotTable-Analyse

Feldbeschriftungen in PivotTables und PivotCharts

Beim Aufbau einer PivotTable werden automatisch Überschriften eingetragen (siehe Abbildung 2.10, Zelle A3, Zeilenbeschriftungen). Sie können diese Überschrift ändern, indem Sie die Zelle A3 aktivieren und in der Bearbeitungszeile eine neue Überschrift eintragen.

Beim Ändern von Überschriften sollten Sie darauf achten, keinen vorhandenen Feldnamen zu benutzen. Für den Fall, dass der gewünschte neue Feldname bereits in Gebrauch ist, erhalten Sie folgende Fehlermeldung: »Der PivotTable-Feldname ist bereits vorhanden.«

Tipp

Allein das Anfügen eines Leerzeichens an den ursprünglichen Feldnamen wird als neue Überschrift in der PivotTable akzeptiert. Sie sollten jedoch im Umgang mit Leerzeichen an dieser Stelle vorsichtig sein, weil diese dann häufig dafür verantwortlich sind, dass in folgenden Arbeitsschritten die Daten nicht richtig bearbeitet werden, beispielsweise bei einer Gruppierung. Bei Beschriftungen von Zeilen und Spalten unterscheidet Microsoft Excel sehr wohl zwischen einem Eintrag mit bzw. ohne Leerzeichen am Ende, was zur Anzeige zusätzlicher Zeilenfelder führen kann. Es ist in diesem Zusammenhang sinnvoll, sich eigene Namenskonventionen zu überlegen: Es ist beispielsweise denkbar, die Feldnamen der Basisdaten in Standardschreibweise zu verwenden und für Überschriften einen Unterstrich oder Punkt voranzustellen.

So erweitern Sie die PivotTable und verändern die Feldanordnung

Der erste Entwurf einer Pivot-Tabelle kann jederzeit entsprechend den Anforderungen und Auswertekriterien verändert bzw. angepasst werden. Bei unseren Beispieldaten und der zuvor erzeugten PivotTable könnte eine sinnvolle Anordnung aus dem Tausch der Spaltenbeschriftung mit der Zeilenbeschriftung [1] und dem Hinzufügen des Feldes Arbeitsgebiet [2] ergeben (Abbildung 2.12).

In welchem Arbeitsgebiet wurde in den ersten vier Monaten des Jahres in Deutschland (DE) der größte Umsatz erzielt? Um diese Frage zu beantworten, ziehen Sie das Feld Arbeitsgebiet in den Layoutabschnitt Zeilen und können dann das Ergebnis unmittelbar ablesen (Abbildung 2.12, [2]).

Abbildung 2.12:Positionstausch der Zeilen- mit der Spaltenbeschriftung [1] und Hinzufügen des FeldesArbeitsgebiet[2]