Обща формула
=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 и lookup_vector се създава с това:
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),"")
