☰

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

tag-icon • tag-icon microsoft-excel worksheet-function
Возврат данных на основе соответствующего диапазона дат

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

  • Столбец А:НАЧИНАТЬимеет даты, определяющие начало периода времени, представленного каждой позицией.
  • Столбец Б:КОНЕЦимеет даты, определяющие окончание периода времени, представленного каждой позицией.
  • Столбец С:Правопреемниксодержит строки, представляющие лицо, назначенное на данную должность в течение периода времени, определенного START и END в одной строке.

Сам списокобычнов порядке возрастания даты НАЧАЛА. Однако, возможно, что лист может оказаться перестроенным.

Между указанными в списке периодами времени не должно быть никаких совпадений.

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

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

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

решение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.

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

Вы также можете захотеть скрыть дополнительный столбец.

решение2

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

Текущий получатель: currRow=match(now(),A:A,1)- поскольку ваши данные упорядочены, он найдет текущую строку, нет необходимости во вспомогательном столбце. Установите имя ячейки, подсчитывающей это, на currRow просто для упрощения ссылок.
Получатель: =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))- и все еще формула массива.

Связанный контент