Общая формула
(=MAX(IF(ISERROR(SEARCH(H5&"*",files)),0,ROW(files)-ROW(INDEX(files,1,1))+1)))
Резюме
Чтобы найти позицию (строку) последней версии файла в таблице, вы можете использовать формулу, основанную на нескольких функциях Excel: MAX, IF, ISERROR, ROW и INDEX.
В показанном примере формула в ячейке H6:
(= МАКС (ЕСЛИ (ЕСТЬ ОШИБКА (ПОИСК (H5 & "*", файлы)), 0, СТРОКА (файлы) -ROW (ИНДЕКС (файлы, 1,1)) + 1)))
где «файлы» - это именованный диапазон C4: C11.
Примечание: это формула массива, и ее нужно вводить с помощью Ctrl + Shift + Enter.
Контекст
В этом примере у нас есть несколько версий файлов, перечисленных в таблице с датой и именем пользователя. Обратите внимание, что имена файлов повторяются, за исключением кода, добавленного в конце для представления версии («CA», «CB», «CC», «CD» и т. Д.).
Для данного файла мы хотим найти позицию (номер строки) для последней ревизии. Это сложная проблема, потому что коды версий в конце имен файлов затрудняют сопоставление имени файла. Кроме того, по умолчанию формулы сопоставления Excel возвращают первое совпадение, а не последнее совпадение, поэтому нам нужно обойти эту проблему с помощью некоторых хитрых приемов.
Объяснение
В основе этой формулы мы создаем список номеров строк для данного файла. Затем мы используем функцию MAX, чтобы получить наибольший номер строки, который соответствует последней ревизии (последнему появлению) этого файла.
Чтобы найти все вхождения данного файла, мы используем функцию ПОИСК, настроенную с помощью подстановочного знака звездочки (*) для соответствия имени файла, игнорируя коды версий. SEARCH выдаст ошибку VALUE, если текст не найден, поэтому мы завершаем поиск в ISERROR:
ISERROR(SEARCH(H5&"*",files))
В результате получается массив значений ИСТИНА и ЛОЖЬ, подобный этому:
(ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ; ИСТИНА)
Это сбивает с толку, но TRUE представляет ошибку (текст не найден), а FALSE представляет совпадение. Результат этого массива передается в функцию ЕСЛИ в качестве логической проверки. Для значения, если ИСТИНА, мы используем ноль, а для значения, если истина, мы предоставляем этот код, который генерирует относительные номера строк для диапазона, с которым мы работаем:
ROW(files)-ROW(INDEX(files,1,1))+1)
Затем функция ЕСЛИ возвращает массив значений, подобных этому:
(1; 0; 3; 4; 0; 0; 7; 0)
Все числа, кроме нуля, представляют совпадения с "filename1" - то есть номер строки внутри именованного диапазона "files", где появляется "filename1".
Наконец, мы используем функцию MAX, чтобы получить максимальное значение в этом массиве, которое в этом примере равно 7.
Используйте INDEX с этим номером строки, чтобы получить информацию, относящуюся к последней редакции (например, полное имя файла, дату, пользователя и т. Д.).
Без именованного диапазона
Именованные диапазоны позволяют быстро и легко настроить более сложную формулу, поскольку вам не нужно вводить адреса ячеек вручную. Однако в этом случае мы используем дополнительную функцию (ИНДЕКС) для получения первой ячейки именованного диапазона «files», что немного усложняет ситуацию. Без именованного диапазона формула выглядит так:
(=MAX(IF(ISERROR(SEARCH(H5&"*",C4:C11)),0,ROW(C4:C11)-ROW(C4)+1)))
