как создать и использовать

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

В учебнике прошлой недели мы рассмотрели различные способы определения статического именованного диапазона в Excel. Статическое имя всегда относится к одним и тем же ячейкам, а это означает, что вам придется обновлять ссылку на диапазон вручную всякий раз, когда вы добавляете новые или удаляете существующие данные.

Если вы работаете с постоянно меняющимся набором данных, вы можете сделать свой именованный диапазон динамическим, чтобы он автоматически расширялся для размещения новых добавленных записей или контрактов для исключения удаленных данных. Далее в этом руководстве вы найдете подробное пошаговое руководство о том, как это сделать.

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

Для начала создадим динамический именованный диапазон, состоящий из одного столбца и переменного количества строк. Чтобы это сделать, выполните следующие действия:

  1. На Формула вкладка, в Определенные имена группы, щелкните Определить имя. Или нажмите Ctrl + F3, чтобы открыть диспетчер имен Excel, и нажмите кнопку «Создать…».
  2. В любом случае, Новое имя откроется диалоговое окно, в котором вы указываете следующие детали:
    • в Имя введите имя динамического диапазона.
    • в Сфера раскрывающийся список, установите область действия имени. Рабочая тетрадь (по умолчанию) рекомендуется в большинстве случаев.
    • в Относится к поле, введите либо СЧЕТЧИК СМЕЩЕНИЯ или же ИНДЕКС СЧЕТЧИК формула.
  3. Нажмите «ОК». Сделанный!

На следующем снимке экрана мы определяем динамический именованный диапазон Предметы который вмещает все ячейки с данными в столбце A, кроме строки заголовка:
Построение динамического именованного диапазона в Excel

Формула OFFSET для определения динамического именованного диапазона Excel

Общая формула для создания динамического именованного диапазона в Excel выглядит следующим образом:

КОМПЕНСИРОВАТЬ(первая_ячейка0, 0, СЧЁТ(столбец), 1)

Где:

  • первая_ячейка — первый элемент, который будет включен в именованный диапазон, например, $A$2.
  • столбец — абсолютная ссылка на столбец типа $A:$A.

В основе этой формулы вы используете функцию COUNTA, чтобы получить количество непустых ячеек в интересующем столбце. Этот номер идет непосредственно к высота аргумент OFFSET(ссылка, строки, столбцы, [height], [width]) сообщает ей, сколько строк нужно вернуть.

Кроме того, это обычная формула смещения, где:

  • ссылка является отправной точкой, от которой вы основываете смещение (first_cell).
  • ряды а также столбцы оба равны 0, так как нет столбцов или строк для смещения.
  • ширина равно 1 столбцу.

Например, чтобы построить динамический именованный диапазон для столбца A в Sheet3, начиная с ячейки A2, мы используем эту формулу:

=СМЕЩЕНИЕ(Лист3!$A$2, 0, 0, СЧЁТ(Лист3!$A:$A), 1)

Примечание. Если вы определяете динамический диапазон на текущем листе, вам не нужно включать имя листа в ссылки, Excel сделает это за вас автоматически. Если вы строите диапазон для какого-то другого листа, добавьте префикс к ячейке или ссылке на диапазон с именем листа, за которым следует восклицательный знак (как в приведенном выше примере формулы).

Формула ИНДЕКС для создания динамического именованного диапазона в Excel

Другой способ создать динамический диапазон Excel — использовать СЧЕТЧИК в сочетании с функцией ИНДЕКС.

первая_ячейка:ИНДЕКС(столбец,СЧЁТ(столбец))

Эта формула состоит из двух частей:

  • Слева от оператора диапазона (:) вы помещаете жестко закодированную начальную ссылку, например $A$2.
  • С правой стороны вы используете ИНДЕКС (массив, row_num, [column_num]) для определения конечной ссылки. Здесь вы предоставляете весь столбец A для массива и используете COUNTA, чтобы получить номер строки (т. е. количество не входящих ячеек в столбце A).

Для нашего примера набора данных (см. скриншот выше) формула выглядит следующим образом:

=$A$2:ИНДЕКС($A:$A, СЧЕТЧИК($A:$A))

Поскольку в столбце A есть 5 непустых ячеек, включая заголовок столбца, функция COUNTA возвращает 5. Следовательно, ИНДЕКС возвращает $A$5, то есть последнюю использованную ячейку в столбце A (обычно формула индекса возвращает значение, но ссылка оператор заставляет его вернуть ссылку). И поскольку мы установили $A$2 в качестве отправной точки, конечным результатом формулы будет диапазон $A$2:$A$5.

Чтобы протестировать только что созданный динамический диапазон, вы можете использовать COUNTA для получения количества элементов:

=СЧЕТЧИК(Элементы)
Тестирование динамического именованного диапазона

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

Примечание. Две рассмотренные выше формулы дают один и тот же результат, однако есть разница в производительности, о которой вам следует знать. OFFSET — это энергозависимая функция, которая пересчитывается при каждом изменении листа. На мощных современных машинах и наборах данных разумного размера это не должно быть проблемой. На компьютерах с малой производительностью и больших наборах данных это может замедлить работу Excel. В этом случае лучше использовать формулу ИНДЕКС для создания динамического именованного диапазона.

Как сделать двумерный динамический диапазон в Excel

Чтобы построить двумерный именованный диапазон, в котором не только количество строк, но и количество столбцов является динамическим, используйте следующую модификацию формулы INDEX COUNTA:

первая_ячейка:ИНДЕКС(1$:1048576$, СЧЁТ(первая_колонка), СЧЁТ(Первый ряд)))

В этой формуле у вас есть две функции COUNTA для получения последней непустой строки и последнего непустого столбца (row_num а также номер_столбца аргументы функции ИНДЕКС соответственно). в множество аргумент, вы загружаете весь лист (1048576 строк в Excel 2016–2007; 65535 строк в Excel 2003 и ниже).

А теперь давайте определим еще один динамический диапазон для нашего набора данных: диапазон с именем продажи который включает данные о продажах за 3 месяца (с января по март) и автоматически корректируется по мере добавления новых элементов (строки) или месяцев (столбцы) в таблицу.

Если данные о продажах начинаются в столбце B, строка 2, формула принимает следующий вид:

=$B$2:ИНДЕКС($1:$1048576,СЧЕТЧИК($B:$B),СЧЕТЧИК($2:$2))
Создание двумерного динамического диапазона в Excel

Чтобы убедиться, что ваш динамический диапазон работает должным образом, введите следующие формулы где-нибудь на листе:

=СУММ(продажи)

=СУММ(B2:D5)

Как вы можете видеть на скриншоте ниже, обе формулы возвращают одинаковую сумму. Разница проявляется в тот момент, когда вы добавляете новые записи в таблицу: первая формула (с динамическим именованным диапазоном) будет обновляться автоматически, тогда как вторую придется обновлять вручную при каждом изменении. Это имеет огромное значение, а?
Использование двумерного динамического диапазона в формуле

Как использовать динамические именованные диапазоны в формулах Excel

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

В этом примере мы возьмем классическую формулу ПОИСКПОЗ ИНДЕКС, которая выполняет ВПР в Excel:

ИНДЕКС (диапазон_возвратаСООТВЕТСТВИЕ (искомое_значение, искомый_диапазон0))

Пример набора данных

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

Как показано на снимке экрана выше, мы пытаемся создать информационную панель, где пользователь вводит название товара в H1 и получает общие продажи этого товара в H2. Наша примерная таблица, созданная для демонстрационных целей, содержит всего 4 элемента, но в ваших реальных таблицах могут быть сотни и даже тысячи строк. Кроме того, новые элементы могут добавляться ежедневно, поэтому использование ссылок не вариант, потому что вам придется обновлять формулу снова и снова. Я слишком ленив для этого! 🙂

Чтобы заставить формулу расширяться автоматически, мы собираемся определить 3 имени: 2 динамических диапазона и 1 ячейку со статическим именем:

Lookup_range: =$A$2:ИНДЕКС($A:$A, СЧЕТЧИК($A:$A))

Диапазон_возврата: =$E$2:ИНДЕКС($E:$E, СЧЕТЧИК($E:$E))

Искомое_значение: =$H$1

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

Теперь начните вводить формулу в H1. Когда дело доходит до первого аргумента, введите несколько символов имени, которое вы хотите использовать, и Excel покажет все доступные совпадающие имена. Дважды щелкните соответствующее имя, и Excel сразу вставит его в формулу:
Добавление именованного диапазона в формулу

Готовая формула выглядит следующим образом:

=ИНДЕКС(Диапазон_возврата, ПОИСКПОЗ(Искомое_значение, Диапазон_искомого, 0))

И работает отлично!
Использование динамических именованных диапазонов в формуле

Как только вы добавите в таблицу новые записи, они сразу же будут включены в ваши расчеты, и вам не придется вносить в формулу ни единого изменения! И если вам когда-нибудь понадобится перенести формулу в другой файл Excel, просто создайте те же имена в целевой книге, скопируйте/вставьте формулу, и она сразу же заработает.

Кончик. Помимо того, что формулы становятся более надежными, динамические диапазоны удобны для создания динамических выпадающих списков.

Вот как вы создаете и используете динамические именованные диапазоны в Excel. Чтобы поближе познакомиться с формулами, обсуждаемыми в этом руководстве, вы можете загрузить наш образец Книга Excel с динамическим именованным диапазоном. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Вас также могут заинтересовать

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *