как создать и использовать
В этом руководстве вы узнаете, как создать динамический именованный диапазон в Excel и как использовать его в формулах для автоматического включения новых данных в расчеты.
В учебнике прошлой недели мы рассмотрели различные способы определения статического именованного диапазона в Excel. Статическое имя всегда относится к одним и тем же ячейкам, а это означает, что вам придется обновлять ссылку на диапазон вручную всякий раз, когда вы добавляете новые или удаляете существующие данные.
Если вы работаете с постоянно меняющимся набором данных, вы можете сделать свой именованный диапазон динамическим, чтобы он автоматически расширялся для размещения новых добавленных записей или контрактов для исключения удаленных данных. Далее в этом руководстве вы найдете подробное пошаговое руководство о том, как это сделать.
Как создать динамический именованный диапазон в Excel
Для начала создадим динамический именованный диапазон, состоящий из одного столбца и переменного количества строк. Чтобы это сделать, выполните следующие действия:
- На Формула вкладка, в Определенные имена группы, щелкните Определить имя. Или нажмите Ctrl + F3, чтобы открыть диспетчер имен Excel, и нажмите кнопку «Создать…».
- В любом случае, Новое имя откроется диалоговое окно, в котором вы указываете следующие детали:
- в Имя введите имя динамического диапазона.
- в Сфера раскрывающийся список, установите область действия имени. Рабочая тетрадь (по умолчанию) рекомендуется в большинстве случаев.
- в Относится к поле, введите либо СЧЕТЧИК СМЕЩЕНИЯ или же ИНДЕКС СЧЕТЧИК формула.
- Нажмите «ОК». Сделанный!
На следующем снимке экрана мы определяем динамический именованный диапазон Предметы который вмещает все ячейки с данными в столбце A, кроме строки заголовка:
Формула 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))
Чтобы убедиться, что ваш динамический диапазон работает должным образом, введите следующие формулы где-нибудь на листе:
=СУММ(продажи)
=СУММ(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 с динамическим именованным диапазоном. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
