Загрузите образец книги
Из этого туториала Вы узнаете, как найти последнее значение в столбце или строке в Excel.
Последнее значение в столбце
Вы можете использовать функцию ПРОСМОТР, чтобы найти последнюю непустую ячейку в столбце.
| 1 | = ПРОСМОТР (2,1 / (B: B ""); B: B) |
Давайте пройдемся по этой формуле.
Часть формулы B: B ”” возвращает массив, содержащий значения True и False: {FALSE, TRUE, TRUE,…}, при тестировании каждая ячейка в столбце B пуста (FALSE).
| 1 | = ПРОСМОТР (2,1 / ({ЛОЖЬ; ИСТИНА; ИСТИНА; ИСТИНА; ИСТИНА; ИСТИНА; ЛОЖЬ;…); B: B) |
Эти логические значения преобразуются в 0 или 1 и используются для деления 1.
| 1 | = ПРОСМОТР (2; {# DIV / 0!; 1; 1; 1; 1; 1; # DIV / 0!;; B: B) |
Это lookup_vector для функции LOOKUP. В нашем случае lookup_value равно 2, но наибольшее значение в lookup_vector равно 1, поэтому функция LOOKUP будет соответствовать последней 1 в массиве и вернет соответствующее значение в result_vector.
Если вы уверены, что в столбце есть только числовые значения, данные начинаются с строки 1, а диапазон данных непрерывен, вы можете использовать несколько более простую формулу с функциями ИНДЕКС и СЧЁТ.
| 1 | = ИНДЕКС (B: B; СЧЁТ (B: B)) |
Функция COUNT возвращает количество ячеек, заполненных данными в непрерывном диапазоне (4), а функция INDEX, таким образом, дает значение ячейки в этой соответствующей строке (4-я).
Чтобы избежать возможных ошибок, когда ваш диапазон данных содержит смесь числовых и нечисловых значений или даже несколько пустых ячеек, вы можете использовать функцию LOOKUP вместе с функциями ISBLANK и NOT.
| 1 | = ПРОСМОТР (2,1 / (НЕ (ПУСТОЙ (B: B))); B: B) |
Функция ISBLANK возвращает массив, содержащий значения True и False, соответствующие единицам и нулям. Функция NOT изменяет True (т.е. 1) на False и False (т.е. 0) на True. Если мы инвертируем этот результирующий массив (при делении 1 на этот массив), мы получим результирующий массив, содержащий снова # DIV / 0! ошибок и единиц, которые можно использовать как поисковый массив (lookup_vector) в нашей функции LOOKUP. Функциональные возможности функции LOOKUP будут такими же, как и в нашем первом примере: она возвращает значение вектора результатов в позиции последней единицы в поисковом массиве.
Когда вам нужно вернуть номер строки с последней записью, вы можете изменить формулу, используемую в нашем первом примере, вместе с функцией ROW в вашем result_vector.
| 1 | = ПРОСМОТР (2,1 / (B: B ""); СТРОКА (B: B)) |
Последнее значение в строке
Чтобы получить значение последней непустой ячейки в строке, заполненной числовыми данными, вы можете использовать аналогичный подход, но с другими функциями: функцию OFFSET вместе с функциями MATCH и MAX.
| 1 | = СМЕЩЕНИЕ (ссылка, строки, столбцы) |
| 1 | = СМЕЩЕНИЕ (B2,0; ПОИСКПОЗ (МАКС (B2: XFD2) + 1; B2: XFD2,1) -1) |
Посмотрим, как работает эта формула.
Функция ПОИСКПОЗ
Мы используем функцию MATCH, чтобы «подсчитать», сколько значений ячеек меньше 1 + максимум всех значений в строке 2, начиная с B2.
| 1 | = ПОИСКПОЗ (искомое_значение, искомое_массив, [тип_соответствия]) |
| 1 | = ПОИСКПОЗ (МАКС (B2: XFD2) + 1; B2: XFD2,1) |
Lookup_value функции MATCH является максимальным из всех значений в строке2 + 1. Поскольку это значение, очевидно, не существует в строке2, а match_type установлено в 1 (меньше или равно lookup_value), функция MATCH вернет Позиция последней «проверенной» ячейки в массиве, то есть количество ячеек, заполненных данными в диапазоне B2: XFD2 (XFD - самый последний столбец в новых версиях Excel).
СМЕЩЕНИЕ Функция
Затем мы используем функцию OFFSET, чтобы получить значение этой ячейки, положение которой было возвращено функцией MATCH.
| 1 | = СМЕЩЕНИЕ (B2,0; C4-1) |
