Завантажте зразок робочої книги
Цей підручник покаже, як знайти останній непустий рядок у наборі даних у Excel та Google Таблицях.
Знайдіть останній рядок із даними
Часто корисно знати, у якому рядку закінчуються ваші дані. Якщо у вашому діапазоні є чи можуть бути порожні клітинки, ви можете знайти останній непустий рядок за допомогою одного з наведених нижче способів.
Універсальний метод
Перший метод використовує функції ROW і MAX і може використовуватися з будь -якими даними:
| 1 | = MAX ((B: B "")*(ROW (B: B))) |
Розберемо цю формулу.
Ми починаємо з використання логічного тесту у стовпці даних. Наша формула переглядає весь стовпець (B: B) і виводить TRUE для непустих клітинок та FALSE для порожніх клітинок.
| 1 | = B: B "" |
Функція ROW створює номер рядка даної комірки. Якщо ми не надаємо йому певного введення комірки, воно вказує номер рядка клітинки, у якій він знаходиться.
| 1 | = ROW () |
Помноження кожного номера рядка на відповідні значення TRUE (= 1) або FALSE (= 0) повертає номер рядка для заповненої комірки та нуль для порожньої комірки.
| 1 | = E3*F3 |
Функція MAX дає максимальний набір чисел. У цьому прикладі, оскільки всі порожні клітинки дають нульове значення, максимальним є найвищий номер рядка.
| 1 | = МАКС. (G3: G12) |
Поєднання цих кроків дає нам нашу оригінальну формулу:
| 1 | = MAX ((B: B "")*(ROW (B: B))) |
Зверніть увагу, що це формула масиву, тому, якщо ви використовуєте Excel 2022 або раніше, вам потрібно натиснути CTRL + SHIFT + ENTER, щоб запустити її.
Метод діапазону тексту
Якщо ваш (неперервний) діапазон містить лише текстові значення та порожні клітинки, можна використати менш складну формулу, що містить функції REPT та MATCH:
| 1 | = MATCH (REPT ("z", 50), B: B) |
Давайте подивимося, як працює ця формула.
Функція REPT
Функція REPT повторює текстовий рядок задану кількість разів. Для цього прикладу ми можемо використовувати його для створення текстового рядка, який буде останнім у будь -якому списку, відсортованому за алфавітом. Якщо ми повторимо “z” 50 разів, це має спрацювати практично для будь -якого стовпця текстових даних; жодні записи не надходитимуть до “zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz” у алфавітному порядку.
| 1 | = REPT ("z", 50) |
Функція MATCH
Функція MATCH знаходить задане значення пошуку в масиві.
Ми виконуємо пошук у всьому стовпці даних для нашого текстового рядка 50-z. Якщо не вводити тип відповідності у функції MATCH, вона знаходить приблизне, а не точне збіг.
| 1 | = MATCH (REPT ("z", 50), B: B) |
Функція MATCH здійснює пошук у стовпці B і шукає наш текстовий рядок 50 “z”. Оскільки вона не знаходить її, формула повертає позицію останньої не порожньої клітинки. Ця клітинка містить останнє значення в масиві пошуку, яке менше (або дорівнює) значенню пошуку.
Майте на увазі, що ця формула працює лише тоді, коли ваш діапазон містить виключно текст та порожні клітинки (або принаймні значення останньої клітинки є нечисловим).
Знайдіть останню рядок із даними в Таблицях Google
Ці формули працюють точно так само в Google Таблицях, як і в Excel.
