Загальна формула
(=MATCH(2,1/(range"")))
Резюме
Щоб отримати останнє відносне положення (тобто останній рядок, останній стовпець) для змішаних даних, які можуть містити порожні клітинки, ви можете використовувати функцію MATCH, як описано нижче.
Примітка: це формула масиву, яку потрібно вводити за допомогою Control + Shift + Enter.
У наведеному прикладі формула в E5 має вигляд:
(=MATCH(2,1/(B4:B10"")))
Остання * відносна * позиція, не рядок на аркуші
При побудові більш досконалих формул часто доводиться з'ясовувати останнє розташування даних у списку. Залежно від даних, це може бути останній рядок із даними, останній стовпець із даними або перетин обох. Ми хочемо, щоб останнє * відносне положення * всередині заданого діапазону не було номером рядка на аркуші:
Пояснення
Ця формула використовує функцію MATCH, налаштовану для пошуку положення останньої непорожньої комірки в діапазоні.
Працюючи зсередини, масив підстановки всередині MATCH будується так:
=1/(B4:B10"")) =1/(TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE) =(1;#DIV/0!;1;#DIV/0!;1;1;#DIV/0!)
Примітка: усі значення в масиві мають або 1, або # DIV / 0! помилка.
Потім MATCH встановлюється таким чином, щоб він відповідав значенню 2 у "режимі приблизного збігу", опускаючи 3-й аргумент, опускається.
Оскільки значення пошуку 2 ніколи не буде знайдено, MATCH завжди знайде останнє значення 1 у масиві пошуку, яке відповідає останній непорожній комірці.
Цей підхід буде працювати з будь-якими даними, включаючи числа, текст, дати тощо. Він також працює з нульовими текстовими рядками, які повертаються такими формулами:
=IF(A1<100,"")
Динамічний діапазон
Ви можете використовувати цю формулу для створення динамічного діапазону з іншими функціями, такими як INDEX та OFFSET. Див. Посилання нижче для прикладів та пояснень:
- Динамічний діапазон з INDEX і COUNTA
- Динамічний діапазон з OFFSET і COUNTA
Натхненням для цієї статті стала чудова книга Майка Гірвіна Control + Shift + Enter, де Майк чудово справляється з поясненням поняття "остання відносна позиція".
