Kategorie: Excel

Neue Kursreihe – Excel-Grundlagen – Basics

 

Hallo,

schön, Dich hier zu begrüßen! In dieser neuen Grundlagenreihe möchte ich nur absolute Excel-Basics zeigen, die ohne große Voraussetzungen direkt in Excel umgesetzt werden können.

Gerne schaue Dir die Videoreihe an, bevor Du einen Excel-Grundkurs besuchst oder wenn Du ganz neu ins Programm Excel „hineingeworfen“ wirst. In einem Grundkurs werden viele neue Inhalte auf Dich einströmen, so dass eine vorherige Beschäftigung mit dem Thema Dir helfen kann, stärker von dem Grundkurs zu profitieren.

Du brauchst natürlich Excel auf dem Rechner. Maus, Tastatur und Bildschirm setze ich mal voraus 😉

Im Nachfolgenden beginne bitte erst einmal, die folgenden Daten in ähnlicher Form in ein leeres Tabellenblatt einzugeben.

Excel 01 - Eingabeübung

Die PDF-Datei kann auch hier heruntergeladen werden: Excel 01 – Eingabeübung

Klicke mit der linken Maustaste oben links in Zelle A1 und trage dort die Zahl 2017 ein. Anschließend bitte nur die Daten (rot umrandet) bis zur Zeile April eingeben. Also nur den Datenbereich von Zelle A1 bis zur Zelle E7 bzw. auch Bereich A1:E7 genannt.

Gebe die Daten und Beschriftungen bitte so ein, wie Du es machen würdest und schaue Dir dabei mal an, wie Du arbeitest. Den roten Rahmen oder die schwarzen Linien brauchst Du nicht einzufärben, das machen wir später schön komfortabel.

In den anschließenden Videos erfährst Du mehr zu den Arbeitstechniken, die Dir im Umgang mit Excel helfen.

Inhalte des Kurses werden unter anderem sein:

  • Orientierung – Arbeitsmappe, Tabellen, Spalten, Zeilen, Bereiche
  • Effizientes Markieren und Navigieren
  • Dateneingabe
  • Arbeitserleichterung per AutoAusfüllen
  • Speichern und Laden von Excel-Arbeitsmappen
  • Korrigieren von Eingaben und Aktionen rückgängig machen
  • Einfache Formatierungen von Zahlen
  • Grundlegende Berechnungen
  • Grundlegender Aufbau von Tabellen
  • Ausdruck überprüfen

Das war es dann auch schon. Mehr gibt es dann in den anderen Kursreihen

Hier gibt es auch das YouTube-Video:

Videolink: https://youtu.be/ykr2LlAlKyA

Freue mich über Dein Feedback, Dein Abo und jeden Daumen nach oben.
Viel Erfolg mit Excel

Andreas

P.S.: Die Auflösung ist auch schon online:

Videolink: https://youtu.be/CCXOy2j5VAg

Advertisements

Excel-Stammtisch NRW am 21.03.2018 in Bochum

Hallo!

Und am 21. März 2018 geht es um 19 Uhr in Bochum weiter… wir starten wieder pünktlich zum Excel-Stammtisch in einem Tagungsraum des Jugendherbergswerks – der selbe Raum wie bei Januar-Treffen. Die DJH liegt im Bermuda3Eck, knapp 5-10 Minuten zu Fuß vom Hauptbahnhof entfernt. Bzw. direkt über die U-Bahn-Haltestelle Musikforum / Engelbert-Brunnen erreichbar.

Beim ersten NRW-Treffen im Januar waren wir ca. 15 Personen und haben uns in knapp 4 Stunden rund um das Thema Excel ausgetauscht, zudem habe ich einige Themen zu AGGREGAT etc. vorne über einen Beamer präsentiert.

Wenn ihr Themen habt, die ihr vorne vorstellen mögt… GERNE! Ich hätte zwar auch immer kleine Themen zu Excel, Power BI, OneNote, Camtasia und Co., würde mich aber auch gerne mal von anderen inspirieren lassen.

Die Jugendherberge bietet an dem Abend auch die Möglichkeit fürs Büfett (buffet) an. Der Beitrag läge bei knapp 10 Euro. Ich müsste allerdings vorbestellen.

Wenn Du Lust und Zeit zur Teilnahme hast, sende mir bitte eine kurze E-Mail an thehos@at-training.de. Bitte erwähne auch, ob Du konkrete Wünsche oder Themen hast und ob Du dort auch speisen magst. Ansonsten stehen in den nahe gelegenen Restaurants genug Möglichkeiten offen.

Würde mich freuen, wieder viele interessierte Excel-Nutzer zu treffen.
Wir müssten mal durchzählen, der wievielte Stammtisch das gerade ist… irgendwie müssten wir mit Bochum (hier begann alles im November 2014), Düsseldorf, Köln, Aachen, Hamburg, Zürich, Basel, München, Frankfurt am Main, Münster und Wien die 50 Termine geschafft haben (dazu noch die inoffiziellen in London und Amsterdam).

Excellente Grüße aus Köln – Direkt von der Microsoft Device Tour Plus
Andreas

P.S.: Zum Excel-Stammtisch werde ich eine WhatsApp-Gruppe anlegen. Dort und auf meiner Facebook-Seite http://www.facebook.com/excelthehos gibt es immer die aktuellsten Infos zum Stammtisch.

In der WhatsApp-Gruppe soll es nur Hinweise zu den einzelnen Terminen geben und anschließend Links zu weiterführenden Themen. Dort bitte kein Spam. Wer hier aufgenommen werden möchte, kontaktiert mich bitte per WhatsApp und dem Stichwort STAMMTISCH.

Immer Ärger mit der KALENDERWOCHE

Es war einmal…

Den 15. September 2016 werde ich wohl immer im Hinblick auf Datumsfunktionen im Gedächtnis behalten!

Ich befand mich an diesem Tag in einem Beratungstermin, während auf einmal das Mobiltelefon meines Kunden klingelte… keine Details… aber da wartete ein verzweifelter Kunde am Hafen einer amerikanischen Metropole auf seine dringend benötigte Ware. Vereinbart gewesen sei Kalenderwoche 38. Entspannte Gesichter… unser dreiteiliger Monatsplaner zeigte für den Tag Kalenderwoche 37 an, die Waren seien gerade in Hamburg.

Jedoch wurde von einer der Parteien bei der Terminabstimmung nicht der kulturelle Unterschied zwischen den USA und Deutschland in der Berechnungsweise der Kalenderwochen bedacht. In den USA war tatsächlich schon Kalenderwoche 38. Bei uns immer noch Kalenderwoche 37.

Inhaltlich wird unsere Kalenderwoche von der ISO 8601[1] bzw. DIN EN 28601 geregelt. Darin ist geklärt, dass eine Woche montags startet und Kalenderwoche 1 diejenige Woche ist, in die der 4. Januar fällt, die also praktisch mehr Tage des neuen Jahres beinhaltet – bzw. wenn in der Woche auch der Donnerstag im neuen Jahr liegt.

DatumISO2

Abb. 1: Kalenderwoche nach DIN EN 28601

In den USA beginnt Kalenderwoche 1 automatisch am 1. Januar – das ist praktisch und leicht zu merken. Der 31. Dezember liegt in Kalenderwoche 52 oder 53. Zudem gibt es zwei Typen von Wochen. Typ 1 zählt eine Woche von Sonntag bis Samstag[2], Typ 2 zählt wie bei uns von Montag bis Sonntag.

DatumISO1

Abb. 2: Kalenderwoche US vs. ISO

Eine Übersicht über die üblichen Arbeitstage eines Landes ist auch bei Wikipedia[3] zu finden. Dass auch der Begriff Woche[4] nicht einheitlich definiert ist, lässt sich dort ebenfalls nachlesen

Zudem werde der Begriff Kalenderwoche im Amerikanischen eher selten genutzt, eher wird die Woche des entsprechenden montags genannt – week of september, 12 anstatt von workweek 38 hätte hier wohl Kommunikationsprobleme vermeiden helfen können.

Das Projekt konnte zum Glück auch mit der Lieferverzögerung weitergeführt werden, hat uns aber etwas ganz deutlich vor Augen geführt:

Ich darf Excel nicht einfach blind vertrauen und muss meine intuitive Nutzung hinterfragen.

Ich muss wissen, mit welchen Grundeinstellungen bzw. Annahmen arbeitet Excel und was bewirken die Parameter der Funktionen.

Die Excel-Funktion KALENDERWOCHE nutzt ohne weiteren Parameter das amerikanische System Typ 1 mit dem Wochenstart auf dem Sonntag.  Typ 2 berechnet die amerikanische Woche ab dem Montag. Nur Paremeter Typ 21 ab Excel 2010 bzw. die seit Excel 2013 eingeführte Funktion ISOKALENDERWOCHE nutzen unsere Kalenderwochenbezeichnung.

Du fragst Dich sicher: Woher soll man denn so etwas wissen?

Alles richtig gemacht! Du bist auf dem richtigen Weg. In diesem Blog möchte ich Dir helfen, Excel-Funktionen korrekt einzusetzen und neue Möglichkeiten abzuwägen.

In einer neuen Reihe gibt es nach und nach sämtliche Zeit- und Datumsfunktionen in Neuauflage dargestellt.

Bis dahin… eine Frohe Weihnachtszeit

Andreas

[1] https://de.wikipedia.org/wiki/ISO_8601

[2] https://de.wikipedia.org/wiki/Sonntag

[3] https://en.wikipedia.org/wiki/Workweek_and_weekend

[4] https://de.wikipedia.org/wiki/Woche

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

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

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

 

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.

2016.png

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:

  1.  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.
  2. 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