Eine der typischen Aufgaben für einen Microsoft Excel-Benutzer. Wir haben zwei Datumsbereiche vom Typ „Anfang-Ende“. Die Herausforderung besteht darin, festzustellen, ob sich diese Bereiche überschneiden, und wenn ja, um wie viele Tage.
Schnittpunkt oder nicht?
Beginnen wir mit der Lösung der Frage, ob es prinzipiell einen Schnittpunkt von Intervallen gibt? Angenommen, wir haben eine Tabelle mit Arbeitsschichten für Mitarbeiter wie diese:
Es ist deutlich zu sehen, dass sich die Arbeitsschichten von Yaroslav und Elena überschneiden, aber wie kann man dies berechnen, ohne auf die Erstellung eines Kalenderplans und visuelle Kontrolle zurückzugreifen? Die Funktion wird uns helfen SUMMENPRODUKT (SUMMENPRODUKT).
Lassen Sie uns eine weitere Spalte in unsere Tabelle mit einer Formel einfügen, die den booleschen Wert TRUE ergibt, wenn sich die Daten überschneiden:
Wie viele Tage dauert die Überfahrt?
Wenn es grundsätzlich nicht einfach ist zu verstehen, ob sich unsere Intervalle schneiden oder nicht, aber genau zu wissen, wie viele Tage genau in den Schnittpunkt fallen, dann wird die Aufgabe komplizierter. Logischerweise ist es notwendig, bis zu 3 verschiedene Situationen in eine Formel zu „pumpen“:
- Intervalle überschneiden sich nicht
- eines der Intervalle absorbiert das andere vollständig
- Intervalle schneiden sich teilweise
Von Zeit zu Zeit sehe ich die Implementierung dieses Ansatzes durch andere Benutzer, die eine Reihe von verschachtelten IF-Funktionen usw. verwenden.
Tatsächlich kann alles wunderbar mit der Funktion erledigt werden MEDIAN (MEDIAN) aus Kategorie Statistisch.
Wenn wir den Beginn des ersten Intervalls bedingt als bezeichnen N1, und das Ende für K1, und der Beginn des zweiten N2 und Ende für K2, dann kann unsere Formel allgemein wie folgt geschrieben werden:
=MITTEL(N1;K1+ 1;K2+1)-MEDIAN(N1;K1+ 1;N2)
Kompakt und elegant, nicht wahr? 😉
- Wie funktioniert Excel eigentlich mit Datumsangaben? Wie berechnet man die Anzahl der Kalender- oder Werktage zwischen Daten?
- Wie erstellt man einen Kalenderplan (Feiertage, Schulungen, Schichten…) in Excel mit bedingter Formatierung?
- Prüfen einer oder mehrerer Bedingungen mit IF (IF)-Funktionen