Общая формула
(=MATCH(2,1/(range"")))
Резюме
Чтобы получить последнюю относительную позицию (т.е. последнюю строку, последний столбец) для смешанных данных, которые могут содержать пустые ячейки, вы можете использовать функцию ПОИСКПОЗ, как описано ниже.
Примечание. Это формула массива, которую необходимо вводить с помощью Control + Shift + Enter.
В показанном примере формула E5 выглядит так:
(=MATCH(2,1/(B4:B10"")))
Последняя * относительная * позиция, а не строка на листе
При построении более сложных формул часто необходимо определить последнее расположение данных в списке. В зависимости от данных это может быть последняя строка с данными, последний столбец с данными или пересечение обоих. Нам нужна последняя * относительная позиция * внутри заданного диапазона, а не номер строки на листе:
Объяснение
В этой формуле используется функция ПОИСКПОЗ, настроенная для поиска позиции последней непустой ячейки в диапазоне.
Работая изнутри наружу, поисковый массив внутри 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 никогда не будет найдено, ПОИСКПОЗ всегда найдет последний 1 в массиве поиска, который соответствует последней непустой ячейке.
Этот подход будет работать с любыми типами данных, включая числа, текст, даты и т. Д. Он также работает с пустыми текстовыми строками, которые возвращаются такими формулами:
=IF(A1<100,"")
Динамический диапазон
Вы можете использовать эту формулу для создания динамического диапазона с другими функциями, такими как ИНДЕКС и СМЕЩЕНИЕ. См. Ссылки ниже для примеров и объяснения:
- Динамический диапазон с INDEX и COUNTA
- Динамический диапазон с OFFSET и COUNTA
Вдохновением для этой статьи послужила отличная книга Майка Гирвина «Control + Shift + Enter», где Майк отлично объяснил концепцию «последнего относительного положения».
