Перечислите все значения перекрывающихся дат
У меня есть список проектов, даты которых совпадают; см. пример скриншота ниже.
В ячейке P1 у меня есть формула ниже, которая дает мне количество перекрывающихся проектов. Я также вставил несколько фильтров, чтобы сосредоточиться только на определенных подмножествах.
=SUMPRODUCT((F2<=FILTER($G$2:$G$8887,$N$2:$N$8887=N2,$B$2:$B$8887=B2))*(G2>=FILTER($F$2:$F$8887,$N$2:$N$8887=N2,$B$2:$B$8887=B2))))
Для строки 2 я могу вручную найти 10 перекрывающихся проектов, но существует ли формула, которая может вернуть эти 10 проектов? У меня довольно длинный список, и делать это вручную не вариант.
Ответы 2
Это может быть решением для вас:
[D2:D31]=TEXTJOIN(";",,
FILTER($A$2:$A$31,
BYROW($B$2:$C$31,
LAMBDA(a,
OR(
AND(B2>=INDEX(a,1,1),B2<=INDEX(a,1,2)),
AND(C2>=INDEX(a,1,1),C2<=INDEX(a,1,2))
)
)
)
)
)
Два проекта считаются перекрывающимися, если дата начала или дата окончания первого проекта находятся в пределах дат второго проекта.
Усовершенствованная формула обеспечивает более четкий результат:
=IFERROR(TEXTJOIN(";",,FILTER($A$2:$A$31,BYROW($A$2:$C$31,LAMBDA(a,AND(A2<>INDEX(a,1,1),OR(AND(B2>=INDEX(a,1,2),B2<=INDEX(a,1,3)),AND(C2>=INDEX(a,1,2),C2<=INDEX(a,1,3)))))))),"")
Улучшенная формула:
=TEXTJOIN(";",TRUE,BYROW($A$2:$C$31,LAMBDA(a,IF(AND(A2<>INDEX(a,1,1),MIN(C2,INDEX(a,1,3))>=MAX(B2,INDEX(a,1,2))),INDEX(a,1,1),""))))
Огромное спасибо, Rotabor, ты отлично справился! Попробовал, и все работает отлично.
Если вы используете Microsoft 365, используйте логику, аналогичную вашей, считая перекрывающиеся дни:
- МИН(конечная_дата, конечная_дата_текущая) – МАКС(начальная_дата, начальная_дата_текущая) + 1
- > 0 указывает на перекрытие
=LET(
prj_data, A1:INDEX(G:G, COUNTA(A:A)),
header, TAKE(prj_data, 1),
start, DROP(INDEX(prj_data, , XMATCH("Start Date", header)), 1),
end, DROP(INDEX(prj_data, , XMATCH("End Date", header)), 1),
id, DROP(INDEX(prj_data, , XMATCH("ID", header)), 1),
prj_indices, SEQUENCE(ROWS(start)),
overlapping_day_count, LAMBDA(prj_index,
MAP(
start,
end,
LAMBDA(s_, e_,
MAX(
MIN(e_, INDEX(end, prj_index)) -
MAX(s_, INDEX(start, prj_index)) + 1,
0
)
)
)
),
list_overlapping_project_ids, LAMBDA(row_index,
ARRAYTOTEXT(
FILTER(
id,
(id <> INDEX(id, row_index)) * overlapping_day_count(row_index),
""
)
)
),
MAP(prj_indices, list_overlapping_project_ids)
)
Это было бы проще и удобнее поддерживать на языке сценариев с библиотекой Excel, например Python или Javascript.