Общая формула
(=MAX(IF(criteria,ROW(rng)-MIN(ROW(rng))+1)))
Резюме
Чтобы получить позицию последнего совпадения (т. Е. Последнего вхождения) искомого значения, вы можете использовать формулу массива, основанную на функциях ЕСЛИ, СТРОКА, ИНДЕКС, ПОИСКПОЗ и МАКС. В показанном примере формула H6 имеет следующий вид:
(=MAX(IF(names=H5,ROW(names)-MIN(ROW(names))+1)))
Где «имена» - именованный диапазон C4: C11.
Примечание: это формула массива, и ее нужно вводить с помощью Ctrl + Shift + Enter.
Объяснение
Суть этой формулы в том, что мы создаем список номеров строк для заданного диапазона, совпадающего по значению, а затем используем функцию MAX для получения наибольшего номера строки, который соответствует последнему совпадающему значению. Мы используем именованный диапазон «names» только для удобства.
Работая изнутри, эта часть формулы будет генерировать относительный набор номеров строк:
ROW(names)-MIN(ROW(names))+1
Результатом приведенного выше выражения является массив таких чисел:
(1;2;3;4;5;6;7;8)
Обратите внимание, что мы получаем 8 чисел, соответствующих 8 строкам в таблице. См. Эту страницу для получения подробной информации о том, как работает эта часть формулы.
Для целей этой формулы нам нужны только номера строк для совпадающих значений, поэтому мы используем функцию ЕСЛИ для фильтрации значений следующим образом:
IF(names=H5,ROW(names)-MIN(ROW(names))+1)
В результате получается массив, который выглядит так:
(1;FALSE;FALSE;4;FALSE;FALSE;7;FALSE)
Обратите внимание, что этот массив по-прежнему содержит восемь элементов. Однако остались только номера строк, в которых значение в именованном диапазоне «names» равно «amy» (т. Е. 1, 4, 7). Все остальные элементы в массиве - ЛОЖЬ, поскольку они не прошли логический тест в функции ЕСЛИ.
Наконец, функция IF доставляет этот массив в функцию MAX. MAX возвращает наивысшее значение в массиве, число 7, которое соответствует номеру последней строки с именем «amy». Как только мы узнаем номер последней совпадающей строки, мы можем использовать INDEX для получения значения в этой позиции.
Предпоследний и т. Д.
Чтобы получить предпоследнюю позицию, предпоследнюю и т. Д., Вы можете переключиться с функции MIN на функцию LARGE следующим образом:
(=LARGE(IF(criteria,ROW(rng)-MIN(ROW(rng))+1),k))
где k представляет собой «n-й по величине». Например, чтобы получить предпоследнее совпадение в приведенном выше примере, вы можете использовать:
(=LARGE(IF(names=H5,ROW(names)-MIN(ROW(names))+1),2))
Как и раньше, это формула массива, и ее нужно вводить с помощью Ctrl + Shift + Enter.
