Sie wollen mittels Excel 2016 herausfinden, wie viel Umsatz ein bestimmtes Unternehmen im letzten Monat erwirtschaftet hat oder wie viele Kunden durchschnittlich in den einzelnen Quartalen des letzten Jahres eine bestimmte Niederlassung aufsuchten? Für die Antwort stellen Sie die Daten in einer Liste zusammen. Am besten funktioniert dies mit Pivot-Tabellen in Excel. Mit diesen speziellen Tabellen lassen sich die Daten auch nach Kategorien sortieren oder Ergebnisse von Personen oder Quartalen miteinander vergleichen. Außerdem lassen sich alle Fragen, die mit „Wie viele“ oder „Wie viel“ anfangen, beantworten.
Was ist eine Pivot-Tabelle?
Eine Pivot-Tabelle ist ein Excel-Tool, mit dem Sie Daten zusammenfassen und analysieren können. Excel bündelt hierzu verschiedene Daten aus einer Tabelle, sortiert diese und generiert daraus die Pivot-Tabelle. Zusätzlich besitzen diese speziellen Tabellen verschiedene Filtermöglichkeiten, die das Analysieren erleichtern. Statt alle Daten in eine Tabelle aufzunehmen, reicht es aus nur die Daten zu erfassen, die für die Beantwortung der Frage relevant sind. Pivot-Tabellen sind flexibel. Sie können verschiedene Fragen beantworten. Hierfür müssen nur die Daten anders angeordnet werden.
Video-Kurs “Excel Formeln & Funktionen & Pivot-Tabellen Masterclass!” bei Udemy ansehen
Eine Pivot-Tabelle erstellen
Bevor Sie eine Pivot-Tabelle erstellen, müssen Sie die Frage formulieren, die Sie beantwortet haben wollen oder welche Informationen Sie aus Ihrer Datensammlung extrahiert haben möchten. Dieser Schritt bestimmt den Tabellen-Aufbau.
Betrachten wir die Daten in diesem Arbeitsblatt und die Frage: „Wie viel Umsatz hat unser Unternehmen in den einzelnen Quartalen 2016 erwirtschaftet?“
Um die Pivot-Tabelle zu erstellen, klicken Sie in irgendein Feld in der Daten-Liste, wählen die Registerkarte Einfügen und anschließend den Punkt PivotTable aus (ja, das heißt auch bei einer deutschsprachigen Excel-Version so). Automatisch markiert Excel den Bereich mit Daten und Überschriften. Falls der ausgewählte Bereich fehlerhaft sein sollte, können Sie diesen manuell korrigieren, indem Sie den markierten Bereich mit der Maus entsprechend anpassen. Am besten ist es die Pivot-Tabelle in ein neues Arbeitsblatt abzulegen. Hierzu wählen Sie aus dem Fenster PivotTable erstellen den Punkt Neues Arbeitsblatt aus dem unteren Bereich.
Diese Stelle kann zu Verwirrungen führen. Denn wenn Sie noch nie eine Pivot-Tabelle erstellt haben, wird Ihnen am Bildschirm nichts bekannt vorkommen. In Wirklichkeit ist es einfacher als es aussieht. Die PivotTable-Felder enthält, wie der Name schon sagt, die Felder aus der Liste. Sie müssen nur die benötigten Daten anklicken, aktivieren und die entsprechenden Werte erscheinen automatisch in der Pivot-Tabelle.
Falls Sie wissen möchten: Wie viel Umsatz die einzelnen Consultants in jedem Quartal 2016 erwirtschaftet hat, wählen Sie rechts aus der Liste der PivotTable-Felder die Felder aus, die Sie berücksichtigen wollen.
Excel erstellt automatisch eine Pivot-Tabelle, aus den ausgewählten Daten. Falls Ihnen die Anordnung der Daten nicht zusagt, können Sie diese einfach ändern.
Pivot-Tabellen formatieren
Für eine komfortablere Übersicht formatieren Sie die Werte der Pivottabelle. Dies geschieht, indem Sie mit der rechten Maustaste auf einen Wert klicken und aus dem Kontextmenü Zahlenformat wählen. Anschließend können Sie die Werte auf unterschiedliche Weise formatieren. Das Layout der Tabelle lässt sich ebenfalls anpassen.
Mit einer Pivot-Tabelle komplexe Fragen beantworten
Mit einer einmal erstellten Pivot-Tabelle lassen sich verschiedene Fragen beantworten, zum Beispiel folgende: „Wie viel hat Davidson für seine Entwicklungen in jedem Quartal verdient?“ Für die Antwort klicken Sie in die Tabelle, um die PivotTable-Felder erneut anzeigen zu lassen. Hier wählen Sie zum Beispiel Consultant aus und ordnen die Werte dem Feld Zeilenbeschriftungen zu. Des Weiteren lassen sich die Ergebnisse filtern, um bestimmte Daten auszublenden. Hierzu klicken Sie auf das Pfeilsymbol neben dem jeweiligen Feld und wählen die entsprechenden Berater aus.
Diesen Vorgang wiederholen Sie so lange bis alle Reihen entsprechend gefiltert sind. In diesem Beispiel wählen Sie als Berater Jens und als Kategorie Standort aus. Anschließend sehen Sie; wie viel Jens pro Quartal für Entwicklung verdient hat. Auf diese Weise können Sie die erstellte Pivot-Tabelle immer wieder verwenden.
Pivot-Tabellen komfortabel auswerten
In Excel 2016 gibt es die Funktion Datenschnitt. Mit dieser können Sie einfach und schnell die Daten komfortabel auswerten, denn die Funktion erstellt Filter-Boxen. Mit diesen lassen sich einzelne oder mehrere Elemente anzeigen. Das Feature befindet sich im Menü Einfügen. Um die Filter-Boxen zu erstellen, rufen Sie die Funktion auf und wählen die betreffenden Elemente für die Filterung aus. Um die Daten zu filtern, klicken Sie das Element, nach dem Sie suchen möchten, an.
Falls Sie mehrere Elemente auswählen möchten, müssen sie die Strg-Taste zusätzlich gedrückt halten. Anhand des Filtersymbols in der rechten oberen Ecke des Datenschnitt-Fensters erkennen Sie, ob ein Filter gesetzt ist. Ein aktives Symbol weist auf einen eingeschalteten Filter hin. Dieser lässt sich ausschalten, indem Sie auf das Filtersymbol klicken. Die Filter-Boxen lassen sich mittels Kontextmenü individuell anpassen.
Pivot-Tabellen aktualisieren
Wenn Sie Daten in der Pivot-Tabelle anders anordnen, wirkt sich das nicht auf die Ursprungsdaten aus. Diese bleiben unverändert. Ebenfalls wirken sich Änderungen in der Ursprungsliste nicht auf die Pivot-Tabelle aus. Nachdem Sie die Daten in der ursprünglichen Liste verändert haben, erfolgt daher keine automatische Aktualisierung der Pivot-Tabelle. Dies müssen Sie manuell erledigen, indem Sie mit der rechten Maustaste auf die Pivot-Tabelle klicken und Aktualisieren auswählen.
Diagramme aus Pivot-Tabellen erstellen
Diagramme, die auf Pivot-Tabellen basieren, lassen sich einfach erstellen. Um Charts zu generieren, klicken Sie in die Pivot-Tabelle und wählen aus dem Menü Einfügen den Punkt PivotChart aus PivotTable . Anschließend müssen Sie die Elemente auswählen, die im Diagramm angezeigt werden sollen. Excel erstellt im Anschluss automatisch ein entsprechendes Chart mit den Daten aus der Pivot-Tabelle. Falls Sie später die Daten in der Pivot-Tabelle via Datenschnitt aktualisieren oder das Layout ändern, passt sich das Diagramm automatisch an.
Neuerungen in Excel 2016
Eine der Neuerungen in Office 2016 sind die erweiterten Diagrammfunktionen in Excel 2016. Die neue Version bietet bessere Visualisierungen, vor allem im Bereich Business Intelligence und Big Data. Die neuen Diagramme sind: * Wasserfall-Diagramm * Histogramm * Pareto-Diagramm * Box-Whiskers Plot, auch Kastendiagramm genannt * Treemap und Sunburst Diagramm
Das Wasserfall-Diagramm basiert auf einem Initialwert, der durch eine Serie von Werten erhöht oder gesenkt wird. Dadurch entsteht jeweils ein Endwert. Bisher konnten diese Diagramme nur über Umwege erstellt werden. Die Diagramme werden häufig im Business Intelligence-Bereich eingesetzt, zum Beispiel zu einer Unternehmensbewertung als EBITDA-Bridge, dem Gewinn vor Steuern, Zinsen, Abschreibungen und Amortisationen. Das ist auch einer der Gründe, warum die Diagramme als Brücken-Diagramme bezeichnet werden. Die Diagramme werden häufig von der Finanzbuchhaltung verwendet, um Gewinn und Verlust zu visualisieren.
Histogramme werden vor allem in der Statistik verwendet, um Bandbreiten und Frequenzen innerhalb einer Verteilung von Daten zu visualisieren. Histogramme bieten die Möglichkeit Diagramme aus der Statistik in Excel zu visualisieren. Haben Sie eine Zahlenreihe vorliegen, können Sie diese über ein Histogramm-Diagramm darstellen. Dazu markieren Sie wieder Ihre Daten, und wählen auf der Registerkarte Einfügen den Diagramm-Typ Histogramm aus.
Bei Pareto-Diagrammen werden die Daten als Säulendiagramm dargestellt und nach der Größe geordnet. Auch diese Diagramme werden häufiger im Bereich der Statistik verwendet. Auch zur Fehleranalyse oder Entscheidungsfindung werden diese Diagramme verwendet. Pareto-Diagramme sind zum Beispiel in der Lage aus vielen Ursachen eines Problems die wahrscheinlichsten Ursachen herauszustellen. Die Wichtigkeit der jeweiligen Ursache ist im Diagramm zu sehen. Der größte Wert liegt normalerweise auf der linken Seite, der kleinste Wert rechts.
Ein Box-Whisker-Plot soll dabei helfen Entscheidern einen Überblick zu vermitteln, in welchem Bereich Daten liegen und wie sich die Daten im Bereich verteilen. Minimum, Maximum, unteres und oberes Quartil sowie Median, Maximum und Spannweite lassen sich direkt aus dem Diagramm auslesen.
Auch Box-Whiskers-Plot-Diagramme sind vor allem im Business Intelligence-Bereich nutzbar. Bei diesen Kastendiagrammen werden fünf Kennwerte in Relation zueinander gesetzt und im Diagramm erfasst. Die Whiskers zeigen dabei meistens das Minimum und Maximum der Werte an. Die Diagramme werden häufig auch für Finanzdaten verwendet. Um Box-Whiskers-Plot zu integrieren, markieren Sie Ihre Daten und wählen auf der Registerkarte Einfügen den Bereich Statistikdiagramme einfügen, wie bei Histogrammen und Pareto-Diagrammen. Als Diagramm wählen Sie Kastengrafik aus.
Mit Treemaps können Sie eine hierarchische Ansicht Ihrer Daten anzeigen lassen. Sinnvoll sind diese Charts zum Beispiel, wenn Sie visualisieren wollen, was die besten verkauften Produkte in Ihrem Unternehmen sind. Außerdem lassen sich verschiedene Kategorien festlegen, die auch farblich unterscheidbar sind. Geht es um die Visualisierung von Proportionen in Daten aller Art, sind Treemaps enorm sinnvoll. Markieren Sie zum Einfügen Ihre Daten und wählen Sie auf der Registerkarte die Option Treemap.
Sunburst-Charts und Treemaps sind sich sehr ähnlich. Sunburst-Chart können relative Größen auf hierarchischen Ebenen visualisieren. Die Hierarchie wird mit einem Ring oder Kreis dargestellt, der weitere Ringe und Kreise enthält. Sie können auch mehrere Kategorien mit einbinden. Sinnvoll ist ein solches Sunburst-Diagramm zum Beispiel, wenn Sie die Umsätze eines Jahres in Bezug auf Monate, Quartale, Monate und einzelne Wochen untergliedern wollen.
Pivot-Tabellen und die Drill-Down-Funktion
Pivot-Tabellen besitzen eine Drill-Down-Funktion. Wenn Sie wissen möchten, aus welchen Ursprungswerten sich ein bestimmter Zahlenwert zusammensetzt, müssen Sie ihn doppelklicken. Daraufhin generiert Excel ein neues Sheet in der Arbeitsmappe, in dem alle beteiligten Daten aufgelistet werden. Da Excel bei einem Doppelklick jedes Mal ein neues Arbeitsblatt generiert, wird die Arbeitsmappe schnell unübersichtlich. Deshalb sollten Sie nicht mehr benötigte Sheets entfernen, indem Sie mit der rechten Maustaste auf die Registerkarte des entsprechenden Arbeitsblattes klicken und den Punkt Löschen auswählen.
Pivot-Tabellen anders berechnen
Excel summiert in einer Pivot-Tabelle standardmäßig die Werte. Wer stattdessen die Felder nach Anzahl, Mittelwert , Maximum oder Minimum zusammengefasst haben möchte, kann dies einstellen. Hierzu rufen Sie das Kontextmenü der jeweiligen Spalte auf und wählen den Punkt Wertfeldeinstellungen aus. Danach geben Sie den Berechnungstyp für die Zusammenfassung an.
Zehn Excel-Tipps für Office-Profis