Wie Pivot-Tabellen in Excel erstellen
Die Pivot-Tabellen sind eine der leistungsstärksten Funktionen in Excel. Selbst wenn Sie ein Neuling sind, können Sie große Datenmengen in nützliche Informationen umwandeln. Mit Pivot Table können Sie in wenigen Minuten Berichte erstellen. Analysieren Sie Ihre sauberen Daten einfach und wenn die Daten nicht sauber sind, können Sie Ihre Daten bereinigen. Ich möchte dich nicht langweilen, also lass uns hineinspringen und erkunden.
So erstellen Sie eine Pivot-Tabelle Es ist ganz einfach. Wählen Sie einfach Ihre Daten aus. Gehen Sie zu Einfügen. Klicken Sie auf die Pivot-Tabelle und fertig.
Aber warte. Stellen Sie vor dem Erstellen einer Pivot-Tabelle sicher, dass alle Spalten eine Überschrift haben.
Wenn eine Spaltenüberschrift leer bleibt, wird die Pivot-Tabelle nicht erstellt und es wird eine Fehlermeldung angezeigt.
Anforderung 1: Alle Spalten sollten eine Überschrift haben, um mit Pivot-Tabellen in Excel
beginnen zu können Sie sollten Ihre Daten mit der richtigen Überschrift organisiert haben. Sobald Sie es haben, können Sie die Pivot-Tabelle einfügen.
Fügen Sie die Pivot-Tabelle aus der Multifunktionsleiste ein
Gehen Sie folgendermaßen vor, um eine Pivot-Tabelle aus dem Menü einzufügen:
-
Wählen Sie Ihren Datenbereich
-
Wechseln Sie zur Registerkarte Einfügen
-
Klicken Sie auf das Pivot Table-Symbol
-
Das Optionsfeld Pivot-Tabelle erstellen wird angezeigt
-
Hier sehen Sie den von Ihnen ausgewählten Datenbereich. Wenn Sie der Meinung sind, dass dies nicht der Bereich ist, den Sie auswählen möchten, ändern Sie die Größe Ihres Bereichs direkt von hier aus, anstatt zurück zu gehen und die Daten erneut auszuwählen.
-
Als Nächstes können Sie auswählen, wo Sie Ihre Pivot-Tabelle haben möchten. Ich empfehle die Verwendung des neuen Arbeitsblatts, aber Sie können auch das aktuelle Arbeitsblatt verwenden. Definieren Sie einfach den Ort im Feld Ort.
-
Wenn Sie mit den Einstellungen fertig sind, klicken Sie auf OK. Sie haben Ihre Pivot-Tabelle in einem neuen Blatt. Wählen Sie einfach Ihre Felder für Zusammenfassungen aus. Wir werden sehen, wie wir mithilfe der Pivot-Tabelle eine Zusammenfassung der Daten erstellen, aber zuerst werden die Grundlagen klargestellt. In diesem Tutorial zur Excel-Pivot-Tabelle erfahren Sie mehr als erwartet.
Pivot-Tabellenverknüpfung einfügen (Alt> N> V)
Dies ist eine sequentielle Tastenkombination zum Öffnen des Optionsfelds „Pivot-Tabelle erstellen“.
Drücken Sie die Alt-Taste und lassen Sie sie los. Drücke N und lass es los. Drücken Sie V und lassen Sie es los. Das Optionsfeld „Pivot-Tabelle erstellen“ wird geöffnet.
Befolgen Sie nun einfach die oben beschriebenen Schritte, um eine Pivot-Tabelle in Excel zu erstellen.
Pivot-Tabellenverknüpfung mit der alten Excel-Verknüpfung einfügen (Alt> D> P)
Eine Sache, die mir an Microsoft Excel am besten gefällt, ist, dass sie in jeder neuen Version von Excel neue Funktionen einführt und die alten Funktionen nicht verwirft (wie es MS mit Win 8 getan hat. Es war erbärmlich). Dies * ermöglicht es dem älteren Benutzer, normal an neuen Versionen zu arbeiten, wie sie es früher mit älteren Versionen getan haben.
Wenn Sie nacheinander ALT, D und P auf der Tastatur drücken, wird Excel geöffnet, um einen Pivot-Tabellen-Assistenten zu erstellen.
Wählen Sie die entsprechende Option. Die im obigen Screenshot ausgewählte Option führt dazu, dass wir eine Pivot-Tabelle erstellen, wie wir sie zuvor erstellt haben.
Drücken Sie die Eingabetaste oder klicken Sie auf Weiter, wenn Sie den ausgewählten Bereich überprüfen möchten.
Drücken Sie erneut die Eingabetaste.
Wählen Sie ein neues Arbeitsblatt oder eine beliebige Stelle aus, an der Ihre Pivot-Tabelle die Eingabetaste drücken soll. Und es ist geschafft.
Berichte mit Pivot-Tabellen erstellen.
Jetzt wissen Sie, wie Sie eine Pivot-Tabelle einfügen. Lassen Sie uns in wenigen Minuten Berichte mit Pivot-Tabellen erstellen.
Wir haben die Daten für die stationäre Bestellung.
Die Spaltenfelder sind:
Bestelldatum: Bestelldatum (Obvious)
Region: Die Bestellregion im Land Kunde: Name des Kunden (_was kann es sonst sein) _ Artikel: Bestellter Artikel Einheit: Anzahl der Einheiten eines bestellten Artikels Stückkosten: Pro Stückkosten Gesamt: Gesamtkosten der Bestellung (Einheit) Kosten).
Pivot-Bereiche: Diese 4 Bereiche werden verwendet, um Ihre Daten auf eine art und Weise anzuzeigen.
FILTER: Fügen Sie hier Felder ein, aus denen Sie Filter in Ihren Bericht anwenden möchten.
SPALTEN: Fügen Sie die gewünschten Felder in Ihre Spalten im Bericht ein: (Es ist besser zu zeigen als zu erklären)
REIHEN: Ziehen Sie Felder, die Sie roh anzeigen möchten, wie im obigen Bild. Ich habe Region in REIHEN angezeigt.
WERTE: Wählen Sie ein Feld aus, um Anzahl, Summe, Durchschnitt, Prozentsatz (und viele mehr) usw. zu erhalten, die Sie sehen möchten.
Mit den oben genannten Informationen haben wir nun diesen schnellen Pivot-Bericht erstellt, der zeigt, aus welcher Region wie viele Bestellungen für jeden Artikel aufgegeben werden.
Nachdem Sie Ihre Daten und Pivot-Felder verstanden haben (schließlich sind Sie schlau), beantworten wir einige Fragen zu diesen Daten mithilfe der Pivot-Tabelle schnell.
Q1. Wie viele Bestellungen gibt es?
Die Pivot-Tabelle ist anfangs leer, wie in der Abbildung unten gezeigt.
Sie müssen Felder (Spaltennamen) in geeigneten Bereichen auswählen, um die Zusammenfassung oder Details dieses Felds anzuzeigen.
Um die obige Frage zu beantworten, werde ich in den Wertfeldern Element auswählen (eine beliebige Spalte auswählen, nur sicherstellen, dass keine leere Zelle dazwischen ist).
Wählen Sie Element aus der Feldliste aus und ziehen Sie es in das Feld Wert.
Wir haben unsere Antwort. Pivot-Tabellen sagen mir, dass es insgesamt 43 Bestellungen gibt. Das ist richtig.
Pro-Tipp: Sie sollten Ihre Daten überprüfen, ob sie korrekt sind oder nicht. Wenn diese Nummer nicht mit den Daten übereinstimmt, bedeutet dies, dass Sie entweder einen falschen Bereich ausgewählt haben oder dass dieses Feld leere Zellen enthält.
Info: Das Wertefeld zählt standardmäßig die Anzahl der Einträge in einer Spalte, wenn es Text enthält, und Summen, wenn das Feld nur Werte enthält.
Sie können dies in den Wertefeldeinstellungen ändern. Wie? Wir sehen uns später in diesem Pivot Table Tutorial.
Nachdem ich im Wertebereich OrderDate ausgewählt habe, wird * 42 angezeigt.
Dies bedeutet, dass OrderDate eine leere Zelle hat, da wir wissen, dass die Gesamtzahl der Bestellungen 43 * beträgt.
Identifizieren Sie unregelmäßige Daten mithilfe von Pivot-Tabellen und bereinigen Sie sie.
Pivot Table kann Ihnen helfen, falsche Informationen in den Daten zu finden.
Die meiste Zeit werden unsere Daten von Dateneingabebetreibern oder von Benutzern aufbereitet, die normalerweise unregelmäßig sind und eine Reinigung benötigen, um genaue Berichte und Analysen zu erstellen.
Sie sollten Ihre Daten immer bereinigen und vorbereiten, bevor Sie Berichte erstellen. Aber manchmal erfahren wir erst nach der Erstellung des Berichts, dass unsere Daten einige Unregelmäßigkeiten aufweisen. Komm, ich zeige dir wie …
F2: Geben Sie die Anzahl der Bestellungen aus jeder Region an
Um diese Frage zu beantworten:
Wählen Sie Region aus und ziehen Sie es in den Zeilenbereich und das Element in den Wertebereich.
Wir erhalten regional geteilte Daten. Wir können antworten, aus welcher Region wie viele Bestellungen eingegangen sind.
Es gibt insgesamt 4 Regionen in unseren Daten gemäß der Pivot-Tabelle.
Aber warten Sie, beachten Sie, dass Central und Centrle Region. Wir wissen, dass Centrle zentral sein sollte. Es liegt eine Unregelmäßigkeit vor. Wir müssen zu unseren Daten gehen und Daten bereinigen.
Um Daten im Regionsfeld zu bereinigen, filtern wir den falschen Regionsnamen (Centrle) heraus und korrigieren ihn (Central).
Kehren Sie nun zu Ihren Pivot-Daten zurück.
Klicken Sie mit der rechten Maustaste auf eine beliebige Stelle in der Pivot-Tabelle und klicken Sie auf Aktualisieren.
Ihr Bericht wurde jetzt aktualisiert.
INFO: Unabhängig davon, welche Änderungen Sie an Ihren Quelldaten vornehmen, arbeitet die Pivot-Tabelle so lange an alten Daten, bis Sie sie aktualisieren. Excel erstellt einen Pivot-Cache und eine Pivot-Tabelle wird in diesem Cache ausgeführt. Beim Aktualisieren wird der alte Cache mit neuen Daten geändert.
Jetzt können Sie sehen, dass es tatsächlich nur 3 Regionen gibt.
Pivot-Berichtsformatierung mit kategorisierten Zeilen.
Manchmal benötigen Sie Berichte wie das obige Bild. Dies macht es einfach, Ihre Daten strukturiert anzuzeigen. Sie können leicht feststellen, aus welcher Region wie viele Artikel bestellt wurden. Mal sehen, wie Sie das machen können.
Region und Gegenstand in den Bereich REIHEN verschieben. Stellen Sie sicher, dass sich der Bereich oben und die Elemente unten befinden (siehe Abbildung).
Ziehen Sie das Element für den Wertebereich.
Als Ergebnis erhalten Sie diesen Bericht.
Das wird reichen. Aber manchmal möchte Ihr Chef in tabellarischer Form ohne Zwischensummen berichten. Dazu müssen wir unsere Pivot-Tabelle formatieren.
Zwischensummen aus der Pivot-Tabelle entfernen
Befolgen Sie diese Schritte:
-
Klicken Sie auf eine beliebige Stelle in Ihrer Pivot-Tabelle.
-
Wechseln Sie zur Registerkarte Design.
-
Klicken Sie auf das Menü Zwischensummen.
-
Klicken Sie auf Zwischensummen nicht anzeigen.
Sie können sehen, dass es jetzt keine Zwischensummen gibt.
Fein. Aber es ist immer noch nicht in tabellarischer Form. Regionen und Elemente werden in einer einzigen Spalte angezeigt. Zeigen Sie sie separat.
Erstellen Sie eine tabellarische Pivot-Tabelle
Gehen Sie folgendermaßen vor, um Regionen und Elemente in verschiedenen Spalten anzuzeigen:
-
Klicken Sie auf eine beliebige Stelle in der Pivot-Tabelle. 2. Wechseln Sie zur Registerkarte Design. 3. Klicken Sie auf Berichtslayout.
-
Klicken Sie auf die Option Anzeigen für das Tabellenformular. Schließlich haben Sie diese differenzierte Ansicht Ihres Berichts.
Jetzt kennen wir die Gesamtzahl der Bestellungen für jeden Artikel aus jeder Region. Es wird in den Spalten Anzahl der Elemente angezeigt.
Bitte ändern Sie den Spaltennamen in Bestellungen.
Es sieht jetzt besser aus.
F3: Wie viele Einheiten jedes Artikels werden bestellt?
Um diese Frage zu beantworten, benötigen wir eine Summe von Einheiten. Verschieben Sie dazu einfach das Feld Einheiten auf Werte. Es wird automatisch die Anzahl der Einheiten für jeden Artikel summiert. Wenn eine Spalte in der Pivot-Tabelle nur Werte enthält, zeigt die Pivot-Tabelle standardmäßig die Summe dieser Werte an. Sie kann jedoch über eine Wertefeldeinstellung geändert werden. Wie? Letzteres zeige ich Ihnen.
Einstellungen für Pivot-Tabellenwertfelder
Q4: Der Durchschnittspreis für jeden Artikel?
In unseren Beispieldaten ist der Preis eines Artikels für verschiedene Bestellungen unterschiedlich. Siehe beispielsweise Bindemittel.
Ich möchte die durchschnittlichen Kosten für jeden Artikel in der Pivot-Tabelle wissen. Um dies herauszufinden, ziehen Sie das Feld Stückkosten auf Wert. Es wird die Summe der Stückkosten angezeigt.
Wir wollen keine Summe der Stückkosten, wir wollen den Durchschnitt der Stückkosten. Dazu …
-
Klicken Sie mit der rechten Maustaste auf die Summe der Spalte Stückkosten in der Pivot-Tabelle. Klicken Sie auf Wertefeldeinstellungen. Wählen Sie basierend auf den verfügbaren Optionen Durchschnitt aus und klicken Sie auf OK.
Schließlich haben Sie diesen Pivot-Bericht:
Berechnete Felder der Pivot-Tabelle
Eine der nützlichsten Funktionen der Pivot-Tabelle sind ihre berechneten Felder. Berechnete Felder sind Felder, die durch einige Operationen an verfügbaren Spalten erhalten werden.
Lassen Sie uns anhand eines Beispiels verstehen, wie berechnete Felder in die Pivot-Tabelle eingefügt werden:
Basierend auf unseren Daten haben wir diesen Bericht erstellt.
Hier haben wir die Summe der Einheiten und die Gesamtkosten. Ich habe gerade die Gesamtspalte in das Feld Werte verschoben und sie dann in Gesamtkosten umbenannt. Jetzt möchte ich den Durchschnittspreis einer Einheit jedes Artikels für jede Region wissen.
Und das wäre:
Average Price = Total Cost / Total Units
Fügen Sie ein Feld in die Pivot-Tabelle ein, das den Durchschnittspreis für jede Artikelregion anzeigt:
Führen Sie die folgenden Schritte aus, um ein berechnetes Feld in die Pivot-Tabelle einzufügen. 1. Klicken Sie auf eine beliebige Stelle in der Pivot-Tabelle und wechseln Sie zur Registerkarte Analysieren
-
Klicken Sie in der Berechnungsgruppe auf Felder, Elemente und Sätze.
-
Klicken Sie auf Berechnete Felder. Sie sehen dieses Eingabefeld für Ihr Berechnungsfeld:
-
Geben Sie in das Namenseingabefeld die durchschnittlichen Kosten oder alles ein, was Sie möchten. Excel macht das nichts aus. Schreiben Sie in das Formeleingabefeld und klicken Sie auf OK.
=Total / Units
Sie können dies manuell über die Tastatur schreiben oder auf die im Bereich Felder aufgeführten Feldnamen doppelklicken, um Vorgänge auszuführen.