Загальна формула
(=MIN(IF((range>=TODAY()),range)))
Резюме
Щоб отримати наступну заплановану подію зі списку подій із датами, ви можете використовувати формулу масиву на основі функцій MIN та TODAY, щоб знайти наступну дату, а INDEX та MATCH для відображення події на цю дату. У наведеному прикладі формула в G6 має вигляд:
(=MIN(IF((date>=TODAY()),date)))
Де "дата" - названий діапазон D5: D14.
Примітка: це формула масиву, яку потрібно вводити за допомогою Control + Shift + Enter.
Пояснення
Перша частина рішення використовує функції MIN та TODAY для пошуку "наступної дати" на основі сьогоднішньої дати. Це робиться шляхом фільтрації дат за допомогою функції IF:
IF((date>=TODAY()),date)
Логічний тест генерує масив значень TRUE / FALSE, де TRUE відповідає датам, більшим або рівним сьогоднішнім:
(FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Коли результат TRUE, дата передається в масив, повернутий IF. Коли результат FALSE, дата замінюється логічним значенням FALSE. Функція IF повертає наступний масив до MIN:
(FALSE;FALSE;FALSE;43371;43385;43399;43413;43427;43441;43455)
Потім функція MIN ігнорує значення FALSE і повертає найменше значення дати (43371), яке є датою 28 вересня 2018 року в системі дат Excel.
Отримання назви фільму
Для відображення фільму, пов'язаного з "наступною датою" ", ми використовуємо INDEX та MATCH:
=INDEX(movie,MATCH(G6,date,0))
Усередині INDEX MATCH знаходить позицію дати в G6 у списку дат. Ця позиція, 4 у прикладі, повертається до INDEX як номер рядка:
=INDEX(movie,4)
і INDEX повертає фільм у цій позиції "Темний лицар".
Все в одній формулі
Щоб повернути наступний фільм в одній формулі, ви можете використовувати цю формулу масиву:
(=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, а формула INDEX і MATCH видасть помилку # N / A, оскільки немає нульового рядка, з якого можна отримати значення. Щоб уловити цю помилку, ви можете замінити MIN функцією SMALL, а потім обернути всю формулу в IFERROR так:
=(IFERROR(SMALL(IF((date>=TODAY()),date),1),"None found"))
На відміну від MIN, функція SMALL видає помилку, коли значення не знайдено, тому IFERROR можна використовувати для управління помилкою.
