KleinPivot (4) – Kleinbrot [ 2 ]

Kleinbrot aber Feinbrot

OK, das Wortspiel musste sein 8-). Denn fein wird das hier gewiss, insbesondere im Gegensatz zum vorherigen Teil. Noch einmal zur Auffrischung oder als Basis-Erklärung: Die Bäckerei Kleinbrot hat mit Excel monatliche Berichte erstellt, wo für jeden Tag des Monats in Spalte A das Datum eingetragen wurde und in Spalte B:G die Umsätze der verschiedenen Produktgruppen. Alle Monate wurden zu einer einzigen großen Tabelle zusammengefügt. Das stellte (und stellt) sich dann so dar:

Die Jahreszusammenfassung in Monate und Tage aufgeteilt.

Die Jahreszusammenfassung in Monate und Tage aufgeteilt.

Sie erkennen, dass Sonn- und Feiertage zwar in Spalte A aufgeführt sind aber da das Geschäft geschlossen war, wurde auch kein Umsatz gemacht. Die entsprechenden Felder bleiben leer. Und da das Jahr 2014 kein Schaltjahr war, gibt es 365 Zeilen mit Daten plus 1 Zeile für die Überschrift..

Diese Anordnung der Daten ist zwar in dieser Form üblich und auch übersichtlich, aber für eine vernünftige Pivot-Auswertung ausgesprochen ungeeignet. Im ersten Teil wurden diese Daten in der Anordnung für eine Pivot-Tabelle zwar genutzt, aber es gab doch so einige Hürden zu überwinden. Und die wenigen Möglichkeiten, die dort erarbeitet worden sind, streiften schon das mögliche Limit. Ganz zu schweigen von dem hohen Aufwand, der mitunter betrieben werden musste.

▲ nach oben …

Pivot-gerechte Umstrukturierung

Mit einem veränderten Aufbau der Daten in der Form, wie sie für eine PT vorgesehen ist werden Sie viel leichter, logischer und schneller zum Ziel gelangen. Sie erkennen, der Aufbau ist ganz simpel:

Die ersten 2 Tage in korrekter Anordnung

Die ersten 2 Tage in korrekter Anordnung

Drei Spalten, nicht mehr. Das Datum, die Produktgruppe und der Umsatz des entsprechenden Produkts an dem Tag. 6 Produkte je Tag umfassen 6 Zeilen. 2 Tage nehmen 12 Zeilen in Anspruch. Und weil an Tagen, die nicht geöffnet waren auch kein Umsatz generiert worden ist, tauchen diese kalendarischen Daten auch nicht in der Liste auf. Sie würden unnötig Platz wegnehmen. Insgesamt sind es 304 Tage á 6 Zeilen plus der Überschrift. Insgesamt 1.825 Zeilen.

Wir wollen Ihnen nicht die Chance nehmen, dass Sie auf welchem Wege auch immer selber die korrekte, eben gezeigte Basisdatei erstellen. Dazu brauchen Sie diese Datei, wo ausschließlich die an den 365 Tagen zusammengefassten Monatsumsätze der einzelnen Produktgruppen enthalten sind, also nicht die einzelnen Monate in getrennten Blättern. Und dabei gleich unsere Empfehlung: Nicht nur, damit Sie weniger Arbeit haben sondern auch der „Datenhygiene“ wegen: Tage, an denen keine Umsätze getätigt worden sind, gleich raus lassen. Da soll ja auch nichts ausgewertet werden. Und das sind nicht nur Sonntage, auch an Feiertagen war der Laden geschlossen.

Die einfachste Lösung für die Umwandlung der Liste mit den 365 Tagesumsätzen scheint auf den ersten Blick zu sein, einen externer Helfer einzusetzen. Stichwort: Praktikant bzw. Praktikantin. Aber das ist wirklich nicht fair und gewiss auch nicht angemessen. Es gibt doch wohl auch andere Wege, oder?

So ist es. Die eine Möglichkeit besteht darin, eine Formel einzusetzen. Die mögen wir uns hier an dieser Stelle aber nicht antun; bei einer anderen Gelegenheit werden wir das gewiss einmal „durchziehen“.  😉 Was aber rasch und wirklich transparent und grandios einfach funktioniert: Power Query. Ab Excel 2010 können Sie dieses Add-In einbinden und es hat -wenn Sie es einmal „intus“ haben- einen unbezwingbaren Charme und erleichtert die Arbeit in vielen Fällen enorm. Entweder in jedem einzelnen Monat oder in der zusammengestellten Jahresliste eingesetzt führt die Durchführung zu 12 größeren Datenblöcken, welche Sie dann zu einer einzigen kompakten Tabelle zusammenfügen oder in der 365-Tage-Tabelle gleich zu der einen, großen Liste.

Unsere Alternative heißt VBA. Mit einem Makro werden alle 12 einzelnen Blätter der Monate automatisch ausgewertet und die der Forderung entsprechenden Daten ohne Eingriff eines Benutzers in ein definiertes Tabellenblatt mit dem Namen Umsatz 2014 geschrieben. Die umsatzlosen Tage sind natürlich erst gar nicht in die Liste eingefügt worden, ansonsten gibt es -der Anforderung entsprechend- für jeden Tag 6 Zeilen mit einem der 6 Produktgruppen und dem jeweiligen Umsatz. Diese Tabelle (oder eine identische von Ihnen erstellte, welche auch dem Muster der obigen Abbildung entspricht) ist die Datenbasis für die künftige Pivot-Tabelle. In diesem Seminar werden Sie naturgemäß immer mit unseren Daten arbeiten, welche wir Ihnen auch stets zur Verfügung stellen. – Das soll Sie natürlich nicht daran hindern, mit Ihren eigenen Daten zu experimentieren.  😀 

▲ nach oben …

Erstellen der Pivot-Tabelle

Die ersten Schritte entsprechen denen, die Sie bereits kennen. Sie klicken in ein beliebiges Feld der eben erstellten Datenbasis oder laden Sie diese Datei von unserem Blog. Klicken Sie in den Datenbereich und wählen Sie im Menü den Weg, eine neue Pivot-Tabelle zu erstellen. Kontrolle, ob der komplette Bereich auch von der „Ameisenkolonne“ eingefasst wird und im Dialogfenster OK anklicken. Und ab jetzt wird es erheblich komfortabler als Sie es im ersten Teil kennen gelernt haben …

Auf den ersten, aber spätestens beim zweiten Blick wird Ihnen auffallen, dass die Feldliste enorm geschrumpft ist:

Die Feldliste des neuen Tabellenaufbaus

Die Feldliste des neuen Tabellenaufbaus

Nur noch drei Felder, erstaunlich. Wo sind die einzelnen Produkte geblieben? Um das herauszubekommen ziehen Sie hier das Feld Produktgruppe nach unten in den Bereich SPALTEN. Überraschung, aber eine positive. In den Spalten A:G sind alle 6 Produkte aufgeführt. Und das mit jeweils dem Namen, wie er in der Quelldatei verwendet worden ist. Das ist gegenüber der ersten Version schon einmal eine enorme Erleichterung. Und alleine das ist schon ein Beleg dafür, dass die Umstrukturierung der Basisdaten in die neue Form durchaus sinnvoll ist.

Als nächstes soll das Datum in die Zeilen. Ganz normal nach unten ziehen und alle 12 Monate stehen in Spalte A, die Produkte sind um 1 Spalte verschoben worden. Jetzt noch den Umsatz in den Bereich WERTE und das war’s. Sie sind mit diesen wenigen Mausklicks mit Ausnahme der Formatierung als Währung auf dem Stand, der für Teil 1 als Ziel vorgegeben war. Selbst die Summen für den einzelnen Monat, welche wir in der ersten Auflage über ein berechnetes Feld erstellen mussten, sind hier automatisch eingefügt worden. So macht das arbeiten Spaß!

▲ nach oben …

Erste Formatierung

Bleibt noch die Formatierung als Währung. Und auch das ist nun ein Kinderspiel geworden. Klicken Sie mit rechts in irgendeine Umsatzzahl und wählen Sie dann aus dem Kontextmenü die Wertfeldeinstellungen. Unten links in dem neuen Fenster ist die Schaltfläche Zahlenformat. Ein Klick darauf und es öffnet sich das typische Formatierungsfenster. Nur dass hier ausschließlich das Register für Zahlen eingeblendet ist. Ein Klick auf Währung und nacheinander in beiden Fenstern OK. Fertig, genau so sollte es sein.


KleinPivot Übersicht (Grau: In Vorbereitung)

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

▲ nach oben …

Dieser Beitrag wurde unter Musterlösungen, Ohne Makro/VBA, Pivot, Tabelle und Zelle abgelegt und mit , , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.