В моей недавней статье я говорил все о link: / excel-range-name-all-about-excel-named-range-excel-range-name [именованные диапазоны в excel]. При изучении именованных диапазонов возникла тема динамического ранжирования. В этой статье я объясню, как сделать динамический диапазон в Excel.

243

Что такое динамический именованный диапазон в Excel?

Нормальный именованный диапазон статичен. Если вы определите C2: C10 как Item, Item всегда будет ссылаться на C2: C10 до тех пор, пока вы не отредактируете его вручную. На изображении ниже мы подсчитываем пробелы в списке элементов. Показывает 2.

Если бы он был динамическим, он бы показал 0.

244

Динамический диапазон имен — это диапазон имен, который расширяется и сжимается в соответствии с данными. Например, если у вас есть список элементов в диапазоне C2: C10 и назовите его Items, он должен расшириться до C2: C11, если вы добавляете новый элемент в диапазон, и должен уменьшиться, если вы уменьшите его при удалении, как указано выше.

Как создать динамический диапазон имен

Создание именованных диапазонов с помощью таблиц Excel

Да, link: / table-excel-2007-17-amazing-features-of-excel-tables [excel tables] может создавать динамические именованные диапазоны. Они сделают каждый столбец в таблице с именем range очень динамичным.

Но у имен таблиц есть один недостаток: их нельзя использовать при проверке данных и условном форматировании. Но там можно использовать определенные именованные диапазоны.

Использование формулы INDIRECT и COUNTA Чтобы сделать диапазон имен динамическим, мы можем использовать link: / lookup-formulas-excel-Indirect-function [INDIRECT]

и link: / counting-excel-counta-function [COUNTA function]

  1. Как? Давайте посмотрим.

Общая формула должна быть записана в разделе «Ссылка на:»

=INDIRECT("$startingCell:$endingColumnLetter$"&COUNTA($columnLetter:$columnLetter))

Приведенная выше общая формула может показаться сложной, но на самом деле это просто. Посмотрим на примере.

Основная идея — определить последнюю использованную ячейку.

Пример динамического диапазона В приведенном выше примере у нас был статический диапазон имен Item в диапазоне C2: C10. Давайте сделаем это динамичным.

245

  • Откройте диспетчер имен, нажав CTRL + F3.

    • Теперь, если имя уже существует в диапазоне, щелкните его, а затем нажмите кнопку «Изменить». В противном случае нажмите New.

    • Назовите это Item.

    • В разделе «Относится к:» укажите формулу ниже.

=INDIRECT("$C2:$C$"&COUNTA($C:$C))

  • Нажмите кнопку ОК.

И дело сделано. Теперь, когда вы набираете элемент в поле имени или в любой формуле, он будет ссылаться на C2 на последнюю использованную ячейку в диапазоне.

246

Внимание: в промежутках между ячейками не должно быть пустых ячеек. В противном случае диапазон будет уменьшен на количество пустых ячеек.

Как это работает?

Как я уже сказал, единственное дело — найти последнюю использованную ячейку. В этом примере между ними не должно быть пустых ячеек. Почему? Вы узнаете.

Функция ДВССЫЛ в Excel преобразует текст в диапазон.

ссылка: / поиск-формулы-excel-косвенная-функция [КОСВЕННО] («$ C $ 2: $ C $ 9»)

будет относиться к абсолютному диапазону $ C $ 2: $ C $ 10. Нам просто нужно динамически найти номер последней строки (9).

Поскольку все ячейки имеют какое-то значение в диапазоне C2: C10, мы можем использовать link: / counting-excel-counta-function [COUNTA function], чтобы найти последнюю строку.

Итак, = INDIRECT («$ C2: $ C $» & эта часть исправляет начальную строку и столбец, а COUNTA ($ C: $ C) динамически вычисляет последнюю использованную строку.

Так что да, вот как вы можете создать наиболее эффективные динамические именованные диапазоны, которые будут работать со всеми формулами и функциями Excel. Вам не нужно снова редактировать именованный диапазон при изменении данных.

Загрузить файл: