Формула Excel: поиск последней версии файла -

Общая формула

=LOOKUP(2,1/(ISNUMBER(FIND(filename,range))),range)

Резюме

Чтобы найти последнюю версию файла в списке, вы можете использовать формулу, основанную на функции ПРОСМОТР вместе с функциями ЕЧИСЛО и НАЙТИ. В показанном примере формула в ячейке G7:

=LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files)

где «файлы» - это именованный диапазон B5: B11.

Контекст

В этом примере у нас есть несколько версий файлов, перечисленных в таблице с датой и именем пользователя. Обратите внимание, что имена файлов повторяются со счетчиком в конце в качестве номера ревизии - 001, 002, 003 и т. Д.

Учитывая имя файла, мы хотим получить имя последней или последней ревизии. Есть две проблемы:

  1. Проблема в том, что коды версий в конце имен файлов затрудняют сопоставление имени файла.
  2. По умолчанию формулы сопоставления Excel возвращают первое совпадение, а не последнее совпадение.

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

Объяснение

Эта формула использует функцию ПРОСМОТР для поиска и извлечения последнего совпадающего имени файла. Значение поиска равно 2, и lookup_vector создается следующим образом:

1/(ISNUMBER(FIND(G6,files)))

Внутри этого фрагмента функция FIND ищет значение в G6 внутри именованного диапазона «files» (B5: B11). Результатом является такой массив:

(1;#VALUE!;1;1;#VALUE!;#VALUE!;1)

Здесь число 1 представляет совпадение, а ошибка #VALUE представляет несоответствующее имя файла. Этот массив входит в функцию ISNUMBER и выглядит так:

(TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE)

Значения ошибок теперь ЛОЖЬ, а число 1 теперь ИСТИНА. Это решает проблему №1, теперь у нас есть массив, который четко показывает, какие файлы в списке содержат интересующее имя файла.

Затем массив используется в качестве знаменателя с 1 в числителе. Результат выглядит так:

(1;#DIV/0!;1;1;#DIV/0!;#DIV/0!;1)

который входит в LOOKUP как lookup_vector. Это непростое решение проблемы №2. Функция ПРОСМОТР работает только в режиме приблизительного совпадения и автоматически игнорирует значения ошибок. Это означает, что с 2 в качестве значения поиска VLOOKUP попытается найти 2, потерпит неудачу и вернется к предыдущему номеру (в этом случае соответствует последней 1 в позиции 7). Наконец, LOOKUP использует 7 как индекс для извлечения 7-го файла в списке файлов.

Обработка пустых поисков

Как ни странно, функция НАЙТИ возвращает 1, если значение поиска является пустой строкой (""). Чтобы предотвратить ложное совпадение, вы можете заключить формулу в IF и проверить наличие пустого поиска:

=IF(G6"",LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files),"")

Интересные статьи...