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

Зміст

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

=MATCH(bignum,range)

Резюме

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

У наведеному прикладі формула в E5 має вигляд:

=MATCH(9.99E+307,B4:B9)

Остання * відносна * позиція, не рядок на аркуші

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

Пояснення

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

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

Результатом є те, що MATCH «відступить» до останнього числового значення в діапазоні і поверне цю позицію.

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

Про bignum

Найбільше число, яке може впоратись Excel, - 9,99999999999999E + 307.

Використовуючи MATCH таким чином, ви можете використовувати будь-яке велике число, яке гарантовано буде більшим за будь-яке значення в діапазоні, наприклад:

=MATCH(1E+06,range) // 1 million =MATCH(1E+09,range) // 1 billion =MATCH(1E+12,range) // 1 trillion

Перевага використання 9.99E + 307 або подібного полягає в тому, що це (1) величезна кількість і (2) розпізнається як заповнювач для "великого числа". Ви побачите, як він використовується в різних вдосконалених формулах Excel.

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

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