Feiertage NRW von 2017 bis 2020

 

Hallo,

in meiner aktuellen Videoreihe zur Jahresübersicht nutzte ich eine Liste von Feiertagen und Ferienzeiten. In meinem Fall für NRW.

Wer die Übungen nachvollziehen möchte, kann auf die folgenden Feiertage (ohne Gewähr) zurückgreifen und diese eventuell sogar mit Power Query auslesen.

Es gibt auch sicher andere Quellen im Netz dafür.

Hier die Feiertage und anderen Ereignisse als Tabelle:

 

Datum Ereignis gesetzlich
01.01.2017 Neujahr ja
27.02.2017 Rosenmontag
14.04.2017 Karfreitag ja
16.04.2017 Ostersonntag ja
17.04.2017 Ostermontag ja
01.05.2017 1. Mai ja
25.05.2017 Christi Himmelfahrt ja
04.06.2017 Pfingstsonntag ja
05.06.2017 Pfingstmontag ja
15.06.2017 Fronleichnam ja
03.10.2017 Tag der Deutschen Einheit ja
31.10.2017 Reformationstag ja
01.11.2017 Allerheiligen ja
24.12.2017 Heiligabend
25.12.2017 1. Weihnachtstag ja
26.12.2017 2. Weihnachtstag ja
31.12.2017 Silvester
01.01.2018 Neujahr ja
12.02.2018 Rosenmontag
30.03.2018 Karfreitag ja
01.04.2018 Ostersonntag ja
02.04.2018 Ostermontag ja
01.05.2018 Maifeiertag ja
10.05.2018 Christi Himmelfahrt ja
20.05.2018 Pfingstsonntag ja
21.05.2018 Pfingstmontag ja
31.05.2018 Fronleichnam ja
03.10.2018 Tag der Deutschen Einheit ja
01.11.2018 Allerheiligen ja
24.12.2018 Heiligabend
25.12.2018 1. Weihnachtstag ja
26.12.2018 2. Weihnachtstag ja
31.12.2018 Silvester
01.01.2019 Neujahr ja
04.03.2019 Rosenmontag
19.04.2019 Karfreitag ja
21.04.2019 Ostersonntag ja
22.04.2019 Ostermontag ja
01.05.2019 Maifeiertag ja
30.05.2019 Christi Himmelfahrt ja
09.06.2019 Pfingstsonntag ja
10.06.2019 Pfingstmontag ja
20.06.2019 Fronleichnam ja
03.10.2019 Tag der Deutschen Einheit ja
01.11.2019 Allerheiligen ja
24.12.2019 Heiligabend
25.12.2019 1. Weihnachtstag ja
26.12.2019 2. Weihnachtstag ja
31.12.2019 Silvester
01.01.2020 Neujahr ja
24.02.2020 Rosenmontag
10.04.2020 Karfreitag ja
12.04.2020 Ostersonntag ja
13.04.2020 Ostermontag ja
01.05.2020 Maifeiertag ja
21.05.2020 Christi Himmelfahrt ja
31.05.2020 Pfingstsonntag ja
01.06.2020 Pfingstmontag ja
11.06.2020 Fronleichnam ja
03.10.2020 Tag der Deutschen Einheit ja
01.11.2020 Allerheiligen ja
24.12.2020 Heiligabend
25.12.2020 1. Weihnachtstag ja
26.12.2020 2. Weihnachtstag ja
31.12.2020 Silvester
15.02.2021 Rosenmontag
28.02.2022 Rosenmontag
20.02.2023 Rosenmontag
12.02.2024 Rosenmontag

 

Anbei die bisherigen Videotitel:

Videolink zur Preview: https://www.youtube.com/watch?v=6Ou1SD6dAkg

Videolink zu Teil 1: https://www.youtube.com/watch?v=O9nz1yVqeLA

Videolink zu Teil 2: https://www.youtube.com/watch?v=mV4QNy1IXdM

Stickbesitzer können die Dateien auch aus den Verzeichnissen E808 bis E810 ziehen und die Übungen direkt parallel zu den Videos nachvollziehen.

Besten Dank für das Super-Feedback zu diesen Videos. Würde mich freuen, wenn Ihr die Titel Euren Kollegen weiterempfehlt.

Sonnige Grüße
Andreas

Aktion 47 beendet – DANKE!

Heute Nacht lief meine kleine Unterstützungsaktion zur Finanzierung einer Studienfahrt nach Buchenwald bzw. Auschwitz aus. Insgesamt sind durch EURE Stickkäufe seit Mitte September 450 Euro zusammengekommen.

Vielen Dank an die 9 Käufer! Ich hoffe, Ihr habt Spaß mit den Daten und den ganzen Übungsdateien. Und denkt daran, Ihr könnt den Stick noch mit zwei anderen beliebigen Personen teilen.

Die nächste Aktion wird sicherlich auch irgendwann kommen. Für Vorschläge und sinnvolle Aktionen bin ich dankbar. Gerne aus der Region Bochum / Ruhrgebiet.

Bis dahin nutze ich den kleinen Überschuss zur Weiterfinanzierung des Kanals / Blogs.

Sonnige Grüße
Andreas

P.S.: Ihr könnte auch den Kanal auf andere Weise unterstützen:

  • Liked Videos die euch gefallen
  • Kommentiert Videos, wenn Sie euch geholfen haben oder Fragen bestehen
  • Abonniert den Kanal (falls noch nicht geschehen)
  • Empfehlt den Kanal weiter

Jede Interaktion zählt und wirkt sich positiv auf das Ranking aus.

DANKE!

 

SVERWEIS statt verschalteter WENN-Funktion – wirklich WAHR

 

Häufig finde ich eine verschachtelte WENN-Funktion vor, wenn Werte in Listen abgeglichen werden sollen. Es geht nicht um exakte Treffer, sondern ein Wert soll auf die Zugehörigkeit in einem Zahlenbereich abgeprüft werden.

Im folgenden Beispiel unterstützt ein Unternehmen seine Mitarbeiter durch reduzierte Beiträge für ein Fitnesscenter. Statt 80 Euro pro Monat werden z.B. nur EUR 10 für Spitzenverdiener fällig und z.B. EUR 40 für Mitarbeiter, die mehr als 4.200 aber weniger als 4.800 Euro verdienen.

Jetzt kann man mit der WENN-Funktion natürlich jeden Bereich abprüfen.

E806a

=WENN([@Gehalt]<$G$5;$I$4;WENN([@Gehalt]<$G$6;$I$5;WENN([@Gehalt]<$G$7;$I$6;WENN([@Gehalt]<$G$8;$I$7;WENN([@Gehalt]<$G$9;$I$8;WENN([@Gehalt]<$G$10;$I$9;WENN([@Gehalt]<$G$11;$I$10;WENN([@Gehalt]<$G$12;$I$11;WENN([@Gehalt]<$G$13;$I$12;$I$13)))))))))

Alles klar?

Macht wirklich keinen Spaß, oder? Vor allem ist die Formel lang, schwer zu lesen, schwer zu überarbeiten und wirklich nur sehr schwer auf Richtigkeit hin zu überprüfen.

Wesentlich kürzer ist da der SVERWEIS Typ WAHR:

E806b.jpg

=SVERWEIS([@Gehalt];tabBeitrag;3;WAHR)

Der SVERWEIS Typ WAHR prüft, ob ein Wert in der Suchspalte der Matrix eingeordnet werden kann. Immer zu lesen als z.B. „von 0 bis unter 600“, „von 600 bis unter 1.200“ bzw. ganz unten „5.400 und größer“. Dafür müssen die Klassenuntergrenzen zwingend aufsteigend sortiert sein. Und statt Zahlen könnte man auch Texte in Buchstabenbereichen einsortieren lassen.

Bei meinen Geschwindigkeitstests war die verschachtelte WENN-Funktion nur ein wenig langsamer. Alternativ könnten auch die Funktionen VERWEIS bzw. INDEX und VERGLEICH genutzt werden.

Hier die Datei für eigene Übungszwecke:

E806 SVERWEIS Typ WAHR für Bereichssuche

Die Datei befindet sich auch auf dem USB-Stick „Excel700“ im Verzeichnis Excel\E806.

Hier geht es zum Video:

Videolink: https://youtu.be/VJlWdERSam4

Viel Spaß beim Nachbasteln und einen schönen Feiertag
Andreas

SVERWEIS – schnell und WAHR

 

Viele nutzen den SVERWEIS… und dann meistens den Typ FALSCH für die exakte Übereinstimmung. Doch ist die Suche im Suchvektor mit Typ FALSCH sehr langsam.

Typ WAHR wird weniger verwendet und dient eigentlich der Bereichssuche. Zudem verlangt Typ WAHR eigentlich, dass der Suchvektor in der Matrix sortiert ist.

Doch mit einem kleinen Trick und der Sortierung des Suchvektors (falls erlaubt), kann man seinem SVERWEIS einen wahren BOOST verschaffen. Bei meinen Messungen von mehreren SVERWEISEN zum Teil um den Faktor 1800 schneller. Ehrlich!

Warum ist der Typ WAHR eigentlich schneller? Ich stelle mir das so vor…. Denkt mal an das Kinderspiel, sich eine Zahl auszudenken und diese nur mit den Infos kleiner oder größer zu erraten. Denkt euch mal die 79!

Ein richtiger Spielverderber würde mit 1 beginnen…. Antwort: Zu klein. Na dann, die 2…. zu klein. Dann …. hmmm.. die 3? Spätestens jetzt gibt es lange Gesichter.

Also mit 50 einsteigen… zu klein …. 75 …. zu klein …. 87 …. zu groß …. und die gewünschte Zahl mit wenigen Suchpunkten eingrenzen und umzingeln. Geht bei dem SVERWEIS Typ WAHR und wenn die Suchmatrix sortiert ist. Also statt bei 100.000 Werten alle von oben bis unten durchzusuchen, vielleicht nur 20x den Bereich ausloten.

Doch hat der SVERWEIS eine natürliche Schwäche. Falls das Suchkriterium nicht gefunden werden kann, nimmt der SVERWEIS den nächst kleineren Wert. Ist doch eigentlich gut, wenn man Bereiche sucht – aber eben nicht für den exakten Treffer.

Folgende Funktion löst das Problem

=WENN(SVERWEIS(A4;Matrix;1;WAHR)=A4;SVERWEIS(A4;Matrix;4;WAHR);SVERWEIS(A4;Matrix;4;FALSCH))

Noch ein WENNNV oder WENNFEHLER um die WENN-Funktion packen und schon hat man einen wirklich sehr schnellen SVERWEIS.

Hier gibt es die Übungsdatei zum sechsten SVERWEIS-Titel

E802 SVERWEIS Sortierte Liste

Zum Video:

Videolink: https://www.youtube.com/watch?v=BSJVh7wfaKw

Hier gibt es die Datei zum siebten Titel

E805 SVERWEIS Typ WAHR Sortierte Liste

Hier gibt es das Video zum schnellen SVERWEIS:

Videolink: https://youtu.be/fG9zvu57LqI

Probiert es doch mal aus… vielleicht erst einmal mit einer Sicherung eurer Lieblingsdatei, die in der Performance nervt und viele SVERWEISe hat. Ich habe da in meiner Beratung schon wahre Wunder erlebt.

Viel Erfolg  und gute Suche
Andreas

 

Benzinverbrauch berechnen – Übung 04 – at Excel Experts

 

 

Übung 4 ist mal wenig abstrakt, hat es aber in sich!

Es sollen alle Tankvorgänge protokolliert werden. Kilometerstand beim Tanken, Preis und Füllmenge. Dabei muss noch geschaut werden, ob der Wagen dabei vollgetankt wurde.

Mit den Informationen kann man dann natürlich eine gute Übersicht über den eigenen Bleifuß und die Kilometerleistung gewinnen.

Das folgende Bild könnte in ähnlicher Form resultieren:

E804ab.jpg

Hier ist auch erkenntlich, wann vollgetankt wurde. Dabei werden die Berechnungen erst dann wieder eingesetzt, wenn nach mehrmaligen nicht-Volltanken mal wieder das Klacken am Zapfhahn zu hören ist.

Die Daten werden dann auch oben in der Gesamtübersicht berücksichtigt, in der man seine Langzeitentwicklung nachverfolgen könnte.

Leider stehen die Daten nur in folgender Form zur Verfügung:

E804b

Es gilt also, ein wenig „Aufzuhübschen“, die korrekten Formeln zu ermitteln und die Statistik im oberen Bereich aufzubauen. Wenn dann auch noch das Druckbild passt… PERFEKT!

Hier sind beide Dateien. Ausgangsdatei UND Lösung. Bitte nicht schummeln:

E804 Verbrauch Uebung

E804 Verbrauch

Hier geht es zum Video:

Videolink: https://youtu.be/LoyUexzARVo

Viel Spaß, die Lösungen folgen auch später erläutert bei YouTube.
Andreas

P.S.: Hier gibt es jetzt die erste Auflösung

Videolink: https://youtu.be/DmBiWKp_4gE

Teil 2 ist auch online:

Videolink: https://www.youtube.com/watch?v=0wqY1xPo5EI

Vertiefende Tutorials zum SVERWEIS

Mit dem ersten SVERWEIS-Video wurden lediglich die Grundlagen des ersten normalen SVERWEISes geschaffen und dazu gleich eine Menge Probleme eröffnet.

Die folgenden drei Titel behandeln die Themen:

  • Wie vermeide ich per Funktion die Meldung #NV?
  • Wie werde ich informiert, wenn ein gesuchter Wert doppelt in der Suchspalte vorkommt?
  • Wie nutze ich über Tabellen einen dynamisch mitwachsenden Datenbereich?

Hier geht es zum zweiten Teil:

Videolink: https://www.youtube.com/watch?v=9BlAdzB44qg

Weiter mit Teil 3:

Videolink: https://www.youtube.com/watch?v=V1yO1LGK8IE

Und dann nochmal alles von vorne mit Video # 4:

Videolink: https://www.youtube.com/watch?v=QlJGuKqqL64

Vielen Dank für das tolle Feedback zu den Titeln.
Beste Grüße

Andreas

Einfacher SVERWEIS – Teil 1

Die Excel-Funktion SVERWEIS (senkrechter Verweis) gehört mit zu den meist genutzten Funktionen in Excel und wird auch in vielen kaufmännischen Lehrgängen behandelt.

Syntax:

=SVERWEIS(Suchkriterium; Matrix; Spaltenindex; Bereich_Verweis)

Beim einfachen SVERWEIS wird geprüft, ob ein Wert (das Suchkriterium) in genau der gesuchten Schreibweise in einer Datenliste (die Matrix) vorkommt. Gesucht wird dabei in Spalte 1 dieser Matrix (dem Suchvektor). Gibt es eine exakte Übereinstimmung (Die exakte Suche muss mit FALSCH definiert werden), kann der Wert einer bestimmten Spalte der Matrix (der Spaltenindex) ausgegeben werden. Alternativ kommt die Meldung #NV.

E797.jpg

Formel in Zelle M9: Das Suchkriterium in Zelle M5 wird in der ersten Spalte (blau) der Matrix B5:J236 gesucht. Da genau das Suchkriterium gefunden werden soll, muss Bereich_Verweis mit FALSCH hinterlegt werden. Das Alter steht dann hier in Spalte 4 der Matrix.

Dieser einfache SVERWEIS hat noch ein paar Schwachstellen:

  • Was passiert, wenn die Spaltensortierung verändert wird?
  • Was passiert, wenn neue Daten unter die Liste kopiert werden?
  • Was passiert, wenn das Suchkriterium nicht gefunden werden kann?
  • Was passiert, wenn das Suchkriterium mehrfach im Suchvektor vorkommt?
  • Was muss man machen, wenn der Suchvektor nicht ganz links in der Matrix steht?
  • Wie muss die Formel umgestellt werden, wenn das Suchkriterium aus zwei oder mehr Zellinhalten erstellt wird?
  • Wie muss die Formel modifiziert werden, wenn es nicht einen, sondern zwei oder mehr Suchvektoren gibt?
  • Was könnte die Fehlerursache dafür sein, wenn z.B. nach der Zahl 10000 gesucht wird, diese zwar im Suchvektor steht aber doch nicht gefunden wird?
  • Was muss man machen, wenn mehrere Treffer möglich sind und diese auch aufgezeigt werden sollen?

Alle diese Punkte werden in den kommenden SVERWEIS-/INDEX-/AGGREGAT-Videos demonstriert.

Hier die Datei zum Download:

E797 SVERWEIS einfach

Hier geht es zum Video:

Videolink: https://youtu.be/qkES78Q6XIw

Viel Spaß beim Nachbasteln
Andreas