Загальна формула
(=MAX(IF(criteria,ROW(rng)-MIN(ROW(rng))+1)))
Резюме
Щоб отримати позицію останнього збігу (тобто останнього входження) значення пошуку, ви можете використовувати формулу масиву на основі функцій IF, ROW, INDEX, MATCH та MAX. У наведеному прикладі формула в H6 така:
(=MAX(IF(names=H5,ROW(names)-MIN(ROW(names))+1)))
Де "імена" - іменований діапазон C4: C11.
Примітка: це формула масиву, і її потрібно вводити за допомогою control + shift + enter.
Пояснення
Суть цієї формули полягає в тому, що ми створюємо список номерів рядків для даного діапазону, зіставляючи значення, а потім використовуємо функцію MAX, щоб отримати найбільший номер рядка, який відповідає останньому значенню відповідності. Ми використовуємо названий діапазон "імена" лише для зручності.
Працюючи зсередини, ця частина формули генерує відносний набір номерів рядків:
ROW(names)-MIN(ROW(names))+1
Результатом наведеного вище виразу є масив таких чисел:
(1;2;3;4;5;6;7;8)
Зверніть увагу, що ми отримуємо 8 чисел, що відповідають 8 рядкам у таблиці. Детальніше про те, як працює ця частина формули, дивіться на цій сторінці.
Для цілей цієї формули нам потрібні лише номери рядків для відповідних значень, тому ми використовуємо функцію IF для фільтрації значень таким чином:
IF(names=H5,ROW(names)-MIN(ROW(names))+1)
Це призводить до масиву, який виглядає так:
(1;FALSE;FALSE;4;FALSE;FALSE;7;FALSE)
Зверніть увагу, що цей масив все ще містить вісім елементів. Однак збереглися лише номери рядків, де значення в названому діапазоні "імена" дорівнює "amy" (тобто 1, 4, 7). Усі інші елементи масиву FALSE, оскільки вони не пройшли логічний тест у функції IF.
Нарешті, функція 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))
Як і раніше, це формула масиву, і її потрібно вводити за допомогою control + shift + enter.
