Возврат данных на основе соответствующего диапазона дат

Question

В нашей группе есть лист Excel, который используется для отслеживания чередующихся обязанностей. Простой пример будет выложен так:

• Столбец A: START содержит даты, определяющие начало периода времени, представленного каждой позицией.
• Столбец B: END содержит даты, определяющие конец периода времени, представленного каждой позицией.
• Столбец C: ASSIGNEE содержит строки, представляющие лицо, назначенное для данной ответственности в течение периода времени, определенного START и END в одной строке.

Сам список обычно в порядке возрастания на дату начала. Тем не менее, возможно, что лист может оказаться переставленным.

Не должно быть никакого перекрытия между периодами времени, определенными в списке.

Я хочу добавить информационный раздел с тремя значениями (в отдельных ячейках):

• ПРЕДЫДУЩИМ будет ПОЛУЧАТЕЛЬ, соответствующий периоду времени непосредственно перед текущим.
• CURRENT будет ответственным лицом в настоящее время.
• СЛЕДУЮЩИЙ будет ПОЛУЧАТЕЛЬ, соответствующий периоду времени после того, в котором мы сейчас находимся.

Предполагая, что электронная таблица остается в правильном порядке (по возрастанию по START), возврат PREVIOUS и NEXT должен быть легким после определения формулы для CURRENT. Но я даже не знаю точно, с чего начать.

Answer 1

Я думаю, что это будет работать для поиска тока. Я проверил это до некоторой степени, но я предполагаю, что игнорирование одной характеристики, в этом случае, не проблема.

Сначала добавьте столбец между B и C (отныне получатель становится D). В этом столбце введите следующую формулу:

=IF(NOW()-A2>0,IF(NOW()-B2<0,"Yes","No"),"No")

Это должно привести к одной строке с Да, а остальные с Нет (для текущего слота).

Теперь, в вашей ячейке для текущего правопреемника, вы кладете:

=INDEX($C$2:$D$4, MATCH("Yes", $C$2:$C$4,0), 2)

Для предыдущего: =INDEX($C$2:$D$4, MATCH("Yes", $C$2:$C$4,0) - 1, 2)
Для следующего: =INDEX($C$2:$D$4, MATCH("Yes", $C$2:$C$4,0) + 1, 2)

Обычно MATCH хочет отсортировать столбец поиска, но поскольку в столбце должно быть только одно Да, я думаю (это часть предположения), что мы можем игнорировать это ограничение.

Моя тестовая таблица состояла всего из 3 строк, поэтому YMMV.

Вам потребуется некоторая проверка ошибок для предыдущего, когда current является первой строкой и т.д., И, вероятно, хорошей идеей будет присвоение имени диапазону источника.

Вы также можете скрыть лишний столбец.

Answer 2

Заказанный ввод:

Текущий исполнитель : currRow=match(now(),A:A,1) - когда ваши данные упорядочены, будет найдена текущая строка, нет необходимости в вспомогательном столбце. Установите имя ячейки, считающей это, на currRow только для упрощения ссылок.
Assignee: =indirect("C"& currRow + x) - x: -1, 0, 1 для предыдущей, текущей и следующей строк соответственно.

Неупорядоченный ввод:

Текущая строка: currRow=match(max(if(A:A>now(),"",A:A)),A:A,0) - это формула массива, поэтому вам нужно ввести ее с помощью CTRL+SHIFT+ENTER.
Текущий получатель: та же формула, что и раньше.
Предыдущий: =indirect("C" & match(max(if(A:A>=indirect("A" & currRow),"",A:A)),A:A,0)) - также формула массива ,
Далее: =indirect("C" & match(min(if(A:A<=indirect("A" & currRow),"",A:A)),A:A,0)) - и все еще массив формула.