Загрузите образец книги
В этом руководстве будет показано, как найти последнюю непустую строку в наборе данных в Excel и Google Таблицах.
Найдите последнюю строку с данными
Часто бывает полезно знать, на какой строке заканчиваются ваши данные. Если в вашем диапазоне есть или могут быть пустые ячейки, вы можете найти последнюю непустую строку одним из следующих способов.
Универсальный метод
Первый метод использует функции ROW и MAX и может использоваться с любыми типами данных:
| 1 | = МАКС ((B: B "") * (СТРОКА (B: B))) |
Давайте проанализируем эту формулу.
Начнем с логической проверки столбца данных. Наша формула смотрит на весь столбец (B: B) и выдает ИСТИНА для непустых ячеек и ЛОЖЬ для пустых ячеек.
| 1 | = B: B "" |
Функция СТРОКА производит номер строки данной ячейки. Если мы не указываем конкретную ячейку ввода, он дает номер строки той ячейки, в которой находится.
| 1 | = СТРОКА () |
Умножение каждого номера строки на соответствующие значения ИСТИНА (= 1) или ЛОЖЬ (= 0) возвращает номер строки для заполненной ячейки и ноль для пустой ячейки.
| 1 | = E3 * F3 |
Функция MAX дает максимум набора чисел. В этом примере, поскольку все пустые ячейки дают нулевое значение, максимум - это самый высокий номер строки.
| 1 | = МАКС (G3: G12) |
Объединение этих шагов дает нам исходную формулу:
| 1 | = МАКС ((B: B "") * (СТРОКА (B: B))) |
Обратите внимание, что это формула массива, поэтому, если вы используете Excel 2022 или более раннюю версию, вам нужно нажать CTRL + SHIFT + ENTER, чтобы вызвать ее.
Метод для текстового диапазона
Если ваш (не непрерывный) диапазон содержит только текстовые значения и пустые ячейки, вы можете использовать менее сложную формулу, содержащую функции REPT и MATCH:
| 1 | = ПОИСКПОЗ (ПОВТОР ("z"; 50); B: B) |
Посмотрим, как работает эта формула.
Функция ПОВТОР
Функция REPT повторяет текстовую строку заданное количество раз. В этом примере мы можем использовать его для создания текстовой строки, которая будет последней в любом алфавитно отсортированном списке. Если мы повторим «z» 50 раз, это должно сработать практически для любого столбца текстовых данных; в алфавитном порядке не будет записей перед «zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz».
| 1 | = ПОВТОР ("z"; 50) |
Функция ПОИСКПОЗ
Функция MATCH находит заданное значение поиска в массиве.
Мы выполняем поиск нашей текстовой строки размером 50 z во всем столбце данных. Отсутствие ввода типа соответствия в функции MATCH указывает ей найти приблизительное, а не точное совпадение.
| 1 | = ПОИСКПОЗ (ПОВТОР ("z"; 50); B: B) |
Функция ПОИСКПОЗ выполняет поиск в столбце B и ищет нашу текстовую строку из 50 «z». Поскольку он не находит его, формула возвращает позицию последней непустой ячейки. Эта ячейка содержит последнее значение в массиве поиска, которое меньше (или равно) значению поиска.
Имейте в виду, что эта формула работает только в том случае, если ваш диапазон содержит исключительно текст и пустые ячейки (или, по крайней мере, значение последней ячейки не является числовым).
Найти последнюю строку с данными в Google Таблицах
Эти формулы работают в Google Таблицах точно так же, как и в Excel.
