Zuerst hört sich die Frage ganz einfach an, aber dann kam ich ins Schleudern:
„Hallo Rene
zum Thema Datumsberechnungen hätte ich gleich eine Frage:
kann Excel auch Zeiträume erkennen, die sich überschneiden, aber
unterschiedliche Anfangs- und Endzeiten haben?
Also zum Beispiel:
Mitarbeiter A arbeitet vom
01.05.2021 bis 31.08.2021
Mitarbeiter B arbeitet vom
01.06.2021 bis 15.09.2021
In welchem Zeitraum haben
beide gearbeitet
Oder
Von
Bis
Thema
8:00
12:00
Nachdenken
11:30
12:30
Pause
12:30
15:00
Nix tun
Ich überlege. Und erweitere das Beispiel. Das Oktoberfest hätte in diesem Jahr vom 18. September bis 03. Oktober stattgefunden. Adelheid ist von 01.09. bis 30.09 in München; Basti vom 25.09. bis 25.10.; Christoph vom 27.09. bis 30.09.; Doris vom 10.09. bis 10.10.; Erich vom 01.09. bis 10.09. und Franziska vom 10.10. bis 20.10. Wie viele Tage hätten sie aufs Oktoberfest gehen können?
Im ersten Schritt habe ich das visualisiert:
Ich beginne mit Erich und Franziska: Wenn Ende < Beginn Oktoberfest, dann 0. Wenn Anfang > Ende Oktoberfest, dann 0:
=WENN(B33<$B$21;0)
Dann die umschließenden Bereiche:
=WENN(UND(B28>=$B$21;B29<=B22);B29-B28+1;0)
Und schließlich die überschneidenden Bereiche:
Wenn Anfang <= Anfang Oktoberfest und Ende <= Ende Oktoberfest, dann Ende – Anfang Oktoberfest:
=WENN(UND(B24<=$B$21;B25<=$B$22);B25-$B$21+1;0)
Analog die zweite Überschneidung:
Wenn Ende >= Ende Oktoberfest und Anfang >= Anfang Oktoberfest, dann Ende Oktoberfest – Anfang:
=WENN(UND(B27>=$B$22;B26>=$B$21);$B$22-B26+1;0)
Dabei fällt auf, dass die ersten beiden Fälle mit einbezogen werden – man muss sie explizit ausschließen, beispielsweise mit einem dritten Fall:
und Ende >= Anfang Oktoberfest, beziehungsweise: Anfang <= Ende Oktoberfest:
Ein Summieren der Varianten verbietet sich, da stets mit >= und <= gearbeitet wurde. So könnte ein Datumsbereich vom 18.09. bis 20.09. in zwei der sechs Kategorien fallen. Entweder man ändert einige der <=in < oder man baue den Baum auf:
Fall: leere Menge
Fall: ganzer Bereich
Fall: Überschneidung
Fall:
=WENN(ODER(B33<$B$21;B32>$B$22);0)
2. Fall a: Datumsbereich liegt im Oktoberfestzeitraum:
Natürlich hätte man die Bedingungen auch umdrehen können.
Natürlich hätte man den Bedingungsbaum auch anders aufbauen können:
Fall: Anfang <= Oktoberfest Anfang? Ja: Fall a) Ende vor Oktoberfest Anfang? Fall b) Ende nach Oktoberfest Ende? Fall c) Ende zwischen Oktoberfest Anfang und Ende? […]
Die Teilnehmerinnen waren begeistert und erschöpft. Leider haben sie nur Excel 2016. Ich überlegte mir, ob man das Problem nicht mit den Matrixfunktionen SEQUENZ & co lösen kann. Die Antwort: Man kann:
Wir beginnen mit Adelheid. Im ersten Schritt werden alle Adelheid-Tage und Oktoberfesttage aufgelistet. Die Funktion SEQUENZ hilft hierbei:
=SEQUENZ(A6-A5+1;1;A5)
Nun kann man zählen, wie oft jeder Adelheid-Tag in der Oktoberfest-Tagesliste vorkommt – einmal oder kein Mal:
Für die anderen Personen muss diese Formel nur entsprechend angepasst werden. Gibt es keine Überschneidung wie bei Erich und Franziska gibt die Formel den Wert #NULL! aus. Wichtig ist das Leerzeichen in der Mitte der Formel. Dadurch wird die Schnittmenge ermittelt.
Salü
Ernst
PS: Nachtrag:
Allerdings
kann man durch eine kleine Änderung der Formel meines Lösungsvorschlages (aus
Zeilen() mache Zeile() und gebe die Formel als Matrixformel ein) eine
einspaltige Matrix erzeugen, in der die Datumswerte der Schnittmenge
eingetragen sind.
Und schließlich kann man das Problem auch mit VBA lösen. Man muss zwei Bereiche (Range) definieren und die Schnittmenge (Application.Intersect) bestimmen:
Dim xlBereich1 As Range
Dim xlBereich2 As Range
Dim xlSchnittmenge As Range
With ThisWorkbook.Worksheets("Helmut")
Set xlBereich1 = .Range(.Cells(Range("B2").Value2, 1),
.Cells(.Range("B3").Value2, 1))
Set xlBereich2 = .Range(.Cells(Range("B5").Value2, 1),
.Cells(.Range("B6").Value2, 1))
End With
Set xlSchnittmenge = Application.Intersect(xlBereich1, xlBereich2)
MsgBox xlSchnittmenge.Cells.Count
Zuerst habe ich mich geärgert. In PowerQuery gab es früher ein Symbol „Von Tabelle“. Daraus wurde in der Gruppe „Daten abrufen und transformieren“ das Symbol „Aus Tabelle/Bereich“.
Seit ein paar Tagen heißt es nun „Vom Blatt“
Muss das sein? Ständiges Umbenennen?
Frank Arentd-Theilen hat mich auf den Grund hingewiesen (danke für den Hinweis):
Ja – denn nun kann man Listen in Excel, die mit den neuen Arrayfunktionen erstellt wurden, beispielsweise mit FILTER, SORTIEREN und SORTIERENNACH in PowerQuery importieren:
Das funktioniert auch mit der Funktion SEQUENZ:
Okay – zugegeben – leider nicht immer. Wenn diese Matrixfunktionen innerhalb einer Liste stehen, wie beispielsweise hier in diesem Monatskalender:
dann wandelt PowerQuery die gesamte Liste in eine (intelligente) Tabelle um und – scheitert! Klar: Tabellen dürfen keine Matrixfunktionen verwenden …
Dynamic array resizes may trigger additional calculation passes to ensure the spreadsheet is fully calculated. If the size of the array continues to change during these additional passes and does not stabilize, Excel will resolve the dynamic array as #SPILL!.
Nochmal langsam:
This error value is generally associated with the use of RAND, RANDARRAY, and RANDBETWEEN functions. Other volatile functions such as OFFSET, INDIRECT, and TODAY do not return different values on every calculation pass.
Ich probiere es aus: In einer Spalte stehen Zufallswerte:
=ZUFALLSBEREICH(„1.1.2020″;“31.12.2020“)
Die beiden Funktionen SORTIEREN und SORTIERENNACH funktionieren problemlos:
Jedoch erzeugen die beiden Funktionen FILTER und EINDEUTIG regelmäßig einen Fehler:
=FILTER(A3:A100;A3:A100>44000) und =EINDEUTIG(A3:A100)
Allerdings nicht immer:
Die Funktion ZUFALLSMATRIX, welche die Funktion ZUFALLSBEREICH in den Parametern min und max verwendet, bleibt erstaunlicherweise stabil:
Im ersten Moment erstaunlich; allerdings konsequent, korrekt und nützlich: sind mehrere Zellen miteinander verbunden, liefert die Funktion SEQUENZ die Fehlermeldung #ÜBERLAUF – das heißt: die Werte werden nciht über die verbundenen Zellen geschrieben.
Gibt es eine Obergrenze für die Funktion SEQUENZ? Kann ich damit ein Tabellenblatt vollschreiben? Die Antworten: Obergrenze – ja; alle Zellen füllen – nein!
Ich verstehe es nicht: In der Zelle G1 steht die Funktion ZUFALLSBEREICH(1;5) und liefert folglich eine ganze Zahl zwischen einschließlich 1 und 5. Die Funktion SEQUENZ in Zelle 1 baut so viele Zeilen (1 bis 5) und zwei Spalten auf.
Bei einigen Zahlenwerten der Funktion ZUFALLSBEREICH kommt es zu einer Fehlermeldung: ÜBERLAUF:
Auch das Editieren mit [F9] liefert nicht die Lösung: