Generische Formel
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start&":"&end)))=dow))
Zusammenfassung
Um Wochentage (Montag, Freitag, Sonntag usw.) zwischen zwei Daten zu zählen, können Sie eine Array-Formel verwenden, die mehrere Funktionen verwendet: SUMPRODUCT, WEEKDAY, ROW und INDIRECT. In dem gezeigten Beispiel lautet die Formel in Zelle E6
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B6&":"&C6)))=D6))
In der generischen Version der Formel ist Start = Startdatum, Ende = Enddatum und Dow = Wochentag.
Erläuterung
Im Kern verwendet diese Formel die Funktion WOCHENTAG, um eine Reihe von Daten zu testen, um festzustellen, ob sie an einem bestimmten Wochentag (Dow) landen, und die Funktion SUMPRODUCT, um die Gesamtsumme zu berechnen.
Wenn ein Datum angegeben ist, gibt WEEKDAY einfach eine Zahl zwischen 1 und 7 zurück, die einem bestimmten Wochentag entspricht. Bei den Standardeinstellungen ist 1 = Sonntag und 7 = Samstag. Also 2 = Montag, 6 = Freitag und so weiter.
Der Trick bei dieser Formel besteht darin, zu verstehen, dass Daten in Excel nur Seriennummern sind, die am 1. Januar 1900 beginnen. Beispielsweise ist der 1. Januar 2016 die Seriennummer 42370 und der 8. Januar 42377. Daten in Excel sehen nur wie Datumsangaben aus, wenn Es wird ein Datumsnummernformat angewendet.
Die Frage lautet also: Wie können Sie eine Reihe von Daten erstellen, die Sie in die WEEKDAY-Funktion eingeben können, um die entsprechenden Wochentage herauszufinden?
Die Antwort ist, ROW mit INDIRECT-Funktionen wie folgt zu verwenden:
ROW(INDIRECT(date1&":"&date2))
Mit INDIRECT können die verketteten Daten "42370: 42377" als Zeilennummern interpretiert werden. Dann gibt die ROW-Funktion ein Array wie folgt zurück:
(42370;42371;42372;42373;42374;42375;42376;42377)
Die WEEKDAY-Funktion wertet diese Zahlen als Datumsangaben aus und gibt dieses Array zurück:
(6;7;1;2;3;4;5;6)
Dies wird gegen den angegebenen Wochentag getestet (6 in diesem Fall ab D6). Sobald die Testergebnisse mit dem doppelten Bindestrich in 1s und 0s konvertiert wurden, wird dieses Array von SUMPRODUCT verarbeitet:
(1;0;0;0;0;0;0;1)
Welches gibt 2 zurück.
Mit SEQUENZ
Mit der neuen SEQUENCE-Funktion kann diese Formel folgendermaßen vereinfacht werden:
=SUMPRODUCT(--(WEEKDAY(SEQUENCE(end-start+1,1,start,1))=dow))
In dieser Version verwenden wir SEQUENCE, um das Array von Daten direkt zu generieren, ohne dass INDIRECT oder ROW erforderlich sind.
