Kategorie: Excel

06/10/2017

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

03/10/2017

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:

=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

24/09/2017

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.

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

19/12/2016

Das Beste kommt zum Schluss!

Ein verrücktes Jahr mit vielen kleineren und größeren Katastrophen, Highlights und spannenden Entwicklungen geht zu Ende. Zeit für ein kleines Resumee.

In 2016 habe ich bislang 99 Videos online gestellt – davon 30 auf meinem neuen Kanal www.youtube.com/atExcel. Das sind knapp 2 Titel pro Woche, wobei wir ja noch ein paar Tage bis Silvester übrig haben. In der Zeit meiner Umzüge und der ganzen Renovierungen(privat und beruflich) hatte ich keine Videos erstellt, so dass sich im Verlauf des Sommers eine schöne „Delle“ im Zugriffsverlauf erkennen lässt. Von knapp 410.000 Zugriffen pro Monat zu Jahresbeginn bin ich aktuell wieder bei knapp 385.000 pro Monat angelangt.

Neu dabei waren einige Videos, die mal nicht Excel mit meiner Stimme im Off zeigten, sondern in denen ich ein paar Statements abgegeben habe. Kleine Smartphone-Videos mit miserablem Ton. Diese Videos wurden von den meisten Zuschauern ganz gut angenommen, so dass ich damit in 2017 weiter machen möchte. Ich denke, auf Premiere-Effekte kann ich dabei verzichten, oder? Insgesamt war es aber bislang das erfolgreichste Jahr meiner bislang 6,5jährigen YouTube-Laufbahn.

Als ewiges Highlight in meinen Videos ist mal wie der der Word-Klassiker „Inhaltsverzeichnis“ mit insgesamt 202.000 Zugriffen im aktuellen Jahr dabei. Vielleicht sollte ich das Produkt wechseln und in Zukunft Word-Videos erstellen! Obwohl… dann lieber OneNote.

Insgesamt freue ich mich natürlich über fast 22.000 Abonnenten und die vielen treuen Stamm-Kommentatoren. Auch hier hat sich wieder gezeigt, dass es sich lohnt, sein Wissen gratis online zu stellen. Ich habe wieder so viele Tipps erhalten, wie man etwas anders und auch besser machen kann.

alltime

Mein USB-Stick Excel 700+ hat die 64 GB-Marke gesprengt. Ich stelle den Vertrieb für Privatpersonen erst einmal zum Jahresende ein, bis ich eine kosten- und zeitsparendere Lösung gefunden habe. Vielen Dank an alle, die sich auch über diesen Weg fortbilden.

Und vor allem der Austausch bei den Excel-Stammtischen war großartig. Ich habe in diesem Jahr in Wien, Köln, München, Münster und Frankfurt am Main teilgenommen.

Mein großes Highlight war aber der dritte Amsterdam Excel Summit, bei dem sich viele Excel MVPs im Vorfeld ihres offiziellen Treffens ausgetauscht haben. Auch wegen des Netzwerks lohnt sich die Teilnahme. In 2017 bin ich sicher wieder dabei http://topexcelclass.com/index.php/amsterdam-excel-summit/. Danke an Tony de Jonker und Jan Karel Pieterse für dieses erstklassige Event und das wirklich phantastische Abendprogramm.

Mein Dank für 2016 gilt zudem Johannes Curio für die Organisation des Excel-Stammtischs in Basel und Dr. René Martin für die regelmäßige Ausrichtung in München. Prof. Dr. Johannes Schwanitz und sein Team von der FH Münster haben zwei tolle Treffen in Münster organisiert.

Natürlich allen Lesern des Blogs. Hey… Ihr dürft gerne auch ein Feedback in den Kommentaren hinterlassen. Danke!

Für 2017 habe ich zwei ganz ehrgeizige Ziele:

Ich will die Schulen, Berufsschulen und Unis erreichen! Ich habe schon viel Feedback von Schülern und Studenten erhalten, die meine Videos im Unterricht gucken „mussten“. Wenn die Videos über YouTube für den Unterricht genutzt werden, dann möchte ich gerne noch ein paar Inhalte dafür besteuern.Warum Excel für die Ausbildung? Weil dort noch viel geleistet werden kann. Ich bin stolz über den erreichten Anteil und das positive Feedback. Aus meiner Erfahrung bei meinen Kunden weiß ich, dass diese Kenntnisse benötigt werden.
Ich möchte auf YouTube 100.000 Abonnenten erreichen. Wenn nur einige von euch die Videos im Unternehmen oder an der Uni weiter empfehlen – vielleicht über einen firmeninternen Newsletter oder als Tipp zur Einsparung von Fortbildungskosten an die Personalentwicklung, dann wäre ich dort schon einen großen Schritt weiter.Warum gerade 100.000? Ich stehe natürlich auch mit YouTube direkt in Kontakt. Als YouTube-Partner erhalte ich Tipps und direkte Empfehlungen von meiner Partner-Managerin aus London. Die haben einen tolles Team bei YouTube, da wäre ich gerne mehr involviert.

Von daher meine Zielsetzung, ich mache kräftig mit den YouTube-Kanälen und diesem Blog weiter. Wenn es gut war, dann mehr davon! Und zudem möchte ich die Vernetzung von uns Excel-Anwendern voran treiben. Es gibt noch einige weiße Stammtisch-Flecken auf der Landkarte…

Ich wünsche euch allen ein ganz tolles Weihnachtsfest und ein erfolgreiches, glückliches und gesundes Neues Jahr.

Träumt nicht von Excel!
Euer Andreas

P.S.: Fast vergessen. Dieser Blog wurde in 2016 bislang von 133.480 unterschiedlichen Besuchern aufgesucht. Da ich in diesem Jahr noch nicht so viele Artikel veröffentlicht habe, ein schöner Erfolg. Danke!

blog

12/12/2016

Excel in der Schule?

Mein jährliches Ritual bei einigen meiner größeren und mittelständischen Kunden: ab September vertrauen mir diese Firmen für einige Tage ihre neuen Auszubildenden an. Das sind knapp 100 junge Menschen pro Jahr, die ich knapp zwei Tage durch Office führen darf. Eine große Ehre und Verantwortung – und zudem macht’s Spaß… auch wenn ich mittlerweile vermutlich älter als manche Eltern sein dürfte.

Klar ist, in diesen Unternehmen werden die Office-Produkte, allen voran Excel, Outlook und Word eingesetzt.

Unglaublich, wie unterschiedlich die Ausbildung und Nutzung in den Office-Produkten während der Schulzeit gehandhabt wird. Im worst case gab es vielleicht gerade mal zwei Stunden Excel oder Calc während der gesamten Schulzeit. Die meisten mussten zumindest PowerPoint für Ihre Präsentationen verwenden. Das betrifft knapp 75 Prozent aller Auszubildenden, denen ich jährlich begegne! Mailen? Fehlanzeige… Snapchat, Instagram und WhatsApp sind die Regel. Digitale Mediennutzung? Klar, den ganzen Tag… Handy, Tablet, Konsole und auch PC. Aber das, was in den Unternehmen genutzt wird kommt in der Schule eindeutig zu kurz. Zumindest, bei der kleinen Stichprobe, der ich begegne… über mehrere Firmen und Branchen hinweg. Ist der digitale Wandel schon in allen Schulen angekommen?

Ein wenig stechen die Schulabgänger hervor, die ein Fachabitur gemacht haben. Hier gab es oft längere EDV-Projekte während der Oberstufe. Und klar, es gibt immer einige in Office superfitte Auszubildende – doch ist dies nicht meine gefühlte Mehrheit.

Woran liegt es? Keine Lust bei den Lehrern? Zu schlechte EDV-Räume? Keine Zeit? Sind die Medien wie ActiveBoard im Unterrichtsraum vorhanden, werden aber nicht konsequent genutzt? Oder wird die Handhabung gebräuchlicher Anwendungen ob LibreOffice oder Microsoft Office einfach nicht als wichtig angesehen? Beziehungsweise wird vorausgesetzt, dass sich die Schüler privat damit auseinandersetzen.

Dann bei der Bewerbung auf den nächsten Job, sollen die Office-Kenntnisse auf einmal perfekt sein. Hier darf dann die Berufsschule noch einiges nachholen. Ich zumindest bin sehr froh, dass meine Kunden selbst die Initiative ergreifen, und noch einmal die Bedeutung der Standardanwendungen für ihre Bedürfnisse hervorheben.

Bei meinen eigenen Kindern habe ich ein Wahnsinnsglück. Nicht, dass Sie schon von Haus aus mit dem Computervirus infiziert sind. In der gemeinsam ausgewählten Schule gibt es pro Schuljahr ca. 4 Laptopklassen (Schwerpunkte Nawi und Englisch) mit Leasinggeräten ab Klasse 5. Der Matheunterricht ist zum Teil mit Excel kombiniert – war ganz schön baff, als ich die Fragen der ersten Klassenarbeit aus Klasse 5 gesehen habe. Hausarbeiten werden in Word geschrieben und hochgeladen, Präsentationen mal eben fix in PowerPoint erstellt und jetzt kommt bald noch OneNote hinzu. Die meisten Lehrer sind digital fit und motiviert. Ein Lehrer steht als Expert Educator mit Microsoft und einem Lehrernetzwerk in Kontakt. https://education.microsoft.com/ ist sicherlich eine Seite, die man sich als Schule mal anschauen sollte. Es geht also…

Doch wie bekommen wir, falls überhaupt gewünscht, mehr Office-Kompetenz in die Schulen? Ich finde es schade, dass junge Menschen mit 18, 19, 20 Jahren Excel bislang kaum kennenlernen durften oder erst an der Uni ihr erstes mehrseitiges Dokument mit Inhaltsverzeichnis erstellen. Es gibt genug spannende Themen und Möglichkeiten für Projekte, die sich bestimmt auch mit Mathe, Sowi oder anderen Fächern kombinieren lassen.

Hier noch einmal ein Link http://www.microsoft.com/de-de/education/school-leaders/showcase-schools/default.aspx zum Microsoft Showcase-Programm für interessierte Schulen. Und nein, hier geht es nicht primär um Office und Microsoft-Produkte. Hier geht es um ein ganzheitliches Konzept um den digitalen Wandel in die Schulen zu bringen.

Es ist Initiative von allen gefordert: Schülern, Lehrern, Eltern, Wirtschaft und Politik.

Träumt nicht von Excel!
Euer Andreas

05/12/2016

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