Общая формула
(=MIN(IF((range>=TODAY()),range)))
Резюме
Чтобы получить следующее запланированное событие из списка событий с датами, вы можете использовать формулу массива, основанную на функциях MIN и TODAY, чтобы найти следующую дату, и INDEX и MATCH, чтобы отобразить событие в эту дату. В показанном примере формула в G6:
(=MIN(IF((date>=TODAY()),date)))
Где «дата» - именованный диапазон D5: D14.
Примечание. Это формула массива, которую необходимо вводить с помощью Control + Shift + Enter.
Объяснение
В первой части решения используются функции MIN и TODAY, чтобы найти «следующую дату» на основе сегодняшней даты. Это делается путем фильтрации дат с помощью функции ЕСЛИ:
IF((date>=TODAY()),date)
Логический тест генерирует массив значений ИСТИНА / ЛОЖЬ, где ИСТИНА соответствует датам, большим или равным сегодняшнему дню:
(FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Если результат ИСТИНА, дата передается в массив, возвращаемый IF. Если результатом является ЛОЖЬ, дата заменяется логическим значением ЛОЖЬ. Функция ЕСЛИ возвращает в MIN следующий массив:
(FALSE;FALSE;FALSE;43371;43385;43399;43413;43427;43441;43455)
Затем функция MIN игнорирует значения FALSE и возвращает наименьшее значение даты (43371), которое является датой 28 сентября 2018 г. в системе дат Excel.
Получение названия фильма
Чтобы отобразить фильм, связанный с «следующей датой», мы используем ИНДЕКС и ПОИСКПОЗ:
=INDEX(movie,MATCH(G6,date,0))
Внутри INDEX MATCH находит позицию даты в G6 в списке дат. Эта позиция, 4 в примере, возвращается в INDEX как номер строки:
=INDEX(movie,4)
а ИНДЕКС возвращает фильм «Темный рыцарь» на этой позиции.
Все в одной формуле
Чтобы вернуть следующий фильм в одной формуле, вы можете использовать эту формулу массива:
(=INDEX(movie,MATCH(MIN(IF((date>=TODAY()),date)),date,0)))
С MINIFS
Если у вас более новая версия Excel, вы можете использовать функцию MINIFS вместо формулы массива в G6:
=MINIFS(date,date,">="&TODAY())
MINIFS был представлен в Excel 2016 через Office 365.
Обработка ошибок
Формула на этой странице будет работать, даже если события не отсортированы по дате. Однако, если предстоящих дат нет, функция MIN вернет ноль вместо ошибки. Это будет отображаться как дата «0-янв-00» в G6, а формулы ИНДЕКС и ПОИСКПОЗ выдадут ошибку # Н / Д, поскольку нет нулевой строки, из которой можно было бы получить значение. Чтобы перехватить эту ошибку, вы можете заменить MIN функцией SMALL, а затем заключить всю формулу в IFERROR следующим образом:
=(IFERROR(SMALL(IF((date>=TODAY()),date),1),"None found"))
В отличие от MIN, функция SMALL выдаст ошибку, если значение не найдено, поэтому ЕСЛИОШИБКА может использоваться для управления ошибкой.
