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

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

Обсуждая в статье “7 причин полюбить именованные диапазоны” способы создания именованных диапазонов, мы в “пятой причине” говорили о возможности создания ИД, который самостоятельно подстраивается под свои текущие размеры. Решение это достаточно известно и построено на формуле СМЕЩ (OFFSET). Данная формула является медленной или волатильной (volatile), так как она пересчитывается всякий раз, когда вы вводите данные в какую-либо ячейку какой-либо открытой книги Excel . Когда данных в книге мало, то вы это не заметите, однако, если таких формул у вас много и/или вы применяете их к большим диапазонам данных, то вы увидите, как ваша книга начнёт буквально спотыкаться на каждом шагу, реагируя на любые ваши действия чувствительными подвисаниями. Есть достаточно серьёзные причины, по которым эта и подобные ей формулы (например, ДВССЫЛ (), СЕГОДНЯ (), ТДАТА (), СЛУЧМЕЖДУ (), СЛЧИС ()) пересчитываются именно так, но мы не будем сейчас в это углубляться. Нас сейчас занимает вопрос, а есть ли способ динамически определить именованный диапазон, не пользуясь волатильными формулами?

Есть такая формула!

Оказывается, это можно сделать при помощи формулы ИНДЕКС (INDEX). Рассмотрим сначала более простой случай, когда ваш диапазон состоит из одного столбца и меняться у него может только количество строк. В этом случае формула, динамически определяющая этот диапазон, будет выглядеть так:

= Single ! $A$2 : ИНДЕКС ( Single ! $A:$A ; СЧЁТЗ ( Single ! $A:$A ) )

Single! $A$2 – с этой ячейки листа Single начинаются значения нашего динамического диапазона;

“:” – обратите внимание на оператор определения диапазона – двоеточие. Слева от двоеточия располагается ссылка на левый верхний угол диапазона, а справа – на нижний правый;

ИНДЕКС ( Single ! $A:$A ; ) – при помощи формулы ИНДЕКС как раз и вычисляется ссылка на нижний правый угол диапазона. Функция ИНДЕКС , вообще говоря, возвращает значение из диапазона, находящееся на пересечении указанной строки и столбца. Когда такой диапазон состоит из одного столбца, то третий параметр (номер столбца) можно не указывать (наш случай). Так вот в качестве диапазона мы указали весь столбец A , а номер строки (тут заменен на ) мы вычисляем при помощи дополнительной формулы, о которой читайте строчкой ниже;

СЧЁТЗ ( Single ! $A:$A ) – это то, что стоит вместо конструкции в формуле ИНДЕКС . Формула СЧЁТЗ подсчитывает количество непустых ячеек в указанном диапазоне.

Рекомендую располагать ваши диапазоны, начиная с A1 . В противном случае в формулу надо будет внести незначительные коррективы. Например, если ваш диапазон будет начинаться в ячейке B2 , то формула изменится так:

=Shifted!$B$3:ИНДЕКС( Shifted!$B$3:$B$10000; СЧЁТЗ( Shifted!$B$3:$B$10000) )

Тут имеется в виду, что в диапазоне заведомо не будет больше 10000 строк. В противном случае нужно было бы использовать большее значение (максимально возможное 1048576).

Теперь – более сложный случай, когда диапазон состоит более чем из одного столбца и, соответственно, может изменяться как по высоте (строки), так и по ширине (столбцы):

= Multi !$A$2 : ИНДЕКС ( Multi ! $A:$AA ; СЧЁТЗ ( Multi! $A:$A ); СЧЁТЗ ( Multi ! $1:$1 ) )

Multi ! $A$2 – аналогично

ИНДЕКС ( Multi ! $A:$AA ; ; ) – тоже самое, что и в предыдущем примере, но добавляется третий параметр для функции ИНДЕКС , так как наш диапазон состоит из нескольких столбцови необходимо указывать, из какого именно столбца возвращать ссылку (параметр );

СЧЁТЗ ( Multi ! $A:$A ) – аналогично

СЧЁТЗ ( Multi ! $1:$1 ) – вычисление номера крайнего правого столбца нашего диапазона. Предполагается, что в строке 1 располагаются заголовки нашей таблицы и количество непустых ячеек в указанном векторе равно количеству столбцов в нашем диапазоне.

Выводы

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

Источник: perfect-excel.ru

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

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

Динамический диапазон – это Именованный диапазон с изменяющимися границами. Границы диапазона изменяются в зависимости от количества значений в определенном диапазоне.

Динамические диапазоны используются для создания таких структур, как: Выпадающий (раскрывающийся) список , Вложенный связанный список и Связанный список .

Задача

Имеется таблица продаж по месяцам некоторых товаров (см. Файл примера ):

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

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

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

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

Для создания динамического диапазона:

  • на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
  • в поле Имя введите: Месяц ;
  • в поле Область выберите лист Книга ;
  • в поле Диапазон введите формулу =СМЕЩ(лист1!$B$5;;;1;СЧЁТЗ(лист1!$B$5:$I$5))
  • нажмите ОК.

Теперь подробнее. Любой диапазон в EXCEL задается координатами верхней левой и нижней правой ячейки диапазона. Исходной ячейкой, от которой отсчитывается положение нашего динамического диапазона, является ячейка B5 . Если не заданы аргументы функции СМЕЩ() смещ_по_строкам, смещ_по_столбцам (как в нашем случае), то эта ячейка является левой верхней ячейкой диапазона. Нижняя правая ячейка диапазона определяется аргументами высота и ширина . В нашем случае значение высоты =1, а значение ширины диапазона равно результату вычисления формулы СЧЁТЗ(лист1!$B$5:$I$5) , т.е. 4 (в строке 5 присутствуют 4 месяца с января по апрель ). Итак, адрес нижней правой ячейки нашего динамического диапазона определен – это E 5 .

При заполнении таблицы данными о продажах за май , июнь и т.д., формула СЧЁТЗ(лист1!$B$5:$I$5) будет возвращать число заполненных ячеек (количество названий месяцев) и соответственно определять новую ширину динамического диапазона, который в свою очередь будет формировать Выпадающий список .

Читайте также:  Как добавить легенду в диаграмму excel 2010

ВНИМАНИЕ! При использовании функции СЧЕТЗ() необходимо убедиться в отсутствии пустых ячеек! Т.е. нужно заполнять перечень месяцев без пропусков.

Теперь создадим еще один динамический диапазон для суммирования продаж.

Для создания динамического диапазона :

  • на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
  • в поле Имя введите: Продажи_за_месяц ;
  • в поле Диапазон введите формулу = СМЕЩ(лист1!$A$6;;ПОИСКПОЗ(лист1!$C$1;лист1!$B$5:$I$5;0);12)
  • нажмите ОК.

Функция ПОИСКПОЗ() ищет в строке 5 (перечень месяцев) выбранный пользователем месяц (ячейка С1 с выпадающим списком) и возвращает соответствующий номер позиции в диапазоне поиска (названия месяцев должны быть уникальны, т.е. этот пример не годится для нескольких лет). На это число столбцов смещается левый верхний угол нашего динамического диапазона (от ячейки А6 ), высота диапазона не меняется и всегда равна 12 (при желании ее также можно сделать также динамической – зависящей от количества товаров в диапазоне).

И наконец, записав в ячейке С2 формулу = СУММ(Продажи_за_месяц) получим сумму продаж в выбранном месяце.

Или, например, в апреле.

Примечание: Вместо формулы с функцией СМЕЩ() для подсчета заполненных месяцев можно использовать формулу с функцией ИНДЕКС() : = $B$5:ИНДЕКС(B5:I5;СЧЁТЗ($B$5:$I$5))

Формула подсчитывает количество элементов в строке 5 (функция СЧЁТЗ() ) и определяет ссылку на последний элемент в строке (функция ИНДЕКС() ), тем самым возвращает ссылку на диапазон B5:E5 .

Визуальное отображение динамического диапазона

Выделить текущий динамический диапазон можно с помощью Условного форматирования . В файле примера для ячеек диапазона B6:I14 применено правило Условного форматирования с формулой: = СТОЛБЕЦ(B6)=СТОЛБЕЦ(Продажи_за_месяц)

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

Источник: excel2.ru

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

Есть ли у вас таблицы с данными в Excel, размеры которых могут изменяться, т.е. количество строк (столбцов) может увеличиваться или уменьшаться в процессе работы? Если размеры таблицы «плавают», то придется постоянно мониторить этот момент и подправлять:

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

Все это в сумме не даст вам скучать 😉

Гораздо удобнее и правильнее будет создать динамический «резиновый» диапазон, который автоматически будет подстраиваться в размерах под реальное количество строк-столбцов данных. Чтобы реализовать такое, есть несколько способов.

Способ 1. Умная таблица

Выделите ваш диапазон ячеек и выберите на вкладке Главная – Форматировать как Таблицу (Home – Format as Table):

Если вам не нужен полосатый дизайн, который добавляется к таблице побочным эффектом, то его можно отключить на появившейся вкладке Конструктор (Design). Каждая созданная таким образом таблица получает имя, которое можно заменить на более удобное там же на вкладке Конструктор (Design) в поле Имя таблицы (Table Name) .

Теперь можно использовать динамические ссылки на нашу «умную таблицу»:

  • Таблица1 – ссылка на всю таблицу кроме строки заголовка (A2:D5)
  • Таблица1[#Все] – ссылка на всю таблицу целиком (A1:D5)
  • Таблица1[Питер] – ссылка на диапазон-столбец без первой ячейки-заголовка (C2:C5)
  • Таблица1[#Заголовки] – ссылка на «шапку» с названиями столбцов (A1:D1)

Такие ссылки замечательно работают в формулах, например:

=СУММ(Таблица1[Москва]) – вычисление суммы по столбцу «Москва»

=ВПР(F5;Таблица1;3;0) – поиск в таблице месяца из ячейки F5 и выдача питерской суммы по нему (что такое ВПР?)

Такие ссылки можно успешно использовать при создании сводных таблиц, выбрав на вкладке Вставка – Сводная таблица (Insert – Pivot Table) и введя имя умной таблицы в качестве источника данных:

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

При создании выпадающих списков прямые ссылки на элементы умной таблицы использовать нельзя, но можно легко обойти это ограничение с помощью тактической хитрости – использовать функцию ДВССЫЛ (INDIRECT) , которая превращает текст в ссылку:

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

Способ 2. Динамический именованный диапазон

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

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

Нам потребуются две встроенных функции Excel, имеющиеся в любой версии – ПОИКСПОЗ (MATCH) для определения последней ячейки диапазона и ИНДЕКС (INDEX) для создания динамической ссылки.

Ищем последнюю ячейку с помощью ПОИСКПОЗ

ПОИСКПОЗ(искомое_значение;диапазон;тип_сопоставления) – функция, которая ищет заданное значение в диапазоне (строке или столбце) и выдает порядковый номер ячейки, где оно было найдено. Например, формула ПОИСКПОЗ(“март”;A1:A5;0) выдаст в качестве результата число 4, т.к. слово «март» расположено в четвертой по счету ячейке в столбце A1:A5. Последний аргумент функции Тип_сопоставления = 0 означает, что мы ведем поиск точного соответствия. Если этот аргумент не указать, то функция переключится в режим поиска ближайшего наименьшего значения – это как раз и можно успешно использовать для нахождения последней занятой ячейки в нашем массиве.

Суть трюка проста. ПОИСКПОЗ перебирает в поиске ячейки в диапазоне сверху-вниз и, по идее, должна остановиться, когда найдет ближайшее наименьшее значение к заданному. Если указать в качестве искомого значение заведомо больше, чем любое имеющееся в таблице, то ПОИСКПОЗ дойдет до самого конца таблицы, ничего не найдет и выдаст порядковый номер последней заполненной ячейки. А нам это и нужно!

Читайте также:  Выделить повторяющиеся значения в excel

Если в нашем массиве только числа, то можно в качестве искомого значения указать число, которое заведомо больше любого из имеющихся в таблице:

Для гарантии можно использовать число 9E+307 (9 умножить на 10 в 307 степени, т.е. 9 с 307 нулями) – максимальное число, с которым в принципе может работать Excel.

Если же в нашем столбце текстовые значения, то в качестве эквивалента максимально большого числа можно вставить конструкцию ПОВТОР(“я”;255) – текстовую строку, состоящую из 255 букв «я» – последней буквы алфавита. Поскольку при поиске Excel, фактически, сравнивает коды символов, то любой текст в нашей таблице будет технически «меньше» такой длинной «яяяяя….я» строки:

Формируем ссылку с помощью ИНДЕКС

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

ИНДЕКС(диапазон; номер_строки; номер_столбца)

Она выдает содержимое ячейки из диапазона по номеру строки и столбца, т.е. например функция =ИНДЕКС(A1:D5;3;4) по нашей таблице с городами и месяцами из предыдущего способа выдаст 1240 – содержимое из 3-й строки и 4-го столбца, т.е. ячейки D3. Если столбец всего один, то его номер можно не указывать, т.е. формула ИНДЕКС(A2:A6;3) выдаст «Самару» на последнем скриншоте.

Причем есть один не совсем очевидный нюанс: если ИНДЕКС не просто введена в ячейку после знака =, как обычно, а используется как финальная часть ссылки на диапазон после двоеточия, то выдает она уже не содержимое ячейки, а ее адрес! Таким образом формула вида $A$2:ИНДЕКС($A$2:$A$100;3) даст на выходе уже ссылку на диапазон A2:A4.

И вот тут в дело вступает функция ПОИСКПОЗ, которую мы вставляем внутрь ИНДЕКС, чтобы динамически определить конец списка:

=$A$2:ИНДЕКС($A$2:$A$100; ПОИСКПОЗ(ПОВТОР(“я”;255) ;A2:A100))

Создаем именованный диапазон

Осталось упаковать все это в единое целое. Откройте вкладку Формулы (Formulas) и нажмите кнопку Диспетчер Имен (Name Manager) . В открывшемся окне нажмите кнопку Создать (New) , введите имя нашего диапазона и формулу в поле Диапазон (Reference) :

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

Источник: www.planetaexcel.ru

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

Адаптированный перевод статьи Тома Огера (Tom Auger) Named Ranges in Excel that Automatically Expand (Dynamic Ranges Part 1). Статья была доступна здесь.

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

Именованные диапазоны в Excel — это отличный инструмент. Они позволяют делать такие вещи, как выпадающие списки в пункте Проверка данных. Или можно присвоить имя диапазону с данными и в дальнейшем ссылаться на него вместо того, чтобы указывать координаты (A1:B5).

Одна из неприятностей, связанных с поддержкой списков — необходимость править диапазон в Формулы > Диспетчер имён после каждого добавления/удаления строк данных в исходном диапазоне. Чтобы избежать подобной ситуации, можно создать динамический диапазон, применив формулы вместо жёстко заданных координат. Чаще всего используется функция СМЕЩ, как показано ниже. Запрос «Excel динамический диапазон» в любом поисковике вернёт сотни ссылок, большинство из которых будут вариантами формулы:

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

СМЕЩ возвращает диапазон, модифицированный относительно базового – пункт Ссылка. Смещпострокам и Смещпостолбцам смещают начало диапазона на соответствующее число строк и столбцов. Высота и Ширина задают количество строк и столбцов в диапазоне.

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

Смещпострокам: обычно 0, т.к. стартовую позицию мы уже определили.

Смещпостолбцам: так же обычно 0, по той же самой причине.

Высота: количество строк нашего диапазона (здесь будет формула, см. ниже).

Ширина: количество столбцов в нашем диапазоне (минимум 1).

Понятно, что функция СМЕЩ сама по себе не слишком полезна для наших целей. Если вы собираетесь использовать статические числа, то откажитесь от СМЕЩ, непосредственно прописав диапазон. Причина, по которой мы используем СМЕЩ — возможность замены параметров (аргументов) формулами. Они и позволяют получить динамическую часть.

Типичная формула динамического диапазона, которую можно найти на просторах интернета, использует СЧЁТ (для числовых данных) или СЧЁТЗ (для текста). Обе эти функции подсчитывают количество непустых ячеек. Если мы подсчитаем количество непустых ячеек в одном столбце и введём это число в параметр Высота, то мы получим диапазон от начальной ячейки до последней ячейки с записью. Но это в теории.

В действительности, если вы внимательно читали, мы получим диапазон высотой в то количество строк, которое мы получили от СЧЁТЗ. Если в диапазоне присутствуют пустые ячейки, то выходной диапазон окажется короче, чем необходимо, и последние ячейки потеряются. Помните об этой тонкости.

Наиболее часто динамический диапазон используется в следующих случаях:

  • в ссылках в формулах для других таблиц
  • для определения исходных диапазонов сводных таблиц
  • для определения исходных диапазонов диаграмм
  • для определения наборов переменных выпадающих списков

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

Сначала мы создаём динамический именованный диапазон с помощью Диспетчера имён, после чего используем в нашей формуле.

Формулы, которые мы использовали:

Следующий вариант применения динамического диапазона — сводные таблицы. Воспользуемся «базой» из предыдущего примера. Нам придётся видоизменить формулу динамического диапазона, так как нужно включить заголовки столбцов и включить второй столбец. Мы хотим получить общую сумму для каждого клиента.

Вот начальные данные:

А вот после добавки новых строк (не забываем нажать Обновить):

Третий вариант использования динамического диапазона — определение исходного диапазона для диаграмм. Здесь выводится график, диапазон исходных данных которого регулируется параметром.

В данном примере использованы два динамических диапазона.

Последний пример, который мы рассмотрим – создание выпадающих списков посредством инструмента Проверка данных. Этот инструмент применяется, когда мы хотим ограничить пользователя при вводе данных в ячейку. В столбец А мы поместили допустимый набор данных, а в столбце Н создали выпадающие списки (как их создавать здесь не рассматривается).

Читайте также:  Как в excel найти одинаковые значения

После добавления новых строк:

Сама формула динамического диапазона тривиальна:

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

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

Дело в том, что ИНДЕКС возвращает ссылку, если стоит завершающим выражением диапазона (стоит после двоеточия). Чтобы получить адрес последней ячейки нам надо вычислить номер строки ячейки в диапазоне. Его мы получаем через функцию ПОИСКПОЗ. Она ищет определённое значение в указанном диапазоне. С помощью параметра Тип_сопоставления мы указываем функции искать ближайшее меньшее значение. При этом указываем заведомо большее значение для поиска. В результате функция обязательно дойдёт до последней ячейки.Если поиск идёт по числам, то можно использовать 9E+307. Для текста применим функцию ПОВТОР.

Конструкция ПОВТОР(“я”;255) создаёт строку из 255 литер “я”. При сравнении практически любой текст окажется «меньше», и функция найдёт последнюю строку. Ещё одним плюсом применения функции ИНДЕКС является нечувствительность к пустым ячейкам.

Источник: needfordata.ru

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

Автоматическое определение диапазона “от-до” в исходной таблице моно применять для автоматизации многих задач связанных с динамической выборкой значений. Рассмотрим один из простых для понимания способов реализации данной задачи.

Как автоматически выделять диапазоны для выборки ячеек из таблицы?

Задание является следующим. В одном из столбцов в разных ячейках находятся какие-то значения (в данном случае текстовые строки “граница”). Они определяют начало и конец секторов (диапазонов). Эти значения вставлены автоматически и могут появляться в разных ячейках. Их размеры и количество в них ячеек также может быть разным. Например, на рисунке ниже выбран сектор данных (диапазон) номер 2.

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

Динамическое определение границ выборки ячеек

Для наглядности приведем решение этой задачи с использованием вспомогательного столбца. В первую ячейку в вспомогательном столбце (A7) вводим формулу:

и копируем ее вниз в оставшиеся ячейки. Везде, где в ячейках соседнего столбца находится значение “граница”, функция возвращает номер строки. В противном случае возвращает пустую строку.

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

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

Как получить адрес диапазона ячеек в Excel?

После чего динамически определим адрес начальной ячейки, с которой будет начинаться диапазон. В C2 вводим следующую формулу:

Во втором аргументе функции НАИМЕНЬШИЙ указывается ссылка на ячейку C1, где находится порядковый номер интересующего нас сектора данных (диапазона). А для функции НАИМЕНЬШИЙ – это значение является порядковым номером наименьшего числа в диапазоне вспомогательного столбца $A$7:$A$22 (первый аргумент).

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

Как не сложно догадаться во втором аргументе функции НАИМЕНЬШИЙ мы прибавляем единицу чтобы получить следующее по порядку наименьшее значение в вспомогательном столбце $A$7:$A$22. Все просто и красиво – такая должна быть магия!

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

Автоматическая подсветка цветом диапазонов ячеек по условию

Будем подсвечивать цвет диапазона, который соответствует порядковому номеру указанном в критериях выборки C1.

  1. Выделите диапазон ячеек C7:C22 и выберите иснтрумент «ГЛАВНАЯ»-«Условное фомратирование»-«Создать правило».
  2. В появившемя окне выберите опцию «Использовать формулу для определения форматируемых ячеек». Там же в поле ввода введите такую фомрулу:
  3. Нажмите на кнопку формат и укажите цвет для подсветки соответствующих ячеек. Например, зеленый.

Теперь мы изменим критерий выборки, например, на 1. Автоматически подсветился зеленым цветом весь первый диапазон. Обратите внимание в нем на одну ячейку больше чем во втором, но все работает безошибочно.

Проверка вводимых значений в Excel на ошибки

Наконец, вы можете предупредить ошибку в случае ввода неверных (не соответствующего формата) значений (числа меньшего или равного нулю, большего, чем общее количество диапазонов) в качестве номера диапазона, который вы ищете. Все это можете сделать при помощи проверки данных. Перейдите на ячейку для ввода критериев выборки C1 и выберите инструмент: «ДАННЫЕ»-«Проверка данных».

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

0;$D$7

Нажмите ОК, после внесения всех изменений как показано выше на рисунке.

Теперь при попытке ввода в критерий выборки числа больше чем количество диапазонов в пределах «границ», будет предупреждение для пользователя:

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

Источник: exceltable.com

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

Динамический именованный диапазон автоматически расширяется при добавлении значения в диапазон.

  1. Например, выберите диапазон A1:A4 и присвойте ему имя Prices.
  2. Рассчитайте сумму.
  3. Когда вы добавляете значение к диапазону, Excel не обновляет сумму.

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

  1. На вкладке Formulas (Формулы) выберите Name Manager (Диспетчер имен).
  2. Нажмите кнопку Edit (Изменить).
  3. Кликните по полю Refers to (Диапазон) и введите формулу:

Объяснение: Функция OFFSET (СМЕЩ) принимает 5 аргументов:

  • ссылка: $A$1,
  • смещение по строкам: ,
  • смещение по столбцам: ,
  • высота: COUNTA($A:$A) или СЧЕТЗ($A:$A),
  • ширина: 1.

Формула COUNTA($A:$A) или СЧЕТЗ($A:$A) – подсчитывает число значений в столбце А. Когда вы добавляете значение к диапазону, количество элементов увеличивается. В результате, именованный диапазон расширяется.

  • Нажмите ОК, а затем Close (Закрыть).
  • Теперь, когда вы добавляете значение в диапазон, Excel автоматически обновляет сумму.
  • Источник: office-guru.ru