Erstellen Sie Ihre erste Pivot-Tabelle und addieren Sie Deckungsbeiträge

Pivot-Übungen zum Mitmachen: Beispieldatenbank

Die Beispieldatenbank in den Download-Dateien Pivot-Übungen.xlsx und Pivot-Lösungen.xlsx stellt die weltweiten Vertriebsaktivitäten der Verkäufer pro Tag dar und hat die in 1 gezeigte Struktur: Pro Tag werden die Verkäufe und die dazugehörigen Einkäufe der einzelnen Vertriebsmitarbeiter nach Produkt und Absatzgebiet aufgeführt. In der Spalte Klasse wird ein Verkauf als Umsatz und ein Einkauf als variable Kosten aufgeführt. Bitte beachten Sie, dass in der Wert-Spalte die Erlöse positiv und die dazugehörigen Einkäufe negativ dargestellt werden.

Übung 1: Die Pivot-Tabelle einfügen und die erste Auswertung starten

Die Beispieldatenbank soll nach der Frage: „Wie viel Deckungsbeitrag wurde pro Absatzgebiet insgesamt erzielt?“, ausgewertet werden. Der Deckungsbeitrag berechnet sich aus Umsatz minus variable Kosten. Wechseln Sie in der Beispieldatei Pivot-Übungen.xlsx auf das Tabellenblatt Übung 1. Die fertige Lösung steht Ihnen in der Lösungsdatei Pivot-Lösungen.xlsx im Tabellenblatt Lösung 1 zur Verfügung.

So erstellen Sie die Pivot-Tabelle:

1. Aktivieren Sie im Tabellenblatt Übung 1 die Zelle A3. An dieser Stelle wollen wir die Pivot-Tabelle platzieren. Klicken Sie im Menüband auf die Registerkarte Einfügen – Befehlsgruppe Tabelle – Befehl PivotTable.

2. Teilen Sie im Dialogfeld PivotTable erstellen Excel den Ort der Datenquelle mit. In unserem Beispiel befindet sie sich in der gleichen Arbeitsmappe im Tabellenblatt Daten und dort im Bereich A1:H64257. Die Schreibmarke steht bereits im Eingabefeld Tabelle/Bereich. Klicken Sie auf die Tabelle Daten und betätigen Sie die Tastenkombination S + a . Die gesamte Tabelle wird im Eingabefeld angezeigt.

3. Stellen Sie den gewünschten Zielort der Pivot-Tabelle ein: Da Sie die Zelle A3 bereits markiert haben, ist die Option vorhandenes Arbeitsblatt aktiviert und der gewünschte Standort wird in Quelle übernommen.

   

4. Bestätigen Sie Ihre Einstellungen mit einem Klick auf OK.

5. Excel erstellt automatisch eine leere Pivot-Tabelle. Um die Daten auszuwerten, müssen Sie die Spalten – die Datenfelder – der Datenquelle festlegen, die ausgewertet werden sollen. Dies stellen Sie über die Feldliste im rechten Bereich des Tabellenblattes ein. In dieser Pivot-Table-Feldliste sehen Sie alle Spalten der Datenquelle als Felder. Darunter wird aufgelistet, welche Felder als Berichtsfilter, Spaltenbeschriftungen, Zeilenbeschriftungen und Werte angelegt sind. Bisher sind noch keine Felder ausgewählt, deshalb sind die Feldlisten leer. Ziehen Sie mit der linken Maustaste das Feld Absatzgebiet in den Bereich Zeilen.

   Ihre Pivot-Tabelle im linken Bereich aktualisiert sich automatisch. Es werden alle in der Datenbank vorkommenden Namen der Absatzgebiete untereinander aufgelistet.

6. Berechnen Sie den Deckungsbeitrag. Aus der Struktur der Datenbank ist ersichtlich, dass Erlöse grundsätzlich als positive Zahlen und die dazugehörigen variablen Kosten als negative Zahlen im Datenfeld Wert enthalten sind. Sie erhalten daher den Deckungsbeitrag, indem Sie das Datenfeld Wert pro Verkaufsgebiet addieren. Ziehen Sie das Feld Wert in den Bereich Werte.

Am Ziel: Sie haben blitzschnell alle Datensätze analysiert und den Deckungsbeitrag pro Absatzgebiet berechnet. Pro Verkaufsgebiet werden die Erlöse und variablen Kosten über die Aggregatsfunktion der Pivot-Tabelle addiert – eigentlich subtrahiert, da die Kosten als negative Zahl in der Liste vorhanden sind.

Darstellung in der Pivot-Tabelle ändern

Standardmäßig wurden die Daten in unserem Beispiel im Bereich Werte mit der Funktion SUMME aggregiert, auch zu sehen an der Überschrift Summe von Wert. Excel kann natürlich viel mehr: Sie können im Werte-Bereich beispielsweise die Anzahl von Elementen, einen höchsten oder niedrigsten Wert sowie Standardabweichungen ausgeben.

Übung 2: Funktion in der Pivot-Tabelle ändern

Sie möchten die Datenanalyse verfeinern und wissen, wie viele Verkäufe und Einkäufe pro Absatzgebiet vorliegen. Aktivieren Sie das Tabellenblatt Übung 2. Hier finden Sie die vorhergehende Pivot-Tabelle mit der SUMME-Funktion. Klicken Sie mit der rechten Maustaste auf eine Zahl im Wertebereich der Pivot-Tabelle und aktivieren Sie Wertfeldeinstellungen. Wählen Sie im Dialogfeld Wertfeldeinstellungen die Funktion Anzahl aus, um die Anzahl der Datensätze pro Absatzgebiet zu ermitteln. Bestätigen Sie Ihre Angaben mit einem Klick auf OK.

Die Pivot-Tabelle passt sich sofort an und zeigt die Anzahl der Verkäufe und Einkäufe pro Verkaufsgebiet.