Общая формула
=MATCH(bigtext,range)
Резюме
Чтобы получить последнюю относительную позицию (т.е. последнюю строку, последний столбец) для текстовых данных (с пустыми ячейками или без них), вы можете использовать функцию ПОИСКПОЗ. В показанном примере формула в D5:
=MATCH(REPT("z",255),B4:B11)
Объяснение
Эта формула использует функцию ПОИСКПОЗ в режиме приблизительного совпадения, чтобы найти последнее текстовое значение в диапазоне. Приблизительное совпадение включается установкой 3-м аргументом в MATCH равным 1 или опусканием этого аргумента, значение которого по умолчанию равно 1.
Значение поиска - это так называемый «большой текст» (иногда сокращенно «большой текст»), который намеренно является значением «больше», чем любое значение, которое появится в диапазоне. При работе с текстом, который сортируется в алфавитном порядке, это означает, что текстовое значение всегда будет отображаться в конце алфавитного порядка сортировки.
Поскольку эта формула соответствует тексту, идея состоит в том, чтобы создать значение подстановки, которое никогда не встречается в реальном тексте, но всегда будет последним. Для этого мы используем функцию ПОВТОР, чтобы повторить букву «z» 255 раз. Число 255 представляет наибольшее количество символов, которое позволяет MATCH в поисковом значении.
Когда ПОИСКПОЗ не может найти это значение, оно «вернется» к последнему текстовому значению в диапазоне и вернет позицию этого значения.
Примечание: этот подход отлично работает с пустыми ячейками в диапазоне, но ненадежен со смешанными данными, которые включают как числа, так и текст.
Последняя относительная позиция по сравнению с номером последней строки
При построении сложных формул, которые создают динамические диапазоны, часто необходимо определить последнее расположение данных в списке. В зависимости от данных это может быть последняя строка с данными, последний столбец с данными или пересечение обоих. Примечание: нам нужна последняя относительная позиция внутри заданного диапазона, а не номер строки на листе:
Динамический диапазон
Вы можете использовать эту формулу для создания динамического диапазона с другими функциями, такими как ИНДЕКС и СМЕЩЕНИЕ. См. Ссылки ниже для примеров и объяснения:
- Динамический диапазон с INDEX и COUNTA
- Динамический диапазон с OFFSET и COUNTA
Вдохновением для этой статьи послужила отличная книга Майка Гирвина «Control + Shift + Enter», где Майк объясняет концепцию «последнего относительного положения».
