Динамический диапазон в Excel

Обновлено: 05.07.2024

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

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

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

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

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

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

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

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

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

В основе этой формулы лежит функция СЧЁТЕЗ, чтобы получить количество непустых ячеек в интересующем столбце. Это число передается непосредственно в аргумент height функции OFFSET(reference, rows, cols, [height], [width]), сообщая ей, сколько строк нужно вернуть.

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

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

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

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

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

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

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

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

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

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

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

Если все сделано правильно, результат формулы изменится, как только вы добавите или удалите элементы из списка:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

И работает отлично!

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

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

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

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

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

У меня возникли проблемы с вводом формулы для динамического диапазона.
=Лист1!АДРЕС(СООТВЕТСТВИЕ(СЕГОДНЯ():$P:$P,0),16):ИНДЕКС($P:$P, СЧЕТЧИК($P:$P))
или < br />=Sheet1!ADDRESS(MATCH(TODAY(),P10:P374,0),16):$P$374
Постоянно выдает мне сообщение о том, что я пытаюсь ввести формулу, должно начинаться с= или -.
Когда я ввожу только формулу ячейки, она принимает ее, но мне нужен диапазон.
=Лист1!АДРЕС(ПОИСКПОЗ(СЕГОДНЯ():$P:$P,0),16) Работает, чтобы дать адрес ячейки сегодняшней даты.
Это создает динамический диапазон, начинающийся с сегодняшней даты в столбце P, и я хочу расширить его до конца данных в столбце P. Столбец P представляет собой список дат.
Мне это нужно, чтобы найти следующее значение в другом столбце после строки с сегодняшней датой.
Это отличается от большинства динамических диапазонов, которые расширяют только нижнюю часть. Я хочу сделать и то, и другое. Один для перемещения верхней части на основе сегодняшней даты и нижней части для расширения на основе добавляемых данных.
Я надеюсь, что вы можете мне помочь. Я многому научился у вас.
Спасибо,
Кольт

Здравствуйте!
Если ваш список дат начинается в ячейке A1, вы можете использовать формулу для создания динамического диапазона, начиная с текущей даты:

Это должно решить вашу задачу.

Здравствуйте,
Как я могу заставить заданное имя работать для динамического диапазона, когда в моем диапазоне есть как пустые, так и непустые ячейки?

Здравствуйте!
Попробуйте использовать функцию СЧИТАТЬПУСТОТЫ в формуле динамического диапазона:

СМЕЩ(первая_ячейка, 0, 0, СЧЕТЧАСТЬ(столбец)+СЧЕТПУСТОТЫ(столбец), 1)

Надеюсь, это будет полезно.

Не думаю, что это сработает. Функция СЧИТАТЬПУСТОТЫ также будет подсчитывать все пробелы под последним элементом в столбце, что дает очень большое значение для современных версий Excel.

Каждый день я копирую список цен акций из Yahoo Finance в Excel. Поместив последние цены акций на конец дня в крайний правый столбец таблицы, все предыдущие записи переместятся на один столбец влево. (Excel делает это для меня).
Цена закрытия каждой компании за каждый день, начиная с даты начала и до настоящего времени, указана в строке. Столбец, содержащий дату начала, перемещается влево в другой столбец день за днем.

Я делаю графики каждой компании. Диаграмма включает дату начала (когда я впервые начал собирать данные компании). Диаграмма записывает до сегодняшнего дня.

Я знаю, где находится каждая дата начала. Используя Index и Match, я могу точно определить, в каком столбце находится дата начала. Я могу перечислить ссылку на столбец в ячейке: - например, в ячейке Q1 указан столбец «CME».

Сегодня в столбце с датой начала моей работы в компании RCP.L стоит CME. Вчера, до того, как я взял дневные цифры, это был CMF.

Теперь мне нужно автоматическое средство (формула) для ввода CME14:CME770 в формулу ИНДЕКС И ПОИСКПОЗ (ниже), которая вчера читалась как CMF14:CMF770:-

Итак, формула совпадения индекса для вчерашнего дня: -
ИНДЕКС(Закрыть!CMF$14:CMF$770,MATCH($Q$1,Закрыть!$ATK$14:$ATK$770,0))
Он считывает вчерашний диапазон, а не сегодняшний.

($SQ$S1 считывает ячейку, содержащую "RCP.L", чтобы найти строку в диапазоне столбцов CMF14:CMF770.)

Сегодня я хочу, чтобы формула сопоставления ИНДЕКС была следующей:
ИНДЕКС(Close!CME$14:CME$770,MATCH($Q$1,Close!$ATK$14:$ATK$770,0))

Завтра мне понадобится CMD14:CMD770 в формуле и так далее.

Я много читал и полностью застрял. Не могли бы вы помочь? С уважением, Филипп.

Здравствуйте!
Как создать динамический именованный диапазон для разблокированных диапазонов с помощью пароля, когда лист защищен.

Один из способов создать динамический именованный диапазон с помощью формулы — использовать функцию СМЕЩ вместе с функцией СЧЁТЕС. Динамические диапазоны также называются расширяющимися диапазонами: они автоматически расширяются и сужаются для размещения новых или удаленных данных.

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

В показанном примере для динамического диапазона используется следующая формула:

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

Первый аргумент в OFFSET представляет первую ячейку в данных (начало координат), в данном случае это ячейка B5. Следующие два аргумента представляют собой смещения для строк и столбцов и задаются равными нулю.

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

Для высоты мы используем функцию COUNTA для подсчета непустых значений в диапазоне B5:B100. Это предполагает отсутствие пустых значений в данных и значений выше B100. СЧЕТЧИК возвращает 6.

Для ширины мы используем функцию COUNTA для подсчета непустых значений в диапазоне B5:Z5. Это предполагает отсутствие ячеек заголовков и заголовков за пределами Z5. СЧЕТЧИК возвращает 6.

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

С этой информацией функция OFFSET возвращает ссылку на B5:G10, что соответствует диапазону из 6 строк в высоту и 6 столбцов в ширину.

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

Вариант с полными ссылками на столбцы/строки

Вы также можете использовать полные ссылки на столбцы и строки для высоты и ширины следующим образом:

Обратите внимание, что высота корректируется с помощью -2, чтобы учесть значения заголовка и заголовка в ячейках B4 и B2. Преимуществом этого подхода является простота диапазонов внутри COUNTA. Недостатком является огромный размер полных столбцов и строк — необходимо соблюдать осторожность, чтобы не допустить появления ошибочных значений за пределами диапазона, поскольку они могут легко привести к сбою в подсчете.

Определение последней строки

Существует несколько способов определить последнюю строку (последнюю относительную позицию) в наборе данных в зависимости от структуры и содержимого данных на листе:

< бр />

Алан Мюррей

Алан Мюррей
Писатель

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

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

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

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

Создание динамического заданного диапазона в Excel

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

Нам нужно, чтобы это было динамически, чтобы при добавлении или удалении других стран диапазон автоматически обновлялся.

В этом примере мы хотим избежать ячейки заголовка. Таким образом, нам нужен диапазон $A$2:$A$6, но динамический. Для этого нажмите Формулы > Определить имя.

Введите «страны» в поле «Имя», а затем введите приведенную ниже формулу в поле «Относится к».

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

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

Первая часть формулы указывает начальную ячейку диапазона (в нашем случае A2), а затем следует оператор диапазона (:).

Использование оператора диапазона заставляет функцию ИНДЕКС возвращать диапазон вместо значения ячейки. Затем функция ИНДЕКС используется с функцией СЧЕТЧИК. COUNTA подсчитывает количество непустых ячеек в столбце A (в нашем случае шесть).

Эта формула запрашивает у функции ИНДЕКС диапазон последней непустой ячейки в столбце A ($A$6).

Конечным результатом является $A$2:$A$6, и из-за функции COUNTA он является динамическим, так как будет найдена последняя строка. Теперь вы можете использовать это определенное имя «страны» в правиле проверки данных, формуле, диаграмме или везде, где нам нужно сослаться на названия всех стран.

Создать двухсторонний динамический заданный диапазон

Первый пример был динамическим только по высоте. Однако с небольшой модификацией и другой функцией COUNTA вы можете создать диапазон, динамический как по высоте, так и по ширине.

В этом примере мы будем использовать данные, показанные ниже.

На этот раз мы создадим динамически определяемый диапазон, включающий заголовки. Нажмите «Формулы» > «Определить имя».

Введите «продажи» в поле «Имя» и введите приведенную ниже формулу в поле «Относится к».

В этой формуле в качестве начальной ячейки используется $A$1. Затем функция ИНДЕКС использует диапазон всего рабочего листа ($1:$1048576) для поиска и возврата.

Одна из функций COUNTA используется для подсчета непустых строк, а другая — для непустых столбцов, что делает ее динамической в обоих направлениях. Хотя эта формула начиналась с A1, вы могли указать любую начальную ячейку.

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

› Как подсчитать количество ячеек с текстом в Microsoft Excel
› Почему СМС должен умереть
› Что означает XD и как вы его используете?
› Как восстановить метки панели задач в Windows 11
› Почему прозрачные чехлы для телефонов желтеют?
› Худшее, что есть в телефонах Samsung, — это программное обеспечение Samsung.
›5 шрифтов, которые следует прекратить использовать (и лучшие альтернативы)

Динамический диапазон в Microsoft Excel обычно используется для хранения большого количества данных с применением функции СМЕЩ. Эти сохраненные данные с определенным именем затем используются для различных расчетов в различных функциях. В этой статье вы точно узнаете, как использовать эту функцию СМЕЩЕНИЯ для хранения, определения и использования диапазона ячеек или данных в Excel.

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

Загрузить рабочую тетрадь

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

Создание и использование динамического именованного диапазона с функцией OFFSET

Прежде чем приступить к созданию и использованию динамического именованного диапазона с функцией СМЕЩ в Excel, давайте сначала познакомимся с функцией СМЕЩ.

Введение в функцию OFFSET

Возвращает ссылку на диапазон, который представляет собой заданное количество строк и столбцов из заданной ссылки.

=OFFSET(ссылка, строки, столбцы, [высота], [ширина])

ссылка — ячейка или диапазон ячеек. На основе этой ссылки применяются параметры смещения.

rows – номер строки, который отсчитывается вниз или вверх от контрольной точки.

cols – номер столбца, отсчитываемый справа или слева от эталонного значения.

[height] — высота или количество строк, которые будут возвращены в качестве результирующих значений.

[width] — ширина или количество столбцов, которые будут возвращены в качестве результирующих значений.

На рисунке ниже есть 4 столбца со случайными названиями брендов компьютеров, типов устройств, названий моделей и цен.

На основе данных из таблицы мы собираемся назначить аргументы, указанные в столбце H.

📌 Шаги:

➤ После нажатия Enter вам будет показан массив возвращаемых значений на основе выбранных вами аргументов.

Так как же работает эта функция? Внутри функции первым аргументом является ячейка B4, известная как опорное значение. Теперь перейдите к 5-й строке вниз и 2-му столбцу справа от этой контрольной ячейки, и вы получите ячейку D9. Поскольку наша высота строки равна 2, функция возвращает 4 ячейки вниз, начиная с D9. И, наконец, высота столбца - 2 означает, что 4 строки будут расширены до следующего столбца прямо до столбца D. Таким образом, окончательный результирующий массив будет состоять из диапазона ячеек D9:E12.

Создание динамического диапазона с помощью функций OFFSET и COUNTA

Функция COUNTA подсчитывает количество ячеек, исключая все пустые ячейки в диапазоне ячеек. Теперь, используя функции COUNTA, мы назначим высоту строки и ширину столбца на основе доступных данных в диапазоне.

📌 Шаги:

➤ Выберите ячейку H4 и введите:

➤ Нажмите Enter, и вы увидите, что в качестве результирующих значений будет возвращен весь массив.

В разделе аргументов высота строки была назначена с помощью COUNTA(B4:B100), и это означает, что мы назначаем строки до 100-й строки в электронной таблице, чтобы при вводе нового значения в пределах исходного диапазона данных в 100-й строке, это новое значение также будет сохранено функцией OFFSET. Опять же, поскольку ширина столбца была определена как COUNTA(B4:E4), четыре столбца (B, C, D, E) теперь назначаются функции на основе эталонного значения, выбранного в функции OFFSET.

На рисунке ниже показан пример того, как при вводе значения в пределах исходного диапазона данных результирующее значение мгновенно отображается в таблице OFFSET.

Использование диспетчера имен для создания динамического именованного диапазона с функциями OFFSET и COUNTA

Используя диспетчер имен, вы можете определить имя результирующего массива, найденного с помощью функции OFFSET.

📌 Шаг 1:

➤ На вкладке "Формула" выберите "Диспетчер имен". Откроется диалоговое окно.

➤ Нажмите "Создать", и появится окно "Редактор имен".

📌 Шаг 2:

➤ Определите имя набора данных или диапазон ячеек, которые вы хотите сместить.

➤ В поле ссылки введите формулу:

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

📌 Шаг 3:

➤ Теперь закройте Диспетчер имен и вернитесь к электронной таблице.

📌 Шаг 4:

➤ Выберите любую ячейку в электронной таблице и начните вводить определенное имя в качестве формулы. Вы найдете определенное имя в списке функций.

➤ Выберите эту функцию и нажмите Enter.

Как на картинке ниже, вы увидите результирующий массив, который был сохранен в качестве ссылки с помощью функции OFFSET диспетчера имен.

Использование динамического именованного диапазона для вычислений

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

📌 Шаг 1:

➤ Снова откройте редактор названий и назовите его «Цены».

➤ В поле эталонной функции введите формулу:

➤ Нажмите OK, и Диспетчер имен покажет определенное имя для цен со справочной формулой внизу.

📌 Шаг 2:

➤ Закройте Диспетчер имен и верните его в свою таблицу.

📌 Шаг 3:

➤ Поскольку мы узнаем сумму всех цен из списка, формула с вновь определенным именованным диапазоном в ячейке H11 будет выглядеть так:

➤ После нажатия Enter вы сразу получите общую стоимость всех устройств.

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

Аналогичным образом, используя функции AVERAGE, MAX и MIN, вы также можете оценить некоторые другие данные в столбце H, показанные на следующем рисунке.

Альтернатива смещению: создание динамического диапазона с помощью функции ИНДЕКС

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

📌 Шаг 1:

➤ Снова откройте Редактор имен и введите формулу в поле ссылки:

➤ Нажмите Enter, и вы найдете новое имя в диспетчере имен.

📌 Шаг 2:

➤ Закройте Диспетчер имен, и все готово.

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

Заключительные слова

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

Читайте также: