Формула на Excel: Търсене на последната версия на файла -

Обща формула

=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 и т.н.

При дадено име на файл искаме да извлечем името на последната или последната редакция. Има две предизвикателства:

  1. Предизвикателството е, че кодовете на версиите в края на имената на файлове затрудняват съвпадението на името на файла.
  2. По подразбиране формулите за съвпадение на 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),"")

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