Формула Excel: получить значение последней непустой ячейки -

Общая формула

=LOOKUP(2,1/(A:A""),A:A)

Резюме

Чтобы найти значение последней непустой ячейки в строке или столбце, вы можете использовать функцию ПРОСМОТР в этой удивительно компактной формуле. В качестве дополнительного бонуса эта формула не является формулой массива и не является изменчивой.

Объяснение

Ключом к пониманию этой формулы является осознание того, что lookup_value, равный 2, преднамеренно больше любых значений, которые появятся в lookup_vector.

  1. Выражение A: A "" возвращает массив истинных и ложных значений: (ИСТИНА, ЛОЖЬ, ИСТИНА,…).
  2. Затем число 1 делится на этот массив и создается новый массив, состоящий либо из единиц, либо из ошибок деления на ноль (# DIV / 0!): (1,0,1,…). Этот массив и есть lookup_vector.
  3. Если lookup_value не может быть найден, LOOKUP будет соответствовать следующему наименьшему значению.
  4. В этом случае lookup_value равно 2, но наибольшее значение в lookup_array равно 1, поэтому поиск будет соответствовать последней 1 в массиве.
  5. ПРОСМОТР возвращает соответствующее значение в result_vector (т.е. значение в той же позиции).

Работа с ошибками

Если в lookup_vector есть ошибки, особенно если есть ошибка в последней непустой ячейке, эту формулу необходимо скорректировать. Эта корректировка необходима, потому что критерий "" сам вернет ошибку, если ячейка содержит ошибку. Чтобы обойти эту проблему, используйте ISBLANK с NOT:

=LOOKUP(2,1/(NOT(ISBLANK(A:A))),A:A)

Последнее числовое значение

Чтобы получить последнее числовое значение, вы можете добавить функцию ISNUMBER следующим образом:

=LOOKUP(2,1/(ISNUMBER(A1:A100)),A1:A100)

Положение последнего значения

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

=LOOKUP(2,1/(A:A""),ROW(A:A))

Здесь мы вводим номера строк из того же диапазона в поиск вектора результатов и возвращаем номер строки последнего совпадения.

Интересные статьи...