Формула Excel, получить последнюю строку, где диапазон является динамическим

Question

Я хочу получить номер последней строки диапазона, но диапазон будет динамическим.

Я не могу использовать именованный диапазон, так как иногда определенный диапазон может измениться в моей электронной таблице. Т.е. в один день именованный диапазон может составлять 50 строк, в следующий - 51. Насколько мне известно, диапазон имен не изменится сам.

Мне нужно что-то вроде: получить последний ряд, где A: A = «стек».

Попытка сделать что-то вроде, https://www.excel-easy.com/examples/dynamic-named-range.html , возникла проблема с реализацией.

Любая помощь будет оценена.

Answer 1

В столбце с текстом вы можете найти последнюю строку, которая имеет определенное значение с этой формулой. Это формула массива и должна быть подтверждена с помощью Ctrl+Shift+Enter

=MAX((A1:A100="stack")*(ROW(A1:A100)))

Смотрите ячейку C1 на скриншоте ниже.

Вы можете превратить эту функцию в имя динамического диапазона. Эта формула всегда начинает диапазон в A1 и опускается до последнего вхождения ячейки с текстом "stack":

=OFFSET(Sheet1!$A$1,0,0,MAX((Sheet1!$A$1:$A$100="stack")*(ROW(Sheet1!$A$1:$A$100))),1)

В менеджере имен вам не нужно использовать Ctrl+Shift+Enter, поскольку все именованные формулы автоматически оцениваются как формулы массива. Если вы используете этот подход, было бы лучше ограничить количество строк в формуле и не использовать ссылки на весь столбец.

Обратите внимание на марширующих муравьев на скриншоте, которые окружают текущий диапазон.

Если вы хотите включить в текст все строки до последней строки, то вы можете использовать более простую формулу.

=OFFSET(Sheet1!$A$1,0,0,MATCH("zzzzz",Sheet1!$A:$A,1),1)

С Match, вы можете использовать ссылку на весь столбец.

Answer 2

Допустим, ваша первая ячейка никогда не меняется, и, скажем, это "A1" . Для этого вы должны использовать такой фрагмент кода:

private function get_last_row(byref ws as worksheet, optional byval letter_of_first_col as string = "A", optional byval index_of_first_row as interger = 1) as long
    with ws
        get_last_row = .range(letter_of_first_row & index_of_first_row).currentRegion.rows.count + (index_of_first_row - 1)
    end with
end function

Надеюсь, это поможет вам