Обща формула
=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
