Excel-Datenanalyse - PivotTables

Eine Datenanalyse für einen großen Datensatz ist häufig erforderlich und wichtig. Dabei werden die Daten zusammengefasst, die erforderlichen Werte ermittelt und die Ergebnisse präsentiert.

Excel bietet PivotTable, mit dem Sie Tausende von Datenwerten einfach und schnell zusammenfassen können, um die erforderlichen Ergebnisse zu erzielen.

Betrachten Sie die folgende Tabelle mit Verkaufsdaten. Aus diesen Daten müssen Sie möglicherweise die Gesamtverkaufsregion, den Monat oder den Verkäufer zusammenfassen. Die einfache Möglichkeit, diese Aufgaben zu erledigen, besteht darin, eine PivotTable zu erstellen, die Sie dynamisch ändern können, um die Ergebnisse nach Ihren Wünschen zusammenzufassen.

PivotTable erstellen

Stellen Sie zum Erstellen von PivotTables sicher, dass die erste Zeile Überschriften enthält.

• Klicken Sie auf die Tabelle.
• Klicken Sie auf der Multifunktionsleiste auf die Registerkarte EINFÜGEN.
• Klicken Sie in der Gruppe Tabellen auf PivotTable. Das Dialogfeld PivotTable wird angezeigt.

Wie Sie im Dialogfeld sehen können, können Sie entweder eine Tabelle oder einen Bereich aus der aktuellen Arbeitsmappe oder eine externe Datenquelle verwenden.

• Geben Sie im Feld Tabelle / Bereich den Tabellennamen ein.
• Klicken Sie auf Neues Arbeitsblatt, um Excel mitzuteilen, wo die PivotTable aufbewahrt werden soll.
• OK klicken.

Eine leere PivotTable- und eine PivotTable-Feldliste werden angezeigt.

Empfohlene PivotTables

Wenn Sie PivotTables noch nicht kennen oder nicht wissen, welche Felder Sie aus den Daten auswählen sollen, können Sie die verwenden Recommended PivotTables das Excel bietet.

• Klicken Sie auf die Datentabelle.

• Klicken Sie auf die Registerkarte EINFÜGEN.

• Klicken Sie in der Gruppe Tabellen auf Empfohlene PivotTables. Das Dialogfeld Empfohlene PivotTables wird angezeigt.

Im empfohlenen Dialogfeld PivotTables werden die möglichen benutzerdefinierten PivotTables angezeigt, die Ihren Daten entsprechen.

• Klicken Sie auf jede der PivotTable-Optionen, um die Vorschau auf der rechten Seite anzuzeigen.
• Klicken Sie auf die PivotTable-Summe des Bestellbetrags nach Verkäufer und Monat.

OK klicken. Die ausgewählte PivotTable wird in einem neuen Arbeitsblatt angezeigt. Sie können die PivotTable-Felder beobachten, die in der Liste der PivotTable-Felder ausgewählt wurden.

PivotTable-Felder

Die Überschriften in Ihrer Datentabelle werden als Felder in der PivotTable angezeigt.

Sie können sie auswählen / abwählen, um Ihre PivotTable sofort so zu ändern, dass nur die gewünschten Informationen und auf die von Ihnen gewünschte Weise angezeigt werden. Wenn Sie beispielsweise die Kontoinformationen anstelle der Bestellbetragsinformationen anzeigen möchten, deaktivieren Sie die Option Bestellbetrag und wählen Sie Konto.

PivotTable-Bereiche

Sie können das Layout Ihrer PivotTable sogar sofort ändern. Sie können die PivotTable-Bereiche verwenden, um dies zu erreichen.

In PivotTable-Bereichen können Sie Folgendes auswählen:

• Welche Felder sollen als Zeilen angezeigt werden?
• Welche Felder sollen als Spalten angezeigt werden?
• So fassen Sie Ihre Daten zusammen
• Filter für eines der Felder
• Wann müssen Sie Ihr PivotTable-Layout aktualisieren?
  • Sie können es sofort aktualisieren, wenn Sie die Felder über Bereiche ziehen, oder
  • Sie können das Update verschieben und nur dann aktualisieren, wenn Sie auf UPDATE klicken

Ein sofortiges Update hilft Ihnen, mit den verschiedenen Layouts herumzuspielen und dasjenige auszuwählen, das Ihren Berichtsanforderungen entspricht.

Sie können die Felder einfach über diese Bereiche ziehen und dabei das PivotTable-Layout beobachten.

Verschachteln in der PivotTable

Wenn Sie in einem der Bereiche mehr als ein Feld haben, erfolgt die Verschachtelung in der Reihenfolge, in der Sie die Felder in diesem Bereich platzieren. Sie können die Reihenfolge ändern, indem Sie die Felder ziehen und beobachten, wie sich die Verschachtelung ändert. In den obigen Layoutoptionen können Sie dies beobachten

• Monate sind in Spalten.
• Region und Verkäufer in Reihen in dieser Reihenfolge. dh Verkäuferwerte sind unter Regionswerten verschachtelt.
• Die Zusammenfassung erfolgt nach der Summe der Bestellmenge.
• Es werden keine Filter ausgewählt.

Die resultierende PivotTable lautet wie folgt:

Klicken Sie in den PivotTable-Bereichen in Zeilen auf Region und ziehen Sie sie unter den Verkäufer, sodass sie wie folgt aussieht:

Die Verschachtelungsreihenfolge ändert sich und die resultierende PivotTable lautet wie folgt:

Note- Sie können deutlich beobachten, dass das Layout mit der Verschachtelungsreihenfolge - Region und dann Verkäufer einen besseren und kompakteren Bericht liefert als das mit der Verschachtelungsreihenfolge - Verkäufer und dann Region. Wenn der Verkäufer mehr als einen Bereich repräsentiert und Sie die Verkäufe nach Verkäufer zusammenfassen müssen, wäre das zweite Layout eine bessere Option gewesen.

Filter

Sie können einem der Felder einen Filter zuweisen, damit Sie die PivotTable basierend auf den Werten dieses Felds dynamisch ändern können.

Ziehen Sie den Bereich von den Zeilen in die Filter in den PivotTable-Bereichen.

Der Filter mit der Bezeichnung "Region" wird über der PivotTable angezeigt (falls Sie keine leeren Zeilen über Ihrer PivotTable haben, wird PivotTable nach unten gedrückt, um Platz für den Filter zu schaffen.

Sie können das sehen -

• Verkäuferwerte werden in Zeilen angezeigt.
• Monatswerte werden in Spalten angezeigt.
• Der Regionsfilter wird oben mit der Standardeinstellung ALL angezeigt.
• Der zusammenfassende Wert ist die Summe des Bestellbetrags
  • Summe des Bestellbetrags Der Verkäufer wird in der Spalte Gesamtsumme angezeigt
  • Summe der Bestellmenge Monatlich wird in der Zeile Gesamtsumme angezeigt

Klicken Sie auf den Pfeil im Feld rechts neben dem Filterbereich. Eine Dropdown-Liste mit den Werten des Feldbereichs wird angezeigt.

• Aktivieren Sie die Option Select Multiple Items. Für alle Werte werden Kontrollkästchen angezeigt.
• Wählen Sie Süd und West aus, deaktivieren Sie die anderen Werte und klicken Sie auf OK.

Die Daten, die sich nur auf die Regionen Süd und West beziehen, werden wie im folgenden Screenshot dargestellt zusammengefasst.

Sie können dies neben dem Filterbereich sehen. Multiple Itemswird angezeigt und zeigt an, dass Sie mehr als ein Element ausgewählt haben. Wie viele Elemente und / oder welche Elemente ausgewählt sind, ist aus dem angezeigten Bericht nicht bekannt. In einem solchen Fall ist die Verwendung von Slicern eine bessere Option zum Filtern.

Schneidemaschinen

Sie können Slicer verwenden, um eine bessere Übersicht darüber zu erhalten, nach welchen Elementen die Daten gefiltert wurden.

• Klicken Sie auf der Multifunktionsleiste unter PIVOTTABLE TOOLS auf ANALYZE.

• Klicken Sie in der Gruppe Filter auf Slicer einfügen. Das Feld Slicer einfügen wird angezeigt. Es enthält alle Felder aus Ihren Daten.

• Wählen Sie die Felder Region und Monat aus. OK klicken.

Slicer für jedes der ausgewählten Felder werden mit allen standardmäßig ausgewählten Werten angezeigt. Auf der Multifunktionsleiste werden Slicer-Tools angezeigt, mit denen Sie die Slicer-Einstellungen und das Erscheinungsbild bearbeiten können.

• Wählen Sie im Slicer für Region Süd und West aus.
• Wählen Sie im Slicer für einen Monat Februar und März aus.
• Halten Sie die Strg-Taste gedrückt, während Sie mehrere Werte in einem Slicer auswählen.

Ausgewählte Elemente in den Slicern werden hervorgehoben. PivotTable mit zusammengefassten Werten für die ausgewählten Elemente wird angezeigt.

Werte durch andere Berechnungen zusammenfassen

In den bisherigen Beispielen haben Sie gesehen, wie Werte nach Summe zusammengefasst wurden. Bei Bedarf können Sie jedoch auch andere Berechnungen verwenden.

In der Liste der PivotTable-Felder

• Wählen Sie das Feldkonto aus.
• Deaktivieren Sie die Feldbestellmenge.

• Ziehen Sie das Feld Konto in den Bereich Werte zusammenfassen. Standardmäßig wird die Kontosumme angezeigt.
• Klicken Sie auf den Pfeil auf der rechten Seite des Felds.
• Klicken Sie in der angezeigten Dropdown-Liste auf Wertefeldeinstellungen.

Das Feld Wertefeldeinstellungen wird angezeigt. Verschiedene Arten von Berechnungen werden als Liste unter dem Feld Wert zusammenfassen mit - angezeigt.

• Wählen Sie in der Liste Anzahl.
• Der benutzerdefinierte Name ändert sich automatisch in Anzahl der Konten. OK klicken.

Die PivotTable fasst die Kontowerte nach Anzahl zusammen.

PivotTable-Tools

Führen Sie die folgenden Schritte aus, um die Verwendung der PivotTable-Tools zu erlernen.

• Wählen Sie die PivotTable aus.

Die folgenden PivotTable-Tools werden in der Multifunktionsleiste angezeigt:

• ANALYZE
• DESIGN

ANALYSIEREN

Manche der ANALYZE Multifunktionsleistenbefehle sind -

• Legen Sie die PivotTable-Optionen fest
• Wertefeldeinstellungen für das ausgewählte Feld
• Feld erweitern
• Feld reduzieren
• Slicer einsetzen
• Timeline einfügen
• Daten aktualisieren
• Datenquelle ändern
• PivotTable verschieben
• Reihenfolge lösen (wenn es mehr Berechnungen gibt)
• PivotChart

DESIGN

Manche der DESIGN Multifunktionsleistenbefehle sind -

• PivotTable-Layout
  • Optionen für Zwischensummen
  • Optionen für Gesamtsummen
  • Berichtslayoutformulare
  • Optionen für leere Zeilen
• PivotTable-Stiloptionen
• PivotTable-Stile

Feld erweitern und reduzieren

Sie können alle Elemente eines ausgewählten Felds auf zwei Arten erweitern oder reduzieren:

• Durch Auswahl des Symbols
  
  oder
  
  links vom ausgewählten Feld.
• Durch Klicken auf das Feld "Erweitern" oder "Feld reduzieren" in der ANALYSE-Multifunktionsleiste.

Durch Auswahl des Symbols Erweitern

oder Reduzieren

links neben dem ausgewählten Feld

• Wählen Sie in der PivotTable die Zelle aus, die Ost enthält.
• Klicken Sie auf das Reduktionssymbol
  
  links von Ost.

Alle Gegenstände unter Ost werden zusammengebrochen. Das Reduktionssymbol

links von Ost ändert sich in das Erweiterungssymbol .

Sie können beobachten, dass nur die Elemente unter Ost reduziert sind. Der Rest der PivotTable-Elemente ist unverändert.

Klicken Sie

links von Ost auf das Symbol Erweitern . Alle Elemente unter Ost werden angezeigt.

Verwenden von ANALYZE auf dem Menüband

Mit den Befehlen Feld erweitern und Feld reduzieren auf der Multifunktionsleiste können Sie alle Elemente in der PivotTable gleichzeitig reduzieren oder erweitern.

• Klicken Sie in der PivotTable auf die Zelle mit Ost.
• Klicken Sie auf der Multifunktionsleiste auf die Registerkarte ANALYSE.
• Klicken Sie in der Gruppe Aktives Feld auf Feld reduzieren.

Alle Elemente des Felds Ost in der PivotTable werden reduziert.

Klicken Sie in der Gruppe Aktives Feld auf Feld erweitern.

Alle Elemente werden angezeigt.

Präsentationsstile melden

Sie können den Präsentationsstil für Ihre PivotTable so auswählen, als würden Sie ihn als Bericht einfügen. Wählen Sie einen Stil, der in den Rest Ihrer Präsentation oder Ihres Berichts passt. Lassen Sie sich jedoch nicht von den Stilen langweilen, denn ein Bericht, der sich auf die Darstellung der Ergebnisse auswirkt, ist immer besser als ein farbenfroher Bericht, der die wichtigen Datenpunkte nicht hervorhebt.

• Klicken Sie in der PivotTable auf Ost.
• Klicken Sie auf ANALYSE.
• Klicken Sie in der Gruppe Aktives Feld auf Feldeinstellungen. Das Dialogfeld Feldeinstellungen wird angezeigt.
• Klicken Sie auf die Registerkarte Layout & Drucken.
• Aktivieren Sie nach jeder Artikelbezeichnung die Option Leerzeile einfügen.

Nach jedem Wert des Felds Region werden leere Zeilen angezeigt.

Sie können leere Zeilen aus dem einfügen DESIGN Registerkarte auch.

• Klicken Sie auf die Registerkarte DESIGN.
• Klicken Sie in der Gruppe Layout auf Berichtslayout.
• Wählen Sie in der Dropdown-Liste In Gliederungsformular anzeigen aus.

• Bewegen Sie die Maus über die PivotTable-Stile. Eine Vorschau des Stils, auf dem die Maus platziert ist, wird angezeigt.
• Wählen Sie den Stil aus, der zu Ihrem Bericht passt.

PivotTable in Gliederungsform mit dem ausgewählten Stil wird angezeigt.

Zeitleiste in PivotTables

Um zu verstehen, wie die Zeitleiste verwendet wird, betrachten Sie das folgende Beispiel, in dem die Verkaufsdaten verschiedener Artikel in Bezug auf Verkäufer und Standort angegeben werden. Es gibt insgesamt 1891 Datenzeilen.

Erstellen Sie eine PivotTable aus diesem Bereich mit -

• Standort und Verkäufer in Zeilen in dieser Reihenfolge
• Produkt in Spalten
• Betragssumme beim Zusammenfassen von Werten

• Klicken Sie auf die PivotTable.
• Klicken Sie auf die Registerkarte EINFÜGEN.
• Klicken Sie in der Gruppe Filter auf Zeitleiste. Die Zeitleisten einfügen wird angezeigt.

Klicken Sie auf Datum und dann auf OK. Das Dialogfeld Zeitleiste wird angezeigt und die Zeitleistenwerkzeuge werden auf der Multifunktionsleiste angezeigt.

• Wählen Sie im Dialogfeld Zeitleiste die Option MONATE.
• Wählen Sie aus der Dropdown-Liste QUARTALE aus.
• Klicken Sie auf Q2 2014.
• Halten Sie die Umschalttaste gedrückt und ziehen Sie auf das vierte Quartal 2014.

Die Zeitleiste ist für das 2. bis 4. Quartal 2014 ausgewählt.

PivotTable wird nach dieser Zeitleiste gefiltert.