Загальна формула
(=MAX(IF(ISERROR(SEARCH(H5&"*",files)),0,ROW(files)-ROW(INDEX(files,1,1))+1)))
Резюме
Щоб знайти позицію (рядок) останньої версії файлу в таблиці, ви можете використовувати формулу, засновану на декількох функціях Excel: MAX, IF, ISERROR, ROW та INDEX.
У наведеному прикладі формула в комірці H6 має вигляд:
(= MAX (IF (ISERROR (SEARCH (H5 & "*", files)), 0, ROW (files) -ROW (INDEX (files, 1,1)) + 1)))
де "файли" - іменований діапазон C4: C11.
Примітка: це формула масиву, і її потрібно вводити за допомогою control + shift + enter.
Контекст
У цьому прикладі ми маємо кілька версій файлів, перелічених у таблиці з датою та іменем користувача. Зверніть увагу, що імена файлів повторюються, за винятком коду, доданого в кінці для представлення версії ("CA", "CB", "CC", "CD" тощо).
Для даного файлу ми хочемо знайти позицію (номер рядка) для останньої редакції. Це складна проблема, оскільки коди версій в кінці імен файлів ускладнюють відповідність імені файлу. Крім того, за замовчуванням формули відповідності Excel повертають перший збіг, а не останній збіг, тому нам потрібно обійти цю проблему за допомогою деяких хитрих прийомів.
Пояснення
В основі цієї формули ми складаємо список номерів рядків для даного файлу. Потім ми використовуємо функцію MAX, щоб отримати найбільший номер рядка, який відповідає останній редакції (останньому вступу) цього файлу.
Щоб знайти всі входження даного файлу, ми використовуємо функцію ПОШУК, сконфігуровану із символом зірочки (*) для відповідності імені файлу, ігноруючи коди версій. ПОШУК видасть значення VALUE, коли тексту не знайдено, тому обгортаємо пошук ISERROR:
ISERROR(SEARCH(H5&"*",files))
Це призводить до масиву значень TRUE і FALSE, таких як:
(FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE)
Це бентежить, але TRUE - це помилка (текст не знайдено), а FALSE - відповідність. Цей результат масиву подається у функцію IF як логічний тест. Для значення if TRUE ми використовуємо нуль, а для value, якщо 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)))
