Kategorie: Excel

Excel-Stammtisch Juni 2019 und mehr…

Ahoi!
Ich bin wieder mal total geflasht vom Excel Summit aus Amsterdam zurückgekehrt. 3 Tage – eher 4 – absolut hochwertigen Input von Excel-Experten aus aller Welt und direkt von einem Microsoft Program Manager für Excel.

Wir haben gesehen, wohin die Reise mit Office 365 und den neuen modernen dynamischen Arrayfunktionen geht, wie sich Excel Online aber auch das Thema Programmierung weiterentwickelt. Zudem gab es massenhaft Input zu Power Query, Power Pivot und Power BI.

Ein bisschen davon werde ich auf dem Excel-Stammtisch in Essen berichten. Unter anderem die Erstellung eines Kalenders mit Power Query auf Basis von Verkaufsdaten, ein wenig zu Diagrammen und ein paar Informationen zu den neuen dynamic array-functions, welche mit Office 365 Insider zur Verfügung gestellt werden.

Wir treffen uns erneut im

Unperfekthaus in Essen
Mi, 12. Juni 2019 um 19 Uhr
https://www.unperfekthaus.de/projekte/excel-stammtisch

Bitte meldet euch vorab bei mir per E-Mail an. Wenn wir aus Platzgründen Probleme bekommen sollten, wäre es schade.

Vielleicht hast ja auch DU Lust, ein Thema vorzustellen oder Fragen aus Deinem Alltag mitzubringen.

Beim letzten Treffen waren wir wieder mit 16 Personen ganz besetzt und wurden um 23 Uhr vor die Tür gesetzt. Über unsere Online-Gruppe in Microsoft Teams tauschen wir uns auch im Nachgang zu den Treffen aus.

Änderungen beim USB-Stick!

Änderung 1 – ein 256 GB-Stick

Hin und wieder interessiert sich jemand für meinen USB-Stick mit mehr als 110 Stunden Excel-Videomaterial. Die Datenmenge umfasst aktuell komplett mit den anderen beigefügten Office-Themen rund 100 GB. Die Auslieferung erfolgt auf einem 128 GB-Datenträger USB 3.0 oder auf Wunsch alternativ auf 2x 64 GB USB 3.0.

Im heimischen Elektronikmarkt gab es jetzt auch 256 GB USB 3.0-Sticks. Der Preisunterschied zum 128 GB-Stick beträgt brutto 15 Euro, welchen ich 1:1 an euch weitergeben mag. Also statt 129 Euro inkl. 19% Mehrwertsteuer wären es dann 144 Euro inkl. 19 % Mehrwertsteuer. Einfach bei einer Bestellung mit angeben!

Änderung 2 – Lieferungen ins Ausland

Seit dem 1. Januar 2019 darf ich meinen Stick nicht mehr im Umschlag ins EU-Ausland schicken. Auch für die Schweiz und andere Nicht-EU-Länder gilt beim Warenversand die Päckchen- bzw. Paketpflicht.

Dadurch haben sich die Versandkosten glatt für mich verdreifacht. Zudem muss ich auch für Österreich oder andere EU-Länder extra ein Päckchen aufgeben und kann nicht mehr den heimischen Briefkasten benutzten.

Zeitlich für mich nicht mehr schaffbar. Der Stick ist nur ein kleiner Service für diejenigen, die unbedingt gerne die Videos auch lokal verfügbar hätten und gerne noch ein Mauspad und ein paar weitere Goodies hätten. Ich werde den Versand aus Zeit- und Kostengründen nur noch für Deutschland anbieten. Sorry!

Excel-Mauspad

Mauspad* mit Excel-Tastenkombinationen (*Serviervorschlag – Lieferung erfolgt ohne Maus)

Europäische Excel-Events in 2019

In 2019 wird es im November zwei Veranstaltungen geben, zu denen ich unbedingt noch hin möchte. Auf beiden wird es viele Top-Speaker geben, die ich auch nach 6x Amsterdam schon näher kennenlernen durfte

Zum einen findet die Excel-Conference am 20. und 21. November 2019 in Sofia in Bulgarien statt. Organisiert durch Excel MVP Boriana Petrova. Vorträge und Masterclasses natürlich auf Englisch. Ein Link folgt.

Zum anderen organisiert Excel MVP Gašper Kamenšek die Excel Olympics Conference in Ljubljana in Slovenien – gleich eine Woche später am 26. und 27. November 2019.

Conference presentation

Man könnte also glatt eine Rundreise buchen 😉

 

Totaaaal verpennt…

… habe ich mein 1000. Excel-Video bei YouTube! Durch meinen zweiten Kanal und einige Videos, die ich nicht als Excel-Video durchnummeriere, bin ich schon einige duzend Nummern drüber.

Also… damit das Feiern nicht zu kurz kommt, zählen wir nach normaler Kanal-Zahlart. Dort habe ich gerade Titel 979, also noch 20 Nummern Zeit, mir was für die offizielle Nummer 1000 einfallen zu lassen. Vielleicht gibt’s ja eine Party dazu zu unserem Excel-Stammtisch im Juli!

 

Doch wieder ein bisschen sozial…

Ich habe vor Inkrafttreten der DGSVO einen Rückzieher bei den meisten Sozialen Netzwerken gemacht. Der Schritt war nicht verkehrt. Vor allem wenn man mitbekommt, was Facebook etc. so mit den Daten treiben.

Ich habe unter anderem viele Dienste gekündigt, weil ich von allen Seiten Anfragen zu „kostenlosen“ Hilfestellungen erhalten hatte, manchmal auf drei Kanälen gleichzeitig. Da hört die Freude dann auf… Dreißig Anfragen am Tag waren keine Seltenheit.

Ich bin jetzt zumindest wieder auf LinkedIn aktiv – rein dienstlich zur Vernetzung unter Excel-Experten und mit alten und potentiellen Kunden. Also nicht als kostenloser Excel-Support. Ich bitte zu entschuldigen, dass ich diese Anfragen einfach nicht beantworten kann.

So… es ist Wochenende und Quality Time!
Sonnige Grüße

Andreas

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

2 Lösungen für Staffelpreisliste

Die Aufgabe zur Ermittlung der Staffelpreise auf Basis einer kompletten Preisliste für jede Stückzahl n von n=1 bis 5000 und einer Liste, die nur die Preissprünge anzeigt hat schon ein paar tolle Lösungen hervorgebracht.

Link zum Beitrag https://thehosblog.com/2019/05/02/gesamtpreis-aus-staffelpreisliste-auslesen-aufgabe/

Folgende Formeln finden den jeweiligen Preis für die linke blaue Tabelle:
=INDEX(tabBlauLinks[Komplettpreis];H7;1) – in H7 steht die Stückzahl 50.

bzw.
=SVERWEIS(H7;tabBlauLinks[[IT-Zugriff]:[Komplettpreis]];2;FALSCH)

Die Lösung für die linke blaue Tabelle setzt allerdings

E966 Ziel

und auch für die rechte graue Tabelle:
=SUMMENPRODUKT(VERWEIS(ZEILE(INDIREKT(„1:“&H17));tabGrauRechts)) – in H17 steckt die Stückzahl 50.

Die ersten beiden Lösungen werden auch in zwei Videos vorgestellt. Sticknutzer können die Dateien auch in den Ordnern E967 und E968 herunterladen.

Videolink: https://youtu.be/_K8aoyUIySA

 

Videolink: https://youtu.be/kdVtJ4N8zVs

Vielleicht fallen euch ja noch schlaue Lösungen ein!
Von Christian habe ich eine neue spannende Lösung mit der Funktion SEQUENZ erhalten. Dies kann ich aber leider aktuell nicht vorstellen, da ich momentan nicht über Office-Insider mit den brandneuesten, aber noch nicht freigegebenen Funktionen verfüge.

Danke und bis später

Andreas

Gesamtpreis aus Staffelpreisliste auslesen – Aufgabe

Willkommen zu einer neuen Aufgabe für angehende Excel Experts!

Es soll der Gesamtpreis für eine Abnahmemenge n ermittelt werden. Und zwar sind die Staffelpreise so organisiert, dass der Stückpreis erst ab dem x. Produkt fälllt. Im Beispiel kostet hier z.B. die Basislizenz für eine Person 200 EUR. Jede zusätzliche Person bis zur einschließlich 10. Person kostet weitere 7,50 EUR. Ab der 51. Person kommen dann kosten von 7 EUR pro Person hinzu. Ab der 101. Person dann 6,50 EUR. Für 50 Personen müssen dann also wie im Beispiel gezeigt 567,50 EUR entrichtet werden.

Die Formeln sollen sich auf die linke (blaue) und die rechte (graue) Tabelle beziehen.

Die linke Tabelle ist dabei recht einfach, hier ist nämlich der tatsächliche Preis für wirklich jede einzelne Personenzahl zu lesen.

Die graue Tabelle rechts hingegen muss zunächst erst einmal aus der blauen Tabelle heraus erstellt werden. Und dann gilt es eine Formel zu finden, die ebenfalls den korrekten Wert ermittelt. Das folgende Bild zeigt den Ausgangsstand der Übungdsdatei, die hier heruntergeladen werden kann.

Die Datei mit der im Video gezeigten Lösung ist für Sticknutzer im Ordner E966 zu finden.

 

Videolink: https://youtu.be/4vEPQI75bKc

Die Übungsdatei: E966 Übungsdatei Staffelpreise

Viel Spaß beim Tüfteln!
Die Lösung folgt später als Video
Andreas

Mehrere Tabellen mit Pivot auswerten – Excel Datenmodell

Die Erfolgsgeschichte der Pivot-Tabellen wird seit einigen Jahren neu geschrieben. Pivot-Tabellen wurden mit Excel 97 in Microsoft Excel eingeführt und erfuhren zunächst mit dem noch öfter mal instabilen Excel 2010-Add-In PowerPivot aus 2011 (Codename: Genesis, 2009) und dem damit verbundenen Pivot-Datenmodell und seit ausgewählten Office-Versionen 2013 und 2016, sowie Office 365 und Office 2019 mit dem Excel-Datenmodell eine gewaltige Neuerung. Auch die neuen Möglichkeiten mit Power BI bauen auf die gleichen Technologien und Funktionen auf, dass man hier schnell auf Power BI umsteigen kann.

Nicht nur, dass man jetzt mehrere Tabellen ohne störende und rechenintensive SVEWREIS-Funktionen miteinander auswerten kann, die auszuwertende Datenmenge kann auch gewaltige Dimensionen annehmen. Einem Mitglied unseres Essener Excel-Stammtischs ist es z.B. mit seiner Excel 64-Bit-version gelungen, mehr als 1,8 Mrd. Datensätze auszuwerten.

In der nachfolgenden Datei gibt es die drei Tabellen zum Nachstellen der im Video gezeigten Verfahren. Alle Tabellen weisen einen Schlüssel auf, über den Sie mit einer der anderen Tabellen verbunden werden können.

Videolink: https://youtu.be/VdywNdZta2w

Hier ist die Datei zum Nachstellen des Videos: E961 Pivot-Datenmodell

Regulär stehen die Dateien den Nutzern meines USB-Sticks zur Verfügung. Aktuell befinden sich über 1100 Videos (Excel, Word, PowerPoint, Project, Outlook,… ) auf dem Stick und viele begleitende Excel-Übungsdateien.

Euch viel Spaß beim Nachbasteln…
… ich freue mich auf den Excel-Stammtisch am 8. Mai in Essen

Andreas

Excel-Stammtisch – Mai 2019

Hallo und eine Frohe Nach-Osterzeit!

Unser fünftes NRW-Treffen findet am 8. Mai 2019 in Essen statt.
Wieder einmal Start um 19 Uhr, wieder einmal im Unperfekthaus gegenüber dem Einkaufszentrum Limbecker Platz.

Web: https://www.unperfekthaus.de/projekte/excel-stammtisch

Bei den letzten Treffen hatten wir uns wieder mit kleineren Herausforderungen bei Diagrammen und vor allem Power Query gekümmert. Vielleicht hast ja auch Du Lust, Dich mit anderen zum Thema Excel auszutauschen. Eine kurze Rückmeldung per E-Mail wäre prima.

Sonnige Grüße und tot ziens.
Andreas

Dynamische Hyperlinks – Navigation in Tabelle

Für mein kleines Wörterbuch Niederländisch-Deutsch habe ich mir eine Liste der bereits erlernten und einzuübenden Wörter angelegt. Da die Liste immer länger wird, möchte ich per Mausklick auf die Zellen F2, G2 und H2 in meinem Blatt navigieren können.

Ein Klick soll nach ganz oben führen – F2.
Ein weiterer soll mich nach ganz unten zur Ergänzung der Liste bringen – G2.
Und letzter führt mich genau zum ersten Treffer meiner aktuellen Suchanfrage – H2.

Meinen kleinen Vokabeltrainer habe ich auf OneDrive platziert, so dass ich auch von unterwegs per Fingereingabe am Smartphone mit meiner Excel mobile app arbeiten kann.

Die ganzen Formeln könnt ihr in der Datei nachschlagen.

Die Datei: E952 Hyperlink dynamisch

Video zu Hyperlink:

Videolink: https://youtu.be/nH1DIfwGEYU

Dank je en tot ziens!

Andreas

 

P.S.: Aktuell bin ich einfach nur fassungslos, was da gerade zu Artikel 11, Artikel 12 und Artikel 13 zur in zwei Wochen geplanten Urheberrechtsreform passiert. Was wären die Alternativen zu YouTube? Ein kostenfreier Zugang für euch zu den Videos über eine öffentliche Streamingplattform wäre so unmöglich, da auch die Alternativen genauso betroffen wären.