Трюк №43. Вложение динамических диапазонов для максимальной гибкости таблицы Excel

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

Например, можно создать именованный диапазон с именем Jnames, который ссылается на все имена в отсортированном списке, начинающиеся с буквы J. Начните со списка имен в столбце А, где ячейка А1 — это заголовок, а список отсортирован. Выберите команду Вставка → Имя → Присвоить (Insert → Name → Define). В поле Имя (Names in workbook) введите Names, а в поле Формула (Refers to) введите следующую формулу: =OFFSET($А$2;0;0,COUNTA($А$2:$А$1000);1), в русской версии Excel =СМЕЩ($А$2;0;0;СЧЁТЗ($А$2:$А$1000);1). Щелкните на кнопке Добавить (Add). Теперь еще раз щелкните поле Имя (Names in workbook) и введите Jnames (вместо J можно подставить любую другую букву).

В поле Формула (Refers to) введите следующее: =OFFSET(INDIRECT(ADDRESS(MATCH(«J*»;Names.0)+1.1));0,0;COUNTIF(Names;»J*»);1), в русской версии Excel =СМЕЩ(ДВССЫЛ(АДРЕС(ПОИСКПОЗ(«J*»,Names;0)+1;1));0;0;СЧЁТЕСЛИ(Names;»J*»);1), где «J*» — это критерий для данных, которые нужно найти, в данном случае — имен, начинающихся cj. Щелкните на кнопке Добавить (Add). Когда вы снова щелкнете поле Формула (Refers to), где находится формула, вокруг всех имен, начинающихся с буквы J, появится рамка выделения.

При желании можно создать по одному именованному диапазону для каждой буквы алфавита, но, возможно, лучше, чтобы именованный диапазон изменялся в зависимости от того, какую букву вы вводите в ячейку на рабочем листе. Для этого просто введите любую букву в любую неиспользуемую ячейку и присвойте этой ячейке имя Letter. Теперь выберите команду Данные → Проверка (Data → Validation) и в списке поля Тип данных (Allow) выберите пункт Список (List). Щелкните поле Источник (Source) и введите А*,В*,С* и т. д. для всех 26 букв алфавита. Закончив, щелкните на кнопке ОК.

Выберите команду Вставка → Имя → Присвоить (Insert → Name → Define) и в поле Имя (Names in workbook) введите имя «Names». В поле Формула (Refers to) введите следующую формулу и щелкните на кнопке Добавить (Add): =OFFSET($А$2;0;0;COUNTA($A$2:$А$1000);1), в русской версии Excel =СМЕЩ($А$2;0;0;СЧЁТЗ($А$2:$А$1000);1). Снова щелкните поле Имя (Names in workbook) и введите LetterNames. В поле Формула (Refers to) введите следующую формулу и, закончив, щелкните на кнопке Добавить (Add) и на кнопке ОК: =OFFSET(INDIRECT(ADDRESS(MATCH(Letter;Names;0)+1;1));0;0;COUNTIF(Names;Letter;1). В русской версии Excel
=СМЕЩ(ДВССЫЛ(АДРЕС(ПОИСКПОЗ(Letter;Names,0)+1;1);0;0;СЧЁТЕСЛИ(Names;Letter);1).
[stextbox id=»warning»]Формулы для динамических именованных диапазонов не нужно вводить заново с нуля. Вместо этого в диалоговом окне Присвоение имени (Define Name) щелкните существующий динамический именованный диапазон, измените имя в поле Имя (Names in workbook), перейдите в поле Формула (Refers to), измените формулу и щелкните на кнопке Добавить (Add). Исходный динамический именованный диапазон не будет заменен новым, вместо этого будет создан новый диапазон с другим именем, которое вы ему присвоите.[/stextbox]
Для проверки введите любую букву в ячейку с именем Letter и вы увидите рамку выделения вокруг данных, начинающихся с этой буквы.