Beginnen Sie mit Daten aus mehreren Jahren.
Angenommen, Sie haben zwei Jahre lang detaillierte Aufzeichnungen. Jeder Datensatz hat ein tägliches Datum. Wenn Sie aus diesem Bericht eine Pivot-Tabelle erstellen, enthält die Pivot-Tabelle Hunderte Zeilen mit täglichen Daten. Dies ist keine große Zusammenfassung.
Wählen Sie eine dieser Datumszellen in der Pivot-Tabelle aus. Wählen Sie auf der Registerkarte Analysieren in der Multifunktionsleiste die Option Gruppenfeld.
Da Sie sich in einem Datumsfeld befinden, erhalten Sie diese Version des Dialogfelds "Gruppierung". Deaktivieren Sie darin Monate und wählen Sie Jahre.
Die täglichen Daten werden auf Jahre gerollt. Verschieben Sie das Datumsfeld von ROWS nach COLUMNS.
Das Ergebnis ist fast perfekt. Anstelle einer Gesamtsumme in Spalte D möchten Sie wahrscheinlich eine prozentuale Abweichung.
Um die Spalte "Gesamtsumme" zu entfernen, wählen Sie auf der Registerkarte "Design" die Option "Gesamtsummen", "Nur für Spalten". (Ich stimme zu, dies ist eine der umständlicheren Formulierungen in Excel.)
Um die Varianzspalte zu erstellen, müssen Sie eine Formel außerhalb der Pivot-Tabelle schreiben, die innerhalb der Pivot-Tabelle zeigt. Berühren Sie beim Erstellen der Formel weder die Maus noch die Pfeiltasten, da sonst die unangenehme Funktion GETPIVOTDATA angezeigt wird. =C5/B5-1Geben Sie stattdessen einfach ein und drücken Sie die Eingabetaste.
Vielen Dank an Александр Воробьев für den Vorschlag dieses Tipps.
Schau Video
- Beginnen Sie mit Daten aus mehreren Jahren
- Einfügen, Pivot-Tabelle
- Ziehen Sie das Datumsfeld in den Zeilenbereich
- Excel 2016: Drücken Sie Strg + Z, um die Gruppierung der Daten aufzuheben
- Ziehen Sie den Umsatz in den Wertebereich
- Wählen Sie ein beliebiges Datum im Zeilenbereich aus
- Gruppenfeld verwenden
- Wählen Sie Monate & Jahre
- Hinzufügen von Zwischensummen zum Feld "Jahre" nach dem Gruppieren einer Pivot-Tabelle
- Verwenden Sie die Tabellenform in einer Pivot-Tabelle, um jedem Zeilenfeld eine eigene Spalte zuzuweisen
- Wiederholen Sie alle Zeilenbeschriftungen, um die Lücken in einer Pivot-Tabelle auszufüllen
- Ziehen Sie Jahre in den Spaltenbereich
- Klicken Sie mit der rechten Maustaste auf die Spaltenüberschrift Gesamtsumme und entfernen Sie sie
- So vermeiden Sie die GetPivotData-Funktion, wenn die Formel auf eine Pivot-Tabelle zeigt
- Um eine Varianz zu erstellen, geben Sie die Formel ohne Maus oder Pfeile ein
- Vielen Dank an Александр Воробьев für den Vorschlag dieses Tipps
Video-Transkript
Lernen Sie Excel aus dem MrExcel-Podcast, Folge 1998. Pivot Table Jahr für Jahr.
Ich werde das gesamte Buch podcasten. Klicken Sie auf das "i" in der oberen rechten Ecke, um die MrExcel Excel-Wiedergabeliste aufzurufen.
Hey, willkommen zurück im Netcast, ich bin Bill Jelen. Heute das erste Thema im Buch über Pivot-Tabellen und wie man einen Jahresbericht erstellt. Wir beginnen also mit dem Datensatz, der sich über zwei Jahre erstreckt. Wir gehen von 2018 bis 2019. Ich werde eine Pivot-Tabelle erstellen, Einfügen, PivotTable, OK, das ist in Ordnung, gehen wir zu a neues Arbeitsblatt.
Auf dem neuen Arbeitsblatt nehmen wir das Feld Datum und ziehen es in den Bereich Zeilen. Und dann werden in Excel 2016 die Dinge automatisch gruppiert. Jetzt weiß ich, dass sich die meisten Leute noch in Excel 2013 befinden, vielleicht in Excel 2010, also drücke ich Strg + Z, um den Vorgang rückgängig zu machen. Das ist die Erfahrung, die Sie in den Jahren 2010 und 2013 machen werden. Wir werden das Feld "Einnahmen" in den Bereich "Werte" ziehen. Wir beginnen also mit einem hässlichen Bericht, es wird Hunderte und Hunderte von Zeilen geben, weil wir jeden Tag Verkäufe hatten. Aber ich gehe zum allerersten Datumsfeld, zum allerersten Datumsfeld, gehe zu den PivotTable-Tools, Analysieren, in den Jahren 2013 oder 2016 und 2010 hieß es Optionen und wähle Gruppenfeld. Stellen Sie sicher, dass Sie ein Datum auswählen, bevor Sie Gruppenfeld auswählen, und rollen Sie die Daten auf Monate und Jahre auf. Oder Viertel und Jahre,oder Monate und Quartale und Jahre, klicken Sie auf OK. Und was wir bekommen, ist ein neues virtuelles Feld namens Jahre hier unten im Bereich "Zeilen".
Nun ein paar Ärger. Es ist ärgerlich, dass sie uns keine Jahreszahlen geben. Es ist einfach genug, das zu beheben, obwohl ich das in meinem Fall nicht beheben muss. Wir klicken auf Feldeinstellungen und Automatisch, weil ich nicht vorhabe, die Jahre hier zu lassen. Natürlich gehe ich jedes Mal, wenn ich eine PivotTable erstelle, zu Design und sage In tabellarischer Form anzeigen, um alles in eine eigene Spalte zu bringen. Wiederholen Sie dann Alle Artikelbezeichnungen, um diese Lücken auf der linken Seite auszufüllen. Auch dies wäre hier nicht notwendig, da ich nicht vorhabe, die Jahre dort zu belassen, wo sie sind. Ich werde die Jahre nehmen und sie in die Spalten ziehen, und das ist der Bericht, für den ich gedreht habe.
Okay, wir haben also Monate auf der linken Seite, 2018 bis 2019. In Spalte D, was will ich wirklich, ich will keine Gesamtsumme, ich werde mit der rechten Maustaste klicken und sagen, entferne die Gesamtsumme Total und jetzt sind wir außerhalb einer PivotTable. % Change, sehr verlockend, diese Formel mit der Maus oder den Pfeiltasten zu erstellen, aber das können Sie nicht, da Sie die Funktion… Pivot-Daten abrufen erhalten, die sich nicht leicht kopieren lässt. Also = C5 / B5-1, wir formatieren das als Prozentsatz und doppelklicken, um es nach unten zu kopieren, rechts. Schöner Bericht, der zeigt, wo wir letztes Jahr waren, wo wir dieses Jahr waren und dann die prozentuale Veränderung von Jahr zu Jahr.
Nun, dieser Tipp und 40 andere sind alle in diesem Buch. Klicken Sie auf das "i" in der oberen rechten Ecke neben dem Buch. Okay, Sie beginnen mit dem Datensatz mit mehreren Jahren. Führen Sie Einfügen, Pivot-Tabelle aus und ziehen Sie das Datumsfeld in den Zeilenbereich. Drücken Sie in Excel 2016 Strg + Z, um die automatischen Gruppierungsaktualisierungen rückgängig zu machen. Warum lassen wir das nicht einfach hier? Nun, es kommt tatsächlich darauf an. Wenn Sie Daten vom 2. Januar bis 30. Dezember haben, werden diese Daten nicht korrekt gruppiert. Es muss ein ganzes Jahr dauern, bis Sie das Jahresfeld erhalten. Es könnte sein, dass es für dich funktioniert, ich bin ein bisschen ein Kontrollfreak, ich werde lieber nur Kontrolle haben, deshalb mache ich rückgängig. Es gibt: Ziehen Sie den Umsatz in den Wertebereich, wählen Sie eine beliebige Zelle, ein beliebiges Datum im Zeilenbereich und dann Gruppenfeld aus, wählen Sie Monate und Jahre,Ziehen Sie Jahre über den Spaltenbereich, klicken Sie mit der rechten Maustaste auf die Gesamtsumme, um sie zu entfernen, und erstellen Sie dann eine Varianz. Geben Sie die Formel ein und verwenden Sie weder die Maus noch die Pfeiltasten.
Ich habe keine Ahnung, wie ich es aussprechen soll, aber danke an diesen Leser (Александр Воробьев) für diesen Vorschlag und danke an Sie für Ihren Besuch. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von.
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast1998.xlsx
