Zu Beginn laden Sie bitte diese Datei mit den Umsätzen der Bäck­erei Klein­brot, welche pro Tag je einen Ein­trag für jede Pro­duk­t­gruppe enthält. Zur Erin­nerung: Das sind die Über­schriften und 1.824 Zeilen mit Dat­en. Das Ziel ist es, im Hin­blick auf die kalen­darischen Dat­en ver­schiedene Möglichkeit­en der Auswer­tung darzustellen.

Vorbereitung

Um die Leis­tungs­fähigkeit der Piv­ot­Ta­bles zu unter­stre­ichen, sollen die schön geord­neten Umsätze ein­mal richtig durcheinan­der „gewür­felt” wer­den. Und natür­lich soll das Ganze als Intel­li­gente Tabelle ein­gerichtet sein. Im ersten Schritt ein­fach ein­mal StrgPos1, um zu A1 zu gelan­gen. Damit ist gewährleis­tet, dass die aktive Zelle auf jeden Fall im Daten­bere­ich ist. Nun StrgT oder StrgL, um die Intel­li­gente Tabelle zu erstellen. Ein kurz­er Kon­troll-Blick, ob der gesamte Bere­ich der Dat­en im entsprechen­den Textfeld angezeigt wird. Außer­dem muss das Häkchen geset­zt sein, dass die Tabelle Über­schriften hat. Nach einem OK ist umge­hend die typ­is­che tabel­lar­ische Darstel­lung, meist in ver­schiede­nen Blautö­nen, zu sehen. Damit ist der erste Schritt erledigt.

Klick­en Sie nun in D2 und geben Sie fol­gende Funk­tion ein: =ZUFALLSZAHL() und Sie wer­den erken­nen, dass ruck zuck auch eine Über­schrift in D1 einge­tra­gen wird und die Formel automa­tisch bis zum let­zten Ein­trag in Zeile 1825 kopiert wurde. Um zu ver­hin­dern, dass immer und immer wieder alles in dieser Spalte neu berech­net wird, wer­den Sie die per Funk­tion berech­neten Zahlen sofort in sta­tis­che Werte umwan­deln. Ein Klick in A2, dann StrgShift↓ um alle berech­neten Werte zu markieren. Nun den markierten Bere­ich auf beliebigem Wege in die Zwis­chen­ablage kopieren, beispiel­sweise per StrgC. Direkt danach ohne die Markierung zu ändern ein Recht­sklick und im Kon­textmenü bei den Ein­fü­geop­tio­nen das Sym­bol für die Werte wählen. Fer­tig, jet­zt sind wun­schgemäß keine Formeln mehr in Spalte D son­dern reine, sta­tis­che Zahlen­werte.

Sie kön­nten jet­zt zwar noch diese Über­schrift nach Belieben anpassen, aber es lohnt nicht. Sie wer­den gle­ich sehen, dass es unnütz wäre. Sortieren Sie nun die Spalte D auf- oder absteigend, wie Sie mögen. Dazu Klick­en Sie auf die Schalt­fläche ▼ rechts in D1 und Klick­en auf den ersten oder zweit­en Punkt in der Auswahl.

Jet­zt haben Sie eine Rei­hen­folge, wie sie gewiss nie in der Real­ität vorkom­men würde aber zur Demon­stra­tion der Leis­tungs­fähigkeit ein­er PT ist solch ein Durcheinan­der recht gut geeignet. Damit hat die Spalte D ihren Zweck erfüllt und Sie kön­nen sie auf beliebigem Wege löschen. Vorzugsweise ein Klick auf den Spal­tenkopf D, damit die gesamte Spalte markiert wird. Dann ein Recht­sklick und Zellen löschen. Das war’s dann auch und die Vor­bere­itun­gen sind erledigt.

▲ nach oben …

Erstellen der PivotTabelle

Acht­en Sie darauf, dass eine beliebige Zelle inner­halb der Intel­li­gen­ten Tabelle markiert ist. Dann Klick­en Sie im Menü ganz rechts auf den Punkt TABELLENTOOLS oder direkt darunter auf ENTWURF. Sofort wird sich die Rib­bon-Leiste, das Menüband ändern. Links in der Gruppe Tools ist die Auswahl Mit Piv­ot­Table zusam­men­fassen zu sehen. Sie Klick­en ein Mal darauf und ohne Verzögerung wird Ihnen das bekan­nte Fen­ster zur Erstel­lung der Piv­ot­Ta­belle ange­boten. Übernehmen Sie die Vor­gaben, sie sind dank der Tabelle kor­rekt.

In dieser noch leeren Auswer­tung set­zen Sie bei den Piv­ot­Table-Feldern bei allen Kästchen in der Rei­hen­folge von oben nach unten ein Häkchen. Das Ergeb­nis stellt sich dann so dar:

Die erste Auswer­tung nach Datum

Der Steuerungs-Bere­ich der Piv­ot-Auswer­tung hat diese Anord­nung:

Nach dem Markieren der Felder …

Eine ewig lange Tabelle … Wenn Sie hier ein­mal StrgEnde betäti­gen wer­den Sie sehen, dass das mit dem Gesamtergeb­nis sage und schreibe 2.132 Zeilen sind. Das ist ja nun wirk­lich nicht über­sichtlich. Wenn schon jed­er einzelne Tag, an dem Umsatz erwirtschaftet wurde hier aufge­führt wird dann sollte das auf Dauer gese­hen möglichst schon im 300^er-Bere­ich liegen.

▲ nach oben …

Mehr Übersicht

An dieser Stelle sei ein­mal Grund­sät­zlich­es ange­merkt: Wenn irgen­deine hier im Script geforderte Aktion die PT bet­rifft, dann muss zwin­gend eine Zelle im Daten­bere­ich der Piv­ot­Table markiert sein. Sie erken­nen das auch daran, dass im oberen Menü rechts der (nor­maler­weise) pink-far­big hin­ter­legte Bere­ich für die Piv­ot­Table-Tools sicht­bar ist.

Einen ersten Schritt zum Ziel der besseren Über­sichtlichkeit erre­ichen Sie, wenn Sie im PT-Menü den Menüpunkt ENTWURF anklick­en und in der Gruppe Lay­out die Schalt­fläche Bericht­slay­out anklick­en und dann die dritte Möglichkeit In Tabel­len­for­mat anzeigen auswählen. Es sind zwar immer noch mehr als 2.000 Zeilen, aber so ist es doch um einiges über­sichtlich­er als vorher. Und spätestens jet­zt sollte Ihnen aufge­fall­en sein, dass ohne Ihr Zutun die „bunte Rei­he” der kalen­darischen Dat­en wohl geord­net erscheint, von Anfang Jan­u­ar bis Ende Dezem­ber.  🙂 

▲ nach oben …

Etwas „Kosmetik”

Belassen Sie es erst ein­mal dabei, was die Zeilen­zahl bet­rifft. Die eine oder andere kos­metis­che Kor­rek­tur sollte nun vorgenom­men wer­den. Begin­nen wir mit den Über­schriften. Datum ist okay, kann aber in der Spal­tenbre­ite etwas angepasst wer­den. Ein Dop­pelk­lick auf den Spal­tentren­ner A|B und die opti­male Bre­ite ist eingestellt. Pro­duk­t­gruppe kön­nen Sie so lassen oder in Pro­duk­te umbe­nen­nen. Summe von Preis soll­ten Sie in Umsatz umbe­nen­nen. Preis (ohne zusät­zliche Zeichen) geht nicht, da diese Beze­ich­nung dem Feld­na­men entspricht und der muss ein­ma­lig sein. „Preis ” mit ange­hängtem Leerze­ichen würde aber akzep­tiert wer­den.

Eigentlich sieht das ja schon ganz vernün­ftig aus. Eigentlich…, denn schauen Sie sich ein­mal C15 und C24 an. Diese Berech­nun­gen wer­den recht „platzs­parend” dargestellt, nur eine bzw. gar keine Nachkom­mas­telle. Und über­haupt, die Währungs­beze­ich­nung sollte schon sein. Klick­en Sie dazu in die Zelle C3 und im PT-Menü auf den Unter­menüpunkt ANALYSIEREN. In der Gruppe Aktives Feld (2. Gruppe von links) ein Klick auf die Felde­in­stel­lun­gen.

Im geöffneten Fen­ster Wert­felde­in­stel­lun­gen Klick­en Sie gle­ich auf Zahlen­for­mat 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 opti­male Bre­ite anzu­passen.

▲ nach oben …

Noch mehr Übersicht

Wenn Sie die Tages-Ansicht bzw. ‑Über­sicht brauchen, dann kön­nen Sie die Über­sichtlichkeit in zwei Vari­anten verän­dern. Möglichkeit 1: Sie belassen es bei diesem grund­sät­zlichen Auf­bau der vie­len Zeilen. Dann wer­den es sog­ar noch mehr Zeilen, weil Sie nach jedem Tag eine Leerzeile ein­fü­gen. Pro­bieren soll­ten Sie es auf jeden Fall ein­mal, auch wenn Sie es speziell hier nicht anwen­den wer­den:

• Menü PT-TOOLS | ENTWURF
• Gruppe Lay­out, Sym­bol Leere Zeilen
• Ober­ste Auswahl Leerzeile nach jedem Ele­ment ein­fü­gen anklick­en.

Das sieht schon ein­mal sehr viel über­sichtlich­er aus. Eine „schlanke” Darstel­lung und jed­er Tag schön separi­ert. 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 kom­men.

Der aller, allere­in­fach­ste Weg dahin: Ent­fer­nen Sie das Häkchen bei der Pro­duk­t­gruppe in den PT-Feldern. Schon ist die Tabelle auf 308 Zeilen geschrumpft. Der Preis dafür ist, dass nur die Gesamt-Umsätze der einzel­nen Tage angezeigt wer­den, nicht jedoch die Pro­duk­te.

Das kann in Einzelfällen erwün­scht sein, aber es sollte doch einen Weg geben, die Pro­duk­te einzeln darzustellen und den­noch im 300^er-Bere­ich zu bleiben. Und natür­lich gibt es den auch. 😎 Der Weg dahin ist beispiel­sweise so:

• Das Häkchen bei der Pro­duk­t­gruppe in den PT-Feldern muss geset­zt sein.
• Ziehen Sie nun das Feld Pro­duk­t­gruppe aus dem (unteren) Bere­ich ZEILEN in den Bere­ich SPALTEN.

Fer­tig, die let­zte Zeile ist die 309. In der Real­ität sind es ja sog­ar noch zwei Zeilen weniger, da ja die ersten bei­den Zie­len leer sind. Bei den bish­eri­gen Berech­nun­gen haben wir das ein­fach ignori­ert und wer­den es auch weit­er­hin tun, da Sie son­st immer umrech­nen müssten. Außer­dem wird beim Druck­en auch der ober­ste Bere­ich mit erfasst, solange nicht ein spezieller Druck­bere­ich fest­gelegt wor­den ist.

▲ nach oben …

Noch mehr Kosmetik

Irgend­wie ist das aber so eine „Zahlen­wüste”. So richtig über­sichtlich ist das immer noch nicht. Eine For­matierung ähn­lich der Intel­li­gen­ten Tabelle wäre hil­fre­ich. Lei­der aber ist diese Form noch nicht als For­matvor­lage inte­gri­ert. Also müssen wir von Hand ran.

• Klick­en 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 let­zte markierte Zeile ist.
• Tas­tenkom­bi­na­tion StrgShift→, dann ist der Bere­ich A5:H308 markiert
• Über das Menü Start in der Gruppe For­matvor­la­gen die Bed­ingte For­matierung aufrufen.
• Neue Regel… und die unter­ste Auswahl Formel… anklick­en.
• Als Formel geben Sie ein: =ISTGERADE(ZEILE(A5)) und als For­matierung haben wir ein helles Grün im Reg­is­ter Aus­füllen gewählt, um alle ger­aden Zeilen her­vorzuheben.
• Je nach Geschmack kön­nen nun noch beispiel­sweise A5:A308 und H5:H308 in Fett und/oder kur­siv her­vorge­hoben wer­den.
• Klick­en Sie nun auf A5 und Menü ANSICHT | Fen­ster | Fen­ster fix­ieren | Fen­ster fix­ieren.

Mit dem let­zt­ge­nan­nten Punkt erre­ichen Sie, dass die Über­schrift­szeile auch dann noch sicht­bar ist, wenn Sie nach unten scrollen und der 2. Jan­u­ar nicht mehr sicht­bar ist:

Die fix­ierte Fen­ster-Ansicht

Diesen Stand der Dinge wer­den Sie vielle­icht später noch ein­mal brauchen. Darum kopieren Sie dieses Blatt auf eine Ihnen genehme Art und arbeit­en Sie ab sofort mit der Kopie weit­er. Zur Vor­sicht kön­nen Sie Ihr Werk schon ein­mal unter einem anderen Namen spe­ich­ern.

▲ nach oben …

Monatliche Auswertung

Begin­nen Sie mit einem Recht­sklick auf A5. Wählen Sie dort den Punkt Grup­pieren… und es wird sich dieses Fen­ster auf­tun:

Das Grup­pieren-Fen­ster

Die vorgeschla­gene Auswahl ist passend, übernehmen Sie diese mit einem OK, nach­dem Sie sich vorher die einzel­nen Möglichkeit­en verin­ner­licht haben. Vom Prinzip her sieht das ja schon recht gut aus. Was nun aber stört, ist die Bed­ingte For­matierung. Die ist irgend­wie nicht den neuen Ver­hält­nis­sen angepasst. Darum löschen Sie die Regeln der BF im gesamten Blatt. B4:H4 rechts­bündig aus­richt­en macht vielle­icht einen besseren Ein­druck. In B3 eventuell die Beze­ich­nung auf Pro­duk­te ändern und dann noch die Spal­ten B:H auf die opti­male Bre­ite ein­stellen.

Überraschung!

Weit­er oben hat­ten wir ja schon ein­mal die Pro­duk­te aus­ge­blendet, indem Sie das Häkchen bei den PT-Feldern ent­fer­nt hat­ten. Probe­weise und des Ver­suchs an sich wegen soll­ten Sie das auch noch ein­mal machen. – Und anschließend gle­ich wieder das Häkchen set­zen. Und nun wis­sen Sie, wie die Über­schrift dieses Absatzes gemeint ist. Es wird nicht der direkt vorher­gende Zus­tand wieder­hergestellt son­dern der ursprüngliche. Sie hät­ten zweck­mäßiger­weise das Feld gle­ich in den Bere­ich SPALTEN ziehen kön­nen oder Sie ziehen es nun dort hin. Wie gut, dass das so wenig Aufwand ist.  😎 

▲ nach oben …

Quartale

Mitunter ist eine quar­tal­sweise Auswer­tung von Dat­en wün­schenswert oder erforder­lich. Eventuell ist Ihnen eben schon aufge­fall­en, dass es neben den Monat­en auch noch die Quar­tale als Grup­pierungs-Kri­teri­um gibt. Also ein Recht­sklick in A4 oder auch in eine der Zellen A5:A16, wo ja ein Datum im For­mat MMM drin ste­ht und natür­lich Grup­pieren… auswählen. Klick­en Sie nun auf Quar­tale und es wird eine neue Spalte mit den Quar­tal­en einge­fügt. Sollen auss­chließlich die Quar­tale angezeigt und natür­lich berech­net wer­den, dann Klick­en Sie im Grup­pieren-Fen­ster ein­fach auf die Markierung Monate, dadurch wird die Selek­tion aufge­hoben.

Eigentlich wäre eine Anzeige pri­ma, welche bei­de Berech­nun­gen bein­hal­tet. Also die Ergeb­nisse alle einzel­nen Monate und die Sum­men der Quar­tale. Auch das lässt sich rel­a­tiv prob­lem­los erre­ichen. Klick­en Sie im Menü PT-TOOLS | ENTWURF Gruppe Lay­out auf das allererste Sym­bol, Tei­l­ergeb­nisse. Da die Zwis­chen­sum­men sin­nvoller­weise jew­eils am Ende des Quar­tals angezeigt wer­den sollen, ist die zweite Auswahl die passende. Und wenn Sie nun noch über Leere Zeilen nach jedem Ele­ment eine Leerzeile ein­fü­gen lassen, sieht das richtig schick aus:

Opti­male Darstel­lung der Umsätze

Sie haben nun einen kleinen Überblick bekom­men, was in Sachen Datum und PT möglich ist. Zugegeben, es ist nur ein klein­er Auss­chnitt des Mach­baren, aber eine solide Grund­lage. Im näch­sten Kapi­tel wer­den diese Dat­en auf der Basis von Kalen­der­wochen aus­gew­ertet. Dazu aber soll­ten Sie das bis hier­her gel­ernte verin­ner­licht haben.

Spe­ich­ern Sie nun ide­al­er­weise Ihre Arbeit, damit Sie das Ganze später noch ein­mal nachvol­lziehen kön­nen.

KleinPivot Übersicht

▲ nach oben …