Am 14. September 2017 haben wir eine überarbeitete Fassung unserer Datenschutzrichtlinie veröffentlicht. Wenn Sie video2brain.com weiterhin nutzen, erklären Sie sich mit diesem überarbeiteten Dokument einverstanden. Bitte lesen Sie es deshalb sorgfältig durch.

Excel 2016: Power Pivot

Datumstabelle aufbauen

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
Auswertungen sollen regelmäßig nach Jahr, Quartal, Monat oder sonst einer Datumsinformation angezeigt werden. Statt jedes mal in Power Pivot die entsprechenden Datumsfunktionen einzubauen, empfiehlt die Trainerin das Anlegen einer Hilfstabelle, die man bei Bedarf lediglich zu kopieren braucht.
10:07

Transkript

Auswertungen will man immer wieder nach Jahr, Quartal, Monat oder sonst eine Datumsinformation anzeigen. Jedes Mal in Power Pivot die entsprechenden DAX-Datumsfunktionen einzubauen, wäre sehr aufwendig. Wieso eine Datumstabelle? Bisher habe ich vielleicht berechnete Spalten gemacht mit der Funktion "YEAR" und "MONTH", den ganzen Datumsfunktionen, die es in DAX gibt. Nachteil, ich muss in meinem Datenmodell jedes Mal berechnete Spalten anfügen, und zwar dass in jeder Tabelle, wo ich das irgendwo brauche, in jedem Projekt wieder, das ist sicher mal der Nachteil. Besser wäre es eine Datumstabelle zu erstellen, und das einmal und somit kann ich diese Datumstabelle nachher in Beziehung bringen mit meinen restlichen Tabellen. Das heißt, ich kann die Daten dann in mein Projekt importieren, und wenn sich diese Datumstabelle dann verändert, wird das in meinem Projekt natürlich auch nachgeführt. Was ist wichtig beim Aufbau einer Datumstabelle? Ich muss jede Information, die ich irgendwo zu einem Datum brauche, in diese Tabelle einpflegen Das heißt, das Datum steht da einmal drin und dann habe ich ganz, ganz viele zusätzliche Spalten mit "X" Informationen. Zum Beispiel ist es ein Werktag, welches Jahr, welcher Monat, Quartal, Finanzjahr, Kalenderwoche und so weiter. All diese Informationen tu ich in diese Datumstabelle rein. Was wichtig ist beim Aufbau, jedes Datum muss dadrin vorkommen und zwar muss ich beim 01.01 des Jahres starten. Und das natürlich dann immer bis zum 31.12. Und nicht nur ein Jahr, sondern über alle Jahre, die ich benötige. Ich baue die meistens auch so auf, dass ich gleich die nächsten Jahre auch schon reintu, sodass ich diese Datumstabelle nicht jedes Jahr wieder anpassen muss. Je nach Performance muss man dann schauen wie viele Jahre man da wirklich reinpackt. Bauen wir nun so eine Datumstabelle zusammen auf. Bei welchem Tag ich starte, kommt jetzt natürlich darauf an, wie weit zurück muss ich noch Daten dann wirklich auswerten, und bei mir ist es so, dass ich 20.15. die ersten Daten habe. das heißt, ich starte hier jetzt mal mit dem 01.01.2015. Das ist mein Startdatum. Mein Kalender soll gehen bis Ende 2020, und jetzt ist es ja schön in Excel, ich muss nicht jedes Datum hier erfassen, sondern ich gehe hier oben, in dem Befehl "Reihe". Sie sehen, ich stehe auf dem Datum, wähle nun den Befehl "Reihe", und jetzt kann ich sagen, dass ich eine Reihe in der Spalte bilden möchte, immer eins weiterzählen möchte im Datumswert, also einen Tag weitergehen, und Endwert soll sein der 31.12.2020. und dann OK. Und somit habe ich das Datum schon mal bis da gebildet. ich gehe wieder zurück und jetzt ist es von Vorteil, wenn diese Tabelle eine sogenannte intelligente Tabelle ist. ich gehe also schnell hier oben auf "Als Tabelle formatieren", wähle mir hier irgendein Design aus und bestätige mit OK. Und ich gebe der Tabelle hier oben links auch gleich den Namen "tbl_Kalender". Enter. Weil diese Tabelle werde ich dann immer wieder brauchen, und ich möchte in meinem Datenmodell, dass die dann unter "tbl_Kalender" erscheint. Jetzt habe ich ganz normale Excel-Funktionen, die ich jetzt hier nutzen kann, ich brauche also das Jahr, ich nehme also die Jahr-Funktion hier in Excel, und beziehe mich auf das entsprechende Datum und drücke die Enter-Taste. Und schon habe ich das Jahr hier in einer zusätzlichen Spalte. Das Gleiche mache ich für den Monat, also ist gleich Monat (=MONAT), auch wieder Bezug auf dieses Datum hier vorne. Und wieder mit Enter bestätigen. Jetzt der Monatstext, also ich möchte den Text noch ausgeschrieben haben, den kompletten Monatsnamen, das kann ich rausholen mit der Text-Funktion, also ist gleich Text (=TEXT), Bezug auf mein Datum (@ Datum), und dann Semikolon und jetzt in Gänsefüßchen den Formatcode, wenn ich es abgekürzt möchte, würde ich 3 "M machen", ich möchte es aber ausgeschrieben und mache jetzt 4 "M". Ich kann natürlich auch zwei solche Spalten einbauen, eine abgekürzt und eine ausgeschrieben. dann den Tag, das ist dann wieder einfach, das ist die Tag-Funktion, auch wieder Bezug auf dieses Datum hier. Die Quartalsnummer, da muss ich jetzt ein bisschen rechnen, weil diese Funktion gibt es in Excel ja nicht, wo man einfach sagen kann "Aus diesem Datum das Quartal". Das kann man machen, indem man die Monatsnummer durch 3 teilt und dann noch auf Ganze rundet. Das heißt, ich beginne hier jetzt mit der AUFRUNDEN-Funktion, sage jetzt, dass ich mich auf den Monat, also auf die Nummer beziehen möchte, und diese geteilt durch drei rechne (/3) und dann Semikolon auf Ganze aufrunden möchte (;0). Das ist die Formel, um das Quartal zu ermitteln. Gucken wir, ob es auch stimmt, wenn wir ein bisschen weiter nach unten gehen, genau, hier kommt das zweite Quartal am 01.04., das ist korrekt. Gehe wieder ganz nach oben, jetzt brauchen wir noch den Text dazu, also zum Beispiel in Kurzform Q1, Q2 und so weiter, da kann ich jetzt einfach einen Text zusammenhängen mit dieser Nummer, ich nehme also =, sage zuerst mal in Gänsefüßchen ich möchte ein Q haben. Die Frage ist, muss die Eins nachher direkt am Q stehen oder möchten Sie doch noch ein Leerzeichen haben, ich möchte noch ein Leerzeichen zuerst, dann Gänsefüßchen, und jetzt kann ich ja hier mit dem Verkettungszeichen hier noch die Zahl dazuhängen. So würde ich jetzt das Quartal zusammenhängen. Und vielleicht brauche ich dann in der Pivot auch mal das Quartal ausgeschrieben, also kann ich hier noch genau das Gleiche tun. ich möchte, dass da steht erstes Quartal, zweites Quartal ausgeschrieben. Würde also hier jetzt beginnen mit der Quartalsnummer, dann möchte ich hier, ja, zuerst mal einen Punkt dazuhängen, also nehme ich wieder dieses Verkettungszeichen und in Gänsefüßchen dann den Punkt, dann vielleicht einen Abstand und jetzt noch den Text "Quartal", Gänsefüßchen zu und Enter. Und schon habe ich das Quartal "Lang" auch zusammengesetzt. Wochentag, ich möchte den Wochentag ermitteln, also ist es ein Montag oder ein Sonntag oder was er auch immer, die Funktion heißt in Excel auch "WOCHENTAG". Da kann ich mich wieder direkt auf das Datum beziehen, dann Semikolon und jetzt muss ich noch den Typ angeben, also was soll denn für eine Zahl genau rauskommen, soll der Sonntag als "eins" gekennzeichnet werden oder doch besser der Montag als "eins" gekennzeichnet", da kann ich wählen. ich würde jetzt mal sagen, für mich ist logisch, dass Montag eine Eins kriegt, also nehme ich hier den Parameter "2". Und dann kann ich die Formel wieder abschließen. Dann möchte ich das Ganze noch ausgeschrieben haben, also Montag, Dienstag, Sonntag und so weiter. Das kann ich tun hier mit der TEXT-Funktion wieder wie vorne, ich beziehe mich also wieder auf das Datum, Semikolon und in Gänsefüßchen gebe ich das Format an, das wären hier jetzt ausgeschrieben vier große T-s. Und Klammer zu, Enter. Und noch die letzte Funktion für das Beispiel, die Kalenderwoche. Da gibt es in den neueren Versionen ja die Isokalenderwoche, die ist ganz einfach, ich muss nur diese Funktion abrufen, beziehe mich auf mein Datum und dann holt er hier die richtige Kalenderwoche heraus. Es gibt noch viele mehr Datumsfunktionen, ich kann natürlich "X" Spalten hier drin machen, die ich dann wirklich mal brauche, ich kann das auch später noch ergänzen, weil diese Tabelle können wir hier ja pflegen und dann immer wieder in unserem Datenmodell, wo wir das benötigen, aktualisieren. Sie wissen nun, wie Sie mit Datumsfunktionen eine Kalendertabelle in Excel aufbauen können, die Sie später in allen Ihren Power Pivot-Auswertungen nutzen können. So nach dem Motto "Einmal richtig erstellt und immer wieder nutzen".

Excel 2016: Power Pivot

Sehen Sie, wie Sie große Datenmengen aus unterschiedlichen Datenquellen importieren, im Datenmodell verknüpfen, aufbereiten und auswerten mit dem BI-Add-In Power Pivot.

3 Std. 21 min (37 Videos)
Derzeit sind keine Feedbacks vorhanden...
 
Hersteller:
Software:
Exklusiv für Abo-Kunden
Erscheinungsdatum:27.01.2017

Dieser Online-Kurs ist als Download und als Streaming-Video verfügbar. Die gute Nachricht: Sie müssen sich nicht entscheiden - sobald Sie das Training erwerben, erhalten Sie Zugang zu beiden Optionen!

Der Download ermöglicht Ihnen die Offline-Nutzung des Trainings und bietet die Vorteile einer benutzerfreundlichen Abspielumgebung. Wenn Sie an verschiedenen Computern arbeiten, oder nicht den ganzen Kurs auf einmal herunterladen möchten, loggen Sie sich auf dieser Seite ein, um alle Videos des Trainings als Streaming-Video anzusehen.

Wir hoffen, dass Sie viel Freude und Erfolg mit diesem Video-Training haben werden. Falls Sie irgendwelche Fragen haben, zögern Sie nicht uns zu kontaktieren!