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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

0;$D$7

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

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

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

Exceltip

Блог о программке Microsoft Excel: приемы, хитрости, секреты, трюки

Именованные спектры в Excel — несколько трюков использования

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

Итак, несколько советов, которые сделают вашу работу с именованными спектрами в Excel наиболее резвой и продуктивной.

Многоразовое создание именованного спектра в один прием

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

Например, у вас имеется набор данных (как показано ниже) и вы желаете сделать отдельные именованные спектры для каждой колонки. Заместо того чтоб создавать их по одному, вы сможете пользоваться сочетанием кнопок CTRL + SHIFT + F3, которое откроет диалоговое окно Создание имен из выделенного спектра. Тоже самое окно доступно во вкладке Формулы -> Определенные имена –> Сделать из выделенного. Сейчас вы сможете сделать больше 1-го спектра – по строчкам, столбцам, оба варианта.

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

Доступ к управлению именованными спектрами

Чтоб открыть диалоговое окно Диспетчер имен, перейдите по вкладке Формулы в группу Определенные имена и щелкните по кнопочке Диспетчер имен. Или нажатием сочетаний кнопок Ctrl + F3.

Внедрение формулы СМЕЩ

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

Внедрение абсолютных ссылок при работе с именованными спектрами

По сути не уверен, это конструктивная изюминка либо ошибка. Используя относительные ссылки (A1 заместо $A$1) при определении именованного спектра, они не остаются на том же месте, вроде бы вы этого не желали. Давайте разглядим этот вариант на примере. Представим, вы желаете сделать спектр, который сдвигается вниз на 10 строк от ячейки A1. 1-ое, что приходит в голову, это написать формулу =СМЕЩ(A1;10;0).

Пока все отлично. Если вы возжелаете пользоваться сиим именованным спектром, нужно подобрать для нее ячейку (скажем B1) и ввести что-то типа =мой_имен_диап. Где мой_имен_диап — это имя, которое вы дали спектру на прошлом шаге.

Но если вы изберите другую ячейку и опять откроете диспетчер имен, формула, которую вы ввели ранее =СМЕЩ(Лист1!A1;10;0), волшебным образом преобразится (например, =СМЕЩ(Лист1!A1048576;10;0)). Это происходит поэтому, что при разработке именованного спектра мы употребляли относительные ссылки, т.е. мотивированной спектр будет всегда сдвигаться в зависимости от адреса, избранной сейчас, ячейки.

Внедрение F2 для конфигурации именованного спектра

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

Чтоб избежать недоразумений при использовании стрелок, нажмите кнопку F2.

Может быть у вас имеются свои трюки по использованию именованных диапазонов?! Не желаете поделиться?)

Для вас также могут быть увлекательны последующие статьи

2 комментария

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

Здрасти, Ренат! Весьма увлекательная диаграмма, даже при том, что и не традиционный тримап. Скажите, а можно выстроить схожую диаграмму так, чтоб её составляющие были положительные и отрицательные. Чтоб их размер зависел, как далековато их значение от 0, а размещались они справа и слева от оси, в зависимости от знака? Была бы весьма увлекательная диаграмма весов.