Általános képlet
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start&":"&end)))=dow))
Összegzés
A hétköznapok (hétfő, péntek, vasárnap stb.) Két dátum közötti számlálásához használhat egy tömbképletet, amely több funkciót használ: SUMPRODUCT, WEEKDAY, ROW és INDIRECT. A bemutatott példában az E6 cellában szereplő képlet az
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B6&":"&C6)))=D6))
A képlet általános verziójában a kezdet = kezdő dátum, a vég = a befejezés dátuma és a dow = a hét napja.
Magyarázat
Lényegében ez a képlet a WEEKDAY függvény segítségével számos dátumot tesztel, hogy lássa-e a hét adott napját (dow), és a SUMPRODUCT függvény az összesítés összegzéséhez.
Dátum megadásakor a WEEKDAY egyszerűen 1 és 7 közötti számot ad vissza, amely a hét egy adott napjának felel meg. Alapértelmezett beállításokkal 1 = vasárnap és 7 = szombat. Tehát 2 = hétfő, 6 = péntek stb.
Ennek a képletnek az a trükkje, hogy megértsük, hogy az Excel dátumai csak sorszámok, amelyek 1900. január 1-jén kezdődnek. Például 2016. január 1-je a 42370, a január 8-a pedig 42377. A dátumok az Excel-ben csak akkor néznek ki, mint amikor dátumszám formátumot alkalmaznak.
Tehát a következő kérdés merül fel: hogyan állíthat össze egy sor dátumot, amelyet bevihet a WEEKDAY függvénybe, hogy megtudja a hét megfelelő napjait?
A válasz az, hogy a ROW-t olyan INDIRECT funkciókkal használja, mint például:
ROW(INDIRECT(date1&":"&date2))
A INDIRECT lehetővé teszi, hogy a összefűzött "42370: 42377" dátumokat sorszámként értelmezzük. Ezután a ROW függvény egy ilyen tömböt ad vissza:
(42370;42371;42372;42373;42374;42375;42376;42377)
A WEEKDAY függvény ezeket a számokat dátumként értékeli, és visszaadja ezt a tömböt:
(6;7;1;2;3;4;5;6)
amelyet a hét adott napjához (ebben az esetben 6-tól D6-tól) tesztelnek. Miután a teszt eredményeit 1 és 0 értékekké konvertálták a kettős kötőjellel, ezt a tömböt a SUMPRODUCT dolgozza fel:
(1;0;0;0;0;0;0;1)
Ami 2-t ad vissza.
SZekvenciával
Az új SEQUENCE függvénnyel ez a képlet kissé így egyszerűsíthető:
=SUMPRODUCT(--(WEEKDAY(SEQUENCE(end-start+1,1,start,1))=dow))
Ebben a verzióban a SEQUENCE segítségével generáljuk a dátumtömböt közvetlenül, nem szükséges INDIRECT vagy ROW.
