Обща формула
(=MAX(IF(ISERROR(SEARCH(H5&"*",files)),0,ROW(files)-ROW(INDEX(files,1,1))+1)))
Обобщение
За да намерите позицията (реда) на последната ревизия на файл в таблица, можете да използвате формула, базирана на няколко функции на Excel: MAX, IF, ISERROR, ROW и INDEX.
В показания пример формулата в клетка H6 е:
(= MAX (АКО (ISERROR (ТЪРСЕНЕ (H5 & "*", файлове)), 0, РЯД (файлове) -ROW (ИНДЕКС (файлове, 1,1)) + 1)))
където "файлове" е наименуваният диапазон C4: C11.
Забележка: това е формула на масив и трябва да се въведе с control + shift + enter.
Контекст
В този пример имаме няколко файлови версии, изброени в таблица с дата и потребителско име. Обърнете внимание, че имената на файлове се повтарят, с изключение на кода, добавен в края, за да представлява версия („CA“, „CB“, „CC“, „CD“ и др.).
За даден файл искаме да намерим позицията (номер на реда) за последната ревизия. Това е труден проблем, тъй като кодовете на версиите в края на имената на файлове затрудняват съвпадението на името на файла. Също така, по подразбиране формулите за съвпадение на Excel ще върнат първото, а не последното съвпадение, така че трябва да заобиколим това предизвикателство с някои сложни техники.
Обяснение
В основата на тази формула изграждаме списък с номера на редове за даден файл. След това използваме функцията MAX, за да получим най-големия номер на ред, който съответства на последната редакция (последно появяване) на този файл.
За да намерим всички случаи на даден файл, използваме функцията SEARCH, конфигурирана със звездичка (*), за да съвпада с името на файла, като игнорираме кодовете на версиите. SEARCH ще изведе грешка VALUE, когато текстът не бъде намерен, затова обгръщаме търсенето в ISERROR:
ISERROR(SEARCH(H5&"*",files))
Това води до масив от TRUE и FALSE стойности като този:
(FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE)
Объркващо е, но TRUE представлява грешка (текстът не е намерен), а FALSE представлява съвпадение. Този резултат от масив се подава във функцията IF като логически тест. За value if TRUE използваме нула, а за value if true предоставяме този код, който генерира относителни номера на редове за диапазона, с който работим:
ROW(files)-ROW(INDEX(files,1,1))+1)
Тогава функцията IF връща масив от стойности като този:
(1; 0; 3; 4; 0; 0; 7; 0)
Всички числа с изключение на нула представляват съвпадения за "filename1" - т.е. номера на реда в посочения диапазон "files", където се появява "filename1".
И накрая, използваме функцията MAX, за да получим максималната стойност в този масив, която е 7 в този пример.
Използвайте INDEX с този номер на ред, за да извлечете информация, свързана с последната редакция (т.е. пълно име на файл, дата, потребител и т.н.).
Без посочен обхват
Имените диапазони улесняват бързото и лесно създаване на по-сложна формула, тъй като не е необходимо да въвеждате адреси на клетки на ръка. В този случай обаче използваме допълнителна функция (INDEX), за да получим първата клетка от посочения диапазон „файлове“, което малко усложнява нещата. Без посочения диапазон, формулата изглежда така:
(=MAX(IF(ISERROR(SEARCH(H5&"*",C4:C11)),0,ROW(C4:C11)-ROW(C4)+1)))
