Excel-Quickies (Vol 31) 4

Ein paar schnelle Tipps, die dir die Arbeit mit Excel erleichtern werden.
 

Excel ist nicht kompliziert, man muss manchmal nur ein wenig um die Ecke denken…

Ein paar Beispiele dafür findest du in den heutigen Excel-Quickies: Vielleicht nicht sofort naheliegende, aber dafür schnell umsetzbare Kniffe ohne viel Schnickschnack.

Im diesem Beitrag zeige ich dir Tipps zu folgenden Themen:

  • Eine spezielle Anwendung der MEDIAN-Funktion
  • Wie berechnet man den ersten Arbeitstag eines Monats
  • Kommentare innerhalb einer komplizierten Formel

Da ist sicher auch für dich etwas dabei.

Excel-Quickie Nr. 103:

Eine sicherlich von den meisten Excel-Anwendern übersehene Funktion ist die MEDIAN-Funktion. Dabei handelt es sich um eine Statistik-Funktion, welche die Zahl zurückliefert, die in der Mitte einer Menge von angegebenen Werten liegt – eben den sogenannten Median.

Wozu man so etwas braucht? Damit lässt sich z.B. rechnerisch ganz einfach ermitteln, ab ein gegebenes Datum innerhalb eines definierten Bereichs liegt, also zwischen einem Anfangs- und einem Enddatum. Im folgenden Bild sieht man, dass das erste gesuchte Datum nicht im gesuchten Bereich liegt, das zweite hingegen schon. Dazu werden einfach alle drei Datumswerte an die MEDIAN-Funktion übergeben und über einen Vergleich geprüft, ob der so berechnete Median mit dem gesuchten Datum übereinstimmt.

Die MEDIAN-Funktion

Die MEDIAN-Funktion

Die ermittelten FALSCH bzw. WAHR-Werte lassen sich nun wunderbar per bedingter Formatierung in einen Farbbalken umwandeln und als einfaches GANTT-Diagramm für einen Projektplan verwenden:

Im ersten Schritt wird für jeden Tag in der Zeile 1 berechnet, über dieses Datum dem Median entspricht (als WAHR oder FALSCH)

Ein praktischer Anwendungsfall

Ein praktischer Anwendungsfall

Mit einer Formatierungsregel wird dann jede Zelle mit WAHR blau eingefärbt.

Regel für eine bedingte Formatierung

Regel für eine bedingte Formatierung

Und um die Texte in den Zellen zu unterdrücken, verwenden wir das benutzerdefinierte Zahlenformat ;;;

Benutzerdefiniertes Zahlenformat

Benutzerdefiniertes Zahlenformat

(Dieser Trick ist in Quickie Nr. 48 beschrieben)

Fertig ist unser kleiner Projektplan:

Das fertige Gantt-Diagramm

Das fertige Gantt-Diagramm

Excel-Quickie Nr. 104: Wie berechnet man den ersten Arbeitstag eines Monats

Excel bietet allerhand Funktionen für Datumsberechnungen, eine davon ist die Funktion ARBEITSTAG. Mit ihr findet man das Datum, das eine bestimmte Anzahl von Arbeitstagen vor oder nach einem Ausgangsdatum liegt. Wochenenden werden dabei automatisch übersprungen.

Wie kann man damit aber den ERSTEN Arbeitstag eines Monats berechnen? Ganz einfach, man addiert zum letzten Kalendertag des Vormonats einen Arbeitstag.

Beispiel:
Um den ersten Arbeitstag des Oktobers 2017 zu berechnen, lautet die Formel
=ARBEITSTAG("30.09.2017";1)

Die Funktion ARBEITSTAG

Die Funktion ARBEITSTAG

Wer es etwas flexibler möchte, kann anstelle eines fixen Datums auch auf bestimmte Eingabezellen verweisen, die dann mit der DATUM-Funktion aufbereitet werden:
=ARBEITSTAG(DATUM(B7;B8;0);1)

Eine flexiblere Variante

Eine flexiblere Variante

Hier habe ich einen weiteren kleinen Trick angewendet: Anstelle des letzten Tages des Vormonats wird der nullte Tag des aktuellen Monats angegeben. Das Ergebnis bleibt gleich.

Excel-Quickie Nr. 105: Kommentare innerhalb einer komplizierten Formel

Diesen Tipp hat mir vor längerer Zeit mein Leser Thomas Verheyen zugeschickt, der öfter mit komplizierten Formeln arbeitet und sich auf diese Weise zumindest rudimentäre Kommentare zur Funktionsweise direkt in die Formel einbaut.

Der Trick ist, den Kommentar in eine WENN-Funktion einzubauen, bei der der erste Teil niemals ausgeführt wird.

=WENN(FALSCH;"Hier steht mein Kommentar zu der folgenden komplizierten Formel im Klartext";komplizierte_Formel)
oder
=WENN(WAHR;komplizierte_Formel;"Hier steht mein Kommentar zu der komplizierten Formel im Klartext")

Kommentar innerhalb einer Formel

Kommentar innerhalb einer Formel

Danke, Thomas, für diesen echten Excel-Quickie!

So, das war’s wieder für heute. Viel Spaß beim Ausprobieren!

 

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

4 Gedanken zu “Excel-Quickies (Vol 31)