Седрику необходимо определить значение предпоследней ячейки в столбце, к которому он обычно добавляет значения. Он видел формулы для получения последнего значения, но не предпоследнего.

Есть много формальных способов решения этой задачи. Во многих отношениях выбранный вами метод будет зависеть от характеристик данных в столбце. Предположим, что ваши данные находятся в столбце A, что они числовые и что ни в одной из ячеек столбца нет пробелов. В этом случае вы можете использовать следующую формулу:

=INDEX(A:A,COUNT(A:A)-1)

Обратите внимание, что эта формула не вернет правильный результат, если в столбце A есть текстовые значения, даже если это текстовое значение находится в заголовке. Если вы хотите компенсировать заголовок, вы можете изменить формулу таким образом. (Обратите внимание, что он проверяет только диапазон A2: A1000. Если ваши данные выходят за пределы строки 1000, вам необходимо изменить указанный диапазон.)

=INDEX(A2:A1000,COUNT(A2:A1000)-1)

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

=OFFSET(INDIRECT("A"&MATCH(9^9,A:A)),-1,0)

=INDEX(A:A,MATCH(9.9E+23,A:A,1)-1,1)

=INDEX(A:A,(MATCH(LOOKUP(10000,A:A),A:A)-1),1)

=INDEX(A:A,AGGREGATE(14,6,ROW(A:A)*A:A/A:A,2))

Вы должны понимать, что если предпоследняя ячейка в столбце пуста, то эти формулы вернут 0. (Ну, за исключением формулы, которая использует функцию АГРЕГАТ. Она возвращает значение в первой непустой ячейке перед предпоследняя ячейка.)

Если ваши данные в столбце A могут содержать числа или текст (но все еще не пустые ячейки), вам нужна другая формула. Подойдет любой из них:

=INDEX(A:A,COUNTA(A:A)-1,1)

=INDIRECT("A"&COUNTA(A:A)-1)

Если в ваших данных могут быть пустые ячейки, вы можете использовать любое из следующего:

=INDEX(A:A,MATCH(LOOKUP(2,1/(A:A<>""),A:A),A:A,0)-1)

=INDEX(A:A,LOOKUP(2,1/(A:A<>""),ROW(A:A))-1)

Опять же, если предпоследняя ячейка пуста, формула возвращает 0.

Наконец, вы также можете использовать следующий тип формулы при условии, что ваши ячейки в столбце используют именованный диапазон (в данном случае MyData).

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

=INDEX(MyData,ROWS(MyData)-1)

Обратите внимание, что практически все формулы, упомянутые в этом совете, содержат в себе -1. Это указывает на то, что вам нужна не последняя ячейка в диапазоне, а предпоследняя («одна вверх» от нижней части диапазона).

Если вам нужно другое смещение от нижней границы диапазона, вы можете изменить эту часть каждой формулы. Например, если вы хотите на 2 ячейки вверх от нижней части диапазона, вы должны изменить все экземпляры -1 на -2.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (1523) применим к Microsoft Excel 2007, 2010, 2013 и 2016.