Загальна формула
=SUMPRODUCT((start_date=start_dates))>1
Резюме
Щоб виділити комірки, де дати перекриваються, можна використовувати умовне форматування з формулою на основі функції SUMPRODUCT. У наведеному прикладі формула на півдні Е6:
=SUMPRODUCT(($C6=$C$5:$C$9))>1
Це та сама формула, яка використовується для виділення цілих рядків у таблиці за допомогою правила умовного форматування на основі формули.
Пояснення
Подумайте трохи, як працюють дати, що збігаються. Щоб проект перекривав дати інших проектів, має бути виконано дві умови:
1. Дата початку повинна бути менше або дорівнювати (<=) принаймні одній іншій даті завершення та списку.
2. Дата завершення проекту повинна бути більшою або дорівнювати (> =) принаймні одній іншій даті початку у списку.
Якщо обидві ці умови відповідають дійсності, дати проекту повинні перекривати інший проект у цьому списку.
Функція SUMPRODUCT ідеально підходить для такого роду тестів, оскільки вона елегантно обробляє порівняння масивів.
Щоб перевірити дату початку проекту за всіма датами завершення, ми використовуємо такий вираз:
($C6<=$D$5:$D$9)
Щоб перевірити дату завершення проекту за всіми датами завершення, ми використовуємо такий вираз:
($D6>=$C$5:$C$9)
Отримані масиви значень TRUE FALSE множаться між собою всередині SUMPRODUCT. Це автоматично примушує результати TRUE і FALSE до 1s і 0s, тому формула вирішується так:
=SUMPRODUCT((0;1;1;1;1)*(1;1;1;0;0))>1 =SUMPRODUCT((0;1;1;0;0))>1 =TRUE
