Поиск позиции предпоследней заполненной ячейке в EXCEL

Найдем значение и позицию предпоследней заполненной ячейке в диапазоне ячеек MS EXCEL.

Подобная задача, но для последней ячейки решена в статье Последняя заполненная ячейка в MS EXCEL .

Диапазон не содержит пропусков

Пусть столбец с данными находится в диапазоне А7:А18 .

Как видно из рисунка, между значениями нет пустых ячеек; кроме того, не весь диапазон заполнен значениями.

В нашем случае в столбце нет пустых ячеек между значениями, поэтому решение элементарно: формула =СЧЁТЗ(A7:A18)-1 вернет позицию предпоследней заполненной ячейке в столбце А7:А18

Формула =ИНДЕКС(A7:A18;СЧЁТЗ(A7:A18)-1) вернет значение этой ячейки.

Примечание : Файл примера содержит все приведенные в статье формулы (кнопка внизу статьи).

Диапазон содержит пропуски

Если есть пропуски, то позицию определить сложнее.

Предположим также, что мы не знаем позицию последней заполненной ячейки, а знаем только первую заполненную ячейку столбца (пусть это будет В7 ): 1) определяем позицию последней заполненной ячейки (работает, если в столбце содержатся числа) =ПОИСКПОЗ(1E+306;B7:B18;1) см. статью Последняя заполненная ячейка в MS EXCEL 2) Поместим это значение в ячейку С10 3) Формула =СМЕЩ(B7;;;С10-1) вернет диапазон от первой заполненной ячейки до предпоследней (не обязательно заполненной)4) Формула массива =МАКС(ЕСЛИ(ЕПУСТО(СМЕЩ(B7;;;С10-1));"";СТРОКА(СМЕЩ(B7;;;С10-1))-СТРОКА(B7)+1)) вернет позицию предпоследней заполненной ячейки.Формула работает так: сначала она определяет номера строк (точнее позицию в столбце, начиная с первой заполненной ячейки В7 ) всех непустых ячеек в диапазоне, полученном на шаге 3. Максимальная позиция будет соответствовать предпоследней заполненной ячейке.