Формула Excel: Підрахувати відпустки між двома датами -

Загальна формула

=SUMPRODUCT((holidays>=start)*(holidays<=end))

Резюме

Для підрахунку свят, які трапляються між двома датами, можна скористатися функцією SUMPRODUCT.

У наведеному прикладі формула в F8 має вигляд:

=SUMPRODUCT((B4:B12>=F5)*(B4:B12<=F6))

Пояснення

Ця формула використовує два вирази в одному масиві всередині функції SUMPRODUCT.

Перший вираз перевіряє кожну святкову дату, щоб перевірити, чи більша вона чи дорівнює даті початку у F5:

(B4:B12>=F5)

Це повертає масив значень TRUE / FALSE, таких як:

(FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE)

Другий вираз перевіряє кожну дату свят, щоб перевірити, чи менша вона чи дорівнює даті завершення у F6:

(B4:B12<=F6)

який повертає масив значень TRUE / FALSE наступним чином:

(TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE)

Помноження цих двох масивів автоматично примушує значення TRUE / FALSE до одиниць і нулів, що призводить до масивів, які виглядають так:

=SUMPRODUCT(((0;0;0;0;1;1;1;1;1))*((1;1;1;1;1;1;1;1;0)))

Після множення ми маємо лише один такий масив:

=SUMPRODUCT((0;0;0;0;1;1;1;1;0))

Нарешті, SUMPRODUCT підсумовує елементи масиву і повертає 4.

Відпустка лише у будні

Для підрахунку свят, які бувають лише у будні (пн-пт), можна розширити формулу так:

=SUMPRODUCT((rng>=F5)*(rng<=F6)*(WEEKDAY(rng,2)<6))

де rng - діапазон, що містить дати свят.

Цікаві статті...