Формула Excel: Останній рядок у текстових даних -

Зміст

Загальна формула

=MATCH(bigtext,range)

Резюме

Щоб отримати останнє відносне положення (тобто останній рядок, останній стовпець) для текстових даних (з порожніми клітинками або без них), ви можете використовувати функцію MATCH. У наведеному прикладі формула в D5 має вигляд:

=MATCH(REPT("z",255),B4:B11)

Пояснення

Ця формула використовує функцію MATCH в режимі приблизного збігу, щоб знайти останнє текстове значення в діапазоні. Приблизний збіг увімкнено, встановивши для третього аргументу в MATCH значення 1, або опустивши цей аргумент, який за замовчуванням має значення 1.

Значення пошуку - це так званий "великий текст" (іноді скорочений "bigtext"), який навмисно є значенням "більшим", ніж будь-яке значення, яке з'явиться в діапазоні. Під час роботи з текстом, який сортується за алфавітом, це означає текстове значення, яке завжди відображатиметься в кінці алфавітного порядку сортування.

Оскільки ця формула відповідає тексту, ідея полягає в тому, щоб побудувати значення підстановки, яке ніколи не зустрічається у фактичному тексті, але завжди буде останнім. Для цього ми використовуємо функцію REPT, щоб повторити букву "z" 255 разів. Число 255 представляє найбільшу кількість символів, яку MATCH допускає у значенні підстановки.

Коли MATCH не може знайти це значення, він "відступить" до останнього текстового значення в діапазоні і поверне позицію цього значення.

Примітка. Цей підхід чудово працює з порожніми клітинками в діапазоні, але не є надійним із змішаними даними, що включають як цифри, так і текст.

Останнє відносне положення проти номера останнього рядка

Створюючи розширені формули, що створюють динамічні діапазони, часто потрібно з'ясувати останнє розташування даних у списку. Залежно від даних, це може бути останній рядок із даними, останній стовпець із даними або перетин обох. Примітка: ми хочемо останню відносну позицію всередині даного діапазону, а не номер рядка на аркуші:

Динамічний діапазон

Ви можете використовувати цю формулу для створення динамічного діапазону з іншими функціями, такими як INDEX та OFFSET. Див. Посилання нижче для прикладів та пояснень: