Generisk formel
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start&":"&end)))=dow))
Resumé
For at tælle hverdage (mandage, fredage, søndage osv.) Mellem to datoer kan du bruge en matrixformel, der bruger flere funktioner: SUMPRODUCT, WEEKDAY, ROW og INDIRECT. I det viste eksempel er formlen i celle E6
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B6&":"&C6)))=D6))
I den generiske version af formlen er start = startdato, slut = slutdato og dow = ugedag.
Forklaring
I kernen bruger denne formel WEEKDAY-funktionen til at teste et antal datoer for at se, om de lander på en given ugedag (dow) og SUMPRODUCT-funktionen for at tælle det samlede beløb.
Når der gives en dato, returnerer WEEKDAY blot et tal mellem 1 og 7, der svarer til en bestemt ugedag. Med standardindstillinger er 1 = søndag og 7 = lørdag. Så, 2 = mandag, 6 = fredag osv.
Tricket med denne formel er at forstå, at datoer i Excel kun er serienumre, der begynder den 1. januar 1900. For eksempel er 1. januar 2016 serienummeret 42370, og 8. januar er 42377. Datoer i Excel ligner kun datoer, når et datonummerformat anvendes.
Så spørgsmålet bliver - hvordan kan du konstruere en række datoer, som du kan føje til WEEKDAY-funktionen for at finde ud af de tilsvarende ugedage?
Svaret er at bruge ROW med INDIRECT-funktioner som sådan:
ROW(INDIRECT(date1&":"&date2))
INDIRECT tillader, at de sammenkædede datoer "42370: 42377" tolkes som rækkenumre. Derefter returnerer ROW-funktionen en matrix som denne:
(42370;42371;42372;42373;42374;42375;42376;42377)
WEEKDAY-funktionen evaluerer disse tal som datoer og returnerer denne matrix:
(6;7;1;2;3;4;5;6)
som testes mod den givne ugedag (6 i dette tilfælde fra D6). Når resultaterne af testen er konverteret til 1s og 0s med dobbelt bindestreg, behandles denne matrix af SUMPRODUCT:
(1;0;0;0;0;0;0;1)
Hvilket returnerer 2.
Med SEKVENS
Med den nye SEQUENCE-funktion kan denne formel forenkles noget som denne:
=SUMPRODUCT(--(WEEKDAY(SEQUENCE(end-start+1,1,start,1))=dow))
I denne version bruger vi SEQUENCE til at generere dataserien direkte uden behov for INDIRECT eller ROW.
