Загальна формула
=LOOKUP(2,1/(A:A""),A:A)
Резюме
Щоб знайти значення останньої непорожньої комірки в рядку чи стовпці, ви можете використовувати функцію LOOKUP у цій напрочуд компактній формулі. Як додатковий бонус, ця формула не є формулою масиву і не мінливою.
Пояснення
Ключ до розуміння цієї формули полягає у визнанні того, що значення lookup_value 2 навмисно більше, ніж будь-які значення, які з’являться у lookup_vector.
- Вираз A: "" повертає масив істинних і хибних значень: (TRUE, FALSE, TRUE, …).
- Потім число 1 ділиться на цей масив і створює новий масив, що складається або з одиниць, або ділиться нульовими помилками (# DIV / 0!): (1,0,1,…). Цей масив є вектором пошуку.
- Коли lookup_value не вдається знайти, LOOKUP буде відповідати наступному найменшому значенню.
- У цьому випадку значення lookup_value дорівнює 2, але найбільше значення в масиві lookup_array дорівнює 1, тому пошук буде відповідати останньому 1 у масиві.
- LOOKUP повертає відповідне значення в result_vector (тобто значення в тій самій позиції).
Виправлення помилок
Якщо у векторі пошуку є помилки, особливо якщо в останній непорожній комірці є помилка, цю формулу потрібно відкоригувати. Це коригування необхідне, оскільки "" критерії самі повернуть помилку, якщо комірка містить помилку. Щоб вирішити цю проблему, використовуйте ISBLANK з NOT:
=LOOKUP(2,1/(NOT(ISBLANK(A:A))),A:A)
Останнє числове значення
Щоб отримати останнє числове значення, ви можете додати функцію ISNUMBER таким чином:
=LOOKUP(2,1/(ISNUMBER(A1:A100)),A1:A100)
Позиція останнього значення
Якщо ви хочете отримати позицію (у цьому випадку номер рядка) останнього значення, ви можете спробувати формулу, подібну до цієї:
=LOOKUP(2,1/(A:A""),ROW(A:A))
Тут ми подаємо номери рядків того самого діапазону для пошуку вектора результатів і отримуємо номер рядка останнього збігу.
