Kategorie: Office 365

Kalendertabelle – volle 10 Jahre

Warum war ein Monat so umsatzschwach? Wie viele Verkaufstage hat ein Monat? Oder wie sieht mein aktueller Umsatz im Vergleich zum passenden Vorjahreszeitraum aus?

Zahlen sagen nur wenig aus, wenn man sie ohne Kontext betrachtet. Zeitraum, Zeitpunkt, parallel laufende Ereignisse etc.
Zudem sieht der Kalender auch nicht für jeden gleich aus.

Wir betrachten meist das Kalenderjahr – und das schon aus unterschiedlichem Blickwinkel. Für die einen beginnt die Woche am Montag und hat fünf Arbeitstage – für die andere jedoch 6 Verkaufs- und Arbeitstage. Für die einen beginnt die erste Kalenderwoche automatisch am 1. Januar einen Jahres (z. B. in den USA) – für uns in der Woche, in der mehr neue Tage als Tage des alten Jahres sind (also mindestens vier Januartage und somit auch der Donnerstag). Für das eine Unternehmen ist das Wirtschaftsjahr identisch mit dem Kalenderjahr – wenn man sich aber die Geschäftsberichte großer Unternehmen anschaut, beginnt zum Beispiel bei thyssenkrupp das Geschäftsjahr am 1. Oktober eines Jahres und endet am 30. September des Folgejahres. Entsprechend werden auch die Monate anders gezählt. Theater widerum haben neben Kalender- und Geschäftsjahr noch ein zusätzliches Spieljahr … ach ja, Ferien pro Bundesland und abweichende Feiertage gibt es ja auch noch.

Kalendertabellen selbst können Großteils automatisiert errechnet werden. Andere Informationen wie z. B. der Reformationstag aus dem Jahr 2018 und z. B. die Ferien eines Bundeslandes wie Nordrhein-Westfalen müssen manuell ergänzt werden.

In einer neuen Seminarreihe gehe ich auf die Kalendererstellung und Datumsberechnung in Excel selbst, in Power Query mit der eigenen Programmiersprache M, sowie der Erstellung in PowerPivot mit DAX-Funktionen (Data Analysis Expressions) ein. Als i-Tüpfelchen könnte noch die Kalenderberechnung in VBA folgen. Oder schreibt man das mittlerweile iTüpfelchen?

Einige sind ja schon auf mein PDF-Skript gestoßen. Das wird nach und nach fortgeführt: Excel – Datums- und Zeitfunktionen

Auch hatte ich an anderer Stelle schon mal eine Kalendertabelle zur Verfügung gestellt und die Feiertage und Schulferien von NRW ergänzt. Diese Informationen und weitere Ergänzungen gibt es nun zum Download in folgender Excel-Datei (aktualisiert!): Kalendertabelle

Die Datei liegt im XLSX-Format vor und beinhaltet auch Informationen zur Ermittlung von Geschäfts- bzw. Fiskaljahren.

Für alle, die nichts herunterladen wollen oder dürfen, die Spalten enthalten:

  1.  Spalte:   Datum –> Liste manuell gelistet
  2.  Spalte:   Jahr =JAHR([@Datum])
  3.  Spalte:   Monat_kurz =MONAT([@Datum])
  4.  Spalte:   Monat =TEXT([@Datum];“MMMM“)
  5.  Spalte:   Tag Monat =TAG([@Datum])
  6.  Spalte:   Tag Woche =WOCHENTAG([@Datum];2)
  7.  Spalte:   Wochentag =TEXT([@Datum];“TTTT“)
  8.  Spalte:   Tage im Monat =TAG(MONATSENDE(DATUM([@Jahr];[@[Monat_kurz]];1);0))
  9.  Spalte:   KW =KALENDERWOCHE([@Datum];21)
  10.  Spalte:   Quartal =“Q“&AUFRUNDEN([@[Monat_kurz]]/3;0)
  11.  Spalte:   Jahr Quartal =[@Jahr]&“ „&[@Quartal]
  12.  Spalte:   Jahr Monat =TEXT([@Datum];“JJJJ-MM“)
  13.  Spalte:   Halbjahr =WENN([@[Monat_kurz]]<=6;“HJ 1″;“HJ 2″)
  14.  Spalte:   Jahr KW =JAHR([@Datum])-WENN(UND(MONAT([@Datum])=1;
    KALENDERWOCHE([@Datum];21)>51);1;0)+WENN(UND(MONAT([@Datum])=12;
    KALENDERWOCHE([@Datum];21)=1);1;0)&“-„&TEXT(KALENDERWOCHE([@Datum];21);“00“)
  15.  Spalte:   Feiern NRW –> manuell auf WAHR und FALSCH gesetzt
  16.  Spalte:   Feiertage NRW –> manuell auf WAHR und FALSCH gesetzt
  17.  Spalte:   Werktag =WENN([@[Tag Woche]]<=5;“Werktag“;“Wochenende“)
  18.  Spalte:   Verkaufstage Monat NRW =ZÄHLENWENNS([Tag Woche];“<=“&6;[Jahr];JAHR([Datum]);
    [Monat_kurz];MONAT([Datum]);[Feiertag NRW];FALSCH)
  19.  Spalte:   Werktage Monat NRW =ZÄHLENWENNS([Tag Woche];“<=“&5;[Jahr];
    JAHR([Datum]);[Monat_kurz];MONAT([Datum]);[Feiertag NRW];FALSCH)
  20.  Spalte:   FY =rngFYPräfix&WENN(rngStartFiskaljahr=1;[@Jahr];
    WENN([@[Monat_kurz]]<rngStartFiskaljahr;[@Jahr]-1&“/“&RECHTS([@Jahr];2);
    [@Jahr]&“/“&1*RECHTS([@Jahr];2)+1))
  21.  Spalte:   FY M =[@FY]&“ – „&TEXT(WENN([@[Monat_kurz]]<
    rngStartFiskaljahr;12-rngStartFiskaljahr+[@[Monat_kurz]];[@[Monat_kurz]]-rngStartFiskaljahr)+1;“00“)
  22.   Spalte:   FY Q =[@FY]&“ – Q“&AUFRUNDEN((WENN([@[Monat_kurz]]<
    rngStartFiskaljahr;12-rngStartFiskaljahr+[@[Monat_kurz]];
    [@[Monat_kurz]]-rngStartFiskaljahr)+1)/3;0)

Auf dem zweiten Tabellenblatt gibt es zwei Zellen benannte Zellen. Die mit rngStartFiskaljahr benannte Zelle muss auf Werte zwischen 1 und 12 gesetzt werden. Die benannte Zelle rngFYPräfix beinhaltet z. B. „GJ “ für Geschäftsjahr oder „FY “ für fiscal year.

Alle Angaben und Berechnungen sind ohne Gewähr. Also bitte noch einmal prüfen. Vielleicht mag sich auch jemand die Mühe machen und die Feiertage und Ferien für andere Bundesländer eintragen – dies allein war schon irre aufwendig.

So!!! Nun haben wir eine Basis, auf die ich in neuen Videos eingehen werde.
Freue mich auf Feedback bei YouTube und auch Danke an alle, die den Kanal supporten.

Bis bald
Andreas

Funktion FELDWERT für neue Datentypen in Excel – Geografie und Aktien

 

In meiner neuesten Version von Office 365 ProPlus (Plan E3) stehen mir zwei neue Datentypen zur Verfügung. Falls noch nicht geschehen, aktualisiere Deine Office-Version mit DATEI – KONTO – Updateoptionen – Jetzt aktualisieren. Es kann tatsächlich sein, dass Du die neuen Datentypen noch nicht verfügbar hast und ein paar Wochen oder Monate warten musst – das hängt auch von Deiner Version und Deiner IT-Abteilung ab. Bzw. manche werden die Funktion wegen der entsprechenden Office-Installation auch einfach nicht erhalten. Deshalb dieser Artikel… prüfe, ob die Funktionalität vorhanden ist.

Im Register Daten befindet sich nun die Gruppe Datentypen mit den beiden Möglichkeiten, Aktiendaten und geografische Informationen aus dem Internet abzurufen.

Datentypen

Mit der Funktion FELDWERT können die in Datentypen konvertierten Felder abgefragt werden. So lassen sich von Aktientiteln die Preise und von Geodaten z.B. die Koordinaten, Währungen etc. abrufen.

Wenn Du mal ausprobieren möchtest, ob die Funktion FELDWERT und die neuen Datentypen bei Dir schon Funktionieren, kannst Du die folgende Datei laden.

Hinweis: Es wird dort angefragt, ob sie Zugriff aufs Internet nehmen darf – insofern die Funktion funktioniert, da ja hier Netzdaten abgerufen werden sollen: E940 Funktion Feldwert

Vielleicht wird es ja noch weitere Datentypen oder eine Ausweitung auf ältere Installationen von Office 2016 geben… wäre fein.

Euch viel Erfolg
Andreas

Videos zu den neuen Datentypen:

Link zum Video: https://youtu.be/eRWb4CXQJ-c

Und für Aktien:

Link zum Video: https://youtu.be/bVzjEXAlObM

Reicht es Ralph? Die Pin ist wieder da!

Kennt Ihr den Disney-Film Ralph reichts? Ich finde den Film super. Doch manchmal glaube ich, ist der auch bei Microsoft aktiv. Was hatten wir mit Office 2010 für ein schönes Haus, in dem im Dateibereich zuletzt genutzte Ordner angezeigt wurden und angeheftet werden konnten. Bis auf ein paar Macken (Name des Standarddesigns: Larissa, 2x die Schaltfläche Fenster einfrieren unter Ansicht) konnte Felix die meisten Fehler fixen. Tja… und der Pin war großartig…. BIS MAN IHN WIEDER WEGGENOMMEN HATTE! Was sollte das denn? Das fühlte sich auf einmal so richtig 2003-Retro an.

excel2016

Hier ein Produktbild aus dem aktuellen Excel 2016 aus Office 2016 Professional Plus. Keine Möglichkeit, Standardordner anzuheften.

Im Dezember 2015 hatte ich beim Excel Bootcamp in London mit einigen Excel Product Managern sprechen können. Sie haben uns wärmstens für Wünsche den neuen Microsoft-Bereich Uservoice empfohlen https://excel.uservoice.com/

Dort schaut das Product Team regelmäßig nach, welche Funktionalitäten gewünscht werden und was geändert werden sollte. Man nimmt sich die dortigen Einträge zu Herzen und möchte die dortigen Impulse nutzen, die Produkte noch besser zu machen. Ein Top-Wunsch ist z.B. ein Menü für die Standardeinstellungen für Pivot-Tabellen, damit man nicht immer alles nachträglich anpassen muss… bin gespannt.

Und mit dem heutigen monatlichen Office 365-Update gab es dann auch die große Überraschung. Ich nutzte neben Office 2016 Professional Plus auch ein Office 365-E3-Plan. Im Admin-Bereich habe ich eingestellt, dass ich die neuen Aktualisierung sofort nutzen möchte – First Release. Das ist nicht immer von Erfolg gekrönt, da Felix manchmal noch Fehler übersehen hat. Zeigt aber, wo die Reise hingeht und welche Möglichkeiten bald allgemein zur Verfügung stehen.

proplus

Ich zumindest freue mich über die neue „alte“ (oder alte „neue“?) Möglichkeit, wieder meine meist genutzten Ordner anzuheften…

excel-365

Also, wenn Ihr Office 365 nutzt, sollte diese Möglichkeit in den kommenden Monaten auch bei euch verfügbar sein. Das gilt natürlich auch für die anderen Office-Anwendungen.

Videolink: https://youtu.be/D8qCj459fzM

Träumt nicht von Excel!
Euer Andreas

 

Excel-Alternativen zur Funktion WENNS

at_Excel

Die Funktion WENNS steht seit dem aktuellen Office 365-Update in Excel 2016 zur Verfügung. LibreOffice Calc-Nutzer kennen diese Funktion bereits.

Doch wie sollen die Anwender vorgehen, die diese Funktion in Excel noch nicht nutzen können?

Anbei einige Alternativen zu WENNS mit SVERWEIS, VERWEIS, INDEX und VERGLEICH und der verschachtelten WENN-Funktion.

WENNS_Excel.jpg

Natürlich gibt es auch wieder ein Video dazu auf meinem neuen YouTube-Kanal!

Link zum Video https://youtu.be/YKWt08CcuzA

Käufer des USB-Sticks finden die Excel-Datei im Netz unter …\Excel600plus\at_Excel\at0002

Ich freue mich auf euer Feedback
Beste Grüße

Andreas