Wie erstelle ich eine Excel-Pivot-Tabelle inkl. Diagrammen und Datenschnitten (Filtern)?

    Concept Office bietet die Möglichkeit, Recherchen zeitgesteuert oder manuell nach Excel auszugeben. Dadurch können beispielsweise wöchentlich Umsatzangaben in ein Excel-Template ausgegeben und via E-Mail versendet werden.

    Im folgenden Beitrag wird beschrieben, wie eine solche Vorlage aufgebaut wird. Dabei wird aus einer Umsatzliste in Excel eine Pivot-Tabelle erstellt. Die Pivot-Tabelle wird auf einem eigenen Arbeitsblatt dargestellt, automatisch beim Öffnen der Datei aktualisiert und anschließend als Grundlage für ein Pivot-Diagramm sowie ein Umsatz-Dashboard verwendet. 


    Beispiel eines fertigen Pivot-Dashboards, inkl. Filtern

    Voraussetzungen

    Für die Einrichtung werden folgende Voraussetzungen benötigt:

    • Die Recherche ist in Concept Office vorhanden.

    • Die Pfade für die Excel-Vorlage und die Zieldatei sind in der Recherche hinterlegt.

    • Das Excel-Blatt „Roh-Umsatz“ ist als Ausgabeblatt definiert.


    Die Excel-Vorlage kann über die Lupe im gleichnamigen Feld der Recherche geöffnet und bearbeitet werden.

    Ausgangssituation

    Die Quelldaten der Recherche werden in den Reiter „Roh-Umsatz“ ausgegeben. Die Tabelle enthält unter anderem folgende Spalten:

    • Lieferantenname

    • Umsatz

    • Umsatzdatum

    • Geschaeftsbereich

    • Warengruppe

    • Verkaufsteam

    • Verkaeufer

    • Sachbearbeiter

    Für das Beispiel wird die Pivot-Tabelle so aufgebaut, dass der Lieferantenname als Zeilenbeschriftung verwendet wird und der Umsatz als Summe ausgewertet wird.

    Neues Arbeitsblatt für die Pivot-Tabelle einfügen

    1. Öffnen Sie die Excel-Datei mit dem Arbeitsblatt „Roh-Umsatz“.

    2. Klicken Sie unten in der Arbeitsblatt-Leiste auf das Plus-Symbol, um ein neues Arbeitsblatt einzufügen.

    3. Benennen Sie das neue Arbeitsblatt in „Pivot-Daten“ um.

      • Klicken Sie dazu mit der rechten Maustaste auf den Namen des neuen Arbeitsblatts.

      • Wählen Sie Umbenennen.

      • Geben Sie Pivot-Daten ein.

      • Bestätigen Sie mit Enter.

    Das Arbeitsblatt „Pivot-Daten“ dient dazu, die Pivot-Tabelle übersichtlich getrennt von den Rohdaten darzustellen.

    Pivot-Tabelle auf Basis der Rohdaten anlegen

    1. Wechseln Sie in das Arbeitsblatt „Roh-Umsatz“.

    2. Klicken Sie auf die Spalte A (die ganze Spalte markiert sich), scrollen Sie nach rechts bis zu letzten Spalte, halten Sie die Shift-Taste gedrückt und wählen die letzte Spalte aus. Es sollten nun alle Inhalte markiert sein.

    3. Öffnen Sie im Menüband den Reiter Einfügen.

    4. Klicken Sie auf PivotTable.

    5. Prüfen Sie im Fenster PivotTable aus Tabelle oder Bereich, ob der vollständige Datenbereich korrekt erkannt wurde.

    6. Wählen Sie als Speicherort Vorhandenes Arbeitsblatt aus.

    7. Klicken Sie in das Feld für den Speicherort.

    8. Wechseln Sie in das Arbeitsblatt „Pivot-Daten“.

    9. Klicken Sie dort in die Zelle, an der die Pivot-Tabelle beginnen soll, zum Beispiel A1.

    10. Bestätigen Sie mit OK.

    Excel erstellt nun eine Pivot-Tabelle im Arbeitsblatt „Pivot-Daten“.

    Pivot-Tabelle aufbauen

    Nach dem Erstellen der Pivot-Tabelle wird rechts der Bereich PivotTable-Felder angezeigt.

    1. Ziehen Sie das Feld Lieferantenname in den Bereich Zeilen.

    2. Ziehen Sie das Feld Umsatz in den Bereich Werte.

    3. Prüfen Sie, ob im Bereich Werte die Einstellung Summe von Umsatz angezeigt wird.

    Wenn dort nicht Summe von Umsatz steht, sondern zum Beispiel Anzahl von Umsatz, ändern Sie die Einstellung wie folgt:

    1. Klicken Sie im Bereich Werte auf den Pfeil neben dem Feld Umsatz.

    2. Wählen Sie Wertfeldeinstellungen.

    3. Wählen Sie Summe aus.

    4. Bestätigen Sie mit OK.

    Die Pivot-Tabelle zeigt nun je Lieferant den aufsummierten Umsatz an:

    Zahlenformat für Umsatz anpassen

    Damit die Umsatzwerte besser lesbar sind, sollte das Zahlenformat angepasst werden.

    1. Klicken Sie in der Pivot-Tabelle auf einen Umsatzwert.

    2. Klicken Sie mit der rechten Maustaste auf den Wert.

    3. Wählen Sie Zahlenformat.

    4. Wählen Sie ein geeignetes Format aus, zum Beispiel Währung oder Zahl.

    5. Legen Sie die gewünschte Anzahl an Dezimalstellen fest.

    6. Bestätigen Sie mit OK.

    Die Formatierung wird dadurch für das gesamte Umsatz-Wertefeld übernommen.

    Pivot-Tabelle beim Öffnen der Excel-Datei automatisch aktualisieren

    Damit die Pivot-Tabelle beim Öffnen der Datei automatisch aktualisiert wird, muss die entsprechende Option in den PivotTable-Optionen aktiviert werden.

    1. Klicken Sie in die Pivot-Tabelle im Arbeitsblatt „Pivot-Daten“.

    2. Öffnen Sie im Menüband den Reiter PivotTable-Analyse.

    3. Klicken Sie auf Optionen.

    4. Wechseln Sie in den Reiter Daten.

    5. Aktivieren Sie die Option "Aktualisieren beim Öffnen der Datei".

    6. Bestätigen Sie mit OK.

    Beim nächsten Öffnen / Export der Excel-Datei aktualisiert Excel die Pivot-Tabelle automatisch auf Basis der hinterlegten Quelldaten.

    Pivot-Diagramm anlegen

    Für eine grafische Darstellung der Pivot-Auswertung wird ein Pivot-Diagramm erstellt.

    1. Klicken Sie in die Pivot-Tabelle im Arbeitsblatt „Pivot-Daten“.

    2. Öffnen Sie im Menüband den Reiter PivotTable-Analyse.

    3. Klicken Sie auf PivotChart oder Pivot-Diagramm.

    4. Wählen Sie einen passenden Diagrammtyp aus, zum Beispiel:

      • Säulendiagramm

      • Balkendiagramm

      • Liniendiagramm

    5. Bestätigen Sie mit OK.

    Excel erstellt nun ein Pivot-Diagramm auf Basis der Pivot-Tabelle.

    Datenschnitte hinzufügen

    Datenschnitte ermöglichen eine einfache Filterung der Pivot-Tabelle und des Pivot-Diagramms.

    1. Klicken Sie in die Pivot-Tabelle oder in das Pivot-Diagramm.

    2. Öffnen Sie im Menüband den Reiter PivotTable-Analyse.

    3. Klicken Sie auf Datenschnitt einfügen.

    4. Wählen Sie die Felder aus, nach denen gefiltert werden soll.

    Geeignete Felder für Datenschnitte können zum Beispiel sein:

    • Geschaeftsbereich

    • Verkaufsteam

    • Verkaeufer

    • Sachbearbeiter

    • Lieferantenname

    • Warengruppe

    • Warenuntergruppe

    1. Bestätigen Sie mit OK.

    2. Verschieben Sie die eingefügten Datenschnitte in das Arbeitsblatt „Umsatz-Dashboard“, sofern sie dort dargestellt werden sollen.

    3. Ordnen Sie die Datenschnitte übersichtlich neben oder oberhalb des Diagramms an.

    Durch Anklicken einzelner Werte in einem Datenschnitt wird die Pivot-Auswertung entsprechend gefiltert.

    Datenschnitte mit weiteren Pivot-Tabellen verknüpfen

    Wenn mehrere Pivot-Tabellen aus derselben Datenquelle erstellt wurden, können Datenschnitte mit mehreren Pivot-Tabellen verbunden werden. Dadurch steuert ein Datenschnitt mehrere Auswertungen gleichzeitig.

    1. Klicken Sie den gewünschten Datenschnitt an.

    2. Öffnen Sie im Menüband den Reiter Datenschnitt.

    3. Klicken Sie auf Berichtsverbindungen oder PivotTable-Verbindungen.

    4. Aktivieren Sie die Pivot-Tabellen, die mit diesem Datenschnitt gesteuert werden sollen.

    5. Bestätigen Sie mit OK.

    Der Datenschnitt filtert nun alle ausgewählten Pivot-Tabellen gleichzeitig. Wichtig ist, dass die Pivot-Tabellen auf derselben Datenquelle beziehungsweise demselben Pivot-Cache basieren. Wenn eine Pivot-Tabelle aus einer anderen Datenquelle erstellt wurde, kann es sein, dass sie nicht mit demselben Datenschnitt verbunden werden kann.

    Weitere sinnvolle Pivot-Auswertungen

    Neben der Auswertung nach Lieferantenname können weitere Pivot-Tabellen angelegt werden, zum Beispiel:

    • Umsatz nach Kundenname

    • Umsatz nach Warengruppe

    • Umsatz nach Verkaufsteam

    • Umsatz nach Verkaeufer

    • Umsatz und Rohgewinn nach Umsatzdatum

    Für jede zusätzliche Pivot-Tabelle sollte geprüft werden, ob sie im Arbeitsblatt „Pivot-Daten“ ausreichend Platz hat oder ob ein weiteres Arbeitsblatt sinnvoller ist.

    Wichtige Hinweise

    Pivot-Tabellen dürfen sich nicht überschneiden

    Pivot-Tabellen können sich bei Aktualisierungen vergrößern oder verkleinern. Wenn mehrere Pivot-Tabellen zu nah nebeneinander oder untereinander platziert werden, kann es beim Aktualisieren zu Überschneidungen kommen. Achten Sie daher darauf, ausreichend Abstand zwischen mehreren Pivot-Tabellen zu lassen. Wenn Excel meldet, dass sich ein PivotTable-Bericht nicht mit einem anderen PivotTable-Bericht überschneiden darf, muss eine der Pivot-Tabellen verschoben oder auf ein eigenes Arbeitsblatt gelegt werden.

    Quelldaten sollten eine saubere Tabellenstruktur haben

    Die Quelldaten im Arbeitsblatt „Roh-Umsatz“ sollten folgende Anforderungen erfüllen:

    • Jede Spalte hat eine eindeutige Überschrift.

    • Es gibt keine komplett leeren Spalten innerhalb des Datenbereichs.

    • Es gibt keine komplett leeren Zeilen innerhalb des Datenbereichs.

    • Gleichartige Werte werden einheitlich geschrieben.

    • Zahlenwerte wie Umsatz, Menge, Rohgewinn und EinkaufsPreis sind als Zahlen formatiert.

    • Datumswerte wie Umsatzdatum sind als Datum formatiert.

    Eine saubere Datenstruktur sorgt dafür, dass Excel den Datenbereich korrekt erkennt und die Pivot-Tabelle zuverlässig funktioniert.

    Änderungen an den Rohdaten müssen aktualisiert werden

    Wenn sich Werte im Arbeitsblatt „Roh-Umsatz“ ändern, muss die Pivot-Tabelle aktualisiert werden.

    Die Aktualisierung kann manuell durchgeführt werden:

    1. Klicken Sie in die Pivot-Tabelle.

    2. Öffnen Sie den Reiter PivotTable-Analyse.

    3. Klicken Sie auf Aktualisieren.

    Alternativ wird die Pivot-Tabelle beim Öffnen der Datei automatisch aktualisiert, wenn die Option Daten beim Öffnen der Datei aktualisieren aktiviert wurde.

    Neue Zeilen müssen im Datenbereich enthalten sein

    Wenn neue Umsatzzeilen unterhalb der bisherigen Daten eingefügt werden, müssen diese im Datenbereich der Pivot-Tabelle enthalten sein.

    Empfohlen wird, die Rohdaten als Excel-Tabelle zu formatieren:

    1. Klicken Sie in die Rohdaten im Arbeitsblatt „Roh-Umsatz“.

    2. Drücken Sie Strg + T.

    3. Prüfen Sie, ob der gesamte Datenbereich markiert ist.

    4. Aktivieren Sie die Option Tabelle hat Überschriften.

    5. Bestätigen Sie mit OK.

    Wenn die Daten als Excel-Tabelle formatiert sind, erweitert sich der Datenbereich automatisch, sobald neue Zeilen ergänzt werden.

    Feldnamen sollten nicht nachträglich geändert werden

    Wenn Spaltenüberschriften in den Rohdaten nachträglich geändert werden, kann es passieren, dass Felder in der Pivot-Tabelle nicht mehr korrekt gefunden werden.

    Ändern Sie Feldnamen daher nur bewusst und prüfen Sie anschließend die Pivot-Tabelle, das Pivot-Diagramm und die Datenschnitte.

    Datenschnitte benötigen passende Daten

    Ein Datenschnitt kann nur Werte anzeigen, die in der Datenquelle vorhanden sind. Wenn ein Feld leer oder uneinheitlich gepflegt ist, wirkt sich das direkt auf die Filtermöglichkeiten aus.

    Beispiel: Wenn im Feld Verkaeufer unterschiedliche Schreibweisen für dieselbe Person verwendet werden, erscheinen diese Schreibweisen im Datenschnitt als getrennte Einträge.

    Ergebnis

    Nach Abschluss der Schritte besteht die Excel-Datei aus mindestens drei relevanten Arbeitsblättern:

    • Roh-Umsatz: enthält die ursprünglichen Umsatzdaten

    • Pivot-Daten: enthält die Pivot-Tabelle auf Basis der Rohdaten

    • Umsatz-Dashboard: enthält das Pivot-Diagramm und die Datenschnitte

    Die Pivot-Tabelle wertet den Umsatz je Lieferant aus. Das Pivot-Diagramm stellt diese Auswertung grafisch dar. Über Datenschnitte kann die Ansicht flexibel gefiltert werden, zum Beispiel nach Geschäftsbereich, Verkaufsteam, Verkäufer oder Warengruppe.

    Veröffentlicht