Pivot-Tabellen sind eine der leistungsstärksten Funktionen in Excel. Selbst wenn Sie ein Neuling sind, können Sie große Datenmengen zu nützlichen Informationen verarbeiten. Pivot Table kann Ihnen helfen, Berichte in wenigen Minuten zu erstellen. Analysieren Sie Ihre sauberen Daten einfach und wenn die Daten nicht sauber sind, kann es Ihnen helfen, Ihre Daten zu bereinigen. Ich möchte dich nicht langweilen, also lass uns hineinspringen und es erkunden.
So erstellen Sie eine Pivot-Tabelle
Es ist 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 zu beginnen
Sie sollten Ihre Daten mit der richtigen Überschrift organisieren. Sobald Sie es haben, können Sie die Pivot-Tabelle einfügen.
Einfügen der Pivot-Tabelle aus der Multifunktionsleiste
Gehen Sie folgendermaßen vor, um eine Pivot-Tabelle aus dem Menü einzufügen:
1. Wählen Sie Ihren Datenbereich
2. Gehen Sie zur Registerkarte Einfügen
3. Klicken Sie auf das Symbol für die Pivot-Tabelle.
4. Das Optionsfeld Erstellen einer Pivot-Tabelle wird angezeigt.
5. Hier sehen Sie den 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ückzugehen und die Daten erneut auszuwählen.
6. Als nächstes können Sie auswählen, wo Sie Ihre Pivot-Tabelle haben möchten. Ich empfehle, das neue Arbeitsblatt zu verwenden, aber Sie können auch das aktuelle Arbeitsblatt verwenden. Definieren Sie einfach den Standort im Feld Standort.
7. Wenn Sie mit den Einstellungen fertig sind, klicken Sie auf die Schaltfläche 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 wollen wir die Grundlagen klären. In diesem Excel-Pivot-Tabellen-Tutorial erfahren Sie mehr, als Sie erwarten.
Verknüpfung für Pivot-Tabelle einfügen (Alt > N > V)
Dies ist eine sequenzielle Tastenkombination, um das Erstellen zu öffnen Pivot-Tabelle Optionsfeld.
Drücken Sie die Alt-Taste und lassen Sie sie los. Schlag n und geben Sie es frei. Schlag V und geben Sie es frei. Das Optionsfeld zum Erstellen einer Pivot-Tabelle wird geöffnet.
Folgen Sie nun einfach dem obigen Verfahren, um eine Pivot-Tabelle in Excel zu erstellen.
Fügen Sie eine Pivot-Tabellenverknüpfung mit der alten Excel-Verknüpfung ein (Alt > D > P)
Eine Sache, die mir an Microsoft Excel am besten gefällt, ist, dass in jeder neuen Version von Excel neue Funktionen eingeführt werden, aber sie die alten Funktionen nicht verwerfen (wie MS mit win 8. Es war erbärmlich). Dies ermöglicht es den alten Benutzern, normal an neuen Versionen zu arbeiten, wie sie früher an älteren Versionen gearbeitet haben.
Wenn Sie nacheinander drücken ALT, D und P auf der Tastatur öffnet Excel einen Assistenten zum Erstellen einer Pivot-Tabelle.
Wählen Sie die entsprechende Option aus. Die ausgewählte Option im obigen Screenshot führt uns dazu, eine Pivot-Tabelle wie zuvor erstellt zu erstellen.
Drücken Sie die Eingabetaste oder klicken Sie auf Weiter, wenn Sie Ihren ausgewählten Bereich überprüfen möchten.
Drücken Sie erneut die Eingabetaste.
Wählen Sie ein neues Arbeitsblatt oder wo immer Sie möchten, dass Ihre Pivot-Tabelle die Eingabetaste drückt. Und es ist vollbracht.
Erstellen von Berichten mit Pivot-Tabellen
Jetzt wissen Sie, wie Sie eine Pivot-Tabelle einfügen. Bereiten wir uns darauf vor, in wenigen Minuten Berichte mit Pivot-Tabellen zu erstellen.
Wir haben die Daten für die stationäre Bestellung.
Die Spaltenfelder sind:
Auftragsdatum: Bestelldatum (offensichtlich)
Region: Die Ordnungsregion des Landes
Kunde: Name des Kunden (was kann es noch sein)
Artikel: Bestellter Artikel
Einheit: Anzahl der Einheiten eines bestellten Artikels
Kosten pro Einheit: Kosten pro Einheit
Gesamt: Gesamtkosten der Bestellung (Einheit*Einheitskosten).
Um Berichte mit Pivot-Tabellen zu erstellen, verwenden wir
Pivot-Tabellenfelder: Enthält die Liste der Spaltennamen in Ihren Daten.
Drehbereiche: Diese 4 Bereiche werden verwendet, um Ihre Daten auf manierierte Weise anzuzeigen.
FILTER: Fügen Sie hier Felder ein, aus denen Sie Filter in Ihrem Bericht anwenden möchten.
SPALTEN: Fügen Sie hier die Felder ein, die Sie in Ihre Spalten im Bericht einfügen möchten: (Besser anzeigen als erklären)
REIHEN: Ziehen Sie Felder, die Sie roh anzeigen möchten, wie im obigen Bild. Ich habe Region in ROWS angezeigt.
WERTE: Wählen Sie ein Feld aus, um Anzahl, Summe, Durchschnitt, Prozentsatz (und vieles 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 wurden.
Nachdem Sie nun Ihre Daten und Pivot-Felder verstanden haben (schließlich sind Sie schlau), lassen Sie uns mit der Pivot-Tabelle schnell einige Fragen zu diesen Daten beantworten.
Q1. Wie viele Bestellungen gibt es?
Die Pivot-Tabelle ist zunächst leer, wie in der Abbildung unten gezeigt.
Sie müssen Felder (Spaltennamen) in den entsprechenden Bereichen auswählen, um die Zusammenfassung oder Details dieses Felds anzuzeigen.
Um nun die obige Frage zu beantworten, wähle ich Item (wählen Sie eine beliebige Spalte aus, stellen Sie nur sicher, dass keine leere Zelle dazwischen vorhanden ist) in den Wertfeldern.
Wählen Sie Element aus der Feldliste 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.
Jetzt habe ich ausgewählt Auftragsdatum im Bereich Werte wird angezeigt 42. Dies meint Auftragsdatum hat eine leere Zelle schon seit Wir wissen, dass die Gesamtzahl der Bestellungen beträgt 43.
Identifizieren Sie unregelmäßige Daten mithilfe von Pivot-Tabellen und bereinigen Sie sie.
Die Pivot-Tabelle kann Ihnen helfen, falsche Informationen in den Daten zu finden.
Die meiste Zeit werden unsere Daten von Dateneingabeoperatoren oder von Benutzern erstellt, die normalerweise unregelmäßig sind und eine gewisse Reinigung benötigen, um genaue Berichte und Analysen zu erstellen.
Sie sollten Ihre Daten immer auf eine manierierte Weise bereinigen und aufbereiten, bevor Sie Berichte erstellen. Aber manchmal erfahren wir erst nach der Erstellung des Berichts, dass unsere Daten Unregelmäßigkeiten aufweisen. Komm, ich zeige dir wie…
F2: Anzahl der Bestellungen aus jeder Region angeben
Um nun diese Frage zu beantworten:
Auswählen Region und ziehe es nach Reihen Bereich und Artikel zu Werte Bereich.
Wir erhalten regional aufgeteilte Daten. Wir können beantworten, aus welcher Region wie viele Bestellungen eingegangen sind.
In unseren Daten gibt es laut Pivot-Tabelle insgesamt 4 Regionen. Aber warte, beachte das Zentral und Zentrum Region. Wir wissen, dass Centrle sein sollte Zentral. Es liegt eine Unregelmäßigkeit vor. Wir müssen zu unseren Daten gehen und eine Datenbereinigung durchführen.
Um die 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.
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 erkennen, aus welcher Region wie viele Artikel bestellt werden. Mal sehen, wie Sie dies tun können.
Bewegung Region und Artikel zu REIHEN Bereich. Stellen Sie sicher, dass sich die Region oben und die Elemente unten befinden, wie in der Abbildung gezeigt.
Ziehen Artikel zum Wert Bereich.
Als Ergebnis erhalten Sie diesen Bericht.
Das wird klappen. 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
Folge diesen Schritten:
1. Klicken Sie auf eine beliebige Stelle in Ihrer Pivot-Tabelle.
2. Gehe zum Entwurf Tab.
3. Klicken Sie auf die Zwischensummen Speisekarte.
4. Klicken Sie auf Zwischensummen nicht anzeigen.
Sie sehen, dass es jetzt keine Zwischensummen mehr gibt.
Bußgeld. Aber es ist immer noch nicht tabellarisch. Regionen und Artikel werden in einer einzigen Spalte angezeigt. Zeigen Sie sie separat an.
Erstellen Sie eine tabellarische Pivot-Tabelle
Um nun Regionen und Elemente in verschiedenen Spalten anzuzeigen, gehen Sie folgendermaßen vor:
1. Klicken Sie auf eine beliebige Stelle in der Pivot-Tabelle
2. Gehen Sie zum Design-Tab
3. Klicken Sie auf Berichtslayout.
4. Klicken Sie auf die Show für die Option Tabellenform. Endlich haben Sie diese ausgefeilte Ansicht Ihres Berichts.
Jetzt kennen wir die Gesamtzahl der Bestellungen für jeden Artikel aus jeder Region. Es wird in der Zählung angezeigt der Artikelspalten.
Bitte ändern Sie den Spaltennamen in Aufträge.
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 summiert automatisch die Anzahl der Einheiten für jeden Artikel. 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 Wertfeldeinstellung geändert werden. Wie? Letzteres zeige ich dir.
Pivot-Tabellenwertfeldeinstellungen
F4: Der Durchschnittspreis jedes Artikels?
In unseren Beispieldaten ist der Preis eines Artikels für verschiedene Bestellungen unterschiedlich. Siehe beispielsweise Ordner.
Ich möchte die durchschnittlichen Kosten jedes Artikels in der Pivot-Tabelle wissen. Um dies herauszufinden, ziehen Sie Stückkosten in das Wertfeld. Es wird die Summe der Stückkosten angezeigt.
Wir wollen nicht Summe der Stückkosten, wir wollen Durchschnitt der Stückkosten. Dazu…
1. Klicken Sie mit der rechten Maustaste irgendwo auf die Summe der Einheitskostenspalte in der Pivot-Tabelle
2. Klicken Sie auf Wertfeldeinstellungen
3. Basierend auf den verfügbaren Optionen, auswählen Durchschnitt 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 die berechneten Felder. Berechnete Felder sind Felder, die durch einige Operationen für verfügbare Spalten abgerufen werden.
Lassen Sie uns anhand eines Beispiels verstehen, wie berechnete Felder in eine Pivot-Tabelle eingefügt werden:
Basierend auf unseren Daten haben wir diesen Bericht erstellt.
Hier haben wir Summe der Einheiten und Gesamtkosten. Ich habe gerade die Summenspalte in das Feld Werte verschoben und dann umbenannt in Gesamtkosten. Jetzt möchte ich den Durchschnittspreis einer Einheit jedes Artikels für jede Region wissen. Und das wäre:
Durchschnittspreis = Gesamtkosten / Gesamteinheiten
Fügen wir ein Feld in die Pivot-Tabelle ein, das den Durchschnittspreis der einzelnen Artikelregionen anzeigt:
Befolgen Sie diese Schritte, um ein berechnetes Feld in die Pivot-Tabelle einzufügen
1. Klicken Sie auf eine beliebige Stelle in der Pivot-Tabelle und gehen Sie zur Registerkarte Analysieren
2. Klicken Sie in der Berechnungsgruppe auf Felder, Elemente und Sets.
3. Klicken Sie auf Berechnete Felder.
Sie sehen dieses Eingabefeld für Ihr Berechnungsfeld:
4. Geben Sie in das Namenseingabefeld die durchschnittlichen Kosten oder etwas anderes ein, Excel wird nichts dagegen haben. Schreiben Sie in das Formeleingabefeld und klicken Sie auf OK.
=Gesamt / Einheiten
Sie können dies manuell über die Tastatur schreiben oder auf die im Bereich Felder aufgelisteten Feldnamen doppelklicken, um Operationen auszuführen.
5. Sie haben Ihr berechnetes Feld jetzt zu Ihrer Pivot-Tabelle hinzugefügt. Sie wird als Summe der durchschnittlichen Kosten bezeichnet, ist aber keine Summe. Excel führt einfach die Standardfunktion aus, um die Spalte zu benennen (wie ein Ritual). Benennen Sie diese Spalte um und begrenzen Sie die angezeigten Dezimalstellen.
Und da haben Sie ein berechnetes Feld. Sie können es so komplex machen, wie Sie möchten. Als Beispiel habe ich diese einfache Durchschnittsoperation genommen.
Gruppieren in der Pivot-Tabelle
Sie haben diesen Pivot-Bericht erstellt.
Nun möchte ich, dass dieser Bericht jährlich geteilt wird. Siehe den Schnappschuss unten.
Wir haben eine Spalte zum Bestelldatum. Verschieben Sie das Feld OrderDate nach Zeilen oben.
Es sieht nicht nach dem erforderlichen Bericht aus. Wir brauchen Gruppentermine im Jahr.
Um nun ein Feld in der Excel-Pivot-Tabelle zu gruppieren, gehen Sie folgendermaßen vor:
1. Klicken Sie mit der rechten Maustaste auf das Feld, das Sie gruppieren möchten.
2. Klicken Sie auf Gruppe. Sie haben dieses Optionsfeld zur Anpassung. Da es sich um eine Datenspalte handelt, zeigt uns Excel die Gruppierung entsprechend an. Sie können Ihr Start- und Enddatum wählen.
3. Wählen Sie für Jahre und klicken Sie auf OK. Sie haben die jährliche Gruppierung in der Pivot-Tabelle von Excel vorgenommen.
Slicer der Pivot-Tabelle
Slicer wurden in Excel 2010 als Add-In eingeführt. In Excel 2013 und 2016 ist es standardmäßig genauso wie Filter verfügbar.
Slicer sind nichts anderes als Filter. Im Gegensatz zu Filtern zeigt Slicer alle verfügbaren Optionen direkt vor Ihnen. Es macht Ihr Dashboard interaktiver.
So fügen Sie Slicer in Pivot-Tabelle, Excel 2016 und 2013 hinzu
Pivot Table Slicer lassen sich einfach hinzufügen. Folge diesen Schritten:
1. Klicken Sie auf eine beliebige Stelle in der Pivot-Tabelle und wechseln Sie zur Registerkarte Analysieren.
2. Klicken Sie auf Slicer einfügen. Sie haben eine Liste mit Feldern für Ihre Daten. Wählen Sie so viele aus, wie Sie möchten.
3. Wählen Sie in diesem Beispiel Region und klicken Sie auf OK.
Sie haben Slicer zu Ihrem Bericht hinzugefügt. Wenden Sie nun den Filter mit nur einem Klick an.
Zeitleiste in Excel 2016 und 2013 einfügen
Dies ist eine meiner Lieblingsfunktionen von Excel-Pivot-Tabellen. Diese neue Funktionalität funktioniert nur mit Datumsangaben. Auf diese Weise können Sie visuell eine Zeitspanne auswählen, um Ihre Daten zu filtern.
Gehen Sie folgendermaßen vor, um einen Timeline-Cursor einzufügen:
So fügen Sie eine Zeitachse in der Pivot-Tabelle hinzu, Excel 2016 und 2013
1. Klicken Sie auf eine beliebige Stelle in der Pivot-Tabelle und wechseln Sie zur Registerkarte Analysieren.
2. Klicken Sie auf Timeline aus Filtergruppe einfügen. Alle Spalten, die Zeitwerte in Quellendaten enthalten, werden in einem Optionsfeld zur Auswahl aufgelistet. Hier haben wir nur einen. Also ja…
2. Wählen Sie Ihre Optionen und drücken Sie die Eingabetaste oder klicken Sie auf OK. Es ist fertig und Sie haben Ihre Pivot-Tabellen-Zeitachse direkt vor sich.
Sie können wählen, ob es täglich, monatlich, vierteljährlich oder jährlich angezeigt werden soll. Ich habe hier monatlich gewählt.
In diesem Artikel habe ich die wichtigsten und nützlichsten Funktionen von Pivot Table behandelt. Wir haben neue Funktionen der Pivot-Tabelle in Excel 2016 und 2013 untersucht. Wir haben die klassische Verwendung einer Pivot-Tabelle kennengelernt, die in Excel 2007, 2010 und älter ausgeführt wurde. Sie sind immer noch nützlich. Wenn Sie Ihre Antwort in Bezug auf Ihre Pivot-Tabelle hier nicht gefunden haben, fragen Sie im Kommentarbereich nach.
Es gibt noch viele weitere Funktionen, die noch erklärt werden müssen. Im nächsten Artikel lernen wir die erweiterte Pivot-Tabellenfunktion kennen. Bis dahin Excel bei allem.