Kleinbrot-Pivot, kalendarische Auswertung (1)
Zu Beginn laden Sie bitte diese Datei mit den Umsätzen der Bäckerei Kleinbrot, welche pro Tag je einen Eintrag für jede Produktgruppe enthält. Zur Erinnerung: Das sind die Überschriften und 1.824 Zeilen mit Daten. Das Ziel ist es, im Hinblick auf die kalendarischen Daten verschiedene Möglichkeiten der Auswertung darzustellen.
Vorbereitung
Um die Leistungsfähigkeit der PivotTables zu unterstreichen, sollen die schön geordneten Umsätze einmal richtig durcheinander „gewürfelt” werden. Und natürlich soll das Ganze als Intelligente Tabelle eingerichtet sein. Im ersten Schritt einfach einmal StrgPos1, um zu A1 zu gelangen. Damit ist gewährleistet, dass die aktive Zelle auf jeden Fall im Datenbereich ist. Nun StrgT oder StrgL, um die Intelligente Tabelle zu erstellen. Ein kurzer Kontroll-Blick, ob der gesamte Bereich der Daten im entsprechenden Textfeld angezeigt wird. Außerdem muss das Häkchen gesetzt sein, dass die Tabelle Überschriften hat. Nach einem OK ist umgehend die typische tabellarische Darstellung, meist in verschiedenen Blautönen, zu sehen. Damit ist der erste Schritt erledigt.
Klicken Sie nun in D2 und geben Sie folgende Funktion ein: =ZUFALLSZAHL()
und Sie werden erkennen, dass ruck zuck auch eine Überschrift in D1 eingetragen wird und die Formel automatisch bis zum letzten Eintrag in Zeile 1825 kopiert wurde. Um zu verhindern, dass immer und immer wieder alles in dieser Spalte neu berechnet wird, werden Sie die per Funktion berechneten Zahlen sofort in statische Werte umwandeln. Ein Klick in A2, dann StrgShift↓ um alle berechneten Werte zu markieren. Nun den markierten Bereich auf beliebigem Wege in die Zwischenablage kopieren, beispielsweise per StrgC. Direkt danach ohne die Markierung zu ändern ein Rechtsklick und im Kontextmenü bei den Einfügeoptionen das Symbol für die Werte wählen. Fertig, jetzt sind wunschgemäß keine Formeln mehr in Spalte D sondern reine, statische Zahlenwerte.
Sie könnten jetzt zwar noch diese Überschrift nach Belieben anpassen, aber es lohnt nicht. Sie werden gleich sehen, dass es unnütz wäre. Sortieren Sie nun die Spalte D auf- oder absteigend, wie Sie mögen. Dazu Klicken Sie auf die Schaltfläche ▼ rechts in D1 und Klicken auf den ersten oder zweiten Punkt in der Auswahl.
Jetzt haben Sie eine Reihenfolge, wie sie gewiss nie in der Realität vorkommen würde aber zur Demonstration der Leistungsfähigkeit einer PT ist solch ein Durcheinander recht gut geeignet. Damit hat die Spalte D ihren Zweck erfüllt und Sie können sie auf beliebigem Wege löschen. Vorzugsweise ein Klick auf den Spaltenkopf D, damit die gesamte Spalte markiert wird. Dann ein Rechtsklick und Zellen löschen. Das war’s dann auch und die Vorbereitungen sind erledigt.
Erstellen der PivotTabelle
Achten Sie darauf, dass eine beliebige Zelle innerhalb der Intelligenten Tabelle markiert ist. Dann Klicken Sie im Menü ganz rechts auf den Punkt TABELLENTOOLS oder direkt darunter auf ENTWURF. Sofort wird sich die Ribbon-Leiste, das Menüband ändern. Links in der Gruppe Tools ist die Auswahl Mit PivotTable zusammenfassen zu sehen. Sie Klicken ein Mal darauf und ohne Verzögerung wird Ihnen das bekannte Fenster zur Erstellung der PivotTabelle angeboten. Übernehmen Sie die Vorgaben, sie sind dank der Tabelle korrekt.
In dieser noch leeren Auswertung setzen Sie bei den PivotTable-Feldern bei allen Kästchen in der Reihenfolge von oben nach unten ein Häkchen. Das Ergebnis stellt sich dann so dar:
Der Steuerungs-Bereich der Pivot-Auswertung hat diese Anordnung:
Eine ewig lange Tabelle … Wenn Sie hier einmal StrgEnde betätigen werden Sie sehen, dass das mit dem Gesamtergebnis sage und schreibe 2.132 Zeilen sind. Das ist ja nun wirklich nicht übersichtlich. Wenn schon jeder einzelne Tag, an dem Umsatz erwirtschaftet wurde hier aufgeführt wird dann sollte das auf Dauer gesehen möglichst schon im 300er-Bereich liegen.
Mehr Übersicht
An dieser Stelle sei einmal Grundsätzliches angemerkt: Wenn irgendeine hier im Script geforderte Aktion die PT betrifft, dann muss zwingend eine Zelle im Datenbereich der PivotTable markiert sein. Sie erkennen das auch daran, dass im oberen Menü rechts der (normalerweise) pink-farbig hinterlegte Bereich für die PivotTable-Tools sichtbar ist.
Einen ersten Schritt zum Ziel der besseren Übersichtlichkeit erreichen Sie, wenn Sie im PT-Menü den Menüpunkt ENTWURF anklicken und in der Gruppe Layout die Schaltfläche Berichtslayout anklicken und dann die dritte Möglichkeit In Tabellenformat anzeigen auswählen. Es sind zwar immer noch mehr als 2.000 Zeilen, aber so ist es doch um einiges übersichtlicher als vorher. Und spätestens jetzt sollte Ihnen aufgefallen sein, dass ohne Ihr Zutun die „bunte Reihe” der kalendarischen Daten wohl geordnet erscheint, von Anfang Januar bis Ende Dezember. 🙂
Etwas „Kosmetik”
Belassen Sie es erst einmal dabei, was die Zeilenzahl betrifft. Die eine oder andere kosmetische Korrektur sollte nun vorgenommen werden. Beginnen wir mit den Überschriften. Datum ist okay, kann aber in der Spaltenbreite etwas angepasst werden. Ein Doppelklick auf den Spaltentrenner A|B und die optimale Breite ist eingestellt. Produktgruppe können Sie so lassen oder in Produkte umbenennen. Summe von Preis sollten Sie in Umsatz umbenennen. Preis (ohne zusätzliche Zeichen) geht nicht, da diese Bezeichnung dem Feldnamen entspricht und der muss einmalig sein. „Preis ” mit angehängtem Leerzeichen würde aber akzeptiert werden.
Eigentlich sieht das ja schon ganz vernünftig aus. Eigentlich…, denn schauen Sie sich einmal C15 und C24 an. Diese Berechnungen werden recht „platzsparend” dargestellt, nur eine bzw. gar keine Nachkommastelle. Und überhaupt, die Währungsbezeichnung sollte schon sein. Klicken Sie dazu in die Zelle C3 und im PT-Menü auf den Untermenüpunkt ANALYSIEREN. In der Gruppe Aktives Feld (2. Gruppe von links) ein Klick auf die Feldeinstellungen.
Im geöffneten Fenster Wertfeldeinstellungen Klicken Sie gleich auf Zahlenformat und wählen dort die Währung aus. OK und nochmals OK und nun bleibt eigentlich nur noch übrig, auch die Spalte C auf die optimale Breite anzupassen.
Noch mehr Übersicht
Wenn Sie die Tages-Ansicht bzw. ‑Übersicht brauchen, dann können Sie die Übersichtlichkeit in zwei Varianten verändern. Möglichkeit 1: Sie belassen es bei diesem grundsätzlichen Aufbau der vielen Zeilen. Dann werden es sogar noch mehr Zeilen, weil Sie nach jedem Tag eine Leerzeile einfügen. Probieren sollten Sie es auf jeden Fall einmal, auch wenn Sie es speziell hier nicht anwenden werden:
- Menü PT-TOOLS | ENTWURF
- Gruppe Layout, Symbol Leere Zeilen
- Oberste Auswahl Leerzeile nach jedem Element einfügen anklicken.
Das sieht schon einmal sehr viel übersichtlicher aus. Eine „schlanke” Darstellung und jeder Tag schön separiert. Aber eben der Nachteil, dass es nach wie vor sehr, sehr viele Zeilen sind. Das Ziel war ja, in die Nähe der 300 Zeilen zu kommen.
Der aller, allereinfachste Weg dahin: Entfernen Sie das Häkchen bei der Produktgruppe in den PT-Feldern. Schon ist die Tabelle auf 308 Zeilen geschrumpft. Der Preis dafür ist, dass nur die Gesamt-Umsätze der einzelnen Tage angezeigt werden, nicht jedoch die Produkte.
Das kann in Einzelfällen erwünscht sein, aber es sollte doch einen Weg geben, die Produkte einzeln darzustellen und dennoch im 300er-Bereich zu bleiben. Und natürlich gibt es den auch. 😎 Der Weg dahin ist beispielsweise so:
- Das Häkchen bei der Produktgruppe in den PT-Feldern muss gesetzt sein.
- Ziehen Sie nun das Feld Produktgruppe aus dem (unteren) Bereich ZEILEN in den Bereich SPALTEN.
Fertig, die letzte Zeile ist die 309. In der Realität sind es ja sogar noch zwei Zeilen weniger, da ja die ersten beiden Zielen leer sind. Bei den bisherigen Berechnungen haben wir das einfach ignoriert und werden es auch weiterhin tun, da Sie sonst immer umrechnen müssten. Außerdem wird beim Drucken auch der oberste Bereich mit erfasst, solange nicht ein spezieller Druckbereich festgelegt worden ist.
Noch mehr Kosmetik
Irgendwie ist das aber so eine „Zahlenwüste”. So richtig übersichtlich ist das immer noch nicht. Eine Formatierung ähnlich der Intelligenten Tabelle wäre hilfreich. Leider aber ist diese Form noch nicht als Formatvorlage integriert. Also müssen wir von Hand ran.
- Klicken Sie in die Zelle A5
- StrgShift↓, die Markierung geht dann bis zur Zeile 309.
- Immer noch oder erneut Shift aber ohne Strg und ein Mal ↑, damit Zeile 308 die letzte markierte Zeile ist.
- Tastenkombination StrgShift→, dann ist der Bereich A5:H308 markiert
- Über das Menü Start in der Gruppe Formatvorlagen die Bedingte Formatierung aufrufen.
- Neue Regel… und die unterste Auswahl Formel… anklicken.
- Als Formel geben Sie ein:
=ISTGERADE(ZEILE(A5))
und als Formatierung haben wir ein helles Grün im Register Ausfüllen gewählt, um alle geraden Zeilen hervorzuheben. - Je nach Geschmack können nun noch beispielsweise A5:A308 und H5:H308 in Fett und/oder kursiv hervorgehoben werden.
- Klicken Sie nun auf A5 und Menü ANSICHT | Fenster | Fenster fixieren | Fenster fixieren.
Mit dem letztgenannten Punkt erreichen Sie, dass die Überschriftszeile auch dann noch sichtbar ist, wenn Sie nach unten scrollen und der 2. Januar nicht mehr sichtbar ist:
Diesen Stand der Dinge werden Sie vielleicht später noch einmal brauchen. Darum kopieren Sie dieses Blatt auf eine Ihnen genehme Art und arbeiten Sie ab sofort mit der Kopie weiter. Zur Vorsicht können Sie Ihr Werk schon einmal unter einem anderen Namen speichern.
Monatliche Auswertung
Beginnen Sie mit einem Rechtsklick auf A5. Wählen Sie dort den Punkt Gruppieren… und es wird sich dieses Fenster auftun:
Die vorgeschlagene Auswahl ist passend, übernehmen Sie diese mit einem OK, nachdem Sie sich vorher die einzelnen Möglichkeiten verinnerlicht haben. Vom Prinzip her sieht das ja schon recht gut aus. Was nun aber stört, ist die Bedingte Formatierung. Die ist irgendwie nicht den neuen Verhältnissen angepasst. Darum löschen Sie die Regeln der BF im gesamten Blatt. B4:H4 rechtsbündig ausrichten macht vielleicht einen besseren Eindruck. In B3 eventuell die Bezeichnung auf Produkte ändern und dann noch die Spalten B:H auf die optimale Breite einstellen.
Überraschung!
Weiter oben hatten wir ja schon einmal die Produkte ausgeblendet, indem Sie das Häkchen bei den PT-Feldern entfernt hatten. Probeweise und des Versuchs an sich wegen sollten Sie das auch noch einmal machen. – Und anschließend gleich wieder das Häkchen setzen. Und nun wissen Sie, wie die Überschrift dieses Absatzes gemeint ist. Es wird nicht der direkt vorhergende Zustand wiederhergestellt sondern der ursprüngliche. Sie hätten zweckmäßigerweise das Feld gleich in den Bereich SPALTEN ziehen können oder Sie ziehen es nun dort hin. Wie gut, dass das so wenig Aufwand ist. 😎
Quartale
Mitunter ist eine quartalsweise Auswertung von Daten wünschenswert oder erforderlich. Eventuell ist Ihnen eben schon aufgefallen, dass es neben den Monaten auch noch die Quartale als Gruppierungs-Kriterium gibt. Also ein Rechtsklick in A4 oder auch in eine der Zellen A5:A16, wo ja ein Datum im Format MMM drin steht und natürlich Gruppieren… auswählen. Klicken Sie nun auf Quartale und es wird eine neue Spalte mit den Quartalen eingefügt. Sollen ausschließlich die Quartale angezeigt und natürlich berechnet werden, dann Klicken Sie im Gruppieren-Fenster einfach auf die Markierung Monate, dadurch wird die Selektion aufgehoben.
Eigentlich wäre eine Anzeige prima, welche beide Berechnungen beinhaltet. Also die Ergebnisse alle einzelnen Monate und die Summen der Quartale. Auch das lässt sich relativ problemlos erreichen. Klicken Sie im Menü PT-TOOLS | ENTWURF Gruppe Layout auf das allererste Symbol, Teilergebnisse. Da die Zwischensummen sinnvollerweise jeweils am Ende des Quartals angezeigt werden sollen, ist die zweite Auswahl die passende. Und wenn Sie nun noch über Leere Zeilen nach jedem Element eine Leerzeile einfügen lassen, sieht das richtig schick aus:
Sie haben nun einen kleinen Überblick bekommen, was in Sachen Datum und PT möglich ist. Zugegeben, es ist nur ein kleiner Ausschnitt des Machbaren, aber eine solide Grundlage. Im nächsten Kapitel werden diese Daten auf der Basis von Kalenderwochen ausgewertet. Dazu aber sollten Sie das bis hierher gelernte verinnerlicht haben.
Speichern Sie nun idealerweise Ihre Arbeit, damit Sie das Ganze später noch einmal nachvollziehen können.
KleinPivot Übersicht
KleinPivot (1) | Ausführliche Grundlagen, Basis „Kleinbrot” |
KleinPivot (2) | Selbstständige Übung, Basis „Bundestag” |
KleinPivot (3) | Aufbau auf (2), nur Teildaten auswerten (filtern) |
KleinPivot (4) | Kleinbrot [2], Aufbau auf KleinPivot (1) |
KleinPivot (5) | Schulnoten und mehr [1], Noten, Fächer, Daten |
KleinPivot (6) | Schulnoten und mehr [2], Klassenschnitt der Fächer |
KleinPivot (7) | Schulnoten und mehr [3], Jungen vs. Mädchen |
KleinPivot (8) | Schulnoten und mehr [4], Notenvergleich der Kategorien |
KleinPivot (9) | Schulnoten und mehr [5], Kalendarische Auswertung |
KleinPivot (10) | Kleinbrot [3], Kalendarische Auswertung (1) |
KleinPivot (11) | Kleinbrot [4], Kalendarische Auswertung (2) |
KleinPivot (12) | Kleinbrot [5], PivotChart (1) |
KleinPivot (13) | Kleinbrot [6], PivotChart (2) |
KleinPivot (14) | Kleinbrot [7], Datenschnitt und Zeitachse | Epilog |