Загальна формула
=$A$1:INDEX($A:$A,lastrow)
Резюме
Одним із способів створення динамічного іменованого діапазону в Excel є використання функції INDEX. У наведеному прикладі названий діапазон "дані" визначається за такою формулою:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
яка роздільна здатність до діапазону $ A $ 2: $ A $ 10.
Примітка: ця формула призначена для визначення іменованого діапазону, який можна використовувати в інших формулах.
Пояснення
На цій сторінці показаний приклад динамічного іменованого діапазону, створеного за допомогою функції INDEX разом із функцією COUNTA. Динамічні іменовані діапазони автоматично розширюються та стискаються, коли дані додаються або видаляються. Вони є альтернативою використанню таблиці Excel, яка також змінює розмір у міру додавання чи видалення даних.
Функція INDEX повертає значення в заданій позиції в діапазоні або масиві. Ви можете використовувати INDEX для отримання окремих значень або цілих рядків і стовпців у діапазоні. Що робить INDEX особливо корисним для динамічних іменованих діапазонів, так це те, що він фактично повертає посилання. Це означає, що ви можете використовувати INDEX для побудови змішаних посилань, таких як $ A $ 1: A100.
У наведеному прикладі названий діапазон "дані" визначається за такою формулою:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
яка роздільна здатність до діапазону $ A $ 2: $ A $ 10.
Як працює ця формула
Спочатку зауважимо, що ця формула складається з двох частин, які розташовані по обидва боки оператора діапазону (:). Ліворуч у нас є початковий еталон для діапазону, кодований як:
$A$2
Справа - кінцеве посилання на діапазон, створений за допомогою INDEX, як це:
INDEX($A:$A,COUNTA($A:$A))
Тут ми подаємо INDEX весь стовпець A для масиву, а потім використовуємо функцію COUNTA, щоб з’ясувати «останній рядок» у діапазоні. COUNTA тут добре працює, оскільки в стовпці A є 10 значень, включаючи рядок заголовка. Тому COUNTA повертає 10, яке безпосередньо переходить у INDEX як номер рядка. Потім INDEX повертає посилання на $ A $ 10, останній використаний рядок у діапазоні:
INDEX($A:$A,10) // resolves to $A$10
Отже, кінцевим результатом формули є такий діапазон:
$A$2:$A$10
Двовимірний діапазон
Наведений приклад працює для одновимірного діапазону. Для створення двовимірного динамічного діапазону, де кількість стовпців також є динамічним, ви можете використовувати той самий підхід, розширений таким чином:
=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Як і раніше, COUNTA використовується для з'ясування "останнього", і ми знову використовуємо COUNTA, щоб отримати "останню колонку". Вони подаються до індексу як number_num та column_num відповідно.
Однак для масиву ми надаємо повний аркуш, введений як усі 1048576 рядків, що дозволяє INDEX повертати посилання у двовимірному просторі.
Примітка: Excel 2003 підтримує лише 65535 рядків.
Визначення останнього ряду
Існує кілька способів визначити останній рядок (останнє відносне положення) у наборі даних, залежно від структури та змісту даних на аркуші:
- Останній рядок у змішаних даних із пробілами
- Останній рядок у змішаних даних без пробілів
- Останній рядок у текстових даних
- Останній рядок у числових даних
