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:
- Quickie 103: Eine spezielle Anwendung der MEDIAN-Funktion
- Quickie 104: Wie berechnet man den ersten Arbeitstag eines Monats
- Quickie 105: Kommentare innerhalb einer komplizierten Formel
Da ist sicher auch für dich etwas dabei.
Excel-Quickie Nr. 103: Liegt das Datum innerhalb eines Bereichs?
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 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
Mit einer Formatierungsregel wird dann jede Zelle mit WAHR blau eingefärbt.
Regel für eine bedingte Formatierung
Und um die Texte in den Zellen zu unterdrücken, verwenden wir das benutzerdefinierte Zahlenformat ;;;
Benutzerdefiniertes Zahlenformat
(Dieser Trick ist in Quickie Nr. 48 beschrieben)
Fertig ist unser kleiner Projektplan:
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
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
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
Danke, Thomas, für diesen echten Excel-Quickie!
So, das war’s wieder für heute. Viel Spaß beim Ausprobieren!
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.
Um einen Kommentar zu einer komplizierten Formel zu erfassen kann man auch die Funktion: N(„Kommentar“) zu einer Formel addieren. Beispiel: =5*13+N(„Hier werden zwei Werte multipliziert“)
Den Tipp habe ich im Tipp 36 auf der Seite http://www.controllerspielwiese.de/index.htm?Inhalte/Toolbox/exltip.htm gefunden.
Gruß
Sven
Hallo Sven,
danke für den Tipp, das ist auch eine sehr gute Lösung.
Schöne Grüße,
Martin
Das mit dem Median ist ein praktischer Trick. Funktioniert auch wunderbar mit normalen Zahlen. Danke.
Hallo Marcel,
gern geschehen.
Schöne Grüße,
Martin