Поиск позиции предпоследней заполненной ячейке в MS 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. Максимальная позиция будет соответствовать предпоследней заполненной ячейке.


Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (1 оценка)