Загальна формула
=LOOKUP(2,1/(ISNUMBER(FIND(filename,range))),range)
Резюме
Для пошуку останньої версії файлу у списку можна використовувати формулу, засновану на функції LOOKUP, разом із функціями ISNUMBER та FIND. У наведеному прикладі формула в комірці G7 має вигляд:
=LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files)
де "файли" - іменований діапазон B5: B11.
Контекст
У цьому прикладі ми маємо кілька версій файлів, перелічених у таблиці з датою та іменем користувача. Зверніть увагу, що імена файлів повторюються з лічильником в кінці як номер редакції - 001, 002, 003 тощо.
Отримавши ім’я файлу, ми хочемо отримати ім’я останньої чи останньої версії. Є дві проблеми:
- Проблема полягає в тому, що коди версій у кінці імен файлів ускладнюють відповідність імені файлу.
- За замовчуванням формули відповідності Excel повертають першу, а не останню відповідність.
Щоб подолати ці виклики, нам потрібно використовувати кілька хитрих прийомів.
Пояснення
Ця формула використовує функцію LOOKUP для пошуку та отримання останнього відповідного імені файлу. Значення пошуку дорівнює 2, і вектор пошуку шукається за допомогою цього:
1/(ISNUMBER(FIND(G6,files)))
Усередині цього фрагмента функція FIND шукає значення в G6 усередині названого діапазону "файли" (B5: B11). Результат - такий масив:
(1;#VALUE!;1;1;#VALUE!;#VALUE!;1)
Тут номер 1 представляє збіг, а помилка #VALUE - невідповідне ім'я файлу. Цей масив переходить у функцію ISNUMBER і виходить таким чином:
(TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE)
Значення помилок тепер FALSE, а число 1 тепер TRUE. Це долає виклик №1, тепер у нас є масив, який чітко показує, які файли у списку містять цікавить ім’я файлу.
Далі масив використовується як знаменник, а 1 - як чисельник. Результат виглядає так:
(1;#DIV/0!;1;1;#DIV/0!;#DIV/0!;1)
який переходить у LOOKUP як lookup_vector. Це складне рішення завдання №2. Функція LOOKUP працює лише у режимі приблизного збігу та автоматично ігнорує значення помилок. Це означає, що як значення підстановки 2, VLOOKUP спробує знайти 2, не вдасться повернутися до попереднього числа (у цьому випадку відповідати останньому 1 у позиції 7). Нарешті, LOOKUP використовує 7 як індекс, щоб отримати 7-й файл у списку файлів.
Обробка порожніх пошуків
Як не дивно, функція FIND повертає 1, якщо значення пошуку є порожнім рядком (""). Щоб захиститися від помилкового збігу, можна обернути формулу в IF і перевірити наявність порожнього пошуку:
=IF(G6"",LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files),"")
