Bäckerei Kleinbrot, Pivot-Auswertung
Vorab angemerkt
Zugegeben, der oberste Titel („KleinPivot”) ist vielleicht etwas irreführend aber er soll auch das Interesse der Leser wecken, die sich bislang nicht an Pivot-Tabellen heran gewagt haben und vielleicht ein kleines, leicht nachzuvollziehendes Beispiel suchen. Das Ziel dieses Seminars (und dieser Beiträge) ist es nicht, Ihnen möglichst umfassend und in kurzer Zeit Pivot-Tabellen zu vermitteln. Der Sinn und Zweck ist eher das Gegenteil des eben gesagten. Sehr kleine Schritte, gut nachvollziehbare Beispiele und eine überschaubare Menge an Stoff, der vermittelt werden soll.
Weniger ist manchmal mehr. Darum ist dieser erste Beitrag auch nicht für Leser gedacht, die schon Grundwissen in Sachen Pivot haben und nur mal schauen wollen, was wir denn dazu zu sagen haben. Er ist wegen der ausführlichen Beschreibung prinzipiell an Einsteiger in diesem Bereich gerichtet, die ernsthaft eine solide Basis suchen und auch wissen, dass dieses nur ein Vehikel sein kann, den Appetit auf mehr zu wecken.
Andererseits ist dieses erste Kapitel ausgesprochen voluminös, sehr umfangreich. Auch das Beispiel der Bäckerei Kleinbrot ist nicht unbedingt das typische Beispiel für den Einstieg in diese interessante Möglichkeit der Datenauswertung. Warum wir dennoch dieses Beispiel gewählt haben? Uns ist es wichtig, dass Sie möglichst von Anfang an erkennen, ob Daten für eine Auswertung mit einer PivotTabelle (PT) geeignet ist oder noch mehr oder weniger nachgearbeitet werden muss. Hier werden Sie als Wichtigstes lernen Grenzen zu erkennen, um unnötige Arbeit zu vermeiden.
Auf der anderen Seite werden Sie hier auch eine PT aus scheinbar ungeeigneten Daten erstellen. Mitunter reichen die beschränkten Möglichkeiten einer typischen, vielspaltigen Tabelle als Quelldatei vollkommen aus, um das gewünschte Ziel zu erreichen. Sehen Sie aber in erster Linie, wie eine PivotTable aufgebaut wird und langsam Schritt für Schritt Kontur annimmt.
Die Daten
Der Ursprung der Daten ist eine Tabelle aus unseren Seminaren. In dieser Jahreszusammenfassung sind einige Auswertungen gemacht worden, welche für die betriebliche Übersicht eine wichtige Basis bilden.
Die ursprünglichen Daten (Material aus unseren Schulungen)
Solch eine Tabelle wäre zwar auch für eine Pivot-Auswertung denkbar, aber im Prinzip ist dort schon viel zu viel zusammengefasst. Solch eine Darstellung kann (und wird) das Ziel einer Auswertung sein, die sich auf die Grund-Daten bezieht. Und das sind die täglichen Umsätze. Dieses (obige) Tabellenblatt dient also nur dem Vergleich, der Orientierung. In der typischen Arbeitsumgebung gibt es diese Übersicht, diese Zusammenfassung noch gar nicht sondern nur einzelne Datensätze wie beispielsweise monatliche Tagesberichte.
Wir haben aus diesem Grunde eine neue Mappe geschaffen, wo die hier aufgezeigten Monats-Umsätze in Tagesumsätze der einzelnen Produkte aufgeschlüsselt worden sind. Per Zufallszahl und der Berücksichtigung der Öffnungstage wurden für jeden Monat solche Tabellenblätter generiert:
Die Jahreszusammenfassung in Monate und Tage aufgeteilt.
Da diese Bäckerei weder an Sonn- noch an Feiertagen geöffnet hat, wurden die entsprechenden Zeilen ab Spalte B ohne Inhalt gelassen. Aus buchhalterischer Sicht wäre es möglich gewesen, eine Null dort einzutragen, aber so werden Sie später einen Effekt kennen lernen, welcher typisch für die Pivot-Tabellen ist.
In dieser Datei finden Sie auf dem Arbeitsblatt Tabelle1 die zuerst angesprochene Jahresübersicht und anschließend die Blätter mit den monatlichen Daten. Damit ist die Basis für die kommende Arbeit gelegt. Und Sie werden rasch erkennen, dass mit einer so detaillierten Datenbasis verschiedene Möglichkeiten der Auswertung gibt.
Das letzte Tabellenblatt Feiertage ist für die Auswertung nicht relevant. Wir haben diese Tabelle genutzt, um nicht nur Sonntage sondern auch die gesetzlichen Feiertage als „geschlossen” zu behandeln. Und wir haben das Blatt nicht aus dieser Mappe entfernt, damit Sie einmal nachsehen können, wie mit recht einfachen Mitteln eine Berechnung der beweglichen Feiertage möglich ist. Diese Dynamische Tabelle hat den Vorteil, dass sie über ihren Namen ansprechbar ist und der Bereich wird automatisch angepasst, wenn Sie Zeilen einfügen oder entfernen. Wenn Sie in einem GMG-CC Seminar sind, dann werden wir Ihnen auf Wunsch auch gerne aufzeigen, wie solch eine Feiertagstabelle sinnvoll eingesetzt werden kann.
Vorbereitung
Es wäre zwar mit Tricks und Umwegen möglich, die einzelnen in dieser Form vorliegenden Tabellen für eine Pivot-Auswertung zu nutzen, aber einerseits sollen hier Grundlagen vermittelt werden und andererseits widerspräche das dem eigentlich vorgesehenen Weg für die Erstellung einer Pivot-Tabelle. Standardmäßig wird genau 1 Tabelle für die Auswertung erwartet und genutzt.
Für die Zusammenfassung der Daten aus den Monatsberichten ist es erforderlich, dass ein neues Tabellenblatt erstellt wird. OK, es wäre auch möglich, dass die Tabelle Januar als Grundlage dient und ständig erweitert wird, aber das ist unserer Meinung nach unschön. Originaldaten, die Datenquellen sollten so erhalten bleiben, wie sie sind.
Wir haben Ihnen diese Datei so vorbereitet, dass das erste Blatt nach Dezember bereits eine formatierte Überschriftzeile enthält. Direkt darunter kopieren Sie dann per Hand die Daten der einzelnen Monate. Nur die Daten, nicht die Überschriften. Dabei können Sie in beliebiger Reihenfolge vorgehen, selbst gemischt. Es dürfen allerdings keine (kompletten) Leerzeilen in der Aufstellung sein. Nur eines ist noch wichtig: Jeder Monat eines Jahres darf nur ein Mal in das entsprechende Blatt eingefügt werden.
Wir empfehlen Ihnen, das Procedere einmal durchzuführen. Es übt ungemein, und bei Ihren eigenen Dateien wird das wahrscheinlich in jedem Fall unumgänglich sein. Für diese Musterdaten haben wir Ihnen eine kleine Hilfe erstellt. Per VBA/Makro wird die leere Tabelle mit den entsprechenden Daten aufgefüllt. Hier der kurze Code:
Sub TabellenZusammenfuehren()
Dim wks As Integer
Dim wksDst As WorkSheet
Dim lRowSrc As Integer, lRowDst As Integer
On Error GoTo ErrorHandler
Application.ScreenUpdating = False
Set wksDst = Sheets("Jahres-Zusammenfassung")
For wks = 2 To 13
lRowSrc = Sheets(wks).Cells(Rows.Count, 1).End(xlUp).Row
lRowDst = wksDst.Cells(Rows.Count, 1).End(xlUp).Row
Sheets(wks).Range("A2:G" & lRowSrc).Copy wksDst.Cells(lRowDst + 1, 1)
Next wks
ErrorHandler:
Application.ScreenUpdating = True
End Sub
Die Datei mit dem bereits eingebundenen Code können Sie hier herunter laden. Wenn Sie eine Hilfestellung brauchen, wie Sie diese Zeilen in ein eigenes Projekt einbinden können, dann schauen Sie beispielsweise an dieser Stelle nach. Nach einem Klick auf die Schaltfläche (die nur in der eben herunter geladenen Datei ist) wird das Makro ausgeführt und ruck zuck ist die Jahres-Zusammenfassung mit allen Daten gefüllt.
Und jetzt eine zwar nicht bittere aber auch nicht gerade wohlschmeckende Pille: So, wie die Daten im Moment vorliegen, können sie nur mit Einschränkungen genutzt werden. Wir haben bewusst diese Form des Monatsberichts gewählt, weil solch ein Aufbau typisch ist. Sie lernen hier, was auf dieser Basis machbar ist aber auch, wo die Grenzen sind. Wir halten es für wichtig, dass Sie auch mit einer suboptimalen Datenbasis ein Ergebnis zustande bringen können. Im Folgeteil dieser Abhandlung werden Sie dann lernen, wie die Grunddaten beschaffen sein müssen, um wirklich alles aus Excel herauszuholen. Dennoch ist dieser Abschnitt wichtig, sehen Sie ihn als erstes Lehrjahr an 😆 . Denn nur wenn Sie die Unterschiede zum arbeiten mit einer optimalen Datenbasis kennen werden Sie die Mühe auf sich nehmen, vorhandene Datenbestände „sauber” für eine PT aufzubereiten.
Erste Schritte in Sachen Pivot-Tabelle
Noch einmal zur Erinnerung: Alle Abbildungen und auch Menüpunkte, etc. beziehen sich auf Excel 2013. Abwärts ist bis zur Version 2007 vieles sehr ähnlich, weiter zurück (beispielsweise Excel 2000 bis zur Version 2003) ist sehr viel ganz anders. Das sind nicht nur die komplett anderen Menüpunkte sondern auch die Optik der leeren Pivot-Tabelle ist eine andere. Mit viel Geduld und etwas Experimentierfreude wird es Ihnen unter Umständen auch in diesen Versionen gelingen, das Ganze nachzuvollziehen. Im Falle eines Falles fragen Sie einfach bei uns an, unser Partner GMG-CC hilft Ihnen dann gerne kostengünstig weiter.
Let’s begin with the beginning. Sie befinden sich in dem Tabellenblatt Jahres-Zusammenstellung und die Daten aller Verkaufstage sind dort aufgelistet. Eine wichtige Vorbereitung ist eine Prüfung, ob es zumindest in Spalte A keine Leerzeilen gibt. Dazu Klicken Sie zuerst einmal in A1. Danach entweder Strg↓ oder Sie führen einen Doppelklick auf den unteren Rand der Zellumrandung aus (der Tipp ist hier näher beschrieben). In beiden Fällen wird Excel in die erste freie Zelle der Spalte A springen. Danach dürfen dann auch keine relevanten Daten mehr kommen. Zurück zur ersten belegten Zelle geht es (per Tastatur) genau umgekehrt: Strg↑. Idealerweise befinden Sie sich dann wieder in A1.
Die aktive bzw. ausgewählte Zelle ist nun irgendwo im Datenbereich, also innerhalb A1:G366. Das ist wichtig. Klicken Sie nun auf das Menü-Register Einfügen und dort auf die erste Schaltfläche Pivot Table. Excel wird nun einen Laufrahmen um den gesamten zusammenhängenden Datenbereich anzeigen und dieses Fenster öffnen:
Datenbereich und Zielort für die neue Pivot-Tabelle festlegen
Achten Sie darauf, dass der Bereich wirklich alle Daten umfasst. Sind es weniger Zeilen oder Spalten, dann ist mit Sicherheit doch noch irgendwo eine Leerzeile oder eine komplett leere Spalte als trennendes Element erkannt worden. Diese Zeile(n) und/oder Spalte(n) müssen Sie nach einem Abbruch der Aktion entfernen und anschließend den Vorgang neu starten. Übernehmen Sie die restlichen Einstellungen wie sie auch in der Abbildung zu sehen sind und auch der Vorgabe entsprechen. Bevor Sie OK anklicken, setzen Sie sich bitte mit den einzelnen Möglichkeiten des Dialogfensters auseinander. Sie brauchen es nicht im Moment aber vielleicht später einmal. Excel legt nun nach dem OK selbstständig eine neues Arbeitsblatt an und wechselt auch automatisch dort hin:
Die noch leere Pivot-Tabelle
Der Name des Tabellenblattes ist stets Tabellen, wobei das n die logische Folgenummer der bisherigen Nummerierung ist. Machen Sie sich bitte erst einmal mit dem Aufbau des Blattes vertraut. Beginnend in A3 wird künftig der Datenbereich sein, welcher den internen Namen PivotTable1 bekommt. Rechts neben dem eigentlichen Tabellenbereich ist ein Bereich, den ich des besseren Verständnisses wegen als Verwaltung tituliere. Sie erkennen dort alle vorhandenen Felder mit den von Excel vergebenen Feldnamen. Darunter erkennen Sie u.a. vier Bereiche mit den Namen FILTER, SPALTEN, ZEILEN,WERTE.
Klicken Sie nun im Bereich der Felder (ober Hälfte der Verwaltung) auf das Kästchen Datum. Das Häkchen wird gesetzt und sofort werden alle Einträge beginnend in A4 in die Tabelle eingefügt. In A3 wird automatisch eine Überschrift erstellt, welche Sie (später) auch fast nach Belieben ändern können:
Die Spalte mit dem Datum ist nun eingefügt
Eine kurze Prüfung, ob wirklich alle Tage übernommen worden sind, sollte sein. Dabei werden Sie auch feststellen, dass in Zeile 369 eine farblich hervorgehobene Beschreibung Gesamtergebnis steht.
So einfach kann PT sein. Zumindest bei den Grundlagen. Aber es gilt dennoch, dass zwar viele Wege nach Rom führen, manche aber doch mehr oder weniger holprig sind. Das Prinzip aber ist sehr oft das gleiche. – Zugegeben, so sehr aussagekräftig ist diese Auflistung nun noch wirklich nicht und es lässt sich gewiss noch kein Vorteil gegenüber der herkömmlichen Arbeitsweise erkennen. Das wird sich rasch ändern …
Weiter oben klang ja schon einmal an, dass auf Basis der in Monatsberichten zusammengefassten Tagesumsätze eine PT erstellt werden soll, die vom Charakter her jenem Aufbau entspricht, der in Tabelle1 zu sehen ist. Die Überschrift ist nicht so wichtig, aber in Spalte A sollen die kalendarischen Daten stehen, unter dem Monat Dezember die Gesamtsumme und in den Spalten B:G die einzelnen Warengruppen. Das ist die Grundstruktur, welcher Sie sich Schritt für Schritt nähern werden. Beginnen Sie damit, dass Sie in der Verwaltung im Feld-Bereich auf die erste Warengruppe Brot Klicken:
Umsätze für Brot per Klick eingefügt
Na gut, die täglichen Umsätze für das Brot wurden in der Tat in die PT eingefügt. Allerdings nicht da, wo sie eigentlich stehen sollten. Sie gehören in Spalte B. Und wenn Sie sich einmal im Verwaltungs-Bereich ganz unten orientieren, dann werden Sie feststellen, dass das Feld Brot auch automatisch bei den ZEILEN mit eingefügt worden ist. Was liegt also näher, als …
Das Feld Brot zu den Spalten verschieben
… das Feld zu verschieben. Einfach mit der Maus anfassen und in den passenden Bereich ziehen. Und die Umsätze für das Brot stehen dann auch in Spalten, aber ganz anders als gedacht oder vorgesehen:
Ungewollte Anordnung der Zahlen
Schön geordnet nach Umsatzhöhe stehen dort 172 Umsatzzahlen nebeneinander, bis hin zur Spalte FQ. Das war ja nun doch nicht der Sinn der Sache. Vielleicht wundert es Sie, dass wir Sie erst einmal auf diesen Holzweg geführt haben. Nun, das mache wir öfter. Denn Fehler sind dazu da, gemacht zu werden und dann daraus zu lernen. 💡 Und früher oder später werden Sie auch ohne dieses „Vorbild” einmal den eigentlich logischen oder scheinbar einfachen Weg einschlagen. Und vielleicht erinnern Sie sich dann „ganz leise”: „Da war doch mal etwas…”.
Sie haben erkannt, dass auch das nicht der richtige Weg ist. Zugegeben, viel Auswahl bleibt auch nicht mehr über. Ziehen Sie nun das Feld in den Bereich WERTE:
Das Brot in den Bereich Werte verschoben
Ah ja, alle in Spalte B. Aber irgend etwas ist da immer noch nicht so, wie vorgesehen. Es wurde ja schließlich nicht an alle Verkaufstagen nur für 1 € Brot verkauft. Die Irritation ist schnell aufgelöst, wenn Sie sich die Überschrift in Zeile 3 ansehen: Anzahl von Brot. Und damit ist die 1 durchaus plausibel. Pro Tag gibt es ja nur 1 Position für das Brot. Würde ein Datum 2 Mal aufgeführt (beispielsweise für eine Filiale) dann wären es 2 Positionen. Dabei ist es nicht relevant, ob die Felder einen Inhalt haben oder nicht.
Um nicht die Anzahl sondern den berechneten Wert der Felder anzuzeigen, Klicken Sie auf das eben verschobene Feld in der Verwaltung und darüber öffnet sich ein Fenster:
Auf die Wertfeldeinstellungen Klicken
Dort nun ein Klick auf die Wertfeldeinstellungen… und ein neues Fenster mit einer Auswahl von möglichen Berechnungen erscheint:
…und die Summe der einen Position für das Brot pro Tag bilden
Auch wenn es in diesem Fall nur eine Position ist, es soll die Funktion der Summe verwendet werden. Nach einem OK werden Sie gewiss zufrieden lächeln, denn das Ziel ist trotz der Umwege (endlich) erreicht.
Für die restlichen Produktgruppen können Sie einen etwas direkteren Weg gehen:
Das Feld Brötchen direkt in den Werte-Bereich ziehen
Ziehen Sie das Feld für die Brötchen (und danach natürlich auch die weiteren Felder) direkt in den Bereich WERTE. Sie brauchen, nein Sie sollten es erst gar nicht mit einem Häkchen versehen, das würde ja wieder dazu führen, dass das Feld direkt in den Bereich ZEILEN verschoben wird. Die grau-grüne Linie zeigt Ihnen dabei die Position an, wo das Feld dann abgelegt wird. So können Sie noch Änderungen in der Reihenfolge vornehmen, indem Sie die Produkte innerhalb des Bereichs ∑ WERTE verschieben.
Jetzt müssen nur noch die Berechnungseinstellungen geändert werden. Und da stellt sich doch die Frage, ob es eine Möglichkeit gibt, Excel von vornherein dazu zu bewegen, nicht die Anzahl sondern die Summe zu berechnen. Es wird Sie erstaunen: Excel wird in einer PT automatisch die Funktion SUMME verwenden, wenn in der Spalte unterhalb einer Überschrift nur Zahlen sind. Da drängt sich doch förmlich die Frage auf: „Und warum hier nicht?” Die Antwort ist nach einer kurzen Recherche klar: Es sind nicht nur Zahlen in der Spalte, es gibt auch Leerzellen. An Sonn- und Feiertagen ist das Feld leer. Auch wenn Excel vielfach eine leere Zelle als mit dem Inhalt Null betrachtet und auch bewertet, speziell hier gilt das in Sachen voreingestellter Funktion nicht. Dass innerhalb der Pivot-Tabelle in die bislang leeren Felder durch die Berechnung der Summe eine 0 geschrieben wird, hat damit nichts zu tun.
Ein vielleicht etwas kürzerer Weg, um in die Auswahl der Berechnungen zu gelangen: Klicken Sie im Menü-Register (ganz oben) ganz rechts auf den farblich hervorgehobenen Menüpunkt ANALYSIEREN, dann ändert sich auch das Menüband. Hier können Sie auch eine Schaltfläche Feldeinstellungen finden und dann darauf Klicken:
Auch so können Sie die Wertfeldeinstellungen ändern
Der einfachste Weg aber ist ein Doppelklick auf die entsprechende Zelle in der Überschriftzeile, wo derzeit noch Anzahl von … steht. Da sind Sie dann direkt bei den Werte-Einstellungen. – Was wir allerdings als echtes Manko empfinden: Nach unserem Wissensstand (trotz ausgiebiger Recherche) ist es nicht möglich, mehrere Felder zu markieren und denen dann gemeinsam die geänderte Eigenschaft zuzuweisen. Es bleibt also nur der Weg, ein Feld nach dem nächsten anzupassen. – Als Umweg (Walkaround) bietet sich natürlich an, in der Datenbasis (also den Monatsberichten) alle Leerzellen, welche eigentlich einen Zahl enthalten sollten, mit einer 0 zu füllen. Das könnte man recht gut mit Suchen und ersetzen oder per VBA erledigen lassen. Da dann alle Zellen eine Zahl enthalten, wird Excel in der PT auch automatisch die SUMME-Funktion einsetzen.
Bevor Sie sich nun die Haare raufen und vielleicht die Verwendung von Pivot-Tabellen wegen eines zu hohen Aufwands für Ihren Bereich ausschließen, arbeiten Sie diesen Teil bis zum Ende durch. Sie werden erkennen, dass vieles nur eine einmalige Aktion ist und die gegebenen Möglichkeiten die Relation Aufwand : Ertrag ins rechte Licht rücken. Und vor allen Dingen: Im zweiten Teil (nach einer Übung als Intermezzo) arbeiten Sie mit einer umgestalteten Datenbasis und da fällt vieles von dem, was jetzt einen gewissen Aufwand bedeutet, weg. Es wird dann so richtig elegant voran gehen. 😛
Bezeichnungen der Titelzeile anpassen
Summe von Brot, Summe von Brötchen, … sind zwar aussagekräftige und korrekte Namen in der Überschriftzeile, aber sie nehmen recht viel Breite ein und wirken durch die stete Wiederholung langweilig. Einfach nur die Produktgruppe ist ideal.
Ein einfache Klick in beispielsweise C3 und in der Editierzeile wird der Text der Überschrift zum ändern angeboten. Alles entfernen und nur die Produktbezeichnung (hier: Brötchen) stehen lassen. Jetzt noch Eingabe drücken oder in eine andere Zelle wechseln und: Überraschung!:
Der Name „Brötchen” ist bereits für das Feld reserviert
Die Fehlermeldung ist natürlich korrekt, wenn auch nicht auf den ersten Blick nachvollziehbar. Aber bei mehrfachem überlegen fällt Ihnen vielleicht wieder ein, dass rechts neben der Tabelle ja die Verwaltung der PT ist und dort sind die Felder aufgeführt. Und zwar mit genau dem Namen, wie er auch in den Ursprungstabelle als Überschrift vermerkt ist und hier in der PT mit dem entsprechenden Zusatz „Summe von ” steht. Und genau das sind die Feldnamen, welche nicht mehrfach für unterschiedliche Objekte verwendet werden dürfen.
Der Ausweg: Ändern Sie die Bezeichnung so ab, dass er nicht identisch ist mit dem PivotTable – Feldnamen. Wenn Sie innerhalb der PT nicht mit Makros arbeiten werden, dann können Sie einfach vor oder nach den Überschrift-Text ein Leerzeichen (Leerschritt) einfügen. Alternativ sieht ja auch ein Bindestrich vor und nach dem Begriff recht passabel aus. Und ehe wir es vergessen: Sie können den Namen auch gleich in dem Fenster, wo Sie eben die SUMME-Funktion eingestellt haben, ändern. Aber erst die Funktion in der Liste anklicken und dann den Namen anpassen.
Währungsformat und Nullwerte ausblenden
Das sieht bis jetzt schon recht ordentlich aus, fast so wie die Quelle der Daten (die Zusammenfassung der einzelnen Monate) 😉 . Um diesen ersten Schritt zu perfektionieren, fehlen noch zwei Dinge: Die Nullen sollen verschwinden und die Zahlen sollen als Währung formatiert werden. Dazu markieren Sie idealerweise die kompletten Spalten B:G und per Rechtsklick gelangen Sie über den Punkt Zellen formatieren… in das Menü für das Zahlenformat:
Mit dieser Einstellung blenden Sie Nullwerte aus
Sie sehen, dass ein Benutzerdefiniertes Format ausgewählt wurde, welches im Grunde das normale Währungsformat ist. Dieses gibt es auch in der Listenauswahl. Nach einem Klick darauf noch ein einzelnes Semikolon (Strichpunkt) einfügen, in der obigen Abbildung gelb markiert. OK und dieser Schritt ist perfekt abgeschlossen:
Alle Nullen sind nun ausgeblendet, unsichtbar
Monate und Quartale
Bis jetzt ist ja noch nicht viel von den Vorteilen einer PT zu erkennen. Eigentlich hat sich nichts zum Aussehen gegenüber der Datenherkunft getan. Und damit ist die Ähnlichkeit zum Ziel, welches ja dem Aussehen der Daten in Tabelle1 nahe kommen soll, keineswegs gegeben. Aber das wird sich nun rasant ändern.
Klicken Sie auf ein beliebiges Feld in der Spalte A des Pivot-Bereichs. Also die Überschrift oder ein beliebiges Datum. Erforderlichenfalls noch ein Klick auf die Kontext-Registerkarte ANALYSIEREN im Menü ganz oben, damit die typischen Pivot-Schaltflächen sichtbar sind. Jetzt im Menüband ein Klick auf Gruppenauswahl und ein Dialogfenster erscheint:
Daten durch Gruppierung zusammengefasst
Aus unerfindlichen Gründen ist der 01.01.2015 als Ende-Datum vorgegeben, das ändern Sie bitte auf den 31.12.2014, den letzten Tag der vorhandenen Daten. Bei den möglichen Gruppierungen ist Monate bereits ausgewählt, Klicken Sie noch zusätzlich auf Quartale und danach auf die Schaltfläche OK. Blitzschnell zeigt sich die berechnete Zusammenfassung:
Die nach Quartal und Monat zusammengefassten Daten
Das sieht doch schon nach etwas aus. Und wenn Sie nun auf die gleiche Weise wie eben durch einen Klick die Quartale entfernen, dann ist das Ziel schon fast erreicht. Es fehlt nur noch eine Spalte ganz rechts, wo die Monatsumsätze aller Produkte ausgewertet werden.
Die erste Idee wird sein, in H5 einfach eine Formel einzutragen: =SUMME(B4:G4) und zugegeben, auf den ersten Blick funktioniert das. Auch die Berechnungen stimmen. Nur die Hintergrundfarbe beim Gesamtergebnis ist nicht vorhanden. Aber es ist nicht wirklich der Hit, denn wenn Sie in diese Berechnung Klicken, wird die Pivot-Funktionalität nicht gegeben sein. Der gesamte Bereich der Verwaltung fehlt. Und auch die Kontext-Registerkarten im Menü sind nicht mehr da. Dadurch wird klar, dass diese Berechnung kein Teil der PT ist und somit nicht genutzt werden sollte.
Berechnete Felder
Aber es gibt eine Möglichkeit, die Sie nutzen können. Das Stichwort heißt: Berechnete Felder. Um die zu nutzen, löschen Sie erst einmal alle Inhalte der Spalte H oder auch gleich die ganze Spalte, Hauptsache es steht dort nichts mehr drin und auch die Formatierungen sollten Standard sein.
Jetzt wieder ein Klick in den Pivot-Bereich, damit alle erforderlichen Menüpunkte und die Verwaltung sichtbar sind. Ziemlich weit rechts im Menüband finden Sie die Auswahl Felder, Elemente, Gruppen. Ein Klick darauf und der oberste Punkt des Sub-Menüs ist das, was Sie gleich nutzen werden: Berechnetes Feld… Also ein Klick darauf und es tut sich wieder einmal ein Dialogfenster auf:
Ein berechnetes Feld erstellen
Auf den ersten Blick etwas fremd. Und auf den zweiten wahrscheinlich auch. Aber bekanntlich wird ja nichts so heiß gegessen, wie es gekocht wird. Im oberen Eingabebereich wird ja Feld1 als Name vorgeschlagen. Das ist ja nun wirklich nicht so aussagekräftig, darum schreiben Sie bitte Gesamt dort hinein. Und die Formel muss natürlich auch geändert werden. Beginnen Sie damit, dass Sie dort den Anfang einer typischen Summenformel eingeben: =SUMME(
Anschließend Klicken Sie im unteren Bereich dieses Fensters auf den ersten Feldnamen, den Sie summieren wollen, also Brot. Entweder ein Doppelklick darauf oder die Schaltfläche Feld einfügen. Der Feldname wird im Formelbereich übernommen. Dann ein Semikolon und das Gleiche mit den restlichen Feldnamen (natürlich ausgenommen das Datum). Nach Sonstiges werden Sie statt des Semikolons eine schließende Klammer verwenden. Nach einem OK wird der PT automatisch eine Spalte hinzugefügt, die Überschrift passen Sie natürlich noch an.
Überraschung…
Fertig. Das Ziel ist erreicht. Es können noch kosmetische Operationen vorgenommen werden. Aber das überlassen wir Ihrem Forscherdrang. Insbesondere wenn Sie sich zum Kontext-Hauptmenü ENTWURF bewegen und dort mit den Möglichkeiten etwas spielen. Schnelle Effekte haben Sie mit Verbundene Zellen und Verbundene Spalten, aber auch die anderen Schaltflächen bewirken mitunter etwas.
„Mitunter”, auch das ist ein Stichwort. Denn eigentlich sollte doch jede Schaltfläche der Ribbons eine Funktionalität haben. Das ist hier nicht gegeben. Sie erinnern sich, dass wir ganz zu Beginn erwähnt haben, dass die Datenbasis trotz der Aufteilung in einzelne Tage nicht wirklich optimal ist. Oder um es nun ganz klar zu sagen: Sie war eine Krücke, mit welcher dieses und jenes machbar ist, aber die wirklichen Möglichkeiten einer Pivot-Auswertung können so nicht genutzt werden.
In folgenden Teil zeigen wir Ihnen zuerst, wie die Ihnen bekannten Daten aussehen müssen, damit sie den Ansprüchen einer PT gerecht werden. Jetzt schon der Hinweis, dass aus den 365 Zeilen mit Daten ganze 1980 werden. Und wie gesagt, die Daten, die Zahlen sind identisch. Nur der Aufbau ist ein anderer. Und ich versichere Ihnen, es lohnt sich. Sie werden Möglichkeiten erfahren, die weit über das hinaus gehen, was Sie bis hierher gelernt haben. Und vor allen Dingen werden Sie sehen, dass plötzlich alles viel einfacher geht. Beispielweise steht in der Überschriftzeile mit zwei Klicks nur noch der reine, ja sogar der echte Feldname ohne jeglichen Zusatz wie Summe von oder so. Und das ist nur ein kleiner Teil dessen, was Sie dort an Erleichterungen erwartet. Und die größte Erleichterung gleich zu Beginn wird sein, dass die Daten schon fertig aufbereitet sind, sodass Sie sich gleich in das Lernvergnügen stürzen können.
Spielwiese
Wenn Sie nun noch Zeit und Lust haben, spielen Sie ein wenig mit den (beschränkten) Möglichkeiten dieser Pivot-Tabelle. Wir machen Ihnen hier per Stichwort einige Vorschläge, die Sie gewiss leicht umsetzen können. Sie erkennen dadurch aber viel eher, was eine PT eigentlich ausmacht, wie Sie mit wenigen Klicks eine komprimierte Ansicht eines Wunschergebnisses (was die Ansicht betrifft) erhalten können.
- Für alle nachfolgenden Aufgaben gilt: Sie befinden sich in der PT mit der berechneten Spalte für den jeweiligen Monat.
- Merken Sie sich einmal ein, zwei Werte aus der berechneten Spalte oder schreiben Sie sich diese auf..
- Rechtsklick irgendwo im Bereich Sonstiges und im Kontextmenü Sonstiges den Punkt entfernen wählen.
- Vergleichen Sie nun die Gesamt-Werte.
- Ziehen Sie aus der Feldliste (Verwaltung) Sonstiges wieder an den alten Platz
- Ärgern Sie sich ruhig über die Beschriftung, die ja wieder den Zusatz „Summe von” hat.
- Rechtsklick in Gesamt und lassen Sie die Daten aufsteigend sortieren.
- Stellen Sie die ursprüngliche Sortierreihenfolge wieder her.
- Wechseln Sie im oberen Kontext-Menü auf ENTWURF und probieren Sie ein wenig mit den Einstellungen für die Optik.
Das war viel, ja sogar sehr viel Text und neues Wissen. Aber so haben Sie eine solide Grundlage für die weiteren Kapitel. Diese werden kleiner, ja sogar viel kleiner ausfallen. Eine überschaubare Aufgabe, vorerst auch eine andere Datenbasis, kein VBA und so gut wie immer ohne Umwege zum Ziel 😎 . In einem späteren Teil dieses Seminars werden Sie wieder mit den Kleinbrot-Daten arbeiten. Sie werden dann andere Wege beschreiten und gewiss die eine oder andere positive Überraschung erleben.
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 3,00 € freuen …
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 |